文章目录
索引的创建和设计原则
索引的分类
MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。
- 从
功能逻辑上说，索引主要有 4 种，分别是普通索引、唯一索引、主键索引、全文索引。 - 按照
物理实现方式，索引可以分为 2 种：聚簇索引和非聚簇索引。 - 按照
作用字段个数进行划分，分成单列索引和联合索引。
隐藏索引
在MySQL 5.7版本及之前，只能通过显式的方式删除索引。此时，如果发现删除索引后出现错误，又只能
通过显式创建索引的方式将删除的索引创建回来。如果数据表中的数据量非常大，或者数据表本身比较
大，这种操作就会消耗系统过多的资源，操作成本非常高。
从MySQL 8.x开始支持 隐藏索引（invisible indexes） ，只需要将待删除的索引设置为隐藏索引，使
查询优化器不再使用这个索引（即使使用force index（强制使用索引），优化器也不会使用该索引），
确认将索引设置为隐藏索引后系统不受任何响应，就可以彻底删除索引。 这种通过先将索引设置为隐藏索 引，再删除索引的方式就是软删除 。
性能分析工具的使用
数据库服务器的优化步骤
分析查询语句:EXPLAIN
如果我们想看看某个查询的执行计划的话，可以在具体的查询语句前边加一个 EXPLAIN ,EXPLAIN 语句输出的各个列的作用如下：
索引优化和查询优化
索引失效案例
全值匹配
比如说有sql语句为
SELECT * FROM student WHERE age=30 AND classId=4 AND name = 'abcd';
那么此时最好建立联合索引
CREATE INDEX idx_age_classid_name ON student(age,classId,name);
最佳左前缀原则
索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。例如上面的索引不变,但是sql语句变成了:
SELECT * FROM student WHERE age=30 AND name='abcd';
那么此时此刻就只能够用上age的索引,什么意思呢?执行下面代码:
EXPLAIN SELECT * FROM student WHERE age=30 AND name='abcd';
就可以知道key_len的长度为5(int类型占4,空占1).
计算、函数、类型转换(自动或手动)导致索引失效
很简单的道理,WHERE后面如果有函数或者计算,就会导致索引失效
类型转换导致索引失效
比如下面的sql语句是不能使用索引的(假设name是varchar类型):
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123;
下面就使用到了索引:
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name='123';
范围条件右边的列索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ;
此时此刻,key_len的长度只能是10.
不等于(!= 或者<>)索引失效
is null可以使用索引，is not null无法使用索引
like以通配符%开头索引失效
页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。
OR 前后存在非索引的列，索引失效
比如
CREATE INDEX idx_age ON student(age);
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=10 OR classid=100;
就不能够使用索引.
关联查询优化
外连接
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;
这里type就是驱动表,而book就是被驱动表.
添加索引时尽量加在被驱动表上,效率更高.
内连接
MySQL自动选择驱动表和被驱动表.
对于内连接来说,如果表的连接条件中只能有一个字段有索引,则有索引的字段所在的表会被座作为驱动表.
在两个表的连接条件都存在所以索引的情况下,会选择小表驱动大表.
JOIN语句原理
简单嵌套循环连接(SNLJ)
算法简单,从表A中取出一条数据1,遍历表B,将匹配到的数据放到resulut…以此类推,驱动表A中的每一条记录与被驱动表B的记录进行判断:
这种方式效率是非常低的,若A表数据100条,B表数据1000条,需要遍历100*1000=10万次:
MySQL当然不会这样去进行表的连接,所以就出现了和后面的两种优化的算法.
索引嵌套循环连接(INLJ)
优化思路是为了减少内层表数据的匹配次数,所以要求被驱动表必须有索引才行.通过外层表匹配条件直接与内层表索引进行匹配,避免和内层表的每条记录去进行比较,极大减少了对内层表的匹配次数.
块嵌套循环连接(BNLJ)
如果存在索引，那么会使用index的方式进行join，如果join的列没有索引，被驱动表要扫描的次数太多了。每次访问被驱动表，其表中的记录都会被加载到内存中，然后再从驱动表中取一条与其匹配，匹配结束后清除内存，然后再从驱动表中加载一条记录，然后把被驱动表的记录在加载到内存匹配，这样周而复始，大大增加了IO的次数。为了减少被驱动表的IO次数，就出现了BlockNested-Loop Join的方式。
不再是逐条获取驱动表的数据，而是一块一块的获取，引入了 join buffer缓冲区，将驱动表join相关的部分数据列(大小受join bufer的限制)缓存到join buffer中，然后全表扫描被驱动表，被驱动表的每一条记录一次性和joinbufer中的所有驱动表记录进行匹配(内存中操作)，将简单嵌套循环中的多次比较合并成一次，降低了被驱动表的访问频率。.
小结
- 整体效率:
INLJ>BNLJ>SNLJ - 永远用小结果集驱动大结果集
- 为被驱动表匹配的条件增加索引
- 增大join buffer size大小
- 减少驱动表中不必要的字段查询
Hash join
从MySQL的8.0.20版本开始将废弃BNLJ，因为从MySQL8.0.18版本开始就加入了hash join默认都会使用hash join
- Hash Join是做
大数据集连接时的常用方式，优化器使用两个表中较小(相对较小)的表利用Join Key在内存中建立散列表，然后扫描较大的表并探测散列表，找出与Hash表匹配的行。- 这种方式适用于较小的表完全可以放于内存中的情况，这样总成本就是访问两个表的成本之和。
- 在表很大的情况下并不能完全放入内存，这时优化器会将它分割成
若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要求有较大的临时段从而尽量提高IO的性能。 - 它能够很好的工作于没有索引的大表和并行査询的环境中，并提供最好的性能。大多数人都说它是Join的重型升降机。Hash Join
只能应用于等值连接(如WHERE A.COL1=B.COL2)，这是由Hash的特点决定的。
子查询优化
MySQL从4.1版本开始支持子查询，使用子查询可以进行SELECT语句的嵌套查询，即一个SELECT查询的结果作为另一个SELECT语句的条件。 子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作 。
子查询是 MySQL 的一项重要的功能,可以帮助我们通过一个 SQL 语句实现比较复杂的查询.但是，子查询的执行效率不高.原因:
- 执行子查询时，MySQL需要为内层查询语句的查询结果
建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。这样会消耗过多的CPU和IO资源，产生大量的慢查询。 - 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都
不会存在索引，所以查询性能会受到一定的影响。 - 对于返回结果集比较大的子查询，其对查询性能的影响也就越大。
在MySQL中,可以使用连接（JOIN）查询来替代子查询.连接查询 不需要建立临时表 ，其 速度比子查询要快 ，如果查询中使用索引的话，性能就会更好。
结论：尽量不要使用NOT IN 或者 NOT EXISTS，用LEFT JOIN xxx ON xx WHERE xx IS NULL替代
排序优化
- SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中
避免全表扫 描，在 ORDER BY 子句避免使用 FileSort 排序。当然，某些情况下全表扫描，或者 FileSort 排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。 - 尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列；
如果不同就使用联合索引。 - 无法使用 Index 时，需要对 FileSort 方式进行调优。
filesort算法:双路排序和单路排序
双路排序(慢)
MySQL 4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据， 读取行指针和order by列 ，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出- 从磁盘取排序字段，在buffer进行排序，再从
磁盘取其他字段。
单路排序(快) - 从磁盘读取查询需要的
所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出， 它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间， 因为它把每一行都保存在内存中了。
优化策略
- 尝试提高
sort_buffer_size - 尝试提高
max_length_for_sort_data - Order by 时select * 是一个大忌。最好
只Query需要的字段。
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会很慢。
优先考虑覆盖索引
什么是覆盖索引?
就是索引列+主键包含SELECT到FROM之间查询的列
优点
- 避免InnoDB表进行索引的二次查询(回表)
- 可以把随机IO变成顺序IO加快查询效率
索引下推(ICP)
Index Condition Pushdown(ICP)是MySQL5.6中新特性,是一种在存储引擎层过滤数据的优化方式.
- 如果没有ICP,存储引擎会遍历索引以定位基表中的行,并将它们返回给MySQL服务器,由MySQL服务器评估
WHERE后面的条件是否保留行 - 启用 ICP后，如果部分
WHERE条件可以仅使用索引中的列进行筛选，则 MySQL服务器会把这部分WHERE条件放到存储引擎筛选。然后，存储引擎通过使用索引条目来筛选数据，并且只有在满足这一条件时才从表中读取行。- 好处:ICP可以减少存储引擎必须访问基表的次数和MySQL服务器必须访问存储引擎的次数。
- 但是，ICP的
加速效果取决于在存储引擎内通过ICP筛选掉的数据的比例。
使用条件
① 只能用于二级索引(secondary index)
②explain显示的执行计划中type值（join 类型）为 range,ref,eq_ref 或者 ref_or_null 。
③ 并非全部where条件都可以用ICP筛选，如果where条件的字段不在索引列中，还是要读取整表的记录到server端做where过滤。
④ ICP可以用于MyISAM和InnnoDB存储引擎
⑤ MySQL 5.6版本的不支持分区表的ICP功能，5.7版本的开始支持。
⑥ 当SQL使用覆盖索引时，不支持ICP优化方法。
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
