MySQL SQL和索引优化介绍
2022/04/20 15:52:57
一、SQL语句和索引的优化
当数据量比较大，若SQL语句写的不合适，会导致SQL的执行效率低，我们需要等待很长时间才能拿到结果
针对性优化的时候，若数据量太大，可通过limit分页
explain分析的时候可能出现以下问题：
没有用索引
用多列索引没有用到第一列，导致没用到索引
联合查询的大小表设置不合理，导致索引没用上（小表是整表查询，大表才用索引）
多表查询不用in（产生中间表），用外连接替代带in子查询的过程，合理使用索引
二、应用优化
除了优化SQL和索引，在实际生产环境中，由于数据库服务器本身的性能局限，就必须要对上层的应用来进行一些优化，使得上层应用访问数据库的压力能够减到最小
引入数据库连接池；防止客户端不断三次握手建立连接，四次挥手关闭连接，耗费网络以及服务器资源，我们可以引入数据库连接池，这是高并发场景下常用的一种优化手段
引入缓存；用于存储热点数据，如果客户端的请求来了，先在redis上查一下（redis是基于内存的数据库），如果redis上直接查到就不经过MySQL数据库，如果没有查到就去访问MySQL数据库，访问MySQL完成后，先把当前访问的数据往redis上缓存一下，再把结果返回给用户
引入redis缓存的话，也会有一些附带的问题：缓存数据一致性问题，缓存穿透和缓存雪崩等等
三、MySQL Server优化
对于MySQL Server端的优化，主要指的是MySQL Server启动时加载的配置文件（my.ini或my.cnf）中配置项的优化
1. 自适应哈希索引
由于hash索引的生成和维护也是耗费性能的，通过以下命令查看自适应哈希索引搜索的频率低于使用二级索引树搜索的频率：
show engine innodb status\G
如果使用自适应哈希索引搜索的频率较低，可以通过变量innodb_adaptive_hash_index关闭自适应哈希索引
2. redo log
可以设置InnoDB log buffer大小（redo log缓存的大小），Innodb_buffer_pool_size（缓存的大小），来减少磁盘I/O次数，因为缓存区大了，在缓冲区工作的时间就长了，redo log的效率就高了。
my.cnf配置参数如下：
在这里插入图片描述
3. MySQL查询缓存
MySQL的查询缓存是把上一次select的查询结果记录下来放在缓存当中，下一次再查询相同内容的时候，直接从缓存中取出来就可以了，不用再进行一遍真正的SQL查询（在内存中划分一块空间用做缓存的地方）
查询缓存适用于更新不频繁的表，查询频繁的表，因为当两个select查询中间出现insert，update，delete语句的时候，查询缓存就会被清空，过多的查询缓存的数据添加和删除，就会影响MySQL的执行效率，可能还不如每次都从磁盘上查询
可以通过以下命令，来查看查询缓存的设置：
如果某个表的查询多而更改少，可以考虑开启查询缓存
通过show status命令，可以查看MySQL查询缓存的使用状况，如下：
可以通过set命令设置上面的缓存参数开启MySQL查询缓存功能，也可以找到MySQL的配置文件（windows是my.ini，linux是my.cnf），修改query_cache_type参数为1就可以了，然后重启MySQL Server就可以使用了，如下：
MySQL查询缓存还是不如使用redis，这是MySQL的查询缓存，我们作为MySQL服务的使用者来说，控制不了MySQL的缓存方式，如果我们引入redis的话，可以通过redis提供的API精确控制要缓存的数据，以及不缓存的数据
4. MySQL线程缓存
主要指配置文件中thread_cache_size配置项。
MySQL Server网络模块采用经典的select I/O复用+线程池模型，之所以引入线程池，主要就是为了在业务执行的过程中，不会因为临时创建和销毁线程，造成系统性能降低，因为线程的创建和销毁是很耗费性能的。
线程池就是在业务使用之前，先创建一组固定数量的线程，等待事件发生，当有SQL请求到达MySQL Server的时候，在线程池中取一个线程来执行该SQL请求就可以了，执行完成后，不销毁线程，而是把线程再归还到线程池中，等待下一次任务的处理（线程池的线程数量随着请求越来越多，是可以动态增加的）
配置完thread_cache_size，重启MySQL Server服务后即可生效
5. 并发连接数量和超时时间MySQL Server作为一个服务器，可以设置客户端的最大连接量和连接超时时间，如果数据库连接统计数量比较大，这两个参数的值需要设置大一些
在配置文件（my.cnf或my.ini）最下面，添加配置：max_connections=1000，然后重启MySQLServer，配置生效
MySQL Server对于长时间未通信的连接，会主动关闭连接。设置超时时间，超过设置时间没有请求就主动断开，单位是秒，在配置文件中添加配置：wait_timeout = 600
当数据量比较大，若SQL语句写的不合适，会导致SQL的执行效率低，我们需要等待很长时间才能拿到结果
针对性优化的时候，若数据量太大，可通过limit分页
explain分析的时候可能出现以下问题：
没有用索引
用多列索引没有用到第一列，导致没用到索引
联合查询的大小表设置不合理，导致索引没用上（小表是整表查询，大表才用索引）
多表查询不用in（产生中间表），用外连接替代带in子查询的过程，合理使用索引
二、应用优化
除了优化SQL和索引，在实际生产环境中，由于数据库服务器本身的性能局限，就必须要对上层的应用来进行一些优化，使得上层应用访问数据库的压力能够减到最小
引入数据库连接池；防止客户端不断三次握手建立连接，四次挥手关闭连接，耗费网络以及服务器资源，我们可以引入数据库连接池，这是高并发场景下常用的一种优化手段
引入缓存；用于存储热点数据，如果客户端的请求来了，先在redis上查一下（redis是基于内存的数据库），如果redis上直接查到就不经过MySQL数据库，如果没有查到就去访问MySQL数据库，访问MySQL完成后，先把当前访问的数据往redis上缓存一下，再把结果返回给用户
引入redis缓存的话，也会有一些附带的问题：缓存数据一致性问题，缓存穿透和缓存雪崩等等
三、MySQL Server优化
对于MySQL Server端的优化，主要指的是MySQL Server启动时加载的配置文件（my.ini或my.cnf）中配置项的优化
1. 自适应哈希索引
由于hash索引的生成和维护也是耗费性能的，通过以下命令查看自适应哈希索引搜索的频率低于使用二级索引树搜索的频率：
show engine innodb status\G
如果使用自适应哈希索引搜索的频率较低，可以通过变量innodb_adaptive_hash_index关闭自适应哈希索引
2. redo log
可以设置InnoDB log buffer大小（redo log缓存的大小），Innodb_buffer_pool_size（缓存的大小），来减少磁盘I/O次数，因为缓存区大了，在缓冲区工作的时间就长了，redo log的效率就高了。
my.cnf配置参数如下：
在这里插入图片描述
3. MySQL查询缓存
MySQL的查询缓存是把上一次select的查询结果记录下来放在缓存当中，下一次再查询相同内容的时候，直接从缓存中取出来就可以了，不用再进行一遍真正的SQL查询（在内存中划分一块空间用做缓存的地方）
查询缓存适用于更新不频繁的表，查询频繁的表，因为当两个select查询中间出现insert，update，delete语句的时候，查询缓存就会被清空，过多的查询缓存的数据添加和删除，就会影响MySQL的执行效率，可能还不如每次都从磁盘上查询
可以通过以下命令，来查看查询缓存的设置：
如果某个表的查询多而更改少，可以考虑开启查询缓存
通过show status命令，可以查看MySQL查询缓存的使用状况，如下：
可以通过set命令设置上面的缓存参数开启MySQL查询缓存功能，也可以找到MySQL的配置文件（windows是my.ini，linux是my.cnf），修改query_cache_type参数为1就可以了，然后重启MySQL Server就可以使用了，如下：
MySQL查询缓存还是不如使用redis，这是MySQL的查询缓存，我们作为MySQL服务的使用者来说，控制不了MySQL的缓存方式，如果我们引入redis的话，可以通过redis提供的API精确控制要缓存的数据，以及不缓存的数据
4. MySQL线程缓存
主要指配置文件中thread_cache_size配置项。
MySQL Server网络模块采用经典的select I/O复用+线程池模型，之所以引入线程池，主要就是为了在业务执行的过程中，不会因为临时创建和销毁线程，造成系统性能降低，因为线程的创建和销毁是很耗费性能的。
线程池就是在业务使用之前，先创建一组固定数量的线程，等待事件发生，当有SQL请求到达MySQL Server的时候，在线程池中取一个线程来执行该SQL请求就可以了，执行完成后，不销毁线程，而是把线程再归还到线程池中，等待下一次任务的处理（线程池的线程数量随着请求越来越多，是可以动态增加的）
配置完thread_cache_size，重启MySQL Server服务后即可生效
5. 并发连接数量和超时时间MySQL Server作为一个服务器，可以设置客户端的最大连接量和连接超时时间，如果数据库连接统计数量比较大，这两个参数的值需要设置大一些
在配置文件（my.cnf或my.ini）最下面，添加配置：max_connections=1000，然后重启MySQLServer，配置生效
MySQL Server对于长时间未通信的连接，会主动关闭连接。设置超时时间，超过设置时间没有请求就主动断开，单位是秒，在配置文件中添加配置：wait_timeout = 600