tidb教程FG137-TiDB SQL优化最佳实践

fgedu.net.cn

一、基础概念

1.1 SQL优化定义

SQL优化是指通过调整SQL语句、索引设计、表结构等方式，提高SQL查询的执行效率，减少资源消耗，提升系统性能。

1.2 TiDB执行计划

执行计划是TiDB执行SQL语句的具体步骤，包括表扫描方式、连接方式、排序方式等。通过分析执行计划，可以识别SQL语句的性能瓶颈。

1.3 索引类型

主键索引：唯一标识表中的记录
唯一索引：确保列值唯一
普通索引：加速查询
组合索引：多个列的索引
覆盖索引：包含查询所需的所有列

1.4 执行计划操作类型

TableScan：全表扫描
IndexScan：索引扫描
IndexLookUp：索引查找
HashJoin：哈希连接
IndexJoin：索引连接
Sort：排序
Aggregate：聚合

二、规划建议

2.1 表结构设计

合理设计表结构：根据业务需求设计表结构
选择合适的数据类型：使用合适的数据类型，减少存储空间
避免使用大字段：大字段会影响查询性能
合理分表：对于大表，考虑分表处理

2.2 索引设计

创建合适的索引：根据查询需求创建索引
避免过多索引：过多索引会影响写入性能
使用组合索引：合理使用组合索引
考虑索引覆盖：使用覆盖索引减少回表操作

2.3 SQL语句设计

避免复杂查询：复杂查询会增加执行时间
使用参数化查询：减少SQL注入风险，提高性能
避免使用SELECT *：只查询需要的列
合理使用分页：避免一次性查询大量数据

三、实施方案

3.1 分析执行计划

查看执行计划

-- 查看执行计划
EXPLAIN SELECT * FROM fgedu_users WHERE age > 25;

-- 查看详细执行计划
EXPLAIN ANALYZE SELECT * FROM fgedu_users WHERE age > 25;

+-------------------------+----------+-----------+---------------+--------------------------------+| id                      | estRows  | task      | access object | operator info                  |+-------------------------+----------+-----------+---------------+--------------------------------+| TableReader_7           | 9900.00  | root      |               | data:Selection_6               | | └─Selection_6           | 9900.00  | cop[tikv] |               | gt(fgedu_users.age, 25)        |   └─TableFullScan_5      | 10000.00 | cop[tikv] | table:fgedu_users | keep order:false, stats:pseudo |+-------------------------+----------+-----------+---------------+--------------------------------+

分析执行计划

-- 分析执行计划中的关键信息
-- 1. 访问方式：TableFullScan表示全表扫描
-- 2. 估计行数：estRows表示估计的结果行数
-- 3. 执行任务：root表示在TiDB层执行，cop[tikv]表示在TiKV层执行
-- 4. 操作符信息：显示具体的操作信息

-- 创建索引后查看执行计划
CREATE INDEX idx_age ON fgedu_users(age);
EXPLAIN SELECT * FROM fgedu_users WHERE age > 25;

+-------------------------------+----------+-----------+---------------+--------------------------------+| id                            | estRows  | task      | access object | operator info                  |+-------------------------------+----------+-----------+---------------+--------------------------------+| IndexReader_6                 | 9900.00  | root      |               | index:IndexRangeScan_5         | | └─IndexRangeScan_5            | 9900.00  | cop[tikv] | table:fgedu_users, index:idx_age | range:(25,+inf], keep order:false |+-------------------------------+----------+-----------+---------------+--------------------------------+

风哥提示：

3.2 索引优化

创建合适的索引

-- 创建普通索引
CREATE INDEX idx_username ON fgedu_users(username);

-- 创建组合索引
CREATE INDEX idx_username_email ON fgedu_users(username, email);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON fgedu_users(email);

-- 查看索引
SHOW INDEX FROM fgedu_users;

+------------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+| Table      | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible |+------------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+| fgedu_users |          0 | PRIMARY          |            1 | id          | A         |       10000 |     NULL |   NULL |      | BTREE      |         |               | YES     | | fgedu_users |          1 | idx_age          |            1 | age         | A         |          20 |     NULL |   NULL |      | BTREE      |         |               | YES     | | fgedu_users |          1 | idx_username     |            1 | username    | A         |       10000 |     NULL |   NULL |      | BTREE      |         |               | YES     | | fgedu_users |          1 | idx_username_email |            1 | username    | A         |       10000 |     NULL |   NULL |      | BTREE      |         |               | YES     | | fgedu_users |          1 | idx_username_email |            2 | email       | A         |       10000 |     NULL |   NULL |      | BTREE      |         |               | YES     | | fgedu_users |          0 | idx_email        |            1 | email       | A         |       10000 |     NULL |   NULL |      | BTREE      |         |               | YES     |+------------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+

删除不需要的索引

-- 删除索引
DROP INDEX idx_age ON fgedu_users;

-- 查看索引
SHOW INDEX FROM fgedu_users;

+------------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+| Table      | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible |+------------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+| fgedu_users |          0 | PRIMARY          |            1 | id          | A         |       10000 |     NULL |   NULL |      | BTREE      |         |               | YES     | | fgedu_users |          1 | idx_username     |            1 | username    | A         |       10000 |     NULL |   NULL |      | BTREE      |         |               | YES     | | fgedu_users |          1 | idx_username_email |            1 | username    | A         |       10000 |     NULL |   NULL |      | BTREE      |         |               | YES     | | fgedu_users |          1 | idx_username_email |            2 | email       | A         |       10000 |     NULL |   NULL |      | BTREE      |         |               | YES     | | fgedu_users |          0 | idx_email        |            1 | email       | A         |       10000 |     NULL |   NULL |      | BTREE      |         |               | YES     |+------------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+

3.3 SQL语句优化

避免全表扫描

-- 不好的查询：全表扫描
SELECT * FROM fgedu_users WHERE age > 25;

-- 好的查询：使用索引
CREATE INDEX idx_age ON fgedu_users(age);
SELECT * FROM fgedu_users WHERE age > 25;

避免使用SELECT *

-- 不好的查询：使用SELECT *
SELECT * FROM fgedu_users WHERE username = 'admin';

-- 好的查询：只查询需要的列
SELECT id, username, email FROM fgedu_users WHERE username = 'admin';

避免在WHERE子句中使用函数

-- 不好的查询：在WHERE子句中使用函数
SELECT * FROM fgedu_users WHERE YEAR(created_at) = 2024;

-- 好的查询：避免使用函数
SELECT * FROM fgedu_users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

合理使用分页

-- 不好的查询：一次性查询大量数据
SELECT * FROM fgedu_users;

-- 好的查询：使用分页
SELECT * FROM fgedu_users LIMIT 10 OFFSET 0;
SELECT * FROM fgedu_users LIMIT 10 OFFSET 10;

使用参数化查询

-- 不好的查询：直接拼接SQL
SELECT * FROM fgedu_users WHERE username = 'admin';

-- 好的查询：使用参数化查询
PREPARE stmt FROM 'SELECT * FROM fgedu_users WHERE username = ?';
SET @username = 'admin';
EXECUTE stmt USING @username;
DEALLOCATE PREPARE stmt;

3.4 表结构优化

选择合适的数据类型

-- 不好的设计：使用过大的数据类型
CREATE TABLE bad_design (
    id INT,
    name VARCHAR(255),
    age INT,
    created_at DATETIME
);

-- 好的设计：使用合适的数据类型
CREATE TABLE good_design (
    id INT UNSIGNED,
    name VARCHAR(50),
    age TINYINT UNSIGNED,
    created_at TIMESTAMP
);

避免使用大字段

学习交流加群风哥QQ113257174

-- 不好的设计：使用大字段
CREATE TABLE bad_design (
    id INT,
    content TEXT,
    image LONGBLOB
);

-- 好的设计：分离大字段
CREATE TABLE good_design (
    id INT,
    content_summary VARCHAR(255),
    content_id INT
);

CREATE TABLE content_details (
    id INT,
    content TEXT,
    image LONGBLOB
);

3.5 监控和诊断

使用TiDB Dashboard

# 访问TiDB Dashboard
# 地址：http://tidb-server:10080/dashboard

# 查看慢查询
# 在TiDB Dashboard > 慢查询

# 查看执行计划
# 在TiDB Dashboard > SQL语句分析

使用SQL诊断

-- 启用SQL诊断
SET GLOBAL tidb_enable_stmt_summary = 1;

-- 查看SQL语句统计
SELECT * FROM information_schema.statements_summary WHERE query_sample_text LIKE '%fgedu_users%' ORDER BY exec_count DESC LIMIT 10;

-- 查看慢查询日志
SHOW VARIABLES LIKE 'slow_query_log';
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 0.1;

-- 查看慢查询日志内容
SHOW GLOBAL VARIABLES LIKE 'slow_query_log_file';
-- 查看对应文件内容

四、实战案例

4.1 慢查询优化

场景：业务系统中存在慢查询，影响系统性能。

步骤1：识别慢查询

-- 查看慢查询
SHOW GLOBAL VARIABLES LIKE 'slow_query_log';
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 0.1;

-- 查看慢查询日志文件
SHOW GLOBAL VARIABLES LIKE 'slow_query_log_file';

-- 分析慢查询日志
-- 例如：/tidb-data/tidb/slow-query.log

步骤2：分析执行计划

-- 示例慢查询
SELECT * FROM fgedu_users WHERE age > 25 AND created_at > '2024-01-01';

-- 查看执行计划
EXPLAIN SELECT * FROM fgedu_users WHERE age > 25 AND created_at > '2024-01-01';

+-------------------------+----------+-----------+---------------+--------------------------------+| id                      | estRows  | task      | access object | operator info                  |+-------------------------+----------+-----------+---------------+--------------------------------+| TableReader_7           | 4950.00  | root      |               | data:Selection_6               | | └─Selection_6           | 4950.00  | cop[tikv] |               | gt(fgedu_users.age, 25), gt(fgedu_users.created_at, 2024-01-01 00:00:00) |   └─TableFullScan_5      | 10000.00 | cop[tikv] | table:fgedu_users | keep order:false, stats:pseudo |+-------------------------+----------+-----------+---------------+--------------------------------+

步骤3：优化查询

-- 创建组合索引
CREATE INDEX idx_age_created_at ON fgedu_users(age, created_at);

-- 查看优化后的执行计划
EXPLAIN SELECT * FROM fgedu_users WHERE age > 25 AND created_at > '2024-01-01';

+----------------------------------+----------+-----------+---------------+--------------------------------+| id                               | estRows  | task      | access object | operator info                  |+----------------------------------+----------+-----------+---------------+--------------------------------+| IndexReader_6                    | 4950.00  | root      |               | index:IndexRangeScan_5         | | └─IndexRangeScan_5               | 4950.00  | cop[tikv] | table:fgedu_users, index:idx_age_created_at | range:(25,+inf], keep order:false, stats:pseudo |+----------------------------------+----------+-----------+---------------+--------------------------------+

步骤4：验证优化效果

-- 执行优化后的查询
SELECT * FROM fgedu_users WHERE age > 25 AND created_at > '2024-01-01';

-- 查看查询执行时间
EXPLAIN ANALYZE SELECT * FROM fgedu_users WHERE age > 25 AND created_at > '2024-01-01';

4.2 索引优化

场景：表中有大量数据，查询性能下降。

步骤1：分析表结构和索引

-- 查看表结构
DESC fgedu_users;

-- 查看索引
SHOW INDEX FROM fgedu_users;

-- 查看表统计信息
ANALYZE TABLE fgedu_users;

步骤2：识别索引使用情况

-- 查看索引使用情况
SELECT * FROM information_schema.statements_summary WHERE query_sample_text LIKE '%fgedu_users%' ORDER BY exec_count DESC LIMIT 10;

-- 查看未使用的索引
SELECT * FROM information_schema.statistics WHERE table_schema = 'fgedudb' AND table_name = 'fgedu_users' AND index_name NOT IN (SELECT DISTINCT index_name FROM performance_schema.statements_with_temp_tables WHERE table_schema = 'fgedudb' AND table_name = 'fgedu_users');

步骤3：优化索引

-- 删除未使用的索引
DROP INDEX idx_username ON fgedu_users;

-- 创建必要的索引
CREATE INDEX idx_email ON fgedu_users(email);
CREATE INDEX idx_created_at ON fgedu_users(created_at);

-- 查看优化后的索引
SHOW INDEX FROM fgedu_users;

五、经验总结

5.1 SQL优化最佳实践

分析执行计划：通过执行计划识别性能瓶颈
创建合适的索引：根据查询需求创建索引
优化SQL语句：避免全表扫描、使用SELECT *等
合理设计表结构：选择合适的数据类型，避免大字段
定期维护：定期分析表统计信息，重建索引
监控慢查询：及时发现和处理慢查询
使用参数化查询：减少SQL注入风险，提高性能
合理使用分页：避免一次性查询大量数据

5.2 索引设计最佳实践

优先考虑WHERE子句中的列：为WHERE子句中的列创建索引
考虑排序和分组列：为ORDER BY和GROUP BY中的列创建索引
使用组合索引：合理使用组合索引，遵循最左前缀原则
避免过多索引：过多索引会影响写入性能
考虑索引覆盖：使用覆盖索引减少回表操作
定期维护索引：定期重建索引，保持索引效率

5.3 常见问题与解决方案

问题	原因	解决方案
全表扫描	没有合适的索引	创建合适的索引
索引未使用	索引设计不合理、SQL语句不当	优化索引设计，调整SQL语句
慢查询	SQL语句复杂、索引不合理	优化SQL语句，创建合适的索引
性能下降	数据量增长、索引失效	定期维护索引，分析表统计信息
写入性能差	过多索引	删除不必要的索引

5.4 SQL优化检查清单

检查项	配置要求	状态
执行计划分析	定期分析执行计划	□
索引设计	创建合适的索引	□
SQL语句优化	优化SQL语句	□
表结构设计	合理设计表结构	□
慢查询监控	启用慢查询日志	□
定期维护	定期分析表统计信息	□
参数化查询	使用参数化查询	□
分页查询	合理使用分页	□

更多视频教程www.fgedu.net.cn

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html