关于MySQL索引的总结

一、关于MySQL索引的总结

1.什么是索引？

• 索引是针对数据库中快速找到某一条数据而设计的一种数据结构。

2.索引的分类

• 主键索引(PRIMARY KEY)：它是一种特殊的唯一索引，和主键一样不允许为空，且唯一；一般当我们在建表时，只要我们给一个表中的某一字段加上主键(PRIMARY KEY)的话，那么它就有一个默认的主键索引。

• 唯一索引(UNIQUE):唯一索引的列值必须唯一，但是不同于主键索引，它可以有多个空值。

• 创建方式: ALTER TABLE 表名 ADD UNIQUE(列名)

• 普通的索引(INDEX)：是最基本的索引，没有限制

• 创建方式: ALTER TABLE 表名 ADD INDEX 索引名称(列名)

• 组合索引（INDEX）:一个索引包含多个列，多用于避免回表查询(也就是当我们查到某一列数据时，又再一次去执行查询全表操作了)

• 创建方式:ALTER TABLE 表名 ADD INDEX 索引名称(列名1，列名2，列名n)

• 全文索引(FULLTEXT):全文索引，是目前搜索引擎使用的一种关键技术，或是在一个文章里面查找某个关键字的操作。

• 创建方式:ALTER TABLE 表名 ADD FULLINDEX 索引名称(列名1)

• 查看指定表的索引:show index from 表名

3.索引的优缺点

• 优点:a.提高查询数据的效率。帮助数据库服务器避免排序和临时表。

• 缺点:a.当我们执行更新(插入、删除、修改)表操作时，影响表的更新速度，因为mysql不仅要保存数据，还要保存索引文件。

• b.建立索引会占用磁盘空间，因为要存索引文件。如果你在一个大表上创建了多种组合索引,索引文件的会膨胀的很快。

• c.如果某个数据列包含许多重复的内容,为它建立索引就没有太大意义了。

• d.对于非常小的表 ,大部分情况下简单的全表扫描更高效;

4.索引的设计原则

• a.适合索引的列是出现在where子句中的列;

• b.更新十分频繁的字段上不宜建立索引，会更改B+树，这是十分消耗数据库性能的。

• c.区分度不大的字段上不宜建立索引:类似于性别这种区分度不大的字段,建立索引的意义不

  大。因为不能有效过滤数据,性能和全表扫描相当。返回数据在30%以外，优化器不会选择索引。

• d.业务上具有唯一特性的字段,即使是多个字段的组合，也必须建成唯一索引。 虽然唯一索引会影响insert速度，但 是对于查询的速度提升是非常明显的。另外，即使在应用层做了非常完善的校验控制，在并发的情况依然有脏数据产生。

• e.多表关联时，要保证关联字段上一定有索引。(外键字段要加索引)

5.索引的存储类型:

• InnoDB引擎使用的是B+Tree

6.索引的优化规则:

• a.如果MySQL估计使用索引比全表扫描还慢 ,则不会使用索引。返回数据比例（30%）是重要的参考指标，比例越低越容易命中索引

• b.前导(s前面%)模糊查询不能命中索引。 一 SELECT * FROM user WHERE name LIKE ‘%s%’;非前导模糊查询则可以使用索引 ,可优化为使用一SELECT . FROM user WHERE name LIKE ‘s%’’:

• c.数据类型出现隐式转换的时候不会命中索引，特别是列类型是字串，一定要将字符串常量值用引号引起来。

• d.复合索引的情况下 ,查询条件不包含索引列最左部分(不满足最左原则)，不会命中

复合索引。

• e.union、 in、 or都能够命中索引,建议使用in。

• f.用or分割开的条件,如果or前的条件中列有索引，而后面的列中没有索引，那么涉及到的索引都不会被用到。

• g.负向条件查询(!等于条件查询)不能使用索引,可以优化为in查询。数据库执行计算或函数不会命中索引。