mysql数据库优化之数据库非sql优化

最新推荐文章于 2024-09-22 11:17:00 发布

傻鱼爱编程

最新推荐文章于 2024-09-22 11:17:00 发布

阅读量378

点赞数

本文链接：https://blog.csdn.net/m0_57640408/article/details/126554518

版权

一、数据库应用优化

在实际生产环境中，我们sql优化已经完成，由于数据库本身的性能局限，就必须要对数据库的应用进行一些优化，来降低数据库的访问压力。

1. 减少对数据库的访问次数

1.1 增加cache层缓存，使用数据库的一级缓存/二级缓存，或者使用redis数据库来缓存数据。

1.2 获取需要不同的字段信息，能用一条sql就合并成一条sql;

比如： select id , name from student;

select id , age from student;

合并后：select id, name , age from student;

1.3 不要在代码循环中操作数据库

2. 对数据库进行集群提供服务

利用多台服务，实现数据库的主从复制，实现读写分离。还可以使用分库分表提高效率（单表500万或者大小超过2G）。

二、数据库查询缓存优化

开启Mysql的查询缓存，当执行完全相同的SQL语句的时候，服务器就会直接从缓存中读取结果，当数据被修改，之前的缓存会失效，修改比较频繁的表不适合做查询缓存。

sql运行流程如下：

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

修改缓存配置，开启支持缓存

1. 查看当前的MySQL数据库是否支持查询缓存：
SHOW VARIABLES LIKE 'have_query_cache';

2. 查看当前MySQL是否开启了查询缓存：
SHOW VARIABLES LIKE 'query_cache_type';

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

4. 查看查询缓存的状态变量：
SHOW STATUS LIKE 'Qcache%';

各个变量的含义如下：

参数	含义
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的查询缓存默认是关闭的，需要手动配置参数 query_cache_type ，来开启查询缓存。query_cache_type 该参数的可取值有三个：

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

在 /usr/my.cnf 配置中，增加以下配置：

配置完毕之后，重启服务既可生效；

三、内存管理及优化

将尽量多的内存分配给MySQL做缓存。

四、mysql并发参数调整

1. max_connections
Show variables like 'max_connections';

采用max_connections 控制允许连接到MySQL数据库的最大数量，默认值是 100。如果开发中需要更多的连接数，可以增大max_connections 的值。

2. back_log
Show variables like 'back_log';

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

3. table_open_cache
Show variables like 'table_open_cache';

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

4. thread_cache_size
Show variables like 'thread_cache_size';

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

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