MySQL-索引优化与查询优化4

个人学习笔记
源自B站尚硅谷讲师宋红康的MySQL课程
B站地址：https://www.bilibili.com/video/BV1iq4y1u7vj

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 2000000, 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 > 2000000 LIMIT 10;

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 <> 20;

举例二：

#索引失效，不符合最左匹配原则，跳过了age
EXPLAIN SELECT * FROM student WHERE `name` LIKE '%abc';

#索引生效
EXPLAIN SELECT id , age ,`name` FROM student WHERE `name` LIKE '%abc';

上述都使用到了声明的索引，下面的情况则不然，查询列依然多了classid，结果是未使用到索引:

#索引失效，classid不在索引列，无法使用覆盖索引
EXPLAIN SELECT id,age,`name`,classid FROM student WHERE `name` LIKE '%abc';

8.2 覆盖索引的利弊

好处:

1.避免Innodb表进行索引的二次查询(回表)

Innodb是以聚集索引的顺序来存储的，对于Innodb来说，二级索引在叶子节点中所保存的是行的主键信息，如果是用二级索引查询数据，在查找到相应的键值后，还需通过主键进行二次查询才能获取我们真实所需要的数据。在覆盖索引中，二级索引的键值中可以获取所要的数据，避免了对主键的二次查询，减少了IO操作，提升了查询效率。

2.可以把随机IO变成顺序IO加快查询效率

由于覆盖索引是按键值的顺序存储的，对于IO密集型的范围查找来说，对比随机从磁盘读取每一行的数据IO要少的多，因此利用覆盖索引在访问时也可以把磁盘的 随机读取的IO 转变成索引查找的 顺序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整个字符串的索引结构），执行顺序是这样的：

1. 从index1索引树找到满足索引值是’ zhangssxyz@xxx.com '的这条记录，取得ID2的值；

2. 到主键上查到主键值是ID2的行，判断email的值是正确的，将这行记录加入结果集；

3. 取index1索引树上刚刚查到的位置的下一条记录，发现已经不满足email=’ zhangssxyz@xxx.com '的条件了，循环结束。

这个过程中，只需要回主键索引取一次数据，所以系统认为只扫描了一行。

如果使用的是 index2（即email(6)索引结构），执行顺序是这样的

1. 从index2索引树找到满足索引值是’zhangs’的记录，找到的 第一个是ID1；

2. 到主键上查到主键值是ID1的行，判断出email的值不是’ zhangssxyz@xxx.com ’，这行记录丢弃；

3. 取index2上刚刚查到的位置的下一条记录，发现仍然是’zhangs’，取出ID2，再到ID索引上取整行然后判断，这次值对了，将这行记录加入结果集；

4. 重复上一步，直到在idxe2上取到的值不是’zhangs’时，循环结束。

也就是说 使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。 前面已经讲过区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少。

9.2 前缀索引对覆盖索引的影响

结论：

使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需要考虑的一个因素。

这样，索引的长度变成了4个字节，比原来小了很多。

从查询效率上看，使用hash字段方式的查询性能相对更稳定一些。

因为crc32算出来的值虽然有冲突的概率，但是概率非常小，可以认为每次查询的平均扫描行数接近1。

而倒序存储方式毕竟还是用的前缀索引的方式,也就是说还是会增加扫描行数。