MySQL索引与SQL语句优化

共计 1675 个字符，预计需要花费 5 分钟才能阅读完成。

概念
1. 普通索引：最基本的索引，它没有任何限制
2. 唯一索引：索引列的值必须唯一，且不能为空，如果是组合索引，则列值的组合必须唯一。
3. 主键索引：特殊的索引，唯一的标识一条记录，不能为空，一般用 primary key 来约束。
4. 联合索引：在多个字段上建立索引，能够加速查询到速度
5.Union：对两个结果集进行并集操作，不包括重复的行，同时进行默认规则排序
6.Union all：对两个结果集进行并集操作，包括重复行，不进行排序

优化

1、前导模糊查询不能使用索引，

如 name like‘% 静’

2、Union、in、or 可以命中索引，建议使用 in

3、负条件查询不能使用索引，可以优化为 in 查询，

其中负条件有！=、<>、not in、not exists、not like 等

4、联合索引最左前缀原则，又叫最左侧查询，

如果在（a，b，c）三个字段上建立联合索引，那么它能够加快 a |（a，b）|（a，b，c）三组的查询速度。

5、建立联合查询时，区分度最高的字段在最左边

6、如果建立了（a，b）联合索引，就不必再单独建立 a 索引。

同理，如果建立了（a，b，c）索引就不必再建立 a，（a，b）索引

7、存在非等号和等号混合判断条件时，在建索引时，要把等号条件的列前置

8、范围列可以用到索引，但是范围列后面的列无法用到索引。

索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引。范围条件有：<、<=、>、>=、between 等。

9、把计算放到业务层而不是数据库层。

在字段上计算不能命中索引，

10、强制类型转换会全表扫描，

如果 phone 字段是 varcher 类型，则下面的 SQL 不能命中索引。Select * fromuser where phone=13800001234

11、更新十分频繁、数据区分度不高的字段上不宜建立索引。

更新会变更 B + 树，更新频繁的字段建立索引会大大降低数据库性能。

“性别”这种区分度不太大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似。

一般区分度在 80% 以上就可以建立索引。区分度可以使用 count（distinct（列名））/count（*）来计算。

12、利用覆盖索引来进行查询操作，避免回表。

被查询的列，数据能从索引中取得，而不是通过定位符 row-locator 再到 row 上获取，即“被查询列要被所建的索引覆盖”，这能够加速度查询。

13、建立索引的列不能为 null，使用 not null 约束及默认值

14、利用延迟关联或者子查询优化超多分页场景，

MySQL 并不是跳过 offset 行，而是取 offset+ N 行，然后放弃前 offset 行，返回 N 行，那当 offset 特别大的时候，效率非常低下，要么控制返回的总数，要么对超过特定阈值的页进行 SQL 改写。

15、业务上唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。

16、超过三个表最好不要用 join，

需要 join 的字段，数据类型必须一致，多表关联查询时，保证被关联的字段需要有索引。

17、如果明确知道查询结果只要一条，limit 1 能够提高效率，比如验证登录的时候。

18、Select 语句务必指明字段名称

19、如果排序字段没有用到索引，就尽量少排序

20、尽量用 union all 代替 union。

Union 需要将集合合并后在进行唯一性过滤操作，这会涉及到排序，大量的 cpu 运算，加大资源消耗及延迟，当然，使用 union all 的前提条件是两个结果集没有重复数据。

21、使用合理的分页提高效率。

select id,name from product limit 866613, 20

使用上述 SQL 语句做分页的时候，可能有人会发现，随着表数据量的增加，直接使用 limit 分页查询会越来越慢。

优化的方法如下：

可以取前一页的最大行数的 id，然后根据这个最大的 id 来限制下一页的起点。

比如此列中，上一页最大的 id 是 866612。

SQL 可以采用如下的写法：select id,name from product where id> 866612 limit 20。