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条件上的字段都为索引,否则只能进行全表查询