当前位置:Gxlcms > 数据库问题 > SQL优化单表案例

SQL优化单表案例

时间: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);

 

业务要求:查找性别为男性(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优化单表案例

标签:cte   cal   ext   logs   test   sql优化   ati   esc   foreign   

人气教程排行