第五章 创建高性能的索引

索引是存储引擎用于快速找到记录的一种数据结构。对于良好的性能非常关键。
是对查询性能优化最有效的手段了。

5.1 索引基础

MYSQL现在索引中找到对应的值，然后根据匹配的索引记录找到对应的数据行。

5.1.1 索引的类型

索引是在存储引擎层实现的。
B-Tree索引
值按顺序存储，每个叶子页到根的距离相同。适合超找范围数据。
不需要全表扫描，而从索引的根节点开始进行搜索。
查询类型：全键值，键值范围或前缀查找。

CREATE TABLE People (
    last_name varchar(50) not null,
    first_name varchar(50) not null,
    dob date not null,
    gender enum('m', 'f')not null,
    key(last_name, first_name, dob) #索引
); 

索引的限制：
1.必须从最左列开始查找，否则无法使用索引。people表必须从last_name开始查找
2.不能跳过索引中的列。不能跳过first_name列，否则从最左列开始索引(last_name)
哈希索引
只有精确地匹配索引中的每一列，查询才有效。

5.2 索引的优点

1.减少服务器扫描的数据量
2.避免排序和临时表
3.将随机IO编程顺序IO
索引并不是最好的解决方案：
1.非常小的表，全表扫描更高效。
2.非常大的表，例如TB级别，使用分表更好。

5.3 高性能索引策略

5.3.1 独立的列
查询中必须是独立的列才能使用索引。
独立的列：列不能是表达式的一部分，也不能是函数的参数：
Ex:

mysql> SELECT actor_id from sakila.actor WHERE actor_id + 1 = 5; 

不能使用actor_id索引，
应改为actor_id = 4

5.3.2 前缀索引和索引选择性

BLOB,TEXT，或者很长的VARCHAR类型的列，必须使用前缀索引。

5.3.3 多列索引

在多个列上建立独立的单独索引大部分情况下并不能提高MYSQL的查询性能。

5.3.4 索引顺序选取原则：经验法则，将选择性最高的列放在最前面

5.3.5 聚簇索引

聚簇索引是一种数据存储方式。InnoDB的聚簇索引实际是在同一个结构中保存了B-Tree索引和数据行。
表有聚簇索引时，数据行实际存放在索引的叶子页中。
一个表只能有一个聚簇索引。

5.3.6覆盖索引

定义：包含所有需要查询字段的值得索引。
查询只需要扫描索引，不需要返回。
好处：
• 索引条目常远小于数据行大小，极大地减少数据访问量。对缓存的负载非常重要。
• 索引按照列值顺序存储，可以减少IO开销
• 覆盖索引对InnoDB表特别有效
不是所有类型的索引都可以成为覆盖索引。覆盖索引必须存储索引列的值。
MYSQL只能使用B-Tree索引做覆盖索引。
MYSQL不能在索引中执行LIKE操作。

5.3.7使用索引扫描来排序

两种生成有序结果的方式：
1 通过排序操作。
2 按索引顺序扫描【explain出来的type列的值为”index”】
由于索引不能覆盖查询所有列，故比顺序全表扫描慢。
索引排序的必要条件：
索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序的方向[倒序或正序]都一致的条件下
大多数情况下都不需要冗余索引和重复索引，表中的索引越多插入速度会越慢。

5.3.11 索引和锁

索引可以让查询锁定更少的行。
InnoDB只有访问行的时候才会对其加锁，而索引能减少InnoDB访问的行数，从而减少锁的数量。
即使使用了索引，也可能锁住一些不需要的数据。
InnoDB在二级索引上使用共享锁，但访问主键使用排它锁。

5.5 维护索引和表

维护表的主要目的：
• 找到并修复损坏的表
• 维护准确的索引统计信息
• 减少碎片

5.5.1 找到并修复损坏的表

检查表格状态：

CHECK table tt;

修复表格：

 repair table tt;

表出现损坏的原因：硬件问题或者磁盘问题。
不存在什么查询能让InnoDB表损坏

5.5.2 更新索引统计信息

5.5.3 减少索引和数据的碎片

B-Tree索引可能会碎片化
数据碎片类型：
行碎片：数据行被存储为多个地方的多个片段中
行间碎片：逻辑上顺序的页，行在磁盘上不是顺序存储
剩余空间碎片：数据页中有大量的空余空间。
减少碎片方式：
执行OPTIMIZE TABLE或者在导入的方式