（全栈须知）3.百万级SQL语句优化(一) 之参考官方一索引

参考文档

官方参考文档： [优化SELECT语句]

其他：
《同一个SQL语句，为啥性能差异咋就这么大呢？（1分钟系列）》《如何利用工具，迅猛定位低效SQL？ | 1分钟系列》
《关于MySQL，你未必知道的！》
《58到家MySQL军规升级版》
《InnoDB并发如此高，原因竟然在这？》
《过完年跳槽，要考虑哪些要素？》

1、优化需要了解的知识

在phpmyadmin查看上篇建表结构，索引情况：

操作	键名	类型	唯一	紧凑	字段	基数	排序规则	空	注释
编辑 删除	PRIMARY	BTREE	是	否	id	0	A	否
编辑 删除	index_user_id	BTREE	否	否	user_id	0	A	否	用户ID哈希索引

a.索引类型

---

PRIMARY、INDEX（普通索引）、UNIQUE（唯一性索引）、FULLTEXT（全文索引）;
外键索引、合并索引/组合索引

b.索引方法

---

BTREE(默认)、HASH
参考《Mysql目前主要的几种索引类型》

c.创建索引的时机

---

在SQL中存在 <，<=，=，>，>=，BETWEEN，IN，以及LIKE 时才会使用索引。
补充：存在 ORDER BY 列时添加索引。

2、SELECT语句优化

MySQL一次查询只能使用一个索引，如果要对多个字段使用索引，建立复合索引。

下面测试数据库表 vote_record ：MySQL8.0.12 innoDB 100w条，主机环境：AMD 3700x+4G内存 的deepin虚拟机。命令行，每条查询语句执行2~5遍，间隔1s+。

a.WHERE子句优化

---

• 使用索引树（默认BTREE）
• 删除不必要的括号 --> 恒定折叠 --> 恒定条件去除
• HAVINGWHERE如果不使用GROUP BY或聚合函数（COUNT()， MIN()等等），则合并

-- 如果存在ORDER BY子句和不同的GROUP BY子句，或者如果 ORDER BY或者GROUP BY 包含连接队列中第一个表以外的表中的列，则会创建临时表。

-- 如果使用SQL_SMALL_RESULT 修饰符，MySQL使用内存临时表。

一些非常快的查询示例：

SELECT COUNT(*) FROM vote_record; 
-- 无索引(0.11 sec) + (0.09 sec)
SELECT MIN(vote_num),MAX(group_id) FROM vote_record; 
--无索引(0.66 sec) + (0.00 sec)
SELECT MAX(group_id) FROM vote_record WHERE vote_num=123; 
--无索引(0.15 sec) + (0.00 sec)
SELECT * FROM vote_record ORDER BY vote_num,group_id LIMIT 10; 
--无索引(0.30 sec) + (0.29 sec)
SELECT * FROM vote_record ORDER BY vote_num DESC, group_id DESC LIMIT 10; 
--无索引(0.32 sec) + (0.30 sec)

-----------------------------
alter table vote_record add INDEX index_vote_num (`vote_num`);
alter table vote_record add INDEX index_group_id (`group_id`);

数字-添加索引1：对于聚合查询(MAX、MIN)正优化；COUNT(*)有提高；对order by变化不明显。

SELECT COUNT(*) FROM vote_record WHERE vote_num=345 AND group_id=2;
--(0.00 sec) + 无索引(0.15 sec)
SELECT group_id FROM vote_record GROUP BY vote_num;
--(1.64 sec) + 无索引(0.27 sec)
SELECT vote_num FROM vote_record GROUP BY group_id;
--(1.27 sec) + 无索引(0.27 sec)

-----------------------------
alter table vote_record drop INDEX index_vote_num;
alter table vote_record drop INDEX index_group_id;

数字-添加索引2：对group by负优化。

SELECT * FROM vote_record ORDER BY vote_num,group_id;
--(0.89 sec) + 无索引(0.90 sec)
SELECT * FROM vote_record ORDER BY vote_num DESC, group_id DESC;
--(0.90 sec) + 无索引(0.91 sec)

数字-添加索引3：对order by变化不明显。

b、范围优化

---

• 单部分索引

删除不能用于范围扫语法块 --> 折叠条件 --> 合并简化

• 多部分索引的范围访问方法

单区间查询方法模型：
(x1,y1,z1) < (key_part1,key_part2,key_part3) < (x2,y2,z2)

SELECT * FROM vote_record WHERE (vote_num < 600 AND group_id IN (1,3) ); 
--无索引(0.23 sec) + (0.22 sec)
SELECT vote_num, group_id FROM vote_record WHERE vote_num> 600;
--无索引(0.28 sec) + (0.27 sec)

• 行构造函数表达式的范围优化

SELECT * FROM vote_record WHERE ( vote_num , group_id ) IN (( 236, 1 ), ( 668, 2 ));
--(0.01 sec)

c、索引合并优化

---

--创建普通索引 CREATE INDEX index_name ON table_name(col_name);
--创建唯一索引 CREATE UNIQUE INDEX index_name ON table_name(col_name);
--创建普通组合索引 CREATE INDEX index_name ON table_name(col_name_1,col_name_2);
--创建唯一组合索引 CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2);

索引：会降低 update 操作的速速，占用磁盘空间使各种操作变慢。

• 合并索引：同时使用多个索引列

(x AND y) OR z => (x OR z) AND (y OR z)
(x OR y) AND z => (x AND z) OR (y AND z)

--合并交叉口访问算法
SELECT * FROM vote_record WHERE vote_num = 111 AND group_id = 2 AND status = 2;
--合并并集访问算法
SELECT * FROM vote_record WHERE vote_num = 234 OR group_id = 2 OR status = 1;
--合并排序联合访问算法
SELECT * FROM vote_record WHERE (vote_num > 100 OR group_id = 3) AND status = 1;

• 组合索引

alter table vote_record drop index `index_vote_num`; alter table vote_record drop index `index_group_id`; 
--性能相当
alter table vote_record add index `index_vote_num_group_id`(`vote_num`,`group_id`);
SELECT * FROM vote_record ORDER BY vote_num,group_id limit 3;
--(0.00 sec)
SELECT * FROM vote_record ORDER BY vote_num desc,group_id limit 3;
--(0.33 sec)
明显的，组合索引元素的 select查询 排序[ ASC, DESC ]要单调一致。

--测试添加，会浪费查询运算性能

ALTER TABLE `vote_record` add `order_use_vir` VARCHAR(32) AS (concat(`vote_num`,`group_id`)) VIRTUAL COMMENT 'ORDER BY vote_num DESC,group_id limit使用';

选择性最高的列放在索引最前面；多个order时尽量创建与 orderby 同顺序的组合索引；

--10 rows in set (0.55 sec)
explain SELECT * FROM vote_record ORDER BY vote_num DESC,group_id limit 880000,10\G; --Extra: Using filesort 

--多个order正逆序随分页

---

对于这种排序优化，提示使用了 filesort排序（比较慢） 索引：能进入缩小范围的尽量使用where限定和范围优化。只能 通过limit以外的逻辑方法实现分页，如：逻辑上在修改时 把 id-vote_num-group_id 放到redis表中存放（反向的vote_num + group_id + id），范围截取得到所需 id组 ，再返回mysql查询结果：select * from vote_record WHERE id in (3,2,5,56,1) order by field(id, 3,2,5,56,1);。

d、LEFT JOIN顺序问题

---

LEFT JOIN 以左表为准，右边缺少的字段忽略0、共有重复的字段备份+、多出的字段添加为null。

SELECT left_tbl.*
  FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id
  WHERE right_tbl.id IS NULL;

a LEFT JOIN b USING (c1, c2, c3)

• INNER JOIN和, （逗号）在没有连接条件的情况下在语义上是等价的：两者在指定的表之间产生笛卡尔积（即，第一个表中的每一行都连接到第二个表中的每一行）。逗号运算符的优先级低于的INNER JOIN，CROSS JOIN，LEFT JOIN，等等。

SELECT * FROM t1 LEFT JOIN (t2, t3, t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

表t1包含行 (1)，(2)
表t2包含行 (1,101)
表t3包含行 (101)

数据库初始表：

t1表			t2表			t3表
a			a	b		a
1			1	101		101
2

• 右连接 A LEFT JOIN (B LEFT JOIN C)

SELECT t1.a as t1a, t2.a as t2a, t2.b as t2b, t3.b as t3b FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL) ON t1.a=t2.a

t1a	t2a	t2b	t3b
1	1	101	101
2	NULL	NULL	NULL

• 左连接 (A LEFT JOIN B) LEFT JOIN C

SELECT t1.a as t1a, t2.a as t2a, t2.b as t2b, t3.b as t3b FROM (t1 LEFT JOIN t2 ON t1.a=t2.a) LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL

t1a	t2a	t2b	t3b
1	1	101	101
2	NULL	NULL	101

等于

SELECT * FROM (t1 LEFT JOIN t2 ON t1.a=t2.a) LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL

a	a	b	b
1	1	101	101
2	NULL	NULL	101

--左右连接输出值顺序不同

3、其他

a. select重新编号

---

SELECT (@i:=@i+1) i,vote_record.* FROM vote_record, (SELECT @i:=0) as i ORDER BY vote_num DESC,group_id limit 888888,10;

b.view视图运用

---

虚拟字段和view一样是虚拟的，相当于一个中介函数，临时执行，不提升性能，而是简化代码、提高代码复用率。

SELECT *,sum(vote_num) as sum FROM vote_record group BY vote_num,group_id order by sum desc limit 10;

create view order_page as (SELECT *,sum(vote_num) as sum FROM vote_record group BY vote_num,group_id order by sum desc limit 10);
select * from order_page ;

c.淘宝数据库内核月报

---

偶遇看到这个，我震惊了：《数据库内核月报 － 2019 / 07》