1. 软优化

1.1. 查询语句优化

1.1.1. 避免使用select *

很多时候，我们写sql语句时，为了方便，喜欢直接使用select *，一次性查出表中所有列的数据。

在实际业务场景中，可能我们真正需要使用的只有其中一两列。select *不会走覆盖索引，会出现大量的回表操作，而从导致查询sql的性能很低。sql语句查询时，只查需要用到的列，多余的列根本无需查出来。

select name,age from user where id=1;

1.1.2. 用union all代替union

sql语句使用union关键字后，可以获取排重后的数据。

而如果使用union all关键字，可以获取所有数据，包含重复的数据。

反例：

(select * from user where id=1) 
union 
(select * from user where id=2);

排重的过程需要遍历、排序和比较，它更耗时，更消耗cpu资源。

所以如果能用union all的时候，尽量不用union。

正例：

(select * from user where id=1) 
union all
(select * from user where id=2);

除非是有些特殊的场景，比如union all之后，结果集中出现了重复数据，而业务场景中是不允许产生重复数据的，这时可以使用union。

1.1.3. 小表驱动大表

小表驱动大表，也就是说用小表的数据集驱动大表的数据集。

假如有order和user两张表，其中order表有10000条数据，而user表有100条数据。

这时如果想查一下，所有有效的用户下过的订单列表。

可以使用in关键字实现：

select * from order
where user_id in (select id from user where status=1)

也可以使用exists关键字实现：

select * from order
where exists (select 1 from user where order.user_id = user.id and status=1)

前面提到的这种业务场景，使用in关键字去实现业务需求，更加合适。

为什么呢？

因为如果sql语句中包含了in关键字，则它会优先执行in里面的子查询语句，然后再执行in外面的语句。如果in里面的数据量很少，作为条件查询速度更快。

而如果sql语句中包含了exists关键字，它优先执行exists左边的语句（即主查询语句）。然后把它作为条件，去跟右边的语句匹配。如果匹配上，则可以查询出数据。如果匹配不上，数据就被过滤掉了。

这个需求中，order表有10000条数据，而user表有100条数据。order表是大表，user表是小表。如果order表在左边，则用in关键字性能更好。

总结一下：

• in 适用于左边大表，右边小表。
• exists 适用于左边小表，右边大表。

不管是用in，还是exists关键字，其核心思想都是用小表驱动大表。

1.2. 优化关联查询

1.2.1. 优化子查询

mysql中如果需要从两张以上的表中查询出数据的话，一般有两种实现方式：子查询和连接查询。

子查询的例子如下：

select * from order
where user_id in (select id from user where status=1)

子查询语句可以通过in关键字实现，一个查询语句的条件落在另一个select语句的查询结果中。程序先运行在嵌套在最内层的语句，再运行外层的语句。

子查询语句的优点是简单，结构化，如果涉及的表数量不多的话。

但缺点是mysql执行子查询时，需要创建临时表，查询完毕后，需要再删除这些临时表，有一些额外的性能消耗。

这时可以改成连接查询。具体例子如下：

select o.* from order o
inner join user u on o.user_id = u.id
where u.status=1

1.2.2. join的表不宜过多

根据阿里巴巴开发者手册的规定，join表的数量不应该超过3个。

反例：

select a.name,b.name.c.name,d.name
from a 
inner join b on a.id = b.a_id
inner join c on c.b_id = b.id
inner join d on d.c_id = c.id
inner join e on e.d_id = d.id
inner join f on f.e_id = e.id
inner join g on g.f_id = f.id

如果join太多，mysql在选择索引的时候会非常复杂，很容易选错索引。

并且如果没有命中中，nested loop join 就是分别从两个表读一行数据进行两两对比，复杂度是 n^2。

所以我们应该尽量控制join表的数量。

正例：

select a.name,b.name.c.name,a.d_name 
from a 
inner join b on a.id = b.a_id
inner join c on c.b_id = b.id

如果实现业务场景中需要查询出另外几张表中的数据，可以在a、b、c表中冗余专门的字段，比如：在表a中冗余d_name字段，保存需要查询出的数据。

不过我之前也见过有些ERP系统，并发量不大，但业务比较复杂，需要join十几张表才能查询出数据。

所以join表的数量要根据系统的实际情况决定，不能一概而论，尽量越少越好。

1.2.3. join小表驱动大表

我们在涉及到多张表联合查询的时候，一般会使用join关键字。

而join使用最多的是left join和inner join。

• left join：求两个表的交集外加左表剩下的数据。
• inner join：求两个表交集的数据。

使用inner join的示例如下：

select o.id,o.code,u.name 
from order o 
inner join user u on o.user_id = u.id
where u.status=1;

如果两张表使用inner join关联，mysql会自动选择两张表中的小表，去驱动大表，所以性能上不会有太大的问题。

使用left join的示例如下：

select o.id,o.code,u.name 
from order o 
left join user u on o.user_id = u.id
where u.status=1;

如果两张表使用left join关联，mysql会默认用left join关键字左边的表，去驱动它右边的表。如果左边的表数据很多时，就会出现性能问题。

要特别注意的是在用left join关联查询时，左边要用小表，右边可以用大表。如果能用inner join的地方，尽量少用left join。

1.3. 使用索引

1.3.1 覆盖索引

如果建立的索引上，查询时就已经有我们需要的字段，就不会回表

-- age索引上，肯定有id的主键
select id from user where age=18;

1.3.2. 联合索引

商品表举例，我们需要根据他的名称，去查他的库存，假设这是一个很高频的查询请求，你会怎么建立索引呢？

大家可以思考上面的回表的消耗对SQL进行优化。

是的建立一个，名称和库存的联合索引，这样名称查出来就可以看到库存了，不需要查出id之后去回表再查询库存了，联合索引在我们开发过程中也是常见的，但是并不是可以一直建立的，大家要思考索引占据的空间。

1.3.3. LIKE关键字

LIKE关键字匹配'%'开头的字符串,不会使用索引

-- 不会使用索引
select * from user where name like '%中'
select * from user where name like '%中%'

-- 会使用索引
select * from user where name like '中%'

1.3.4. 最左匹配原则

最左匹配原则就是指在联合索引中，如果你的 SQL 语句中用到了联合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个联合索引去进行匹配。例如某表现有索引(a,b,c)，现在你有如下语句：

-- 会使用索引 a,b,c
select * from t where a=1 and b=1 and c =1;

-- 会使用索引 a,b
select * from t where a=1 and b=1;

-- 会使用索引 a,b（mysql有查询优化器）
select * from t where b=1 and a=1;

-- 会使用索引 a
select * from t where a=1;

-- 不会使用索引 
select * from t where b=1 and c=1;

-- 会使用索引 a索引，但是b,c索引用不到
select * from t where a=1 and c=1;

当遇到范围查询(>、<、between、like)就会停止匹配

-- 会使用索引 a,b，但是c索引用不到 
select * from t where a=1 and b>1 and c =1;

这条语句只有 a,b 会用到索引，c 都不能用到索引。这个原因可以从联合索引的结构来解释。

但是如果是建立(a,c,b)联合索引，则a,b,c都可以使用索引，因为优化器会自动改写为最优查询语句

-- 如果是建立(a,c,b)联合索引，则a,b,c都可以使用索引
select * from t where a=1 and b >1 and c=1; 
-- 优化器改写为
select * from t where a=1 and c=1 and b >1;

1.3.5. 条件字段函数操作

日常开发过程中，大家经常对很多字段进行函数操作，如果对日期字段操作，浮点字符操作等等，大家需要注意的是，如果对字段做了函数计算，就用不上索引了，这是MySQL的规定。

对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

需要注意的是，优化器并不是要放弃使用这个索引。

这个时候大家可以用一些取巧的方法，比如

 -- 不会使用索引
 select * from tradelog where id + 1 = 10000

 -- 会使用索引
 select * from tradelog where id = 9999

1.3.6. 隐式类型转换

select * from t where id = 1

如果id是字符类型的，1是数字类型的，你用explain会发现走了全表扫描，根本用不上索引，为啥呢？

因为MySQL底层会对你的比较进行转换，相当于加了 CAST( id AS signed int) 这样的一个函数，上面说过函数会导致走不上索引。

1.3.7. 隐式字符编码转换

如果两个表的字符集不一样，一个是utf8mb4，一个是utf8，因为utf8mb4是utf8的超集，所以一旦两个字符比较，就会转换为utf8mb4再比较。

转换的过程相当于加了CONVERT(id USING utf8mb4)函数，那又回到上面的问题了，用到函数就用不上索引了。

1.3.8. OR关键字

OR关键字的两个字段必须都是用了索引，该查询才会使用索引。

select * from user where age = 1 or id = 1

1.3.9. 索引优化(explain、describe)

sql优化当中，有一个非常重要的内容就是：索引优化。

很多时候sql语句，走了索引，和没有走索引，执行效率差别很大。所以索引优化被作为sql优化的首选。

索引优化的第一步是：检查sql语句有没有走索引。

那么，如何查看sql走了索引没？

可以使用explain命令，查看mysql的执行计划。

desc select * from user

-- 排除mysql缓存问题分析，8.0以上的版本就不用担心这个问题，如果是8.0之下的版本，记得排除缓存的干扰
desc select SQL_NO_CACHE * from user

1.4. 分解表

对于字段较多的表,如果某些字段使用频率较低,此时应当,将其分离出来从而形成新的表。

1.5. 增加中间表

对于将大量连接查询的表可以创建中间表,从而减少在查询时造成的连接耗时。

1.6. 增加冗余字段

类似于创建中间表，增加冗余也是为了减少连接查询。

1.7. 分析表，检查表，优化表

分析表主要是分析表中关键字的分布，检查表主要是检查表中是否存在错误，优化表主要是消除删除或更新造成的表空间浪费。

• 分析表：使用 ANALYZE 关键字，如ANALYZE TABLE user;

• • Op：表示执行的操作
  • Msg_type：信息类型，有status，info，note，warning，error
  • Msg_text：显示信息

• 检查表：使用 CHECK关键字，如CHECK TABLE user [option]

• • option 只对MyISAM有效,共五个参数值:
  • QUICK：不扫描行，不检查错误的连接
  • FAST：只检查没有正确关闭的表
  • CHANGED：只检查上次检查后被更改的表和没被正确关闭的表
  • MEDIUM：扫描行，以验证被删除的连接是有效的，也可以计算各行关键字校验和
  • EXTENDED：最全面的的检查，对每行关键字全面查找

• 优化表：使用OPTIMIZE关键字，如OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE user;

LOCAL|NO_WRITE_TO_BINLOG都是表示不写入日志，优化表只对VARCHAR，BLOB和TEXT有效，通过OPTIMIZE TABLE语句可以消除文件碎片，在执行过程中会加上只读锁。

2. 硬优化

2.1. 硬件三件套(CPU，内存，磁盘)

• 配置多核心和频率高的cpu，多核心可以执行多个线程。
• 配置大内存，提高内存，即可提高缓存区容量，因此能减少磁盘I/O时间,从而提高响应速度。
• 配置高速磁盘或合理分布磁盘：高速磁盘提高I/O，分布磁盘能提高并行操作的能力。

2.2. 参数设置

优化数据库参数可以提高资源利用率，从而提高MySQL服务器性能。MySQL服务的配置参数都在my.cnf或my.ini，下面列出性能影响较大的几个参数

• key_buffer_size：索引缓冲区大小
• table_cache：能同时打开表的个数
• query_cache_size和query_cache_type：前者是查询缓冲区大小，后者是前面参数的开关，0表示不使用缓冲区，1表示使用缓冲区，但可以在查询中使用SQL_NO_CACHE表示不要使用缓冲区，2表示在查询中明确指出使用缓冲区才用缓冲区，即SQL_CACHE
• sort_buffer_size：排序缓冲区

2.3. 分库分表

分库分表方案是对关系型数据库数据存储和访问机制的一种补充。

• 分库：将一个库的数据拆分到多个相同的库中，访问的时候访问一个库

  • 垂直拆分
  
  • 水平拆分
  

• 分表：把一个表的数据放到多个表中，操作对应的某个表就行

  • 垂直拆分
  
  • 水平拆分
  

2.4. 主从复制 + 读写分离

让主数据库（master）处理事务性增、改、删操作（INSERT、UPDATE、DELETE），而从数据库（slave）处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。