mysql <> 索引,MySQL 索引及优化实战(一)

随着系统用户量的不断增加，MySQL 索引的重要性不言而喻，对于后端工程师，只有在了解索引及其优化的规则，并应用于实际工作中后，才能不断的提升系统性能，开发出高性能、高并发和高可用的系统。

MySQL 索引及优化实战(一)和(二)会跟大家介绍一下 MySQL 索引中的各种概念，然后介绍优化索引的若干条规则，最后利用这些规则，针对面试中常考的知识点，做详细的实例分析。通过这两篇文章，您将学到如下内容：

MySQL 索引概念：聚集索引、非聚集索引、联合索引、主键、外键、唯一索引等。

MySQL 索引优化规则：前导模糊查询、or / in / union、负向查询、null、最左前缀等。

常见面试题分析：通过大量实例来说明如何优化索引。

索引是一种使记录有序化的技术，它可以指定按某列/某几列预先排序，从而大大提高查询速度(类似于汉语词典中按照拼音或者笔画查找)。

索引的主要作用是加快数据查找速度，提高数据库的性能。

从物理存储角度上，索引可以分为聚集索引和非聚集索引。

1.聚集索引(Clustered Index)

聚集索引决定数据在磁盘上的物理排序，一个表只能有一个聚集索引。

2.非聚集索引(Non-clustered Index)

非聚集索引并不决定数据在磁盘上的物理排序，索引上只包含被建立索引的数据，以及一个行定位符 row-locator，这个行定位符，可以理解为一个聚集索引物理排序的指针，通过这个指针，可以找到行数据。

从逻辑角度，索引可以分为以下几种。

普通索引：最基本的索引，它没有任何限制。

唯一索引：与普通索引类似，不同的就是索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

主键索引：它是一种特殊的唯一索引，用于唯一标识数据表中的某一条记录，不允许有空值，一般用 primary key 来约束。主键和聚集索引的关系详见“问题详解”中的第4题。

联合索引(又叫复合索引)：多个字段上建立的索引，能够加速复合查询条件的检索。

全文索引：老版本 MySQL 自带的全文索引只能用于数据库引擎为 MyISAM 的数据表，新版本 MySQL 5.6 的 InnoDB 支持全文索引。默认 MySQL 不支持中文全文检索，可以通过扩展 MySQL，添加中文全文检索或为中文内容表提供一个对应的英文索引表的方式来支持中文。

可以通过以下规则对 MySQL 索引进行优化。

1.前导模糊查询不能使用索引。

例如下面 SQL 语句不能使用索引。

select * from doc where title like '%XX'而非前导模糊查询则可以使用索引，如下面的 SQL 语句。

select * from doc where title like 'XX%'页面搜索严禁左模糊或者全模糊，如果需要可以用搜索引擎来解决。

2.union、in、or 都能够命中索引，建议使用 in。

union：能够命中索引。

示例代码如下：

select * from doc where status=1union allselect * from doc where status=2      直接告诉 MySQL 怎么做，MySQL 耗费的 CPU 最少，但是一般不这么写 SQL。

in：能够命中索引。

示例代码如下：

select * from doc where status in (1, 2)      查询优化耗费的 CPU 比 union all 多，但可以忽略不计，一般情况下建议使用 in

or：新版的 MySQL 能够命中索引。

示例代码如下：

select * from doc where status = 1 or status = 2      查询优化耗费的 CPU 比 in 多，不建议频繁用 or。

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

负向条件有：!=、<>、not in、not exists、not like 等。

例如下面代码：

select * from doc where status != 1 and status != 2可以优化为 in 查询：

select * from doc where status in (0,3,4)4.联合索引最左前缀原则(又叫最左侧查询)

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

例如登录业务需求，代码如下。

select uid, login_time from user where login_name=? and passwd=?      可以建立(login_name, passwd)的联合索引。

因为业务上几乎没有 passwd 的单条件查询需求，而有很多 login_name 的单条件查询需求，所以可以建立(login_name, passwd)的联合索引，而不是(passwd, login_name)。

建联合索引的时候，区分度最高的字段在最左边。

如果建立了(a,b)联合索引，就不必再单独建立 a 索引。同理，如果建立了(a,b,c)联合索引，就不必再单独建立 a、(a,b) 索引。

存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如 where a>? and b=?，那么即使 a 的区分度更高，也必须把 b 放在索引的最前列。

最左侧查询需求，并不是指 SQL 语句的 where 顺序要和联合索引一致。

下面的 SQL 语句也可以命中 (login_name, passwd) 这个联合索引。

select uid, login_time from user where passwd=? and login_name=?      但还是建议 where 后的顺序和联合索引一致，养成好习惯。

5.范围列可以用到索引(联合索引必须是最左前缀)。

范围条件有：、>=、between等。

范围列可以用到索引(联合索引必须是最左前缀)，但是范围列后面的列无法用到索引，索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引。

假如有联合索引 (empno、title、fromdate)，那么下面的 SQL 中 emp_no 可以用到索引，而 title 和 from_date 则使用不到索引。

select * from employees.titles where emp_no < 10010' and title='Senior Engineer' and from_date between '1986-01-01' and '1986-12-31'6.把计算放到业务层而不是数据库层。

在字段上进行计算不能命中索引。

例如下面的 SQL 语句。

select * from doc where YEAR(create_time) <= '2016'      即使 date 上建立了索引，也会全表扫描，可优化为值计算，如下：

select * from doc where create_time <= '2016-01-01'把计算放到业务层。

这样做不仅可以节省数据库的 CPU，还可以起到查询缓存优化效果。

比如下面的 SQL 语句：

select * from order where date < = CURDATE()      可以优化为：

select * from order where date < = '2018-01-24 12:00:00'      优化后的 SQL 释放了数据库的 CPU 多次调用，传入的 SQL 相同，才可以利用查询缓存。

7.强制类型转换会全表扫描

如果 phone 字段是 varchar 类型，则下面的 SQL 不能命中索引。

select * from user where phone=234      可以优化为：

select * from user where phone='234'8.更新十分频繁、数据区分度不高的字段上不宜建立索引。

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

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

一般区分度在80%以上的时候就可以建立索引，区分度可以使用 count(distinct(列名))/count(*) 来计算。

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

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

例如登录业务需求，代码如下。

select uid, login_time from user where login_name=? and passwd=?可以建立(login_name, passwd, login_time)的联合索引，由于 login_time 已经建立在索引中了，被查询的 uid 和 login_time 就不用去 row 上获取数据了，从而加速查询。

10.如果有 order by、group by 的场景，请注意利用索引的有序性。

order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。

例如对于语句 where a=? and b=? order by c，可以建立联合索引(a,b,c)。

如果索引中有范围查找，那么索引有序性无法利用，如 WHERE a>10 ORDER BY b;，索引(a,b)无法排序。

这篇文章介绍了MySQL索引的概念及10条优化规则，下篇文章会介绍第11～20条优化规则，并通过实例来验证这些规则。