04SQL索引优化(一)

SQL索引优化(一)

• 示例表

CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
`position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
PRIMARY KEY (`id`),
KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

 INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW());
 INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW());
 INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());

 ‐‐ 插入一些示例数据
 drop procedure if exists insert_emp;
 delimiter ;;
 create procedure insert_emp()
 begin
 declare i int;
 set i=1;
 while(i<=100000)do
 insert into employees(name,age,position) values(CONCAT('zhuge',i),i,'dev');
 set i=i+1;
 end while;
 end;;
 delimiter ;
 call insert_emp();

常见例子

1.联合索引第一个字段用范围不会走索引

EXPLAIN SELECT * FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';

• 联合索引的第一个字段就用范围查找不会走索引，mysql内部可能觉得第一个字段就用范围，结果集会很大，回表效率不高，还不如全表扫描

为什么使用索引会比全表扫描要慢？

• 因为索引扫描需要在索引树上进行查找，然后再通过索引指向的物理位置读取数据，这个过程可能涉及多次磁盘的IO操作，而全表扫描则是顺序的读取整个表的数据，磁盘的顺序读取的速度是很快的

2. 强制走索引

EXPLAIN SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei' AND age = 22 AND position ='manager';

• 虽然使用强制走索引让联合索引的第一个字段范围查找也走索引，扫描的行数rows看上去也少了点，但是最终查找效率不一定比全表扫描高，因为回表效率不高

3. 覆盖索引优化

EXPLAIN SELECT name,age,position FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';

4. in和or在表数据量大的情况会走索引，在表记录不多的情况会选择全表扫描

5. like KK%一般情况下都会走索引

• 为什么会走索引
  • 用到了索引下推
  • 对于辅助的联合索引，正常情况下按照最前缀原则select * from employees where name like ‘LiLei%’ and age = 22 and position = 'manager’这种情况下只会走name索引，因为根据name字段过滤完，得到所有行里的age和position是无序的，无法很好使用索引
  • 在MYSQL5.6之前的版本，这个查询只能在联合索引匹配到名字是‘LiLei’开头的索引，然后拿这些索引对应的主键逐个回表，到主键索引上找出相应的记录，再对比age和position这两个字段的值是否符合
  • MySQL5.6引入了索引下推优化，可以在索引遍历的过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效减少回表次数。使用了索引下推优化后，上面那个查询在联合索引里匹配到名字是‘LiLei’开头的索引之后，同时还会在索引里过滤age和position这两个字段，拿这过滤完剩下的索引对应的主键id再会查整行数据
• 范围查找没有使用索引下推优化
• 估计是MySQL认为范围查找过滤的结果集过大。

Explain select * from employees where name > 'a'

常见SQL优化

Order By 与Group By优化

• Case 1

• Case 2

• Case 3

• Case 4

• Case 5

• Case 6

• Case 7

• Case 8

优化总结

• MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低
• order by两种情况会使用using index
  • order by语句使用索引最左前列
  • 使用where子句与order by子句条件列组合满足索引最左前缀法则
• 尽量在索引列上完成排序，遵循索引建立(索引创建的顺序)时的最走前缀法则
• 如果order by的条件不在索引列上，就会产生file sort
• 能用覆盖索引就用覆盖索引
• group by与order by很类似，其实质是先排序后分组，遵循索引创建顺序的最左前缀法则，对应group by的优化如果不需要排序的可以加上order by null禁止排序，注意。where高于having，能写在where条件就不要使用having去限定了。

Using filesort文件排序原理详解

filesort文件排序方式

• 单路排序:是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序，用trace工具可以看到sort_mode信息里显示<sort_key, additional_fields>或者<sort_key, packed_additional_fields>
• 双路排序(又叫回表排序模式):是首先根据条件取出相应的排序字段和直接定位行记录的行id，然后在sort buffer中进行排序，排序完成之后需要再次取回其他需要的字段；用trace工具可以看到sort_mode信息里显示<sort_key, rowid>

MySQL通过比较系统变量max_length_for_sort_data（默认1024字节）的大小和需要查询的总字段大小来判断使用哪种排序模式

• 如果字段的总长度小于max_length_for_sort_data,那么使用单路排序模式
• 如果字段的总长度大于max_length_for_sort_data，那么使用双路排序模式.

单路排序的详细过程

• 从索引name找到第一个满足name条件的主键id
• 根据主键id取出整行，取出所有字段的值，存入sort_buffer中
• 从索引name找到下一个满足name条件的主键id
• 重复上面第二、三步直到不满足name条件
• 对sort_buffer中的数据字段position进行排序
• 返回结果给客户端

双路排序的过程

• 从索引name找到第一个满足name='zhuge’的主键id
• 根据主键id取出整行，把排序字段position和主键id放入sort_buffer 中
• 从索引name取下一个满足name="zhuge"记录的主键id
• 重复三、四步直到不满足name=“zhuge”
• 对sort_buffer中的字段和主键id按照字段position进行排序
• 遍历号排序好的id和字段position，按照id的值回到原表中取出所有的字段的值返回给客户端。

索引设计原则

• 代码先行，索引后上
• 联合索引尽量覆盖条件
• 不要在小基数字段上建立索引
• 长字符串我们可以使用前缀索引(取前20个字符)
• where与order by冲突时优先选择where
• 基于慢sql做优化

索引设计实战

以社交场景APP来举例，我们一般会去搜索一些好友，这里面就涉及到对用户信息的筛选，这里肯定就是对用户user表搜索了，这个表一般来说数据量会比较大，我们先不考虑分库分表的情况，比如，我们一般会筛选地区(省市)，性别，年龄，身高，爱好之类的，有的APP可能用户还有评分，比如用户的受欢迎程度评分，我们可能还会根据评分来排序等等。

对于后台程序来说除了过滤用户的各种条件，还需要分页之类的处理，可能会生成类似sql语句执行：

select xx from user where xx=xx and xx=xx order by xx limit xx,xx

对于这种情况如何合理设计索引了，比如用户可能经常会根据省市优先筛选同城的用户，还有根据性别去筛选，那我们是否应该设计一个联合索引 (province,city,sex) 了？这些字段好像基数都不大，其实是应该的，因为这些字段查询太频繁了。

假设又有用户根据年龄范围去筛选了，比如 where province=xx and city=xx and age>=xx and age<=xx，我们尝试着把age字段加入联合索引 (province,city,sex,age)，注意，一般这种范围查找的条件都要放在最后，之前讲过联合索引范围之后条件的是不能用索引的，但是对于当前这种情况依然用不到age这个索引字段，因为用户没有筛选sex字段，那怎么优化了？其实我们可以这么来优化下sql的写法：where province=xx and city=xx and sex in (‘female’,‘male’) and age>=xx and age<=xx

对于爱好之类的字段也可以类似sex字段处理，所以可以把爱好字段也加入索引 (province,city,sex,hobby,age)

假设可能还有一个筛选条件，比如要筛选最近一周登录过的用户，一般大家肯定希望跟活跃用户交友了，这样能尽快收到反馈，对应后台sql可能是这样：

where province=xx and city=xx and sex in (‘female’,‘male’) and age>=xx and age<=xx and latest_login_time>= xx

那我们是否能把 latest_login_time 字段也加入索引了？比如 (province,city,sex,hobby,age,latest_login_time) ，显然是不行的，那怎么来优化这种情况了？其实我们可以试着再设计一个字段is_login_in_latest_7_days，用户如果一周内有登录值就为1，否则为0，那么我们就可以把索引设计成 (province,city,sex,hobby,is_login_in_latest_7_days,age) 来满足上面那种场景了！

一般来说，通过这么一个多字段的索引是能够过滤掉绝大部分数据的，就保留小部分数据下来基于磁盘文件进行order by语句的排序，最后基于limit进行分页，那么一般性能还是比较高的。

不过有时可能用户会这么来查询，就查下受欢迎度较高的女性，比如sql：where sex = ‘female’ order by score limit xx,xx，那么上面那个索引是很难用上的，不能把太多的字段以及太多的值都用 in 语句拼接到sql里的，那怎么办了？其实我们可以再设计一个辅助的联合索引，比如 (sex,score)，这样就能满足查询要求了。

以上就是给大家讲的一些索引设计的思路了，核心思想就是，尽量利用一两个复杂的多字段联合索引，抗下你80%以上的查询，然后用一两个辅助索引尽量抗下剩余的一些非典型查询，保证这种大数据量表的查询尽可能多的都能充分利用索引，这样就能保证你的查询速度和性能了！