|
 前言 数据库存储数据越来越多，导致查询速度慢，建立索引是有效的解决方法，有时候错误的执行sql语句，导致索引失效，所以要避免这种情况。 一、准备数据 1.1、创建表 //部门 create table dep( id int unsigned primary key auto_increment, depno mediumint unsigned not null default 0, depname varchar(20) not null default "", memo varchar(200) not null default "" ); //员工 create table emp( id int unsigned primary key auto_increment, empno mediumint unsigned not null default 0, empname varchar(20) not null default "", job varchar(9) not null default "", mgr mediumint unsigned not null default 0, hiredate datetime not null, sal decimal(7,2) not null, comn decimal(7,2) not null, depno mediumint unsigned not null default 0 ); 1.2、创建生成随机字符串、编号 /* 随机字符串的函数*/ DELIMITER $ drop FUNCTION if EXISTS rand_string; CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255) BEGIN DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmlopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'; DECLARE return_str VARCHAR(255) DEFAULT ''; DECLARE i INT DEFAULT 0; WHILE i < n DO SET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1)); SET i = i+1; END WHILE; RETURN return_str; END $ DELIMITER; /*随机部门编号的函数*/ DELIMITER $ drop FUNCTION if EXISTS rand_num; CREATE FUNCTION rand_num() RETURNS INT(5) BEGIN DECLARE i INT DEFAULT 0; SET i = FLOOR(RAND()*100+RAND()*20); RETURN i; END $ DELIMITER; 1.3、模拟500W员工数据 /*建立存储过程：往emp表中插入数据*/ DELIMITER $ drop PROCEDURE if EXISTS insert_emp; CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10)) BEGIN DECLARE i INT DEFAULT 0; /*set autocommit =0 把autocommit设置成0，把默认提交关闭*/ SET autocommit = 0; REPEAT SET i = i + 1; INSERT INTO emp(empno,empname,job,mgr,hiredate,sal,comn,depno) VALUES ((START+i),rand_string(6),'SALEMAN',0001,now(),2000,400,rand_num()); UNTIL i = max_num END REPEAT; COMMIT; END $ DELIMITER; /*插入500W条数据*/ call insert_emp(0,5000000); 1.4、模拟120条部门记录 /*建立存储过程：往dep表中插入数据*/ DELIMITER $ drop PROCEDURE if EXISTS insert_dept; CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10)) BEGIN DECLARE i INT DEFAULT 0; SET autocommit = 0; REPEAT SET i = i+1; INSERT INTO dep( depno,depname,memo) VALUES((START+i),rand_string(10),rand_string(8)); UNTIL i = max_num END REPEAT; COMMIT; END $ DELIMITER; /*插入120条数据*/ call insert_dept(0,120); 1.5、创建复合索引（数据大时间比较长） # 这边建立一个复合索引，包含 depno（部门编号）、empname（员工姓名）、job（工作岗位）。 create index idx_depno_empname_job on emp(depno,empname,job); 二、验证索引失效 2.1、员工姓名模糊查询。 # 验证脚本：未使用到了索引，全表扫描 mysql> explain select empno,empname,job from emp where empname like 'C%'; +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 4982087 | 11.11 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.01 sec) 2.2、根据员工工作条件查询。 # 验证脚本：未使用到了索引，全表扫描 mysql> explain select empno,empname,job from emp where job=8; +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 4982087 | 10.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ 1 row in set, 2 warnings (0.00 sec) 2.3、根据部门编号、工作条件查询。 # 验证脚本：未使用到了索引，但仅覆盖了depno，所以扫描行数也有 37626 行 mysql> explain select empno,empname,job from emp where depno=7 and job=8; +----+-------------+-------+------------+------+-----------------------+-----------------------+---------+-------+-------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+-----------------------+-----------------------+---------+-------+-------+----------+-----------------------+ | 1 | SIMPLE | emp | NULL | ref | idx_depno_empname_job | idx_depno_empname_job | 3 | const | 37626 | 10.00 | Using index condition | +----+-------------+-------+------------+------+-----------------------+-----------------------+---------+-------+-------+----------+-----------------------+ 1 row in set, 2 warnings (0.01 sec) 只是使用了索引depno字段，如果数据量多也会很慢。 2.4、索引列使用函数运算。 mysql> select * from emp where id = 4990000; +---------+---------+---------+---------+-----+---------------------+------+------+-------+ | id | empno | empname | job | mgr | hiredate | sal | comn | depno | +---------+---------+---------+---------+-----+---------------------+------+------+-------+ | 4990000 | 4990000 | PWmulY | SALEMAN | 1 | 2021-01-23 16:46:24 | 2000 | 400 | 102 | +---------+---------+---------+---------+-----+---------------------+------+------+-------+ 1 row in set (0.002 sec) mysql> select * from emp where id+1 = 4990001; +---------+---------+---------+---------+-----+---------------------+------+------+-------+ | id | empno | empname | job | mgr | hiredate | sal | comn | depno | +---------+---------+---------+---------+-----+---------------------+------+------+-------+ | 4990000 | 4990000 | PWmulY | SALEMAN | 1 | 2021-01-23 16:46:24 | 2000 | 400 | 102 | +---------+---------+---------+---------+-----+---------------------+------+------+-------+ 1 row in set (1.762 sec) 2.5、类型转换。 empname为字符串类型，当转换数字时，导致索引失效。 mysql> explain select * from emp where empname =1; +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 4982087 | 10.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ 1 row in set, 2 warnings (0.00 sec) 2.6、条件or前后存在非索引列。 #全表扫描 mysql> explain select empno,empname,job from emp where depno=1 or mgr=1; +----+-------------+-------+------------+------+-----------------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+-----------------------+------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | emp | NULL | ALL | idx_depno_empname_job | NULL | NULL | NULL | 4982087 | 10.74 | Using where | +----+-------------+-------+------------+------+-----------------------+------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) #全表扫描 mysql> explain select empno,empname,job from emp where mgr=1 or depno=1 ; +----+-------------+-------+------------+------+-----------------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+-----------------------+------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | emp | NULL | ALL | idx_depno_empname_job | NULL | NULL | NULL | 4982087 | 10.74 | Using where | +----+-------------+-------+------------+------+-----------------------+------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) 2.7、索引列比较。 # 使用不等比较(!= 或者<>) 导致索引失效 mysql> explain select * from emp where depno <> 7; +----+-------------+-------+------------+------+-----------------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+-----------------------+------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | emp | NULL | ALL | idx_depno_empname_job | NULL | NULL | NULL | 4982087 | 52.45 | Using where | +----+-------------+-------+------------+------+-----------------------+------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) 2.8、索引列检索数据量超过30%。 mysql> explain select * from emp where depno > 7; +----+-------------+-------+------------+------+-----------------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+-----------------------+------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | emp | NULL | ALL | idx_depno_empname_job | NULL | NULL | NULL | 4982087 | 50.00 | Using where | +----+-------------+-------+------------+------+-----------------------+------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) 总结，索引失效情况 1、违法最左匹配原则； 2、索引列函数计算； 3、索引列类型转换； 4、模糊查询左边不能包含'%’； 5、条件OR关键字，存在非索引列； 6、条件检索数据不能超过30%； 7、条件不能使用（!=或<>）符号； |
|
|
