MySQL中的索引

什么是索引

索引是一个单独的，存储在磁盘空间上的数据结构，通过索引我们能快速找到某个或多个列中有特定值的行。

索引优缺点

• 优点：可以加快检索速度，根据索引可以加快分组和排序
• 索引需要占据磁盘空间，一般是数据表的1.5倍；创建和维护索引需要时间，随着数据量增大而增大；而且索引会降低数据库插入，修改，删除的效率，因为还需要维护索引。

索引分类

• 普通索引
• 唯一索引（可以为空，不可重复） UNIQUE
• 主键唯一索引（非空唯一索引）PRIMARY KEY
• 联合索引（多个列构建的索引，这多个列中的值不能有空值，否则索引失效）
• 全文索引（大文本对象的列构建的索引）FULLTEXT

索引底层实现

索引由存储引擎实现，MySQL中索引的存储类型有两种B+ TREE和HASH和FULLTEXT，InnoDB和MyISAM只支持B+TREE索引、全文索引，Memary和Heap支持哈希索引。

• hash索引的优点：根据hash值查找，查找速度快，但是由于使用散列算法，数据存储比较分散，不适合区域查找。
• B+TREE：是树的度最大化，减少IO操作次数来增加查找速度。
• FULLTEXT全文索引：对于大文本对象，建立全文索引时，生成FULLTEXT索引时，会为文本生成一份单词的清单，在索引时及根据这个单词的清单来索引。

聚簇和非聚簇索引

MyISAM引擎实现的索引为非聚簇索引，InnoDB实现的索引为聚簇索引。

首先要介绍几个概念，在索引的分类中，我们可以按照索引的键是否为主键来分为“主索引”和“辅助索引”，使用主键键值建立的索引称为“主索引”，其它的称为“辅助索引”。因此主索引只能有一个，辅助索引可以有很多个。

• 非聚簇索引：其数据表和索引表是分开的，其主索引和辅助索引基本相同，根据索引键值找到的是数据库数据的真实存储物理地址，还需要再次去该地址读取数据，比聚簇索引多一次IO操作。
• 聚簇索引：数据和主键索引存储在一起，主索引存储数据本身，辅助索引存储对于数据的主键键值。

索引使用策略

什么时候使用索引？

• 主键自动建立唯一索引
• 经常查询、分组或者排序的列要建立索引
• 高并发条件下倾向组合索引

什么时候不用索引？

• 经常增删改的列不要建立索引
• 大量重复列不要建立索引

索引失效的情况？

• 组合索引中有列的值为null，如果有，那么这一列对组合索引就是无效的。
• 在一个SELECT语句中，索引只能使用一次，如果在WHERE中使用了，那么在ORDER BY中就不要用了。
• 不满足最左原则
• 在索引的列上使用表达式或者函数会使索引失效
• 在查询条件中使用不等于，包括<符号、>符号和！=会导致索引失效。特别的是如果对主键索引使用！=则不会使索引失效，如果对主键索引或者整数类型的索引使用<符号或者>符号不会使索引失效。（经erwkjrfhjwkdb同学提醒，不等于，包括<符号、>符号和！，如果占总记录的比例很小的话，也不会失效）
• 在查询条件中使用IS NULL或者IS NOT NULL会导致索引失效
• 字符串不加单引号会导致索引失效。更准确的说是类型不一致会导致失效

索引优化

• 满足最左原则
• 设置列为非null
• 使用短索引
• 不要在索引列上使用表达式或者函数