explain 检查索引的使用场景
- OS:windows
- version:mysql-8.0.42-winx64
create table user (
id int primary key,
name char(10) default null,
age int default null,
id_no char(18),
phone char(13),
address char(30),
INDEX `i_n_a_a`(name,age,address),
UNIQUE INDEX `u_id_no` (id_no),
INDEX `i_phone` (phone)
);
insert into user values(1,'zhangsan',19,'341223199208212345','18365265123','nanjing');
insert into user values(2,'lisi',17,'341223199608212346','18365265124','nanjing');
insert into user values(3,'wangwu',22,'341223199008212347','18365265125','nanjing');
- 主键索引
explain select * from user where id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | user | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
explain select * from user where id>1;
explain select * from user where id between 1 and 23;
explain select * from user where id in (1,3,7);
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 3 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
- 普通索引
explain select * from user where phone="15213424563";
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | user | NULL | ref | i_phone | i_phone | 53 | const | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
explain select * from user where phone in ("15213424563","15213424564");
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | user | NULL | range | i_phone | i_phone | 53 | NULL | 2 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
- 唯一索引
explain select * from user where id_no="341223199008212345";
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | user | NULL | const | u_id_no | u_id_no | 73 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
explain select * from user where id_no in ("341223199008212345","341223199008212347");
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | user | NULL | range | u_id_no | u_id_no | 73 | NULL | 2 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
- 联合索引
explain select * from user where name = "s" and age=13 and address="s";
+----+-------------+-------+------------+------+---------------+---------+---------+-------------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------------------+------+----------+-----------------------+
| 1 | SIMPLE | user | NULL | ref | i_n_a_a | i_n_a_a | 167 | const,const,const | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+---------+---------+-------------------+------+----------+-----------------------+
explain select * from user where name = "s" and age=13;
+----+-------------+-------+------------+------+---------------+---------+---------+-------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------------+------+----------+-----------------------+
| 1 | SIMPLE | user | NULL | ref | i_n_a_a | i_n_a_a | 46 | const,const | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+---------+---------+-------------+------+----------+-----------------------+
explain select * from user where name = "s";
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | user | NULL | ref | i_n_a_a | i_n_a_a | 41 | const | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
explain select * from user where age=13;
explain select * from user where address = "s";
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
explain select * from user where name = "lisi" or age=22;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | ALL | i_n_a_a | NULL | NULL | NULL | 3 | 55.56 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
索引失效场景
- 联合索引不满足最左匹配原则
- 索引列参与运算
- 索引列参使用了函数
- 错误的Like使用 (占位符出现在首部，导致无法走索引)
- 类型隐式转换 (字段类型为varchar，但在SQL语句中使用了int类型，导致全表扫描: where id_no = 3412231993)
- 使用OR操作
- 两列做比较 (字段1 >= 字段2)
- 不等于比较 (where id_no <> '3412231993')
- is not null (where id_no is not null)
- not in和not exists
- order by导致索引失效 ( order by id_no) 主键使用order by时，可以正常走索引
- DBMS发现全表扫描比走索引效率更高，因此就放弃了走索引。 当查询条件为大于等于、in等范围查询时，根据查询结果占全表数据比例的不同，优化器有可能会放弃索引，进行全表扫描
Hash索引和B+树索引的区别
- Hash索引
优势：
当查询数据时，Hash索引通过一次哈希函数计算直接定位到数据的存储位置，因此在等值查询中表现出色，查询复杂度为O(1)
劣势：
- 不支持范围查询：由于哈希函数的结果是无序的，Hash索引无法用于执行范围查询。
- 不支持排序操作：Hash索引无法利用索引顺序进行排序，因为哈希值的顺序与原始数据的顺序不一致。
- 不支持多列联合索引的最左前缀匹配：在计算哈希值时，Hash索引将所有键值合并，因此无法单独利用联合索引的部分键值。
- 存在哈希碰撞问题：当存在大量重复键值时，哈希碰撞会降低查询效率，特别是在非唯一键值的情况下。
- B+树索引
B+树索引是一种多路平衡查找树，它支持高效的范围查询和排序操作。B+树索引的叶子节点形成有序链表，便于执行范围查询和数据排序
- 支持范围查询：由于叶子节点是有序的，B+树索引可以快速进行范围查询。
- 支持排序和分页：B+树索引可以利用叶子节点的有序性进行排序和分页操作。
- 稳定的查询性能：B+树索引的查询性能相对稳定，因为所有查询都需要从根节点遍历到叶子节点。
- 支持最左前缀匹配：B+树索引支持最左前缀匹配规则，允许部分模糊查询。
如果查询主要是等值查询，且数据具有高唯一性(区分度高的列)，Hash索引可能会提供更好的性能
区分度的公式是：count(distinct col)/count(*)
