MySQL优化

陈岂几真幸运

已于 2023-06-18 12:25:19 修改

阅读量581

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 sql

于 2023-05-07 22:23:31 首次发布

本文链接：https://blog.csdn.net/super8ayan/article/details/130548309

版权

MySQL 专栏收录该内容

18 篇文章 0 订阅

订阅专栏

MySQL需要优化的地方有哪些？

1. SQL和索引的优化

慢查询日志=》设置慢查询时间=》记录慢查询sql=》explain分析sql执行计划=》优化措施

常见的一些索引设计方法：

        经常作为where条件过滤的字段考虑添加索引；
        字符串列创建索引时，尽量规定索引长度，不能让索引值的长度key_len过长（字符串类型的列包含的字符串长度是不固定的，如果不指定索引长度，MySQL会使用整个列的长度来创建索引，会使索引的长度变得非常大key_len也会变得很长，这会影响索引的效率和使用，甚至会导致索引失效。）
        索引字段涉及类型强制转换、mysql函数调用，表达式计算等索引失效。

索引的失效以及常见优化方法：
(28条消息) Mysql索引优化的方法_陈岂几真幸运的博客-CSDN博客

2.应用上的优化

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

增加数据库连接池：

        应用上一般访问数据库，都是先和MySQL Server创建连接，然后发送SQL语句，Server处理完成后，再把结果通过网络返回给应用，然后关闭和MySQL Server的连接，因此短时间大量的数据库访问，消耗的TCP三次握手和四次挥手所花费的时间就很大了，稍微大一点的项目，我们都会在应用访问数据库的那一层上，添加连接池模块，相当于应用和MySQL Server事先创建一组连接，当应用需要请求 MySQL Server时，不需要再进行TCP连接和释放连接了，一般连接池都会维护以下资源：

        1、连接池里面保持固定数量的活跃TCP连接，供应用使用。

        2、如果应用瞬间访问MySQL的量比较大，那么连接池会实时创建更多的连接给应用使用。

         3、当连接池里面的TCP连接一段时间内没有被用到，连接池会释放多余的连接资源，保留它设置的最大空闲连接量就可以了。连接池可以自己实现，也可以用第三方写好的库。

增加cache缓存层：

业务上增加redis、memcache，一般用缓存把经常访问的数据缓存起来。

3.MySQL Server优化

MySQl查询缓存

查询多更改少，可以开启查询缓存；

把select查询语句上一次的查询结果记录下来放在缓存当中，下一次再查询相同内容的时候，直接从缓存中取出来就可以了，不用再进行一遍真正的SQL查询。但是当两个select查询中间出现insert，update，delete语句的时候，查询缓存就会被清空。查询缓存适用更新不频繁的表，因为当表更新频繁的话，查询缓存也总是被清空，过多的查询缓存的数据添加和删除，就会影响MySQL的执行效率，还不如每次都从磁盘上查来得快（缓存指的就是一块内存，内存I/O比磁盘I/O快很多）。

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

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

索引和数据缓存

redo log

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

innodb_buffer_pool_size=512M

MySQL线程缓存

主要指配置文件中thread_cache_size配置项。MySQL Server网络模块采用经典的I/O复用 +线程池模型，之所以引入线程池，主要就是为了在业务执行的过程中，不会因为临时创建和销毁线程，造成系统性能降低，因为线程的创建和销毁是很耗费性能的，所以线程池就是在业务使用之前，先创建一组固定数量的线程，等待事件发生，当有SQL请求到达MySQL Server的时候，在线程池中取一个线程来执行该SQL请求就可以了，执行完成后，不销毁线程，而是把线程再归还到线程池中，等待下一次任务的处理（MySQL会根据连接量，自动加大线程池的数量）。