【数据库】：MySQL数据库优化

1. MySQL架构

MySQL整体架构图如下：

2. 查询执行流程

查询执行的流程是这样的：

连接

1. 客户端发起一条Query请求，监听客户端的‘连接管理模块’接收请求
2. 将请求转发到‘连接进/线程模块’
3. 调用‘用户模块’来进行授权检查
4. 通过检查后，‘连接进/线程模块’从‘线程连接池’中取出空闲的被缓存的连接线程和客户端请求对接，如果失败则创建一个新的连接请求。

处理

1. 先查询缓存，检查Query语句是否完全匹配，
2. 查询缓存失败则转交给‘命令解析器’
3. 再转交给对应的模块处理
4. 如果是SELECT查询还会经由‘查询优化器’做大量的优化，生成执行计划
5. 模块收到请求后，通过‘访问控制模块’检查所连接的用户是否有访问目标表和目标字段的权限
6. 有则调用‘表管理模块’，先是查看table cache中是否存在，有则直接对应的表和获取锁，否则重新打开表文件
7. 根据表的meta数据，获取表的存储引擎类型等信息，通过接口调用对应的存储引擎处理
8. 上述过程中产生数据变化的时候，若打开日志功能，则会记录到相应二进制日志文件中

结果

1. Query请求完成后，将结果集返回给‘连接进/线程模块’
2. 返回的也可以是相应的状态标识，如成功或失败等
3. ‘连接进/线程模块’进行后续的清理工作，并继续等待请求或断开与客户端的连接

3. 什么是优化

优化主要包含如下几大要点：

• 合理安排资源、调整系统参数使MySQL运行更快、更节省资源。
• 优化是多方面的，包括查询、表设计、服务器等。
• 原则：减少系统瓶颈，减少资源占用，增加系统的反应速度。

4. 查询优化

在优化MySQL时，通常需要库进行分析。常见的分析手段有 慢查询日志，EXPLAIN分析查询，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。

4.1 慢查询日志

慢查询日志开启

在配置文件my.cnf或my.ini中在[mysqld]一行下面加入两个配置参数：

log-slow-queries=/data/mysqldata/slow-query.log
long_query_time=5

注：

1. log-slow-queries参数为慢查询日志存放的位置，一般这个目录要有mysql的运行帐号的可写权限，一般都将这个目录设置为mysql的数据存放目录；
2. long_query_time=5中的5表示查询超过五秒才记录；
3. 还可以在my.cnf或者my.ini中添加log-queries-not-using-indexes参数，表示记录下没有使用索引的查询。

慢查询分析

我们可以通过打开log文件查看得知哪些SQL执行效率低下，从日志中，可以发现查询时间超过5 秒的SQL，而小于5秒的没有出现在此日志中。

如果慢查询日志中记录内容很多，可以使用mysqldumpslow工具（MySQL客户端安装自带）来对慢查询日志进行分类汇总。mysqldumpslow对日志文件进行了分类汇总，显示汇总后摘要结果。

进入log的存放目录，运行：

[root@mysql_data]#mysqldumpslow slow-query.log

Reading mysql slow query log from slow-query.log
Count: 2 Time=11.00s (22s) Lock=0.00s (0s) Rows=1.0 (2), root[root]@mysql
select count(N) from t_user;

mysqldumpslow命令:

/path/mysqldumpslow -s c -t 10 /database/mysql/slow-query.log

这会输出记录次数最多的10条SQL语句，其中：

• -s, 是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙；
• -t, 是top n的意思，即为返回前面多少条的数据；
• -g, 后边可以写一个正则匹配模式，大小写不敏感的；

例如：

/path/mysqldumpslow -s r -t 10 /database/mysql/slow-log
得到返回记录集最多的10个查询。

/path/mysqldumpslow -s t -t 10 -g “left join” /database/mysql/slow-log
得到按照时间排序的前10条里面含有左连接的查询语句。

使用mysqldumpslow命令可以非常明确的得到各种我们需要的查询语句，对MySQL查询语句的监控、分析、优化是MySQL优化非常重要的一步。开启慢查询日志后，由于日志记录操作，在一定程度上会占用CPU资源影响mysql的性能，但是可以阶段性开启来定位性能瓶颈。

4.2 EXPLAIN

在MySQL中可以使用EXPLAIN查看SQL执行计划，用法：EXPLAIN SELECT * FROM products。

5.索引使用

5.1 MySQL索引

B-Tree索引

一般来说，MySQL中的B-Tree索引的物理文件大多都是以二叉树的结构来存储的，也就是所有实际需要的数据都存放于树的叶子节点，而且到任何一个叶子节点的最短路径的长度都是完全相同的。

R-Tree索引

RTREE在mysql很少使用，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。相对于BTREE，RTREE的优势在于范围查找。

Hash索引

Hash索引在MySQL中使用的并不是很多，目前主要是Memory存储引擎使用，而且在Memory存储引擎中将Hash索引作为默认的索引类型。所谓Hash索引，实际上就是通过一定的Hash算法，将需要索引的键值进行Hash运算，然后将得到的Hash值存入一个Hash表中。然后每次需要检索的时候，都会将检索条件进行相同算法的Hash运算，然后再和Hash表中的Hash值进行比较并得出相应的信息。
Hash索引特点：

1. Hash索引仅仅只能满足“=”,“IN”和“<=>”查询，不能使用范围查询；
2. Hash索引无法被利用来避免数据的排序操作；
3. Hash索引不能利用部分索引键查询；
4. Hash索引在任何时候都不能避免表扫面；
5. Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高；

Full-text索引

Full-text索引也就是我们常说的全文索引，目前在MySQL中仅有MyISAM存储引擎支持，而且也并不是所有的数据类型都支持全文索引。目前来说，仅有CHAR，VARCHAR和TEXT这三种数据类型的列可以建Full-text索引。

5.2 创建索引

是否需要创建索引，几点原则：

• 较频繁的作为查询条件的字段应该创建索引；
• 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件；
• 更新非常频繁的字段不适合创建索引；
• 不会出现在WHERE子句中的字段不该创建索引；

索引能够极大的提高数据检索效率，也能够改善排序分组操作的性能，但是我们不能忽略的一个问题就是索引是完全独立于基础数据之外的一部分数据，更新数据会带来的IO量和调整索引所致的计算量的资源消耗。

5.3 使用索引

1. 使用联合索引的查询：MySQL可以为多个字段创建索引，一个索引可以包括16个字段。对于联合索引，只有查询条件中使用了这些字段中第一个字段时，索引才会生效。
2. 使用OR关键字的查询：查询语句的查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，索引才会生效，否则，索引不生效。

6. 存储优化

存储数据时，影响存储速度的主要是索引、唯一性校验、一次存储的数据条数等。

存储数据的优化，不同的存储引擎优化手段不一样，在MySQL中常用的存储引擎有，MyISAM和InnoDB，两者的区别。

6.1 存储引擎介绍

MyISAM存储引擎

MyISAM存储引擎是一种非事务性的引擎，提供高速存储和检索，以及全文搜索能力，适合数据仓库等查询频繁的应用。

每一个表都被存放为三个以表名命名的物理文件。有存放表结构定义信息的.frm文件，还有存放了表的数据.MYD文件和存放索引数据的.MYI文件。

Innodb 存储引擎

Innodb 存储引擎是事务安全的, 因此如果需要一个事务安全的存储引擎,建议使用它。如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑应该使用InnoDB表。

InnoDB 给 MySQL 提供了具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。InnoDB 提供了行锁(locking on row level)，提供与 Oracle 类型一致的不加锁读取(non-locking read in SELECTs)。这些特性均提高了多用户并发操作的性能表现。

在InnoDB表中不需要扩大锁定(lock escalation)，因为 InnoDB 的列锁定(row level locks)适宜非常小的空间。InnoDB 是 MySQL 上提供外键约束(FOREIGN KEY constraints)的表引擎。
InnoDB 的设计目标是处理大容量数据库系统，它的 CPU 利用率是其它基于磁盘的关系数据库引擎所不能比的。在技术上，InnoDB 是一套放在 MySQL 后台的完整数据库系统，InnoDB 在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

InnoDB 把数据和索引存放在表空间里，可能包含多个文件，这与MyISAM不一样。InnoDB 表的大小只受限于操作系统的文件大小，一般为 2 GB。InnoDB所有的表都保存在同一个数据文件 ibdata1 中(也可能是多个文件，或者是独立的表空间文件),相对来说比较不好备份。备份的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump。

6.2 MyISAM和Innodb的区别

InnoDB和MyISAM是许多人在使用MySQL时最常用的两个表类型，这两个表类型各有优劣，视具体应用而定。基本的差别为：MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持。MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快，但是不提供事务支持，而InnoDB提供事务支持已经外部键等高级数据库功能。

具体实现的差别：

• MyISAM是非事务安全型的，而InnoDB是事务安全型的。
• MyISAM锁的粒度是表级，而InnoDB支持行级锁定。
• MyISAM支持全文类型索引，而InnoDB不支持全文索引。
• MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM。
• MyISAM表是保存成文件的形式，在跨平台的数据转移中使用MyISAM存储会省去不少的麻烦。
• InnoDB表比MyISAM表更安全，可以在保证数据不会丢失的情况下，切换非事务表到事务表（alter table tablename type=innodb）。

7.数据库结构优化

7.1 优化表结构

优化表结构的常用思路：

• 尽量将表字段定义为NOT NULL约束，这时由于在MySQL中含有空值的列很难进行查询优化，NULL值会使索引以及索引的统计信息变得很复杂。
• 对于只包含特定类型的字段，可以使用enum、set 等符合数据类型。
• 数值型字段的比较比字符串的比较效率高得多，字段类型尽量使用最小、最简单的数据类型。例如P地址可以使用int类型。
• 尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT，如果非负则加上UNSIGNED VARCHAR的长度只分配真正需要的空间。
• 尽量使用TIMESTAMP而非DATETIME。
• 单表不要有太多字段，建议在20以内。
• 合理的加入冗余字段可以提高查询速度。

7.2 表拆分

垂直拆分

垂直拆分按照字段进行拆分，其实就是把组成一行的多个列分开放到不同的表中，这些表具有不同的结构，拆分后的表具有更少的列。例如用户表中的一些字段可能经常访问，可以把这些字段放进一张表里。另外一些不经常使用的信息就可以放进另外一张表里。

插入的时候使用事务，也可以保证两表的数据一致。缺点也很明显，由于拆分出来的两张表存在一对一的关系，需要使用冗余字段，而且需要join操作，我们在使用的时候可以分别取两次，这样的来说既可以避免join操作，又可以提高效率。

水平拆分

水平拆分按照行进行拆分，常见的就是分库分表。以用户表为例，可以取用户ID，然后对ID取10的余数，将用户均匀的分配进这 0-9这10个表中。查找的时候也按照这种规则，又快又方便。

有些表业务关联比较强，那么可以使用按时间划分的。例如每天的数据量很大，需要每天新建一张表。这种业务类型就是需要高速插入，但是对于查询的效率不太关心。表越大，插入数据所需要索引维护的时间也就越长。

7.3 分区

使用分区是大数据处理后的产物。比如系统用户的注册推广等等，会产生海量的日志，当然也可以按照时间水平拆分，建立多张表。但在实际操作中，容易发生忘记切换表导致数据错误。

分区适用于例如日志记录，查询少。一般用于后台的数据报表分析。对于这些数据汇总需求，需要很多日志表去做数据聚合，我们能够容忍1s到2s的延迟，只要数据准确能够满足需求就可以。

MySQL主要支持4种模式的分区：range分区、list预定义列表分区，hash 分区，key键值分区。

7.4 读写分离

大型网站会有大量的并发访问，如果还是传统的数据结构，或者只是单单靠一台服务器扛，如此多的数据库连接操作，数据库必然会崩溃，数据丢失的话，后果更是不堪设想。这时候，我们需要考虑如何减少数据库的联接。

我们发现一般情况对数据库而言都是“读多写少”，也就说对数据库读取数据的压力比较大，这样分析可以采用数据库集群的方案。其中一个是主库，负责写入数据，我们称为写库；其它都是从库，负责读取数据，我们称为读库。这样可以缓解一台服务器的访问压力。

7.5 数据库集群

如果访问量非常大，虽然使用读写分离能够缓解压力，但是一旦写操作一台服务器都不能承受了，这个时候我们就需要考虑使用多台服务器实现写操作。

例如可以使用MyCat搭建MySql集群，对ID求3的余数，这样可以把数据分别存放到3台不同的服务器上，由MyCat负责维护集群节点的使用。

8. 硬件优化

是服务器的硬件性能直接决定着MySQL数据库的性能，硬件的性能瓶颈，直接决定MySQL数据库的运行速度和效率。

可以从以下几个方面考虑：

1. 配置较大的内存。足够大的内存，是提高MySQL数据库性能的方法之一。内存的IO比硬盘快的多，可以增加系统的缓冲区容量，使数据在内存停留的时间更长，以减少磁盘的IO。
2. 磁盘I/O相关：
   • 使用SSD或者PCIe SSD设备，至少获得数百倍甚至万倍的IOPS提升；
   • 购置阵列卡同时配备CACHE及BBU模块，可明显提升IOPS
   • 尽可能选用RAID-10，而非RAID-5
   • 使用机械盘的话，尽可能选择高转速的，例如选用15000RPM，而不是7200RPM的盘
3. 配置CPU相关，在服务器的BIOS设置中，可调整下面的几个配置：
   • 选择Performance Per Watt Optimized(DAPC)模式，发挥CPU最大性能；
   • 关闭C1E和C States等选项，提升CPU效率；
   • Memory Frequency（内存频率）选择Maximum Performance；

9. MySQL缓存

为了提高查询速度，我们可以通过不同的方式去缓存我们的结果从而提高响应效率。当我们的数据库打开了Query Cache（简称QC）功能后，数据库在执行SELECT语句时，会将其结果放到QC中，当下一次处理同样的SELECT请求时，数据库就会从QC取得结果，而不需要去数据表中查询。如果缓存命中率非常高的话，有测试表明在极端情况下可以提高效率238%。

但一个缓存机制是否有效，效果如何，却是一个需要好好思考的问题。Query Cache有如下规则，如果数据表被更改，那么和这个数据表相关的全部Cache全部都会无效，并删除之。这里“数据表更改”包括: INSERT, UPDATE, DELETE, TRUNCATE, ALTER TABLE, DROP TABLE, or DROP DATABASE等。

举个例子，如果数据表item访问频繁，那么意味着它的很多数据会被QC缓存起来，但是每一次item数据表的更新，无论更新是不是影响到了cache 的数据，都会将全部和item表相关的cache清除。如果你的数据表更新频繁的话，那么Query Cache将会成为系统的负担。有实验表明，糟糕时，QC会降低系统13%的处理能力。

缓存又分为全局缓存和局部缓存。这个比较复杂，这里就不做展开了。

参考

1. https://blog.csdn.net/qq_36541478/article/details/84206512