索引及其类别

一、索引：存储引擎用于快速找到记录的一种数据结构

  • 索引是存储引擎层而不是服务器层实现

二、 B-Tree 索引

  • 每个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历
  • 对索引列是顺序结构存储的，可以来做ORDER BY 和 GROUP BY
  • 存储索引列值，所以部分查询只使用索引就能够完成全部查询，无需扫面数据库
  • 索引示例：

mysql 很快但加上排序就很慢_ci

对于初学者来说这个看起来相当困惑，于是找了找关于* B-Tree*的知识

详情请参考： B-Trees: Balanced Tree Data Structures

  • B-Tree ：Balanced Tree Data Structures

1、Sample B-Tree

2、Searching a B-Tree for Key 21

3、Inserting Key 33 into a B-Tree (w/ Split)

继续索引示例：

  • 索引对多个值进行排序的依据是CREATE TABLE语句中定义索引时列的顺序
  • B-Tree索引适用于全键值、键值范围或键前缀查找（只适用于根据最左前缀查找）

1、可以使用 B-Tree 索引的查询类型

mysql 很快但加上排序就很慢_服务器_02

2、关于 B-Tree 索引的一些限制

  • 如果不是按照索引的最左列开始查找的，则无法使用索引
  • 不能跳过索引中的列
  • 如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引优化查找

有些限制并不是B-Tree本身导致的，而是MySql优化器和存储引擎使用索引方式导致的。

三、哈希索引

  • 基于哈希表实现，只有精确匹配所有索引列的查询才有效
  • MySql 中，只有 Memory 引擎显示支持哈希索引，这也是 Memory 引擎表的默认索引类型， Memory 引擎同时支持 B-Tree 索引。值得一提的是， Memory 引擎是支持非唯一哈希索引的；如果多个列的哈希值相同。索引会已链表的方式存放多个记录指针到同一个哈希条目中。

mysql 很快但加上排序就很慢_服务器_03

  • 哈希索引的限制

1、哈希索引只包含哈希值和行指针，而不存储字段值，索引不能使用索引中的值来避免读取行。

2、无法排序，因为哈希索引并不是按照索引值顺序存储的
3、哈希索引也并不支持部分索引列匹配查找，因为始终是使用索引列的全部内容来计算哈希值的。

4、哈希索引只支持等值比较，包括 = ， IN() , <=> （注意<> 和 <=> 是不同的操作）；也不支持任何范围查询。

5、访问哈希索引的数据非常快，除非有很多哈希冲突（不同的索引列值确有相同的哈希值）。

4、如果哈希冲突很多的话，一些索引维护操作的代价也很高。

  • 使用哈希索引

1、哈希值的生成（整数组成）


CRC32() 会出现大量哈希冲突 SHA1() 和 MD5() 是强加密函数，浪费存储空间，降低性能，比较哈希值时间变长 截取 MD5() 中部分值，方便快捷，也不影响性能 FNV64() 速度快，冲突比 CRC32() 少很多


2、使用触发器维护哈希索引
3、使用 where = “常量” 来解决哈希冲突

mysql 很快但加上排序就很慢_mysql_04

四、R-Tree（空间数据索引）

MySql 中很少使用

五、全文索引

索引的优点

  • 索引大大减少了服务器需要扫描的数据量
  • 索引可以帮助服务器避免排序和临时表
  • 索引可以将随机I/O变为顺序I/O

高性能的索引策略

1、独立的列：索引列不能是表达式的一部分，也不能是函数的参数

常见错误

SELECT actor_id FROM salila.actor WHERE actor_id + 1 = 5;
SELECT ... WHERE TO_DAYS(CURRENT_DAY) - TO_DAYS(date_col) <= 10;

2、前缀索引和索引选择性

索引的选择性：不重复的索引值（也称为基数，cardinality）和数据表的记录总数（#T）的比值，范围从 1/#T 到 1 之间。

  • 计算完整列的选择性
SELECT CONUT(DISTINCT city)/COUNT(*) FROM sakila.city_demo;
  • 测试不同前缀的选择性
SELECT 
    COUNT (DISTINT LEFT(city, 4))/COUNT(*) AS sel4，
    COUNT (DISTINT LEFT(city, 5))/COUNT(*) AS sel5，
    COUNT (DISTINT LEFT(city, 6))/COUNT(*) AS sel6，
    COUNT (DISTINT LEFT(city, 7))/COUNT(*) AS sel7，
FROM sakila.city_demo;
  • 创建前缀索引
ALTER TABLE sakila.city_demo ADD KEY (city(7));
  • 前缀索引的优缺点
    优点：能使索引更小、更快的有效办法
    缺点：MySql 无法使用前缀索引做 ORDER BY 和 GROUP BY ，也无法使用前缀索引做覆盖扫描

3、多列索引

索引合并策略有时候是一种优化的结果，但实际上更多时候说明了表上的索引建的很糟糕：

  • 当出现服务器对多个索引做相交操作时（通常有多个 AND 条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的索引。
  • 当出现服务器对多个索引做联合操作时（通常有多个 OR 条件），通常需要消耗大量 CPU 和内存资源在算法的缓存、排序和合并上操作。特别是当其中有些索引的选择性不高，需要合并扫描返回大量数据的时候。
  • 更重要的是，优化器不会把这些计算到“查询成本”中，与化器关心随机页面读取。这会使得查询成本被“低估”，导致该执行计划还不如直接走全表扫描。这样不但会消耗更多的 CPU 和内存资源，还可能影响查询的并发性，但是如果单独运行这样的查询往往会忽略对并发性的影响。

注：后面两点并不懂，先记下吧

4、选择合适的索引序列（适合 B-Tree 索引）

  • 检验法则：将选择性最高的的列放到索引最前面（选择性高：通过此列分组获得的组最多）
  • pt-query-digest 报告最坏“查询”确定索引顺序

5、聚簇索引

  • 不是一种单独的索引类型，而是一种数据存储方式。具体的细节依赖于其实现方式，但 InnoDB 的聚簇索引实际上在同一结构中保存了 B_Tree 索引的数据行。
  • InnoDB通过主键聚集数据

mysql 很快但加上排序就很慢_ci_05

  • InnoDB 和 MyISAM 的数据分布对比

主要是在聚集索引和非聚集索引的数据分布，主键索引和二级索引的数据分布方面不同

MyISAM 的数据分布： MyISAM 按照数据插入的顺序存储在磁盘上

mysql 很快但加上排序就很慢_mysql_06

mysql 很快但加上排序就很慢_ci_07

mysql 很快但加上排序就很慢_ci_08

  • 在 InnoDB 表中按主键顺序插入行

mysql 很快但加上排序就很慢_服务器_09

mysql 很快但加上排序就很慢_服务器_10

mysql 很快但加上排序就很慢_mysql 很快但加上排序就很慢_11

mysql 很快但加上排序就很慢_mysql 很快但加上排序就很慢_12

主键不自动递增的缺点

mysql 很快但加上排序就很慢_mysql 很快但加上排序就很慢_13

6、覆盖索引

覆盖索引：如果一个索引包含（或者说覆盖）所需要查询的字段的值，我们就称之为“覆盖索引”

优点：

mysql 很快但加上排序就很慢_mysql 很快但加上排序就很慢_14

无法覆盖索引的实例：

mysql 很快但加上排序就很慢_mysql_15

mysql 很快但加上排序就很慢_mysql 很快但加上排序就很慢_16

7、使用索引扫描来排序

  • 如果 EXPLAIN 出来的 type 列的值为 “index” 。则说明 MySql 使用了索引扫描来做排序
  • 扫面索引本身是很快的，因为只需要从一条记录移到紧接着的下一条记录。
  • 但如果索引不能覆盖查询所需的列，那就不得不每扫描一条索引记录都回表查询一次对应的行。这基本上都是随机 I/O，因此按索引顺序读取的速度通常比顺序的全表扫描慢， 尤其是在 I/O 密集型工作负载时。
  • 只有当索引的列的顺序和 ORDER BY 子句的顺序完全一致，并所有列的排序方向（倒序或者正序）