数据库索引的那点事
背景
想了解一下mysql索引失效的原因,和有索引、无索引的差距。
为了使测试效果更好去mysql官网下载了个测试的数据,数据有百万级别的。
mysql测试库说明文档:https://dev.mysql.com/doc/employee/en/
mysql测试库下载地址:https://github.com/datacharmer/test_db
测试库的表结构:
索引失效的原因
分析索引用到explain工具参考:https://www.cnblogs.com/butterfly100/archive/2018/01/15/8287569.html
或
https://dev.mysql.com/doc/refman/8.0/en/explain-output.html
首先我在employees表给first_name,last_name建立索引
CREATE index name_index on employees(first_name,last_name)
1、索引上计算/使用函数 导致索引失效
由于在索引上计算(first_name + ‘i’)导致索引失效
EXPLAIN SELECT * FROM employees
where first_name + 'i' = 'Georgii' and last_name = 'Birnbaum';
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299157 | 10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
正常情况是这样的
EXPLAIN SELECT * FROM employees
where first_name = 'Georgi' and last_name = 'Birnbaum';
+----+-------------+-----------+------------+------+---------------+------------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------------+---------+-------------+------+----------+-------------+
| 1 | SIMPLE | employees | NULL | ref | name_index | name_index | 94 | const,const | 1 | 100.00 | Using index |
+----+-------------+-----------+------------+------+---------------+------------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
2、索引的最左匹配原则
-- 联合索引(a,b,c)用左边的才生效如:a/ a,b / a,b,c(必须从左边开始取连续的一部分)
--用b,c或a,c 或b或c会失效
EXPLAIN SELECT * FROM employees
where last_name = 'Birnbaum'
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299157 | 10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
索引底层的数据结构其实是b+树,在建立联合索引(a,b,c)时,它会吧a放在第一个节点,b放在第二个,c。。。。。匹配时先根据第一个节点a找到对应的数据,然后去找b,然后c。
如果条件为where b=1 and c=2、 没有a,它就不知道去匹配谁,所以所以会失效。
同样的where a=1 and c=2 先去匹配a,所以a是生效的,但没有b,所以c是不生效的
3、使用范围性条件
-- 使用范围性条件,如!=/ > / < / bettween and / is not null和is null
EXPLAIN
SELECT * FROM employees
where first_name != 'Georgi';
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | employees | NULL | ALL | name_index | NULL | NULL | NULL | 299157 | 100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
注:范围性查找其实不应当失效,只要匹配的行数比例足够小就会走索引,听说查询的记录数 大于总记录数的15%左右会不走索引(查询记录/总记录>15%)
下面这种情况虽然是范围查找走索引,总记录数30万,first_name > ‘z’ 的记录6千左右,2%左右
EXPLAIN
SELECT * FROM employees
where first_name > 'z' ;
+----+-------------+-----------+------------+-------+---------------+------------+---------+------+-------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+------------+---------+------+-------+----------+-----------------------+
| 1 | SIMPLE | employees | NULL | range | name_index | name_index | 44 | NULL | 12408 | 100.00 | Using index condition |
+----+-------------+-----------+------------+-------+---------------+------------+---------+------+-------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)
3、使用like %开头的
-- 使用like "%xxxxx" %开头的条件索引失效
EXPLAIN
SELECT * FROM employees
where first_name like '%eorgi' ;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299157 | 11.11 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
同样like会相匹配开头的 如like “gdfdf%” 先匹配g,如果%开头“%eorgi” 不知道匹配那个会失效
走索引和不走索引的差距
走索引的
SELECT * FROM employees where first_name = 'Georgi' and last_name = 'Birnbaum';
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 35707 | 1960-10-09 | Georgi | Birnbaum | M | 1993-08-31 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.00 sec)
把索引删掉再查,用时0.14 sec,比之前的0.00sec差距很小,却又很大(无穷多倍)
DROP index name_index on employees;
SELECT * FROM employees where first_name = 'Georgi' and last_name = 'Birnbaum';
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 35707 | 1960-10-09 | Georgi | Birnbaum | M | 1993-08-31 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.14 sec)