前言

关于mysql索引分析和优化(一) 关于mysql索引分析和优化(二) 大家可以先看看前面几篇文章

应用优化

一般情况下对 mysql 的优化方案很少, 比如分表分库, 然后是集群等等方案, 但这些往往还是无法承受前端请求的巨量并发流量对数据库的摧残, 这里我们就针对这点对数据库进行各种优化方案

使用连接池

在不使用连接池前, 我们的连接被频繁的创建和关闭, 这样效率会非常的慢, 所以一般使用连接池进行操作, 用户访问 mysql 时, 需要拿到一个连接对其进行操作, 这样预先创建一堆线程池方案, 提高的效率是非常的高

减少对 mysql 的访问

尽量一次查询获取自己所需要的数据

在数据库前做一个缓存命中层

数据库的数据大部分缓存在缓存层中, 比如业界使用比较多的 redis 或者 memcached 等方案进行缓存, 每次获取数据前都先去缓存层获取数据, 如果缓存层没有命中数据再去数据库中查询数据, 但这产生了很多问题, 其中就有很多的问题比如: 缓存穿透, 缓存击穿, 缓存雪崩等

缓存穿透: 主要是用户查询的数据 id = -1 等永远无法获取到数据的情况, 这样的问题导致用户的所有请求都跑到了数据库中, 造成数据库压力巨大
解决方案其实也是很简单的

在接口层增加效验, 比如: 用户鉴权, 做 id的基础效验
在缓存中获取不到数据, 在数据库中同样获取不到数据, 此时解决方案很简单, 将查询 sql 做成缓存的 key 然后将 value 设置成 null 并且这条缓存数据 expire 生命周期, 一般使用的时间是 30s 过期

缓存击穿: 主要的问题就是多个用户并发获取同一段数据, 但这段数据缓存中根本没有, 需要去数据库中获取数据, 但此时并发量太大了, 根本无法即使的把数据存储到缓存中, 全给整到数据库中进行了并发查找, 完了, 数据库爆炸

解决方案:

创建热数据永远或者很长很长的一段时间内不会过期
我们可以考虑对数据或者 SQL 的值做为锁, 这样保证了数据不会高并发访问数据库的功能, 每次获取对应的数据只能够有一个线程在获取数据和存储数据, 其他线程只能够阻塞中或者别的方案反馈给用户
当然前面对 key 的值上锁方案其实类似于非乐观锁方案, 可以选择一个标志位, 当一个线程进入时, 标志位为 false 否则恢复为 true, 这样其他线程检测到标志位为 false , 则等待 100ms 然后再次尝试获取数据

缓存雪崩: 主要的问题就是缓存在某段时间内同时失效的情况, 数据库在某段时间内很多存在生命周期的变量失效了, 此时大量请求进入到数据库中, 导致系统宕机, 这种方式和缓存击穿不同, 缓存击穿时在高并发情况下, 所有的请求都在获取同一个数据, 但缓存雪崩的话, 是多个数据不巧的是在某段时间内同时失效了, 导致大量请求又跑到了数据库中

解决方案:

解决方案其实很简单, 变量的生命周期设置随机时长方案, 防止多个数据同时消失
适当提高热数据的生命周期
将数据分布在各个分布式数据缓存中, 以减少用户访问同一个数据导致宕机的情况出现

缓存穿透: 找不到数据, 只能穿透缓存到数据库中

缓存击穿: 一个是高并发, 同时访问一个数据, 恰巧缓存中没有, 跑数据库中访问了, 数据库崩

缓存雪崩: 多个存在生命周期的变量在某个段时间内大量失效, 导致所有失效的请求需要去数据库中申请不同的变量

总结: 找不到数据; 很多人抢数据; 所有的数据都过期了;

现在我做个总结吧

方案其实很简单

设置变量生命周期时间随机化
配置数据库读写分离策略, 做好数据库的 mha高可用方案
将缓存层做成分布式方案, 将不同的数据分布在不同的机器上, 同时每台机器设置从机, 以保证结点的稳定, 并做好通知和日志服务系统, 万一出现结点失效了或者分布式集群压力变大的, 能够及时通知维护人员
适当增加变量的生命周期, 对 key 上锁, 或者使用乐观锁方案, 当线程发现已经有线程已经在获取数据了, 直接等待几十ms 等待再次获取
数据库不再直接接受数据的访问, 在外层缓存层中设置两种定时时钟, 一种基本上几秒获取最新数据一次, 另一个时钟几个小时或者1天的时间获取另一部分数据, 这样访问数据库的权限全权交给我们去控制了
继上面的方法后, 我们还可以借助 mq 直接对数据库进行操作, 把上面的缓存层直接访问数据库的权限交出来给 mq 进行操作

Mysql中查询缓存优化

mysql 自己也是有缓存的存在, 可以开启查询缓存功能

具体的 mysql 查询流程

客户端发送一条查询给服务器；
服务器先会检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的结果。否则进入下一阶段；
服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划；
MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询；
将结果返回给客户端。

查询缓存配置

查询当前数据库是否支持缓存

show variables like 'have_query_cache';

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | NO    |
+------------------+-------+

mysql8.0不支持, 主要是官方推荐在服务端查询重写或者使用 Proxy 作为中间人缓存所以我再切换 mysql 5.7 {Server version: 5.7.28 MySQL Community Server (GPL)}

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+

查询当前 mysql 是否开启了查询缓存

SHOW VARIABLES LIKE 'query_cache_type';

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | OFF   |
+------------------+-------+

查看查询缓存的占用大小: SHOW VARIABLES LIKE 'query_cache_size';

+------------------+---------+
| Variable_name    | Value   |
+------------------+---------+
| query_cache_size | 1048576 |
+------------------+---------+

查看查询缓存的状态变量

SHOW STATUS LIKE 'Qcache%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 1031872 |
| Qcache_hits             | 0       |
| Qcache_inserts          | 0       |
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 156     |
| Qcache_queries_in_cache | 0       |
| Qcache_total_blocks     | 1       |
+-------------------------+---------+

各个变量的含义:

参数	含义
Qcache_free_blocks	查询缓存中的可用内存块数
Qcache_free_memory	查询缓存的可用内存量
Qcache_hits	查询缓存命中数
Qcache_inserts	添加到查询缓存的查询数
Qcache_lowmen_prunes	由于内存不足而从查询缓存中删除的查询数
Qcache_not_cached	非缓存查询的数量（由于 query_cache_type 设置而无法缓存或未缓存）
Qcache_queries_in_cache	查询缓存中注册的查询数
Qcache_total_blocks	查询缓存中的块总数

开启查询缓存功能

在mysql 的 my.ini 配置文件中加入 query_cache_type=1

[mysql]
default-character-set=utf8
[mysqld]
port = 3306 
basedir=D:\program\env\mysql-5.7.28-winx64
datadir=D:\program\env\mysql-5.7.28-winx64\data
max_connections=200
character-set-server=utf8
default-storage-engine=INNODB
default-time_zone='+8:00'
query_cache_type=1

其中 query_cache_type 中的几种模式:

值	含义
OFF 或 0	查询缓存功能关闭
ON 或 1	查询缓存功能打开，SELECT的结果符合缓存条件即会缓存，否则，不予缓存，显式指定SQL_NO_CACHE，不予缓存
DEMAND 或 2	查询缓存功能按需进行，显式指定 SQL_CACHE 的SELECT语句才会缓存；其它均不予缓存

然后重启 mysql

show variables like 'have_query_cache'; # 查看是否支持查询缓存功能
show variables like 'query_cache_type'; # 查看是否启动查询缓存功能

如果查询类型被社会成 DEMAND 模式, 则需要在 sql 语句中添加 SQL_CACHE 和 SQL_NO_CACHE 来代表是否开启缓存

查询缓存失效的情况

不确定结果函数不走函数

一般情况下 sql 一样的话, 可以从缓存中直接拉取数据, 但是你需要规避掉一些不确定的函数返回结果, 比如 now() rand() 等, 时间函数, 随机函数等, 每次执行都不一样

now() , current_date() , curdate() , curtime() , rand() , uuid() , user() , database()

上面这些都无法触发缓存机制

查询 mysql， information_schema或 performance_schema 数据库中的表时，不会走查询缓存
在存储的函数，触发器或事件的主体内执行的查询。
如果表的结构发生变化或者表中的数据被写入修改删除又或者表直接被删除掉了, 则几种情况都会导致查询缓存失效

mysql 数据存储引擎内存优化参数

这段贼无聊, 当字典就好

对 myISAM 的内存参数优化

key_buffer_size: key_buffer_size决定MyISAM索引块缓存区的大小，直接影响到MyISAM表的存取效率, 一般分配 1/4 的内存给它, 如果你的数据库都是 myISAM 存储引擎

在 my.ini 或者 my.cnf 文件中添加 key_buffer_size=xxxM

read_buffer_size: 如果需要经常顺序扫描myisam表，可以通过增大read_buffer_size的值来改善性能。 read_buffer_size是每个session独占的，如果默认值设置太大，就会造成内存浪费
read_rnd_buffer_size: 对于需要做排序的myisam表的查询，如带有order by子句的sql，适当增加 read_rnd_buffer_size 的值，可以改善此类的sql性能。但需要注意的是 read_rnd_buffer_size 是每个session独占的，如果默认值设置太大，就会造成内存浪费

InnoDB 内存优化

innodb用一块内存区做IO缓存池，该缓存池不仅用来缓存innodb的索引块，而且也用来缓存innodb的数据块

innodb_buffer_pool_size: 该变量决定了 innodb 存储引擎表数据和索引数据的最大缓存区大小。在保证操作系统及其他程序有足够内存可用的情况下，innodb_buffer_pool_size 的值越大，缓存命中率越高，访问InnoDB表需要的磁盘I/O 就越少，性能也就越高

innodb_buffer_pool_size=xxxM

innodb_log_buffer_size: 决定了innodb重做日志缓存的大小，对于可能产生大量更新记录的大事务，增加innodb_log_buffer_size的大小, 可以避免innodb在事务提交前就执行不必要的日志写入磁盘操作

innodb_log_buffer_size=xxxM

Mysql并发参数调整

从实现上来说，MySQL Server 是多线程结构，包括后台线程和客户服务线程。多线程可以有效利用服务器资源，提高数据库的并发性能。在Mysql中，控制并发连接和线程的主要参数包括 max_connections、back_log、 thread_cache_size、table_open_cahce

max_connections

采用max_connections 控制允许连接到MySQL数据库的最大数量，默认值是 151。如果状态变量 connection_errors_max_connections 不为零，并且一直增长，则说明不断有连接请求因数据库连接数已达到允许最大值而失败，这是可以考虑增大max_connections 的值。

Mysql 最大可支持的连接数，取决于很多因素，包括给定操作系统平台的线程库的质量、内存大小、每个连接的负荷、CPU的处理速度，期望的响应时间等。在Linux 平台下，性能好的服务器，支持 500-1000 个连接不是难事，需要根据服务器性能进行评估设定

back_log

back_log 参数控制MySQL监听TCP端口时设置的积压请求栈大小。如果MySql的连接数达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过 back_log，将不被授予连接资源，将会报错。5.6.6 版本之前默认值为 50 ，之后的版本默认为 50 + （max_connections / 5），但最大不超过900。如果需要数据库在较短的时间内处理大量连接请求，可以考虑适当增大back_log 的值

table_open_cache

该参数用来控制所有SQL语句执行线程可打开表缓存的数量，而在执行SQL语句时，每一个SQL执行线程至少要打开 1 个表缓存。该参数的值应该根据设置的最大连接数 max_connections 以及每个连接执行关联查询中涉及的表的最大数量来设定： max_connections x N

thread_cache_size

为了加快连接数据库的速度，MySQL 会缓存一定数量的客户服务线程以备重用，通过参数 thread_cache_size 可控制 MySQL 缓存客户服务线程的数量

innodb_lock_wait_timeout

该参数是用来设置InnoDB 事务等待行锁的时间，默认值是50ms ，可以根据需要进行动态设置。对于需要快速反馈的业务系统来说，可以将行锁的等待时间调小，以避免事务长时间挂起；对于后台运行的批量处理程序来说，可以将行锁的等待时间调大，以避免发生大的回滚操作。

关于mysql索引分析和优化(三)

前言