SQL优化

SQL基础优化

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

• 字段多时，大表能达到100多个字段甚至达200多个字段

• 只取需要的字段，节省资源、减少网络开销

• select * 进行查询时，很可能不会用到索引，就会造成全表扫描

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

• 解决 : # 使用union all 或 # 分开两条sql写

• 使用or可能会使索引失效，从而全表扫描

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

3 使用varchar代替char

• varchar变长字段按数据内容实际长度存储，存储空间小，可以节省存储空间

• char按声明大小存储，不足补空格

• 其次对于查询来说，在一个相对较小的字段内搜索，效率更高

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

• 例如: 支付方式（payment）：1-现金、2-微信、3-支付宝、4-信用卡、5-银行卡

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

• 如果查询返回数据量很大，就会造成查询时间过长，网络传输时间过长。大量数据返回也可能没有实际意义，用户也看不过来,通常采用分页，一页习惯10/20/50/100条。

6 使用explain分析你SQL执行计划

EXPLAIN
SELECT * FROM student WHERE id=1

type：

• ALL 全表扫描，没有优化，最慢的方式

• index 索引全扫描

• range 索引范围扫描，常用语<，<=，>=，between等操作

• ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中

• eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询

• const/system 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询

• null MySQL不访问任何表或索引，直接返回结果

possible_keys：显示可能应用在这张表中的索引

key： 真正使用的索引方式

7 创建字段的索引

ALTER TABLE student ADD INDEX index_name (NAME)

8 优化like语句

正例：

EXPLAIN
SELECT id,NAME FROM student WHERE NAME LIKE '1%'

9 搜索类型匹配 字符串现象

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

正例：
#使用索引
EXPLAIN
SELECT * FROM student WHERE NAME='123'

• 不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL会做隐式的类型转换，把它们转换为数值类型再做比较

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

• 索引并不是越多越好，虽其提高了查询的效率，但却会降低插入和更新的效率

• 索引可以理解为一个就是一张表，其可以存储数据，其数据就要占空间

• 再者，索引表的一个特点，其数据是排序的，那排序要不要花时间呢？肯定要

• insert或update时有可能会重建索引，如果数据量巨大，重建将进行记录的重新排序，所以建索引需要慎重考虑，视具体情况来定

• 索引列有大量重复数据，Mysql查询优化器推算发现不走索引的成本更低，很可能就放弃索引了。

11 where限定查询的数据

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

12 避免在索引列上使用内置函数

业务需求：查询最近七天内新生儿（用学生表替代下）
给birthday字段创建索引：
ALTER TABLE student ADD INDEX idx_birthday (birthday)
当前时间加7天：
SELECT NOW() 
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY)

反例：
EXPLAIN
SELECT * FROM student
WHERE DATE_ADD(birthday,INTERVAL 7 DAY) >=NOW();

正例：
EXPLAIN
SELECT * FROM student
WHERE  birthday >= DATE_ADD(NOW(),INTERVAL 7 DAY);

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

反例：
EXPLAIN
SELECT * FROM student WHERE id+1-1=+1

正例：
EXPLAIN
SELECT * FROM student WHERE id=+1-1+1
 
EXPLAIN
SELECT * FROM student WHERE id=1
理由： SQL解析时，如果字段相关的是表达式就进行全表扫描

14 避免在where子句中使用!=或<>操作符

• 使用!=和<>很可能会让索引失效

15 去重distinct过滤字段要少

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

16 where中使用默认值代替null

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

SQL高级优化

1 批量插入性能提升

• 默认新增SQL有事务控制，导致每条都需要事务开启和事务提交；而批量处理是一次事务开启和提交。自然速度飞升

2 批量删除优化

• 一次性删除太多数据，可能造成锁表，会有lock wait timeout exceed的错误，所以建议分批操作

3 伪删除设计

商品状态（state）：1-上架、2-下架、3-删除

理由：

• 这里的删除只是一个标识，并没有从数据库表中真正删除，可以作为历史记录备查
• 同时，一个大型系统中，表关系是非常复杂的，如电商系统中，商品作废了，但如果直接删除商品，其它商品详情，物流信息中可能都有其引用。
• 通过where state=1或者where state=2过滤掉数据，这样伪删除的数据用户就看不到了，从而不影响用户的使用
• 操作速度快，特别数据量很大情况下

4 提高group by语句的效率

• 执行分组语句前过滤不需要记录, 先过滤，后分组

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

5 复合索引最左特性

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

6 排序字段创建索引

• where和order by中常出现的字段就创建索引。

7 删除冗余和重复的索引

8 不要有超过5个以上的表连接

• 关联的表个数越多，编译的时间和开销也就越大
• 每次关联内存中都生成一个临时表
• 应该把连接表拆开成较小的几个执行，可读性更高
• 如果一定需要连接很多表才能得到数据，那么意味着这是个糟糕的设计了
• 阿里规范中，建议多表联查三张表以下

9 inner join 、left join、right join，优先使用inner join

三种连接如果结果相同，优先使用inner join，如果使用left join左边表尽量小

• inner join 内连接，只保留两张表中完全匹配的结果集
• left join会返回左表所有的行，即使在右表中没有匹配的记录
• right join会返回右表所有的行，即使在左表中没有匹配的记录

理由：

• 如果inner join是等值连接，返回的行数比较少，所以性能相对会好一点
• 同理，使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少。这是mysql优化原则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优

10 in子查询的优化

• 数据库最费劲的就是程序链接的释放。假设链接了两次，每次做上百万次的数据集查询，查完就结束，这样就只做了两次；相反建立了上百万次链接，申请链接释放反复重复，就会额外花费很多实际，这样系统就受不了了，慢，卡顿

11 尽量使用union all替代union

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