mysql索引失效问题

索引

索引主要时为了加快数据检索速度而出现的数据库对象。

在mysql中，访问数据库 表主要有两种：

顺序访问 ：进行全表扫描

索引访问：通过遍历索引访问

#创建索引 
create index 索引名 on 表名 （列名）
 #查看表中所有的索引 
show index from 表名 
#查看索引使用情况
 explain select * from 表名 where 查询字段='XXx' 
#删除索引 drop index 索引名 on 表名
 alter table 表名 drop index 索引名
 #添加索引 a
lter table 表名 add 索引类型 索引名称（列名）

索引使用原则

1. 在经常需要搜索的列上建立
2. 作为主键的列上
3. 经常使用表连接的列上
4. 经常需要根据范围进行搜索的列上、
5. 经常需要排序的列上
6. 经常使用where子句的列上

索引虽然可以加快查询效率，但是也有弊端：

• 创建索引和维护需要耗费的时间长，
• 所索引会占用数据空间

索引长度计算

1.所有的索引字段，如果没有设置not null，则需要加一个字节。

2.定长字段，int占四个字节、date占三个字节、char(n)占n个字符。

3.对于变长字段varchar(n)，则有n个字符+两个字节。

4.不同的字符集，一个字符占用的字节数不同。latin1编码的，一个字符占用一个字节，gbk编码的，一个字符占用两个字节，utf8编码的，一个字符占用三个字节。

5.索引长度 char()、varchar()索引长度的计算公式：

(Character Set：utf8mb4=4,utf8=3,gbk=2,latin1=1) * 列长度 + 1(允许null) + 2(变长列)；

索引失效

#创建一张表 
CREATE TABLE `test1` (
 `id` bigint NOT NULL, `code` varchar(30) NOT NULL,
 `age` int NOT NULL, `
name` varchar(30) NOT NULL,
 `height` int NOT NULL, PRIMARY KEY (`id`), 
KEY `idx_code_age_name` (`code`,`age`,`name`) USING BTREE, 
KEY `idx_height` (`height`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 
#插入数据
 INSERT INTO `test1`(`id`, `code`, `name`, `age`,`address`) VALUES (1, '001', '张飞', 18,'7');
 INSERT INTO `test1`(`id`, `code`, `name`, `age`,`address`) VALUES (2, '002', '关羽', 19,'8');

• 不满足最左前缀原则

#执行sql: explain select * from test1 where code='001' and age=18 and name='张飞'; #索引长度=30*3 + 2 + 4 + 30*3 +2=188，此时索引使用是最充分的

#执行sql: explain select * from test1 where code='001' #索引长度=30*3 + 2 = 92，此时只是用了索引的一个字段，索引使用不充分

#执行sql： explain select * from test1 where code='001' and name='张飞' #索引长度=30*3 + 2 =92，此时使用到一个索引字段，name字段的索引失效了

#执行sql: explain select * from test1 where code='001' and name='张飞' and age=18 #在这种情况下，mysql会自动优化执行顺序，结果和第一种一样，索引长度=30*3 + 2 + 4 + 30*3 +2=188

#执行sql: explain select * from test1 where age=18 and name='张飞'; #索引失效，进行全表扫描

• 范围索引列没有放在最后

范围查询放最后，指的是联合索引中的范围列放在最后，不是指where条件中的范围列放最后。如果联合索引中的范围列放在最后了，即使where条件中的范围列没放最后也能正常走到索引

• 使用select *

#执行sql explain select * from test1; #使用select * 进行了全表扫描，索引失效

#执行sql explain select code,age,name from test1; #使用索引

• 索引列上有计算

#执行sql explain select code,age,name,height * 8 + 1 from test1; #索引失效

• 索引列上使用了函数

#执行SQL explain select * from test1 where SUBSTR(height ,1 , 1); #索引失效

• 字符类型没加引号

#执行SQL explain select * from test1 where code=001; #索引失效

#执行SQL explain select * from test1 where code='001';

• 用 is null和is not null没注意字段是否允许为空

1. 如果字段不允许为空，则is null 和 is not null这两种情况索引都会失效。
2. 如果字段允许为空，则is null走 ref 类型的索引，而is not null走 range 类型的索引

• like查询左边有%

#执行sql explain select * from test1 where code like '%001'; #索引失效

#执行sql explain select * from test1 where code like '001%';

#执行sql explain select * from test1 where code like '%001%'; #索引失效

• 使用or关键字时没注意

#执行sql explain select * from test1 where code='001' or height=8; #索引失效

#执行sql explain (select * from test1 where code='001') union (select * from test1 where height=8);

索引失效除了上述的常见问题之外， mysql 通过索引扫描的行记录数超过全表的10%~30% 左右,优化器也可能不会走索引，自动变成全表扫描

索引设计的建议

• 优先使用唯一索引
• 为常用查询字段建立索引
• 为排序、分组、和联合查询字段建立索引
• 一张表的索引数量不超过5个
• 表数据量少，可以不用索引
• 尽量使用占用空间少的字段建立索引
• 使用idx_或者unx_等前缀命名索引
• 删除没用的索引

索引结构

树的深度加深一层，意味着多一次查询，对于数据库磁盘而言，就是多一次IO操作，导致查询效率低下

二叉搜索树

所有非叶子节点最多拥有两个儿子 left和right

所有节点存储一个关键字

非叶子节点的左指针指向小于其关键字的子树，右指针指向大于其关键字的子树

Hash

采用一定的哈希算法，将键值换算成新的hash值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。

Hash索引比较的是进行hash运算之后的hash值，所以他只能用于等值的过滤。不能用于基本范围的过滤，因为经过相应的hsah算法处理之后hsah的大小关系，并不能保证和Hash运算前完全一样

B-树

多路搜索树，特征：关键字集合分布在整棵树中；

任何一个关键字出现且只出现在一个节点中；

搜索有可能在非叶子节点结束；

搜索性能等价于在关键字全集内做一次二分查找；

自动层次控制；

搜索：从根节点开始，对节点内的关键字（有序）序列进行二分查找，如果命中，则结束，否则，进入查询关键字所属范围的儿子节点，重复，直到所对应的儿子指针为空或者已经是叶子节点。

B+树

B树的一种变体，也是一种多路搜索树

特证：

所有的关键字都出现在叶子节点的链表中，并且链表中的关键字恰好的有效的；

不可能在非叶子节点命中；

非叶子节点相当于是叶子节点的索引，叶子节点相当于是存储关键字数据的数据层；

每一个叶子节点都包括含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历；

更适合文件索引系统。

B+树和B-树

B+树的磁盘读写代价更低；

B+树的查询效率更加稳定

B+树更易于遍历

B+树更擅长范围查询

B+树占用内存空间小