常用

工具 https://github.com/percona/percona-toolkit

创建聚合索引

create index idx_all on tb_1(filed_1, filed_2);

查看正在执行的语句，并删除

# 查看
show processlist;

show full processlist;

# 根据状态查找，也可根据info查找，info是执行的sql语句
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND != 'Sleep';

# 删除
kill query 60581562;

分析

主要就是看type字段。

EXPLAIN sql;

查看没有建立索引的表

SELECT 
    table_schema AS `Database`,
    table_name AS `Table`
FROM 
    information_schema.tables
WHERE 
    table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys') -- 排除系统数据库
    AND table_type = 'BASE TABLE'
    AND NOT EXISTS (
        SELECT 
            1
        FROM 
            information_schema.statistics
        WHERE 
            table_schema = tables.table_schema
            AND table_name = tables.table_name
        LIMIT 1
    )
ORDER BY 
    table_schema, table_name;

简单的单表分区

适合单表业务，和其他表不存在逻辑关系

https://dev.mysql.com/doc/refman/8.4/en/partitioning-selection.html
https://www.squash.io/tutorial-on-database-sharding-in-mysql/

使用hash示例

# 创建
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT,
    customer_id INT,  -- 字符串类型
    order_date DATE,
    amount DECIMAL(10, 2),
    PRIMARY KEY (order_id, customer_id)
) ENGINE=InnoDB
PARTITION BY HASH customer_id
PARTITIONS 20;

# 插入
INSERT INTO orders (customer_id, order_date, amount) VALUES (1, '2023-05-01', 100.00);
INSERT INTO orders (customer_id, order_date, amount) VALUES (21, '2023-05-02', 150.00);
INSERT INTO orders (customer_id, order_date, amount) VALUES (42, '2023-05-03', 200.00);

# 查询
SELECT * FROM orders WHERE customer_id = 1;

查看每个分区的详情

SELECT 
    TABLE_NAME,
    PARTITION_NAME,
    PARTITION_ORDINAL_POSITION,
    PARTITION_METHOD,
    TABLE_ROWS,
    DATA_LENGTH,
    INDEX_LENGTH,
    PARTITION_COMMENT
FROM 
    information_schema.PARTITIONS
WHERE 
    TABLE_NAME = 'orders';  -- 替换为你的表名