Mysql | 十字螺丝钉的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年11月3日 cnail MySQL, 基础知识 Leave a comment

MySQL 创建触发器引起的元数据锁

同事反映，在主库使用pt-online-schema-change工具修改字段长度时，引起三个从库中的一个从库产生了元数据锁，此时一个创建触发器的语句在等待获取元数据（Waiting for table metadata lock）。 pt-online-schema-change执行时，会创建三个触发器，用于数据同步，分别是insert、update、delete触发器。同事在描述问题时，我脑

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年9月11日 cnail MySQL, 工具 Leave a comment

 SELECT object_type,
        object_schema,
        object_name,
        index_name,
        count_star,
        count_read,
        count_fetch
   FROM performance_schema.table_io_waits_summary_by_index_usage
  WHERE index_name IS NOT NULL
    AND count_star = 0
    AND object_schema not in ('mysql')
    AND index_name <> 'PRIMARY'
  ORDER BY object_schema, object_name;

SELECT object_type,

object_schema,

object_name,

index_name,

count_star,

count_read,

count_fetch

FROM performance_schema.table_io_waits_summary_by_index_usage

WHERE index_name IS NOT NULL

AND count_star = 0

AND object_schema not in ('mysql')

AND index_name <> 'PRIMARY'

ORDER BY object_schema, object_name;

2020年7月20日 cnail DBA 常用脚本, MySQL, 运维脚本 Leave a comment

MySQL 导出表上所有索引的创建语句

获取单表上所有索引的建表语句

SELECT 
    CONCAT('ALTER TABLE `',
            TABLE_NAME,
            '` ',
            'ADD ',
            IF(NON_UNIQUE = 1,
                CASE UPPER(INDEX_TYPE)
                    WHEN 'FULLTEXT' THEN 'FULLTEXT INDEX'
                    WHEN 'SPATIAL' THEN 'SPATIAL INDEX'
                    ELSE CONCAT('INDEX `',
                            INDEX_NAME,
                            '` USING ',
                            INDEX_TYPE)
                END,
                IF(UPPER(INDEX_NAME) = 'PRIMARY',
                    CONCAT('PRIMARY KEY USING ', INDEX_TYPE),
                    CONCAT('UNIQUE INDEX `',
                            INDEX_NAME,
                            '` USING ',
                            INDEX_TYPE))),
            '(',
            GROUP_CONCAT(DISTINCT CONCAT('`', COLUMN_NAME, '`')
                ORDER BY SEQ_IN_INDEX ASC
                SEPARATOR ', '),
            ');') AS 'Show_Add_Indexes'
FROM
    information_schema.STATISTICS
WHERE
    TABLE_SCHEMA = '库名'
        AND TABLE_NAME = '表明'
GROUP BY TABLE_NAME , INDEX_NAME
ORDER BY TABLE_NAME ASC , INDEX_NAME ASC;

SELECT

CONCAT('ALTER TABLE `',

TABLE_NAME,

'` ',

'ADD ',

IF(NON_UNIQUE = 1,

CASE UPPER(INDEX_TYPE)

WHEN 'FULLTEXT' THEN 'FULLTEXT INDEX'

WHEN 'SPATIAL' THEN 'SPATIAL INDEX'

ELSE CONCAT('INDEX `',

INDEX_NAME,

'` USING ',

INDEX_TYPE)

END,

IF(UPPER(INDEX_NAME) = 'PRIMARY',

CONCAT('PRIMARY KEY USING ', INDEX_TYPE),

CONCAT('UNIQUE INDEX `',

INDEX_NAME,

'` USING ',

INDEX_TYPE))),

'(',

GROUP_CONCAT(DISTINCT CONCAT('`', COLUMN_NAME, '`')

ORDER BY SEQ_IN_INDEX ASC

SEPARATOR ', '),

');') AS 'Show_Add_Indexes'

FROM

information_schema.STATISTICS

WHERE

TABLE_SCHEMA = '库名'

AND TABLE_NAME = '表明'

GROUP BY TABLE_NAME , INDEX_NAME

ORDER BY TABLE_NAME ASC , INDEX_NAME ASC;

获取非主键索引的建表语句

SELECT 
    CONCAT('ALTER TABLE `',
            TABLE_NAME,
            '` ',
            'ADD ',
            IF(NON_UNIQUE = 1,
                CASE UPPER(INDEX_TYPE)
                    WHEN 'FULLTEXT' THEN 'FULLTEXT INDEX'
                    WHEN 'SPATIAL' THEN 'SPATIAL INDEX'
                    ELSE CONCAT('INDEX `',
                            INDEX_NAME,
                            '` USING ',
                            INDEX_TYPE)
                END,
                IF(UPPER(INDEX_NAME) = 'PRIMARY',
                    CONCAT('PRIMARY KEY USING ', INDEX_TYPE),
                    CONCAT('UNIQUE INDEX `',
                            INDEX_NAME,
                            '` USING ',
                            INDEX_TYPE))),
            '(',
            GROUP_CONCAT(DISTINCT CONCAT('`', COLUMN_NAME, '`')
                ORDER BY SEQ_IN_INDEX ASC
                SEPARATOR ', '),
            ');') AS 'Show_Add_Indexes'
FROM
    information_schema.STATISTICS
WHERE
    UPPER(INDEX_NAME) <> 'PRIMARY'
        AND TABLE_SCHEMA = '库名'
        AND TABLE_NAME = '表名'
GROUP BY TABLE_NAME , INDEX_NAME
ORDER BY TABLE_NAME ASC , INDEX_NAME ASC;

SELECT

CONCAT('ALTER TABLE `',

TABLE_NAME,

'` ',

'ADD ',

IF(NON_UNIQUE = 1,

CASE UPPER(INDEX_TYPE)

WHEN 'FULLTEXT' THEN 'FULLTEXT INDEX'

WHEN 'SPATIAL' THEN 'SPATIAL INDEX'

ELSE CONCAT('INDEX `',

INDEX_NAME,

'` USING ',

INDEX_TYPE)

END,

IF(UPPER(INDEX_NAME) = 'PRIMARY',

CONCAT('PRIMARY KEY USING ', INDEX_TYPE),

CONCAT('UNIQUE INDEX `',

INDEX_NAME,

'` USING ',

INDEX_TYPE))),

'(',

GROUP_CONCAT(DISTINCT CONCAT('`', COLUMN_NAME, '`')

ORDER BY SEQ_IN_INDEX ASC

SEPARATOR ', '),

');') AS 'Show_Add_Indexes'

FROM

information_schema.STATISTICS

WHERE

UPPER(INDEX_NAME) <> 'PRIMARY'

AND TABLE_SCHEMA = '库名'

AND TABLE_NAME = '表名'

GROUP BY TABLE_NAME , INDEX_NAME

ORDER BY TABLE_NAME ASC , INDEX_NAME ASC;

获取主键索引的建表语句

SELECT 
    CONCAT('ALTER TABLE `',
            TABLE_NAME,
            '` ',
            'ADD ',
            IF(NON_UNIQUE = 1,
                CASE UPPER(INDEX_TYPE)
                    WHEN 'FULLTEXT' THEN 'FULLTEXT INDEX'
                    WHEN 'SPATIAL' THEN 'SPATIAL INDEX'
                    ELSE CONCAT('INDEX `',
                            INDEX_NAME,
                            '` USING ',
                            INDEX_TYPE)
                END,
                IF(UPPER(INDEX_NAME) = 'PRIMARY',
                    CONCAT('PRIMARY KEY USING ', INDEX_TYPE),
                    CONCAT('UNIQUE INDEX `',
                            INDEX_NAME,
                            '` USING ',
                            INDEX_TYPE))),
            '(',
            GROUP_CONCAT(DISTINCT CONCAT('`', COLUMN_NAME, '`')
                ORDER BY SEQ_IN_INDEX ASC
                SEPARATOR ', '),
            ');') AS 'Show_Add_Indexes'
FROM
    information_schema.STATISTICS
WHERE
    UPPER(INDEX_NAME) = 'PRIMARY'
        AND TABLE_SCHEMA = '库名'
        AND TABLE_NAME = '表名'
GROUP BY TABLE_NAME , INDEX_NAME
ORDER BY TABLE_NAME ASC , INDEX_NAME ASC;

SELECT

CONCAT('ALTER TABLE `',

TABLE_NAME,

'` ',

'ADD ',

IF(NON_UNIQUE = 1,

CASE UPPER(INDEX_TYPE)

WHEN 'FULLTEXT' THEN 'FULLTEXT INDEX'

WHEN 'SPATIAL' THEN 'SPATIAL INDEX'

ELSE CONCAT('INDEX `',

INDEX_NAME,

'` USING ',

INDEX_TYPE)

END,

IF(UPPER(INDEX_NAME) = 'PRIMARY',

CONCAT('PRIMARY KEY USING ', INDEX_TYPE),

CONCAT('UNIQUE INDEX `',

INDEX_NAME,

'` USING ',

INDEX_TYPE))),

'(',

GROUP_CONCAT(DISTINCT CONCAT('`', COLUMN_NAME, '`')

ORDER BY SEQ_IN_INDEX ASC

SEPARATOR ', '),

');') AS 'Show_Add_Indexes'

FROM

information_schema.STATISTICS

WHERE

UPPER(INDEX_NAME) = 'PRIMARY'

AND TABLE_SCHEMA = '库名'

AND TABLE_NAME = '表名'

GROUP BY TABLE_NAME , INDEX_NAME

ORDER BY TABLE_NAME ASC , INDEX_NAME ASC;

如果想获取整个库中所有表的索引创建语句，可以写存储过程循环执行就行了。 &nbs

十字螺丝钉的DBA日记

分类：MySQL

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

MySQL 创建触发器引起的元数据锁

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

innodb_ruby – InnoDB结构分析工具的安装及使用

MySQL修改字段默认值的三种方法

MySQL IP转数字函数 INET_ATON() INET_NTOA()

MySQL：如何找出不使用的索引

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