优化 | 十字螺丝钉的DBA日记

2020年11月17日 cnail 优化 Leave a comment

MySQL 执行计划中的 key_len 表示什么

我们在使用MySQL中的 explain 命令查看执行计划中，往往能看到key_len 这个指标。使用复合索引时，通过key_len 的值能判定SQL使用了索引中的哪些字段。

root@ 14:37:  [sbtest1]&gt; show create table sbtest1\G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  `in_time` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`),
  KEY `idx_k_pad` (`k`,`pad`)
) ENGINE=InnoDB AUTO_INCREMENT=500001 DEFAULT CHARSET=utf8mb4 MAX_ROWS=1000000
1 row in set (0.00 sec)


root@ 14:38:  [sbtest1]&gt; explain select * from sbtest1 where k='3' and pad='2342';
+----+-------------+---------+------------+------+---------------+-----------+---------+-------------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key       | key_len | ref         | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+-----------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | sbtest1 | NULL       | ref  | idx_k_pad     | idx_k_pad | 244     | const,const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+-----------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

`k` int(10) 4*10=40
`pad` char(60) 60*3=180

root@ 14:37: [sbtest1]> show create table sbtest1\G

*************************** 1. row ***************************

Table: sbtest1

Create Table: CREATE TABLE `sbtest1` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`k` int(10) unsigned NOT NULL DEFAULT '0',

`c` char(120) NOT NULL DEFAULT '',

`pad` char(60) NOT NULL DEFAULT '',

`in_time` date DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `k_1` (`k`),

KEY `idx_k_pad` (`k`,`pad`)

) ENGINE=InnoDB AUTO_INCREMENT=500001 DEFAULT CHARSET=utf8mb4 MAX_ROWS=1000000

1 row in set (0.00 sec)

root@ 14:38: [sbtest1]> explain select * from sbtest1 where k='3' and pad='2342';

+----+-------------+---------+------------+------+---------------+-----------+---------+-------------+------+----------+-------+

+----+-------------+---------+------------+------+---------------+-----------+---------+-------------+------+----------+-------+

| 1 | SIMPLE | sbtest1 | NULL | ref | idx_k_pad | idx_k_pad | 244 | const,const | 1 | 100.00 | NULL |

+----+-------------+---------+------------+------+---------------+-----------+---------+-------------+------+----------+-------+

1 row in set, 1 warning (0.00 sec)

`k` int(10) 4*10=40

`pad` char(60) 60*3=180

长度计算方法 1.字符集字符集不同，字符占据的字节长度也不同。字符集字节长度 gbk 1个字符占2个字节 utf8 1个字符占3个字节 utf8mb4

2020年9月29日 cnail MySQL, 优化 Leave a comment

MySQL order by + limit bug 触发原因及解决办法

问题描述下面这个SQL，应该走where的索引 idx_iv_pid，包含 id_value 、parent_id 两列。但是，走的是排序列 add_time的索引add_time_idx。从而SQL执行速度慢。但是，但是！ add_time_idx 并不在“possible_keys”里。

mysql>desc SELECT *
FROM `ecs_ugo_comment`
WHERE id_value = 23613
AND comment_type IN (0, 3)
AND STATUS = 1
AND parent_id = 0
ORDER BY add_time DESC
LIMIT 1
+--------------+-----------------+-----------------+---------+--------------------------------------------+---------------+-------------------+----------+----------------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+--------------+-----------------+-----------------+---------+--------------------------------------------+---------------+-------------------+----------+----------------+-----------------+
| 1 | SIMPLE | ecs_ugo_comment | index | parent_id,id_value,idx_iv_at_ac,idx_iv_pid | add_time_idx | 4 | | 1601 | Using where |
+--------------+-----------------+-----------------+---------+--------------------------------------------+---------------+-------------------+----------+----------------+-----------------+
返回行数: [1], 耗时: [52ms]

mysql>desc SELECT *

FROM `ecs_ugo_comment`

WHERE id_value = 23613

AND comment_type IN (0, 3)

AND STATUS = 1

AND parent_id = 0

ORDER BY add_time DESC

LIMIT 1

+--------------+-----------------+-----------------+---------+--------------------------------------------+---------------+-------------------+----------+----------------+-----------------+

+--------------+-----------------+-----------------+---------+--------------------------------------------+---------------+-------------------+----------+----------------+-----------------+

+--------------+-----------------+-----------------+---------+--------------------------------------------+---------------+-------------------+----------+----------------+-----------------+

返回行数: [1], 耗时: [52ms]

SQL真实执行时间为 1202ms

2020年5月11日 cnail MySQL, 优化 Leave a comment

profiling – MySQL资源使用情况剖析工具

profiling 用于剖析当前session执行SQL语句的资源使用情况。如何使用profileing 1、开启profiling set profiling = 1; profiling变量默认是0（off），且一般开启session级别。 2、执行需要诊断剖析SQL语句 show tables like ‘sbtest%’; 3、查看性能

2019年5月6日 cnail MySQL, 优化, 复制 Leave a comment

MySQL 减少从库延迟的方法

从库延迟有两方面原因： 1、IO thread慢，主要是因为网络带宽不足。在主从库开启启压缩参数slave_compressed_protocol减少压力。网上查看实验数据，压缩率大概是1/4（开启压缩7.14MB/s，不开启则是23.76MB/s）如果CPU压力已经很大不建议开启压缩参数，毕竟压缩要消耗大量CPU资源。 2、 SQL thread慢。 SQL thread负责读取relay

2018年7月24日 cnail MySQL, 优化 Leave a comment

MySQL 不能用create table as select 的原因

1、新表不会自动创建创建和原表相同的索引。 2、一个事务中混合了事务和非事务语句。它会提交当前和未完成的事务。例如，该例子中，第2个update未执行成功，会导致第1个update回滚，但是其中的CTAS语句为DDL操作，无法回滚。解决办法：使用临时表create temporary table ，退出会话后临时表自动删除。 begin; update accounts set amount

2018年7月19日 cnail MySQL, 优化 Leave a comment

order by + limit 改写优化

order by +limit 常用于分页功能。本案例中，用ID列进行排序，除了查询ID列外，还查询了value和stuffing列。这种情况容易出现性能问题或者不是性能最优的SQL写法。

SELECT id, value, LENGTH(stuffing) AS len
  FROM t_limit
 ORDER BY id LIMIT 150000, 10;

SELECT id, value, LENGTH(stuffing) AS len

FROM t_limit

ORDER BY id LIMIT 150000, 10;

测试中发现，如果t_limit为MySIAM。很容易出现性能问题，执行计划走全表扫描不走索引。现在几乎不用MySIAM，所以本文测试结果基于I

2017年1月18日十字螺丝钉优化 Leave a comment

MySQL慢查询日志 – Slow Query Log

The Slow Query Log，也就是MySQL的慢查询日志，其记录了执行时间超过设定值的SQL，也可以记录不使用索引的SQL语句。慢查询日志，能够帮助DBA快速锁定性能异常的SQL语句，然后进行优化工作，以保障MySQL数据库的正常运行。慢查询日志记录了两部分SQL： 1、SQL查询时间超过变量 long_query_time 设定值，默认10秒。 2、SQL返回行数大于 min_exa

2017年1月17日十字螺丝钉优化 Leave a comment

一、慢查询日志介绍 MySQL中,SQL的执行效率直接影响了MySQL的性能。为了便于DBA及时发现并处理有性能异常的SQL，MySQL专门设立了慢查询日志（slow query log）。SQL执行时间大于long_query_time设定的时候，MySQL就会将这条SQL记录到慢查询日志中。 MySQL慢查询日志既可以保存成文件，又可以保存在MySQL的表中。由于保存在表中，会给数据库带来额外

十字螺丝钉的DBA日记

分类：优化

MySQL 执行计划中的 key_len 表示什么

MySQL order by + limit bug 触发原因及解决办法

profiling – MySQL资源使用情况剖析工具

MySQL 减少从库延迟的方法

MySQL 不能用create table as select 的原因

order by + limit 改写优化

MySQL慢查询日志 – Slow Query Log

mysqldumpslow – 读懂MySQL慢查询日志

2024年4月
一	二	三	四	五	六	日
« 9月
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30