Mysql-索引失效、关联查询优化、子查询优化、order by优化、group by优化、分页优化

Mysql 专栏收录该内容
7 篇文章 1 订阅

一.建立索引实验数据

  • 1.建表:
#创建部门表
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
 `id` int(20) NOT NULL AUTO_INCREMENT,
 `deptname` varchar(30) DEFAULT NULL COMMENT '部门名称',
 `address` varchar(40) DEFAULT NULL COMMENT '地址',
 `ceo` int NULL COMMENT 'ceo',
 PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部门';

#创建员工表
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
 `id` int(20) NOT NULL AUTO_INCREMENT,
 `empno` int NOT NULL COMMENT '员工编号',
 `name` varchar(30) DEFAULT NULL COMMENT '姓名',
 `age` int(3) DEFAULT NULL COMMENT '年龄',
 `deptId` int(11) DEFAULT NULL COMMENT '部门id',
 PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='员工';
  • 2.创建函数
#随机产生字符串
DROP FUNCTION IF EXISTS rand_str;
create FUNCTION rand_str(strlen INT ) RETURNS VARCHAR(255)
BEGIN
DECLARE randStr VARCHAR(255) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890';
DECLARE i SMALLINT DEFAULT 0;
DECLARE resultStr VARCHAR(255) DEFAULT '';
WHILE i<strlen DO
SET resultStr=CONCAT(SUBSTR(randStr,FLOOR(RAND()*LENGTH(randStr))+1,1),resultStr);
SET i=i+1;
END WHILE;
RETURN resultStr;
END

#随机产生数字
DROP FUNCTION IF EXISTS rand_num;
create FUNCTION rand_num (from_num INT,to_num INT ) RETURNS INT(11)
BEGIN
DECLARE i INT DEFAULT 0;
SET i=FLOOR(from_num +RAND()*(to_num -from_num+1));
RETURN i;
END
  • 3.创建函数,假如报错:This function has none of DETERMINISTIC......
#由于开启过慢查询日志,因为我们开启了 bin-log, 我们就必须为我们的function指定一个参数。
show variables like 'log_bin_trust_function_creators';
set global log_bin_trust_function_creators=1;

#这样添加了参数以后,如果mysqld重启,上述参数又会消失,永久方法:
#linux下   /etc/my.cnf下my.cnf 加上
[mysqld]
log_bin_trust_function_creators=1
  • 4.创建存储过程
#创建往emp表中插入数据的存储过程
#drop PROCEDURE insert_emp;
create PROCEDURE insert_emp(start INT,max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i+1;
INSERT INTO emp(empno,name,age,deptid)VALUES ((START+i) ,rand_str(6)   , rand_num(30,50),rand_num(1,10000));
UNTIL i =max_num
END REPEAT;
COMMIT;
END

#创建往dept表中插入数据的存储过程
 #drop PROCEDURE insert_dept;
create PROCEDURE insert_dept(max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i+1;
INSERT INTO dept(deptname,address,ceo)VALUES (rand_str(8), rand_str(10),rand_num(1,50000));
UNTIL i =max_num
END REPEAT;
COMMIT;
END
  • 5.调用存储过程
#执行存储过程,往dept表添加1万条数据
CALL insert_dept(10000); 

#执行存储过程,往emp表添加50万条数据
CALL insert_emp(100000,500000); 

二.索引失效案例

  • 如何判断索引是否失效?

  • 查看Explain命令分析器,通过type、key、Extra关键字
    在这里插入图片描述

  • key:使用到的索引,如果为null,索引失效

  • type:访问类型,如果 为all,索引失效

  • Extra:额外信息(使用的索引详细信息)
    using index :使用覆盖索引的时候就会出现
    using where:在查找使用索引的情况下,需要回表去查询所需的数据
    using index condition:查找使用了索引,但是需要回表查询数据
    using index & using where:查找使用了索引,但是需要的数据都在索引列中能找到,所以不需要回表查询数据

  • 什么情况下会造成索引失效?

  • 未遵循最佳左前缀法则
  • 在索引上做 计算操作
  • 范围条件查询右边的索引字段,(但范围查询之前的索引字段不会索引失效)
  • 使用不等于(!= 或者<>)
  • 使用is not null(但is null不会导致索引失效)
  • 使用or
  • like以通配符%开头(’%abc…’)
  • 字符串不加单引号
  • 索引失效对比分析:
  • 0.未添加索引:(初始状态)
#查看表中的索引
mysql> show index from emp ;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| emp   |          0 | PRIMARY  |            1 | id          | A         |      470526 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

#Explain查看初始状态下的索引使用情况
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age = 30  AND deptId = 4 AND NAME = 'bREJiw';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 499145 |     0.10 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
  • 总结:
    初始状态下的,只有主键id,mysql 默认加了索引,但我们的sql语句未使用到id ,故 type为ALL,key为NULL,未使用到任何索引
  • 1.最佳做前缀法则
#添加组合索引
CREATE INDEX idx_age_deptId_name ON emp(age,deptId,name);  

#1.以组合索引的第一个字段作为过滤条件
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age = 30  ;
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+-------+----------+-------+
| id | select_type | table | partitions | type | possible_keys       | key                 | key_len | ref   | rows  | filtered | Extra |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+-------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | ref  | idx_age_deptId_name | idx_age_deptId_name | 5       | const | 49176 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+-------+----------+-------+

#2.以组合索引的前两个字段作为过滤条件
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age = 30  AND deptId = 4 ;
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys       | key                 | key_len | ref         | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | ref  | idx_age_deptId_name | idx_age_deptId_name | 10      | const,const |    3 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------------+------+----------+-------+

#3.以组合索引的前三个字段作为过滤条件
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age = 30  AND deptId = 4 AND NAME = 'bREJiw';
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys       | key                 | key_len | ref               | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | ref  | idx_age_deptId_name | idx_age_deptId_name | 133     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------------------+------+----------+-------+

#4.将组合索引的第一个字段age去除
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE deptId = 4 AND NAME = 'bREJiw';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 499145 |     1.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+

#5.将组合索引的第二个字段deptId去除
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age = 30  AND NAME = 'bREJiw';
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+-------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys       | key                 | key_len | ref   | rows  | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+-------+----------+-----------------------+
|  1 | SIMPLE      | emp   | NULL       | ref  | idx_age_deptId_name | idx_age_deptId_name | 5       | const | 49176 |    10.00 | Using index condition |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+-------+----------+-----------------------+
  • 对比分析:
    1) 1、2、3对比,都使用到了key:idx_age_deptId_name,ref:const(观察const数量和where过滤条件的数量的关系)m,rows(扫描的物理行数在不断减小)
    2) 3和4对比,将组合索引的首字段age去除后(即跳过age字段),4的索引失效了
    3) 3和5对比,将组合索引的中间字段deptId去除后,ref 中const减少为1个,Extra为Using index condition(查找使用了索引,但回表查询了数据),(即跳过了deptId字段,5中的索引发生了中断,只有中断前的age字段生效,age之后的name字段失效了)
  • 总结:
    查询需从索引的最左前列开始,并且不跳过索引中的列
  • 2.不在索引上做操作

#1.以组合索引的第一个字段作为过滤条件
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age = 30  ;
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+-------+----------+-------+
| id | select_type | table | partitions | type | possible_keys       | key                 | key_len | ref   | rows  | filtered | Extra |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+-------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | ref  | idx_age_deptId_name | idx_age_deptId_name | 5       | const | 49176 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+-------+----------+-------+

 #1.在索引上计算
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age + deptId = 34 ;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 499145 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+

 #2.在索引上使用函数
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE abs(age) = 30  ;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 499145 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+

 #3.使用or条件
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age = 30 or deptId = 4 ;
+----+-------------+-------+------------+------+---------------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys       | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | idx_age_deptId_name | NULL | NULL    | NULL | 499145 |    14.50 | Using where |
+----+-------------+-------+------------+------+---------------------+------+---------+------+--------+----------+-------------+
  • 对比分析:
    1) 1、2、3、4对比,2、3、4中的type:ALL,key:NULL,索引都失效了
  • 总结:
    索引上的计算、函数、(自动or手动)类型转换,会导致索引失效而转向全表扫描
  • 3.不能使用索引中的范围条件右边的列
 #1.以组合索引的前三个字段作为过滤条件
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age = 30  AND deptId = 4 AND NAME = 'bREJiw';
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys       | key                 | key_len | ref               | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | ref  | idx_age_deptId_name | idx_age_deptId_name | 133     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------------------+------+----------+-------+

 #2.where后的过滤条件变为范围查询
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age = 30  AND deptId > 4 AND NAME = 'bREJiw';
+----+-------------+-------+------------+-------+---------------------+---------------------+---------+------+-------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys       | key                 | key_len | ref  | rows  | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------------+---------------------+---------+------+-------+----------+-----------------------+
|  1 | SIMPLE      | emp   | NULL       | range | idx_age_deptId_name | idx_age_deptId_name | 10      | NULL | 49088 |    10.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------------+---------------------+---------+------+-------+----------+-----------------------+
  • 对比分析:
    1)1和2 对比,2的type变为range,说明使用了age和deptId的索引,但deptId之后的索引name失效了
  • 总结:
    范围查询条件右边的索引会失效
  • 4.不能使用不等于
#1.不等于
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age != 30 ;
+----+-------------+-------+------------+------+---------------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys       | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | idx_age_deptId_name | NULL | NULL    | NULL | 499145 |    50.00 | Using where |
+----+-------------+-------+------------+------+---------------------+------+---------+------+--------+----------+-------------+

#2.大于
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age > 30 ;
+----+-------------+-------+------------+------+---------------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys       | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | idx_age_deptId_name | NULL | NULL    | NULL | 499145 |    50.00 | Using where |
+----+-------------+-------+------------+------+---------------------+------+---------+------+--------+----------+-------------+

#3.小于
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age < 300 ;
+----+-------------+-------+------------+------+---------------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys       | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | idx_age_deptId_name | NULL | NULL    | NULL | 499145 |    50.00 | Using where |
+----+-------------+-------+------------+------+---------------------+------+---------+------+--------+----------+-------------+

#4.不等于
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age <> 300 ;
+----+-------------+-------+------------+------+---------------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys       | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | idx_age_deptId_name | NULL | NULL    | NULL | 499145 |    50.00 | Using where |
+----+-------------+-------+------------+------+---------------------+------+---------+------+--------+----------+-------------+
  • 总结:
    不等于(!=或者<>)索引会失效,导致全表扫描
  • 5.可以使用is null,不可以使用is not null
#1.is null 
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age is null ;
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys       | key                 | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | emp   | NULL       | ref  | idx_age_deptId_name | idx_age_deptId_name | 5       | const |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-----------------------+

#2.is not null
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age is not null ;
+----+-------------+-------+------------+------+---------------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys       | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | idx_age_deptId_name | NULL | NULL    | NULL | 499145 |    50.00 | Using where |
+----+-------------+-------+------------+------+---------------------+------+---------+------+--------+----------+-------------+
  • 对比分析:
    1)1和2 对比,1中的is null索引未失效,type:ref等同于常量查询,2中的索引失效了
  • 总结:
    is null索引不会失效,is not null 会导致索引失效
  • 6.like 不能以通配符开头,如('%abc')
#1.like语句不含通配符
mysql> EXPLAIN SELECT SQL_NO_CACHE age,deptId,name FROM emp WHERE name like 'abwL3q' ;
+----+-------------+-------+------------+-------+---------------+---------------------+---------+------+--------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key                 | key_len | ref  | rows   | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------------------+---------+------+--------+----------+--------------------------+
|  1 | SIMPLE      | emp   | NULL       | index | NULL          | idx_age_deptId_name | 133     | NULL | 499145 |    11.11 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------------------+---------+------+--------+----------+--------------------------+

#2.like语句以通配符开头
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE name like '%abwL3q' ;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 499145 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+

#3.like语句包含通配符但不以通配符开头
mysql> EXPLAIN SELECT SQL_NO_CACHE age,deptId,name  FROM emp WHERE name like 'abw%L3q' ;
+----+-------------+-------+------------+-------+---------------+---------------------+---------+------+--------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key                 | key_len | ref  | rows   | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------------------+---------+------+--------+----------+--------------------------+
|  1 | SIMPLE      | emp   | NULL       | index | NULL          | idx_age_deptId_name | 133     | NULL | 499145 |    11.11 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------------------+---------+------+--------+----------+--------------------------+
  • 对比分析:
    1)1、2和3 对比,1和3的索引未失效,2中的索引失效了
  • 总结:
    like语句以通配符%开头的查询索引会失效,导致全表扫描
  • 7.字符串不加单引号
#1.字符串查询加单引号
mysql> EXPLAIN SELECT SQL_NO_CACHE id,name FROM emp WHERE name = '123' ;
+----+-------------+-------+------------+-------+---------------+---------------------+---------+------+--------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key                 | key_len | ref  | rows   | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------------------+---------+------+--------+----------+--------------------------+
|  1 | SIMPLE      | emp   | NULL       | index | NULL          | idx_age_deptId_name | 133     | NULL | 499145 |    10.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------------------+---------+------+--------+----------+--------------------------+

#2.字符串查询不加单引号
mysql> EXPLAIN SELECT SQL_NO_CACHE id,name,empno FROM emp WHERE name = 123 ;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 499145 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
  • 总结:
    字符串不加单引号索引会失效,导致全表扫描
  • 索引建议:
  • 1.单键索引: 尽量选择针对当前过滤性更好的索引,参考索引失效
  • 2.创建组合索引: 创建组合索引时,查询条件中过滤性更好的字段在组合索引中的位置越靠前越好,根据查询条件,可以适当select字段顺序或where字段顺序
  • 3.选择组合索引: 选择组合索引时,尽量选择包含当前where条件更多字段的索引,如果出现范围查询等,尽量将范围查询或引起索引失效的字段放到组合索引的后面

三.单表查询优化

  • explain分析
SELECT SQL_NO_CACHE * FROM emp WHERE age =30 AND empno <101000 ORDER BY NAME ;
  • 0.未添加索引:(初始状态)
    在这里插入图片描述
    在这里插入图片描述
  • 1.优化-创建3字段的组合索引
#创建3字段组合索引,where条件后有3个字段age、empno、name,创建组合索引
CREATE index idx_age_empno_name on emp(age,empno,NAME);

在这里插入图片描述
在这里插入图片描述

  • 2.优化-创建2字段的组合索引
#先删除已有的索引
drop index idx_age_empno_name on emp ;
#创建2字段组合索引,where条件后有2个字段age、name,创建组合索引(因为empno为不等于条件查询,会导致其后的查询条件索引失效,索引empno和name我们只能二选一)
CREATE index idx_age_name on emp(age,name);

在这里插入图片描述
在这里插入图片描述

  • 3.优化-创建2字段的组合索引
#先删除已有的索引
drop index idx_age_name on emp ;
#创建2字段组合索引,where条件后有2个字段age、empno
CREATE index idx_age_empno on emp(age,empno);

在这里插入图片描述
在这里插入图片描述

  • 上述3种组合索引对比分析:

通过Explain执行分析器的分析结果,组合索引idx_age_name 肯定是最优的因为type为ref好于其它的range,Extra中也未出现Using filesort,但通过查询结果的耗时来说却是组合索引idx_age_name耗时最长。
为什么组合索引idx_age_name的Explain的明明显示是最优的,但执行耗时却是最长的?

原因: 所有的排序都是在条件过滤之后才执行的,当50万条数据被过滤条件刷选之后,可能就几百条数据需要排序,这几百条数据消耗的性能很小,即使在排序字段上增加索引来提高效率,提高的空间也很小,而使用empno <101000这个条件未使用到索引,需要对几万条数据进行扫描,非常消耗性能,所以对该字段进行扫描是有极大的性能提高空间的

结论: 当范围条件和group by 或者 order by 的字段出现二选一时 ,优先观察条件字段的过滤数量,如果过滤的数据足够多,而需要排序的数据并不多时,优先把索引放在范围字段上。反之,亦然


四.关联查询优化

  • 创建表
CREATE TABLE IF NOT EXISTS `class` ( `id` INT ( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT, `card` INT ( 10 ) UNSIGNED NOT NULL, PRIMARY KEY ( `id` ) );

CREATE TABLE IFNOT EXISTS `book` ( `bookid` INT ( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT, `card` INT ( 10 ) UNSIGNED NOT NULL, PRIMARY KEY ( `bookid` ) );

  • 插入数据
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  • explain分析
  • 0.未添加索引:(初始状态)
EXPLAIN SELECT SQL_NO_CACHE * FROM class LEFT JOIN book ON class.card = book.card;

在这里插入图片描述

  • 1.优化-在驱动表class上添加索引后分析
CREATE INDEX idx_card_class ON class (card);

在这里插入图片描述

  • 2.优化-继续在被驱动表book上添加索引后分析
CREATE INDEX idx_card_book ON book (card);

在这里插入图片描述

  • 添加索引前后对比分析:

我们分析的union查询为 class LEFT JOIN book ON class.card = book.card ,其中class为驱动表,book为被驱动表,通过对比发现在添加idx_card_book后性能有明显提升。这是由左连接的特性决定的,LEFT JOIN条件用于确定从右表搜索行,左边的一定都有,所有就会有class 表的rows不论我们怎么优化其rows都不变,所以我们只能优化右表(即需要在右表上创建索引)

  • 关联查询索引建议:

1)保证 被驱动的索引
2)left join,选择小表作为驱动表,大表作为被驱动表 (right join则相反)
3)inner join,会自动选择小的结果集作为驱动表
4) 子查询尽量不要放在被驱动表,有可能使用不到索引


四.子查询索引优化:

  • explain分析
  • 1.子查询中使用not in,未添加索引
EXPLAIN SELECT SQL_NO_CACHE age  FROM emp a WHERE id NOT IN ( SELECT ceo FROM dept  WHERE ceo IS NOT NULL ) GROUP BY age HAVING count( age ) < 10000;

在这里插入图片描述

  • 2.子查询中使用not in,添加索引
   CREATE index idx_ceo_dept on dept(ceo);

在这里插入图片描述

  • 3.优化:将子查询的not in改为left outer join on xxx is null
EXPLAIN SELECT SQL_NO_CACHE age FROM emp LEFT OUTER JOIN dept ON emp.id = dept.ceo WHERE dept.ceo IS NULL GROUP BY age HAVING count( age ) < 10000;

在这里插入图片描述

  • 对比分析:

1和2对比,在添加索引后,扫描的物理行数减少了一半;
子查询改为left outer join 后,扫描的物理函数有一个指数级的提升,这是由于我们利用了覆盖索引和is null不会索引失效的特性。

  • 子查询优化建议:

尽量不要使用not in或not exists,当子查询中出现 not in时,将子查询改为left outer join on


五.order by关键字优化:

  • explain分析
   CREATE index idx_age_deptid_name on emp (age,deptId,NAME)
  • 1.无过滤条件,必然会出现 Using filesort
EXPLAIN SELECT * FROM emp ORDER BY age,deptId ;

在这里插入图片描述

  • 2.limit过滤条件,索引生效
EXPLAIN SELECT * FROM emp ORDER BY age,deptId LIMIT 10 ;

在这里插入图片描述

  • 3.where条件限制,order by 2 字段(2字段为已建立组合索引字段,并按照组合索引的顺序排序),索引生效
EXPLAIN SELECT * FROM emp WHERE age = 45 ORDER BY deptId ,name;

在这里插入图片描述

  • 4.where条件限制,order by 2 字段(2字段为已建立组合索引字段,但排序的顺序和组合索引的顺序不一致),出现Using filesort
EXPLAIN SELECT * FROM emp WHERE age = 45 ORDER BY name ,deptId;

在这里插入图片描述

  • 5.where条件限制,order by 2 字段(其中某一字段为非组合索引字段),出现Using filesort
EXPLAIN SELECT * FROM emp WHERE age = 45 ORDER BY deptId ,empno;

在这里插入图片描述

  • 6.where条件限制,where and条件的值确定,排序条件中有该定值字段,即使order by后字段顺序和组合索引的顺序不一致(排序字段去除定值字段后剩余字段后组合索引顺序一致),此时不会出现Using filesort
EXPLAIN SELECT * FROM emp WHERE age = 45 ORDER BY deptId,age;

在这里插入图片描述

  • 7.order by后跟的排序字段是desc和asc 组合,不论排序顺序是否和组合索引顺序一致,必然会出现Using filesort
EXPLAIN SELECT * FROM emp WHERE age = 45 ORDER BY deptId DESC,name ASC;

在这里插入图片描述

  • order by 索引对比:

1)无过滤条件(无where和limit)的order by 必然会出现 Using filesort
2)过滤条件中的字段和order by 后跟的字段的顺序不一致,必然会出现 Using filesort
3)order by后跟的字段排序即有DESC也有ASC,必然会出现Using filesort
4)where条件的值确定,且order by后跟了跟了where条件的排序字段(order by 字段去除定值字段后剩余单字段),即使order by后跟的字段和组合索引字段顺序不一致,也不会出现Using filesort

  • order by 索引建议:

1)尽量在索引列上完成排序操作,遵循最佳做前缀法则
2)order by子句,尽量使用index方式排序,避免使用filesort方式

  • 无索引order by排序算法::

双路排序:扫描2次磁盘获取最终数据,第一次扫描读取行指针和order by字段列的值进行排序,刷选出需要的排完序的行指针,第二次扫描读取所需的全部数据
单路排序:从磁盘中读取查询所需的全部列,在buff中进行排序,排序后进行输出,只需要扫描一次磁盘
问题:双路排序相比单路排序会减少I/O次数,但会消耗更多的内存,如果取出的数据总大小超出sort_buffer的容量,会创建temp文件进行多路合并,反而会增加I/O次数,同理双路排序也会出现同样的问题,但单路排序的相对几率要高很多
优化:
1)增大sort_buffer_size
2)增大max_length_for_sort_data
3)减少select后跟的查询字段


六. limit 分页查询优化:

  • explain分析
  • 0.未加索引(初始状态)
   EXPLAIN SELECT SQL_NO_CACHE * FROM emp ORDER BY deptId LIMIT  10000,40;

在这里插入图片描述
在这里插入图片描述

  • 1.优化:给deptId添加索引
CREATE INDEX idx_deptid_emp on emp (deptId);

在这里插入图片描述
在这里插入图片描述

  • 2.再优化:利用覆盖索引将需要的数据行的主键获取到,再用这个主键列与数据表做关联
EXPLAIN SELECT SQL_NO_CACHE * FROM emp INNER JOIN (SELECT id FROM emp ORDER BY deptId	LIMIT 10000,40) a ON a.id = emp.id;

在这里插入图片描述
在这里插入图片描述

  • 上述limit 索引Explain分析:

1)之所以SELECT SQL_NO_CACHE * FROM emp ORDER BY deptId LIMIT 10000,40; 在添加索引前后的Explain及实际查询耗时都无明显变化,是因为我们查询的字段为select*,查询了全部的字段,这时分析器认为是否使用索引已无必要,mysql使用了filesort,一次加载全部数据在内存中进行数据处理,但当我们把select*改为select id后,mysql用到了覆盖索引,即有效使用到了索引,所以效率有了明显的提升


  • 1
    点赞
  • 0
    评论
  • 8
    收藏
  • 打赏
    打赏
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页

打赏作者

残阳蓝夕

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值