时间：2021-07-01 10:21:17 帮助过：9人阅读
-- 创建数据库 mysql> create database db_index_case; Query OK, 1 row affected (0.00 sec) -- 查看数据库 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db02 | | db_index | | db_index_case | | db_test | | mysql | | performance_schema | | test | +--------------------+ 8 rows in set (0.01 sec)
/* Navicat MySQL Data Transfer Source Server : localhost_3306 Source Server Version : 50203 Source Host : localhost:3306 Source Database : db_test Target Server Type : MYSQL Target Server Version : 50203 File Encoding : 65001 Date: 2017-06-29 09:19:23 */ SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for tb_emp -- ---------------------------- DROP TABLE IF EXISTS `tb_emp`; CREATE TABLE `tb_emp` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT ‘主键‘, `name` varchar(20) NOT NULL COMMENT ‘姓名‘, `sex` char(1) NOT NULL COMMENT ‘性别‘, `age` int(11) NOT NULL COMMENT ‘年龄‘, `jobLen` int(11) NOT NULL COMMENT ‘工作年限‘, PRIMARY KEY (`id`), KEY `idx_emp_sja` (`sex`,`jobLen`,`age`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of tb_emp -- ---------------------------- INSERT INTO `tb_emp` VALUES (‘1‘, ‘张三‘, ‘1‘, ‘25‘, ‘3‘); INSERT INTO `tb_emp` VALUES (‘2‘, ‘李四‘, ‘1‘, ‘25‘, ‘3‘); INSERT INTO `tb_emp` VALUES (‘3‘, ‘王二‘, ‘1‘, ‘25‘, ‘3‘); INSERT INTO `tb_emp` VALUES (‘4‘, ‘麻子‘, ‘1‘, ‘25‘, ‘3‘); INSERT INTO `tb_emp` VALUES (‘5‘, ‘小花‘, ‘0‘, ‘25‘, ‘3‘); INSERT INTO `tb_emp` VALUES (‘6‘, ‘小明‘, ‘1‘, ‘27‘, ‘4‘); INSERT INTO `tb_emp` VALUES (‘7‘, ‘小四‘, ‘0‘, ‘27‘, ‘4‘); INSERT INTO `tb_emp` VALUES (‘8‘, ‘小李‘, ‘0‘, ‘27‘, ‘4‘); INSERT INTO `tb_emp` VALUES (‘9‘, ‘小丹‘, ‘0‘, ‘27‘, ‘4‘); INSERT INTO `tb_emp` VALUES (‘10‘, ‘小花‘, ‘0‘, ‘27‘, ‘4‘); INSERT INTO `tb_emp` VALUES (‘11‘, ‘小冷‘, ‘1‘, ‘24‘, ‘5‘); INSERT INTO `tb_emp` VALUES (‘12‘, ‘小邳‘, ‘0‘, ‘18‘, ‘5‘); INSERT INTO `tb_emp` VALUES (‘13‘, ‘小影‘, ‘0‘, ‘27‘, ‘5‘); INSERT INTO `tb_emp` VALUES (‘14‘, ‘小兰‘, ‘1‘, ‘32‘, ‘5‘); INSERT INTO `tb_emp` VALUES (‘15‘, ‘小翟‘, ‘1‘, ‘27‘, ‘5‘); INSERT INTO `tb_emp` VALUES (‘16‘, ‘大花‘, ‘1‘, ‘33‘, ‘10‘); INSERT INTO `tb_emp` VALUES (‘17‘, ‘大胖‘, ‘1‘, ‘28‘, ‘10‘); INSERT INTO `tb_emp` VALUES (‘18‘, ‘小胖‘, ‘1‘, ‘27‘, ‘3‘); INSERT INTO `tb_emp` VALUES (‘19‘, ‘骚欣‘, ‘1‘, ‘30‘, ‘10‘); INSERT INTO `tb_emp` VALUES (‘20‘, ‘雷子‘, ‘1‘, ‘30‘, ‘10‘);
mysql> select * from tb_emp where sex = ‘1‘ and jobLen > 3 order by age limit 1; +----+--------+-----+-----+--------+ | id | name | sex | age | jobLen | +----+--------+-----+-----+--------+ | 11 | 小冷 | 1 | 24 | 5 | +----+--------+-----+-----+--------+ 1 row in set (0.00 sec)
mysql> explain select * from tb_emp where sex = ‘1‘ and jobLen > 3 order by age limit 1; +----+-------------+--------+------+---------------+------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+-----------------------------+ | 1 | SIMPLE | tb_emp | ALL | NULL | NULL | NULL | NULL | 20 | Using where; Using filesort | +----+-------------+--------+------+---------------+------+---------+------+------+-----------------------------+ 1 row in set (0.00 sec)
结论：type为ALL，即最坏的情况；key为null，没有用到索引；Extra中含有Using filesort，不是按照表内的索引进行排序。
mysql> create index idx_emp_sex_jobLen_age on tb_emp(sex, jobLen, age); Query OK, 0 rows affected (1.59 sec) Records: 0 Duplicates: 0 Warnings: 0 -- 查看索引 mysql> show index from tb_emp; +--------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | tb_emp | 0 | PRIMARY | 1 | id | A | 20 | NULL | NULL | | BTREE | | | | tb_emp | 1 | idx_emp_sex_jobLen_age | 1 | sex | A | 4 | NULL | NULL | | BTREE | | | | tb_emp | 1 | idx_emp_sex_jobLen_age | 2 | jobLen | A | 20 | NULL | NULL | | BTREE | | | | tb_emp | 1 | idx_emp_sex_jobLen_age | 3 | age | A | 20 | NULL | NULL | | BTREE | | | +--------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 4 rows in set (0.00 sec)
mysql> explain select * from tb_emp where sex = ‘1‘ and jobLen > 3 order by age limit 1; +----+-------------+--------+------+------------------------+------------------------+---------+-------+------+----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+------------------------+------------------------+---------+-------+------+----------------------------------------------------+ | 1 | SIMPLE | tb_emp | ref | idx_emp_sex_jobLen_age | idx_emp_sex_jobLen_age | 3 | const | 8 | Using index condition; Using where; Using filesort | +----+-------------+--------+------+------------------------+------------------------+---------+-------+------+----------------------------------------------------+ 1 row in set (0.00 sec) -- 将 jobLen > 3 改变成 jobLen = 3 比较两个查询执行计划 mysql> explain select * from tb_emp where sex = ‘1‘ and jobLen = 3 order by age limit 1; +----+-------------+--------+------+------------------------+------------------------+---------+-------------+------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+------------------------+------------------------+---------+-------------+------+------------------------------------+ | 1 | SIMPLE | tb_emp | ref | idx_emp_sex_jobLen_age | idx_emp_sex_jobLen_age | 7 | const,const | 5 | Using index condition; Using where | +----+-------------+--------+------+------------------------+------------------------+---------+-------------+------+------------------------------------+ 1 row in set (0.00 sec)
结果：Extra 中还是存在 Using filesort。
分析：比对改变jobLen > 3 条件前后的查询执行计划，可以得出是因为 jobLen > 3 导致索引失效。因为按照 BTree 索引的工作原理，首先排序 sex，若是遇到相同的 sex 则再去排序 jobLen，若是遇到相同的 jobLen 则再去排序 age。按照联合索引顺序向下去排序，可是 where 后查询条件 jobLen > 3 是一个范围值( range )，导致索引失效，MySQL 无法对后面的 age 进行检索，即 range 类型查询字段后面的索引失效。
-- 删除索引 mysql> drop index idx_emp_sex_jobLen_age on tb_emp; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 -- 创建索引 mysql> create index idx_emp_sex_age on tb_emp(sex, age); Query OK, 0 rows affected (1.60 sec) Records: 0 Duplicates: 0 Warnings: 0 -- 查看索引 mysql> show index from tb_emp; +--------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | tb_emp | 0 | PRIMARY | 1 | id | A | 20 | NULL | NULL | | BTREE | | | | tb_emp | 1 | idx_emp_sex_age | 1 | sex | A | 4 | NULL | NULL | | BTREE | | | | tb_emp | 1 | idx_emp_sex_age | 2 | age | A | 20 | NULL | NULL | | BTREE | | | +--------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.00 sec)
mysql> explain select * from tb_emp where sex = ‘1‘ and jobLen > 3 order by age limit 1; +----+-------------+--------+------+-----------------+-----------------+---------+-------+------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+-----------------+-----------------+---------+-------+------+------------------------------------+ | 1 | SIMPLE | tb_emp | ref | idx_emp_sex_age | idx_emp_sex_age | 3 | const | 13 | Using index condition; Using where | +----+-------------+--------+------+-----------------+-----------------+---------+-------+------+------------------------------------+ 1 row in set (0.00 sec)
结果：Extra 中的 Using filesort 优化之后消失了，完成任务。
mysql> explain select * from tb_emp where sex = 1 and jobLen > 3 order by age limit 1; +----+-------------+--------+------+-----------------+------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+-----------------+------+---------+------+------+-----------------------------+ | 1 | SIMPLE | tb_emp | ALL | idx_emp_sex_age | NULL | NULL | NULL | 20 | Using where; Using filesort | +----+-------------+--------+------+-----------------+------+---------+------+------+-----------------------------+ 1 row in set (0.02 sec)
结果： type 为 ALL，key 为 NULL，rows 为 20，Extra 中包含 Using filesort。
查看表结构：
-- 查看表结构 mysql> desc tb_emp; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | sex | char(1) | NO | MUL | NULL | | | age | int(11) | NO | | NULL | | | jobLen | int(11) | NO | | NULL | | +--------+-------------+------+-----+---------+----------------+ 5 rows in set (0.02 sec)
分析：因为 sex 为 char 类型的字段， sql 查询条件中 sex = 1 是数字类型。因为类型不匹配，MySQL 会自动解析字符类型，会浪费性能。
SQL优化单表案例
标签：cte cal ext logs test sql优化 ati esc foreign