SQL性能优化技巧，常见优化10经验

小咪咪 2023-09-29 13:47 18阅读 0赞

里就给小伙伴们带来工作中常用的一些 SQL 性能优化技巧总结，包括常见优化十经验、order by 与 group by 优化、分页查询优化、join 关联查询优化、in 和 exsits 优化、count(\*)查询优化。

## 一、常见优化十经验 ##

**其实这个十经验不一定准确**，通过上一篇 MySQL再深入执行计划之trace工具 我们已经知道 **MySQL 执行查询语句时会进行成本分析，数据量和实际数据值会影响到 MySQL 的实际查询过程**

-- 示例表
    CREATE TABLE `employees` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
      `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
      `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
      `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
      PRIMARY KEY (`id`),
      KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';
    
    INSERT INTO employees(name,age,position,hire_time) VALUES('itwxe',22,'manager',NOW());
    INSERT INTO employees(name,age,position,hire_time) VALUES('weiwei', 23,'test',NOW());
    INSERT INTO employees(name,age,position,hire_time) VALUES('leilei',23,'dev',NOW());
    
    -- 插入10w条测试数据
    drop procedure if exists insert_employees; 
    delimiter $$
    create procedure insert_employees()        
    begin
      declare i int;                    
      set i = 1;                          
      while(i <= 100000)do                 
        insert into employees(name, age, position) values(CONCAT('itwxe', i), rand() * 42 + 18, 'dev');  
        set i = i + 1;                       
      end while;
    end$$
    delimiter ;
    call insert_employees();

## 1. 尽量全值匹配 ##

explain select * from employees where name = 'itwxe';
    explain select * from employees where name = 'itwxe' and age = 22;
    explain select * from employees where name = 'itwxe' and age = 22 and position = 'manager';

![2abcf65dd49baa97d96d051ccf12f15e.png][]

记住这三个 key\_len 的值，idx\_name\_age\_position (name,age,position)由这三个字段组成，74代表使用了 name 列；78代表使用了 name,age 列；140代表使用了 name,age,position 列。

## 2. 最左前缀原则 ##

在使用联合索引的时候要特别注意最左前缀原则，即查询从**联合索引**的最左前列开始并且不跳过索引中的列。

explain select * from employees where name = 'itwxe' and age = '18';
    explain select * from employees where name = 'itwxe' and position = 'manager';
    explain select * from employees where position = 'manager';

![0d81968f58038aba9c35d12a520188a1.png][]

应该也是比较好理解的，不过需要注意的是和查询 SQL 书写的顺序无关，最左指的是**联合索引创建时列的顺序**。例如 where 中颠倒顺序还是会使用 idx\_name\_age\_position (name,age,position) 中的三个列索引查询。

explain select * from employees where name = 'itwxe' and age = 22 and position = 'manager';
    explain select * from employees where age = 22 and position = 'manager' and name = 'itwxe';
    explain select * from employees where position = 'manager' and name = 'itwxe' and age = 22;

![312269ceedf4b7836488060d2cefd5e5.png][]

可以看到即使颠倒了顺序，三个的执行计划也是一毛一样的。

## 3. 不在索引列上做任何操作(计算、函数、(自动/手动)类型转换)，会导致索引失效而转向全表扫描 ##

需要注意的是这里说的索引列任何操作(计算、函数、(自动/手动)类型转换)不做操作指的是 where 条件之后的，而不是查询结果字段里面的。

例如对 name 列进行 left 函数操作。

explain select * from employees where name = 'weiwei';
    explain select * from employees where left(name,6) = 'weiwei';

![4fd1d7947c7231a8de3ef8a7a9800730.png][]

## 4. 存储引擎不能使用索引中范围条件右边的列 ##

explain select * from employees where name = 'itwxe' and age = 22 and position = 'manager';
    explain select * from employees where name = 'itwxe' and age > 22 and position = 'manager';

![e292497192662f12b7f08aeee5fa5495.png][]

可以看到第二条 SQL 使用了 name,age 列作为索引来查询，position 并没有使用。

## 5. 尽量使用覆盖索引，减少select \*语句 ##

覆盖索引前面的文章提到过，不赘述了。

## 6. MySQL 在使用不等于( != 或者 <> )，not in，not exists 的时候无法使用索引会导致全表扫描 ##

<、>、<=、>= 这些，MySQL 内部优化器会根据检索比例、表大小等多个因素计算查询成本是否使用索引。

explain select * from employees where name != 'itwxe';

![214522c43c275b9904b3a38177dc308a.png][]

## 7. is null 和 is not null 一般情况下也无法使用索引 ##

explain select * from employees where name is null;
    explain select * from employees where name is not null;

![5726f93338bd7de564b16899605c2632.png][]

## 8. like 以通配符开头(’%itwxe…’) MySQL 索引失效会变成全表扫描操作 ##

explain select * from employees where name like 'wei%';
    explain select * from employees where name like '%wei';

![7d87fbc2800e4a619ca525dc8853f631.png][]

相信理解 B+tree 底层数据结构的小伙伴都很容易就知道为啥了，解决办法：

*  根据业务建立联合索引，使用覆盖索引查询。
 *  不能使用覆盖索引的则借助 ES 等搜索引擎。

查询中可以简单的把 like KK% 理解为 = 常量，%KK和%KK% 理解为 范围查询。

这里引入一个**索引下推**的概念：

explain select * from employees where name like 'weiwei%' and age = 22 and position = 'manager';
    explain select * from employees where name like 'itwxe%' and age = 22 and position = 'manager';

![fc6e6d53d703256eaeab84cab377a2f7.png][]

可以看到第一条SQL中 name = 'weiwei%' ，根据 B+tree 的结构可以知道其后的 age,position 列是无序的，应该无法使用 age,position 列过滤数据才对，但是最后的 key\_len 确是140，也就意味着 MySQL 利用到了 name,age,postion 三个列来查询，这是因为 MySQL 在5.6版本做的优化，引入了索引下推。

**索引下推**可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。

即当 name = 'weiwei%' 过滤时，使用了索引下推优化，过滤 name 时同时还会在索引里过滤 age,position 两个列的条件，拿着过滤完剩下的索引对应的主键 id 再回表查整行数据。

那么很简单，第二条 SQL 没有使用索引的原因是因为 MySQL 计算使用索引下推过滤出数据后，查询二级索引+回表的查询成本大于全表扫描，所以 MySQL 选择全表扫描。

## 9. 类型不匹配 MySQL 自动转型导致索引失效 ##

字符串不加单引号索引失效，亦或是数值类型加单引号索引失效，也就是第三点中提到的自动类型转换(也叫隐式转换)导致索引失效。

## 10. 少用 or 或 in ，用它查询时，MySQL 不一定使用索引 ##

MySQL 内部优化器会根据检索比例、表大小等多个因素计算查询成本是否使用索引。

## 二、order by 和 group by 优化 ##

## 1. order by 优化 ##

在前面的文章中小二给大家介绍了索引的底层数据结构，知道了索引本身就是一种**排好序**的**数据结构**，所以排序优化最好的办法就是落实到索引上，这样查询出来的数据就已经排好序了，这种排序在 MySQL 中被称之为 Using Index，即覆盖索引。那么如果查询出来后的数据本身没有按所需字段排序，那么就会出现 Using filesort，即文件排序。

所以，我们要优化 order by，那么主要就是消灭低效的 Using filesort，建立合适的联合索引使用覆盖索引来排序。

**Using filesort文件排序原理详解**

Using filesort 分为**单路排序**和**双路排序(又叫回表排序模式)**。

*  单路排序：一次性取出满足条件行的所有字段，然后在 sort buffer 中进行排序；用 trace 工具可以看到 sort\_mode 信息里显示 < sort\_key, additional\_fields > 或者 < sort\_key, packed\_additional\_fields >
 *  双路排序：是首先根据相应的条件取出相应的**排序字段**和**可以直接定位行数据的行 ID**，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；用 trace 工具可以看到sort\_mode 信息里显示 < sort\_key, rowid >

MySQL 通过比较系统变量 max\_length\_for\_sort\_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式。

*  如果字段的总长度小于 max\_length\_for\_sort\_data ，那么使用单路排序模式
 *  如果字段的总长度大于 max\_length\_for\_sort\_data ，那么使用双路排序模式

接下来使用 trace 工具来瞅瞅单路排序和双路排序：

-- 单路排序
    mysql> set session optimizer_trace="enabled=on", end_markers_in_json=on;
    
    mysql> select * from employees where name = 'itwxe' order by position;
    
    mysql> select * from information_schema.OPTIMIZER_TRACE;
    
    "join_execution": {  -- sql执行阶段
      "select#": 1,
      "steps": [
        {
          "filesort_information": [
            {
              "direction": "asc",
              "table": "`employees`",
              "field": "position"
            }
          ] /* filesort_information */,
          "filesort_priority_queue_optimization": {
            "usable": false,
            "cause": "not applicable (no LIMIT)"
          } /* filesort_priority_queue_optimization */,
          "filesort_execution": [
          ] /* filesort_execution */,
          "filesort_summary": {  -- 文件排序信息
            "rows": 1,  -- 预计扫描行数
            "examined_rows": 1,  -- 参与排序的行数
            "number_of_tmp_files": 0,  -- 使用临时文件的个数，这个值如果为0代表全部使用的sort_buffer内存排序，否则使用的 磁盘文件排序
            "sort_buffer_size": 262056,  -- 排序缓存的大小，单位Byte
            "sort_mode": "<sort_key, packed_additional_fields>"  -- 排序方式，这里用的单路排序
          } /* filesort_summary */
        }
      ] /* steps */
    } /* join_execution */
    
    -- 双路排序
    mysql> set max_length_for_sort_data = 10;  -- employees表所有字段长度总和哪怕一条记录也肯定大于10字节
    
    mysql> select * from employees where name = 'itwxe' order by position;
    
    mysql> select * from information_schema.OPTIMIZER_TRACE;
    
    "join_execution": {
      "select#": 1,
      "steps": [
        {
          "filesort_information": [
            {
              "direction": "asc",
              "table": "`employees`",
              "field": "position"
            }
          ] /* filesort_information */,
          "filesort_priority_queue_optimization": {
            "usable": false,
            "cause": "not applicable (no LIMIT)"
          } /* filesort_priority_queue_optimization */,
          "filesort_execution": [
          ] /* filesort_execution */,
          "filesort_summary": {
            "rows": 1,
            "examined_rows": 1,
            "number_of_tmp_files": 0,
            "sort_buffer_size": 262136,
            "sort_mode": "<sort_key, rowid>"  -- 排序方式，这里用的双路排序
          } /* filesort_summary */
        }
      ] /* steps */
    } /* join_execution */

**单路排序**的详细过程：

1.  从索引 name 找到第一个满足 name = 'itwxe' 条件的主键 id
2.  根据主键 id 取出整行，**取出所有字段的值，存入 sort\_buffer 中**
3.  从索引 name 找到下一个满足 name = 'itwxe' 条件的主键 id。
4.  重复步骤 2、3 直到不满足 name = 'itwxe'
5.  对 sort\_buffer 中的数据按照字段 position 进行排序
6.  返回结果给客户端

**双路排序**的详细过程：

1.  从索引 name 找到第一个满足 name = 'itwxe' 的主键id
2.  根据主键 id 取出整行，**把排序字段 position 和主键 id 这两个字段放到 sort buffer 中**
3.  从索引 name 取下一个满足 name = 'itwxe' 记录的主键 id
4.  重复 3、4 直到不满足 name = 'itwxe'
5.  对 sort\_buffer 中的字段 position 和主键 id 按照字段 position 进行排序
6.  遍历排序好的 id 和字段 position，按照 id 的值**回到原表**中取出所有字段的值返回给客户端

**总结**：单路排序会将整行所有数据缓存到 sort buffer 中，双路排序只将主键id和排序字段放入到 sort buffer 中排序，在根据排序好的数据，从原来表中根据id查询数据返回给客户端。

> 如何选项单路排序还是多路排序？

MySQL 优化器使用双路排序还是单路排序是有自己的算法判断的，如果查询的列字段大于 max\_length\_for\_sort\_data 变量，则会使用双路排序，反之则会使用单路排序，单路排序速度是更快的，不过比较占据内存，如果在内存空间允许的情况下想要使用单路排序的话，可以增加 max\_length\_for\_sort\_data 变量的大小。

**不过需要注意**，如果全部使用 sort\_buffer 内存排序一般情况下效率会高于磁盘文件排序，但不能因为这个就随便增大 sort\_buffer(默认1M)，mysql很多参数设置都是做过优化的，不要轻易调整。

## 2. group by 优化 ##

group by 与 order by 很类似，其实质是**先排序后分组**，遵照**索引创建顺序的最左前缀法则**。对于 group by 的优化**如果不需要排序的可以加上 order by null 禁止排序**。

## 三、分页查询优化 ##

分页查询优化示例表仍为 employees。

select * from employees limit 90000,10

很多时候我们业务系统实现分页功能可能会用如下 SQL 实现，看似 MySQL 是取90001行开始的10条记录，但是实际上 MySQL 在处理这个分页的时候是先读取前 90010 条记录，然后把前90000条记录舍弃，取出90001-90010的数据返回给客户端。因此如果要查询一张大表比较靠后的数据，执行效率是很低的。

## 1. 根据自增且连续的主键排序的分页查询 ##

select * from employees where id > 90000 limit 10;

原理：根据主键索引 id 排除 <90000 的数据，取后10条数据避免全表扫描。

缺点：如果主键 id 不连续，或者中间有删除数据，则无法实现效果，所以通常使用下面的第二种方式。

## 2. 根据非主键字段排序的分页查询 ##

select * from employees order by name limit 90000,10;
    select * from employees ed_all inner join (select id from employees order by name limit 90000,10) ed_id on ed_all.id = ed_id.id;

![060318c3a7f0738f9e151a11e756cc92.png][]

可以看到查询相同的结果，但是非常大的查询速度差距，这还只是10w+的测试数据，加入是100w呢，差距得多大嘞！

![a513976fc2efbee874b380d54a0932ec.png][]

阿里Java开发手册中也对这种情况进行了说明，非常推荐各位小伙伴们看下阿里Java开发手册，其中专门有一个章节对 MySQL 开发规范进行了说明。

![0e86c5117c25d8f9c79c043eacf58d12.png][]

## 四、join关联查询优化 ##

-- 示例表
    CREATE TABLE `t1` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `a` int(11) DEFAULT NULL,
      `b` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `idx_a` (`a`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    create table t2 like t1;
    
    -- 往t1表插入1w行测试数据
    drop procedure if exists insert_t1;
    delimiter $$
    create procedure insert_t1()
    begin
      declare i int;
      set i = 1;
      while(i <= 10000)do
        insert into t1(a,b) values(i,i);
        set i = i + 1;                  
      end while;
    end$$
    delimiter ;
    call insert_t1();
    
    -- 往t2表插入100行测试数据
    drop procedure if exists insert_t2;
    delimiter $$
    create procedure insert_t2()
    begin
      declare i int;
      set i = 1;
      while(i <= 100)do
        insert into t2(a,b) values(i,i);
        set i = i + 1;                  
      end while;
    end$$
    delimiter ;
    call insert_t2();

## 1. 表关联常见的两种算法 ##

## 嵌套循环连接Nested-Loop Join(NLJ) 算法 ##

**NLJ 算法**一次一行循环地从第一张表(**驱动表**)中读取行，在这行数据中取到关联字段，根据关联字段在另一张表(**被驱动表**)里取出满足条件的行，然后取出两张表的结果合集。

explain select * from t1 inner join t2 on t1.a = t2.a;

![b72b3052256d22139fd90abc857dba76.png][]

从执行计划可以看出，t2 作为**驱动表**，t1 作为**被驱动表**。先执行的就是驱动表(执行计划结果的 id如果一样则按从上到下顺序执行 SQL)，优化器**通常**会优先选择小表做驱动表，用 where 条件过滤完驱动表，然后再跟被驱动表做关联查询。**所以使用 inner join 时，排在前面的表并不一定就是驱动表**。

*  当使用 left join 时，左表是驱动表，右表是被驱动表。
 *  当使用 right join 时，右表时驱动表，左表是被驱动表。
 *  当使用 inner join 时，MySQL **通常**会选择数据量比较小表作为**驱动表**，大表作为**被驱动表**。

**注意**：在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，**过滤完成之后**，计算参与 join 的各个字段的总数据量，**数据量小的那个表，就是“小表”**，应该作为驱动表。**而不是简单的比较两个表的总数据量。**

**上面 SQL 的大致流程如下：**

*  从表 t2 中读取一行数据(如果t2表有查询过滤条件的，用先用条件过滤完，再从过滤结果里取出一行数据)。
 *  从第 1 步的数据中，取出关联字段 a，到表 t1 中查找。
 *  取出表 t1 中满足条件的行，跟 t2 中获取到的结果合并，作为结果返回给客户端。
 *  重复上面 3 步。

整个过程会读取 t2 表的所有数据(**扫描100行**)，然后遍历这每行数据中字段 a 的值，根据 t2 表中 a 的值索引扫描 t1 表中的对应行(**扫描100次 t1 表的索引，1次扫描可以认为最终只扫描 t1 表一行完整数据，也就是总共 t1 表也扫描了100行**)。因此整个过程扫描了200 行。

如果连接查询的列上没有索引，NLJ 算法性能会比较低，那么 MySQL 则会选择 BNL 算法。

## 基于块的嵌套循环连接Block Nested-Loop Join(BNL)算法 ##

BNL 算法把**驱动表**的数据读入到 join\_buffer(连接查询缓存) 中，然后扫描**被驱动表**，把**被驱动表**每一行取出来跟 join\_buffer 中的数据做对比。

explain select * from t1 inner join t2 on t1.b = t2.b;

![bee23df0b968dfdd7b15e74fdd3d3011.png][]

Extra 中的 Using join buffer (Block Nested Loop) 说明该关联查询使用的是 BNL 算法。同时可以看到 t2 仍然作为驱动表，t1 作为被驱动表。

**上面 SQL 的大致流程如下：**

*  把 t2 的所有数据放入到 **join\_buffer** 中。
 *  把表 t1 中每一行取出来，跟 join\_buffer 中的数据做对比。
 *  返回满足 join 条件的数据。

整个过程对表 t1 和 t2 都做了一次全表扫描，因此扫描的总行数为 10000(表 t1 的数据总量) + 100(表 t2 的数据总量) = 10100。并且 join\_buffer 里的数据是无序的，因此对表 t1 中的每一行，都要做 100 次判断，所以内存中的判断次数是 100 \* 10000= 100万次。

> 这个例子里表 t2 才 100 行，要是表 t2 是一个大表，join\_buffer 放不下怎么办呢？

join\_buffer 的大小是由参数 join\_buffer\_size 设定的，默认值是 256k。如果放不下表 t2 的所有数据话，策略很简单，就是**分段放**。流程如下：

*  取驱动表的一部分数据放入 join\_buffer，直至 join\_buffer 放不了。(比如 t2 表有1000行记录， join\_buffer 一次只能放800行数据，那么执行过程就是先往 join\_buffer 里放800行记录)
 *  扫描被驱动表的每一行数据，跟 join\_buffer 中的数据做对比，满足 join 条件的数据作为结果集的一部分返回。(从 t1 表里取数据跟 join\_buffer 中数据对比得到部分结果)
 *  清空 join\_buffer。(清空 join\_buffer)
 *  继续读取驱动表剩下的数据，重复前三个步骤，一直驱动表的数据被扫描完。(再将 t2 表剩余200行记录记录放到 join\_buffer 中，再次从 t1 表里取数据跟 join\_buffer 中数据对比。所以多扫了一次 t1 表和多扫描分段次数的 t2 表)

那么以这个分段放的例子来说，若驱动表的行数是 N，需要分 K 段才能扫描完，被驱动表的行数是 M，则扫描的行数是 N + K \* M，即 1000 + 2 \* 10000 = 21000 行；总的内存判断次数为 (800 + 200) \* 10000 = 100万次。

> 被驱动表的关联字段没索引为什么要选择使用 BNL 算法而不使用 NLJ 算法呢？

假设没有索引的情况下选择 NLJ 算法，那么需要扫描的行数为 100 \* 10000 = 100w次，但是这个确是**磁盘扫描**。

很显然，用 BNL 算法磁盘扫描次数少很多，并且相比磁盘扫描，BNL 的内存计算会快得多。

因此 MySQL 对于被驱动表的关联字段没索引的关联查询，一般都会使用 BNL 算法；如果有索引一般选择 NLJ 算法，有索引的情况下 NLJ 算法比 BNL算法性能更高。

## 2. 对于关联SQL的优化 ##

看完了 NLJ 算法和 BLJ 算法，想必关联 SQL 的优化也有一些思路了。

*  超过三个表禁止 join，多表关联查询时，保证被关联的字段需要有索引，尽量选择 NLJ 算法，同时需要 join 的字段，数据类型必须绝对一致。
 *  小表驱动大表，写多表连接 SQL 时如果明确知道哪张表是小表可以用 straight\_join 写法固定连接驱动方式，省去mysql优化器自己判断的时间。

**straight\_join**：straight\_join 功能同 inner join 类似，但能让左边的表来驱动右边的表，能改表优化器对于联表查询的执行顺序。比如：select \* from t2 straight\_join t1 on t2.a = t1.a; 代表指定 MySQL 选择 t2 表作为驱动表。

*  straight\_join 只适用于 inner join，并不适用于 left join、right join。(因为 left join、right join 已经代表指定了表的执行顺序)
 *  尽可能让优化器去判断，使用 straight\_join 一定要慎重，因为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱。

## 五、in和exsits优化 ##

**原则**：小表驱动大表。

**in优化**：当B表的数据集小于A表的数据集时，in 优于 exists。

select * from A where id in (select id from B);
    # 等价于：
    for(select id from B) {
        select * from A where A.id = B.id;
    }

**exsits优化**：当A表的数据集小于B表的数据集时，exists 优于 in。

select * from A where exists (select 1 from B where B.id = A.id);
    # 等价于：
    for(select * from A) {
        select 1 from B where B.id = A.id;
    }

*  exists (subquery) 只返回 true 或 false，因此子查询中的 SELECT \* 也可以用 SELECT 1 替换，官方说法是实际执行时会忽略 select 清单，因此没有区别。
 *  exists 子查询往往也可以用 join 来代替，怎么最优查询需要具体问题具体分析。

## 六、count(\*)查询优化 ##

## 1. count比较 ##

-- 临时关闭MySQL查询缓存
    set global query_cache_size=0;
    set global query_cache_type=0;
    
    -- count
    explain select count(*) from employees;
    explain select count(1) from employees;
    explain select count(id) from employees;
    explain select count(name) from employees;

小伙伴们可能都听过 DBA 或者某些博客建议不要使用 count(\*) 来统计数据行数，但是实际上并不是这样的，可以发现上面四条 SQL 的执行计划是一毛一样的。

![4f9254b3724c0ade08af14be24597bd0.png][]

那么既然执行计划是一样的，那么说明这4条语句的执行效率差不多其实，甚至5.7版本后 count(\*) 效率还更高。不过需要注意的是 count(name) 不会统计 name 为 null 的数据行。

*  字段有索引：count(\*) ≈ count(1) > count(字段) > count(主键id) // 字段有索引，count(字段) 统计走二级索引，二级索引存储数据比主键索引少，所以 count(字段) > count(主键id)。
 *  字段无索引：count(\*) ≈ count(1)> count(主键id) > count(字段) // 字段没有索引，count(字段) 统计走不了索引，count(主键id) 还可以走主键索引，所以 count(主键id) > count(字段)
 *  count(1) 跟 count(字段) 执行过程类似，不过 count(1) 不需要取出字段统计，就用常量1做统计，count(字段) 还需要取出字段，所以理论上 count(1) 比 count(字段) 会快一点。
 *  count(\*) 是例外，MySQL 并不会把全部字段取出来，而是专门做了优化，不取值，按行累加，效率很高，所以不需要用 count(列名)或 count(常量) 来替代 count(\*)。

> 为什么对于 count(id)，MySQL 最终选择辅助索引而不是主键聚集索引？

因为二级索引相对主键索引存储数据更少，检索性能应该更高，MySQL5.7 版本内部做了点优化。

## 2. 常见优化方法 ##

a. **查询MySQL自己维护的总行数**

对于 MyISAM 存储引擎的表做不带 where 条件的 count 查询性能是很高的，因为 MyISAM 存储引擎的表的总行数会被 MySQL 存储在磁盘上，查询不需要计算。

select count(*) from test_myisam;
    explain select count(*) from test_myisam;

![6c42e38c64669f4703cd149d38edd0c0.png][]

可以看到执行计划表都没有查询。

b. **show table status**

如果只需要知道表总行数的估计值可以用 show table status like 'employyees'; 查询，查询结果是个估计值。

![958a394e90e45b24a40d99672eacb00a.png][]

c. **将总数维护到Redis里**

插入或删除表数据行的时候同时维护 Redis 里的表总行数 key 的计数值(用 incr 或 decr 命令)，但是这种方式可能不准，很难保证表操作和redis操作的事务一致性。

d. **增加数据库计数表**

插入或删除表数据行的时候同时维护计数表，让他们在同一个事务里操作。

常见的 SQL 性能优化技巧就写到这里啦，写到此处已是深夜~~~

当然啦，SQL 优化技巧当然离不开怎么创建一个合适的索引，这篇文章的篇幅估计很长了…

![1c37e6e28634afc5ad17c547952a3635.png][]

[2abcf65dd49baa97d96d051ccf12f15e.png]: https://img-blog.csdnimg.cn/img_convert/2abcf65dd49baa97d96d051ccf12f15e.png
[0d81968f58038aba9c35d12a520188a1.png]: https://img-blog.csdnimg.cn/img_convert/0d81968f58038aba9c35d12a520188a1.png
[312269ceedf4b7836488060d2cefd5e5.png]: https://img-blog.csdnimg.cn/img_convert/312269ceedf4b7836488060d2cefd5e5.png
[4fd1d7947c7231a8de3ef8a7a9800730.png]: https://img-blog.csdnimg.cn/img_convert/4fd1d7947c7231a8de3ef8a7a9800730.png
[e292497192662f12b7f08aeee5fa5495.png]: https://img-blog.csdnimg.cn/img_convert/e292497192662f12b7f08aeee5fa5495.png
[214522c43c275b9904b3a38177dc308a.png]: https://img-blog.csdnimg.cn/img_convert/214522c43c275b9904b3a38177dc308a.png
[5726f93338bd7de564b16899605c2632.png]: https://img-blog.csdnimg.cn/img_convert/5726f93338bd7de564b16899605c2632.png
[7d87fbc2800e4a619ca525dc8853f631.png]: https://img-blog.csdnimg.cn/7d87fbc2800e4a619ca525dc8853f631.png
[fc6e6d53d703256eaeab84cab377a2f7.png]: https://img-blog.csdnimg.cn/img_convert/fc6e6d53d703256eaeab84cab377a2f7.png
[060318c3a7f0738f9e151a11e756cc92.png]: https://img-blog.csdnimg.cn/img_convert/060318c3a7f0738f9e151a11e756cc92.png
[a513976fc2efbee874b380d54a0932ec.png]: https://img-blog.csdnimg.cn/img_convert/a513976fc2efbee874b380d54a0932ec.png
[0e86c5117c25d8f9c79c043eacf58d12.png]: https://img-blog.csdnimg.cn/img_convert/0e86c5117c25d8f9c79c043eacf58d12.png
[b72b3052256d22139fd90abc857dba76.png]: https://img-blog.csdnimg.cn/img_convert/b72b3052256d22139fd90abc857dba76.png
[bee23df0b968dfdd7b15e74fdd3d3011.png]: https://img-blog.csdnimg.cn/img_convert/bee23df0b968dfdd7b15e74fdd3d3011.png
[4f9254b3724c0ade08af14be24597bd0.png]: https://img-blog.csdnimg.cn/img_convert/4f9254b3724c0ade08af14be24597bd0.png
[6c42e38c64669f4703cd149d38edd0c0.png]: https://img-blog.csdnimg.cn/img_convert/6c42e38c64669f4703cd149d38edd0c0.png
[958a394e90e45b24a40d99672eacb00a.png]: https://img-blog.csdnimg.cn/img_convert/958a394e90e45b24a40d99672eacb00a.png
[1c37e6e28634afc5ad17c547952a3635.png]: https://img-blog.csdnimg.cn/img_convert/1c37e6e28634afc5ad17c547952a3635.png