数据库-索引

1.什么是索引？

索引（在MySQL中也叫“键key”）是存储引擎快速找到记录的一种数据结构，在MySQL中默认使用的是InnoDB引擎，InnoDB引擎使用的是B+Tree。

2.索引类型

• 主键索引（PRIMARY KEY）
  它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引。注意：一个表只能有一个主键索引。
• 唯一索引（UNIQUE）
  唯一索引列的值必须唯一，但允许有空值。

  ALTER TABLE 表名 ADD UNIQUE（字段名称）  
  

• 普通索引（INDEX）
  最基本的索引，它没有任何限制

  ALTER TABLE 表名 ADD INDEX（字段名称）  
  

• 组合索引（INDEX）
  一个索引包含多个列，多用于避免回表查询

  ALTER TABLE 表名 ADD INDEX（字段名称1，字段名称2，字段名称n.）  
  

• 全文索引（FULLTEXT）
  全文检索，是目前搜索引擎使用的一种关键技术

  ALTER TABLE 表名 ADD FULLTEXT（字段名称）  
  

3.查看指定表的索引

SHOW INDEX FROM 表名；  

4.索引的优缺点

• 优点：
  1.索引大大减少了服务器需要扫描的数据量
  2.索引可以帮助服务器避免排序和临时表
• 缺点：
  1.降低更新表的速度，如对表进行INSER、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存索引文件。
  2.建立索引会占用磁盘空间的索引文件，一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件会膨胀很快。
  3.如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
  4.对于非常小的表，大部分情况下简单的全表扫描更高效。

5.索引设计的原则

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

6.索引优化规则

• 如果MySQL估计使用索引比全表扫描还慢，则不会使用索引。返回数据的比例是一个重要的指标，比例越低越容易命中索引。记住这个范围值：30%，后面所讲的内容都是建立在返回数据的比例在30%以内的基础上。
• 前导模糊查询不会命中索引。

   SELECT * FROM user WHERE name LIKE '%s%'
  

• 非前导模糊查询则可以使用索引，可优化为使用非前导模糊查询。

  SELECT * FROM user WHERE name LIKE 's%'
  

• 数据类型出现隐式转换的时候不会命中索引，特别是当列类型是字符串，一定要将字符常量值用引号引起来。
• 复合索引的情况下，查询条件不包含索引列最左边部分（不满足最左原则），不会命中复合索引。
• union、in、or 都能命中索引，建议使用in。
• 用or分割开的条件，如果or前的条件中列有索引，而后面的列中没有索引，那么涉及到的索引都不会被用到。
• 负向条件查询不能使用索引（where中条件判断为不等于），可以优化为in查询。
• 数据库执行计算或函数不会命中索引。