2022-05-13发表2022-01-26更新24 分钟读完 (大约3586个字)0次访问

【MySQL】MySql相关优化分享

InterviewCoder

# 【MySQL】MySql 相关优化分享

# 文章目录

前言
一、数据库优化
二、表优化
三、SQL 语句优化

# 前言

现在在网上搜索，有很多类似文章，mysql 优化大全，mysql 最强总结等等，部分文章存在一些错误。在这里个人总结整理的一些点，希望对大家有所帮助。

本篇从数据库、表、sql 语句几个维度来说优化，如果文章有误之处欢迎指正～

# 一、数据库优化

# 1. 选择合适存储引擎

MySQL5.5 版本开始，InnoDB 已经成为 Mysql 的默认引擎 (之前是 MyISAM)，说明其优势是有目共睹的。如果你不知道用什么存储引擎，那就用 InnoDB，至少不会差。

如何选择呢：

是否要支持事务，如果要请选择 InnoDB，如果不需要可以考虑 MyISAM；
如果表中绝大多数都只是读查询，可以考虑 MyISAM，如果既有读，写也挺频繁，请使用 InnoDB。
系统奔溃后，MyISAM 恢复起来更困难，能否接受，不能接受就选 InnoDB；

# 2. 选择合适的连接池

1：性能方面 hikariCP>druid>tomcat-jdbc>dbcp>c3p0 。hikariCP 的高性能得益于最大限度的避免锁竞争。
2：druid 功能最为全面，sql 拦截等功能，统计数据较为全面，具有良好的扩展性。
3：HikariCP 因为细节方面优化力度较大，性能方面强于 Druid
4：综合性能，扩展性等方面，可考虑使用 druid 或者 hikariCP 连接池。

注：SpringBoot 2.0 以后默认连接池是 hikariCP。

# 3. 分库分表

在数据量增长和增长速度越来越高的情况下，单库可能在容量、IO、并发性能上都无法支撑，这个时候就要对业务进行切分或数据库进行扩展，数据库的扩展也就是分库分表。

分库分表的方式有垂直拆分和水平拆分。
垂直拆分是根据业务进行拆分，这种拆分不能解决单业务点数据量大的问题。
水平拆分是根据某一列进行拆分（如 id，userId），拆分后的每个库结构一致。

# 4. 主从同步

一般部署架构为一台 Master 和 n 台 Slave，Master 的主责为写，并将数据同步至 Slave，Slave 主要提供查询功能。

可以使用数据库中间件，例如 MyCat 来实现。MyCat 的读写分离是建立在 MySQL 主从复制基础之上实现的，Mycat 读写分离和自动切换机制，需要 mysql 的主从复制机制配合。

# 二、表优化

# 1. 表中的字段选择合适的数据类型

能用数字类型，就不用字符串，因为字符的处理往往比数字要慢。

尽可能使用小的类型，比如：用 bit 存布尔值，用 tinyint 存枚举值等。

长度固定的字符串字段，用 char 类型，该类型的字段存储空间的固定的。

长度可变的字符串字段，用 varchar 类型，该类型的字段存储空间会根据实际数据的长度调整，不会浪费存储空间。

金额字段用 decimal，避免精度丢失问题。

1、当一个列可以选择多种数据类型时，应该优先考虑数字类型，其次是日期和二进制类型，最后是字符类型。
2、对于相同级别的数据类型，应该优先选择占用空间小的数据类型。

# 2. 适当添加索引

MySQL 里同一个数据表里的索引总数限制为 16 个。

索引尽量的扩展索引，不要新建索引。

在表中建立索引，优先考虑 where、order by 使用到的字段。

阿里巴巴的开发者手册中规定，单表的索引数量应该尽量控制在 5 个以内，并且单个索引中的字段数不超过 5 个。

# 3. 表中适当保留冗余数据

没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，提高读性能，就必须降低范式标准，适当保留冗余数据。

虽然三大范式是为了解决数据库冗余的问题，但是阿里开发手册中提到可以适当的违反范式，允许少量的冗余，以便提高查询效率，也就是使用空间换时间。

具体做法是：在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，减少了查询时的关联，提高查询效率。

# 4. 增加中间表

对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。

# 5. 字段很多的表分解成多个表

对于字段比较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。

# 6. 添加适当存储过程

一个存储过程是一个可编程的函数，它在数据库中创建并保存，一般由 SQL 语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的特定功能时，存储过程尤为合适。

存储过程与 SQL 语句如何抉择：

架构设计没有绝对，只有在当前的场景下最合适的。
普通的项目开发中，不建议大量使用存储过程，对比 SQL 语句，存储过程适用于业务逻辑复杂，比较耗时，同时请求量较少的操作，例如后台大批量查询、定期更新等。
（1）当一个事务涉及到多个 SQL 语句时或者涉及到对多个表的操作时可以考虑应用存储过程
（2）在一个事务的完成需要很复杂的商业逻辑时可以考虑应用存储过程
（3）比较复杂的统计和汇总可以考虑应用后台存储过程

# 三、SQL 语句优化

# 1. 尽量使用表的别名，减少解析

当在 SQL 语句中连接多个表时，使用表的别名并把别名前缀于每个 Column 上，这样一来，就可以减少解析的时间并减少那些由 Column 歧义引起的语法错误。

# 2.select 子句中避免使用 * 号

使用具体的列名，可以有效增加查询速度。

避免回表查询。
比如你创建了 name， age 索引 name_age_index，查询数据时使用了：
select * from table where name =‘陈哈哈’ and age = 26;
由于附加索引中只有 name 和 age，因此命中索引后，数据库还必须回去聚集索引中查找其他数据，这就是回表。

失去 MySQL 优化器 “覆盖索引” 策略优化的可能性。

# 3. 将 where 中用的比较频繁的字段建立索引，避免全表扫描

1. 普通索引：这是最基本的索引类型，而且它没有唯一性之类的限制。
2. 唯一索引：和普通索引基本相同，只是索引列的所有值都只能出现一次，即必须唯一。
3. 主键索引：就是唯一且不能为空。主键索引是一种特殊的唯一索引。必须指定为 “PRIMARY KEY”。
4. 联合索引：多列值组成一个索引，专门用于组合搜索。
5. 全文索引：用于在一篇文章中，检索文本信息的，适合在进行模糊查询的时候使用。

提示点：

唯一索引和普通索引使用的结构都是 B+Tree, 执行时间复杂度都是 O。
如果在一个列上同时建唯一索引和普通索引的话，mysql 会自动选择唯一索引。
MySQL 建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。同时遇到范围查询 (>、<、between、like) 就会停止匹配。
索引区分度低的字段不要加索引，比如性别，如果添加了索引每次查询会先走索引树，再回表查询，增加了额外的 io 消耗。

# 4. 避免索引失效情况

索引失效情况

1、like 查询以 “%” 开头；(这个范围非常大，所以没有使用索引的必要了)
2、or 查询左右都没有使用索引；(or 可以使用 unint)
3、联合索引中没有使用第一列索引；(为遵循最左匹配原则)
4、在 where 中索引列上使用 “not”，“<>”，“!=”；(不等于操作符可能不会用到索引的，产生全表扫描)
5、在 where 中索引列上使用函数或进行计算操作，索引失效。(更改字段导致失效)
6、如果 mysql 觉得全表扫描更快时（数据少时）
7、在索引列上使用 “IS NULL” 或 “IS NOT NULL” 操作，索引可能失效（如果列上全部数据不为空，索引会失效，但是如果有空值，索引不会失效）
…

在这里插入图片描述

# 5. 当只需要一条数据的时候可以使用 limit 1

这是为了使 EXPLAIN 中 type 列达到 const 类型

# 6. 调整 Where 字句中的连接顺序

采用自下而上的顺序解析 where 字句，根据这个原理表连接最好写在其他 where 条件之前，那些可以过滤掉最大数量记录。

# 7. 小表驱动大表

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

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

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

总结一下：

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

# 8. 善用 EXPLAIN 查看 SQL 执行计划

1	EXPLAIN select column_name from table_name;