分区表的原理
数据库表分区把一个大的物理表分成若干个小的物理表,并使得这些小物理表在逻辑上可以被当成一张表来使用。
Select操作:查询的条件一定要有分区的字段;
Insert操作和delete操作:分区层先打开所有的底层表,确认那个分区接收,再将记录写入底层表;
Update操作:分区层先打开所有的底层表,确认那个分区接收,将数据拿出更新,然后确认是哪个分区,将数据写入,原数据删除操作;
虽然每一个操作很多会先打开锁住所有的底层表,但这并不代表在处理过程中是锁住全表的。如果存储引擎能够自己实现行级锁(innoDB),则会在分区层释放对应的表锁,所以建议使用类似innodb的引擎;
分区类型
- RANGE分区:基于属于一个给定连续区间的列值,把多行分配给分区。
- LIST分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
- HASH分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
- 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),'[email protected]',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:执行情况的说明和描述。