MySQL索引

前言

1. 表的索引越全越好，对吗？

不是的

2.为什么不要在性别字段上建立索引？

就只有男和女，效果相当于全量扫描了、

3. 为什么不建议使用身份证做为主键？

太长了

4. 模糊匹配 like abc%，都用不到索引是吗？

5. 不要使用select *, 写明具体查询字段，为什么？

索引

数据库索引，是数据库管理系统中的一个排序的数据结构，以协助快速查询、更新数据库表中数据。
可以理解为“目录”
单列索引、联合索引

索引类型

normal 普通索引
unique 唯一索引
primary 主键索引 不唯一且not null
full text 全文索引

索引的数据结构

二叉查找树 binary search tree。左子树的节点<父节点 右子树的节点>父节点
平衡二叉树 avl tree=balanced binary search tree 左右子树深度差绝对值不能超过1
保持平衡：左旋和右旋
多路平衡查找树 balanced tree b树 【一个节点存储多个数据（关键数的数量 +1 = 分叉树），还保持平衡】
保持平衡：分裂和合并
B+树：b树的plus版本
特性：关键数的数量=分叉树的数量
根节点和分支节点都不存储数据。只有叶子节点才存储数据。
叶子节点和叶子节点之间有前后指针，形成了双向链表的特点。

b树能解决的问题，b+树都能解决
扫库、扫表的能力更强【扫全表的时候只要扫叶子节点就可以了】
磁盘读写能力更强【只有叶子节点存数据，那么根节点和分支节点就可以存更多的数据，所以树的深度就会很浅一些】
排序能力更强【大于、小于、区间查找，根据叶子节点就可以查】
效率更加稳定 【所有的查询语句都要走到叶子节点，查询趋于稳定】

为什么要用B+树，而不是红黑树？

红黑树：只有两路，深度会比较大，且是非平衡的树，红黑树一般用于内存

myIsam - 主键索引
叶子节点存储的是数据的地址
所有的索引都是这样的
innodb： 索引即数据，数据即索引，叶子节点存储的是节点数据，而非地址。
只有聚集索引（索引键值顺序等于数据行的物理存储顺序）是这样存放的哈。其他索引的叶子节点放主键id
二级索引（也是b+树，叶子节点存的是对应记录的主键值）
步骤：先扫描自己的索引树，然后再扫描主键的索引树（这个过程叫做回表）

（1）、如果有主键索引，则主键索引就是聚集索引
（2）、如果没有主键索引，那么不包含空值的唯一索引，就是聚集索引。
（3）、如果既没有主键索引，也没有不包含空值的唯一索引，那么mysql会给表生成一个自增的隐藏的列，rowId，然后会把这个rowId当做聚集索引。

索引的使用原则

误区：
索引不是越多越好。占用磁盘空间较大，并且插入修改数据时，会比较麻烦，会引起每个索引b+树的变动。
列的离散度越低，查询性能越差。如性别就不适合做为索引字段，原因就是b+树全是0和1，全是重复的值。不要在离散度很低的数据上建立索引，这样的索引是无用的索引。
（https://www.cs.usfca.edu/~galles/visualization/Algorithms.html）

联合索引的最左匹配原则
联合索引的b+树里是一个复合的结构，比如name+phone，在b+树里存的就是(‘程小可’,‘182xxxx3048’)
只有第一个字段是有序的，后面的字段是只有第一个字段有序的情况下，第二个字段才是有序的。
在使用联合索引的时候，查询条件的字段，必须从第一个字段开始，不能中断（跳过）

覆盖索引（using index）
查询的值就是索引的值，不需要回表操作

有个联合索引 name phone
select name from table where phone = ‘182xxxx3048’; // 用到了覆盖索引

索引条件下推（ICP）
select * from table where a=’’ and b=’’
第一种方案：
假设a是索引，那么查询顺序就是
先用a索引查出来数据，然后回表，拿到所有数据，再利用b条件过滤数据
第二种方案：
a和b是联合索引
先根据联合索引查出满足a条件的索引
然后从二级索引里查出满足b条件的索引
然后再回表，到主键索引上查询全部符合条件的数据，返回给server层
using condition pushing down

创建索引

1、在用于where判断、order排序、join、group by字段上创建索引
2、索引的个数不要过多
3、区分度低的字段，例如性别，不要建立索引
4、频繁更新的值，不要做为主键或者索引，会引起b+树的调整，耗费性能
5、不建议用无序的值做为索引
6、复合索引把散列度高的值放在前面
7、创建复合索引，而不是修改单列索引
8、过长的字段，建立前缀索引（给某一个字段的一小部分创建索引）

什么时候会用不到索引

1、索引列上使用函数（replace/substr/concat/sum count avg）、表达式
2、字符串不加引号。出现隐式转换
3、索引的最左匹配，like条件中前面带%
4、反向查询能用到索引吗？ 不等于 not in
不一定。