mysql巡检脚本sql补充

-- 没有主键索引
mysql [localhost:8028] {root} (test) > SELECT t.table_schema,t.table_name,t.engine
    -> FROM information_schema.tables t
    -> JOIN information_schema.columns c
    -> ON t.table_schema=c.table_schema
    -> AND t.table_name=c.table_name
    -> WHERE t.table_schema NOT IN ('mysql', 'information_schema', 'sys', 'performance_schema')
    -> AND t.table_type = 'BASE TABLE'
    -> GROUP BY t.table_schema,t.table_name, t.engine
    -> HAVING SUM(IF(column_key IN ('PRI','UNI'), 1,0)) = 0;
+--------------+------------+--------+
| TABLE_SCHEMA | TABLE_NAME | ENGINE |
+--------------+------------+--------+
| test         | test6      | InnoDB |
+--------------+------------+--------+
1 row in set (0.01 sec)

– 主键不是整型（在高并发场景下，自增id主键就有性能问题，产生热点块争用，这种情况下，uuid可能更好些，解决了热点争用问题。分布式高并发下，可能用区别号+机房号+主机序号+流水号，可能会更好些。主键要根据实际的业务场景设计。）

mysql [localhost:8028] {root} (test) > SELECT table_schema, table_name, column_name, data_type, character_maximum_length
    -> FROM information_schema.columns
    -> WHERE column_key IN ('PRI','UNI')
    -> AND ordinal_position=1
    -> AND data_type NOT IN ('tinyint', 'smallint', 'mediumint', 'int', 'bigint', 'timestamp', 'datetime')
    -> AND table_schema NOT IN ('mysql', 'information_schema', 'sys', 'performance_schema');
+--------------+------------+-------------+-----------+--------------------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH |
+--------------+------------+-------------+-----------+--------------------------+
| test         | test7      | id          | varchar   |                       20 |
+--------------+------------+-------------+-----------+--------------------------+
1 row in set (0.00 sec)

– 存储引警不是innodb表

mysql [localhost:8028] {root} (test) > SELECT t.table_schema,t.table_name,t.engine
    -> FROM information_schema.tables t
    -> WHERE t.table_schema NOT IN ('mysql', 'information_schema', 'sys', 'performance_schema')
    -> AND t.engine <> 'InnoDB'
    -> AND t.table_type = 'BASE TABLE';
+--------------+------------+--------+
| TABLE_SCHEMA | TABLE_NAME | ENGINE |
+--------------+------------+--------+
| test         | test8      | MyISAM |
+--------------+------------+--------+
1 row in set (0.00 sec)

– 延迟最长的表和索引

SELECT * FROM sys.schema_table_statistics
WHERE table_schema='test'
AND table_schema NOT IN ('mysql', 'information_schema', 'sys', 'performance_schema')
Order by total_latency desc;

SELECT *
FROM sys.schema_index_statistics
WHERE table_schema='test'
AND table_schema NOT IN ('mysql', 'information_schema', 'sys', 'performance_schema');

– 索引数据大于数据的50%

mysql [localhost:8028] {root} (test) > SELECT table_schema, table_name, index_length, data_length, index_length/data_length AS index_to_data_ratio
    -> FROM information_schema.tables
    -> WHERE table_schema NOT IN ('mysql', 'information_schema', 'sys', 'performance_schema')
    -> AND INDEX_LENGTH > DATA_LENGTH*1.5;
+--------------+------------+--------------+-------------+---------------------+
| TABLE_SCHEMA | TABLE_NAME | INDEX_LENGTH | DATA_LENGTH | index_to_data_ratio |
+--------------+------------+--------------+-------------+---------------------+
| test         | test8      |         2048 |          20 |            102.4000 |
+--------------+------------+--------------+-------------+---------------------+

– 表有重复索引的

mysql [localhost:8028] {root} (test) > SELECT table_schema,table_name,redundant_index_name AS redundant_index, redundant_index_columns AS redundant_columns, dominant_index_name AS covered_by_index,sql_drop_index
    -> FROM sys.schema_redundant_indexes
    -> WHERE table_schema NOT IN ('mysql', 'information_schema', 'sys', 'performance_schema');
+--------------+------------+-----------------+-------------------+------------------+-------------------------------------------------+
| table_schema | table_name | redundant_index | redundant_columns | covered_by_index | sql_drop_index                                  |
+--------------+------------+-----------------+-------------------+------------------+-------------------------------------------------+
| test         | test8      | idx_seq         | seq_no            | idx_seq2         | ALTER TABLE `test`.`test8` DROP INDEX `idx_seq` |
+--------------+------------+-----------------+-------------------+------------------+-------------------------------------------------+
1 row in set (0.01 sec)

– 没用到索引的表

mysql [localhost:8028] {root} (test) > SELECT *
    -> FROM sys.schema_unused_indexes
    -> WHERE object_schema NOT IN ('mysql', 'information_schema', 'sys', 'performance_schema');
+---------------+-------------+------------+
| object_schema | object_name | index_name |
+---------------+-------------+------------+
| test          | test8       | idx_seq    |
| test          | test8       | idx_seq2   |
+---------------+-------------+------------+
2 rows in set (0.01 sec)