数据库性能优化指南

1. 查看数据库索引卡inality比率

以下查询用于分析数据库表的索引cardinality比率，帮助评估索引的合理性。cardinality比率通常应控制在表数据的10%左右。

SELECT    T1.TABLE_SCHEMA, 
    T1.TABLE_NAME, 
    T2.INDEX_NAME, 
    ROUND(T2.CARDINALITY / T1.TABLE_ROWS * 100, 2) AS RATE
FROM    INFORMATION_SCHEMA.TABLES T1,
    INFORMATION_SCHEMA.STATISTICS T2
WHERE    T1.TABLE_SCHEMA = T2.TABLE_SCHEMA 
        AND T1.TABLE_NAME = T2.TABLE_NAME 
        AND T2.SEQ_IN_INDEX = (
            SELECT            MIN(T3.SEQ_IN_INDEX) 
            FROM            INFORMATION_SCHEMA.STATISTICS T3 
            WHERE                T2.TABLE_NAME = T3.TABLE_NAME 
                AND T2.TABLE_SCHEMA = T3.TABLE_SCHEMA 
                AND T2.INDEX_NAME = T3.INDEX_NAME)
AND T1.TABLE_SCHEMA NOT IN ('mysql', 'performance_schema', 'information_schema', 'sys')
AND T1.TABLE_ROWS >= 100
ORDER BY RATE;

2. 查看锁阻塞情况

以下查询用于分析数据库中的锁阻塞情况，帮助识别可能导致高阻塞的SQL。

SELECT    t3.trx_id AS blocking_trx_id, 
    t3.trx_mysql_thread_id AS blocking_thread, 
    t3.trx_query AS blocking_query,
    t2.trx_id AS waiting_trx_id, 
    t2.trx_mysql_thread_id AS waiting_thread, 
    t2.trx_query AS waiting_query
FROM    information_schema.innodb_lock_waits t1,
    information_schema.innodb_trx t2,
    information_schema.innodb_trx t3
WHERE    t1.blocking_trx_id = t2.trx_id 
        AND t1.requesting_trx_id = t3.trx_id;

3. 识别非InnoDB引擎表

以下查询用于识别数据库中非InnoDB引擎表，确保所有重要表使用InnoDB存储引擎。

SELECT    TABLE_SCHEMA, 
    TABLE_NAME, 
    TABLE_TYPE, 
    ENGINE
FROM    INFORMATION_SCHEMA.TABLES
WHERE    TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
        AND ENGINE != 'InnoDB';

4. 生成修改存储引擎的SQL语句

以下查询用于生成将表从非InnoDB引擎切换为InnoDB引擎的SQL语句。

SELECT    CONCAT('alter table ', TABLE_SCHEMA, '.', TABLE_NAME, ' engine=InnoDB;') AS alter_sql
FROM    INFORMATION_SCHEMA.TABLES
WHERE    TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
        AND ENGINE != 'InnoDB';

5. 查看指定数据库的表信息

以下查询用于查看指定数据库中的表详细信息。

SET @table_schema := 'employees';
SELECT    table_name, 
    table_type, 
    engine, 
    table_rows, 
    avg_row_length, 
    data_length, 
    index_length, 
    table_collation, 
    create_time
FROM    information_schema.tables
WHERE    table_schema = @table_schema
ORDER BY    table_name;

6. 查看数据库会话连接信息

以下查询用于查看当前数据库的会话连接信息。

SELECT    THREAD_ID, 
    name, 
    type, 
    PROCESSLIST_ID, 
    PROCESSLIST_USER AS user, 
    PROCESSLIST_HOST AS host, 
    PROCESSLIST_DB AS db, 
    PROCESSLIST_COMMAND AS cmd, 
    PROCESSLIST_TIME AS time, 
    PROCESSLIST_STATE AS state, 
    PROCESSLIST_INFO AS info, 
    CONNECTION_TYPE AS type, 
    THREAD_OS_ID AS os_id
FROM    performance_schema.threads
WHERE    type = 'foreground'
ORDER BY    THREAD_ID;

7. 查看数据库字符集和排序规则

以下查询用于查看数据库支持的字符集和排序规则。

SELECT * FROM INFORMATION_SCHEMA.CHARACTER_SETS
WHERE CHARACTER_SET_NAME LIKE 'utf%';
SHOW CHARACTER_SET LIKE 'utf%';
SELECT * FROM INFORMATION_SCHEMA.COLLATIONS
WHERE COLLATION_NAME LIKE 'utf%';
SHOW COLLATION LIKE 'utf%';

8. 查看表结构定义信息

以下查询用于查看指定表的结构定义信息。

SELECT    table_name, 
    COLUMN_NAME, 
    ordinal_position, 
    DATA_TYPE, 
    IS_NULLABLE, 
    COLUMN_DEFAULT, 
    column_type, 
    column_key, 
    character_set_name, 
    collation_name
FROM    INFORMATION_SCHEMA.COLUMNS
WHERE    table_name = 'employees'
        AND table_schema = 'employees';
show columns from employees;
DESCRIBE employees.employees;

9. 查看数据库支持的引擎

以下查询用于查看数据库支持的引擎类型。

SELECT * FROM INFORMATION_SCHEMA.ENGINES;
SHOW ENGINES;

10. 查看数据库数据文件信息

以下查询用于查看数据库的数据文件信息，包括文件大小和使用情况。

SELECT    FILE_ID, 
    FILE_NAME, 
    FILE_TYPE, 
    TABLESPACE_NAME, 
    FREE_EXTENTS, 
    TOTAL_EXTENTS, 
    ((TOTAL_EXTENTS - FREE_EXTENTS) * EXTENT_SIZE) / 1024 / 1024 AS MB_used, 
    EXTENT_SIZE, 
    INITIAL_SIZE, 
    MAXIMUM_SIZE, 
    AUTOEXTEND_SIZE, 
    DATA_FREE, 
    STATUS, 
    ENGINE
FROM    INFORMATION_SCHEMA.FILES;

11. 查看指定表的约束信息

以下查询用于查看指定表的约束信息，包括外键约束。

SELECT    constraint_schema, 
    table_name, 
    constraint_name, 
    column_name, 
    ordinal_position, 
    CONCAT(table_name, '.', column_name, ' -> ', referenced_table_name, '.', referenced_column_name) AS list_of_fks
FROM    information_schema.KEY_COLUMN_USAGE
WHERE    REFERENCED_TABLE_SCHEMA = 'employees'
        AND REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY    TABLE_NAME, COLUMN_NAME;

12. 查看分区表信息

以下查询用于查看数据库中的分区表信息。

SELECT    TABLE_SCHEMA, 
    table_name, 
    partition_name, 
    subpartition_name sub_par, 
    partition_ordinal_position par_position, 
    partition_method method, 
    partition_expression expression, 
    partition_description description, 
    table_rows
FROM    information_schema.PARTITIONSWHERE    
    table_schema = 'test'
        AND table_name = 't';

13. 查看数据库插件信息

以下查询用于查看数据库支持的插件信息。

SELECT  PLUGIN_NAME, PLUGIN_STATUS, PLUGIN_TYPE, PLUGIN_LIBRARY, PLUGIN_LICENSE
FROM INFORMATION_SCHEMA.PLUGINS;
SHOW PLUGINS;

14. 查看数据库连接信息

以下查询用于查看数据库中的连接信息，包括会话状态和命令。

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;
SHOW FULL PROCESSLIST;

15. 查看存储过程和函数

以下查询用于查看数据库中的存储过程和函数。

SELECT    ROUTINE_SCHEMA, 
    routine_name, 
    ROUTINE_TYPE, 
    data_type, 
    routine_body, 
    routine_definition, 
    routine_comment
FROM    INFORMATION_SCHEMA.ROUTINES
WHERE    ROUTINE_TYPE = 'PROCEDURE'
        AND ROUTINE_SCHEMA = "employees";

16. 查看数据库大小和存储情况

以下查询用于查看数据库中的表大小和存储情况。

SELECT    table_schema 'database', 
    CONCAT(ROUND(SUM(data_length + index_length) / (1024 * 1024), 
                2), 'M') size
FROM    information_schema.TABLESWHERE    
    ENGINE in ('MyISAM', 'InnoDB')
GROUP BY table_schema
ORDER BY size DESC;

17. 查看表大小和存储情况

以下查询用于查看指定表的大小和存储情况。

SELECT    CONCAT(table_schema, '.', table_name) table_name, 
    CONCAT(ROUND(data_length / (1024 * 1024), 2), 'M') data_length, 
    CONCAT(ROUND(index_length / (1024 * 1024), 2), 'M') index_length, 
    CONCAT(ROUND(ROUND(data_length + index_length) / (1024 * 1024), 2), 'M') total_size
FROM    information_schema.TABLESWHERE    
    table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys', 'employees')
ORDER BY    data_length DESC;

以上查询和信息均基于MySQL INFORMATION_SCHEMA数据库提供的系统视图，适用于对数据库性能和结构进行全面评估和优化。

转载地址：http://itqfk.baihongyu.com/

你可能感兴趣的文章