MySQL高级（一） 索引 数据结构

1索引：

概念：帮助mysql高效获取数据的 ！排好序！ 的 数据结构 (key - value)

1.1数据结构

二叉树：BST树

​ 如果1234567，跟链表一样，与全表扫描一样（二分查找：log(n)）

平衡二叉树：AVL树

​ 最短子树、最高子树 高度差 不能超过1

红黑树：BRT树

​ 平衡树，但是大数据时层数太多，IO也是很多，最长路径不超过最短路径的2倍

B-Tree：

​ 叶子节点没有指针，范围查找不友好

​ 非叶子节点存储data和索引

B+Tree：

​ 非叶子节点存冗余索引不存data，叶子节点存data和所有索引。多叉平衡树！从左到右递增。
​ 叶节点：16kb、内存随机匹配 innodb_page_size
​ B+树指针：双向链表
​ 每个叶子节点限定索引个数max.Degree，所以能保证B+数层数3~4层
​ B+树可以有很多个，但是有原始数据的B+树只有一个；其他普通索引的B+树存的主键值

hash表：

​ 范围查找不合适;无序；InnoDB有hash索引，叫自适应hash

1.2索引分类

​ 主键索引:不允许空值（mysql默认会为 主键、唯一键 创建索引）
​ 唯一索引:允许空值
​ 普通索引:既不是主键、又不是唯一
​ 全文索引:luncene、solr
​ 组合索引:多个列 (联合索引)

1.2.1联合索引(组合索引)

​ 最左前缀：不能跳过前面的索引；因为联合索引，是根据name、age、position进行排序的。所以跳过name的话，直接查找age时，age是无序的，在name相同时，age才是有序的。

​

1.3索引相关概念

回表 ：

根据普通列的索引的B+树查找，找到后根据key(主键)，查找主键的带原始数据的B+树中查询。

索引覆盖：

select id,name ;name加索引 ，where name=“张三”。为了不回表

最左匹配：

省份、城市、城镇 。 优化器会调整顺序， 如：name age 索引，where age=? and name=?也可以匹配

索引下推：

name age索引
​ 5.6之前：先根据name去存储引擎拿所有数据，然后在server层对age数据过滤
​ 5.6之后：根据name,age两列直接获取数据
​

优化器：

​ COB:基于成本的优化
​ RBO:基于规则的优化

1.4索引匹配方式

​ 全值匹配：
​ 匹配最左前缀
​ 匹配列前缀：like 'j%‘走索引，’%j’不走索引
​ 匹配范围值：隐式转换(类型转换)导致索引失效 如：name>123 与 name>‘123’
​ 精确匹配某一列并范围匹配另外一列：组合索引中间有范围查询时，导致后面的索引失效
​ 只访问索引的查询
​

细节：

​ 1、尽量不使用表达式、函数
​ 2、尽量使用主键查询，因为主键索引不会回表查询，IO次数增加
​ 3、使用前缀索引：适合比较长的字符串，节省索引空间大小
​ 4、使用索引扫描来排序：
​ 5、union all、in or
​ 6、范围列可以用到索引
​ 7、对经常修改的字段不要创建索引
​ 8、删除不再使用或很少使用的索引
​

注意：

​ 1>要遵循最左匹配原则
​ 2>隐式转换导致索引失效,例如 字符串类型为数字时不添加引号
​ 3>对索引进行运算导致索引失效(+ - * / !)
​ 4>对于 not in, in, !=, not exist也会导致索引失效
​ 5>对于like “%_” 百分号在前面也会导致索引失效
​ 6>or,除非or条件上的字段都为索引,否则只能进行全表查询

​