sql优化核心思想-罗炳森

SQL优化笔记

时间：2022.8.15

评价：好书，非常有用。
评分：9/10

第1章 SQL优化必懂概念

1. 基数 (Cardinality)

定义：MySQL 表中某一列的不同值的数量。
作用：基数越高，列越有成为索引的价值。MySQL 执行计划会基于基数选择索引。

查询基数：

desc dede_archives;
SELECT COUNT(DISTINCT id), COUNT(*), COUNT(DISTINCT mtype), COUNT(DISTINCT click)
FROM dede_archives;
SELECT click, COUNT(*)
FROM dede_archives
GROUP BY click
ORDER BY 2 DESC
LIMIT 10;
ANALYZE TABLE dede_archives; -- 主动更新基数
SHOW INDEX FROM dede_archives;

经验：查询返回数据量占总数据的 5% 以上走全表扫描，5% 以下走索引（非绝对）。

2. 选择性 (Selectivity)

定义：基数与总行数的比值乘以 100%。
观点：只有大表才会有性能问题。选择性大于 20% 的列通常需要创建索引。

查询字段：

SELECT COLUMN_NAME
FROM information_schema.COLUMNS
WHERE table_schema = 'kemiyacom_www' AND table_name = 'dede_archives';

查询行数：

SELECT TABLE_ROWS
FROM information_schema.TABLES
WHERE TABLE_NAME = 'dede_archives' AND table_schema = 'kemiyacom_www';

3. 自动化脚本

功能：统计字段是否适合创建索引。

脚本：gmysql.sh

#!/bin/bash
my_error() {
    echo "请按下面格式操作"
    echo "$0 -c dbname //create table temp.howto_create_index for dbname"
    echo "$0 -g dbname //gen cardinality for dbname"
    echo "$0 -s dbname //gen selectivity for dbname"
    echo "$0 -n dbname //print notice for dbname"
    exit
}

create_table() {
    dbname=$1
    mysql -e "create database if not exists temp;"
    mysql -e "drop table if exists temp.howto_create_index_for_$dbname;"
    mysql -e "create table temp.howto_create_index_for_$dbname (id int unsigned NOT NULL AUTO_INCREMENT,col_name varchar(30),table_name varchar(50),table_rows int unsigned,cardinality int unsigned,selectivity int unsigned,notice varchar(30),PRIMARY KEY (id) USING BTREE);"
    mysql -e "insert into temp.howto_create_index_for_$dbname (col_name,table_name) SELECT COLUMN_NAME,TABLE_NAME FROM information_schema.COLUMNS WHERE table_schema =\"$dbname\";"
    mysql -e "select TABLE_ROWS,TABLE_NAME from information_schema.TABLES where table_schema = \"$dbname\";" >/tmp/row.txt
    cat /tmp/row.txt | while read file; do
        myrows=$(echo $file | awk '{print $1}')
        mytable=$(echo $file | awk '{print $2}')
        mysql -e "update temp.howto_create_index_for_$dbname set table_rows=$myrows where table_name=\"$mytable\";"
    done
}

gen_cardinality() {
    dbname=$1
    mysql -e "select id,col_name,table_name from temp.howto_create_index_for_$dbname;" >/tmp/col.txt
    cat /tmp/col.txt | while read file; do
        myid=$(echo $file | awk '{print $1}')
        mycol=$(echo $file | awk '{print $2}')
        mytable=$(echo $file | awk '{print $3}')
        mysql -e "update temp.howto_create_index_for_$dbname set cardinality=(select count(distinct $mycol) from $dbname.$mytable) where id=$myid;"
    done
}

gen_selectivity() {
    dbname=$1
    mysql -e "update temp.howto_create_index_for_$dbname set selectivity=round(cardinality / table_rows * 100, 0);"
    mysql -e "update temp.howto_create_index_for_$dbname set notice=\"Need index\" where selectivity >=20;"
}

gen_notice() {
    dbname=$1
    mysql -e "select * from temp.howto_create_index_for_$dbname where notice=\"Need index\" and table_rows > 1000 and selectivity > 60 limit 10;"
}

if [ $# != 2 ]; then
    my_error
fi

case $1 in
    -c) create_table $2 ;;
    -g) gen_cardinality $2 ;;
    -s) gen_selectivity $2 ;;
    -n) gen_notice $2 ;;
    *) my_error ;;
esac

使用：

chmod +x gmysql.sh
./gmysql.sh -c kemiyacom_www # 建立元数据表
./gmysql.sh -g kemiyacom_www # 生成基数
./gmysql.sh -s kemiyacom_www # 生成选择性
./gmysql.sh -n kemiyacom_www # 打印推荐索引字段

4. 直方图 (Histogram)

作用：帮助优化器对基数低、数据分布不均衡的列进行更精确的 rows 估算。
适用场景：列出现在 WHERE 条件中且选择性小于 1%。
参考文档：MySQL Histogram

5. 回表 (Table Access by Index Rowid)

定义：通过索引记录的 rowid 访问表中的数据。
影响：回表次数过多会严重影响 SQL 性能。
优化建议：
- 查询的列包含在索引中时不需要回表。
- 使用组合索引消除回表。

示例：

-- 不回表
SELECT COUNT(*) FROM table;

-- 回表
SELECT * FROM table WHERE ...;

6. 表与表之间的关系

关系类型：
- 1:1：一对一。
- 1:N：一对多。
- N:M：多对多（需通过中间表实现）。

查询关系：

SELECT *
FROM emp e, dept d
WHERE e.deptno = d.deptno;

SELECT deptno, COUNT(*)
FROM emp
GROUP BY deptno
ORDER BY 2 DESC;

SELECT deptno, COUNT(*)
FROM dept
GROUP BY deptno
ORDER BY 2 DESC;

分析结果：emp 和 dept 是 N:1 的关系。

7. 优化案例

问题：LEFT JOIN 查询性能差。

SQL：

SELECT COUNT(*)
FROM a
LEFT JOIN b ON a.id = b.id;

优化：a 和 b 是 1:1 关系，直接查询 a 表。
```
SELECT COUNT(*) FROM a;
```

第2章统计信息

内容：略（全是 ORACLE 的内容）。

第3章执行计划

1. SQL 慢的原因

数据库参数设置不合理。
SQL 写法问题。
缺少索引。
优化器 BUG。
业务原因（如查询历史数据）。

2. 使用 `OPTIMIZER_TRACE`

启用：

SET SESSION OPTIMIZER_TRACE="enabled=on";
<statement to trace>;
SELECT * FROM information_schema.OPTIMIZER_TRACE;
SET SESSION OPTIMIZER_TRACE="enabled=off";

记录最后 5 条：

SET optimizer_trace_offset=-5, optimizer_trace_limit=5;
CALL stored_routine();
SELECT * FROM information_schema.OPTIMIZER_TRACE;

3. 使用 `EXPLAIN`

关注点：
- 逻辑读 (consistent gets)：越小越好。
- 行数据 (rows processed)：决定执行计划（如 HASH JOIN 或 NESTED LOOP）。

第4章访问路径

1. 访问路径类型

全表扫描 (FULL)：性能较差，扫描所有列。
ROWID 获取数据：性能最好。
范围扫描 (RANGE)：WHERE 条件包含 >= 或 <=。
回表 (BY INDEX)：通过索引访问表数据。
唯一索引扫描 (INDEX UNIQUE SCAN)：性能接近 BY USER ROWID。
索引范围扫描 (INDEX RANGE)：返回大量数据时需优化。
索引全扫描 (INDEX FULL SCAN)：大索引性能较差。
笛卡儿连接 (CARTESIAN JOIN)：无连接条件时产生，需添加条件。

2. 索引扫描 vs 全表扫描

经验：返回表中 5% 以内数据走索引，超过 5% 走全表扫描。

第5章表连接方式

1. 嵌套循环 (Nested Loops)

适用场景：关联数据量小。
优化建议：被驱动表必须走索引。

2. HASH 连接

适用场景：等值关联，返回大量数据。
优化建议：小表作为驱动表。

3. 排序合并连接 (Sort Merge Join)

适用场景：非等值关联（如 >, <, >=, <=）。
优化建议：等值关联改为 HASH JOIN。

4. 笛卡儿连接 (Cartesian Join)

优化建议：添加连接条件。

5. 标量子查询

优化建议：标量子查询可改为外连接。

6. 半连接 (Semi Join)

优化建议：优化 IN 和 EXISTS。

7. 反连接 (Anti Join)

优化建议：NOT IN 和 NOT EXISTS 改为外连接。

第6章成本计算

核心思想：减少 SQL 的物理 I/O 次数。

第7章查询变换

子查询非嵌套：消除 FILTER，减少 IN、EXISTS 的影响。

第8章调优技巧

1. 使用 `UNION` 代替 `OR`

示例：

SELECT *
FROM t1
WHERE owner = 'scott' OR object_id IN (SELECT object_id FROM t2);

改写为：

SELECT * FROM t1 WHERE owner = 'scott'
UNION
SELECT * FROM t1 WHERE object_id IN (SELECT object_id FROM t2);

2. 分页语句优化

错误分页：

SELECT *
FROM (SELECT t.*, ROWNUM rn FROM (-需要分页的 SQL-) t)
WHERE rn >= 1 AND rn <= 10;

正确分页：

SELECT *
FROM (SELECT *
      FROM (SELECT a.*, ROWNUM rn FROM (-需要分页的 SQL-) a)
      WHERE rownum <= 10)
WHERE rn >= 1;

3. 使用分析函数优化自连接

原 SQL：

SELECT ename, deptno, sal
FROM emp a
WHERE sal = (SELECT MAX(sal) FROM emp b WHERE a.deptno = b.deptno);

优化后：

SELECT ename, deptno, sal
FROM (SELECT a.*, MAX(sal) OVER (PARTITION BY deptno) max_sal FROM emp a)
WHERE sal = max_sal;

4. LIKE 语句优化

原 SQL：

SELECT * FROM t1 WHERE name LIKE '%gao%';

优化后：

CREATE TABLE index_t1 LIKE t1;
INSERT INTO index_t1 SELECT name, ROWID rid FROM t1;
SELECT *
FROM t1
WHERE ROWID IN (SELECT rid FROM index_t1 WHERE name LIKE '%gao%');

第9章 SQL优化案例

内容：略。

第10章全自动SQL审核

1. 查看没有索引的表

SELECT t.TABLE_SCHEMA, t.TABLE_NAME, t.TABLE_ROWS
FROM information_schema.TABLES t
LEFT JOIN information_schema.STATISTICS s
ON t.TABLE_SCHEMA = s.TABLE_SCHEMA AND t.table_name = s.TABLE_NAME AND s.INDEX_NAME = 'PRIMARY'
WHERE t.TABLE_SCHEMA NOT IN ('mysql', 'performance_schema', 'information_schema', 'sys')
AND TABLE_TYPE = 'BASE TABLE'
AND s.INDEX_NAME IS NULL
ORDER BY 3 DESC;

2. 查看没有主键的表

SELECT table_schema, table_name, TABLE_TYPE
FROM information_schema.tables
WHERE table_name NOT IN (
    SELECT DISTINCT table_name
    FROM information_schema.columns
    WHERE column_key = "PRI"
)
AND table_schema NOT IN ('mysql', 'information_schema', 'sys', 'performance_schema')
ORDER BY TABLE_TYPE, 1;

sql优化核心思想-罗炳森

SQL优化笔记

时间：2022.8.15

第1章 SQL优化必懂概念

1. 基数 (Cardinality)

2. 选择性 (Selectivity)

3. 自动化脚本

4. 直方图 (Histogram)

5. 回表 (Table Access by Index Rowid)

6. 表与表之间的关系

7. 优化案例

第2章 统计信息

第3章 执行计划

1. SQL 慢的原因

2. 使用 OPTIMIZER_TRACE

3. 使用 EXPLAIN

第4章 访问路径

1. 访问路径类型

2. 索引扫描 vs 全表扫描

第5章 表连接方式

1. 嵌套循环 (Nested Loops)

2. HASH 连接

3. 排序合并连接 (Sort Merge Join)

4. 笛卡儿连接 (Cartesian Join)

5. 标量子查询

6. 半连接 (Semi Join)

7. 反连接 (Anti Join)

第6章 成本计算

第7章 查询变换

第8章 调优技巧

1. 使用 UNION 代替 OR

2. 分页语句优化

3. 使用分析函数优化自连接

4. LIKE 语句优化

第9章 SQL优化案例

第10章 全自动SQL审核

1. 查看没有索引的表

2. 查看没有主键的表

第2章统计信息

第3章执行计划

2. 使用 `OPTIMIZER_TRACE`

3. 使用 `EXPLAIN`

第4章访问路径

第5章表连接方式

第6章成本计算

第7章查询变换

第8章调优技巧

1. 使用 `UNION` 代替 `OR`

第10章全自动SQL审核