03.索引优化
索引优化
在《Database-Notes/索引》一节中，我们讨论了 B+Tree, LSM-Tree 这样的文件索引以及全文索引的基础算法。索引优化是数据库优化的重中之重。一个查询使用索引与不使用索引的差别可能只在 100 个数量级，而一个好的索引与不好的索引差别可能在 1000 个数量级，但是一个最优的索引与普通的索引查询效率可能就相差上万甚至更高的数量级。
索引（Index）是帮助数据库系统高效获取数据的数据结构，而数据库索引本质上是以增加额外的写操作，与用于维护索引数据结构的存储空间为代价的，用于提升数据库中数据检索效率的数据结构。索引可以帮助我们快速地定位到数据而不需要每次搜索的时候都遍历数据库中的每一行。当然，索引不是建立的越多、越长越好，因为索引除了占用空间之外，对后续数据库的增加、删除、修改都有额外的操作来更新索引。一般来说，小表使用全表扫描更快，中大表才使用索引，而超级大表索引基本无效，我们可能需要借助独立的全文索引系统；MySQL 自带的全文索引只能用于 InnoDB、MyISAM，并且只能对英文进行全文检索，一般使用 ES，Solr 这样的全文索引引擎。
查询优化
索引设置原则
-
索引区分度低：假如表中有 1000w 记录，其中有 status 字段表示状态，可能 90%的数据 status=1，可以不将 status 作为索引，因为其对数据记录区分度很低。
-
切忌过多创建索引：每个索引都需要占用磁盘空间，修改表数据时会对索引进行更新，索引越多，更新越复杂。因为每添加一个索引，.ibd 文件中就需要多维护一个 B+Tree 索引树，如果某一个 table 中存在 10 个索引，那么就需要维护 10 棵 B+Tree，写入效率会降低，并且会浪费磁盘空间。
-
常用查询字段建索引：如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度，属于热门字段，为其建立索引非常必要。
-
常排序/分组/去重字段建索引：对于需要经常使用 ORDER BY、GROUP BY、DISTINCT 和 UNION 等操作的字段建立索引，可以有效借助 B+树的特性来加速执行。
-
主键和外键建索引：主键可以用来创建聚集索引，外键也是唯一的且常用于表关联的字段，也需要建索引来提高性能。
SQL 的优化
如果数据库表的索引设置比较合理，SQL 语句书写不当会造成索引失效，甚至造成全表扫描，迅速拉低性能。
索引失效
我们在写 SQL 的时候在某些情况下会出现索引失效的情况。
-
对索引使用函数：
select id from std upper(name) = 'JIM'; -
对索引进行运算：
select id from std where id+1=10; -
对索引使用<> 、not in 、not exist、!=：
select id from std where name != 'jim'; -
对索引进行前导模糊查询：
select id from std name like '%jim'; -
隐式转换会导致不走索引：比如：字符串类型索引字段不加引号，select id from std name = 100;保持变量类型与字段类型一致
-
非索引字段的 or 连接：并不是所有的 or 都会使索引失效，如果 or 连接的所有字段都设置了索引，是会走索引的，一旦有一个字段没有索引，就会走全表扫描。
-
联合索引仅包含复合索引非前置列：联合索引包含 key1，key2，key3 三列，但 SQL 语句没有 key1，根据联合索引的最左匹配原则，不会走联合索引。
select name from table where key2=1 and key3=2;
好的建议
-
使用连接代替子查询：对于数据库来说，在绝大部分情况下，连接会比子查询更快，使用连接的方式，MySQL 优化器一般可以生成更佳的执行计划，更高效地处理查询而子查询往往需要运行重复的查询，子查询生成的临时表上也没有索引， 因此效率会更低。
-
LIMIT 偏移量过大的优化：禁止分页查询偏移量过大，如 limit 100000,10
-
使用覆盖索引：减少
select *借助覆盖索引，减少回表查询次数。 -
多表关联查询时，小表在前，大表在后：在 MySQL 中，执行 from 后的表关联查询是从左往右执行的，第一张表会涉及到全表扫描，所以将小表放在前面，先扫小表，扫描快效率较高，在扫描后面的大表，或许只扫描大表的前 100 行就符合返回条件并 return 了。
-
调整 Where 字句中的连接顺序：MySQL 采用从左往右的顺序解析 where 子句，可以将过滤数据多的条件放在前面，最快速度缩小结果集。
-
使用小范围事务，而非大范围事务
-
遵循最左匹配原则
-
使用联合索引，而非建立多个单独索引