mysql索引优化规则,MySQL性能优化-索引优化

索引是提高查询性能最有效的方式之一，在表结构设计阶段就应当考虑索引的设计，索引也不是越多越好，需要结合具体的SQL、执行频率、数据分布等多个方面综合考虑。本文整理了MySQL索引优化的一些原则、经验和技巧。

MySQL的索引实现因存储引擎的差异而略有不同，本文主要介绍InnoDB存储引擎的索引优化。

一、查看索引信息

查看表中有哪些索引，比如主键索引，唯一索引，普通索引等，在表结构中就能看到，如下命令：

show create table table_name\G

想要看到索引的更多信息，比如索引的基数，索引的类型等，执行如下命令：

show index from table_name;

二、判断SQL有没有使用索引

MySQL提供explain命令查看SQL的执行计划，通过执行计划能够判断SQL在执行过程中是否使用索引。比如：

explain select * from table_name where name = 'xxx';

根据explain的输出结果key字段来判断是否使用索引，以及使用了哪个索引。

三、索引分类

(1)主键索引 primary key

主键索引不允许有空值(null)，一张表只有一个主键，MySQL InnoDB表是索引组织表，所有的数据都在主键索引的叶子节点中。

如果用户没有定义主键，那么MySQL会选择一个非空唯一索引作为主键，如果没有非空唯一索引，那么MySQL会创建一个隐式的主键。

(2)唯一键索引 unique key

唯一索引的值必须唯一，不允许重复，但可以为空值(null)，一张表中可以创建多个唯一索引。

(3)普通索引

普通的B+树索引，可以有多个，允许重复值，也允许为空值(null)。

以上三种索引还可以根据索引包含的字段数量来划分，如下：

(1)单列索引，索引字段只有一个。

(2)组合索引，索引字段有多个，常用于多个条件查询，以及形成覆盖索引，避免回表。

四、索引优化的原则

一定要有主键索引，主键索引字段尽可能的小，最好自增，比如 int auto_increment。

主键索引不建议使用md5，uuid这类无序字符串，插入数据会频繁页分裂，影响性能，并且磁盘占用过大。

适合索引的列通常是出现在where条件中的列，或者join连接中的连接字段。

建索引的列，不建议为null值。

区分度不高的字段不适合建索引，比如性别。

组合索引建议将区分度高的字段放到前面，区分度低的字段放在后面。

建议使用短索引，对于长字符串，如果其前N个字符已经有很好的区分度，可以指定前缀索引，既能优化查询，也避免了索引过大。

不要过度索引，维护索引也是有成本的，不仅占用磁盘空间，还影响写入性能。

更新频繁的字段，不建议建索引。更新操作会变更索引，影响性能。

不要创建冗余和重复的索引。

单表索引建议控制在5个以内。

组合索引字段数建议不超过5个。字段超过5个时，实际已经起不到有效过滤数据的作用了。

五、覆盖索引

覆盖索引不是索引类型，它只是使用索引的一种方式，只读取索引数据就能返回结果给用户，不需要再回表查询。

覆盖索引优点：

利用索引直接返回数据，不再回表查询

利用索引的有序性，避免不必要的排序

注意：使用前缀索引，将导致覆盖索引不可用。

六、索引使用情况监控

可以通过查看MySQL的状态变量来确认索引的使用情况：

show status like 'Handler_read%';

Handler_read_key：如果索引正常工作，该值将很高。

Handler_read_rnd_next：数据文件中读取下一行的请求数，如果正在进行大量的表扫描，该值较高，说明索引利用不理想。