MySQL索引

定义

索引 (在MySQL中也叫"键key" )是存储引擎快速找到记录的一种数据结构。

索引类型

1. 主键索引（PRIMARY KEY）：它是一种特殊的唯一索引，不允许有空值。

   创建：一般是在建表的时候同时创建主键索引。一个表只能有一个主键索引。

2. 唯一索引（UNIQUE）：唯一索引列的值必须唯一，但允许有空值。

   创建： ALTER TABLE 表名 ADD UNIQUE (字段名称)

3. 普通索引（INDEX）：最基本的索引，它没有任何限制。

   创建：ALTER TABLE 表名 ADD INDEX 索引名称(字段名称)

4. 组合索引（INDEX）：一个索引包含多个列 ,多用于避免回表查询。

   创建：ALTER TABLE 表名 ADD INDEX 索引名称(字段名称1,字段名称2字段名称N)

5. 全文索引（FULLTEXT）：全文检索,是目前搜索引擎使用的一种关键技术。

   创建：ALTER TABLE 表名 ADD FULLTEXT (字段名)

查看指定索引列表

​ show index from表名;

索引的优缺点

• 优点

  1. 索引大大减小了服务器需要扫描的数据量。
  2. 索引可以帮助服务器避免排序和临时表。

• 缺点

  1. 降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据,还要保存索引文件。
  2. 建立索引会占用磁盘空间的索引文件。如果在一个大表上创建了多种组合索引,索引文件会很快膨胀。
  3. 如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
  4. 对于非常小的表 ，大部分情况下简单的全表扫描更高效。

索引设计的原则

1. 适合索引的列是出现在where子句中的列。
2. 更新十分频繁的字段上不宜建立索引（更新操作会变更B+树，重建索引，非常消耗数据库性能）。
3. 区分度不大的字段上不宜建立索引（类似于性别这种区分度不大的字段，建立索引的意义不大。因为不能有效过滤数据，性能和全表扫描相当。另外返回数据的比例在30%以下的情况下，优化器不会选择使用索引）。
4. 业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。 虽然唯一索引会影响insert速度，但是对于查询的速度提升是非常明显的。另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，在并发的情况下，依然有脏数据产生。
5. 多表关联时，要保证关联字段上一定有索引。(外键字段要加索引)

索引存储类型

​ InnoDB引擎使用的是B+Tree。

索引优化规则

1. 如果MySQL估计使用索引比全表扫描还慢 ,则不会使用索引。（返回数据的比例是重要的指标,比例越低越容易命中索引。范围值：30%以内）
2. 前导模糊查询不能命中索引。（如：SELECT * FROM user WHERE name LIKE '%s%';）
3. 非前导模糊查询则可以使用索引 ，可优化为使用非前导模糊查询。（SELECT * FROM user WHERE name LIKE 's%:）
4. 数据类型出现隐式转换的时候不会命中索引，特别是当列类型是字符串,一 定要将字符常量值用引号引起来。（错误 : SELECT * FROM user WHERE name=l;正确 : SELECT * FROM user WHERE name=T;）
5. 复合索引的情况下 ，查询条件不包含索引列最左边部分(不满足最左原则)，不会命中符合索引。
6. union、 in、 or都能够命中索引,建议使用in（效率高）。
7. 用or分割开的条件，如果or前的条件中列有索引：而后面的列中没有索引，那么涉及到的索引都不会被用到。
8. 负向条件查询不能使用索引，可以优化为in查询。
9. 数据库执行计算或函数不会命中索引。