摘要：慢查询日志的慢查询日志是提供的一种日志记录，它用来记录在中响应时间超过阀值的语句，具体指运行时间超过值的，则会被记录到慢查询日志中查看慢查询是否开启开启慢查询默认是关闭设置时间阀值默认是未使用索引的查询也被记录到慢查询日志中默认关闭分析日志
1.慢查询日志
MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中
#查看慢查询是否开启 >show variables like "%slow_query_log%"; #开启慢查询（默认是关闭） >set global slow_query_log=1; #设置时间阀值（默认是10s） >set global long_query_time=1; #未使用索引的查询也被记录到慢查询日志中（默认关闭） >set global log_queries_not_using_indexes=1;
分析日志 mysqldumpslow /var/lib/mysql/ArchLinux-slow.log Count: 1 #访问计数 Time=0.00s (0s) #平均查询时间(总查询时间) Lock=0.00s (0s) #平均锁定时间(总锁定时间) Rows_sent=1000.0 (1000) #平均返回记录数(总返回记录数) Rows_examined=1000.0 (1000) #平均扫描过的行数(总扫描过的行数) Rows_affected=0.0 (0) #平均更新行数(总更新行数)
分析日志(percona-toolkit) pt-query-digest /var/lib/mysql/ArchLinux-slow.log 开始总的摘要信息 # 170ms user time, 10ms system time, 26.00M rss, 213.39M vsz --此工具执行日志分析时的所用时间、内存资源(rss物理内存占用大小，vsz虚拟内存占用大小) # Current date: Mon Jul 28 09:55:34 2014 --分析时的系统时间 # Hostname: lump.group.com --进行分析的主机名 # Files: mysql-slow.log --分析的日志文件名称 # Overall: 5 total, 4 unique, 0.02 QPS, 0.04x concurrency --文件中总共的语句数量，唯一的语句数量(对语句进行了格式化)，QPS(每秒查询数(queries per second))，并发数 # Time range: 2014-07-28 09:50:30 to 09:54:50 --记录日志的时间范围 # Attribute total min max avg 95% stddev median --total总计，min最小，max最大，avg平均，95%把所有值从小到大排列，位于95%的那个数 –Exec time：语句执行时间 –Lock time：锁占有时间 –Rows sent：发送到客户端的行数 –Row examine：扫描的行数(SELECT语句) –Row affecte：发送改变的行数(UPDATE, DELETE, INSERT语句) –Bytes sent：发送多少bytes的查询结果集 –Query size：查询语句的字符 查询分组统计结果 –Rank:分析的所有查询语句的排名，默认按查询时间降序排序 –Query ID：查询语句的指纹 –Response time：响应时间 –Calls：查询执行的次数 –R/Call：每次执行的平均响应时间 –V/M：响应时间Variance-to-mean的比率 –Item：查询语句 每个独立查询语句的分析
#保存到数据库中（简单） pt-query-digest --user=root --password=123456 --review h=127.0.0.1,D=sakila,t=global_query_review --no-report --create-review-table /var/lib/mysql/ArchLinux-slow.log #保存到数据库中（详细） pt-query-digest --user=root --password=123456 --history h=127.0.0.1,D=sakila,t=global_query_history --no-report --create-history-table /var/lib/mysql/ArchLinux-slow.log
2.分析数据
>explain select customer_id,first_name,last_name from customer; id:1 #如果id相同，从上往下顺序执行；否则id值越大，优先级越高，越先执行 select_type: SIMPLE #查询类型(简单查询SIMPLE;含子查询或联合查询PRIAMRY;联合查询UNION;联合查询结果UNION RESULT;非from子查询SUBQUERY;from子查询DERIVED) table: customer #显示这条数据是哪一张表(表的别名;derived子查询;null计算结果) type: ALL #显示连接使用何种类型（null不用访问表或索引>const直接获取(主索引)>eq_ref主索引引用(常用于多表连接)> ref次索引检索>range只检索给定范围索引>index遍历索引>all遍历全表） possible_keys: NULL #显示可能应用在这张表的索引 key: NULL #实际使用的索引 key_len: NULL #使用索引的长度（越短越好） ref: NULL #显示索引的那一列被使用 rows: 599 #检索行数 Extra: #额外信息（Using index:使用索引;Using where:在使用索引的基础上进行条件判断; Using filesort:表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询; Using temporary:MySQL中无法利用索引完成的排序操作称为“文件排序”）
3.查询优化
#优化Max >explain select max(payment_date) from payment; (type为ALL) >create index idx_paydate on payment(payment_date); >explain select max(payment_date) from payment; (type为NULL)
#优化子查询
>explain select title,release_year,length from film where film_id in
(select film_id from film_actor where actor_id in
( select actor_id from actor where first_name ="sandra")
); （采用子查询）
>explain select film.title,film.release_year,film.length from film,film_actor,actor
where actor.first_name="sandra"
and film_actor.actor_id=actor.actor_id
and film_actor.film_id=film.film_id; (采用连接查询)
#去除重复索引（重复索引指相同的列以相同的顺序建立的同类型的索引）
create table test(
id int primary key,
name varchar(10),
constraint union_key unique(id)
);
pt-duplicate-key-checker -u root -p "123456" -h 127.0.0.1
#去除冗余索引（冗余索引指多个索引的前缀列相同，或是在联合索引中包含主键的索引）
create table test(
id int primary key,
name varchar(10),
index union_index(id,name(4))
)
pt-duplicate-key-checker -u root -p "123456" -h 127.0.0.1
#删除不用的索引 pt-index-usage -u root -p "123456" -h 127.0.0.1
#索引左前缀原则【假如有联合索引index(c1,c2,c3,c4)】 (a).where c1=x and c2=x and c4>x and c3=x : index(c1,c2,c3,c4),where添加可以交换位置 (b).where c1=x and c2=x and c4=x order(group) by c3 : index(c1,c2,c3), (c).where c1=x and c2=x and c3=x order(group) by c4 : index(c1,c2,c3,c4) (d).where c1=x and c4=x order(group) by c2,c3 : index(c1,c2,c3),order(group) by不可以交换位置 (e).where c1=x and c4=x order(group) by c3,c2 : index(c1)
#索引覆盖【假如主键key(c1),联合索引index(c1,c2)】 (a).select * from t1 order by c1 : key(c1), (b).select * from t1 order by c1,c2 : index(1,2)
4.如何建立索引
a.在where从句，group by从句，order by从句，on从句中出现的列
b.索引字段越小越好
c.离散度大的列放到联合索引的前面(count(distinct 字段名)越大的离散度越大)
文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。
转载请注明本文地址：https://www.ucloud.cn/yun/47674.html
摘要：数据实体之间的逻辑关系，解决数据冗余和数据维护异常。判断标准使用，可以通过输出的列来判断，对于一个索引覆盖查询，显示为查询优化器在执行查询前会决定是否有索引覆盖查询。 上一篇：MySQL性能管理及架构设计（一）：什么影响了数据库查询速度、什么影响了MySQL性能 一、数据库结构优化（非常重要） 1.1 数据库结构优化目的 1、减少数据冗余：（数据冗余是指在数据库中存在相同的数据...
摘要：在库存系统中，最重要的就是要防止超卖。系列创建高性能的索引索引是存储引擎用于快速找到记录的一种数据结构。对查询性能优化最有效的手段。性能优化梳理前言本文主要针对的是关系型数据数据库。用户可以通过特殊的关键字提示优化器，影响的决策过程。 图文并茂详解 SQL JOIN Join 是关系型数据库系统的重要操作之一，一般关系型数据库中包含的常用 Join：内联接、外联接和交叉联接等。如果我们...
摘要：在库存系统中，最重要的就是要防止超卖。系列创建高性能的索引索引是存储引擎用于快速找到记录的一种数据结构。对查询性能优化最有效的手段。性能优化梳理前言本文主要针对的是关系型数据数据库。用户可以通过特殊的关键字提示优化器，影响的决策过程。 图文并茂详解 SQL JOIN Join 是关系型数据库系统的重要操作之一，一般关系型数据库中包含的常用 Join：内联接、外联接和交叉联接等。如果我们...
摘要：如果语句中使用了子查询集合操作临时表等情况，会给列带来很大的复杂性。会递归执行这些子查询，把结果放在临时表里。查询优化器从中所选择使用的索引。该字段显示了查询优化器通过系统收集的统计信息估算出来的结果集记录条数。 引言 优化SQL，是DBA常见的工作之一。如何高效、快速地优化一条语句，是每个DBA经常要面对的一个问题。在日常的优化工作中，我发现有很多操作是在优化过程中必不可少的步骤。然...
摘要：如果语句中使用了子查询集合操作临时表等情况，会给列带来很大的复杂性。会递归执行这些子查询，把结果放在临时表里。查询优化器从中所选择使用的索引。该字段显示了查询优化器通过系统收集的统计信息估算出来的结果集记录条数。 引言 优化SQL，是DBA常见的工作之一。如何高效、快速地优化一条语句，是每个DBA经常要面对的一个问题。在日常的优化工作中，我发现有很多操作是在优化过程中必不可少的步骤。然...
摘要：如果语句中使用了子查询集合操作临时表等情况，会给列带来很大的复杂性。会递归执行这些子查询，把结果放在临时表里。查询优化器从中所选择使用的索引。该字段显示了查询优化器通过系统收集的统计信息估算出来的结果集记录条数。 引言 优化SQL，是DBA常见的工作之一。如何高效、快速地优化一条语句，是每个DBA经常要面对的一个问题。在日常的优化工作中，我发现有很多操作是在优化过程中必不可少的步骤。然...
阅读 1819·2021-08-11 11:13
阅读 821·2021-07-25 21:37
阅读 2461·2019-08-29 18:42
阅读 2390·2019-08-26 12:18
阅读 753·2019-08-26 11:29
阅读 1568·2019-08-23 17:17
阅读 2550·2019-08-23 15:55
阅读 2482·2019-08-23 14:34
