Mysql-索引失效、關聯查詢優化、子查詢優化、order by優化、group by優化、分頁優化

一.建立索引实验数据

• 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分析

• 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.未添加索引：(初始状态)

• 1.优化-在驱动表class上添加索引后分析

• 2.优化-继续在被驱动表book上添加索引后分析

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

我们分析的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，未添加索引

• 2.子查询中使用not in，添加索引

• 3.优化：将子查询的not in改为left outer join on xxx is null

• 对比分析：

1和2对比，在添加索引后，扫描的物理行数减少了一半；

子查询改为left outer join 后，扫描的物理函数有一个指数级的提升，这是由于我们利用了覆盖索引和is null不会索引失效的特性。

• 子查询优化建议：

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

五.order by关键字优化：

• explain分析

• 1.无过滤条件，必然会出现 Using filesort

• 2.limit过滤条件，索引生效

• 3.where条件限制，order by 2 字段(2字段为已建立组合索引字段，并按照组合索引的顺序排序)，索引生效

• 4.where条件限制，order by 2 字段(2字段为已建立组合索引字段，但排序的顺序和组合索引的顺序不一致)，出现Using filesort

• 5.where条件限制，order by 2 字段(其中某一字段为非组合索引字段)，出现Using filesort

• 6.where条件限制，where and条件的值确定，排序条件中有该定值字段，即使order by后字段顺序和组合索引的顺序不一致(排序字段去除定值字段后剩余字段后组合索引顺序一致)，此时不会出现Using filesort

• 7.order by后跟的排序字段是desc和asc 组合，不论排序顺序是否和组合索引顺序一致，必然会出现Using filesort

• 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.未加索引(初始状态)

• 1.优化：给deptId添加索引

• 2.再优化：利用覆盖索引将需要的数据行的主键获取到，再用这个主键列与数据表做关联

• 上述limit 索引Explain分析：

1）之所以

SELECT SQL_NO_CACHE * FROM emp ORDER BY deptId LIMIT 10000,40;

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