都有哪些维度可以进行数据库调优?简言之:
- 索引失效、没有充分利用到索引——索引建立
- 关联查询太多JOIN (设计缺陷或不得已的需求)——SQL优化
- 服务器调优及各个参数设置(缓冲、线程数等)———调整my.cnf。
- 数据过多――分库分表
关于数据库调优的知识点非常分散。不同的DBMS,不同的公司,不同的职位,不同的项目遇到的问题都不尽相同。这里我们分为三个章节进行细致讲解。
虽然SQL查询优化的技术有很多,但是大方向上完全可以分成物理查询优化
和逻辑查询优化
两大块。
- 物理查询优化是通过
索引
和表连接方式
等技术来进行优化,这里重点需要掌握索引的使用。 - 逻辑查询优化就是通过SQL
等价变换
提升查询效率,直白一点就是说,换一种查询写法执行效率可能更高。
1. 数据准备
学员表 插 50万 条, 班级表 插 1万 条。
步骤1:建表
CREATE TABLE class
(
id INT(11) NOT NULL AUTO_INCREMENT,
className VARCHAR(30) DEFAULT NULL,
address VARCHAR(40) DEFAULT NULL,
monitor INT NULL,
PRIMARY KEY (id)
) ENGINE = INNODB
AUTO_INCREMENT = 1
DEFAULT CHARSET = utf8;
CREATE TABLE student
(
id INT(11) NOT NULL AUTO_INCREMENT,
stuno INT NOT NULL,
name VARCHAR(20) DEFAULT NULL,
age INT(3) DEFAULT NULL,
classId INT(11) DEFAULT NULL,
PRIMARY KEY (id)
# CONSTRAINT fk_class_id FOREIGN KEY (classId) REFERENCES t_class (id)
) ENGINE = INNODB
AUTO_INCREMENT = 1
DEFAULT CHARSET = utf8;
步骤2:设置参数
命令开启:允许创建函数设置 :
SET GLOBAL log_bin_trust_function_creators = 1; # 不加global只是当前窗口有效。
步骤3:创建函数
# 保证每条数据都不同
# 随机产生字符串
DELIMITER //
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n
DO
SET return_str = CONCAT(return_str, SUBSTRING(chars_str, FLOOR(1 + RAND() * 52), 1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END //
DELIMITER ;
# 假如要删除
# drop function rand_string;
# 随机产生班级编号
# 用于随机产生多少到多少的编号
DELIMITER //
CREATE FUNCTION rand_num(from_num INT, to_num INT) RETURNS INT(11)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(from_num + RAND() * (to_num - from_num + 1));
RETURN i;
END //
DELIMITER ;
# 假如要删除
# drop function rand_num;
步骤4:创建存储过程
# 创建往stu表中插入数据的存储过程
DELIMITER //
CREATE PROCEDURE insert_stu(START INT, max_num INT)
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0; # 设置手动提交事务
REPEAT
# 循环
SET i = i + 1; # 赋值
INSERT INTO student (stuno, name, age, classId)
VALUES ((START + i), rand_string(6), rand_num(1, 50), rand_num(1, 1000));
UNTIL i = max_num
END REPEAT;
COMMIT; # 提交事务
END //
DELIMITER ;
# 假如要删除
# drop PROCEDURE insert_stu;
# 创建往class表中插入数据的存储过程
# 执行存储过程,往class表添加随机数据
DELIMITER //
CREATE PROCEDURE `insert_class`(max_num INT)
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO class (classname, address, monitor)
VALUES (rand_string(8), rand_string(10), rand_num(1, 100000));
UNTIL i = max_num
END REPEAT;
COMMIT;
END //
DELIMITER ;
# 假如要删除
# drop PROCEDURE insert_class;
步骤5:调用存储过程
# 执行存储过程,往class表添加1万条数据
CALL insert_class(10000);
# 执行存储过程,往stu表添加50万条数据
CALL insert_stu(100000, 500000);
CALL insert_stu(600000, 1000000);
步骤6:删除某表上的索引
# 创建存储过程
DELIMITER //
CREATE PROCEDURE `proc_drop_index`(dbname VARCHAR(200), tablename VARCHAR(200))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE ct INT DEFAULT 0;
DECLARE _index VARCHAR(200) DEFAULT '';
DECLARE _cur CURSOR FOR SELECT index_name
FROM information_schema.STATISTICS
WHERE table_schema = dbname
AND table_name = tablename
AND seq_in_index = 1
AND index_name <> 'PRIMARY';
# 每个游标必须使用不同的declare continue handler for not found set done=1来控制游标的结束
DECLARE CONTINUE HANDLER FOR NOT FOUND set done = 2;
# 若没有数据返回,程序继续,并将变量done设为2
OPEN _cur;
FETCH _cur INTO _index;
WHILE _index <> ''
DO
SET @str = CONCAT("drop index ", _index, " on ", tablename);
PREPARE sql_str FROM @str;
EXECUTE sql_str;
DEALLOCATE PREPARE sql_str;
SET _index = '';
FETCH _cur INTO _index;
END WHILE;
CLOSE _cur;
END //
DELIMITER ;
# 执行存储过程
CALL proc_drop_index("dbname", "tablename");
2. 索引失效案例
MySQL 中提高性能
的一个最有效的方式是对数据表设计合理的索引
。索引提供了高效访问数据的方法,并且加快查询的速度,因此索引对查询的速度有着至关重要的影响。
- 使用索引可以
快速地定位
表中的某条记录,从而提高数据库查询的速度,提高数据库的性能。 - 如果查询时没有使用索引,查询语句就会
扫描表中的所有记录
。在数据量大的情况下,这样查询的速度会很慢。
大多数情况下都(默认)采用B+树
来构建索引。只是空间列类型的索引使用R-树
,并且 MEMORY 表还支持hash索引
。
其实,用不用索引,最终都是优化器说了算。优化器是基于什么的优化器?基于cost开销(CostBaseOptimizer)
,它不是基于规则(Rule-BasedOptimizer)
,也不是基于语义
。怎么样开销小就怎么来。另外,SQL语句是否使用索引,跟数据库版本、数据量、数据选择度都有关系。
开销不是基于时间
2.1 全值匹配我最爱
意思是创建联合索引多个索引同时生效。
系统中经常出现的 sql 语句如下:
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 and classId=4;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 and classId=4 AND name='abcd';
建立索引前执行(关注执行时间):
mysql> SELECT SQL_NO_CACHE * FROM student WHERE age=30 and classId=4 AND name = 'abcd';
Empty set,1 warning ( 0.28 sec)
建立索引
CREATE INDEX idx_age ON student (age);
CREATE INDEX idx_age_classid ON student (age, classId);
CREATE INDEX idx_age_classid_name ON student (age, classId, name);
建立索引后执行:
mysql> SELECT SQL_NO_CACHE * FROM student WHERE age=30 and classId=4 AND name = 'abcd';
Empty set,1 warning (0.01 sec)
可以看到,创建索引前的查询时间是0.28秒,创建索引后的查询时间是0.01秒,索引帮助我们极大的提高了查询效率。
2.2 最佳左前缀法则
在 MySQL 建立联合索引时会遵守最佳左前缀匹配原则,即最左优先,在检索数据时从联合索引的最左边开始匹配。
举例1:
EXPLAIN
SELECT SQL_NO_CACHE *
FROM student
WHERE student.age = 30
AND student.name = 'abcd';
# 走`idx_age_classid_name` 使用了Using index condition
举例2:
EXPLAIN
SELECT SQL_NO_CACHE *
FROM student
WHERE student.classid = 1
AND student.name = 'abcd';
# 没有索引匹配上。
举例3:索引idx_age_classid_name还能否正常使用?
EXPLAIN
SELECT SQL_NO_CACHE *
FROM student
WHERE classid = 4
and student.age = 30
AND student.name = 'abcd';
如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
mysq1> EXPLAIN
mysq1> SELECT SQL_NO_CACHE *
mysq1> FROM student
mysq1> WHERE student.age = 30
mysq1> AND student.name = 'abcd';
结论 :MySQL 可以为多个字段创建索引,一个索引可以包括16个字段。对于多列索引,过滤条件要使用索引必须按照索引建立时的顺序,依次满足,一旦跳过某个字段,索引后面的字段都无法被使用。**如果查询条件中没有使用这些字段中第1个字段时,多列(或联合)索引不会被使用。
拓展:Alibaba《Java开发手册》
索引文件具有 B-Tree 的最左前缀匹配特性,如果左边的值未确定,那么无法使用此索引。
2.3 主键插入顺序
对于一个使用InnoDB
存储引擎的表来说,表中的数据实际上都是存储在聚簇索引
的叶子节点的。而记录又是存储在数据页中的,数据页和记录又是按照记录主键值从小到大
的顺序进行排序,所以如果我们插入
的记录的主键值是依次增大
的话,那我们每插满一个数据页就换到下一个数据页继续插,而如果我们插入的主键值忽大忽小的话,就比较麻烦了,假设某个数据页存储的记录已经满了,它存储的主键值在1~100
之间:
如果此时再插入一条主键值为 9
的记录,那它插入的位置就如下图:
可这个数据页已经满了,再插进来咋办呢?我们需要把当前 页面分裂
成两个页面,把本页中的一些记录移动到新创建的这个页中。页面分裂和记录移位意味着什么?意味着: 性能损耗!
所以如果我们想尽量避免这样无谓的性能损耗,最好让插入的记录的 主键值依次递增
,这样就不会发生这样的性能损耗了。
所以我们建议:让主键具有 AUTO_INCREMENT
,让存储引擎自己为表生成主键,而不是我们手动插入 ,比如person_info
表(这是比较推荐的做法,但并不是很常用的做法,因其id自增会暴露数据,常用的做法是使用改进过的UUID进行逐渐创建):
CREATE TABLE person_info
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
birthday DATE NOT NULL,
phone_number CHAR(11) NOT NULL,
country varchar(100) NOT NULL,
PRIMARY KEY (id),
KEY idx_name_birthday_phone_number (name(10), birthday, phone_number)
);
我们自定义的主键列 id
拥有 AUTO_INCREMENT
属性,在插入记录时存储引擎会自动为我们填入自增的主键值。这样的主键占用空间小,顺序写入,减少页分裂。
2.4 计算、函数、类型转换(自动或手动)导致索引失效
- 这两条 sql 哪种写法更好
CREATE INDEX idx_name ON student (name);
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';
# 这个索引失效。因为用上函数了。
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name, 3) = 'abc';
- 创建索引
CREATE INDEX idx_sno ON student (stuno);
- 第一种:索引优化生效
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';
mysql> SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';
+---------+---------+--------+------+---------+
| id | stuno | name | age | classId |
+---------+---------+--------+------+---------+
| 5301379 | 1233401 | AbCHEa | 164 | 259 |
| 7170042 | 3102064 | ABcHeB | 199 | 161 |
| 1901614 | 1833636 | ABcHeC | 226 | 275 |
| 5195021 | 1127043 | abchEC | 486 | 72 |
| 4047089 | 3810031 | AbCHFd | 268 | 210 |
| 4917074 | 849096 | ABcHfD | 264 | 442 |
| 1540859 | 141979 | abchFF | 119 | 140 |
| 5121801 | 1053823 | AbCHFg | 412 | 327 |
| 2441254 | 2373276 | abchFJ | 170 | 362 |
| 7039146 | 2971168 | ABcHgI | 502 | 465 |
| 1636826 | 1580286 | ABcHgK | 71 | 262 |
| 374344 | 474345 | abchHL | 367 | 212 |
| 1596534 | 169191 | AbCHHl | 102 | 146 |
...
| 5266837 | 1198859 | abclXe | 292 | 298 |
| 8126968 | 4058990 | aBClxE | 316 | 150 |
| 4298305 | 399962 | AbCLXF | 72 | 423 |
| 5813628 | 1745650 | aBClxF | 356 | 323 |
| 6980448 | 2912470 | AbCLXF | 107 | 78 |
| 7881979 | 3814001 | AbCLXF | 89 | 497 |
| 4955576 | 887598 | ABcLxg | 121 | 385 |
| 3653460 | 3585482 | AbCLXJ | 130 | 174 |
| 1231990 | 1283439 | AbCLYH | 189 | 429 |
| 6110615 | 2042637 | ABcLyh | 157 | 40 |
+---------+---------+--------+------+---------+
401 rows in set, 1 warning (0.01 sec)
第二种:索引优化失效
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name, 3) = 'abc';
mysql> SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name, 3) = 'abc';
+---------+---------+--------+------+---------+
| id | stuno | name | age | classId |
+---------+---------+--------+------+---------+
| 5301379 | 1233401 | AbCHEa | 164 | 259 |
| 7170042 | 3102064 | ABcHeB | 199 | 161 |
| 1901614 | 1833636 | ABcHeC | 226 | 275 |
| 5195021 | 1127043 | abchEC | 486 | 72 |
| 4047089 | 3810031 | AbCHFd | 268 | 210 |
| 4917074 | 849096 | ABcHfD | 264 | 442 |
| 1540859 | 141979 | abchFF | 119 | 140 |
| 5121801 | 1053823 | AbCHFg | 412 | 327 |
| 2441254 | 2373276 | abchFJ | 170 | 362 |
| 7039146 | 2971168 | ABcHgI | 502 | 465 |
| 1636826 | 1580286 | ABcHgK | 71 | 262 |
| 374344 | 474345 | abchHL | 367 | 212 |
| 1596534 | 169191 | AbCHHl | 102 | 146 |
...
| 5266837 | 1198859 | abclXe | 292 | 298 |
| 8126968 | 4058990 | aBClxE | 316 | 150 |
| 4298305 | 399962 | AbCLXF | 72 | 423 |
| 5813628 | 1745650 | aBClxF | 356 | 323 |
| 6980448 | 2912470 | AbCLXF | 107 | 78 |
| 7881979 | 3814001 | AbCLXF | 89 | 497 |
| 4955576 | 887598 | ABcLxg | 121 | 385 |
| 3653460 | 3585482 | AbCLXJ | 130 | 174 |
| 1231990 | 1283439 | AbCLYH | 189 | 429 |
| 6110615 | 2042637 | ABcLyh | 157 | 40 |
+---------+---------+--------+------+---------+
401 rows in set, 1 warning (3.62 sec)
type为“ALL”,表示没有使用到索引,查询时间为 3.62 秒,查询效率较之前低很多。
再举例:
-
student 表的字段 stuno 上设置有索引
CREATE INDEX idx_sno ON student(stuno); # 计算导致索引失效 EXPLAIN SELECT SQL_NO_CACHE id, stuno, name FROM student WHERE stuno + 1 = 900001;
运行结果:
类型是 ALL 原因是计算导致了索引失效。
-
索引优化生效(没有计算):
EXPLAIN SELECT SQL_NO_CACHE id, stuno, name FROM student WHERE stuno = 900000;
再举例:
-
student 表的字段 name 上设置有索引
CREATE INDEX idx_sno ON student (stuno); # 上面已经运行过了
-
索引失效:
EXPLAIN SELECT id, stuno, name FROM student WHERE SUBSTRING(name, 1, 3) = 'abc'; # 使用函数导致失效,可以改用like 'abc%'
2.5 类型转换导致索引失效
下列哪个 sql 语句可以用到索引。(假设name字段上设置有索引)
# 未使用到索引(name = 123 发生类型转换,索引失效)
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name = 123;
# 使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name = '123';
2.6 范围条件右边的列索引失效
ALTER TABLE student DROP INDEX idx_name;
ALTER TABLE student DROP INDEX idx_age;
ALTER TABLE student DROP INDEX idx_age_classid;
SHOW INDEX FROM student;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age = 30 AND student.classId > 20 AND student.name = 'abc' ;
因为用上了范围查找,在范围查找的索引后面的索引就失效了。
tips : 因为范围条件导致的索引失效,可以考虑把确定的索引放在前面。
例如上面这个例子,
CREATE INDEX idx_age_name_cid ON student(age, name, classId);
这里 name 放在了范围查找 classId 前面索引就能生效了。
哪些属于范围?
- 大于等于,大于,小于等于,小于
between
应用开发中范围查询,例如: 金额查询,日期查询往往都是范围查询。创建联合索引时考虑放在后面。
创建索引时,务必把范围涉及到的字段写在最后
2.7 不等于( != 或者 <> )索引失效
-
为name字段创建索引
CREATE INDEX idx_name ON student (name);
-
查看索引是否失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name <> 'abc';
没救 索引只能查到知道的东西
2.8 is null可以使用索引,is not null无法使用索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL;
# is not null 索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;
结论:最好在设计数据表的时候就将
字段设置为 NOT NULL 约束
,比如你可以将 INT 类型的字段,默认值设置为 0。将字符类型的默认值设置为空字符串。拓展:同理,在查询中使用
not like
也无法使用索引,导致全表扫描。
2.9 like 以通配符 % 开头索引失效
在使用LIKE关键字进行查询的查询语句中,如果匹配字符串的第一个字符为“%”,索引就不会起作用。只有“%"不在第一个位置,索引才会起作用。
# 用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name LIKE 'ab%';
# 用不到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name LIKE '%ab%';
拓展:Alibaba《Java开发手册》
【强制】页面搜索严禁左模糊或者全模糊,如果需要请走搜索引擎来解决。
2.10 OR 前后存在非索引的列,索引失效
在 WHERE 子句中,如果在 OR 前的条件列进行了索引,而在 OR 后的条件列没有进行索引,那么索引会失效。也就是说,OR 前后的两个条件中的列都是索引时,查询中才使用索引。
CREATE INDEX idx_age ON student (age);
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classId = 100;
因为 OR 的含义就是两个只要满足一个即可,因此只有一个条伴列进行了索引是没有意义的
,只要有条件列没有进行索引,就会进行全表扫描,因此索引的条件列也会失效。
2.11 数据库和表的字符集统一使用utf8mb4
统一使用 utf8mb4(5.5.3版本以上支持) 兼容性更好,统一字符集可以避免由于字符集转换产生的乱码。不同的字符集进行比较前需要进行 转换
会造成索引失效。
2.12 练习及一般性建议
练习:假设:index(a, b, c)
WHERE 语句 | 索引是否被引用 |
---|---|
WHERE a = 3 | Y, 使用到a |
WHERE a = 3 AND b = 5 | Y, 使用到a, b |
WHERE a = 3 AND b = 5 AND c = 4 | Y, 使用到a, b, c |
WHERE b = 3 或者 WHERE b = 3 AND c = 4 或者 WHERE c = 4 | N |
WHERE a = 3 AND c = 5 | 使用到a, 但是c不可以, b中间断了 |
WHERE a = 3 AND b > 4 AND c = 5 | 使用到a和b, c不能用在范围之后, b断了 |
WHERE a IS NULL AND b IS NOT NULL | is null 支持索引 但是 is not null 不支持,索引a可以使用索引, 但是b不可以使用 |
WHERE a <> 3 | 不能使用索引 |
WHERE abs(a) = 3 | 不能使用索引 |
WHERE a = 3 AND b LIKE ‘kk%’ AND c = 4 | Y, 使用到a, b, c |
WHERE a = 3 AND b LIKE ‘%kk’ AND c = 4 | Y, 使用到a |
WHERE a = 3 AND b LIKE ‘%kk%’ AND c = 4 | Y, 使用到a |
WHERE a = 3 AND b LIKE ‘k%kk%’ AND c = 4 | Y, 使用到a, b, c |
一般性建议:
- 对于单列索引,l尽量选择针对当前query过滤性更好的索引
- 在选择组合索引的时候,当前query中过滤性最好的字段在索引字段顺序中,位置越靠前越好。
- 在选择组合索引的时候,尽量选择能够包含当前query中的where子句中更多字段的索引。
- 在选择组合索引的时候,如果某个字段可能出现范围查询时,尽量把这个字段放在索引次序的最后面。
总之,书写SQL语句时,尽量避免造成索引失效的情况。
3. 关联查询优化
3.1 数据准备
# 分类
CREATE TABLE IF NOT EXISTS type
(
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
card INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (id)
);
# 图书
CREATE TABLE IF NOT EXISTS book
(
bookid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
card INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (bookid)
);
# 向分类表中添加20条记录
INSERT INTO type (card) VALUES (FLOOR(1 + (RAND() * 20)));
# 向图书表中添加20条记录
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
3.2 采用左外连接
下面开始 EXPLAIN 分析
EXPLAIN SELECT SQL_NO_CACHE * FROM type LEFT JOIN book ON type.card = book.card;
结论:type 有 All
添加索引优化
# 添加索引
ALTER TABLE book ADD INDEX Y(card); #【被驱动表】,可以避免全表扫描
EXPLAIN SELECT SQL_NO_CACHE * FROM type LEFT JOIN book ON type.card = book.card;
可以看到第二行的 type 变为了 ref,rows 也变成了优化比较明显。这是由左连接特性决定的。LEFT JOIN条件用于确定如何从右表搜索行,左边一定都有,所以 右边是我们的关键点,一定需要建立索引 。
结论:如果只能添加一边的索引,那就给
被驱动表
添加上索引。
ALTER TABLE type ADD INDEX X (card); #【驱动表】,无法避免全表扫描
EXPLAIN SELECT SQL_NO_CACHE * FROM type LEFT JOIN book ON type.card = book.card;
接着:
DROP INDEX Y ON book;
EXPLAIN SELECT SQL_NO_CACHE * FROM type LEFT JOIN book ON type.card = book.card;
去掉被驱动索引,又变成了 join buffer
3.3 采用内连接
前置知识
drop index X on type;
drop index Y on book; #(如果已经删除了可以不用再执行该操作)
换成 inner join(MySQL 自动选择驱动表)
EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card = book.card;
添加索引优化
ALTER TABLE book ADD INDEX Y (card);
EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card = book.card;
# type 加索引
ALTER TABLE type ADD INDEX X (card);
# 观察执行情况
EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card = book.card;
这里刚给 type 加了索引后,驱动表和被驱动表还是原来的样子。
给 type 继续加了一些数据后
优化器会判断,哪个数据比较少。就作为驱动表
结论:
-
内连接
中被驱动表是由优化器决定的。优化器认为哪个成本比较小,就采用哪种作为驱动表; -
如果两张表只有一个有索引,那有索引的表作为
被驱动表
;- 原因:驱动表要全查出来。有没有索引你都得全查出来
-
两个索引都存在的情况下, 数据量大的 作为
被驱动表
(小表驱动大表)。- 原因:驱动表要全部查出来,而大表可以通过索引加快查找
3.4 JOIN语句原理
JOIN 方式连接多个表,本质就是各个表之间数据的循环匹配。MySQL 5.5 版本之前,MySQL 只支持一种表间关联方式,就是嵌套循环(Nested Loop Join
)。如果关联表的数据量很大,则 JOIN 关联的执行时间会非常长。在 MySQL 5.5 以后的版本中,MySQL 通过引入BNLJ
算法来优化嵌套执行。
1. 驱动表和被驱动表
驱动表就是主表,被驱动表就是从表、非驱动表。
-
对于内连接来说:
SELECT * FROM A JOIN B ON ...
A 一定是驱动表吗?不一定,优化器会根据你查询语句做优化,决定先查哪张表。先查询的那张表就是驱动表,反之就是被驱动表。通过 explain 关键字可以查看。
-
对于外连接来说:
SELECT * FROM A LEFT JOIN B ON ... # 或 SELECT * FROM B RIGHT JOIN A ON ...
通常,大家会认为 A 就是驱动表,B 就是被驱动表。但也未必。测试如下:
CREATE TABLE a (f1 INT, f2 INT, INDEX(f1)) ENGINE = INNODB; CREATE TABLE b (f1 INT, f2 INT) ENGINE = INNODB; INSERT INTO a VALUES (1,1), (2,2), (3,3), (4,4), (5,5), (6,6); INSERT INTO b VALUES (3,3), (4,4), (5,5), (6,6), (7,7), (8,8); # 测试1 EXPLAIN SELECT * FROM a LEFT JOIN b ON (a.f1 = b.f1) WHERE (a.f2 = b.f2); # 测试2 EXPLAIN SELECT * FROM a LEFT JOIN b oN (a.f1 = b.f1) AND (a.f2 = b.f2);
测试1结果:
得出这种结论太不可思议了,跟上一个show warnings 看看:
测试2结果:
继续show warnings \G
-
2. Simple Nested-Loop Join (简单嵌套循环连接)
算法相当简单,从表A中取出一条数据1,遍历表B,将匹配到的数据放到 result 以此类推,驱动表A中的每一条记录与被驱动表B的记录进行判断:
这个例子是在没有索引的情况,做了全表扫描
可以看到这种方式效率是非常低的,以上述表A数据100条,表B数据1000条计算,则 A * B = 10万次。开销统计如下:
开销统计 | SNLJ |
---|---|
外表扫描次数: | 1 |
内表扫描次数: | A |
读取记录数: | A + B * A |
JOIN 比较次数: | B * A |
回表读取记录次数: | 0 |
当然 MySQL 肯定不会这么粗暴的去进行表的连接,所以就出现了后面的两种对 Nested-Loop Join 优化算法。
3. Index Nested-Loop Join (索引嵌套循环连接)
Index Nested-Loop Join 其优化的思路主要是为了减少内层表数据的匹配次数
,所以要求被驱动表上必须有索引
才行。通过外层表匹配条件直接与内层表索引进行匹配,避免和内层表的每条记录去进行比较,这样极大的减少了对内层表的匹配次数。
驱动表中的每条记录通过被驱动表的索引进行访问,因为索引查询的成本是比较固定的,故 MySQL 优化器都倾向于使用记录数少的表作为驱动表(外表)。
开销统计 | SNLJ | INLJ |
---|---|---|
外表扫描次数: | 1 | 1 |
内表扫描次数: | A | 0 |
读取记录数: | A + B * A | A + B (match) |
JOIN 比较次数: | B * A | A * Index (Height) |
回表读取记录次数: | 0 | B (match) (if possible) |
如果被驱动表加索引,效率是非常高的,但如果索引不是主键索引,所以还得进行一次回表查询。相比,被驱动表的索引是主键索引,效率会更高。
4. Block Nested-Loop Join (块嵌套循环连接)
如果存在索引,那么会使用 index 的方式进行 join,如果 join 的列没有索引,被驱动表要扫描的次数太多了。每次访问被驱动表,其表中的记录都会被加载到内存中,然后再从驱动表中取一条与其匹配,匹配结束后清除内存,然后再从驱动表中加载一条记录,然后把被驱动表的记录在加载到内存匹配这样周而复始,大大增加了 IO 的次数。为了减少被驱动表的 IO 次数,就出现了 Block Nested-Loop Join 的方式。
不再是逐条获取驱动表的数据,而是一块一块的获取,引入了join buffer缓冲区
,将驱动表join
相关的部分数据列(大小受 join buffer 的限制)缓存到 join buffer 中,然后全表扫描被驱动表被驱动表的每—条记录—次性和 join buffer 中的所有驱动表记录进行匹配(内存中操作
),将简单嵌套循环中的多次比较合并成一次,降低了被驱动表的访问频率。
注意:
这里缓存的不只是关联表的列,select 后面的列也会缓存起来。(存的是驱动表)
在一个有 N 个 join 关联的sql中会分配 N - 1 个 join buffer。所以查询的时候尽量减少不必要的字段,可以让 join buffer 中可以存放更多的列。
开销统计 | SNLJ | INLJ | BNLJ |
---|---|---|---|
外表扫描次数: | 1 | 1 | 1 |
内表扫描次数: | A | 0 | A * used_cloumn_size / join_buffer_size + 1 |
读取记录数: | A + B * A | A + B (match) | A + B * (A * used_cloumn_size / join_buffer_size) |
JOIN 比较次数: | B * A | A * Index (Height) | B * A |
回表读取记录次数: | 0 | B (match) (if possible) | 0 |
参数设置:
-
block_nested_loop
通过
show variables like '%optimizer_switch%'
查看block_nested_loop
状态。默认是开启的。 -
join_buffer_size
驱动表能不能一次加载完,要看 join buffer 能不能存储所有的数据,默认情况下
join_buffer_size = 256k
。mysql> show variables like '%join_buffer%'; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | join_buffer_size | 262144 | +------------------+--------+ 1 row in set (0.00 sec)
join_buffer_size 的最大值在32位系统可以申请4G,而在64位操作系统下可以申请大于4G的 Join Buffer 空间(64位Windows除外,其大值会被截断为4GB并发出警告)。
5. Join小结
1、整体效率比较:INLJ > BNLJ > SNLJ
2、永远用小结果集驱动大结果集(其本质就是减少外层循环的数据数量)(小的度量单位指的是表行数 * 每行大小)
# straight_join 不让优化器优化谁是驱动表 (驱动表 straight_join 被驱动表)
# 这个例子是说 t2 的列比较多,相同的 join buffer 加的会比较少。所以不适合用 t2 作为 !!!驱动表
select t1.b, t2.* from t1 straight_join t2 on (t1.b = t2.b) where t2.id <= 180; # 推荐
select t1.b, t2.* from t2 straight_join t1 on (t1.b = t2.b) where t2.id <= 100; # 不推荐
3、为被驱动表匹配的条件增加索引(减少内层表的循环匹配次数)
4、增大 join buffer size 的大小(一次缓存的数据越多,那么内层包的扫表次数就越少)
5、减少驱动表
不必要的字段查询(字段越少,join buffer 所缓存的数据就越多)
6、在决定哪个表做驱动表的时候,应该是两个表按照各自的条件过滤,过滤完成之后,计算参与 join 的各个字段的总数据量,数据量小的那个表,就是“小表”,应该作为驱动表。
3.5 小结
- 保证被驱动表的 JOIN 字段已经创建了索引
- 需要 JOIN 的字段,数据类型保持绝对一致。
- LEFT JOIN 时,选择小表作为驱动表,
大表作为被驱动表
。减少外层循环的次数。 - INNER JOIN 时,MySQL 会自动将
小结果集的表选为驱动表
。选择相信 MySQL 优化策略。 - 能够直接多表关联的尽量直接关联,不用子查询。(减少查询的趟数)
- 不建议使用子查询,建议将子查询 SQL 拆开结合程序多次查询,或使用 JOIN 来代替子查询。
- 衍生表建不了索引
3.6 Hash Join
从 MySQL 的 8.0.20 版本开始将废弃 BNLJ,因为从 MySQL 8.0.18 版本开始就加入了 hash join 默认都会使用 hash join
-
Nested Loop:
对于被连接的数据子集较小的情况,Nested Loop 是个较好的选择。 -
Hash Join 是做
大数据集连接
时的常用方式,优化器使用两个表中较小(相对较小)的表利用 Join Key 在内存中建立散列表
,然后扫描较大的表并探测散列表,找出与 Hash 表匹配的行。-
这种方式适用于较小的表完全可以放于内存中的情况,这样总成本就是访问两个表的成本之和。
-
在表很大的情况下并不能完全放入内存,这时优化器会将它分割成
若干不同的分区
,不能放入内存的部分就把该分区写入磁盘的临时段,此时要求有较大的临时段从而尽量提高 I/O 的性能。 -
它能够很好的工作于没有索引的大表和并行查询的环境中,并提供最好的性能。大多数人都说它是 Join 的重型升降机。Hash Join只能应用于等值连接 (如 WHERE A.COL1 = B.COL2 ),这是由 Hash 的特点决定的。
类别 Nested Loop Hash Join 使用条件 任何条件 等值连接(=) 相关资源 CPU、磁盘I/O 内存、临时空间 特点 当有高选择性索引或进行限制性搜索时效率比较高,能够快速返回第一次的搜索结果。 当缺乏索引或者索引条件模糊时,Hash Join 比 Nested Loop 有效。在数据仓库环境下,如果表的记录数多,效率高。 缺点 当索引丢失或者查询条件限制不够时,效率很低;当表的记录数多时,效率低。 为建立哈希表,需要大量内存。第一次的结果返回较慢。 -
4.子查询优化
MySQL 从 4.1 版本开始支持子查询,使用子查询可以进行 SELECT 语句的嵌套查询,即一个 SELECT 查询的结果作为另一个 SELECT 语句的条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作
。
子查询是 MySQL 的一项重要的功能,可以帮助我们通过一个 SQL 语句实现比较复杂的查询。但是,子查询的执行效率不高。原因:
① 执行子查询时 MySQL 需要为内层查询语句的查询结果建立一个临时表
,然后外层查询语句从临时表中查询记录。查询完毕后,再撤销这些临时表
。这样会消耗过多的 CPU 和 IO 资源,产生大量的慢查询;
② 子查询的结果集存储的临时表,不论是内存临时表还是磁盘临时表都不会存在索引
,所以查询性能会受到一定的影响;
③ 对于返回结果集比较大的子查询,其对查询性能的影响也就越大。
在MySQL中,可以使用连接(JOIN)查询来替代子查询。连接查询不需要建立临时表
,其速度比子查询要快
,如果查询中使用索引的话,性能就会更好。
举例1:查询学生表中是班长的学生信息
-
使用子查询(不推荐)
# 创建班级表中班长的索引 CREATE INDEX idx_monitor ON class (monitor); EXPLAIN SELECT * FROM student s WHERE s.stuno IN ( SELECT monitor FROM class c WHERE monitor IS NOT NULL );
-
使用多表查询(推荐)
EXPLAIN SELECT s.* FROM student s JOIN class c ON s.stuno = c.monitor WHERE c.monitor IS NOT NULL;
举例2:取所有不为班长的同学
-
子查询(不推荐)
EXPLAIN SELECT SQL_NO_CACHE a.* FROM student a WHERE a.stuno NOT IN ( SELECT monitor FROM class b WHERE monitor IS NOT NULL );
-
修改成多表查询(推荐)
EXPLAIN SELECT SQL_NO_CACHE a.* FROM student a LEFT OUTER JOIN class b ON a.stuno = b.monitor WHERE b.monitor IS NULL;
结论:尽量不要使用 NOT IN 或者 NOT EXISTS,用 LEFT JOIN xxx ON xx WHERE xx IS NULL 替代
5. 排序优化
5.1 排序优化
问题:在 WHERE 条件字段上加索引但是为什么在 ORDER BY 字段上还要加索引呢?
回答:
在 MySQL 中,支持两种排序方式,分别是FileSort
和Index
排序。
- Index 排序中,索引可以保证数据的有序性,不需要再进行排序,
效率更高
。 - FileSort 排序则一般在
内存中
进行排序,占用CPU较多
。如果待排结果较大,会产生临时文件I/O到磁盘进行排序的情况,效率较低。
优化建议:
- SQL 中,可以在 WHERE 子句和 ORDER BY 子句中使用索引,目的是在 WHERE 子句中
避免全表扫描
,在ORDER BY 子句避免使用 FileSort 排序
。当然,某些情况下全表扫描,或者 FileSort 排序不一定比索引慢。但总的来说,我们还是要避免,以提高查询效率。 - 尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列;如果不同就使用联合索引。
- 无法使用 Index 时,需要对 FileSort 方式进行调优。
5.2 测试
删除 student 表和 class 表中已创建的索引。
# 方式1:
DROP INDEX idx_monitor ON class;
DROP INDEX idx_cid ON student;
DROP INDEX idx_age ON student;
DROP INDEX idx_name ON student;
DROP INDEX idx_age_name_classid ON student;
DROP INDEX idx_age_classid_name ON student;
# 方式2:
call proc_drop_index('atguigudb2', 'student');
以下是否能使用到索引,能否去掉using filesort
过程一:
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age, classid;
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age, classid limit 10;
过程二:order by 时不 limit,索引失效
# 创建索引
CREATE INDEX idx_age_classid_name ON student (age, classid, name);
# 不限制,索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age, classid;
这里优化器觉得,还需要回表。会费时间更大,不走索引。
使用覆盖索引试试看
不用回表,优化器觉得走索引快。就使用了索引。
增加 limit 条件
增加 limit 减少回表的数量,优化器觉得走索引快,会使用索引
过程三:order by 时顺序错误,索引失效
CREATE INDEX idx_age_classid_stuno ON student (age, classid, stuno);
# 以下哪些索引失效?
# 不会走,最左前缀原则
EXPLAIN SELECT * FROM student ORDER BY classid LIMIT 10;
# 不会走,最左前缀原则
EXPLAIN SELECT * FROM student ORDER BY classid, name LIMIT 10;
# 走
EXPLAIN SELECT * FROM student ORDER BY age, classid, stuno LIMIT 10;
# 走
EXPLAIN SELECT * FROM student ORDER BY age, classid LIMIT 10;
# 走
EXPLAIN SELECT * FROM student ORDER BY age LIMIT 10;
过程四:order by 时规则不一致,索引失效(顺序错,不索引;方向反,不索引)
# age desc 方向反 索引失效
EXPLAIN SELECT * FROM student ORDER BY age DESC, classid ASC LIMIT 10;
# 没有最左前缀 索引失效
EXPLAIN SELECT * FROM student ORDER BY classid DESC, name DESC LIMIT 10;
# age asc 没问题 classid desc 降序, 优化器认为,文件排序比较快索引失效
# 方向反了不走索引
EXPLAIN SELECT * FROM student ORDER BY age ASC, classid DESC LIMIT 10;
# Backward index scan 走索引了,倒着走索引
EXPLAIN SELECT * FROM student ORDER BY age DESC, classid DESC LIMIT 10;
过程五:无过滤,不索引
EXPLAIN SELECT * FROM student WHERE age = 45 ORDER BY classid;
EXPLAIN SELECT * FROM student WHERE age = 45 ORDER BY classid, name;
EXPLAIN SELECT * FROM student WHERE classid = 45 order by age;
EXPLAIN SELECT * FROM student WHERE classid = 45 order by age limit 10;
这里第一条排序走 Using filesort 很好理解
第二条为啥不是 Using filesort
呢?
这里 type = index,key = idx_age_classid_name。 这说明了 优化器预估对 idx_age_classid_name 索引进行完整的遍历。由于索引本身就是根据 age 升序存储的。所以只要在遍历的过程中,遇到前十个 classid = 45。就可以停止遍历。回表返回数据。(根据上完课自己想的,无法验证,不知道有没有偏差)
小结:
INDEX a_b_c( a, b,c)
order by 能使用索引最左前缀
- ORDER BY a
- ORDER BY a, b
- ORDER BY a , b, c
- ORDER BY a DESC, b DESC,c DESC
# 如果WHERE使用索引的最左前缀定义为常量,则 order by 能使用索引
- WHERE a = const ORDER BY b, c
- WHERE a = const AND b = const ORDER BY c
- WHERE a = const ORDER BY b, c
- WHERE a = const AND b > const ORDER BY b , c
# 不能使用索引进行排序
- ORDER BY a ASC, b DESC, c DESC /* 排序不一致 */
- WHERE g = const ORDER BY b, c /* 丢失a索引 */
- WHERE a = const ORDER BY c /* 丢失b索引 */
- WHERE a = const ORDER BY a, d /* d不是索引的一部分 */
- WHERE a in (...) ORDER BY b, c /* 对于排序来说,多个相等条件也是范围查询 */
索引只会用到一个,没办法一个索引用来where 一个索引用来 order by。
但是可以建立联合索引。
5.3 案例实战
ORDER BY 子句,尽量使用 Index 方式排序,避免使用 FileSort 方式排序。
执行案例前先清除 student 上的索引,只留主键:
DROP INDEX idx_age ON student;
DROP INDEX idx_age_classid_stuno ON student;
DROP INDEX idx_age_classid_name ON student;
# 或者
call proc_drop_index( 'my_sql' , ' student' ) ;
show index from student;
场景:查询年龄为30岁的,且学生编号小于101000的学生,按用户名称排序
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno < 101000 ORDER BY name;
mysql> SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno < 101000 ORDER BY name;
+-----+--------+--------+------+---------+
| id | stuno | name | age | classId |
+-----+--------+--------+------+---------+
| 417 | 100417 | bBAYtX | 30 | 159 |
....
| 372 | 100372 | xwODCc | 30 | 764 |
+-----+--------+--------+------+---------+
18 rows in set, 1 warning (0.17 sec)
结论: type是ALL,即最坏的情况。Extra里还出现了
Using filqsort
,也是最坏的情况。优化是必须的。
优化思路:
方案一:为了去掉 filesort 我们可以把索引建成
# 创建新索引
CREATE INDEX idx_age_name ON student (age, name);
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno < 101000 ORDER BY name;
方案二:尽量让 where 的过滤条件和排序使用上索引
create index idx_age_stuno_name on student (age, stuno, name);
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno < 101000 ORDER BY name;
下面这个方案虽然使用了 Using filesort
但是速度反而更快了。
原因:
所有的排序都是在条件过滤之后才执行的。所以,如果条件过滤掉大部分数据的话,剩下几百几千条数据进行排序其实并不是很消耗性能,即使索引优化了排序,但实际提升性能很有限。相对的 stuno < 101000 这个条件,如果没有用到索引的话,要对几万条的数据进行扫描,这是非常消耗性能的,所以索引放在这个字段上性价比最高,是最优选择。
结论:
两个索引同时存在,MySQL 自动选择最优的方案。(对于这个例子 MySQL 选择idx_age_stuno_name)。但是,
随着数据量的变化,选择的索引也会随之变化的。
当【范围条件】和【group by或者order by】的字段出现二选一时,优先观察条件字段的过滤数量,如果过滤的数据足够多,而需要排序的数据并不多时,优先把索引放在范围字段上。反之,亦然。
思考:这里我们使用如下索引,是否可行?
DROP INDEX idx_age_stuno_name ON student;
# 当然可以了,因为3个也只是用到了两个索引
CREATE INDEX idx_age_stuno ON student (age, stuno);
5.4 filesort算法:双路排序和单路排序
排序的字段若如果不在索引列上,则filesort
会有两种算法:双路排序和单路排序
双路排序(慢)
MySQL 4.1之前是使用双路排序
,字面意思就是两次扫描磁盘,最终得到数据,读取行指针和 order by 列,对他们进行排序,然后扫描已经排序好的列表,按照列表中的值重新从列表中读取对应的数据输出;- 从磁盘取排序字段,在buffer进行排序,再从磁盘取其他字段。
取一批数据,要对磁盘进行两次扫描,众所周知,IO是很耗时的,所以在 mysql 4.1之后,出现了第二种改进的算法,就是单路排序。
单路排序(快)
从磁盘读取查询需要的所有列
,按照 order by 列在 buffer 对他们进行排序,然后扫描排序后的列表进行输出,它的效率更快一些,避免了第二次读取数据。并且把随机IO变成了顺序IO,但是他会使用更多的空间,因为他把每一行都保存在内存中了。
结论及引申出的问题
- 由于单路是后出的,总体而言好过双路
- 但是用单路有问题
- 在 sort_buffer 中,单路比多路要
多占用更多空间
,因为单路是把所有字段都取出,所以有可能取出的数据的总大小超出了sort_buffer
的容量,导致每次只能取sort_buffer
容量大小的数据,进行排序〈创建tmp文件,多路合并),排完再取sort_buffer容量大小,再排…从而多次I/O。 - 单路本来想省一次I/o操作,
反而导致了大量的I/0操作
,反而得不偿失。
- 在 sort_buffer 中,单路比多路要
优化策略
1. 尝试提高 sort_buffer_size
-
不管用哪种算法,提高这个参数都会提高效率,要根据系统的能力去提高,因为这个参数是针对每个进程 (connection) 的 1M - 8M 之间调整。MySQL 5.7,InnoDB 存储引擎默认值是1048576字节,1MB。
mysql> SHOW VARIABLES LIKE '%sort_buffer_size%'; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | innodb_sort_buffer_size | 1048576 | | myisam_sort_buffer_size | 8388608 | | sort_buffer_size | 262144 | +-------------------------+---------+ 3 rows in set (0.00 sec)
2. 尝试提高 max_length_for_sort_data
-
提高这个参数,会增加用改进算法的概率。
mysql> SHow VARIABLES LIKE '%max_length_for_sort_data%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | max_length_for_sort_data | 4096 | +--------------------------+-------+ 1 row in set (0.00 sec)
但是如果设的太高,数据总容量超出
sort_buffer_size
的概率就增大,明显症状是高的磁盘I/O活动和低的处理器使用率。如果需要返回的列的总长度大于max_length_for_sort_data
使用双路算法
,否则使用单路算法。1024 - 8192 字节之间调整
3. Order by 时 select * 是一个大忌。最好只 Query 需要的字段。原因:
- 当 Query 的字段大小总和小于
max_length_for_sort_data
,而且排序字段不是 TEXT | BLOB 类型时,会用改进后的算法――单路排序,否则用老算法――多路排序。 - 两种算法的数据都有可能超出 sort_buffer_size 的容量,超出之后,会创建 tmp 文件进行合并排序,导致多次 I/O,但是用单路排序算法的风险会更大一些,所以要提高
sort_buffer_size
。
6. GROUP BY优化
- group by 使用索引的原则几乎跟 order by 一致,group by 即使没有过滤条件用到索引,也可以直接使用索引;
- group by 先排序再分组,遵照索引建的最佳左前缀法则;
- 当无法使用索引列,增大
max_length_for_sort_data
和sort_buffer_size
参数的设置; - where 效率高于 having,能写在 where 限定的条件就不要写在 having 中了;
- 减少使用 order by,和业务沟通能不排序就不排序,或将排序放到程序端去做;
- Order by、group by、distinct 这些语句较为耗费 CPU,数据库的 CPU 资源是极其宝贵的;
- 包含了 order by、group by、distinct 这些查询的语句,where 条件过滤出来的结果集请保持在 1000 行以内,否则 SQL 会很慢。
7. 优化分页查询
一般分页查询时,通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题就是limit 2000000, 10,此时需要 MySQL 排序前 2000010 记录,仅仅返回 2000000 - 2000010 的记录,其他记录丢弃,查询排序的代价非常大。
EXPLAIN SELECT * FROM student LIMIT 2088800, 10;
优化思路一
在索引上完成排序分页操作,最后根据主键关联回原表查询所需要的其他列内容。
EXPLAIN SELECT * FROM student t, ( SELECT id FROM student ORDER BY id LIMIT 2000000, 10) a WHERE t.id = a.id;
优化思路二(几乎没法用)
该方案适用于主键自增的表,可以把Limit查询转换成某个位置的查询。
EXPLAIN SELECT * FROM student WHERE id > 2080880 LIMIT 10;
不靠谱,生产中id可能会删除,查询的条件也不可能这么简单。
8. 优先考虑覆盖索引
8.1 什么是覆盖索引?
理解方式一:索引是高效找到行的一个方法,但是一般数据库也能使用索引找到一个列的数据,因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据;当能通过读取索引就可以得到想要的数据,那就不需要读取行了。一个索引包含了满足查询结果的数据就叫做覆盖索引。
理解方式二:非聚簇复合索引的一种形式,它包括在查询里的 SELECT、JOIN 和 WHERE 子句用到的所有列(即建索引的字段正好是覆盖查询条件中所涉及的字段)。
简单说就是, 索引列 + 主键
包含 SELECT 到 FROM 之间查询的列
。
举例一:覆盖索引长什么样子。 索引列 + 主键
# 删除之前的索引
DROP INDEX idx_age_stuno ON student;
CREATE INDEX idx_age_name ON student (age, name);
EXPLAIN SELECT * FROM student WHERE age <> 20;
EXPLAIN SELECT id, age, name FROM student WHERE age <> 28;
上述都使用到了声明的索引,下面的情况则不然,在查询列中多了一列 classid,显示未使用到索引:
EXPLAIN SELECT id, age, name, classid FROM student WHERE age <> 28;
举例二:
EXPLAIN SELECT *FROM student WHERE name LIKE '%abc';
CREATE INDEX idx_age_name ON student (age, name);
EXPLAIN SELECT id, age, name FROM student WHERE name LIKE '%abc ';
# 索引覆盖失效
EXPLAIN SELECT id, age, name, classid FROM student WHERE name LIKE '%abc ';
查询多了classid,结果是未使用到索引
之前有说过,不等于与左模糊会导致索引失效。但是这里为什么又用上了呢?原因是优化器发现,数据已经都在索引了。直接遍历索引就可以返回数据。。而遍历索引,肯定是比遍历全表数据量少的。这样IO就可以更少。
一切都是成本的考量。
8.2 覆盖索引的利弊
好处:
1. 避免Innodb表进行索引的二次查询(回表)
Innodb是以聚集索引的顺序来存储的,对于Innodb来说,二级索引在叶子节点中所保存的是行的主键信息,如果是用二级索引查询数据,在查找到相应的键值后,还需通过主键进行二次查询才能获取我们真实所需要的数据。
在覆盖索引中,二级索引的键值中可以获取所要的数据,避免了对主键的二次查询,减少了IO操作
,提升了查询效率。
2. 可以把随机IO变成顺序IO加快查询效率
由于覆盖索引是按键值的顺序存储的,对于I0密集型的范围查找来说,对比随机从磁盘读取每一行的数据IO要少的多,因此利用覆盖索引在访问时也可以把磁盘的随机读取的IO
转变成索引查找的顺序IO
。
3. 数据在索引里面数据量少更紧凑
索引肯定是比原来的数据,数据量少。这样就可以减少IO.
由于覆盖索引可以减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段。
弊端:
索引字段的维护
总是有代价的。因此,在建立冗余索引来支持覆盖索引时就需要权衡考虑了。这是业务DBA,或者称为业务数据架构师的工作。
9. 如何给字符串添加索引
有一张教师表,表定义如下:
create table teacher
(
ID bigint unsigned primary key,
email varchar(64),
...
) engine = innodb;
讲师要使用邮箱登录,所以业务代码中一定会出现类似于这样的语句:
mysql> select col1, col2 from teacher where email='xxx';
如果 email 这个字段上没有索引,那么这个语句就只能做 全表扫描
。
9.1 前缀索引
MySQL是支持前缀索引的。默认地,如果你创建索引的语句不指定前缀长度,那么索引就会包含整个字符串
mysql> alter table teacher add index index1(email);
# 或
mysql> alter table teacher add index index2(email(6))
这两种不同的定义在数据结构和存储上有什么区别呢?下图就是这两个索引的示意图
以及
如果使用的是index1(即email整个字符串的索引结构),执行顺序是这样的:
- 从index1索引树找到满足索引值是’ zhangssxyz@xxx.com ’的这条记录,取得ID2的值;
- 到主键上查到主键值是ID2的行,判断email的值是正确的,将这行记录加入结果集;
- 取index1索引树上刚刚查到的位置的下一条记录,发现已经不满足email=’ zhangssxyz@xxx.com ’的条件了,循环结束。
这个过程中,只需要回主键索引取一次数据,所以系统认为只扫描了一行。
如果使用的是index2(即email(6)索引结构),执行顺序是这样的:
- 从index2索引树找到满足索引值是’zhangs’的记录,找到的第一个是ID1;
- 到主键上查到主键值是ID1的行,判断出email的值不是’ zhangssxyz@xxx.com ’,这行记录丢弃;
- 取index2上刚刚查到的位置的下一条记录,发现仍然是’zhangs’,取出ID2,再到ID索引上取整行然后判断,这次值对了,将这行记录加入结果集;
- 重复上一步,直到在idxe2上取到的值不是’zhangs’时,循环结束。
也就是说使用前缀索引,定义好长度,就可以做到既节省空间,又不用额外增加太多的查询成本。前面已经讲过区分度,区分度越高越好。因为区分度越高,意味着重复的键值越少。
9.2 前缀索引对覆盖索引的影响
结论:
使用前缀索引就用不上覆盖索引对查询性能的优化了,这也是你在选择是否使用前缀索引时需要考虑的一个因素。
10. 索引下推
10.1 使用前后对比
Index Condition Pushdown(ICP) 是 MySQL 5.6 中新特性,是一种在存储引擎层使用索引过滤数据的一种优化方式。
- 如果没有 ICP,存储引擎会遍历索引以定位基表中的行,并将它们返回给 MySQL 服务器,由 MySQL 服务器评估
WHERE
后面的条件是否保留行。 - 启用 ICP 后,如果部分
WHERE
条件可以仅使用索引中的列进行筛选,则 MySQL 服务器会把这部分WHERE
条件放到存储引擎筛选。然后,存储引擎通过使用索引条目来筛选数据,并且只有在满足这一条件时才从表中读取行。- 好处: ICP 可以减少存储引擎必须访问基表的次数和 MySQL 服务器必须访问存储引擎的次数。
- 但是,ICP 的
加速效果
取决于在存储引擎内通过ICP筛选
掉的数据的比例。
例子:
key1 有索引
这里条件 like ‘%a’ 其实可以在索引里面,算出来哪些符合条件。过滤出符合条件的,再回表。这样回表的数据可以减少很多。还有一个好处,没有索引下推,就需要把数据都回表查出来,这些数据可能在不同的页当中,又会产生 IO
条件下推,下推到下一个条件符不符合。
10.1 使用前后的扫描过程
在不使用ICP索引扫描的过程:
storage层:只将满足index key条件的索引记录对应的整行记录取出,返回给server层
server 层:对返回的数据,使用后面的where条件过滤,直至返回最后一行。
使用ICP扫描的过程:
storage层:
首先将index key条件满足的索引记录区间确定,然后在索引上使用index filter进行过滤。将满足的index filter条件的索引记录才去回表取出整行记录返回server层。不满足index filter条件的索引记录丢弃,不回表、也不会返回server层。
server 层:
对返回的数据,使用table filter条件做最后的过滤。
使用前后的成本差别
使用前,存储层多返回了需要被index filter过滤掉的整行记录
使用ICP后,直接就去掉了不满足index filter条件的记录,省去了他们回表和传递到server层的成本。
ICP的 加速效果 取决于在存储引擎内通过 ICP筛选 掉的数据的比例。
10.2 ICP的开启/关闭
-
默认情况下启用索引条件下推。可以通过设置系统变量
optimizer_switch
控制:index_condition_pushdown
# 打开索引下推 SET optimizer_switch = 'index_condition_pushdown = off'; # 关闭索引下推 SET optimizer_switch = 'index_condition_pushdown = on';
-
当使用索引条件下推时,
EXPLAIN
语句输出结果中 Extra 列内容显示为Using index condition
。
10.3 ICP使用案例
# 建表
CREATE TABLE people
(
id INT NOT NULL AUTO_INCREMENT,
zipcode VARCHAR(20) COLLATE utf8_bin DEFAULT NULL,
firstname varchar(20) COLLATE utf8_bin DEFAULT NULL,
lastname varchar(20) COLLATE utf8_bin DEFAULT NULL,
address varchar(50) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (id),
KEY zip_last_first (zipcode, lastname, firstname)
) ENGINE = InnoDB
AUTO_INCREMENT = 5
DEFAULT CHARSET = utf8mb3
COLLATE = utf8_bin;
# 插入数据
INSERT INTO people
VALUES ('1', '000001', '三', '张', '北京市'),
('2', '000002', '四', '李', '南京市'),
('3', '000003', '五', '王', '上海市'),
('4', '000001', '六', '赵', '天津市');
为该表定义联合索引 zip_last_first (zipcode,lastname,firstname)。如果我们知道了一个人的邮编,但是不确定这个人的姓氏,我们可以进行如下检索:
SELECT *
FROM people
WHERE zipcode = '000001'
AND lastname LIKE '%张%'
AND address LIKE '%北京市%';
执行查看SQL的查询计划,Extra中显示了Using index condition
,这表示使用了索引下推。另外,Usingwhere
表示条件中包含需要过滤的非索引列的数据,即address LIKE '%北京市%'这个条件并不是索引列,需要在服务端过滤掉。
10.4 开启和关闭ICP的性能对比
创建存储过程,主要目的就是插入很多 000001 的数据,这样查询的时候为了在存储引擎层做过滤,减少 IO,也为了减少缓冲池(缓存数据页,没有 IO)的作用。
DELIMITER //
CREATE PROCEDURE insert_people(max_num INT)
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTo people (zipcode, firstname, lastname, address) VALUES ('000001', '六', '赵', '天津市');
UNTIL i = max_num
END REPEAT;
COMMIT;
END //
DELIMITER ;
# 调用存储过程
CALL insert_people(1000000);
首先打开profiling
。
# 查看
mysql> show variables like 'profiling%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| profiling | OFF |
| profiling_history_size | 15 |
+------------------------+-------+
set profiling = 1;
执行 SQL 语句,此时默认打开索引下推。
SELECT *
FROM people
WHERE zipcode = '000001'
AND lastname LIKE '%张%';
再次执行 SQL 语句,不使用索引下推
SELECT /*+ no_icp (people) */ * FROM people WHERE zipcode='000001' AND lastname LIKE '%张%';
查看当前会话所产生的所有profiles
show profiles\G
结果如下。
多次测试效率对比来看,使用ICP优化的查询效率会好一些。这里建议多存储一些数据效果更明显。
10.5 ICP的使用条件
-
如果表访问的类型为 range、ref、eq_ref和ref_or_null可以使用ICP
-
ICP可以用于
InnoDB
和MyISAM
表,包括分区表InnoDB
和MyISAM
表 -
对于
InnoDB
表,ICP
仅用于二级索引。ICP的目标是减少全行读取次数,从而减少I/o操作。 -
当SQL使用覆盖索引时,不支持ICP。因为这种情况下使用ICP不会减少I/O。
索引覆盖不能使用,一个原因是,索引覆盖,不需要回表。。ICP作用是减小回表,ICP需要回表
-
相关子查询的条件不能使用ICP
11. 普通索引 vs 唯一索引
从性能的角度考虑,你选择唯一索引还是普通索引呢?选择的依据是什么呢?
假设,我们有一个主键列为ID的表,表中有字段k,并且在k上有索引,假设字段 k 上的值都不重复。
这个表的建表语句是
mysql> create table test(
id int primary key,
k int not null,
name varchar(16),
index (k)
)engine=InnoDB;
表中R1~R5的(ID,k)值分别为(100,1)、(200,2)、(300,3)、(500,5)和(600,6)
11.1 查询过程
假设,执行查询的语句是 select id from test where k=5。
- 对于普通索引来说,查找到满足条件的第一个记录(5,500)后,需要查找下一个记录,直到碰到第一个不满足k=5条件的记录。
- 对于唯一索引来说,由于索引定义了唯一性,查找到第一个满足条件的记录后,就会停止继续检索。
那么,这个不同带来的性能差距会有多少呢?答案是, 微乎其微
。
11.2 更新过程
为了说明普通索引和唯一索引对更新语句性能的影响这个问题,介绍一下change buffer。
当需要更新一个数据页时,如果数据页在内存中就直接更新,而如果这个数据页还没有在内存中的话,在不影响数据一致性的前提下, InooDB会将这些更新操作缓存在change buffer中
,这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候,将数据页读入内存,然后执行change buffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。
将change buffer中的操作应用到原数据页,得到最新结果的过程称为 merge 。除了 访问这个数据页 会触发merge外,系统有 后台线程会定期
merge。在 数据库正常关闭(shutdown)
的过程中,也会执行merge操作。
如果能够将更新操作先记录在change buffer, 减少读磁盘
,语句的执行速度会得到明显的提升。而且,数据读入内存是需要占用 buffer pool 的,所以这种方式还能够 避免占用内存
,提高内存利用率。唯一索引的更新就不能使用change buffer
,实际上也只有普通索引可以使用。
如果要在这张表中插入一个新记录(4,400)的话,InnoDB的处理流程是怎样的?
11.3 change buffer的使用场景
-
普通索引和唯一索引应该怎么选择?其实,这两类索引在查询能力上是没差别的,主要考虑的是对 更新性能 的影响。所以,建议你 尽量选择普通索引 。
-
在实际使用中会发现, 普通索引 和 change buffer 的配合使用,对于 数据量大 的表的更新优化还是很明显的。
-
如果所有的更新后面,都马上 伴随着对这个记录的查询 ,那么你应该 关闭change buffer 。而在其他情况下,change buffer都能提升更新性能。
-
由于唯一索引用不上change buffer的优化机制,因此如果 业务可以接受 ,从性能角度出发建议优先考虑非唯一索引。但是如果"业务可能无法确保"的情况下,怎么处理呢?
- 首先, 业务正确性优先 。我们的前提是“业务代码已经保证不会写入重复数据”的情况下,讨论性能问题。如果业务不能保证,或者业务就是要求数据库来做约束,那么没得选,必须创建唯一索引。
- 这种情况下,本节的意义在于,如果碰上了大量插入数据慢、内存命中率低的时候,给你多提供一个排查思路。
然后,在一些“ 归档库 ”的场景,你是可以考虑使用唯一索引的。比如,线上数据只需要保留半年,然后历史数据保存在归档库。这时候,归档数据已经是确保没有唯一键冲突了。要提高归档效率,可以考虑把表里面的唯一索引改成普通索引。
12. 其它查询优化策略
12.1 EXISTS 和 IN 的区分
问题:
不太理解哪种情况下应该使用 EXISTS,哪种情况应该用 IN。选择的标准是看能否使用表的索引吗?
回答:
索引是个前提,其实选择与否还是要看表的大小。你可以将选择的标准理解为小表驱动大表
。在这种方式下效率是最高的。
比如下面这样:
SELECT * FROM A WHERE cc IN (SELECT cc FROM B)
SELECT * FROM A WHERE EXISTS (SELECT cc FROM B WHERE B.cc = A.cc)
- 当 A 小于 B 时,用 EXISTS。因为 EXISTS 的实现,相当于外表循环,实现的逻辑类似于:
for i in A
for j in B
if j.cc == i.cc then ...
- 当 B 小于 A 时,用 IN,因为实现的逻辑类似于:
for i in B
for j in A
if j.cc == i.cc then ...
- 哪个表小就用哪个表来驱动,A 表小就用 EXISTS,B 表小就用 IN。
12.2 COUNT(*) 与 COUNT(具体字段) 效率
问:在MySQL中统计数据表的行数,可以使用三种方式:SELECT COUNT(*)
、SELECT COUNT(1)
和 SELECT COUNT(具体字段)
,使用这三者之间的查询效率是怎样的?
答:
前提:如果你要统计的是某个字段的非空数据行数,则另当别论,毕竟比较执行效率的前提是结果一样才可以。
环节1:COUNT(*)
和 COUNT(1)
都是对所有结果进行 COUNT
, COUNT(*)
和 COUNT(1)
本质上并没有区别(二者执行时间可能略有差别,不过你还是可以把它俩的执行效率看成是相等的)。如果有 WHERE 子句,则是对所有符合筛选条件的数据行进行统计;如果没有 WHERE 子句,则是对数据表的数据行数进行统计。
环节2: 如果是 MyISAM 存储引擎,统计数据表的行数只需要 o(1)
的复杂度,这是因为每张 MyISAM 的数据表都有一个 meta 信息存储了 row_count
值,而一致性则由表级锁来保证。
如果是 InnoDB 存储引擎,因为 InnoDB 支持事务,采用行级锁和 MVCC 机制,所以无法像 MyISAM 一样,维护一个 row_count 变量,因此需要采用 扫描全表
,是 o(n)
复杂度,进行 循环+计数
的方式来完成统计。
环节(重点)3:在 InnoDB 引擎中,如果采用 COUNT(具体字段)
来统计数据行数,要尽量采用二级索引。因为主键采用的索引是聚簇索引,聚簇索引包含的信息多,明显会大于二级索引(非聚簇索引)。对于 COUNT(*)
和 COUNT(1)
来说,它们不需要查找具体的行,只是统计行数,系统会自动
采用占用空间更小的二级索引来进行统计。
如果有多个二级索引,会使用 key_len
小的二级索引进行扫描。当没有二级索引的时候,才会采用主键索引来进行统计。
12.3 关于SELECT(*)
在表查询中,建议明确字段,不要使用 * 作为查询的字段列表,推荐使用 SELECT <字段列表>
查询。
原因:
-
MySQL 在解析的过程中,会通过
查询数据字典
将 “*” 按序转换成所有列名,这会大大的耗费资源和时间。 -
无法使用
覆盖索引
12.4 LIMIT 1 对优化的影响
针对的是会扫描全表的 SQL 语句,如果你可以确定结果集只有一条,那么加上 LIMIT 1 的时候,当找到一条结果的时候就不会继续扫描了,这样会加快查询速度。
如果数据表已经对字段建立了唯一索引,那么可以通过索引进行查询,不会全表扫描的话,就不需要加上 LIMIT 1
了。
12.5 多使用COMMIT
只要有可能,在程序中尽量多使用 COMMIT,这样程序的性能得到提高,需求也会因为 COMMIT 所释放的资源而减少。
COMMIT 所释放的资源:
- 回滚段上用于恢复数据的信息
- 被程序语句获得的锁
- redo / undo log buffer 中的空间
- 管理上述 3 种资源中的内部花费
13. 淘宝数据库,主键如何设计的?
聊一个实际问题:淘宝的数据库,主键是如何设计的?
某些错的离谱的答案还在网上年复一年的流传着,甚至还成为了所谓的 MySQL 军规。其中,一个最明显的错误就是关于 MySQL 的主键设计。
大部分人的回答如此自信:用8字节的 BIGINT 做主键,而不要用 INT。 错
!
这样的回答,只站在了数据库这一层,而没有 从业务的角度
思考主键。主键就是一个 自增ID 吗?用自增做主键,架构设计上可能 连及格都拿不到
。
13.1 自增ID的问题
自增ID 做主键,简单易懂,几乎所有数据库都支持自增类型,只是实现上各自有所不同而已。自增ID 除了简单,其他都是缺点,总体来看存在以下几方面的问题:
-
可靠性不高
存在 自增ID 回溯的问题,这个问题直到最新版本的 MySQL 8.0 才修复。
-
安全性不高
对外暴露的接口可以非常容易猜测对应的信息。比如:/User/1/ 这样的接口,可以非常容易猜测 用户ID 的值为多少,总用户数量有多少,也可以非常容易地通过接口进行数据的爬取。
-
性能差
自增ID 的性能较差,需要在数据库服务器端生成。
-
交互多
业务还需要额外执行一次类似 last_insert_id() 的函数才能知道刚才插入的自增值,这需要多一次的网络交互。在海量并发的系统中,多1条SQL,就多一次性能上的开销。
-
局部唯一性
最重要的一点,自增ID 是局部唯一,只在当前数据库实例中唯一,而不是全局唯一,在任意服务器间都是唯一的。对于目前分布式系统来说,这简直就是噩梦。
13.2 业务字段做主键
为了能够唯一地标识一个会员的信息,需要为 会员信息表
设置一个主键。那么,怎么为这个表设置主键,才能达到我们理想的目标呢? 这里我们考虑业务字段做主键。
表数据如下:
在这个表里,哪个字段比较合适呢?
-
选择卡号(cardno)
会员卡号(cardno)看起来比较合适,因为会员卡号不能为空,而且有唯一性,可以用来 标识一条会员记录。
mysql> CREATE TABLE demo.membermaster
-> (
-> cardno CHAR(8) PRIMARY KEY, -- 会员卡号为主键
-> membername TEXT,
-> memberphone TEXT,
-> memberpid TEXT,
-> memberaddress TEXT,
-> sex TEXT,
-> birthday DATETIME
-> );
Query OK, 0 rows affected (0.06 sec)
不同的会员卡号对应不同的会员,字段“cardno”唯一地标识某一个会员。如果都是这样,会员卡号与会员一一对应,系统是可以正常运行的。
但实际情况是, 会员卡号可能存在重复使用
的情况。比如,张三因为工作变动搬离了原来的地址,不再到商家的门店消费了 (退还了会员卡),于是张三就不再是这个商家门店的会员了。但是,商家不想让这个会 员卡空着,就把卡号是“10000001”的会员卡发给了王五。
从系统设计的角度看,这个变化只是修改了会员信息表中的卡号是“10000001”这个会员 信息,并不会影响到数据一致性。也就是说,修改会员卡号是“10000001”的会员信息, 系统的各个模块,都会获取到修改后的会员信息,不会出现“有的模块获取到修改之前的会员信息,有的模块获取到修改后的会员信息,而导致系统内部数据不一致”的情况。因此,从 信息系统层面
上看是没问题的。
但是从使用 系统的业务层面
来看,就有很大的问题 了,会对商家造成影响。
比如,我们有一个销售流水表(trans),记录了所有的销售流水明细。2020 年 12 月 01 日,张三在门店购买了一本书,消费了 89 元。那么,系统中就有了张三买书的流水记录,如下所示:
接着,我们查询一下 2020 年 12 月 01 日的会员销售记录:
mysql> SELECT b.membername,c.goodsname,a.quantity,a.salesvalue,a.transdate
-> FROM demo.trans AS a
-> JOIN demo.membermaster AS b
-> JOIN demo.goodsmaster AS c
-> ON (a.cardno = b.cardno AND a.itemnumber=c.itemnumber);
+------------+-----------+----------+------------+---------------------+
| membername | goodsname | quantity | salesvalue | transdate |
+------------+-----------+----------+------------+---------------------+
| 张三 | 书 | 1.000 | 89.00 | 2020-12-01 00:00:00 |
+------------+-----------+----------+------------+---------------------+
1 row in set (0.00 sec)
如果会员卡“10000001”又发给了王五,我们会更改会员信息表。导致查询时:
mysql> SELECT b.membername,c.goodsname,a.quantity,a.salesvalue,a.transdate
-> FROM demo.trans AS a
-> JOIN demo.membermaster AS b
-> JOIN demo.goodsmaster AS c
-> ON (a.cardno = b.cardno AND a.itemnumber=c.itemnumber);
+------------+-----------+----------+------------+---------------------+
| membername | goodsname | quantity | salesvalue | transdate |
+------------+-----------+----------+------------+---------------------+
| 王五 | 书 | 1.000 | 89.00 | 2020-12-01 00:00:00 |
+------------+-----------+----------+------------+---------------------+
1 row in set (0.01 sec)
这次得到的结果是:王五在 2020 年 12 月 01 日,买了一本书,消费 89 元。显然是错误的!结论:千万不能把会员卡号当做主键。
- 选择 会员电话 或 身份证号
会员电话可以做主键吗?不行的。在实际操作中,手机号也存在 被运营商收回
,重新发给别人用的情况。
那身份证号行不行呢?好像可以。因为身份证决不会重复,身份证号与一个人存在一一对 应的关系。可问题是,身份证号属于 个人隐私
,顾客不一定愿意给你。要是强制要求会员必须登记身份证号,会把很多客人赶跑的。其实,客户电话也有这个问题,这也是我们在设计会员信息表的时候,允许身份证号和电话都为空的原因。
所以,建议尽量不要用跟业务有关的字段做主键。毕竟,作为项目设计的技术人员,我们谁也无法预测
在项目的整个生命周期中,哪个业务字段会因为项目的业务需求而有重复,或者重用之类的情况出现。
经验:
刚开始使用 MySQL 时,很多人都很容易犯的错误是喜欢用业务字段做主键,想当然地认为了解业务需求,但实际情况往往出乎意料,而更改主键设置的成本非常高。
13.3 淘宝的主键设计
在淘宝的电商业务中,订单服务是一个核心业务。请问, 订单表的主键
淘宝是如何设计的呢?是自增ID吗?
打开淘宝,看一下订单信息:
从上图可以发现,订单号不是自增ID!我们详细看下上述4个订单号:
1550672064762308113
1481195847180308113
1431156171142308113
1431146631521308113
订单号是19位的长度,且订单的最后5位都是一样的,都是08113。且订单号的前面14位部分是单调递增的。
大胆猜测,淘宝的订单ID设计应该是:
订单ID = 时间 + 去重字段 + 用户ID后6位尾号
这样的设计能做到全局唯一,且对分布式系统查询及其友好。
13.4 推荐的主键设计
非核心业务
:对应表的主键自增ID,如告警、日志、监控等信息。
核心业务
:主键设计至少应该是全局唯一且是单调递增。全局唯一保证在各系统之间都是唯一的,单调递增是希望插入时不影响数据库性能。
这里推荐最简单的一种主键设计:UUID。
UUID 的特点:
全局唯一,占用36字节,数据无序,插入性能差。
认识 UUID:
- 为什么 UUID 是全局唯一的?
- 为什么 UUID 占用36个字节?
- 为什么 UUID 是无序的?
MySQL 数据库的 UUID 组成如下所示:
UUID = 时间 + UUID版本(16字节)- 时钟序列(4字节) - MAC地址(12字节)
我们以 UUID值 e0ea12d4-6473-11eb-943c-00155dbaa39d 举例 :
1. 为什么 UUID 是全局唯一的?
在 UUID 中时间部分占用60位,存储的类似 TIMESTAMP 的时间戳,但表示的是从1582-10-15 00:00:00.00到现在的 100ns 的计数。可以看到 UUID 存储的时间精度比 TIMESTAMPE 更高,时间维度发生重复的概率降低到 1/100ns。
时钟序列是为了避免时钟被回拨导致产生时间重复的可能性。MAC 地址用于全局唯一。
2. 为什么 UUID 占用36个字节?
UUID 根据字符串进行存储,设计时还带有无用"-"字符串,因此总共需要36个字节。
3. 为什么 UUID 是随机无序的呢?
因为 UUID 的设计中,将时间低位放在最前面,而这部分的数据是一直在变化的,并且是无序。
4. 改造UUID
若将时间高低位互换,则时间就是单调递增的了,也就变得单调递增了。MySQL 8.0 可以更换时间低位和时间高位的存储方式,这样 UUID 就是有序的 UUID 了。
MySQL 8.0 还解决了 UUID 存在的空间占用的问题,除去了 UUID 字符串中无意义的"-"字符串,并且将字符串用二进制类型保存,这样存储空间降低为了16字节。
可以通过 MySQL 8.0 提供的 uuid_to_bin 函数实现上述功能,同样的,MySQL 也提供了 bin_to_uuid 函数进行转化:
SET @uuid = UUID();
SELECT @uuid, uuid_to_bin(@uuid), uuid_to_bin(@uuid, TRUE);
# uuid_to_bin(@uuid) 转成16进制存储
# uuid_to_bin(@uuid,TRUE); 修改成先高位 中位 地位,就可以保证uuid地政了
通过函数 uuid_to_bin(@uuid, TRUE) 将 UUID 转化为有序 UUID 了。全局唯一 + 单调递增,这不就是我们想要的主键!
4、有序UUID性能测试
16字节的有序 UUID,相比之前8字节的自增 ID,性能和存储空间对比究竟如何呢?
我们来做一个测试,插入1亿条数据,每条数据占用500字节,含有3个二级索引,最终的结果如下所示:
从上图可以看到插入1亿条数据有序 UUID 是最快的,而且在实际业务使用中有序 UUID 在 业务端就可以生成 。还可以进一步减少 SQL 的交互次数。
另外,虽然有序 UUID 相比自增 ID 多了8个字节,但实际只增大了 3G 的存储空间,还可以接受。
在当今的互联网环境中,非常不推荐自增 ID 作为主键的数据库设计。更推荐类似有序 UUID 的全局唯一的实现。
另外在真实的业务系统中,主键还可以加入业务和系统属性,如用户的尾号,机房的信息等。这样的主键设计就更为考验架构师的水平了。
如果不是MySQL 8.0 肿么办?
手动赋值字段做主键!
比如,设计各个分店的会员表的主键,因为如果每台机器各自产生的数据需要合并,就可能会出现主键重复的问题。
可以在总部 MySQL 数据库中,有一个管理信息表,在这个表中添加一个字段,专门用来记录当前会员编号的最大值。
门店在添加会员的时候,先到总部 MySQL 数据库中获取这个最大值,在这个基础上加 1,然后用这个值作为新会员的“id”,同时,更新总部 MySQL 数据库管理信息表中的当 前会员编号的最大值。
这样一来,各个门店添加会员的时候,都对同一个总部 MySQL 数据库中的数据表字段进 行操作,就解决了各门店添加会员时会员编号冲突的问题。