mysql--索引

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)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

索引的选择

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影响不大

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

扫描行数判定

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

排序判定

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

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

索引选择处理

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

前缀索引

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'