1.B+树索引

Mysql底层使用的是B+树索引,B+树是一种平衡二叉树(AVL Tree), 其特点是:

所有非叶子节点存储关键字(索引)信息,
所有行数据存储于叶子节点,mysql的B+树叶子节点存储的是数据页
叶子节点通过指针相连,形成链表

2. B+树 vs B树

B树的每个节点都存储整行行数据, 同一数据页存储的数据数量比B+树少,故B树深度一般大于B+树
范围查询时,B树需要遍历每个关键字,B+树则可以根据链表左右移动查询

3.索引的使用

mysql> create table T (
ID int primary key,
k int NOT NULL DEFAULT 0, 
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;

insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

select * from T where k between 3 and 5

执行流程: 在 k 索引树上找到 k=3 的记录，取得 ID = 300；再到 ID 索引树查到 ID=300 对应的 R3；在 k 索引树取下一个值 k=5，取得 ID=500；再回到 ID 索引树查到 ID=500 对应的 R4；在 k 索引树取下一个值 k=6，不满足条件，循环结束。

在这个过程中，回到主键索引树搜索的过程，我们称为回表。可以看到，这个查询过程读了 k 索引树的 3 条记录（步骤 1、3 和 5），回表了两次（步骤 2 和 4）。

覆盖索引 如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。

索引下推 MySQL 5.6 引入的索引下推优化（index condition pushdown)，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

4.索引的选择

CREATE TABLE `t` ( 
`id` int(11) NOT NULL, 
`a` int(11) DEFAULT NULL, 
`b` int(11) DEFAULT NULL, 
PRIMARY KEY (`id`), 
KEY `a` (`a`), KEY `b` (`b`)
) ENGINE=InnoDB；
//表中插入10万条数据(1,1,1)，(2,2,2)，(3,3,3)

由上图可见,扫描行数为10001行,与预估相符

set long_query_time=0;  //打开慢查询日志,记录每一条sql
select * from t where a between 10000 and 20000; /*Q1*/  
select * from t force index(a) where a between 10000 and 20000;/*Q2*/

Q1会走全表扫描,扫描10w行,Q2走索引,扫描10001行,为什么会产生这种情况呢? Q1中由于sessionA的存在,sessionB删除的数据存在于undolog中(delete只会修改deleteflag并且会记录再update undolog中),数据页中的数据也不会删除,因此再次插入10w条记录会导致新纪录寻找新的地址进行插入,引起cardinality不准确,进而影响优化器判断索引选择 Q2中新插入的数据会沿用老数据的位置,对cardinality影响不大

优化器在选择索引的时候会根据扫描行数+是否使用临时表+是否排序来综合判定

4.1扫描行数判定

show index form t; //cardinality,索引统计值,通过采样统计得到
analyze table t //如果统计信息不对的话,可以重新计算索引信息

4.2排序判定

select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;

如果使用索引a的话只需要扫描1000行,但是优化器选择了b,因为需要b排序;

4.3索引选择处理

可以使用force index强行指定索引修改语句,引导mysql使用我们期望的索引增加一个更合适的索引,或者删掉误用的索引

5.前缀索引

alert table t add index_name(t.field(6));

使用前缀索引可以节省空间,但是有可能会增加索引扫描行数建立前缀索引,区分度越高越好前缀索引无法使用覆盖索引,必须回表

mysql> select 
  count(distinct left(email,4)）as L4,
  count(distinct left(email,5)）as L5,
  count(distinct left(email,6)）as L6,
  count(distinct left(email,7)）as L7,
from SUser;
--预先设定一个可以接受的损失比例,再L4~L7中找到最小的符合条件的长度作为前缀长度

如果前缀索引无法提供足够的区分度,可以使用倒序存储或者hash字段,这两者均不支持范围查询,仅支持等值查询

select field_list from t where id_card = reverse('input_id_card_string');
 
select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

6. 普通索引的change buffer

change buffer使用场景: 数据页不存在内存中时,执行更新/新增/删除时,对普通索引维护时,不需要加载对应数据页到内存,而直接记录到changebuffer中,减少一次磁盘io读取 ; 当下次读取对应数据时,先加载数据页到内存,然后将change buffer中的变化merge到内存数据页中

通常用于写多读少的场景,或者写完之后不会立马读的场景,比如日志归档等场景

7.如何建立高效的索引

建议建立索引的场景:

经常使用的列
join/order/范围查询上的列
where 中经常出现的列

不建议建立索引的场景:

写多读少的表慎重选择索引
数据区分度小的列

8.索引失效/低效的场景

模糊查询时使用左模糊匹配
数据类型不一致
列上使用函数
空值(null), 索引不使用null值
使用运算符
联合索引不满足最左匹配原则
mysql认为全表扫描更快时会放弃索引,一般是使用索引扫描行数达到总行数的一定比例时会放弃走索引,比如使用or in !=这类时易产生

mysql 索引