数据库知识【第15期】-- MySQL优化

vlambda
2022-03-16

数据库知识【第15期】-- MySQL优化

导读:

大家好，我是老田。本篇是数据库系列的第15篇，今天我们梳理MySQL数据库如何进行优化。主要体现在四个方面：数据库设计、SQL语句优化、系统配置优化、硬件优化。

1.MySQL优化的方式

MySQL数据库优化主要有以下几个方面：

数据库设计优化
SQL语句优化
系统配置优化
硬件优化

2.数据库设计优化

我们在开发应用程序时，设计的数据库要最大程度的遵守三范式。使用三范式的目的是对表进行规范化。

第一范式：第一范式用来规范所有的字段，所有的字段都不可再分,两列的属性相近或相似或一样，尽量合并属性一样的列，确保不产生冗余数据。
第二范式：第二范式需要确保数据库表中每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
第三范式：第三范式要消除传递依赖。也就是在非主键字段中，不可以通过一个字段可以推导出另外一个字段。
反范式：三范式最大的问题在于查询时通常需要join很多表，导致查询效率很低。所以有时候基于性能考虑，我们需要有意的违反三范式，适度的做冗余，以达到提高查询效率的目的。注意这里的反范式是适度的，必须为这种做法提供充分的理由。

3.表中选取最适用的字段属性

MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。

对于那些可能值很有限的列，使用tinyint代替VARCHAR
对于那些定长字符串，可以使用char，比如邮编，总是5位
对于那些长度未知的字符串，使用varchar
适当打破数据库范式添加冗余字段，避免查询时的表连接
文件、图片等大文件用文件系统存储，不用数据库。数据库只存储路径。

例如，在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库增加了不必要的空间，甚至使用VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。同样的，如果可以的话，我们应该使用INT而不是BIGIN来定义整型字段。

另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOTNULL，这样在将来执行查询的时候，数据库不用去比较NULL值。

NULL使得索引维护更加复杂，强烈建议对索引列设置NOT NULL
NOT IN、!=等负向条件查询在有NULL值的情况下返回永远为空结果，查询容易出错
NULL列需要一个额外字节作为判断是否为NULL的标志位
使用NULL时和该列其他的值可能不是同种类型，导致问题。（在不同的语言中表现不一样）
MySQL难以优化对可为NULL的列的查询

4.SQL语句优化

SQL语句优化方法非常多，我只列出常用的，其他的大家可以再总结总结。

EXPLAIN 使用EXPLAIN查看SQL执行计划
SQL语句中IN包含的值不应过多
MySQL对于IN做了相应的优化，即将IN中的常量全部存储在一个数组里面，而且这个数组是排好序的。但是如果数值较多，产生的消耗也是比较大的
SELECT语句务必指明字段名称
SELECT * 增加很多不必要的消耗（CPU、IO、内存、网络带宽）；增加了使用覆盖索引的可能性；当表结构发生改变时，前端也需要更新。所以要求直接在select后面接上字段名。
当只需要一条数据的时候，使用limit 1
如果排序字段没有用到索引，就尽量少排序
如果限制条件中其他字段没有索引，尽量少用or+
or两边的字段中，如果有一个不是索引字段，而其他条件也不是索引字段，会造成该查询不走索引的情况。很多时候使用union all或者是union（必要的时候）的方式来代替“or”会得到更好的效果。
尽量用union all代替union
union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。当然，union all的前提条件是两个结果集没有重复数据。
区分使用in和exists、not in和not exists
区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。所以IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。
使用合理的分页方式以提高分页的效率
```
 select id,name from product limit 100000, 10
```
使用上述SQL语句做分页的时候，可能有人会发现，随着表数据量的增加，直接使用limit分页查询会越来越慢。
优化的方法如下：可以取前一页的最大行数的id，然后根据这个最大的id来限制下一页的起点。比如此列中，上一页最大的id是999999。SQL可以采用如下的写法：
```
 select id,name from product where id> 999999 limit 10
```
避免在where子句中对字段进行null值判断
对于null的判断会导致引擎放弃使用索引而进行全表扫描。
不建议使用%前缀模糊查询
例如LIKE "%name"或者LIKE "%name%"，这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE "name%"。%name%这种可以使用全文索引代替。

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

 select user_id,user_project from user_base where age*2=36;
 -- 对字段就行了算术运算，这会造成引擎放弃使用索引，建议改成：
 select user_id,user_project from user_base where age=36/2;

对于联合索引来说，要遵守最左前缀法则
最左前缀匹配原则：在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

如果在数据列a，b，c上建立了复合索引，索引中的数据行按照a/b/c次序排列，因此数据表会自动的按照a/b和a排列，即使指定了a，或者a和b的值，mysql也可以使用这个索引。

Mysql不能利用这个索引搜索没有包含在最左前缀的内容。如按照b或者按照c来搜索，就不会使用这个索引
关于JOIN优化

尽量使用inner join，避免left join：

参与联合查询的表至少为2张表，一般都存在大小之分。如果连接方式是inner join，在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表，但是left join在驱动表的选择上遵循的是左边驱动右边的原则，即left join左边的表名为驱动表。
合理利用索引：被驱动表的索引字段作为on的限制字段。

5.系统配置优化

数据库配置参数优化

修改back_log参数值
back_log值指在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。也就是说，如果MySql的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某⼀一连接释放资源，该堆栈的数量即back_log
修改wait_timeout参数值，由默认的8小时，修改为1或者半小时。减少wait的连接数，如果系统使用的人少可以调大一点
修改max_connections参数值，根据系统需要调整
修改max_user_connections值，由默认的0，修改为800，max_user_connections=800针对某一个账号的所有客户端并行连接到MYSQL服务的最大并行连接数。简单说是指同一个账号能够同时连接到mysql服务的最大连接数。设置为0表示不限制。
修改thread_concurrency值，由目前默认的8，修改为64 thread_concurrency=64
设置MySQL的默认存储引擎default-storage-enginedefault-storage-engine= InnoDB
修改max_connect_errors为100这个参数负责阻止客户端尝试暴力破解密码，当某台主机错误连接次数达到该值时，该主机无法再尝试登陆。解决方法是重启mysql，或者把该值改大一点
开启查询缓存query_cache_type=1 开启缓存，显示为ONquery_cache_size设置为10241024128

6.大表拆分

对于那些数据量非常大或者增长很快的表，一定要提前做好垂直分表或者水平分表，当数据量超过百万以后，查询速度会明显下降。

分库分表尽量在数据库设计初期敲定方案，否则后期会极大增加代码复杂性而且不易更改。

垂直拆分把主键和一些常用的字段放到一个表中，把主键和其他的字段放到另一个表中。

优点：垂直拆分可以使一个数据页放更多的数据，可以较少IO次数。
缺点：查询所需的数据可能需要通过JOIN来查询。
适用场景：表过宽，包含text或blob字段，可以将不常用的列或text/blob列放到另外的表中存储。比如文章表可以将文章内容拆分到另外的表中。

水平拆分根据某一列的值把数据放到多个独立的表中，比如历史数据放到另一张表里。

优点：减少大多数查询读取的数据量，降低索引层数，提高查询速度。
缺点：增加查询复杂度，查询多个表需要使用UNION，或者通过MERGE表。
适用场景：表中数据量过大，历史数据查询次数很少，比如订单信息、操作记录等。

7.合理的硬件资源

读写分离

如果数据库压力很大，一台机器支撑不了，那么可以用mysql复制实现多台机器同步，将数据库的压力分散。

读写分离就是让主库处理事务性查询，从库处理select查询。数据库复制被用来把事务性查询导致的数据变更同步到从库，当然，主库也可以select查询。

读写分离最大的作用就是缓解服务器的压力。

读写分离的好处：

增加冗余；
缓解服务器的压力；增加机器的处理能力；

对于读应用较多的，应用读写分离是最好的，可以确保写的服务器压力更小，而读的服务器又可以接受点时间上的延迟。

读写分离知识点还很多，就不展开描述了。后续有机会再给大家分享。

博观而约取，厚积而薄发！

--END--

vlambda博客
学习文章列表