MySQL优化

1、优化思路

　　说到数据库性能优化，一般情况下是为了让查询速度更快。一个查询的动作由很多个环节组成，每个环节都会消耗时间，要提高查询效率，就要从每一个环节入手。

　　　                                         　

 

2、连接——配置优化

　　第一个环节是客户端连接到服务端，连接可能出现什么样的性能问题呢？有可能是服务端连接数不够导致应用程序获取不到连接，比如报了一个Mysql：error 1040：Too many connections 的错误。

　　可以从两个方面来解决连接数不够的问题：

（1）从服务端来说，可以增加服务端的可用连接数。

　　1）修改配置参数增加可用连接数，修改max_connections的大小：

show variables like 'max_connections'; -- 修改最大连接数，当有多个应用连接的时候

　　2）或者及时释放不活跃的连接。交互式和非交互式的客户端的默认超时时间都是28800秒，8小时，可以把这个值调小。

show global variables like 'wait_timeout'; --及时释放不活动的连接，注意不要释放连接池还在使用的连接

（2）从客户端来说，可以减少从服务端获取的连接数，即不是每一次执行SQL都创建一个新的连接。这时可以引入连接池，实现连接的重用。

　　可以在哪些层面使用连接池呢？

　　　　1）ORM层面（mybatis自带了一个连接池）

　　　　2）使用专用的连接池工具（阿里的Druid、spring boot 2.x版本默认的连接池Hikari、老牌的DBCP和C3P0）

　　当客户端改成从连接池获取连接之后，连接池的大小应该怎么设置呢？

　　　　并不是连接池最大连接数越大越好，有时反而连接池越大，效率越低。比如Druid默认最大连接数为8，Hikari默认最大连接数是10，而且Hikari官方建议的设置连接池大小为：机器核数乘以2加1，也就是4核机器，连接池维护9个连接就够了。

　　　　之所以连接池大小与CPU核数有关，是因为每一个连接，服务端都要创建一个线程去处理它。连接数越多，服务端创建的线程就越多，CPU通过分配时间片，上下文切换去执行线程，所以线程过多会造成频繁的上下文切换，反而会造成较大的性能开销。

3、架构优化

3.1、缓存

　　使用缓存来减少数据库的读写压力，比如redis。

3.2、主从复制——读写分离

　　单台数据库服务满足不了访问需求，可以做数据库的集群。集群的话就会面临数据一致性问题，也就有了主从复制。被复制的节点成为master，复制的节点成为slave，slave本身也可以作为其他节点的数据来源，叫做级联复制。

　　主从复制通过binlog实现，更新语句会记录binlog，从服务器获取主服务器的binlog文件，然后解析里面的SQL语句，在从服务器上执行一遍，以此保持主从数据一致。其中涉及到三个线程：

　　　　1）master上的log dump线程，用来发送binlog给slave。

　　　　2）slave上的I/O线程，连接到master获取binlog，并解析binlog写入中继日志。

　　　　3）slave上的SQL线程，用来读取中继日志（relay log），把数据写入到数据库。

　　　　　　　　

 

　　做了主从复制之后，只把数据写入master节点，而读的请求可以分担到slave节点，这就叫读写分离。但是如果使用读写分离，在master写入之后，马上到slave上查询，数据可能还没同步过来，所以需要知道主从复制慢的原因。

（1）单线程

　　在早期的MySQL中，slave的SQL线程是单线程。master可以支持SQL语句的并行执行，配置了多少的最大连接数就是最多同时多少个SQL并行执行。而slave的SQL却只能单线程排队执行，在主库并发量很大的情况下，同步数据肯定会出现延迟。

（2）异步与全同步

　　在主从复制的过程中，MySQL默认是异步复制的。也就是说，对于主节点来说，写入binlog，事务结束，就返回给客户端了。对于slave来说，接收到binlog，就完事儿了，master不关心slave的数据有没有写入成功。要减少主从数据同步的延迟，需要等待全部从库的事务执行完毕，主库才返回给客户端，这种方式叫全同步复制。全同步虽然保证主从数据的一致，但是带来的影响就是事务执行时间会变长，导致master节点性能下降。

（3）半同步复制

　　介于异步复制和全同步复制之间，还有一种半同步复制的方式。主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到binlog并写到relaylog中才返回给客户端。master不会等待很长的时间，但是返回给客户端的时候，数据就即将写入成功了，因为它只剩最后一步了：就是读取relaylog，写入从库。（使用半同步复制需要在主库和从库安装不同的插件）

　　相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，它需要等待一个slave写入中继日志，这里多了一个网络交互的过程，所以，半同步复制最好在低延时的网络中使用，但是相比全同步效率就高了很多。这个是从主库和从库连接的角度，来保证slave数据的写入。

（4）异步复制之GTID复制

　　除了从主库和从库连接角度入手，还可以通过让从库上多个SQL语句并行执行，而不是排队执行，减少SQL执行造成的等待时间。把那些在主库上并行执行的事务，分为一个组，并且给他们编号，这一个组的事务在从库上面也可以并行执行。这个编号，把它叫做 GTID（Global Transaction Identifiers），这种主从复制的方式，叫做基于GTID的复制。

 　　　　　　　　　　　　

　　GTID复制，可以通过修改配置参数打开它，默认是关闭的：show global variables like 'gtid_mode';

3.3、分库分表

　　主从复制是从数据库的层面去解决数据库的请求压力问题。也可以从应用层面来提升查询效率。如果单个master节点或者单张表的数据过大，也会导致单表的查询性能下降，可以通过分库分表来优化。

　　垂直分库，减少并发压力；水平分表，解决存储瓶颈。

　　分库：如单个库中包含客户相关表、合同相关表、放款相关表、分控相关表，根据业务拆分成不同的库。

　　　　　　　　　　

 

　　分表：把单张表的数据按照一定的规则分布到多个数据库。

　　　　　　　　　　

　　通过主从或者分库分表可以减少单个数据库节点的访问压力和存储压力，达到提升数据库性能的目的。

 4、SQL语句分析与优化

4.1、慢查询日志

（1）打开慢日志开关

　　开启慢查询日志是有代价的（跟binlog、optimizer-trace一样），默认是关闭的：show variables like 'slow_query%'。除了开关，还有一个参数控制超过多长时间的SQL才记录到慢日志，默认10秒：

　　动态设置，重启失效：

set @@global.slow_query_log=1; --1 开启，0 关闭，重启后失效 
set @@global.long_query_time=3; -- mysql 默认的慢查询时间是 10 秒，另开一个窗口后才会查到最新值

　　或者修改配置文件my.cnf。以下配置定义了慢查询日志的开关、慢查询的时间、日志文件的存放路径。

slow_query_log=ON 
long_query_time=2 
slow_query_log_file=/var/lib/mysql/localhost-slow.log

（2）慢日志分析

　　查看慢日志：cat /var/lib/mysql/localhost-slow.log。还可以使用mysqldumpslow去分析慢日志，如查询用时最多的20条慢SQL：

mysqldumpslow -s t -t 20 g 'select' /var/lib/mysql/localhost-slow.log

　　　　　　

　　Count代表这个SQL执行了多少次；Time代表执行的时间，括号里面是累计时间；Lock表示锁定的时间，括号是累计；Rows表示返回的记录数，括号是累计。

4.2、SHOW  PROFILE

　　除了慢查询日志，还有一个SHOW PROFILE工具可以使用。它可以查看SQL语句执行时使用的资源，如CPU、IO的消耗情况。

（1）开启profile

select@@profiling; 
set@@profiling=1;

（2）查看 profile 统计 

　　show profiles：

　　　　　　

　　也可以根据ID查看执行详细信息，在后面带上for query + ID：

show profile for query 1;

4.3、其他系统命令

（1）show processlist 运行线程

　　用于显示用户运行线程。可以根据id号kill线程。也可以查表，效果一样：select * from information_schema.processlist。

　　　　

 

（2）showstatus 服务器运行状态

　　用于查看 MySQL 服务器运行状态（重启后会清空），有 session和global两种作用域，格式：参数-值。可以用like带通配符过滤。

SHOW GLOBAL STATUS LIKE'com_select';-- 查看 select 次数

（3）showengine 存储引擎运行信息

　　用来显示存储引擎的当前运行信息，包括事务持有的表锁、行锁信息；事务的锁等待情况；线程信号量等待；文件IO请求；buffer pool统计信息。

show engine innodb status;

4.4、EXPLAIN 执行计划

　　通过以上方法可以找到哪些SQL慢了，但是为什么慢，可以通过EXPLAIN以模拟优化器执行SQL 查询语句的过程，从而进一步分析语句或者表的性能瓶颈。如下：

　　　　　　

 

　　参数含义：

（1）selecttype 查询类型 

　　1）SIMPLE  简单查询，不包含子查询和关联查询（union）。

　　2）PRIMARY  子查询SQL语句中的主查询，也就是最外面的那层查询。

　　3）SUBQUERY  子查询中所有的内层查询都是该类型。

　　4）DERIVED  衍生查询，表示在得到最终查询结果之前会用到临时表。

　　5）UNION  用到了UNION查询。

（2）type 连接类型

　　从左到右，性能越来越差。常用的连接类型：system > const > eq_ref > ref > range > index > all，除了all，其他都能用到索引。

　　　　1）const  主键索引或者唯一索引，只能查到一条数据的SQL。

　　　　2）system  是const的一种特例，只有一行满足条件，例如：只有一条数据的系统表。

　　　　3）eq_ref  通常出现在多表的 join 查询，表示对于前表的每一个结果,，都只能匹配到后表的一行结果。一般是唯一性索引的查询。

　　　　4）ref  查询用到了非唯一性索引，或者关联操作只使用了索引的最左前缀。

　　　　5）range  索引范围扫描。如：where后跟的范围条件。

　　　　6）index  Full Index Scan，查询全部索引中的数据（比不走索引要快）。

　　　　7）all  FullTableScan，如果没有索引或者没有用到索引，type就是ALL。代表全表扫描。

　　一般来说，需要保证查询至少达到range级别，最好能达到ref，all（全表扫描）和index（查询全部索引）都是需要优化的。

（3）possible_key、key

　　可能用到的索引和实际用到的索引。如果是NULL就代表没有用到索引。

（4）key_len 

　　索引的长度（使用的字节数）。跟索引字段的类型、长度有关。 

（5）rows

　　MySQL认为扫描多少行才能返回请求的数据，是一个预估值。一般来说行数越少越好。

（6）filtered 

　　这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，它是一个百分比。

（7）ref 

　　使用哪个列或者常数和索引一起从表中筛选数据。 

（8）extra 

　　执行计划给出的额外的信息说明。类型如下：

　　　　1）using where

　　　　　　使用了where过滤，表示存储引擎返回的记录并不是所有的都满足查询条件，需要在server层进行过滤（跟是否使用索引没有关系）。

　　　　2）using index condition（索引条件下推）

　　　　3）using filesort

　　　　　　不能使用索引来排序，用到了额外的排序（跟磁盘或文件没有关系）。需要优化。

　　　　4）using temporary

　　　　　　用到了临时表。如：distinct 非索引列；group by 非索引列；使用join的时候，group任意列，这些情况都会用到临时表。

5、存储引擎

　　为不同的业务表选择不同的存储引擎，例如：查询插入操作多的业务表，用MyISAM。临时数据用Memeroy。常规的并发大更新多的表用InnoDB。

　　字段定义原则：使用可以正确存储数据的最小数据类型。为每一列选择合适的字段类型。

　　1）字符类型

　　　　变长情况下，varchar更节省空间，但是对于varchar字段，需要一个字节来记录长度。固定长度的用char，不要用varchar。

　　2）非空

　　　　非空字段尽量定义成NOT NULL，提供默认值，或者使用特殊值、空串代替null。NULL类型的存储、优化、使用都会存在问题。

　　3）不要用外键、触发器、视图

　　　　降低了可读性；

　　　　影响数据库性能，应该把把计算的事情交给程序，数据库专心做存储；

　　　　数据的完整性应该在程序中检查。

　　4）大文件存储

　　　　不要用数据库存储图片（比如base64编码）或者大文件；把文件放在NAS 上，数据库只需要存储URI（相对路径），在应用中配置 NAS服务器地址