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

最新推荐文章于 2024-03-21 14:32:07 发布
最新推荐文章于 2024-03-21 14:32:07 发布
阅读量1.4k 收藏 10
点赞数 1
分类专栏： Mysql 文章标签： Mysql索引优化 索引失效 关联查询优化 子查询优化 order by、group by、分页优化
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_41047933/article/details/87270985
版权

一.建立索引实验数据

  • 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
    点赞
  • 踩
  • 10
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
四、MySQL分页查询 + 子查询复习 学习笔记 （复习连接查询相关内容 详解）
12-14
8：分页查询 应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求 语法： SELECT 查询列表 FROM 表名 【JOIN type JOIN 表2 ON 连接条件 WHERE 筛选条件 GROUP BY 分组字段 HAVING 分组后的筛选 ORDER BY ...
mysql索引失效
weixin_47796247的博客
12-11 3812
某些查询的时候没有办法使用索引 比如下面的例子 ，因为没有办法使用数据结构 ，索引一般是hash或者btree结构，比如我们一般用某种数据结构二分法查询一个值，可以查到。但是我们查询一个结果，用二分法要求查询不属于某些值的，就没有办法使用数据结构，就使用不了索引。所以核心就是判断一个sql语句能不能通过数据结构来查询出结果，能查询出来就是能够使用索引，不能查询出来就是不能使用索引。，该索引失效，只有当or左右查询字段均为索引时，才会生效。，导致不能命中索引，引发失效。，导致不能命中索引,引发失效。
mysql 使用order by存在的问题与优化思考
xad669475795的博客
04-06 5189
前言：在很多实际业务中，往往需要涉及分页和排序，还存在where和order by一起使用的场景。开发人员往往没有关心mysql背后的逻辑，导致这部分查询在数据量大的情况下，存在查询缓慢的问题，即使部分开发针对这部分查询建立了索引，但是索引使用不恰当，也会带来额外的IO开销。当然，在业务体量不大的情况下，“乱用” ordey by，where, 分页问题不大。但是，个人觉得，事情应该尽可能做到极致，懂背后的逻辑才有意思。 一 limit分页 limit大数据下分页慢的本质：limit语句的查询时间与
Mysql索引失效的场景
最新发布
lmyxmm123的博客
03-21 1019
mysql 索引
mysql order by 索引问题综合分析
C18298182575的博客
06-02 1994
一，文章1总结：0，索引 与 查询条件 与 排序字段关系，Using filesort出现场景1.联合索引，最左匹配原则，不仅查询条件需要遵循，排序也需要遵循，查询+排序组合也要遵循2.extra 避免出现filesort，使用index排序3.关于排序算法，内存排序 还是 磁盘排序，缓冲区大小4.结论中，举例了，最左匹配的成功与否样例二，文章2此文主要说明以下问题1.为什么order by要加索引，从以下几个方面说明explan 是否使用索引key，扫描行数rows，extra优化。
mysql使用orderby 不起作用
Zyw907155124的博客
07-21 4917
mysql 使用order by排序的时候不起作用，
Mysql索引失效的常见原因&如何用好索引
H_Jason_的博客
10-11 1357
本篇文章主要介绍了索引失效的常见原因和如何用好索引，并以案例的形式进行了说明
MYsql 数据库0基础SQL语句实战精讲.docx
07-21
8. 子查询 38 9. 分页查询 46 10. 联合查询 48 11. 库和表的关联 49 1、整型 51 2、浮点型 51 3、字符型 51 4、日期型 52 5、二进制型 52 12. 自增长列 54 13. 事务处理 55 1、开启事务 56 2、编写事务需要的sql语句...
MySQL 5权威指南(第3版) 中文版 下载地址
12-11
　10.7 子查询 　10.8 保证数据的一致性 　10.9 找出冗余的数据记录 　10.10 数据表设计方案的改进 　10.11 对前n条或后n条记录进行处理 　10.12 以随机方式选择数据记录 　10.13 全文索引 　10.14 锁定 　...
MySQL5 权威指南第3版中文版_part1
02-12
　10.7 子查询 　10.8 保证数据的一致性 　10.9 找出冗余的数据记录 　10.10 数据表设计方案的改进 　10.11 对前n条或后n条记录进行处理 　10.12 以随机方式选择数据记录 　10.13 全文索引 　10.14 锁定 　10.15 ...
order by使用索引列排序时会失效吗?
libingbojava的博客
07-10 5712
order by使用索引列排序时会失效吗? 在使用order by对某列进行排序时, 其sql一定进行了多表联查的操作. 说白了肯定做了表关联. 既然有表关联, 那order by排序就可以对任意的表进行排序. 那么应该对那个表进行排序好点呢? 先说结论, order by后面跟的索引列只是对 驱动表才起作用, 也就是说如果是对驱动表进行排序, 那么order by后面的索引是起作用的, 但是如果order by后面跟的排序字段不是驱动表的, 那么这时索引是不起作用的. 问题来了 , 什么是驱动表?
mysql group 索引失效_介绍mysql索引失效的情况
weixin_42410477的博客
02-17 1242
mysql视频教程栏目索引失效的情况。索引对于MySQL而言，是非常重要的篇章。索引知识点也巨多，要想掌握透彻，需要逐个知识点一一击破，今天来先来聊聊哪些情况下会导致索引失效。图片总结版相关免费学习推荐：mysql视频教程全值匹配(索引最佳)explain select * from user where name = 'zhangsan' and age = 20 and pos = 'cxy'...
【MySQL高级】MySQL索引失效情况及避免方式【案例分析】
记录一名在读研究生的学习笔记、感悟、开发产物
06-27 1028
了解Mysql常见的索引失效方式，及其避免方法（尚硅谷课程学习笔记）
导致mysql索引失效的原因有哪些
搬砖农民工的博客
08-03 4589
本文将介绍mysql有哪些索引，索引失效有哪些原因，以及sql执行顺序
MySQL索引优化：索引失效以及不适合建立索引的场景
PeaK欢迎大家评论留言，一起交流Java技术
07-23 4120
索引失效以及不适合建立索引的场景，本篇文章是在看完尚硅谷宋红康老师的视频后总结的内容，希望可以和大家进行交流学习
15个必知的Mysql索引失效场景，别再踩坑了！
C18298182575的博客
06-15 2567
也就是说，当Mysql发现通过索引扫描的行记录数超过全表的10%-30%时，优化器可能会放弃走索引，自动变成全表扫描。此时，索引失效的原因与第三种情况一样，都是因为数据库要先进行全表扫描，获得数据之后再进行截取、计算，导致索引索引失效。的含义为：表示索引使用的字节数，根据这个值可以判断索引的使用情况，特别是在组合索引的时候，判断该索引有多少部分被使用到非常重要。当然，还有其他一些是否走索引的规则，这与索引的类型是B-tree索引还是位图索引也有关系，就不再详细展开。的条件再走一次索引反而是浪费了。
造成mysql 索引失效的原因
weixin_42610010的博客
01-11 533
MySQL 索引失效的原因有很多，下面是一些常见的原因： 数据类型不匹配：在建立索引时，如果查询条件的数据类型和索引字段的数据类型不匹配，那么索引将不能正常使用。 运算符不支持：如果在查询中使用不支持索引的运算符(如 != 或 <>)，那么索引将不能正常使用。 查询中使用了函数：如果在查询中使用了函数，那么索引将不能正常使用。 数据库统计信息过时：如果数据库统计信息过时，那么 ...
mysql 子查询添加索引_mysql 子查询 可以加索引优化吗
05-24
MySQL 的子查询是可以添加索引来优化查询性能的。通常情况下，可以为子查询中的子表添加索引来提高查询效率。 具体来说，如果子查询中的子表是一个较小的表，可以使用普通索引来优化；如果子表较大，可以使用覆盖索引或联合索引来优化。 另外，还可以在子查询中使用 EXISTS 或 NOT EXISTS 来代替子查询中的 SELECT 语句，这样可以避免使用子查询，更容易优化查询性能。 需要注意的是，添加索引并不是银弹，需要根据具体情况选择合适的索引策略。同时，过多的索引也会降低写操作的性能。因此，建议综合考虑查询频率、数据量、数据更新频率等因素，谨慎地添加索引。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值