Mysql分区表原理-快速造100万用户数据

分区表的原理

数据库表分区把一个大的物理表分成若干个小的物理表，并使得这些小物理表在逻辑上可以被当成一张表来使用。

Select操作：查询的条件一定要有分区的字段；

Insert操作和delete操作：分区层先打开所有的底层表，确认那个分区接收，再将记录写入底层表；

Update操作：分区层先打开所有的底层表，确认那个分区接收，将数据拿出更新，然后确认是哪个分区，将数据写入，原数据删除操作；

虽然每一个操作很多会先打开锁住所有的底层表，但这并不代表在处理过程中是锁住全表的。如果存储引擎能够自己实现行级锁（innoDB），则会在分区层释放对应的表锁，所以建议使用类似innodb的引擎；

分区类型

1. RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。

2. LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

3. HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。

4. KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

快速造100万用户数据

创建表

 CREATE TABLE `app_user`(
 `id` INT  NOT NULL AUTO_INCREMENT COMMENT '主键',
 `name` VARCHAR(50) DEFAULT '' COMMENT '用户名称',
 `email` VARCHAR(50) NOT NULL COMMENT '邮箱',
 `phone` VARCHAR(20) DEFAULT '' COMMENT '手机号',
 `gender` TINYINT DEFAULT '0' COMMENT '性别（0-男 ：1-女）',
 `password` VARCHAR(100) NOT NULL COMMENT '密码',
 `age` TINYINT DEFAULT '0' COMMENT '年龄',
 `create_time` DATETIME DEFAULT NOW(),
 `update_time` DATETIME DEFAULT NOW(),
 PRIMARY KEY (`id`)
 )ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT='app用户信息表'

创建函数

 SET GLOBAL log_bin_trust_function_creators=TRUE; -- 创建函数一定要写这个
 DELIMITER $$   -- 写函数之前必须要写，该标志
 
 CREATE FUNCTION mock_user_data()-- 创建函数（方法）
 RETURNS INT -- 返回类型
 BEGIN-- 函数方法体开始
 DECLARE num INT DEFAULT 1000000; -- 定义一个变量num为int类型。默认值为100 0000
 DECLARE i INT DEFAULT 0;
 
 WHILE i < num DO -- 循环条件
  INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
  VALUES(CONCAT('用户',i),'121313@qq.com',CONCAT('18',FLOOR(RAND() * ((999999999 - 100000000) + 1000000000))),FLOOR(RAND()  *  2),UUID(),FLOOR(RAND()  *  100));
 SET i = i + 1;-- i自增
 END WHILE;-- 循环结束
 RETURN i;
 END; 

调用函数

 SELECT mock_user_data();

查询创建结果

 SELECT id, name, email, phone, gender, password, age, create_time, update_time  FROM app_user;
 
 -- 查询总数
 SELECT COUNT(id) FROM app_user;

Mysql分区实验

HASH 分区

 ALTER  table app_user PARTITION by hash(id) PARTITIONS 5;

每个分区数据量大概是40万

查询效率对照

使用非主键字段查询

 explain select * from app_user au where au.phone = '181873386579';

使用主键查询

 explain select * from app_user au where au.id = 1000000;

使用主键查询type是const，rows 1条，非主键 type是ALL，rows1960365条

explain解释:

select_type：表示 SELECT 的类型。

常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）

PRIMARY（主查询，即外层的查询）

UNION（UNION 中的第二个或者后面的查询语句）

SUBQUERY（子查询中的第一个 SELECT）等。

table：输出结果集的表。

type：表示表的连接类型，性能由好到差的连接类型如下

1.system（表中仅有一行，即常量表）

2.const（单表中最多有一个匹配行，例如 primary key 或者 unique index）

3.eq_ref（对于前面的每一行，在此表中只查询一条记录，简单来说，就是多表连接中使用primary key或者unique index）

4.ref（与eq_ref类似，区别在于不是使用primary key 或者 unique index，而是使用普通的索引）

5.ref_or_null（与 ref 类似，区别在于条件中包含对 NULL 的查询）

6.index_merge(索引合并优化)

7.unique_subquery（in的后面是一个查询主键字段的子查询）

8.index_subquery（与 unique_subquery 类似，区别在于 in 的后面是查询非唯一索引字段的子查询）

9.range（单表中的范围查询）

10.index（对于前面的每一行，都通过查询索引来得到数据）

11.all（对于前面的每一行，都通过全表扫描来得到数据）。

possible_keys：表示查询时，可能使用的索引。

key：表示实际使用的索引。

key_len：索引字段的长度。

rows：扫描行的数量。

Extra：执行情况的说明和描述。

​​​​​​​