话不多说，直接上代码
-- 创建单列索引
CREATE INDEX idx_columns ON table_name (column_name);
-- 创建复合索引
CREATE INDEX idx_columns ON table_name (column1, column2, ...);
-- 创建唯一索引
CREATE UNIQUE INDEX idx_columns ON table_name (column_name);
-- 创建主键索引
ALTER TABLE table_name ADD PRIMARY KEY (column_name);
-- 全文索引
CREATE FULLTEXT INDEX idx_columns ON table_name (column_name);
-- 查看表的索引
SHOW INDEX FROM schema_db.table_name;
-- 通过信息模式查看索引
SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'schema_db' AND TABLE_NAME = 'table_name';
-- 删除普通索引
DROP INDEX index_name ON table_name;
-- 删除主键索引
ALTER TABLE table_name DROP PRIMARY KEY;
-- 更新索引信息
ANALYZE TABLE schema_db.table_name;
-- 查找未使用的索引
SELECT
OBJECT_SCHEMA,
OBJECT_NAME,
INDEX_NAME
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE INDEX_NAME IS NOT NULL
AND COUNT_STAR = 0
AND OBJECT_SCHEMA = 'schema_db';
-- 查看索引的使用统计（需要开启性能模式）
SELECT
OBJECT_SCHEMA,
OBJECT_NAME,
INDEX_NAME,
COUNT_READ,
COUNT_FETCH
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'schema_db'
ORDER BY COUNT_READ DESC;
-- 查看索引的选择性（选择性越高，索引效果越好）
SELECT
t.TABLE_NAME,
s.INDEX_NAME,
GROUP_CONCAT(s.COLUMN_NAME ORDER BY s.SEQ_IN_INDEX) AS INDEX_COLUMNS,
s.NON_UNIQUE,
s.CARDINALITY,
t.TABLE_ROWS,
ROUND(s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0) * 100, 2) AS selectivity_ratio,
s.INDEX_TYPE,
s.COMMENT
FROM INFORMATION_SCHEMA.TABLES t
JOIN INFORMATION_SCHEMA.STATISTICS s
ON t.TABLE_SCHEMA = s.TABLE_SCHEMA AND t.TABLE_NAME = s.TABLE_NAME
-- WHERE t.TABLE_SCHEMA = 'schema_db'
-- AND t.TABLE_TYPE = 'table_name'
GROUP BY t.TABLE_NAME, s.INDEX_NAME, s.NON_UNIQUE, s.CARDINALITY,
t.TABLE_ROWS, s.INDEX_TYPE, s.COMMENT
ORDER BY t.TABLE_NAME, s.INDEX_NAME;
-- 检查索引碎片率
SELECT
t.TABLE_NAME,
s.INDEX_NAME,
ROUND((t.DATA_LENGTH + t.INDEX_LENGTH) / 1024 / 1024, 2) AS total_size_mb,
ROUND(t.DATA_FREE / 1024 / 1024, 2) AS free_size_mb,
ROUND((t.DATA_FREE / (t.DATA_LENGTH + t.INDEX_LENGTH)) * 100, 2) AS fragmentation_percent
FROM INFORMATION_SCHEMA.TABLES t
JOIN INFORMATION_SCHEMA.STATISTICS s on t.TABLE_SCHEMA = s.TABLE_SCHEMA AND t.TABLE_NAME = s.TABLE_NAME
WHERE t.TABLE_SCHEMA = 'schema_db'
AND t.DATA_FREE > 0
HAVING fragmentation_percent > 10;
-- 优化碎片率高的表
OPTIMIZE TABLE schema_db.table_name;