SQL和索引优化总结

首先我们需要知道MySQL主要是从以下3个方面进行优化：

• SQL语句和索引
• 应用优化（引入缓存、连接池）
• 配置参数优化

一、SQL语句和索引的优化

当数据量比较大，若SQL语句写的不合适，会导致SQL的执行效率低，我们需要等待很长时间才能拿到结果

针对性优化的时候，

explain分析的时候可能出现以下问题：

• 用，导致没用到索引

• 的大小表设置不合理，导致索引没用上（小表是整表查询，）

• 多表查询不用in（产生中间表），用的过程，合理使用索引

二、应用优化

除了优化SQL和索引，在实际生产环境中，由于数据库服务器本身的性能局限，就必须要对上层的应用来进行一些优化，使得上层应用访问数据库的压力能够减到最小

1. 引入数据库连接池；防止客户端不断，耗费网络以及服务器资源，我们可以引入数据库连接池，这是高并发场景下常用的一种优化手段（需要设置初始连接量，最大连接量以及最大空闲时间等参数）
2. 引入缓存；用于，如果客户端的请求来了，先在redis上查一下（redis是基于内存的数据库），如果redis上直接查到就不经过MySQL数据库，如果没有查到就去访问MySQL数据库，访问MySQL完成后，先把当前访问的数据往redis上缓存一下，再把结果返回给用户

引入redis缓存的话，也会有一些附带的问题：等等

三、MySQL Server优化

对于MySQL Server端的优化，主要指的是MySQL Server启动时加载的配置文件（my.ini或my.cnf）中配置项的优化

自适应哈希索引

[InnoDB自适应哈希索引 | 张威的编程学习笔记 (gitee.io)](https://iszhwei.gitee.io/mysql/04 MYSQL索引/InnoDB自适应哈希索引.html)

由于hash索引的生成和维护也是耗费性能的，通过以下命令查看自适应哈希索引搜索的频率低于使用二级索引树搜索的频率：

show engine innodb status\G

如果使用，可以通过变量innodb_adaptive_hash_index关闭自适应哈希索引

redo log

可以根据物理机的条件，，Innodb_buffer_pool_size（缓存的大小），来减少磁盘I/O次数，因为缓存区大了，在缓冲区工作的时间就长了，redo log的效率就高了

my.cnf配置参数如下：

MySQL查询缓存

MySQL的查询缓存是把上一次select的查询结果记录下来放在缓存当中，下一次再查询相同内容的时候，直接从缓存中取出来就可以了，不用再进行一遍真正的SQL查询（在内存中划分一块空间用做缓存的地方）

查询缓存适用于更新不频繁的表，查询频繁的表，因为

MySQL5.7 支持查询缓存

可以通过以下命令，来查看查询缓存的设置：

如果某个表的查询多而更改少，可以考虑开启查询缓存

通过show status命令，可以查看MySQL查询缓存的使用状况，如下：

可以通过set命令设置上面的缓存参数开启MySQL查询缓存功能，也可以找到MySQL的配置文件（windows是my.ini，linux是my.cnf），修改query_cache_type参数为1就可以了，然后重启MySQL Server就可以使用了，如下：

，如果我们引入redis的话，可以通过redis提供的API精确控制要缓存的数据，以及不缓存的数据

MySQL8.0 不支持查询缓存

索引和数据缓存

主要指的就是innodb_buffer_pool_size配置项，从名字上就能看到，该配置项是针对InnoDB存储引擎起作用的，这个参数定义了InnoDB存储引擎的表数据和索引数据的最大内存缓冲区大小。innodb_buffer_pool_size是同时为数据块和索引块做缓存，这个值设的越高，访问表中数据需要的磁盘I/O就越少

innodb_buffer_pool_size = 402653184  -- 384M

MySQL线程缓存

主要指配置文件中thread_cache_size配置项

MySQL Server网络模块采用经典的，之所以引入线程池，主要就是为了在业务执行的过程中，不会因为临时创建和销毁线程，造成系统性能降低，因为线程的创建和销毁是很耗费性能的

配置完thread_cache_size，

并发连接数量和超时时间

MySQL Server作为一个服务器，可以设置客户端的**和**，如果数据库连接统计数量比较大，这两个参数的值需要设置大一些

在配置文件（my.cnf或my.ini）最下面，添加配置：max_connections=1000，然后

MySQL Server对于长时间未通信的连接，会主动关闭连接。设置超时时间，超过设置时间，单位是秒，在配置文件中添加配置：wait_timeout = 600

> wait_timeout：服务器在关闭非交互式连接之前等待活动的秒数（空闲连接存活时间）

> connect_timeout：mysqld服务器在响应错误握手之前等待连接数据包的秒数（连接超时时间）

例子

>如果wait_timeout为1800(30分钟),如果连接空闲,则DB连接将在30分钟内关闭(由进程列表的命令列中的Sleep指示). >如果connect_timeout是10,那么如果您在10秒内无法登录(验证),mysqld将拒绝您.