深入理解mysql索引

深入理解mysql索引

1 深入理解索引
1.1 索引基础理论知识；
1.2 B+树索引
1.3 哈希索引
1.4 理解B+树、哈希索引结构及区别；
1.5 理解常见索引的基本概念：主键索引、唯一索引、普通索引、联合索引等之间的区别；
1.6 理解MyISAM和InnoDB的索引结构区别；
1.7 理解如何通过索引提高SQL效率；

binary search，二分查找法，也叫折半查找法（折半搜索、二分查找算法、二分搜索），是一种在有序数组中查找某一特定元素的搜索算法
这种搜索算法每一次比较都使搜索范围缩小一半，可在最坏的情况下用O(log n)完成搜索任务
二分查找法的优点是比较次数少，查找速度快，平均性能好；其缺点是要求待查表为有序表，且插入删除困难。因此，折半查找方法适用于不经常变动而查找频繁的有序列表。
binary tree，二叉树的每个节点至多只有二棵子树(不存在度大于2的节点)，二叉树的子树有左右有序之分，次序不能颠倒
平衡树，平衡二叉树, Self-balancing binary search tree

平衡二叉树具有以下性质：
- 它是一棵空树或其左右两个子树的高度差的绝对值不超过1，且左右两个子树也是平衡二叉树；
- 不平衡树会通过自旋，变成平衡树；
左节点<根节点<右节点

一棵m阶（比如4阶）的B树满足下列条件：
- 树中每个节点至多有m个（4个）子节点；
- 除根节点和叶子节点外，其它每个节点至少有m/2个（2个）子节点；
- 若根节点不是叶子节点，则至少有2个子节点；
- 所有叶子节点都出现在同一层，叶子节点不包含任何关键字信息；

B+树是B树（B Tree）的变体，也是一种多路搜索树
1、非叶子结点的子树指针与关键字个数相同；
2、为所有叶子结点增加一个链指针；
3、所有关键字都在叶子结点出现；

二叉树、B树、B+树的小结
Binary树：二叉树，每个结点只存储一个关键字，等于则命中，小于走左结点，大于走右结点；
B树：多路搜索树，每个结点存储M/2到M个关键字，非叶子结点存储指向关键字范围的子结点；
所有关键字在整颗树中出现，且只出现一次，非叶子结点可以命中；
B+树：在B-树基础上，为叶子结点增加链表指针，所有关键字都在叶子结点中出现，非叶子结点作为叶子结点的索引；B+树总是到叶子结点才命中

1、alter table engine=x
2、optimize table

hash，哈希，散列
关键字key，经过哈希算法 hash(key) 后，产生一个精确的结果值

哪些情况下建议创建索引
• 经常需要搜索的列
• 作为主键的列，有唯一约束索引
• 经常用在表连接的列
• 经常需要排序的列
• 经常使用在WHERE子句中的列
经常需要排序/分组的列

alter table x add index ( a , b ) -- 正确
alter table x add index ( a asc, b desc) -- 正确吗
1次读key，3次顺序读key
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 3 |

b+tree索引
所有叶子节点高度相同
叶子节点有双向链表指向，上/下一个叶子节点
每一个entry保存了整行数据，表即索引
TID用于事务控制(锁)
RP用于MVCC（rollback pointer）
InnoDB clustered index规则
1、主键
2、第一个不包含null列的唯一索引
3、内置的rowid

using index -- 覆盖索引，通过索引直接获取结果
using filesort -- 使用filesort排序算法，对查询结果进行排序（该排序工作无法通过索引的排序直接完成，简言之，就是要排序的列无索引
5.6版本以上，才支持EXPLAIN DELETE/UPDATE
key_len计算规则：
(1) 索引字段的附加信息：可以分为变长和定长数据类型讨论，当索引字段为定长数据类型，比如char，int，datetime，需要有是否为空的标记，这个标记需要占用1个字节；
对于变长数据类型，比如：varchar，除了是否为空的标记外，还需要有长度信息，需要占用2个字节；
(备注：当字段定义为非空的时候，是否为空的标记将不占用字节)
(2) 同时还需要考虑表所使用的字符集，不同的字符集，gbk编码的为一个字符2个字节，utf8编码的一个字符3个字节;
总结，key_len的长度计算公式：
varchr(10)变长字段且允许NULL : 10*(Character Set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)
varchr(10)变长字段且不允许NULL : 10*(Character Set：utf8=3,gbk=2,latin1=1)+2(变长字段)
char(10)固定字段且允许NULL : 10*(Character Set：utf8=3,gbk=2,latin1=1)+1(NULL)
char(10)固定字段且不允许NULL : 10*(Character Set：utf8=3,gbk=2,latin1=1)

key_len是表示得到结果集所使用的选择的索引的长度，但不包括order by,也就是说，如果order by也使用了索引则key_len则不计算在内

CREATE TABLE `mytab` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`c1` int(11) NOT NULL DEFAULT '0',
`c2` int(10) unsigned DEFAULT NULL,
`c5` int(10) unsigned NOT NULL DEFAULT '0',
`c3` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`c4` varchar(200) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `idx_c1` (`c1`),
KEY `key_c2` (`c2`)
) ENGINE=InnoDB AUTO_INCREMENT=2686347;

### 1、提高检索效率
### 2、提高聚合函数效率
#有索引，可完全使用索引，而且一次性定位完成，不需要扫描索引
mysql&get; explain select max(c2) from mytab;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
#没有索引，需要全表扫描
#求平均值，有索引时，扫描索引即可，无需全表扫描（避免回表）
mysql&get; explain select avg(c1) from mytab;
#求平均值，无论如何，没索引时，总是全表扫描
mysql&get; explain select avg(c5) from mytab;
### 3、提高排序效率
#无索引，全表扫描，而且还要使用filesort排序算法
mysql&get; explain select c5 from mytab where c5 &get; 100 order by c5 limit 10;
#有索引，可快速排序完成
mysql&get; explain select c5 from mytab where c1 &get; 100 order by c1 limit 10;
#有索引，可快速排序完成，并且通过索引直接返回结果（避免回表）
mysql&get; explain select c1 from mytab where c1 &get; 100 order by c1 limit 10;
### 5、减少多表关联时扫描行数
mysql&get; explain select * from mytab t1 left join mytab t2 using (c1);

什么情况下无法使用索引
- 通过索引扫描的记录数超过30%，变成全表扫描
- 联合索引中，第一个索引列使用范围查询
- 联合索引中，第一个查询条件不是最左索引列
- 模糊查询条件列最左以通配符 % 开始
- 内存表(HEAP 表)使用HASH索引时，使用范围检索或者ORDER BY
- 两个独立索引，其中一个用于检索，一个用于排序
- 使用了不同的 ORDER BY 和 GROUP BY 表达式

# 只能用到 c1 索引，排序时无法用到 c2 索引
mysql&get; desc select * from mytab where c1 = 1234 order by c2;
#and条件下，只能用到一个索引
mysql&get; explain select * from mytab2 where c1 &get; 102400 and c2&get;102400;
mysql&get; explain select * from mytab2 where c1 &get; 102400 or c2&get;102400;

问题：联合索引中，第一个检索条件列使用 IN 是否可以用到整个索引？
WHERE key_part1 IN ( IN_LIST ) AND key_part2 = ? --- 可以用到索引吗
WHERE key_part1 IN ( IN_LIST ) ORDER BY key_part2 --- 可以用到索引吗
#第一个列用于检索，第二列用于排序，不能用到全部索引
mysql&get; explain select * from mytab where c1 in ( 123, 4576) order by c2;
#两列都用于检索，可以用到全部索引
mysql&get; explain select * from mytab where c2 = 1234 and c1 IN ( 1234, 466);
select * from mytab where c2 in( 1234 , 466) and c1 IN ( 1234, 466); -- 这个可以用到整个索引吗？ //可以的
select * from mytab where c1 &get;123 and c1 < 1234 and c2 = 33; -- 这种情况下，只能用到 idx_c1_c2 联合索引中的 c1 列这部分，c2列用不到
## 不支持函数索引，或者表达式索引
- 索引列上使用函数后，无法使用索引，可能导致全表扫描
- 索引列上不能附加运算表达式，否则也无法使用索引

create table t_idx (
id int unsigned not null auto_increment,
c1 int not null default '0',
c2 int not null default '0',
c3 int not null default '0',
c4 int not null default '0',
c5 timestamp not null,
c6 varchar(200) not null default '',
primary key(`id`),
KEY `idx_c2`(`c2`),
key `idx_c3`(`c3`)
);

-- 测试有无索引对比写入效率存储过程

delimiter $$$
CREATE PROCEDURE `insert_t_idx`(in row_num int )
begin
declare i int default 1;
while i <= row_num do
insert into t_idx(id, c1, c2, c3,c4, c5,c6) values(i, floor(rand()*row_num),floor(rand()*row_num),floor(rand()*row_num),floor(rand()*row_num),now(), repeat('wubx', floor(rand()*20)));
set i = i+1;
END while;
end $$$
call insert_t_idx (500000);

mysql&get; explain select * from t1 where user like 'user2%' ;
mysql&get; explain select * from t1 where user like 'user3%' ;
联合索引最左匹配原则
假设有索引：KEY `idx1` (`id`,`user`,`passwd`)
SELECT * FROM t1 WHERE id=?
SELECT * FROM t1 WHERE id=? AND user=?
SELECT * FROM t1 WHERE id=? AND user=? AND passwd=?
SELECT * FROM t1 WHERE passwd=? AND user=? AND id=?
SELECT * FROM t1 WHERE user=? AND passwd=? AND id=?
SELECT * FROM t1 WHERE passwd=? AND id=?
SELECT * FROM t1 WHERE user=? AND passwd=?
SELECT * FROM t1 WHERE passwd=?
SELECT id,user FROM t1 where id = ? order by user;
SELECT id,user FROM t1 where id = ? order by passwd;

InnoDB表主键、索引：
• Innodb表每一个表都要显式设置主键
• 主键越短越好，最好是自增类型；如果不能使用自增，则应考虑构造使用单向递增型主键，禁止使用随机类型值用于主键。
• 主键最好由一个字段构成，组合主键不允许超过3个字段。如果业务需求，则可以创建一个自增字段作为主键，再添加一个唯一索引。

• 索引设计原则
• 低选择性的列不加索引，如性别
• 常用的字段放在前面；选择性高的字段放在前面
• 需要经常排序的字段，可加到索引中，列顺序和最常用的排序一致
• 对较长的字符数据类型的字段建索引，优先考虑前缀索引，如index(url(64))
• 只创建需要的索引，避免冗余索引，如：index(a,b)，index(a)

MySQL索引限制
• 只支持B+ Tree、HASH索引，不支持BITMAP
• 不支持表达式、函数索引索引
• 类型不一致时会发生隐式转换
• 不支持全模糊匹配
• InnoDB索引最大支持768字节，MyISAM索引最大支持1000字节
• 超过30%的扫描比例时，直接走全表引扫描
• BLOB和TEXT类型的列只能创建前缀索引
• Join 语句中 Join 条件字段类型不一致的时候，MySQL 无法高效使用索引

如何提升MySQL索引统计信息的准确度？
1、定期ANALYZE TABLE t1，更新统计信息；
2、设置 innodb_stats_auto_recalc = on 自动更新统计信息；（默认开启）
3、设置 innodb_stats_persistent = 1，持久化统计信息（重启后无需重新采集）；（默认开启）
4、个别表索引统计信息不准确时，可调整单独调大 STATS_SAMPLE_PAGES 值，例如改成100（默认值是20）；
5、MySQL 8.0后使用直方图（尤其对基数低的列，可能比索引效果更好）；
6、有时候是因为数据本身不均衡造成索引倾斜，这种情况可以尝试加大统计页数或使用直方图。