数据库优化详解

一、优化方向

!在这里插入图片描述](https://img-blog.csdnimg.cn/20200306094937203.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzOTQ1MjQ2,size_16,color_FFFFFF,t_70)

可以看出来，数据结构、SQL、索引是成本最低，且效果最好的优化手段。

数据库优化从以下几个方面优化：

数据库设计—三大范式、字段、表结构
数据库索引
存储过程 (模块化编程，可以提高速度)
分表分库 (水平分割，垂直分割)
主从复制、读写分离
SQL 调优
对 MySQL 配置优化 (配置最大并发数 my.ini, 调整缓存大小)
定时清除不需要的数据，定时进行碎片整理

二、具体优化方案

（一）数据库设计—三大范式、字段、表结构

1.根据数据库三范式来进行表结构的设计。设计表结构时，就需要考虑如何设计才能更有效的查询。

第一范式：数据表中每个字段都必须是不可拆分的最小单元，也就是确保每一列的原子性；
第二范式：满足一范式后，表中每一列必须有唯一性，都必须依赖于主键；
第三范式：满足二范式后，表中的每一列只与主键直接相关而不是间接相关 (外键也是直接相关)，字段没有冗余。

2.其他：

尽量使用 TINYINT、SMALLINT、MEDIUM_INT 作为整数类型而非 INT，如果非负则加上 UNSIGNED
VARCHAR 的长度只分配真正需要的空间
尽量使用整数代替字符串类型
单表不要有太多字段，建议在 20 以内
避免使用 NULL 字段，很难查询优化且占用额外索引空间
不建议使用 select * from t ，用具体的字段列表代替 “*”，不要返回用不到的任何字段。尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理
表与表之间通过一个冗余字段来关联，要比直接使用 JOIN 有更好的性能
select count (*) from table；这样不带任何条件的 count 会引起全表扫描

（二）索引

索引也算数据库设计的一部分

1.一般来说，应该在这些列上创建索引：

在经常需要搜索的列上，可以加快搜索的速度；
在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
在经常需要**排序的列（group by 或者 order by）**上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
在经常使用在 WHERE 子句中的列上面创建索引，加快条件的判断速度。

总结就是：唯一、不为空、经常被查询的字段

2.对于有些列不应该创建索引：

对于那些在查询中很少使用或者参考的列不应该创建索引。
对于那些只有很少数据值的列也不应该增加索引。
对于那些定义为 text, image 和 bit 这种数据量很大的数据类型的列不应该增加索引。
当修改性能远远大于检索性能时，不应该创建索引。修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

3.索引失效

在以下这些情况种，执行引擎将放弃使用索引而进行全表扫描

在 where 子句中使用**！= 或 <> 操作符**
在 where 子句中使用 or 来连接条件，当连接的字段有字段没有索引时，将导致所有字段的索引失效
在 where 子句字段进行 null 值判断，
在 where 子句中 like 的模糊匹配以 % 开头
在 where 子句中对索引进行表达式运算或函数操作
如果执行引擎估计使用全表扫描要比使用索引快，则不使用索引

（三）主从复制

1.定义：

在实际的生产环境中，对数据库的读和写都在同一个数据库服务器中，是不能满足实际需求的。无论是在安全性、高可用性还是高并发等各个方面都是完全不能满足实际需求的。因此，通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力。

作用：数据库备份，读写分离，高可用，集群.

2.过程：

在每个事务更新数据完成之前，master 在二进制日志记录这些改变。写入二进制日志完成后，master 通知存储引擎提交事务。
Slave 将 master 的 binary log 复制到其中继日志。首先 slave 开始一个工作线程（I/O），I/O 线程在 master 上打开一个普通的连接，然后开始 binlog dump process。binlog dump process 从 master 的二进制日志中读取事件，如果已经跟上 master，它会睡眠并等待 master 产生新的事件，I/O 线程将这些事件写入中继日志。
Sql slave thread（sql 从线程）处理该过程的最后一步，sql 线程从中继日志读取事件，并重放其中的事件而更新 slave 数据，使其与 master 中的数据一致，只要该线程与 I/O 线程保持一致，中继日志通常会位于 os 缓存中，所以中继日志的开销很小。

（四）分库分表

主从复制中，从数据库可以通过增加数量不断扩张，但是主数据库不能轻易增加，这个时候可以考虑分表分库。

1.分表方式

水平分割（按行）、垂直分割 (按列)

垂直拆分：垂直拆分就是要把表按模块划分到不同的数据库中，数据库按模块和功能把表划分出来，趋向于服务化
水平拆分：水平拆分就是要把一个表按照一定的规则把数据划分到不同的表或数据库中。比如按时间，账号规则，年份，取模算法等.

2.分表场景

根据经验，mysql 表数据一般达到百万级别，查询效率就会很低。
一张表的某些字段值比较大并且很少使用。可以将这些字段隔离成单独一张表，通过外键关联，例如考试成绩，我们通常关注分数，不关注考试详情。

3.水平分表策略

按时间分表：当数据有很强的实效性，例如微博的数据，可以按月分割。
按区间分表：例如用户表 1 到一百万用一张表，一百万到两百万用一张表。
hash 分表：通过一个原始目标 id 或者是名称按照一定的 hash 算法计算出数据存储的表名。

4.分表缺点：

分页查询困难
查询非常受限

（五）SQL调优

SQL最常见的方式是，由自带的慢查询日志或者开源的慢查询系统定位到具体的出问题的 SQL，然后使用 explain、profile 等工具来逐步调优，最后经过测试达到效果后上线。

1.开启慢查询

（1）定义：

MySQL 默认设置 10s 没有返回结果的，属于慢查询，并存到日志中 (在 my.ini 可以指定慢查询日志目录).

（2）开启慢查询

slow_query_log 慢查询开启状态。
slow_query_log_file 慢查询日志存放的位置（这个目录需要 MySQL 的运行帐号的可写权限，一般设置为 MySQL 的数据存放目录）。
long_query_time 查询超过多少秒才记录。

以上三个参数可以在数据库的配置文件中设定开启，也可以在在mysql命令行通过set命令开启。

当在配置文件中开启慢查询日志记录之后，就会在指定的存放目录生成日志文件。

2.分析慢查询—explain

当我们获得慢查询的日志之后，查看日志，观察那些语句执行是慢查询，在该语句之前加上explain再次执行，explain 会在查询上设置一个标志，当执行查询时，这个标志会使其返回关于在执行计划中每一步的信息，而不是执行该语句。它会返回一行或多行信息，显示出执行该计划中的每一部分和执行次序.

explain通常用于查看索引是否生效

显示字段如下：

id	SELECT 识别符。这是 SELECT 的查询序列号
select_type	SELECT 类型，可以为以下任何一种: SIMPLE: 简单 SELECT (不使用 UNION 或子查询) PRIMARY: 最外面的 SELECT UNION:UNION 中的第二个或后面的 SELECT 语句 DEPENDENT UNION:UNION 中的第二个或后面的 SELECT 语句，取决于外面的查询 UNION RESULT:UNION 的结果 SUBQUERY: 子查询中的第一个 SELECT DEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询 DERIVED: 导出表的 SELECT (FROM 子句的子查询)
table	输出的行所引用的表
type	联接类型。下面给出各种联接类型，按照从最佳类型到最坏类型进行排序: system: 表仅有一行 (= 系统表)。这是 const 联接类型的一个特例。 const: 表最多有一个匹配行，它将在查询开始时被读取。因为仅有一行，在这行的列值可被优化器剩余部分认为是常数。const 表很快，因为它们只读取一次！ eq_ref: 对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了 const 类型。 ref: 对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。 ref_or_null: 该联接类型如同 ref, 但是添加了 MySQL 可以专门搜索包含 NULL 值的行。 index_merge: 该联接类型表示使用了索引合并优化方法。 unique_subquery: 该类型替换了下面形式的 IN 子查询的 ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery 是一个索引查找函数，可以完全替换子查询，效率更高。 index_subquery: 该联接类型类似于 unique_subquery。可以替换 IN 子查询，但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr) range: 只检索给定范围的行，使用一个索引来选择行。 index: 该联接类型与 ALL 相同，除了只有索引树被扫描。这通常比 ALL 快，因为索引文件通常比数据文件小。 ALL: 对于每个来自于先前的表的行组合，进行完整的表扫描。
possible_keys	指出 MySQL 能使用哪个索引在该表中找到行
key	显示 MySQL 实际决定使用的键 (索引)。如果没有选择索引，键是 NULL。
key_len	显示 MySQL 决定使用的键长度。如果键是 NULL, 则长度为 NULL。
ref	显示使用哪个列或常数与 key 一起从表中选择行。
rows	显示 MySQL 认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数。
filtered	显示了通过条件过滤出的行数的百分比估计值。
Extra	该列包含 MySQL 解决查询的详细信息 Distinct:MySQL 发现第 1 个匹配行后，停止为当前的行组合搜索更多的行。 Not exists:MySQL 能够对查询进行 LEFT JOIN 优化，发现 1 个匹配 LEFT JOIN 标准的行后，不再为前面的的行组合在该表内检查更多的行。 range checked for each record (index map: #):MySQL 没有发现好的可以使用的索引，但发现如果来自前面的表的列值已知，可能部分索引可以使用。 Using filesort:MySQL 需要额外的一次传递，以找出如何按排序顺序检索行。 Using index: 从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。 Using temporary: 为了解决查询，MySQL 需要创建一个临时表来容纳结果。 Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。 Using sort_union(...), Using union(...), Using intersect(...): 这些函数说明如何为 index_merge 联接类型合并索引扫描。 Using index for group-by: 类似于访问表的 Using index 方式，Using index for group-by 表示 MySQL 发现了一个索引，可以用来查询 GROUP BY 或 DISTINCT 查询的所有列，而不要额外搜索硬盘访问实际的表。