麒麟v10 上部署 TiDB v5.1.2 生产环境优化实践
622
2023-06-14
MySQL数据库的SQL调优，你会了吗？
MySQL数据库的SQL调优，你会了吗？
目录
前言初步了解索引要调优 SQL，怎么能不认识 explain重点!SQL 优化
一、前言
因为笔者现在工作中用的存储引擎大多是 InnoDB，所以本文基于 InnoDB，数据库版本MySQL 5.7为前提写的。我们平常说的 SQL 优化，基本上就是对索引的优化。这里既然重点是 SQL 优化，所以我们得先了解索引，然后了解下我们分析 SQL 的工具 explain，最后才能到优化。这也是本文的大纲顺序。
了解 SQL 优化之前，有几个概念需要先知道：
MySQL 索引的数据结构
聚簇索引
叶子节点存储索引对应的 record信息。
非聚簇索引
叶子节点只存储主键数据，所以要查询索引以外的数据需要回表。
回表
走非聚簇索引得到主键数据后，根据主键再走一次聚簇索引那里查询列需要的数据。
优化器
优化器是MySQL 众多组件中的一个，它会对我们的 SQL 进行分析，看预计使用哪些索引，SQL 的执行顺序如何，实际会使用哪些索引(没有真的执行 SQL，执行 SQL 是存储引擎去进行读写的)，使用索引的情况等等。
二、初步了解索引
需要知道使用 InnoDB 的表肯定有一个聚簇索引(有且仅有一个)，使用的数据结构是 B+Tree。
*.frm：数据表结构相关信息存储的文件
*.idb：索引和数据存储的文件
注意：*.idb 这个文件本身就是 B+Tree 的文件，叶子节点包含完整的数据记录。
下面以主键索引为例(我的user表就只有三个字段)
为什么大厂的DBA都建议InnoDB表建自增整型主键?
主键(不会重复)
如果我们没有主键，MySQL会使用我们表从第一列开始选择一列所有元素都不相等的列构建B+Tree，假设我们不存在符合这个要求的列，MySQL会自己为我们创建一个符合这个条件的隐藏列构建索引。像这种开销没必要花费，我们自己建表时，直接处理可以。
自增
维护B+Tree时，更容易，性能更好。
整型
查询范围时，整型比较大小更简单;整型占用空间更小，节约空间，事实上公司一般都会要求明确字段大小，过大字段，DBA一般都会要求开发解释为什么要这么大，当然从存储数据量角度来看，索引也是越小越好。
二级索引
二级索引是非聚集的，主要是为了节约空间。二级索引是先找到主键，通过主键回表找到真正的数据行。
联合索引(复合索引)
假如现在我有个用户表有4个字段：username、telephone、age、sex。
我们可以建两种类型的联合索引：联合主键，普通的联合索引。
联合主键
现在我用 username、sex 构建成联合主键，维护索引如下：
普通的联合索引
这个和上面的差不多，只是 data 存的是主键，需要回表查找。
最左匹配原则：
以上图为例子，先根据名字转成的ascii码进行排序，如果 ascii 码一样，那么再根据性别的 ascii 码大小比较排序。只有 username 的索引生效了，sex 的索引才有可能生效。要证明也很容易：如果没有匹配 username，直接匹配 sex，单看 sex 的话，我们索引的排序是无序的，就没法使用二分法了，所以不走索引。
讲了索引的数据结构，以及生效的情况，那么接下来就要看看如何 SQL 优化了。但是在此之前，我们要先了解下 explain 。
三、要调优 SQL，怎么能不认识 explain
使用 explain 可以模拟优化器执行 SQL，分析 SQL，看看能否优化。
explain 标识的 SQL 不会真的执行，只是返回执行计划。如果 from 中包含子查询，仍会执行该子查询，子查询的结果将会放在临时表中。
explain 分析的 SQL 中，每查询一个表就会有一行记录。
3.1 explain 中各列的含义
了解每一列的意义，掌握最常用那几列。
3.1.1 id
id 列的编号是 select 的序列号，查几个表就有几个 id，并且 id 值越大执行优先级越高。如果 id 值相同，就从上往下执行，最后执行 id 为 null 的。
3.1.2 select_type
查询类型。
primary
简单查询。查询不包含子查询和union。
subquery
复杂查询中最外层的 select。
derived
包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表(derived的英文含义)。如下：
#关闭mysql5.7新特性对衍生表的合并优化set session optimizer_switch='derived_merge=off'; explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der;
• union
其实就是使用了 union 关键字后面的查询，如下：
3.1.3 table
表示这一列使用的是哪一张表。
当 from 子句中有子查询时，table列是格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。如下图：
当有 union 时，UNION RESULT 的 table 列的值为，1和2表示参与 union 的 select 行id。
3.1.4 partitions
使用的哪个分区，需要结合表分区才可以看到。因为我的例子都是没有分区的，所以是 null。
3.1.5 type
关联类型或者访问类型。一般要保证查询达到 range 级别，最好达到 ref。
从最优到最差：system > const > eq_ref > ref > range > index > ALL。
system, const
const 是 MySQL 能对查询的某部分转成一个常量，如下:
而 system 是 conts 的一个特例，当表里只有一条记录时，匹配时为 system。
eq_ref
使用了主键字段或者唯一索引字段进行关联，最多只会返回一条符合条件的记录时，等级为 eq_ref。
explain select * from film_actor left join film on film_actor.film_id = film.id
ref
相较于 eq_ref，它使用的是普通索引或者唯一索引的部分前缀，可能会找到多条符合条件的记录。
range
范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。
explain select * from actor where id > 1;
index
这种一般是通过扫描某个二级索引的所有叶子节点(其实就是应该做全表扫描，但是这里利用了B+Tree的叶子节点是链表的特性遍历)。这种方式，虽然比较慢，但是用覆盖索引优化，性能上还是要比全表扫描(ALL)要好的，因为它占用空间小，一次IO可以读更多数据。
ALL
这个级别没啥好说的，就是我们常说的全表扫描。
3.1.6 possible_keys
显示可能会使用的索引。
3.1.7 key
实际会使用的索引。
3.1.8 key_len
通过这个值，可以推算出使用到索引的哪些列(一般针对联合索引使用多些)，举个例子：
film_actor 的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个 int 列组成，并且每个 int 是4字节。通过结果中的 key_len=4 可推断出查询使用了第一个列：film_id列来执行索引查找。
explain select * from film_actor where film_id = 2;
key_len计算规则如下：
字符串：char(n) 和 varchar(n)，5.0.3以后版本中，n均代表字符数，而不是字节数，如果是utf-8，一个数字或字母占1个字节，一个汉字占3个字节
– char(n)：如果存汉字长度就是 3n 字节
– varchar(n)：如果存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度，因为varchar是变长字符串
数值类型
– tinyint：1字节
– smallint：2字节
– int：4字节
– bigint：8字节
时间类型
– date：3字节
– timestamp：4字节
– datetime：8字节
如果字段允许为 NULL，需要1字节记录是否为 NULL。索引最大长度是768字节，当字符串过长时，MySQL会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。
3.1.9 ref
这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const(常量)，字段名(例：film.id)。
3.1.10 rows
这一列是MySQL估计要读取并检测的行数，注意这个不是结果集里的行数。
3.1.11 filtered
通过过滤条件之后对比总数的百分比。
3.1.12 Extra
这一列展示的是额外信息。常见的重要值如下：
Using index
使用覆盖索引。覆盖索引其实就是查询列是索引字段，这样就能避免回表，提高性能。因此，我们覆盖索引针对的是辅助索引。
Using where
使用 where 语句处理结果，并且查询列未被索引覆盖。如下：
explain select * from actor where name = 'a';
Using index condition
查询的列没被索引完全覆盖， where 条件中是一个前导列的范围。
explain select * from film_actor where film_id > 1;
Using temporary
创建临时表来处理查询
(1)actor.name没有索引，此时创建了张临时表来distinct。
explain select distinct name from actor;
(2)film.name建立了idx_name索引，此时查询时extra是using index,没有用临时表。
explain select distinct name from film;
Using filesort
使用外部排序而不是索引排序，数据量较小时使用内存，否则会使用磁盘。
(1)actor.name未创建索引，会浏览actor整个表，保存排序关键字name和对应的id，然后排序name并检索行记录。
explain select * from actor order by name;
(2)film.name建立了idx_name索引，此时查询时extra是using index。
explain select * from film order by name;
Using filesort 原理详解：
– 单路排序
一次性取出满足条件的所有字段，然后在 sort buffer 中排序。用 trace 工具可以看到 sort_mode 信息里显示 或者 < sort_key, packed_additional_fields>
– 双路排序(回表排序)
先根据条件获取相应的排序字段和可以直接定位行数据的行ID，然后在 sort buffer 中排序，最后回表获取完整记录。用 trace 工具可以看到 sort_mode 信息里显示 。
– MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式。
如果字段的总长度小于 max_length_for_sort_data，那么使用单路排序。如果字段的总长度大于 max_length_for_sort_data，那么使用双路排序。
Select tables optimized away
使用某些聚合函数(比如 max、min)来访问存在索引的某个字段时为 Select tables optimized away。
四、重点!SQL 优化
极端点说，SQL 优化就是对索引的优化。因此，我们要看下各种情况下，如何优化索引。
在我看来，SQL优化分以下几种情况：
1.可以走索引
应该走索引，但是没走走索引了，但是没到最优(explain 分析，type 一般我们要求至少到达 range 这个级别)order by 和 group by 优化
2.没法走索引(客观现实上的)或者 type 是 index，而且数据量大
了解适用索引的情况，请不要只有面试时会说，工作就不知道了(数据量不大，直接查没事;大的话，考虑引进其他技术解决，如 :Redis, ***, elasticsearch等)
3.小表驱动大表
4.count 查询优化
5.如何建索引
该在哪个字段建索引哪些字段要使用联合索引表字段的设计(数据类型，大小)
Note : 单个索引生不生效，怎么处理还是比较简单的，所以下面只针对联合索引做分析。
下面先建表和造数据：
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=100004 DEFAULT CHARSET=utf8 COMMENT='员工记录表';CREATE TABLE `actor` ( `id` int(11) NOT NULL, `name` varchar(45) DEFAULT NULL, `update_time` datetime DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='演员表';CREATE TABLE `film` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_name` (`name`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='电影表';CREATE TABLE `film_actor` ( `id` int(11) NOT NULL, `film_id` int(11) NOT NULL, `actor_id` int(11) NOT NULL, `remark` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_film_actor_id` (`film_id`,`actor_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='电影演员中间表';
其中员工表插入了10W+数据。
4.1 常见的应该走索引，但是没走
联合索引第一个字段不能过滤大部分数据，导致回表效率低，走全表扫描的 cost 更小。
explain SELECT * FROM `employees` where `name` like "sai%" and age = 22 and position = 'dev';
当然我们也可以选择强制走索引，如下：
explain SELECT * FROM `employees` force index(idx_name_age_position) where `name` like "sai%" and age = 22 and position = 'dev';
不过，走索引一定性能就更好吗?我们试验下。
-- 关闭查询缓存SET GLOBAL query_cache_size = 0;SET GLOBAL query_cache_type = 0;-- 耗时 0.064sSELECT * FROM `employees` WHERE `name` LIKE "sai%" AND age = 22 AND position = 'dev';-- 耗时 0.079sSELECT * FROM `employees` force index(idx_name_age_position) where `name` like "sai%" and age = 22 and position = 'dev';
别看我这差距不大，我这只是表列不多，字段不大，数据量也不算太多，所以差距不大，如果表更大的话，差距就会比较明显了。实际工作中，我们很难确定走索引的 cost 就一定小于全表扫描的。因此，我们一般不强制走索引。
优化方案：
我想让 MySQL自己去走索引，而不是我强制走索引。怎么办呢?其实上面已经提到了，这里是因为第一个字段过滤不多，导致回表效率低。既然如此，我们让它不回表不就好了吗?使用覆盖索引优化，就是我们查询列的字段都是使用的这个索引树上建了索引的字段，这样就不需要回表了。如下：
explain SELECT id,`name`,age,position FROM `employees` where `name` like "sai%" and age = 22 and position = 'dev';-- 耗时 0.051sSELECT id,`name`,age,position FROM `employees` where `name` like "sai%" and age = 22 and position = 'dev';
扩展：
我们使用 in 和 or 时，有时走索引，有时不走，其实是因为 MySQL 判断走索引的cost不如全表扫描的。我们这里用了 like 'keyword%'，这里涉及到一个概念叫索引下推。其实就是，MySQL 5.6 之前，对于以下的SQL，如果是走索引的话，它会先根据 name 过滤得到主键，进行回表拿到数据后，再去对比 age 和 position。MySQL 5.6 对此进行了优化——索引下推，根据 name 过滤后，不先回表，而是直接去对比 age 和 position，最后得到的主键才回表查数据。注意：1、索引下推只用于二级索引;2、不是 like 'keyword%' 就一定使用索引下推。
SELECT * FROM `employees` WHERE `name` LIKE "sai%" AND age = 22 AND position = 'dev';
分页不走索引
分页查询，系统十分常见的查询，建议大家学习完后，赶紧看下自己负责的分页功能是否走索引了，或者是否走了索引但是还能优化。以下，看例子来说一些优化手段。
select * from employees limit 10000, 10;
这 SQL 其实是去了10010条记录出来，然后再舍弃前面的一万条。因此数据量大的话，其实效率是十分低的。
一些优化方案：
1.和产品同事商量，给一些一定有的查询条件或者隐藏的查询条件，给这些条件使用上索引。
这个方案是最简单并且直接的。
2.像我这里记录的id是连续且自增的情况下：
explain select * from employees where id > 10000 limit 10;
属于取巧，通过主键索引使用 where 直接筛选掉前面10000条记录。
缺点：
(1) 如果 id 不是连续且自增，那么这种方式就不行。
(2)不是使用主键排序，这种情况也不行。
3.非主键排序，不用ID连续自增也能生效。
-- 0.085sselect * from employees order by `name` desc limit 10000, 10;
首先想到覆盖索引优化，看看能否这样干
explain select `name`, age, position from employees order by `name` desc limit 10000, 10;-- 0.077sselect `name`, age, position from employees order by `name` desc limit 10000, 10;
扩展：
-- 我们常认为 like 以通配符开头，索引会失效，但其实也可以通过覆盖索引，让索引生效。explain select `name`, age, position from employees where `name` like '%sai%';
解决方案如下：
（1）
explain select e.* from employees e inner join (select id from employees order by `name` desc limit 10000, 10) t on t.id = e.id;-- 0.045sselect e.* from employees e inner join (select id from employees order by `name` desc limit 10000, 10) t on t.id = e.id;
这里其实就是利用了二级索引，拿到了10010条数据，并且按照 name 排好序，由于这里的子查询只要 id，所以不需要回表，然后再通过 join 就能利用主键索引快速拿到记录。
（2）当然除了这种方式，我们也可以强制走索引，因为我们知道这里二级索引只有一个，并且 name 是前导列，所以我这个案例走索引性能肯定比全表扫描好。因此，我们也可以选择强制走索引。
-- 0.011sselect * from employees force index(idx_name_age_position) order by `name` desc limit 10000, 10;
不符合最左原则
我们索引之所以可以帮我们快速找到目标数据，是因为它的数据结构的特点。其中有序这一特征十分重要，如果不满足，那么肯定是不会走索引的(具体原因要回到平衡二叉查找树，再到二分法。因为不是这里的重点，所以不展开讲)。
在索引列上做了以下操作：
– 对索引列是用了函数
– 对索引列做了类型转换
-- 类型转换会有特例，当我们转成日期范围查询时，有可能走索引。ALTER TABLE `employees` ADD INDEX `idx_hire_time` (`hire_time`) USING BTREE ;EXPLAIN select * from employees where hire_time >='2018‐09‐30 00:00:00' and hire_time <='2018‐09‐30 23:59:59';
根据查询条件过滤的数据不多，导致优化器认为走索引不如全表扫描。
其实第一个案例已经涉及到了，但是这里针对的是不等于， not in, not exists, <, >, is null, is not null 等等，这些能匹配到多条记录的写法。
4.2 order by 和 group by 优化
排序和分组的优化其实是十分像的，本质是先排序后分组，遵循索引创建顺序的最左匹配原则。因此，这里以排序为例。
接下来写的都是有查询条件的情况。
explain select * from employees where `name` = 'sai999' and position = 'dev' order by age;
-- 这里没有走索引，是因为不符合最左原则，跳过了 ageexplain select * from employees where `name` = 'sai999' order by position;
-- 这样就会走索引了，排序了explain select * from employees where `name` = 'sai999' order by age, 1position;
-- 又不走索引了，因为 age 和 position 顺序颠倒了，不符合我们索引的顺序explain select * from employees where `name` = 'sai999' order by position, age;
-- 修改成这样，就又可以走索引了，因为 age 是个常量了，所以在排序中被优化，没有和索引顺序冲突explain select * from employees where `name` = 'sai999' and age = 999 order by position, age;
-- 这里虽然符合索引顺序，但是 age 是升序，而 position 是降序，所以不走索引。听说 MySQL 8 支持这种查询方式，我没安装8就不测试了explain select * from employees where `name` = 'sai999' order by age asc, position desc;
-- 想想我们联合索引的 B+Tree 数据结构，当 name 有两个值时，得出的结果集对于 age, position 而言是无序的，所以没法走索引explain select * from employees where `name` in ('sai100', 'sai101') order by age, position;
-- 可以使用覆盖索引优化explain select `name`, age, position from employees where `name` > 'a' order by `name`;
MySQL 支持两种排序方式 filesort 和 index， Using index 是扫描索引完成的排序，而 Using filesort 是利用内存甚至磁盘完成排序的。因此，index 效率高，filesort 效率低。
4.3 小表驱动大表
当我们做多表关联查询时，常常会听到小表驱动大表。这里要了解什么是小表，什么是大表，为什么是小表驱动大表，MySQL 用了什么算法。
下面以两张表关联为例，介绍概念
什么是小表，什么是大表?不是表数据量较多那张表就是大表!!!而是经过我们的条件筛选后，匹配数据相对较小的那张表就是小表，另外一张就是大表。
所谓的小表驱动大表就是：先查小表，然后通过关联字段去匹配大表数据。
MySQL 的表关联常见有两种算法：
Nested-Loop Join 算法(NLJ)Block Nested-Loop Join 算法(BNL)
4.3.1 NLJ，嵌套循环连接算法
这个算法就是一次一行地从驱动表中读取，通过关联字段在被驱动表中取出满足条件的行，然后取出两张表的结果合集。
explain select * from uuc_user u inner join uuc_user_role ur on ur.user_id = u.id;
从执行计划可以看出：
uuc_user_role 是驱动表，并且扫描了9条记录(表里只有9条记录)，然后通过 user_id 去关联了 uuc_user(被驱动表)。
注意：优化器一般会优先选择小表驱动大表，我们 SQL 写的表的先后顺序有可能会被优化。
上面 SQL 的大致流程如下：
先从 uuc_user_role 中读取一行记录(如果有查询条件，会根据查询条件过滤结果中取一条)获取关联字段，通过关联字段到 uuc_user 找到匹配记录对第二步得到的记录，根据查询条件得到的记录跟第一步得到的记录进行合并，返回客户端重复上面三步
查询结果如下(由于数据太多，手工拼接图了)
NLJ这个过程会读取 ur 所有数据(9行记录)，每次读一行并拿到 user_id 的值，然后得到对应的 uuc_user 里的记录(这就是又扫了一次索引得到一行数据)。也就是说，整个过程扫描了18行记录。注意：如果被驱动表的关联字段没有索引，使用NLJ算法性能较低，MySQL会选择使用 BNL 算法。
扩展：如果我这里使用的是 left join，这时，左边的是驱动表，右边的是被驱动表;right join 则刚好相反。
explain select * from uuc_user u left join uuc_user_role ur on ur.user_id = u.id;
4.3.2 BNL，基于块的嵌套循环连接算法
把驱动表的数据读入 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来和 join_buffer 中的数据做匹配。
上面扩展已经出现了 BNL 算法的例子了，我就直接使用了。
select * from uuc_user u left join uuc_user_role ur on ur.user_id = u.id;
这条 SQL 的流程大致如下：
把 uuc_user 所有记录放入 join_buffer查 uuc_user_role 的记录和 join_buffer 中的数据匹配返回满足条件的数据
整个过程扫描了 uuc_user 表225条记录和 uuc_user_role 表9条记录，总扫描行数为234行。内存比较最大次数 = 225 * 9 = 2025(次)，想想 for 循环的代码就知道了。
两个问题：
如果内存不够大，即 join_buffer 放不下 uuc_user 的数据怎么办?为什么被驱动表的关联字段没有索引会选择 BNL 算法呢?
答案：
内存不够，那就分段放。打个比方我内存只能放下200条记录，我这里225，那么我一次放200，分两次放完就好了。join_buffer 默认值是256k。如果关联字段没有索引，使用 NLJ 算法的话，那么我们的比较都需要走磁盘扫描(等于是查询没有用到索引)。这时，都没用到索引的话，我内存比较的性能要比磁盘的好。因此，使用 BNL。但是有索引的话，我们可以通过索引大大提升查询性能(其实就是减少IO)，所以会使用 NLJ。
4.3.3 多表关联的优化
互联网公司其实一般不允许做多表关联，如果做了关联，最多不超过3张表。多表关联时，关联字段一定要有索引，并且数据类型保持一致。为什么这么要求?直接原因，阿里规范(老大都这样规范，小弟跟着做，没毛病)。根本原因?看《高性能MySQL》，这本书推荐阅读。
关联字段加索引，让 MySQL 做 join 时尽量选择 NLJ 算法。小表驱动大表，如果自己能知道哪张表肯定是小表，我们可以使用 straight_join，省去优化器的判断时间。
-- 像 select * from uuc_user u inner join uuc_user_role ur on ur.user_id = u.id; 这 SQL-- 我们可以优化成下面的 SQL,用左边的表驱动右边的表explain select * from uuc_user_role ur straight_join uuc_user u on ur.user_id = u.id;
4.3.4 in 和 exsits
原则还是小表驱动大表
假设 A 表是左表，B 表是子查询的表。当 A 表是大表， B 表是小表时，使用 in。
select * from A where id in (select id from B)
当 A 表是小表， B 表是大表时，使用 exsits。
-- exists(subquery)只返回 true 或 false，官方也有说过实际执行时会忽略查询列。因此，select * 和 select 1 没区别。-- exists子查询实际执行过程是被优化了的，不是我们之前理解的逐条匹配。select * from A where exists (select 1 from B where B.id = A.id)
4.4 count 查询优化
网上挺多资料说，要count(id)或者count(1)，不要count(*)，到底是不是这样呢?我们今天就来实践一下。
-- 临时关闭查询缓存，看实验的真实时间set global query_cache_size=0;set global query_cache_type=0;-- 首先下面四条语句得到的执行计划都是一样的，说明理论上这四个SQL的执行效率应该是差不多的explain select count(1) from employees; -- 有时0.03左右，有时0.015s左右explain select count(id) from employees;-- 稳定在0.015s左右explain select count(*) from employees;-- 稳定在0.015s左右explain select count(`name`) from employees;-- 稳定在0.015s左右
具体耗时如下(其实，随着电脑的状态不同，会有出入，但是多次测试会发现，这截图的排序结果是多数)。
因此，我们可以看出 count(*) 少用，性能较差是谣言，可以放心使用。这是因为 MySQL 5.6+ 会对 count(*) 进行优化，所以执行效率还是很高的。
hire_time 慢的原因是因为没有索引。
4.5 如何建索引
老生常谈的东西了，面试也经常问，这里就做个总结。
对于如何建索引这个问题，我个人觉得应该从以下几个角度思考：
什么场景要建索引应该挑选哪些字段建索引，字段的大小，字段的类型索引的数量
4.5.1 什么场景要建索引
高频查询，且数据较多，能够通过索引筛选较多数据表关联统计，排序，分组聚合
4.5.2 应该挑选哪些字段建索引，字段的大小，字段的类型
高频查询，更新低频，并且可以过滤较多数据的字段用于表关联的关联字段用于排序，分组，统计等等的字段作为建索引的字段尽量小，可以降低树的高度，具体规则看下面的阿里规范
4.5.3 索引的数量
索引的数量要尽量的少。
因为索引是会占空间的;记录更新数据库记录时，是有维护索引的成本的，数量越多，维护成本越高;一张表索引过多，当一个条件发现多个索引都生效时，优化器一般会挑选性能最好的那个索引来用，数量多，优化器的挑选的成本也会上升。
4.6 索引设计原则
1.代码先行，索引后上
只有对系统有了一定全局观，才知道哪些地方需要用索引，大多 SQL 是怎样的，我应该如何建索引。这样，我们就能有效减少不必要的索引，做到联合索引尽量覆盖条件。
2.尽量不要在过滤数据不多的字段建立索引，如：性别。
3.where 与 order by 冲突时，优先处理 where。
作者介绍
蔡柱梁，51CTO社区编辑，从事Java后端开发8年，做过传统项目广电BOSS系统，后投身互联网电商，负责过订单，TMS，中间件等。
版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。
相关文章
