高性能mysql_高性能索引

索引基础：

1. 索引类型
   索引在存储引擎层（不是服务器层）

B树索引：
索引有效范围（以姓 名 生日 的顺序为索引）：
全值匹配（所有具体姓 以及其名 生日等信息）
最左前缀（仅姓为索引）
匹配列前缀（姓索引列的开头字母等 J%这样的）
匹配范围值（姓索引列的范围 比较 BOB 到 JACY之间）
精准前列 范围后列（姓精准 名范围）
只访问索引的查询（覆盖索引？？？）

规则：
1 最左前
2 范围查询后的匹配无法使用索引

哈希索引：
每一个键值对 对应一个哈希码，哈希索引将所有哈希码存在索引中，同时在哈希表中保存每个数据行的指针
查询时，先解析查询字段的哈希值，然后寻找记录指针，最后比较第n行是否为目的行
mysql中 memory引擎支持哈希索引（支持非唯一哈希索引。多个列的哈希值相同，会以链表的方式存放多个记录指针到同一个哈希条目中）
限制：
1 并非索引值顺序存储，无法用于排序
2 不支持部分索引查询，（A，B）索引生成的哈希值，单查询A的哈希值是不对的
3 只支持等值查询 = in <=>，不支持范围查询
4 哈希冲突时需要遍历table中的所有行指针 逐行比较
5 哈希冲突过多时，会在冲突多的列上建立哈希索引。删除时需要一直遍历找到并删除对应行的引用。冲突越多，代价越大

对于innodb，自适应哈希索引，对于某些索引值被使用得非常频繁时，会在内存中基于B树索引之上创建一个哈希索引，更加快速。（自动行为，可关闭）

索引的优点：
1 减少服务器需要扫面的数据量
2 避免排序以及临时表
3 将随机IO变为顺序IO

高性能索引策略
1 独立列，不能时表达式的一部分，否则无法使用索引
2 前缀索引，使索引更小更快，选取基数和原来差不多；缺点无法使用order by以及group by，无法使用前缀索引做覆盖扫描（后缀索引 把存储倒过来）
3 索引合并，多个单列索引合并使用，一般先并再交或者先交再并
4 合适的索引顺序，考虑全局基数与选择性，将选择性高的作为索引的前列；最左前原则
5 聚簇索引，不是数据结构，是存储的方式；数据和和相邻的键值紧凑存储在一起；
优点 相关数据保存在一起，减少每次寻找的io；访问更快；主键直接使用
缺点 数据全放内存中，访问顺序不再重要；插入速度 依赖于插入顺序；更新待告高，强制将更新行移动到新位置；页分裂导致磁盘空间减少；全表扫描变慢，稀疏数据 不连续 分页情况等；二级索引低效化，占用高，次数高

聚簇索引是 行数据就在叶节点上；非聚簇索引是叶节点存储行，需要辅助索引二次查找

6 覆盖索引，一个索引包含所有需要查询的字段的值。索引条目小，减少数据访问量；myisam只在内存中缓存索引，数据依赖于OS，访问数据需要系统调用；innodb可以避免主键索引的二次查询

7 索引扫描排序。
mysql排序：排序操作 或者 索引顺序扫描
索引不能覆盖查询所需的列，则需要每扫描一套索引记录就回表查询一次对应的行（随机IO）；按照索引顺序读取数据的速度通常比顺序全表扫描慢
设计索引，最好满足 排序 + 查找行
查询时，order by后面也要满足最左前原则（或者where指定第一个常数时，用到了第一个索引 where A = … order by B,C A >… order by A,B）
无法使用索引排序的情况：
1）where A = … order by A desc,B asc 索引时正序排序的（这里使用了两种不同的排序方式）
2）where A = … order by B,D D不在索引中
3）where A = … order by C 不符合最左前
4）where A> … order by B,C 第一列范围条件，后面无法使用（要使用的话必须得排序按照ABC得顺序来）
5）where A=… and B in(1,2) order by C 第二列范围查询，后面无法使用索引排序
（使用索引做排序的最重要用法就是 查询同时有 order by 和limit子句）

8 压缩索引
myisam使用前缀压缩来减少索引的大小
完全保留第一个值，其他值与第一个值比较得到相同前缀的字节数以及剩余的不同后缀部分（perform performance—7,ance）；行指针也采用类似的前缀压缩
CPU密集型，压缩将导致查询变慢；IO密集型对某些查询带来的好处比成本多很多
create table 的 pack_keys指定索引压缩方式

9 冗余与重复索引
重复：一般primary key unique index 这三个操作都是重复创建索引； 特例：同一列创建不同类型索引来满足查询需求（key fulltext key）
冗余： AB 后创建 A；大多情况不需要（当需要添加一个长varchar列扩展时，性能会急剧下降，特别当作覆盖索引或者范围查询（因为索引压缩））
冗余 重复 导致插入操作更加速度下降

10 未使用的索引
建议删除

11 索引与锁
索引会染个查询使用更少的行，从不访问的行会不被锁定

1）innodb行锁效率更高，内存使用更少，锁行仍然会带来额外开销
2）锁定超过需要的行会增加锁争用减少并发性

新版本innodb可以在服务端过滤行后就释放锁，旧版本要提交事务以后才行
innodb在二级索引上使用共享（读锁），访问主键索引需要排他（写锁）锁

12 优化排序
使用order 和 limit时 需要花费大量时间扫描需要丢弃的数据
反范式化 预先计算 缓存可以解决这类查询问题（或者限制用户翻页数量）
延迟关联：覆盖索引查询需要的主键，再根据主键关联原表获取需要的行
check table 查找错误

总结：

1. 单行访问很慢，最好能从一次IO读取的块中获取更多的行信息
2. 顺序访问范围数据很快，IO不需要多次寻道，比随机IO快很多；按照顺序读取的数据不需要额外的排序操作，gtoup by也不需要再做排序和行聚合计算了
3. 覆盖索引很快，避免了回表查找行