本文已参与「新人创作礼」活动，一起开启掘金创作之路
本文参考自宋红康老师的课程
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的语句，where过滤出来的结果集尽量保持在1000行内
7.优化分页查询
EXPLAIN SELECT * FROM student LIMIT 2000000,10;
上条语句需要前2000010条记录，仅返回2000000-2000010的记录，其他记录丢弃，查询代价大。
优化思路：
-
在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容
SELECT * FROM student t,(SELECT id FROM student ORDER BY id LIMIT 2000000,10) a WHERE t.id = a.id;
-
主键自增的表中，可以把limit查询转换成某个位置的查询
SELECT * FROM student WHERE id > 2000000 LIMIT 10;
8.优先考虑覆盖索引
一个索引包含了满足查询结果的数据叫做覆盖索引。索引列+主键包含SELECT 到 FROM之间查询的列。
8.1覆盖索引的利弊
好处：
- 避免InnoDB表进行索引的二次查询（回表）
- 可以把随机IO变成顺序IO加快查询效率
弊端：
索引字段的维护。
9.给字符串添加索引
邮箱字段：email varchar(64)
9.1 前缀索引
如果创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。
alter table teacher add index index1(email);
alter table teacher add index index2(email(6));
由于email(6)这个索引结构中每个邮箱字段都只取前6个字节，所以占用的空间会更小，但是可能会增加额外的记录扫描次数。
select id, name, email from teacher where email = 'zhangssxyz@xxx.com'；
如果使用的是整个字符串的索引结果：
- 从index1索引树找到满足索引值是'zhangssxyz@xxx.com'的记录，记录ID2的值
- 到主键上查到主键值是ID2的行，判断email的值是正确的，将这行记录加入结果集
- 取index1索引树上刚查到的位置的下一条记录，发现不满足'zhangssxyz@xxx.com'条件就结束循环
如果使用的是email(6)的索引结构：
- 从index2索引树找到索引值是'zhangs'的记录，找到第一个ID1
- 到主键上查找主键是ID1的行，判断email的值不是'zhangssxyz@xxx.com'，丢弃记录
- 取index2上刚查到位置的下一条记录，重复操作
9.2 前缀索引对覆盖索引的影响
假设定义的索引为email(11)，这时执行
select id, email from teacher where email = 'zhb@xxx.com'；
虽然index2已经包含了所有信息，但是InnoDB还是要拿到id回表查一次。使用前缀索引就用不上覆盖索引对查询性能的优化了。
如果要查询身份证号，我国的身份证号一共18位，其中前6位是地址码，同一个县的身份证号前6位一般相同，这时如果对身份证做长度为6的前缀索引，区分度就很低，此时需要长度为12以上的前缀索引，为了减少索引长度，有以下处理方法：
-
倒序存储
把身份证号倒过来存储，每次查询时
select field_list from teacher where id_card = reverse('input_id_card_string');
-
使用hash字段
可以在表上再创建一个整数字段来保存身份证的校验码，在这个字段上创建索引。每次插入新记录时，同时用crc32()函数得到校验码填到这个字段
10.索引下推
Index Condition Pushdown(ICP)是MySQL5.6中的新特性，是一种在存储引擎层使用索引过滤数据的一种优化方式。ICP可以减少存储引擎访问基表的次数以及MySQL服务器访问存储引擎的次数。
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的使用条件
①只能用于二级索引
②explain显示的执行计划中type值(join类型)为range、ref、eq_ref或ref_or_null。
③并非全部where条件都可以用ICP筛选，如果where条件的字段不在索引列中，还是要读取整表的记录到server端做过滤。
④ICP可以用于MyISAM和InnoDB存储引擎。
⑤5.7版本开始支持分区表的ICP功能。
⑥当SQL使用覆盖索引时，不支持ICP优化方法。
10.3 ICP使用案例
SELECT * FROM tuser
WHERE NAME LIKE '张%' AND age = 10 AND ismale = 1;
11.普通索引和唯一索引
假设有一个主键为ID的表，字段k上有索引，k上的值都不重复：
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记录的条件。
-
唯一索引：
由于索引定义了唯一性，找到了第一个满足条件的记录后就会停止继续检索。
