MySQL 索引

索引（Index ）是数据库优化中最常用也是最重要的手段之一，通过索引通常可以解决大多数的 SQL 性能问题。索引是帮助 MySQL 高效获取数据的数据结构，它用于快速找出在某个列中含有某一特定值的行。
索引的本质是空间换时间 ，通过索引这个缓存来提高数据查询的效率。 在MySQL 中，InnoDB 和 MyISAM 存储引擎使用的是B+树索引，Memory使用的是hash索引。

一、索引的优缺点

1、优点

• 索引大大减小了服务器需要扫描的数据量；
• 索引可以帮助服务器避免排序和临时表；
• 索引可以将随机IO变成顺序IO；

2、缺点

• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存索引文件；
• 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。
• MySQL里同一个数据表里的索引总数限制为16个。

二、索引的分类

1. 主键索引：将主键字段作为索引列；
2. 唯一索引：将唯一字段作为索引；
3. 普通索引：将非主键非唯一的字段作为索引列；
4. 全文索引：数据库中用的很少，一般用来全文检索（lucene,solr,es）；
5. 组合索引：表中的多个字段值可以共同构成索引； 唯一约束保证在一个字段或者一组字段里的数据与表中其它行的数据相比是唯一的。

三、聚簇索引和非聚簇索引

从物理存储角度来分， 索引可以分为聚簇索引和非聚簇索引（二级索引），区别主要看叶子节点存了什么数据。
在《数据库原理》一书中是这么解释聚簇索引和非聚簇索引的区别的：聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。

1、InnoDB

由于聚簇索引是将数据跟索引结构放到一块，因此一个表仅有一个聚簇索引。
聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。
InnoDB 中，在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引，辅助索引叶子节点存储的不再是行的物理位置，而是对应主键值。

2、MyISM

MyISM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

3、聚簇索引的优势

1. 由于行数据和叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中，再次访问的时候，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快；
2. 辅助索引使用主键作为"指针"而不是使用地址值作为指针的好处是，减少了当出现行移动或者数据页分裂时辅助索引的维护工作，使用主键值当作指针会让辅助索引占用更多的空间，换来的好处是InnoDB在移动行时无须更新辅助索引中的这个"指针"；
3. 聚簇索引适合用在排序的场合，非聚簇索引不适合；
4. 取出一定范围数据的时候，使用用聚簇索引；
5. 二级索引需要两次索引查找（回表），而不是一次才能取到数据，因为存储引擎第一次需要通过二级索引找到索引的叶子节点，从而找到数据的主键，然后在聚簇索引中用主键再次查找索引，再找到数据。

4、聚簇索引的劣势

1. 维护索引很昂贵，特别是插入新行或者主键被更新导至要分页的时候。建议在大量插入新行后，选在负载较低的时间段，通过OPTIMIZE TABLE优化表，因为必须被移动的行数据可能造成碎片。使用独享表空间可以弱化碎片；
2. 如果主键比较大的话，那辅助索引将会变的更大，因为辅助索引的叶子存储的是主键值；过长的主键值，会导致非叶子节点占用占用更多的物理空间。

5、为什么主键通常建议使用自增id

1. 聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，会不断地调整数据的物理地址、分页；如果是自增的，只需要一 页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高；
2. MyISAM 的索引并非聚簇索引，他的数据的物理地址必然是凌乱的，拿到这些物理地址，按照合适的算法进行I/O读取，于是开始不停的寻道不停的旋转。聚簇索引则只需一次I/O。

四、Hash索引

哈希索引基于哈希表实现，只有精确索引所有列的查询才有效。
对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据的指针。
MySQL中，只有 Memory 存储引擎显示支持hash索引，是 Memory 表的默认索引类型，尽管Memory表也可以使用 B-Tree 索引。
Memory存储引擎支持非唯一hash索引，这在数据库领域是罕见的：如果多个值有相同的hash code，索引把它们的行指针用链表保存到同一个hash表项中。

1、Hash索引的限制

1. 由于索引仅包含hash code和记录指针，所以，MySQL不能通过使用索引避免读取记录，即每次使用哈希索引查询到记录指针后都要回读查取数据；
2. 不能使用hash索引排序；
3. Hash索引不支持键的部分匹配，因为是通过整个索引值来计算hash值的；
4. Hash索引只支持等值比较；
5. 访问Hash索引的速度非常快，除非有很多哈希冲突（不同的索引列值却有相同的哈希值）。当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找到所有符合条件的行；
6. 如果哈希冲突很多的话，一些索引维护操作的代价也会很高。当从表中删除一行时，存储引擎要遍历对应哈希值的链表中的每一行，找到并删除对应行的引用，冲突越多，代价越大。
   2、自适应哈希索引
   InnoDB引擎有一个特殊的功能叫做“自适应哈希索引”，由Mysql自动管理，不需要人为干预。默认情况下为开启，可以通过参数 innodb_adaptive_hash_index 来禁用此特性。
   当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于缓冲池中的B+ Tree索引上再创建一个哈希索引，这样B-Tree索引也具有哈希索引的一些优点，比如快速的哈希查找。
   InnoDB官方文档显示，启用自适应哈希索引后，读和写性能可以提高2倍，对于辅助索引的连接操作，性能可以提高5倍。

五、索引优化

1. 索引字段尽量使用数字型：若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了；
2. 尽量不要让字段的默认值为NULL：在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。而且索引不会包含有 NULL 值的列，只要列中包含有 NULL 值都将不会被包含在索引中，复合索引中只要有一列含有 NULL 值，那么这一列对于此复合索引就是无效的；
3. 前缀索引：对串列进行索引，如果可能应该指定一个前缀长度。对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为MYSQL不允许索引这些列的完整长度。前缀索引是一种能使索引更小、更快的有效办法，但另一方面也有其缺点：MySQL无法使用前缀索引做order by和group by，也无法使用前缀索引做覆盖扫描；
4. 提高索引的选择性：索引的选择性是指，不重复的索引值（基数）和数据表中的记录总数的比值。索引的选择性越高则查询效率越高；
5. like语句不要以通配符开头：对于LIKE，在以通配符 % 和 _ 开头作查询时，MySQL不会使用索引；
6. 不要在列上进行运算：索引列不能是表达式的一部分，也不是是函数的参数；
7. 尽量不要使用 NOT IN、<>、!= 操作：应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。对于not in，可以用 not exists 来代替。例如： select num from a where num in (select num from b) 用下面的语句替换： select num from a where exists (select 1 from b where num=a.num)。对于<>，用其它相同功能的操作运算代替，如 a<>0 改为 a>0 or a<0；
8. or 条件：用 or 分割开的条件，如果 or 前的条件中的列有索引，而后面的列中没有索引，那么涉及到的索引都不会被用到；
9. 组合索引的使用要遵守“最左前缀”原则’：
   • 查询必须从索引的最左边的列开始，否则无法使用索引；
   • 不能跳过某一索引列。
10. 使用索引排序时，ORDER BY也要遵守“最左前缀”原则：
    • 当索引的顺序与ORDER BY中的列顺序相同，且所有的列是同一方向（全部升序或者全部降序）时，可以使用索引来排序；
    • ORDER BY子句和查询型子句的限制是一样的：需要满足索引的最左前缀的要求，有一种情况下ORDER BY子句可以不满足索引的最左前缀要求，那就是前导列为常量时：WHERE子句或者JOIN子句中对前导列指定了常量；
    • 如果查询是连接多个表，仅当ORDER BY中的所有列都是第一个表的列时才会使用索引。
11. 如果列类型是字符串，那么一定记得在 where 条件中把字符常量值用引号引起来，否则的话即便这个列上有索引。