SQL优化

一、索引设计和优化

在简历索引之前需要考虑设计的问题，一个好的索引设计能够让慢查询得到优化。

1. 不要随意扩展索引。比如表中已经有了a的索引，那么根据情况,如果b是伴随着a条件经常出现的话，就加（a,b）的索引，修改原来的索引即可。如果是b自己经常单独出现，则需要自己增加单列索引。联合索引遵循最左前缀匹配原则。针对单列索引，mysql是一直像右匹配直到遇到范围查询（>,<,like,between）就停止匹配，比如索引（a,b,c），必须有a才会走这条索引，如果只单独走b和c是不走的。当顺序为a,b,c的时候，全走索引，当条件为a=1,b>2,c=3或者a=1，c>2,b=3，a的搜索走索引，最后一个字段不走索引,当条件是a,c，这时候是a会走索引c不走。对条件查询里的范围查询字段要放在有索引字段的后面，否则查询不走索引。

2. 索引列不能参与计算，比如条件为 date(create_time) = date('2019-01-01')是不走索引的，只有create_time = date('2019-01-01')和create_time = '2019-01-01'这种才会走索引，尽量要条件使用函数，不要对字段使用函数。

3. 单个多列组合索引和多个单列索引的检索查询效果不同，因为在执行SQL时，MySQL只能使用一个索引，会从多个单列索引中选择一个限制最为严格的索引。

4. 等于（=）和in 可以乱序。比如，a = 1 AND b = 2 AND c = 3 建立（a,b,c）索引可以任意顺序，MySQL的查询优化器会帮你优化成索引可以识别的模式。

5. 尽量选择区分度高的列作为索引，区分度的公式是 COUNT(DISTINCT col) / COUNT(*)。表示字段不重复的比率，比率越大我们扫描的记录数就越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度是0。可能有人会问，这个比率有什么经验么？使用场景不同，这个值也很难确定，一般需要JOIN的字段我们要求在0.1以上，即平均1条扫描10条记录。

二、sql查询优化

1. sql查询里应当避免使用!=和<>操作符，负责将导致存储引擎直接放弃二级索引而进行全表扫描，如果是聚簇索引，还是会走。在mysql里只有以下操作符才走索引：<, <=, =, >, >=, between, in以及某些方式的like 'a%'才会走索引。比如以下操作：

   走索引的sql：
   select * from base_user where user_code like '111111%' ;
   不走索引的sql：
   select * from base_user where user_code like '%1%' ;

2. 避免用like '%a%' 和like '%a'，这两种方式是不走索引而进行全表扫描。

3. 对null的使用，如果使用索引去对null判断的成本大于全表扫描，就走全表扫描。反之则走索引。由引擎选择，尽量使用执行计划查看sql的执行过程。

4. 避免使用or，如果使用or的时候，两个字段都是索引字段则全都会走索引搜索（包括联合索引），如果其中一个不是索引字段，将全部不走索引，全表扫描。应该使用union连接多个查询，如果有联合索引则使用union all效率更高。

5. 使用in和exists的选择，外层查询表小于子查询表，则用exists，外层查询表大于子查询表，则用in，如果外层和子查询表差不多，则爱用哪个用哪个。使用not in 和 not exists的选择为使用not exists代替not in，因为not in是不走索引。

三、IN条件查询

3.1 使用in注意事项

使用in不走索引的原因

1. 索引类型不匹配： 如果字段的类型与索引的类型不匹配，例如在字符型字段上创建了索引，但在 IN 子句中使用了数值类型的值，数据库可能无法使用该索引。
2. 数据分布不均匀： 如果 IN 子句中的值分布不均匀，例如在一个范围内包含了大量的值，而其他范围很少包含值，数据库可能会选择不使用索引，因为全表扫描可能更快。
3. 索引统计信息不准确： 数据库系统可能根据统计信息来决定是否使用索引，如果统计信息不准确，可能会导致数据库选择不使用索引。
4. 优化器选择全表扫描： 查询优化器可能会根据查询的复杂性、数据分布情况和表的大小等因素决定是使用索引还是全表扫描。有时候即使有合适的索引，优化器也可能选择全表扫描。
5. IN 子句中的值数量过多： 如果 IN 子句中包含了大量的值，超出了数据库系统的最大索引长度或查询优化器的限制，可能会导致数据库选择不使用索引。