运维脚本 | 十字螺丝钉的DBA日记

2020年8月21日 cnail MySQL, 运维脚本 Leave a comment

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

在数据库中，索引起到查询加速的作用，可以有效提升查询速度以及排序查询效率。但是，如果表上索引过多，会影响插入速度。我们日常工作中，要日常检查索引是否在使用，不使用的索引要删掉节省空间。 MySQL查找不使用的索引：

 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

2019年12月9日 cnail DBA 常用脚本, 运维脚本 Leave a comment

MySQL：如何找出长时间执行的查询SQL和事务SQL

MySQL中存在长时间运行的SQL和事务（超过120S），对数据库而言是潜在隐患，这颗“雷”总有爆炸的一天。比如，修改表结构之前，切记要在从库检查是否有长时间运行的SQL，有请杀掉，避免从库产生元数据锁引起的从库延迟。建议定时扫描MySQL中长时间运行的SQL，并kill掉，排除隐患。

SELECT
	trx.trx_id,
	trx.trx_started,
	trx.trx_mysql_thread_id,
	trx.trx_query
FROM
	INFORMATION_SCHEMA.INNODB_TRX AS trx
INNER JOIN INFORMATION_SCHEMA. PROCESSLIST AS pl 
	ON trx.trx_mysql_thread_id = pl.id
WHERE
	trx.trx_started < CURRENT_TIMESTAMP - INTERVAL 120 SECOND
AND pl. USER <> 'system_user';

SELECT

trx.trx_id,

trx.trx_started,

trx.trx_mysql_thread_id,

trx.trx_query

FROM

INFORMATION_SCHEMA.INNODB_TRX AS trx

INNER JOIN INFORMATION_SCHEMA. PROCESSLIST AS pl

ON trx.trx_mysql_thread_id = pl.id

WHERE

trx.trx_started < CURRENT_TIMESTAMP - INTERVAL 120 SECOND

AND pl. USER <> 'system_user';

2019年11月30日 cnail 运维脚本 Leave a comment

MySQL：如何找出数据库中没有主键的表

工作中MySQL使用Innodb引擎时，创建表一定要加上主键（最好是无意义的auto increment列）。如果表没有主键和索引，且数据量很大，那么在主从复制时，从库上该表的dml操作不能根据主键和索引快速定位到操作的数据，只能采用全表扫描，速度非常缓慢。因此，对于InnoDB而言，主键非常重要。找出数据库中哪些表没有主键：

SELECT t.table_schema,
       t.table_name,
       k.constraint_name,
       k.column_name
FROM information_schema.tables t
     LEFT JOIN information_schema.key_column_usage k
          ON t.table_schema = k.table_schema
             AND t.table_name = k.table_name
             AND k.constraint_name = 'PRIMARY'
 WHERE t.table_schema NOT IN ( 'mysql', 'information_schema',
                                       'performance_schema' )
       AND k.constraint_name IS NULL
       AND t.table_type = 'BASE TABLE';

SELECT t.table_schema,

t.table_name,

k.constraint_name,

k.column_name

FROM information_schema.tables t

LEFT JOIN information_schema.key_column_usage k

ON t.table_schema = k.table_schema

AND t.table_name = k.table_name

AND k.constraint_name = 'PRIMARY'

WHERE t.table_schema NOT IN ( 'mysql', 'information_schema',

'performance_schema' )

AND k.constraint_name IS NULL

AND t.table_type = 'BASE TABLE';

一	二	三	四	五	六	日
« 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