[ 笔记 ] mysql创建高性能的索引

索引及其类别

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

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

二、 B-Tree 索引

• 每个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历

• 对索引列是顺序结构存储的，可以来做ORDER BY 和 GROUP BY

• 存储索引列值，所以部分查询只使用索引就能够完成全部查询，无需扫面数据库

• 索引示例：

对于初学者来说这个看起来相当困惑，于是找了找关于* 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 索引的查询类型

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

• 如果不是按照索引的最左列开始查找的，则无法使用索引

• 不能跳过索引中的列

• 如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引优化查找

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

三、哈希索引

• 基于哈希表实现，只有精确匹配所有索引列的查询才有效

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

• 哈希索引的限制

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

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

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

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

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

• 使用哈希索引

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

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

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

四、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通过主键聚集数据

• InnoDB 和 MyISAM 的数据分布对比

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

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

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

主键不自动递增的缺点

6、覆盖索引

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

优点：

无法覆盖索引的实例：

7、使用索引扫描来排序

• 如果 EXPLAIN 出来的 type 列的值为 “index” 。则说明 MySql 使用了索引扫描来做排序

• 扫面索引本身是很快的，因为只需要从一条记录移到紧接着的下一条记录。

• 但如果索引不能覆盖查询所需的列，那就不得不每扫描一条索引记录都回表查询一次对应的行。这基本上都是随机 I/O，因此按索引顺序读取的速度通常比顺序的全表扫描慢， 尤其是在 I/O 密集型工作负载时。

• 只有当索引的列的顺序和 ORDER BY 子句的顺序完全一致，并所有列的排序方向（倒序或者正序）