range

If there were you, the world would be just right

<?php

$db = new ran_pdo("qx_yisdk_user",true); 

$tables = [
    "yisdk_user",
];

foreach ($tables as $tab) {
    $count_sql = 'select count(*) c from yisdk_user where last_login_time>=1602518400 and uuid!=MD5(concat("new_",CONCAT_WS("_",channel,pf_game_id,user_id)))'; 

    $row = $db->get_one($count_sql);
    // 需要执行的条数
    $count = !empty($row["c"]) ? $row["c"] : 0;

    // 每次执行的条数 1000
    $do_num = 100;

    // 需要执行的次数
    $dc = ceil($count/$do_num);

    if($dc) for ($i=0; $i < $dc; $i++) { 
        $p = $i * $do_num;
        $sql = "select uuid,MD5(concat('new_',CONCAT_WS('_',channel,pf_game_id,user_id))) as new_uuid from yisdk_user where last_login_time>=1602518400 and uuid!=MD5(concat('new_',CONCAT_WS('_',channel,pf_game_id,user_id))) limit {$p},{$do_num}";

        $data = $db->get_all($sql);

        $update_sql = "UPDATE {$tab} a JOIN  (";
        $k = 0;
        foreach ($data as $key => $val) {

                $k = 1;
                $update_sql .= " SELECT '{$val['uuid']}' as uuid,'{$val['new_uuid']}' AS new_uuid UNION";

        }

        $sql = rtrim($update_sql,"UNION"); 
        $sql .= ") b USING(uuid) SET a.uuid=b.new_uuid";
        echo $sql;die;
        if($k){
            $db->query($sql);
        }
        sleep(1);
    }
}

分表?
将单个大的数据库表进行拆分,拆分成多个数据表,然后用户访问的时候,根据一定的算法(hash/取模),让用户访问不同的表,这样数据分散到多个数据表中,减少了单个数据表的访问压力,缩短查询时间提升了数据库访问性能。

场景1:
1000万用户user表,拆分为100个分表,命名 user_00 - user_99 ,通过用户id 取模的方法把数据分散到这100个表中
场景2:
用户登录操作日志类,日流量高达500万+,通过使用日期分表方法,将数据按日期保存表名 xxlog_20190918

分表缺点
查询汇总数据难度增大

分区

  • 逻辑上表仍然是一张完整的表,只是将表中的数据在物理上存放到多个表空间(物理文件)
  • 一个表最多只能有1024个分区。
  • 如果分区字段中有主键或者唯一索引的列,那么多有主键列和唯一索引列都必须包含进来。
    即:分区字段要么不包含主键或者索引列,要么包含全部主键和索引列。
  • 分区表中无法使用外键约束

分区类型

  • RANGE分区:基于属于一个给定连续区间的列值,把多行分配给分区。
  • LIST分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
  • HASH分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
  • KEY分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

建立分区表注意:

  • 顺应MYSQL的要求,primary key和unique key必须包含在分区key的一部分 所以把分区字段加入到主键中,组成复合主键
  • 查询sql 必须走分区键,避免查所有区
  • 在where语句中包含分区条件时,可以只扫描一个或多个分区表来提高查询效率;
  • 插入数据,如果数据对应本该插入的分区不存在会报错
  • 单张表最多是只能有1024个分区的(所以不按日做表)

分区选择

这个看个人业务,此处主要说我用到的

1、list 分区键必须为整数类型( 弃用 )

2、RANGE 区间范围( 与日期相关的统一采用range )

#维护相关
  查分区内容
    SELECT * FROM tableName PARTITION (p1,p2)
  添加分区:
    alter table tableName add partition (partition p0 values less than(1991));  //只能添加大于分区键的分区  
  删除分区数据
    alter table tableName  truncate partition p1,p2;  
    alter table tableName  truncate partition all; 
  删除分区
    alter table tableName drop partition p0; //可以删除任意分区

3、hash 分区键必须为整数类型( 可用hash则不用key,userId 为整形选择hash作为用户表分区 )

TIPS:
1)hash的分区必须是整数列
2)drop partition命令只能用在RANGE和LIST分区中。
3)分区的字段必须是要包含在主键字段之内,不然会报错

维护相关
  添加分区:
    ALTER TABLE tableName ADD PARTITION PARTITIONS 6; // 加6个区
  减少分区:
    ALTER TABLE tableName COALESCE PARTITION 4; // 减4个区
  移除表的分区
    ALTER TABLE tablename REMOVE PARTITIONING ;

注意:使用remove移除分区是仅仅移除分区的定义,并不会删除数据和drop PARTITION不一样,后者会连同数据一起删除

4、key 分区键字段不限制为整数类型( 设备为字符串可选择key分区 )

TIPS:
1) KEY分区支持除text和BLOB之外的所有数据类型的分区
2) 数据会分布不均(多次测试结果,创建分区个数为奇数 如99,可使数据相对分布更均匀)
3) 无法直接定位数据在哪个分区

打点记录一下

1 如果有设置,先清空

DROP PROCEDURE IF EXISTS proc_batch_insert;

2 创建存储过程

CREATE PROCEDURE proc_batch_insert()
BEGIN
DECLARE i INT;
SET i=100000;

WHILE i < 2000000 DO
    INSERT INTO hd_carry_prize_dzq(`date`,addtime,user_id,username,prize_id,ip,status) VALUES('2019-05-22','1558454400',i,'测试','10105','127.0.0.1',1);
SET i=i+1;
END WHILE;
END;  

3 执行存储过程

call proc_batch_insert();

配置修改

vim /etc/my.cnf

查找sql_mode,如果没有该字段,在[mysqld]标签里新增:

sql_mode = NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

重启mysql

service mysql restart

因为yum安装mysql的时候,数据库的data目录默认是在/var/lib下,出于数据安全性的考虑需要把它挪到/data分区,原ubuntu环境更换centos环境,在数据库迁移这里卡住(个人菜),记录一下
安装mysql5.7

wget http://repo.mysql.com/mysql57-community-release-el7-8.noarch.rpm
rpm -ivh mysql57-community-release-el7-8.noarch.rpm
yum -y install mysql-server
service mysqld restart

临时密码查看:

grep 'temporary password' /var/log/mysqld.log

更改密码(mysql命令):

ALTER USER 'root'@'localhost' IDENTIFIED BY 'your password'

1.停止mysql

service mysqld stop

2.拷贝原数据目录(cp -aR 保留原文件权限)

cp -aR /var/lib/mysql /data/mysql

3.修改 /etc/my.cnf

把/var/lib/mysql 目录相关改为,/data/mysql

4.关键一步,关闭selinux

# 临时关闭
setenforce 0

# 永久关闭,把 /etc/selinux/config 中
SELINUX=enforcing 修改为 SELINUX=disabled

5.启动mysql

service mysqld start

6.查看数据目录

mysql> select @@datadir;
+--------------+
| @@datadir    |
+--------------+
| /data/mysql/ |
+--------------+

我的是yum安装,迁移问题解决。如果启动不成功,请查看日志(位置在my.cnf中有配置),自行百度答案