文章目录

• 查询 SQL 尽量不要使用 select *，而是具体字段
• 避免在 where 子句中使用 or 来连接条件
• 使用 varchar 代替 char
• 尽量使用数值替代字符串类型
• 查询尽量避免返回大量数据
• 使用 explain 分析你 SQL 执行计划
• 创建 name 字段的索引
• 优化 like 语句：
• 字符串怪现象
• 索引不宜太多，一般 5 个以内
• 索引不适合建在有大量重复数据的字段上
• where 限定查询的数据
• 避免在 where 中对字段进行表达式操作
• 去重 distinct 过滤字段要少
• where 中使用默认值代替 null
• 批量删除优化
• 提高 group by 语句的效率
• 复合索引最左特性
• 排序字段创建索引
• 尽量使用 union all 替代 union

查询 SQL 尽量不要使用 select *，而是具体字段

反例：

SELECT * FROM student

正例：

SELECT id,NAME FROM student

理由：

• 字段多时，大表能达到 100 多个字段甚至达 200 多个字段
• 只取需要的字段，节省资源、减少网络开销
• select * 进行查询时，很可能不会用到索引，就会造成全表扫描

避免在 where 子句中使用 or 来连接条件

查询 id 为 1 或者薪水为 3000 的用户：
反例：

SELECT * FROM student WHERE id=1 OR salary=30000

正例：

使用 union all

SELECT * FROM student WHERE id=1UNION ALLSELECT * FROM student WHERE salary=30000

分开两条 sql 写

SELECT * FROM student WHERE id=1SELECT * FROM student WHERE salary=30000

理由：

• 使用 or 可能会使索引失效，从而全表扫描
• 对于 or 没有索引的 salary 这种情况，假设它走了 id 的索引，但是走到
  salary 查询条件时，它还得全表扫描。也就是说整个过程需要三步：全表
  扫描+索引扫描+合并。如果它一开始就走全表扫描，直接一遍扫描就搞
  定。虽然 mysql 是有优化器的，处于效率与成本考虑，遇到 or 条件，索
  引还是可能失效的

使用 varchar 代替 char

反例：

`deptname` char(100) DEFAULT NULL COMMENT '部门名称'

正例：

`deptname` varchar(100) DEFAULT NULL COMMENT '部门名称'

理由：

• varchar 变长字段按数据内容实际长度存储，存储空间小，可以节省存储
  空间
• char 按声明大小存储，不足补空格
• 其次对于查询来说，在一个相对较小的字段内搜索，效率更高

尽量使用数值替代字符串类型

• 主键（id）：primary key 优先使用数值类型 int，tinyint
• 性别（sex）：0-代表女，1-代表男；数据库没有布尔类型，mysql 推荐
  使用 tinyint
• 支付方式（payment）：1-现金、2-微信、3-支付宝、4-信用卡、5-银行
  卡
• 服务状态（state）：1-开启、2-暂停、3-停止
• 商品状态（state）：1-上架、2-下架、3-删除

查询尽量避免返回大量数据

如果查询返回数据量很大，就会造成查询时间过长，网络传输时间过长。同
时，大量数据返回也可能没有实际意义。如返回上千条甚至更多，用户也看不
过来。

通常采用分页，一页习惯 10/20/50/100 条。

使用 explain 分析你 SQL 执行计划

SQL 很灵活，一个需求可以很多实现，那哪个最优呢？SQL 提供了 explain 关
键字，它可以分析你的 SQL 执行计划，看它是否最佳。Explain 主要看 SQL 是
否使用了索引。

EXPLAINSELECT * FROM student WHERE id=1

返回结果：

创建 name 字段的索引

提高查询速度的最简单最佳的方式

ALTER TABLE student ADD INDEX index_name (NAME)

优化 like 语句：

模糊查询，程序员最喜欢的就是使用 like，但是 like 很可能让你的索引失效
反例：

EXPLAINSELECT id,NAME FROM student WHERE NAME LIKE '%1'EXPLAINSELECT id,NAME FROM student WHERE NAME LIKE '%1%'

正例：

EXPLAINSELECT id,NAME FROM student WHERE NAME LIKE '1%'

理由：
未使用索引：故意使用 sex 非索引字段

EXPLAINSELECT id,NAME FROM student WHERE NAME=1 OR sex=1

主键索引生效

EXPLAINSELECT id,NAME FROM student WHERE id=1

索引失效，type=ALL，全表扫描

EXPLAINSELECT id,NAME FROM student WHERE id LIKE '%1'

字符串怪现象

反例：

#未使用索引EXPLAINSELECT * FROM student WHERE NAME=123

正例：

#使用索引EXPLAINSELECT * FROM student WHERE NAME='123'

理由：

• 为什么第一条语句未加单引号就不走索引了呢？这是因为不加单引号时，
  是字符串跟数字的比较，它们类型不匹配，MySQL 会做隐式的类型转
  换，把它们转换为数值类型再做比较

索引不宜太多，一般 5 个以内

• 索引并不是越多越好，虽其提高了查询的效率，但却会降低插入和更新的
  效率
• 索引可以理解为一个就是一张表，其可以存储数据，其数据就要占空间
• 再者，索引表的一个特点，其数据是排序的，那排序要不要花时间呢？肯
  定要
• insert 或 update 时有可能会重建索引，如果数据量巨大，重建将进行记
  录的重新排序，所以建索引需要慎重考虑，视具体情况来定
• 一个表的索引数最好不要超过 5 个，若太多需要考虑一些索引是否有存在
  的必要

索引不适合建在有大量重复数据的字段上

如性别字段。因为 SQL 优化器是根据表中数据量来进行查询优化的，如果索引
列有大量重复数据，Mysql 查询优化器推算发现不走索引的成本更低，很可能
就放弃索引了。

where 限定查询的数据

数据中假定就一个男的记录
反例：

SELECT id,NAME FROM student WHERE sex='男'

正例：

SELECT id,NAME FROM student WHERE id=1 AND sex='男'

理由：

• 需要什么数据，就去查什么数据，避免返回不必要的数据，节省开销

避免在 where 中对字段进行表达式操作

反例：

EXPLAINSELECT * FROM student WHERE id+1-1=+1

正例：

EXPLAINSELECT * FROM student WHERE id=+1-1+1EXPLAINSELECT * FROM student WHERE id=1

理由：

• SQL 解析时，如果字段相关的是表达式就进行全表扫描
  
• 字段干净无表达式，索引生效
  

去重 distinct 过滤字段要少

#索引失效EXPLAINSELECT DISTINCT * FROM student#索引生效EXPLAINSELECT DISTINCT id,NAME FROM studentEXPLAINSELECT DISTINCT NAME FROM student

理由：

• 带 distinct 的语句占用 cpu 时间高于不带 distinct 的语句。因为当查询很
  多字段时，如果使用 distinct，数据库引擎就会对数据进行比较，过滤掉
  重复数据，然而这个比较、过滤的过程会占用系统资源，如 cpu 时间

where 中使用默认值代替 null

环境准备：

#修改表，增加 age 字段，类型 int，非空，默认值 0ALTER TABLE student ADD age INT NOT NULL DEFAULT 0;#修改表，增加 age 字段的索引，名称为 idx_ageALTER TABLE student ADD INDEX idx_age (age);

反例：

EXPLAINSELECT * FROM student WHERE age IS NOT NULL

正例：

EXPLAINSELECT * FROM student WHERE age>0

理由：

• 并不是说使用了 is null 或者 is not null 就会不走索引了，这个跟 mysql
  版本以及查询成本都有关
• 如果 mysql 优化器发现，走索引比不走索引成本还要高，就会放弃索
  引，这些条件 !=，，is null，is not null 经常被认为让索引失效，其实
  是因为一般情况下，查询的成本高，优化器自动放弃索引的
• 如果把 null 值，换成默认值，很多时候让走索引成为可能，同时，表达意
  思也相对清晰一点

批量删除优化

避免同时修改或删除过多数据，因为会造成 cpu 利用率过高，会造成锁表操
作，从而影响别人对数据库的访问。
反例：

#一次删除 10 万或者 100 万+？delete from student where id <100000;#采用单一循环操作，效率低，时间漫长for（User user:list）{ delete from student;}

正例：

#分批进行删除，如每次 500for(){delete student where id<500;}delete student where id>=500 and id<1000;

理由：

• 一次性删除太多数据，可能造成锁表，会有 lock wait timeout exceed 的
  错误，所以建议分批操作
  伪删除设计
  商品状态（state）：1-上架、2-下架、3-删除
  理由：
• 这里的删除只是一个标识，并没有从数据库表中真正删除，可以作为历史
  记录备查
• 同时，一个大型系统中，表关系是非常复杂的，如电商系统中，商品作废
  了，但如果直接删除商品，其它商品详情，物流信息中可能都有其引用。
• 通过 where state=1 或者 where state=2 过滤掉数据，这样伪删除的数据
  用户就看不到了，从而不影响用户的使用
• 操作速度快，特别数据量很大情况下

提高 group by 语句的效率

可以在执行到该语句前，把不需要的记录过滤掉
反例：先分组，再过滤

select job，avg（salary） from employee group by job having job ='president' or job = 'managent';

正例：先过滤，后分组

select job，avg（salary） from employee where job ='president' or job = 'managent'group by job;

复合索引最左特性

创建复合索引，也就是多个字段

ALTER TABLE student ADD INDEX idx_name_salary (NAME,salary)

满足复合索引的左侧顺序，哪怕只是部分，复合索引生效

EXPLAINSELECT * FROM student WHERE NAME='name1'

没有出现左边的字段，则不满足最左特性，索引失效

EXPLAINSELECT * FROM student WHERE salary=3000

复合索引全使用，按左侧顺序出现 name,salary，索引生效

EXPLAINSELECT * FROM student WHERE NAME='陈子枢' AND salary=3000

虽然违背了最左特性，但 MYSQL 执行 SQL 时会进行优化，底层进行颠倒优化

EXPLAINSELECT * FROM student WHERE salary=3000 AND NAME='name1'

理由：

• 复合索引也称为联合索引
• 当我们创建一个联合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、
  (k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则
• 联合索引不满足最左原则，索引一般会失效，但是这个还跟 Mysql 优化
  器有关的

排序字段创建索引

什么样的字段才需要创建索引呢？原则就是 where 和 order by 中常出现的字段
就创建索引。

#使用*，包含了未索引的字段，导致索引失效EXPLAINSELECT * FROM student ORDER BY NAME;EXPLAINSELECT * FROM student ORDER BY NAME,salary#name 字段有索引EXPLAINSELECT id,NAME FROM student ORDER BY NAME#name 和 salary 复合索引EXPLAINSELECT id,NAME FROM student ORDER BY NAME,salaryEXPLAINSELECT id,NAME FROM student ORDER BY salary,NAME#排序字段未创建索引，性能就慢EXPLAINSELECT id,NAME FROM student ORDER BY sex

尽量使用 union all 替代 union

反例：

SELECT * FROM studentUNIONSELECT * FROM student

正例：

SELECT * FROM studentUNION ALLSELECT * FROM student

理由：

• union 和 union all 的区别是，union 会自动去掉多个结果集合中的重复结
  果，而 union all 则将所有的结果全部显示出来，不管是不是重复
• union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则
  的排序
• union 在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生
  的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中
  是不会产生重复的记录，最常见的是过程表与历史表 UNION