MySQL数据库应用实战教程 拓展 MySQL高性能优化
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
MySQL程序设计
第8章 MySQL高性能优化
8.3 慢查询日志分析
慢查询日志分析
# Time: 2018-01-10T14:00:56.345242Z # User@Host: root[root] @ localhost [::1] Id: 20 # Query_time: 0.102006 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 189999 SET timestamp=1515592856; SELECT COUNT(userName) FROM employee WHERE id > 10000 AND id < 200000;
MySQL程序设计
第8章 MySQL高性能优化
8.1 为什么查询慢 8.2 数据准备 8.3 慢查询日志分析 8.4 索引优化
MySQL程序设计
第8章 MySQL高性能优化
8.5 特定类型优化 8.6 数据库表优化 8.7 数据库配置优化 8.8 服务器硬件优化
MySQL程序设计
第8章 MySQL高性能优化
使用show variables like 'long_query_time';来设置多长时间为慢查询；long_query_time：设置慢查询时间， 超过这个时间记录到慢查询日志里。
MySQL程序设计
第8章 MySQL高性能优化
8.3 慢查询日志分析
开启慢查询两种方式
（1）使用命令来开启 将slow_query_log参数值设置为ON来开启慢查询；将slow_query_log_file参数值设置为慢查询日志文件存放目录 （D:/Program Files/MySQL/mysql-5.7.20-winx64/data/slow.log）； （2）修改my.ini配置文件 slow_query_log = ON slow_query_log_file = D:\Program Files\MySQL\mysql-5.7.20-winx64\data\slow.log long_query_time = 1 log_queries_not_using_indexes=ON
8.7 数据库配置优化
其他参数优化
default-character-set：设置MySQL客户端的字符集，通常设置为utf8； character-set-server：设置MySQL数据库的字符集，通常设置为utf8; default-storage-engine：MySQL数据库默认使用的存储引擎; innodb_read_io_threads/innodb_write_io_threads：Innodb存储引擎读写IO的线程数，默认值为4，可以 适当提高参数值，加大并发能力； innodb_purge_threads：用于碎片回收的线程个数;
8.1 为什么查询慢
查询慢的原因 （1）SQL语句过于复杂，使用了多表关联查询，MySQL数据库多表关联查询是使用笛卡尔积的形式将多表关联起来， 比如第一张表4条数据，第二张表有5条数据，笛卡尔积就会产生20条数据，如果查询的时候在全表扫描会非常慢， 特别是数据量大的关联表多的时候，查询慢的效果很明显； （2）一次性查询数据量大、返回不必要的数据、出现死锁，会导致查询慢； （3）SQL语句没有建立索引或者是合适的索引，使用索引查询要比全表扫描高效的多； （4）SQL特定类型语句（COUNT、MAX、GROUP BY、LIMIT、UNION）使用不合理，会导致查询慢； （5）数据库表结构设计不合理，对表字段类型设计不合理，也会导致查询慢； （6）数据库系统配置不合理、服务器硬件（IO、CPU、内存）性能不足、网络慢，也会导致查询慢；
MySQL程序设计
第8章 MySQL高性能优化
8.7 数据库配置优化
IO分配参数优化
innodb_file_per_table：独立表空间设置； innodb_file_io_threads：文件读取的线程个数； innodb_open_files：打开的文件个数；
MySQL程序设计
第8章 MySQL高性能优化
MySQL程序设计
第8章 MySQL高性能优化
8.6 数据库表优化
选择字段数据类型 不常用字段分离 添加冗余字段
MySQL程序设计
第8章 MySQL高性能优化
8.7 数据库配置优化
内存使用参数优化
innodb_buffer_pool_size：Innodb存储引擎的缓冲池的大小； innodb_buffer_pool_instances：缓冲池的个数；
MySQL程序设计
第8章 MySQL高性能优化
索引提高查询效率 合适的字段添加索引 离散度大的联合索引 合适长度的索引 使用索引覆盖 前缀不易区分建立索引 删除重复和冗余索引
8.4 索引优化
MySQL程序设计
第8章 MySQL高性能优化
8.5 特定类型优化
COUNT类型优化 MAX、MIN类型优化 GROUP BY类型优化 LIMIT类型优化
MySQwk.baidu.com程序设计
第8章 MySQL高性能优化
数据库设计 创建数据库
8.2 数据准备
MySQL程序设计
第8章 MySQL高性能优化
8.3 慢查询日志分析
验证慢查询是否开启
使用show variables like 'slow_query%';来查看慢查询相关参数：slow_query_log：慢查询是否开启，ON 慢查询是开启状态，等于OFF慢查询是关闭状态；slow_query_log_file，慢查询存放日志的文件路径；
MySQL程序设计
第8章 MySQL高性能优化
8.7 数据库配置优化
日志控制参数优化
innodb_log_buffer_size：日志缓冲的大小； innodb_flush_log_at_trx_commit：有三个值0、1、2； innodb_log_file_size：指定事务日志的大小； innodb_log_files_in_group：指定日志组的个数；
MySQL程序设计
第8章 MySQL高性能优化
8.3 慢查询日志分析
开启SQL语句统计
使用设置set profiling=1来开启SQL语句统计功能，然后使用show profiles来统计执行哪些SQL以及这些 SQL语句的执行时间；Query_ID查询SQL语句的自动生成的标识；Duration查询的时长，时长越长，说明需 要更多的时间查询SQL语句；Query查询了哪些SQL语句。
第8章 MySQL高性能优化
8.3 慢查询日志分析
慢查询日志分析
# Time: 2018-01-10T14:00:56.345242Z # User@Host: root[root] @ localhost [::1] Id: 20 # Query_time: 0.102006 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 189999 SET timestamp=1515592856; SELECT COUNT(userName) FROM employee WHERE id > 10000 AND id < 200000;
MySQL程序设计
第8章 MySQL高性能优化
8.1 为什么查询慢 8.2 数据准备 8.3 慢查询日志分析 8.4 索引优化
MySQL程序设计
第8章 MySQL高性能优化
8.5 特定类型优化 8.6 数据库表优化 8.7 数据库配置优化 8.8 服务器硬件优化
MySQL程序设计
第8章 MySQL高性能优化
使用show variables like 'long_query_time';来设置多长时间为慢查询；long_query_time：设置慢查询时间， 超过这个时间记录到慢查询日志里。
MySQL程序设计
第8章 MySQL高性能优化
8.3 慢查询日志分析
开启慢查询两种方式
（1）使用命令来开启 将slow_query_log参数值设置为ON来开启慢查询；将slow_query_log_file参数值设置为慢查询日志文件存放目录 （D:/Program Files/MySQL/mysql-5.7.20-winx64/data/slow.log）； （2）修改my.ini配置文件 slow_query_log = ON slow_query_log_file = D:\Program Files\MySQL\mysql-5.7.20-winx64\data\slow.log long_query_time = 1 log_queries_not_using_indexes=ON
8.7 数据库配置优化
其他参数优化
default-character-set：设置MySQL客户端的字符集，通常设置为utf8； character-set-server：设置MySQL数据库的字符集，通常设置为utf8; default-storage-engine：MySQL数据库默认使用的存储引擎; innodb_read_io_threads/innodb_write_io_threads：Innodb存储引擎读写IO的线程数，默认值为4，可以 适当提高参数值，加大并发能力； innodb_purge_threads：用于碎片回收的线程个数;
8.1 为什么查询慢
查询慢的原因 （1）SQL语句过于复杂，使用了多表关联查询，MySQL数据库多表关联查询是使用笛卡尔积的形式将多表关联起来， 比如第一张表4条数据，第二张表有5条数据，笛卡尔积就会产生20条数据，如果查询的时候在全表扫描会非常慢， 特别是数据量大的关联表多的时候，查询慢的效果很明显； （2）一次性查询数据量大、返回不必要的数据、出现死锁，会导致查询慢； （3）SQL语句没有建立索引或者是合适的索引，使用索引查询要比全表扫描高效的多； （4）SQL特定类型语句（COUNT、MAX、GROUP BY、LIMIT、UNION）使用不合理，会导致查询慢； （5）数据库表结构设计不合理，对表字段类型设计不合理，也会导致查询慢； （6）数据库系统配置不合理、服务器硬件（IO、CPU、内存）性能不足、网络慢，也会导致查询慢；
MySQL程序设计
第8章 MySQL高性能优化
8.7 数据库配置优化
IO分配参数优化
innodb_file_per_table：独立表空间设置； innodb_file_io_threads：文件读取的线程个数； innodb_open_files：打开的文件个数；
MySQL程序设计
第8章 MySQL高性能优化
MySQL程序设计
第8章 MySQL高性能优化
8.6 数据库表优化
选择字段数据类型 不常用字段分离 添加冗余字段
MySQL程序设计
第8章 MySQL高性能优化
8.7 数据库配置优化
内存使用参数优化
innodb_buffer_pool_size：Innodb存储引擎的缓冲池的大小； innodb_buffer_pool_instances：缓冲池的个数；
MySQL程序设计
第8章 MySQL高性能优化
索引提高查询效率 合适的字段添加索引 离散度大的联合索引 合适长度的索引 使用索引覆盖 前缀不易区分建立索引 删除重复和冗余索引
8.4 索引优化
MySQL程序设计
第8章 MySQL高性能优化
8.5 特定类型优化
COUNT类型优化 MAX、MIN类型优化 GROUP BY类型优化 LIMIT类型优化
MySQwk.baidu.com程序设计
第8章 MySQL高性能优化
数据库设计 创建数据库
8.2 数据准备
MySQL程序设计
第8章 MySQL高性能优化
8.3 慢查询日志分析
验证慢查询是否开启
使用show variables like 'slow_query%';来查看慢查询相关参数：slow_query_log：慢查询是否开启，ON 慢查询是开启状态，等于OFF慢查询是关闭状态；slow_query_log_file，慢查询存放日志的文件路径；
MySQL程序设计
第8章 MySQL高性能优化
8.7 数据库配置优化
日志控制参数优化
innodb_log_buffer_size：日志缓冲的大小； innodb_flush_log_at_trx_commit：有三个值0、1、2； innodb_log_file_size：指定事务日志的大小； innodb_log_files_in_group：指定日志组的个数；
MySQL程序设计
第8章 MySQL高性能优化
8.3 慢查询日志分析
开启SQL语句统计
使用设置set profiling=1来开启SQL语句统计功能，然后使用show profiles来统计执行哪些SQL以及这些 SQL语句的执行时间；Query_ID查询SQL语句的自动生成的标识；Duration查询的时长，时长越长，说明需 要更多的时间查询SQL语句；Query查询了哪些SQL语句。