数据库索引的那点事

数据库索引的那点事

背景

想了解一下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)