索引
正确的创建合适的索引是提升数据库查询性能的基础。
索引定义
索引是为了加速对表中数据行的检索而创建的一种分散存储的数据结构(硬盘级)。
使用索引意义
- 索引能极大的减少存储引擎需要扫描的数据量。
- 索引可以把随机IO变成顺序IO。
- 索引可以帮助我们在进行分组、排序等操作时，避免使用临时表。
使用B+树的意义
二叉查找树 Binary Search Tree
平衡二叉查找树 Balanced Binary Search Tree
二叉树和平衡二叉树的缺点：
- 树结构太深
因为每个节点都存有数据，数据处的(高)深度决定着他的IO操作次数，IO操作耗时大。
数据存储太小
- 每一个磁盘块(节点/页，单位4kb)保存的数据量(远远不足4k)太小了
- 没有很好的利用操作磁盘IO的数据交换特性，也没有利用好磁盘IO的预读能力(空间局部性原理，预读8k、12k等)，从而带来频繁的IO操作。
多路平衡查找树 B-Tree :
平衡查找树的分支数量与关键字大小有关，可以大致认为：磁盘块的容量/关键字大小 = 平衡树分支数量。
加强版的多路平衡查找树 Mysql的B+Tree :
B+Tree与B-Tree的区别
- B+节点关键字搜索采用闭合区间
- B+非叶节点不保存数据相关信息，只保存关键字和子节点的引用
- B+关键字对应的数据保存在叶子节点中
- B+叶子节点是顺序排列的，并且相邻节点具有顺序引用的关系
选择B+树的原因：
- B+树是B-树的变种(PLUS版)多路绝对平衡查找树，他拥有B-树的优势
- B+树扫库、表能力更强
- B+树的磁盘读写能力更强
- B+树的排序能力更强
- B+树的查询效率更加稳定
MySql中B+Tree索引的体现形式
Myisam:数据和索引分开存储，数据保存在MYD文件，索引文件为MYI。
Myisam双索引：
Innodb 索引结构：
未指定索引的情况下InnoDB会自动生成隐式索引:
使用辅助索引查询某项具体信息时，需要反向到主键索引中查找相关信息（回表操作）。
这样设计的好处就是在数据迁移的时候辅助索引可以不做作相应的指向改变。
对于InnoDB的辅助索引，它的叶子节点存储的是索引值和指向主键索引的位置。
Myisam 与InnoDB对比：
)
索引知识点汇总
- 列的离散性 ：
离散性低的索引会造成选择性差，无法寻找合适的分支，数库会使用全局扫描。类似男女这种字段如果简历索引则要遍历位图索引。
- 最左匹配原则
对索引中关键字进行计算(对比)，一定是从左往右依次进行(每一位)，且不可跳过。
- 联合索引
- 单列索引：节点中关键字[name]
- 联合索引：节点中关键字[name,phoneNum]
- 单列索引是特殊的联合索引
联合索引列选择原则 :
- 经常用的列优先 【最左匹配原则】
- 选择性(离散度)高的列优先【离散度高原则】
- 宽度小的列优先【最少空间原则】
覆盖索引
- 如果索引包含所有满足查询需要的数据的索引成为覆盖索引(Covering Index)，也就是平时所说的不需要回表操作。
- 使用explain，可以通过输出的extra列来判断，对于一个索引覆盖查询，显示为using index，MySQL查询优化器在执行查询前会决定是否有索引覆盖查询。
- 覆盖索引可减少数据库IO，将随机IO变为顺序IO，可提高查询性能。
索引使用的注意点
- 索引列的数据长度能少则少。
- 索引一定不是越多越好，越全越好，一定是建合适的。
- 匹配列前缀可用到索引 like 9999%，like %9999%、like %9999用不到索引; Where 条件中 not in 和 <>操作无法使用索引;
- 匹配范围值，order by 也可用到索引;
- 多用指定列查询，只返回自己想到的数据列，少用select *;
- 联合索引中如果不是按照索引最左列开始查找，无法使用索引;
- 联合索引中精确匹配最左前列并范围匹配另外一列可以用到索引;
- 联合索引中如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引;