MySQL 性能优化
[TOC]
MySQL 工作原理
MySQL 体系结构
1549913724163
管理工具
MySQL服务软件安装后提供的命令
连接池
检查本机是否有资源处理当前的连接请求 ( 空闲的线程 内存 )
SQL 接口
把 SQL 命令传递给 mysql 服务的进程处理.
分析器
检查执行的SQL命令是否有语法错误,
优化器
优化执行的sql命令,使其能以最节省系统资源的方式执行
查询缓存
查询缓存的存储空间是从系统的物理内存里划分出来的,用来存储查询过的查询结果
存储引擎
软件自带的功能程序,是用来处理表的处理器
文件系统
数据库服务器存储数据的磁盘
MySQL 执行流程
1549913788017
MySQL性能调优思路
提高 MySQL 系统的性能,响应速度
-
替换有问题的硬件 ( CPU/磁盘/内存 等 )
-
服务程序的运行参数调整
-
对 SQL 查询进行优化
运行参数优化
并发及连接控制
连接数,连接超时
| 选项 | 说明 |
|---|---|
| max_connections | 允许的最大并发连接数 |
| connect_timeout | 等待连接超时,默认十秒,仅登录时有效 |
| wait_timeout | 等待关闭连接的不活动超时秒数,默认 28800秒 ( 8小时 ) |
1549910455606
#查看有过的最大连接数
mysql> show global status like "max_used_connections";
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| Max_used_connections | 2 |
+----------------------+-------+
#查看默认的最大连接数
mysql> show variables like "max_connections";
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 151 |
+-----------------+-------+
命令行修改
set [ global ] 变量名=值
mysql> set global max_connections=501;
mysql> show variables like "max_connections";
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 501 |
+-----------------+-------+
永久修改
[mysqld]
max_connections=值
有过的最大连接数量/并发连接数=0.85
Max_used_connections / Max_used_connections=0.85
留百分之十五的空闲
缓存参数控制
缓冲区,线程数量,开表数量
| 选项 | 说明 |
|---|---|
| key_buffer_size | 用于 MyISAM 引擎的关键索引缓存大小 |
| sort_buffer_size | 为每个要排序的线程分配此大小的缓存空间 |
| read_buffer_size | 为顺序读取表记录保留的缓存大小 |
| thread_cache_size | 允许保存在缓存中被重用的线程数量 |
| table_open_cache | 为所有线程缓存的打开的表的数量 |
索引缓存
key_buffer_size = 8M
- 当 Key_reds / Key_read_requests 较低时,可适当加大此缓存值
mysql> show global status like "key_read%";
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Key_read_requests | 6 |
| Key_reads | 3 |
+-------------------+-------+
mysql> show variables like "key_buffer_size";
+-----------------+---------+
| Variable_name | Value |
+-----------------+---------+
| key_buffer_size | 8388608 |
+-----------------+---------+
排序缓存
sort_buffer_size= 256K
- 增大此值可提高 ORDER 和 GROUP的速度
mysql> show variables like "sort_buffer_size";
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| sort_buffer_size | 262144 |
+------------------+--------+
表记录读取缓存
read_buffer_size
- 此表缓存值影响 SQL 查询的响应速度
mysql> show variables like "read_buffer_size";
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| read_buffer_size | 131072 |
+------------------+--------+
查看可重用线程数
thread_cache_size = 9
mysql> show variables like "thread_cache_size";
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| thread_cache_size | 9 |
+-------------------+-------+]
查看当前的线程重用状态
mysql> show global status like "threads_%";
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 0 |
| Threads_connected | 2 |
| Threads_created | 2 |
| Threads_running | 2 |
+-------------------+-------+
查看已打开,打开过多少个表
mysql> show global status like "open%tables";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 104 |
| Opened_tables | 111 |
+---------------+-------+
查看可缓存多少个打开的表
mysql> show variables like "table_open_cache";
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| table_open_cache | 2000 |
+------------------+-------+
1549911531311
显示查询缓存的设置
一般生产环境不开查询缓存,用专门的缓存中间件做查询缓存,例如 memcache redis mogodb
query_cache_type = 0 | 1 | 2
mysql> show variables like "%query_cache%";
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1048576 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+
query_cache_wlock_invalidate off
当对myisam存储引擎的表,查询的时候,若此时有客户端对表执行写操作,Mysql服务不会从缓存里查找数据返回给客户端,而是等写操作完成后,重新从表里查找数据返回给客户端.
显示查询缓存数据信息
mysql> show global status like "qcache%";
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1031832 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 6 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+---------+
SQL 查询优化
程序员编写的SQL 复杂导致处理速度慢.
MySQL 日志类型
| 类型 | 用途 | 配置 |
|---|---|---|
| 错误日志 | 记录启动/运行/停止过程中的错误信息 | log-error[=name] |
| 查询日志 | 记录客户端连接和查询操作 | general-log general-log-file= |
| 慢查询日志 | 记录耗时较长或不使用索引的查询操作 | slow-query-log slow-query-log-file= long-query-time= |
优化 SQL 查询
- 记录慢查询
| 选项 | 说明 |
|---|---|
| slow-query-log | 启用慢查询 |
| slow-query-log-file= | 指定慢查询日志文件 |
| long-query-time= | 超过时间 ( 默认10秒 ) |
| log-queries-not-using-indexes | 记录为使用索引的查询 |
慢查询日志 只记录超过超时时间显示查询结果的sql命令
#开启慢查询日志
[mysqld]
slow-query-log
long-query-time=1
log_queries_not_using_indexes
#重启后 睡眠10秒
mysql> select sleep(10);
#用工具统计满日志信息
mysqldumpslow test1-slow.log #mysqldumpslow 统计慢日志信息
数据架构或者存储问题导致的数据库响应缓慢,考虑调整架构或者更换固态或分布式存储解决.
帮助文档:
mysql配置文件详解
mysql 帮助手册
网友评论