首页 > 代码库 > SQL优化单表案例
SQL优化单表案例
数据准备：
-- 创建数据库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 TransferSource Server : localhost_3306Source Server Version : 50203Source Host : localhost:3306Source Database : db_testTarget Server Type : MYSQLTarget Server Version : 50203File Encoding : 65001Date: 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‘);
业务要求：查找性别为男性(0:女，1：男)，工作年限大于3年，年龄最小的员工。
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优化单表案例
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。