数据库查询优化的12种方式

查询优化方式：

 

 

1 硬件层的优化

1 CPU:
个数 / 核数 / 频率 / 线程数 / 一级 cache/ 二级 cache
2 内存 :
容量与 64-bits/ 带宽
3 I/O:
seek(>100 次 / 秒 )/read/write(>10–20MB/s)
4 网络 :
带宽 / 传输协议

2 存储引擎优化

1 InnoDB ：
1) innodb_buffer_pool_size ： caching data and indexes in memory ，可配置为
可用内存的（非物理内存）的 50%--60%
2) innodb_buffer_pool_instances ：配合 innodb_buffer_pool_size 使用，把 buf
分区，增加并发度
2 MyISAM ：
1) key_buffer_size ： Index blocks for MyISAM tables are buffered and are shared
by all threads ，可配置为可用内存的 10-%-20%
2) table_open_cache ： The number of open tables for all threads. 使用“ SHOW
GLOBAL STATUS LIKE 'Opened_tables';” 命令检查“ Opened_tables” 的值，太
小则改大

秘诀：
磁盘类数据库，瓶颈在 IO ；
必须优先调整和 IO 有关的参数。
解决 IO 瓶颈的方法，就是缓存；
必须优先调整和缓存相关的参数。

3 表设计优化

1 表的存储引擎选择：事务型选 InnoDB ，非事务型选 MyISAM 等
2 表的压缩选择：压缩的表 IO 少， CPU 空闲 IO 瓶颈大课采取压缩
3 表结构符合第三范式：更新类应用可让表的个数多些单表的列少一些；分析类的应用可让
表个数少些单表的列多些
4 表数据物理分布策略：尽量让表的数据分散在不同的物理存储设备上。利用表空间技术把
数据分散
5 表数据逻辑分布策略：利用分区技术把数据从逻辑上即分开
6 表的数据类型选择：数字类型优于字符类型；长度小的优于长度大的。变长的 VARCHAR
优于定长的 CHAR 。 BLOB 类型用 BINARY VARCHAR 替代，替代不了则用单独的表存放。
如果比较 BLOB 类，则新建字段其值等于用 MD5() 处理后的结果。 BOLB 类型甚至不存放到
数据库内部，数据库只存储 BLOB 的路径。
7 启用完整性约束：使用 NOT NULL 标识字段 ; 设置 default value 。
8 其他：列名不超过 18 个字符。使用 sample character set （如用 latin1 ，尽量少用 utf-8
等，因为 utf-8 等占用的空间是 latin1 的 3 倍）

4 InnoDB 优化

1 单表容量优化： OPTIMIZE TABLE statement to reorganize the table and
compact any wasted space 。
2 单表统计数据优化： ANALYZE TABLE tpch.customer;
3 启用压缩：测试各级压缩哪种有效
4 应用尽量使用短事务减少使用长事务：应用程序控制
5 事务管理：
5.1 写操作多： SET AUTOCOMMIT=0 or a START TRANSACTION statement,
followed by a COMMIT statement after making all the changes.
5.2 读操作多： SET AUTOCOMMIT=1
6 加大日志文件和日志缓存： innodb_log_buffer_size + innodb_log_file_size

7 主键建立：使用最重要且是最常用的列作主键，务必建立主键而不是使用
InnoDB 默认方式
8 主键列的数据类型不要太长：浪费存储空间和内存，浪费其他索引的空间和内存
9 有效建立索引：除主键外，尽量建立联合索引而不是多个单列上分别建立
secondary index
10 删除数据释放空间： Use TRUNCATE TABLE to empty a table, not DELETE
FROM tbl_name.
11 数据刷出的方式： In some versions of GNU/Linux and Unix, flushing files to
disk with the Unix fsync() call (which InnoDB uses by default) and similar methods
is surprisingly slow. If database write performance is an issue, conduct
benchmarks with the innodb_flush_method parameter set to O_DSYNC.
其他刷出方式参考： optimization.html#optimizing-innodb-storage-layout

5 库级优化

5.1 同一个库中表不要太多：设置 table_open_cache 和 max_connections 来调整。
With MyISAM tables, one extra file descriptor is required for the data file for each client that has the table open. (By contrast, the index file descriptor is shared
between all sessions.)

5.2 启用查询缓存： 适用于特定场景 .
If you often have recurring queries for tables that are not updated frequently, enable the query cache:
[mysqld]
query_cache_type = 1
query_cache_size = 10M

5.3 使用长连接： 避免频繁使用短连接 .
推荐启用连接池 .
设置 thread_cache_size ： 8 + (max_connections / 100) <--default value This variable can be increased to improve performance if you have a lot of new
connections. Normally, this does not provide a notable performance improvement if you have a good thread implementation. However, if your server sees hundreds of connections per second you should normally set thread_cache_size high enough so that most new connections use cached threads.

5.4 主从架构： 复制技术， master 完成写操作， slave 完成读操作 .
1) 优化读写操作
2) 提高备份速度，减少对 master 的影响

6 数据获取方式的优化

1 一次获取的数据尽量少：查询获取数据 , 尽量带 WHERE 条件精确指定获取范
围 , 且一次获取的数据量要少（应用层开发阶段必须注意）
数据获取，遵循的基本原则：
--- 少：不要全表扫描。要用什么取什么
--- 准：带 where 条件获取，用谁取谁
--- 快：加索引到 where 条件涉及的列上，用谁则快速取到谁
--- 减少关联：没有直接联系，不要硬拉郎配。减少耦合，减少关联。

2 不用 select * ： 臭名昭著，远远避之。
获取什么列就明确指定列名。
查询语句中出现的列名，最好是索引的一部分。
3 LIMIT x ：在满足应用需求的情况下可限制元组个数

7 利用索引优化

1 正确使用索引：

每条查询，使用 EXPLAIN 验证2 索引列做条件不参与运算： index_col <op> expression如 : col IN (value1, value2,...), col 是索引列，才可以利用索引加快数据
获取速度 . 操作符一侧的对象必须是不参与运算的索引列 .
3 精确化查找条件：单表不做全部数据读取，多表连接不做全表扫描，务必
带有 WHERE 子句限制数据， WHERE 子句中列对象有合适的索引可用
4 等式 / 不等式的一些推理人工完成：
a>b AND b>3 ==> a>3 AND b>3 AND a>b
a 列上有索引且选择率低
5 求最值建索引：

单表求 MIN/MAX/COUNT(*) 的操作，最好在对应列上建立索引

6 GROUPBY 、 ORDERBY 、 DISTINCT 作用在索引列上：
6.1 利用索引进行 GROUPBY 、 ORDERBY 、 DISTINCT 操作（考虑在这些谓词
后面的对象上建立索引）
6.2 避免使用随机数等不确定的对象做排序分组操作，如不用： ORDER BY RAN
D()
7 建立主外键关系：尽管 MySQL 目前不支持带有主外键关系的表连接优
化
（好处：符合第三范式，表明实体已经被拆分为小表，有利于减少读取的数据量）

8 引入新列：在表上增加新列，并在其上创建索引
SELECT * FROM tbl_name
WHERE hash_col=MD5(CONCAT(col1,col2))
AND col1='constant' AND col2='constant';
BLOB 的比较，也可以使用同样的方式。
9 存在范围查找，建立 Btree 索引：默认情况下是 Btree 。
10 索引类型：尽量使用主键索引 / 唯一索引。

11 创建索引 : 索引的键值类型越短越好。在数值型列上创建索引最好。
12 少建索引：对 InnoDB ，主键不可用时，尽量用联合索引替换多个单
列的 second index 。
13 删除索引：删除使用很少的索引。
14 只读索引： Covering Indexes ，覆盖索引。 查询语句中出现的对象
尽量限制在单个索引的全部列中。
15 前缀索引：尽量使用索引的前缀部分。
16 通配符：字符型索引列参与比较，另外各一个操作符前不用通配符。
key LIKE ‘%abc’

17 强制索引：特定情况强制使用指定的索引
index_hint:
USE {INDEX|KEY}
[FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
| IGNORE {INDEX|KEY}
[FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
| FORCE {INDEX|KEY}
[FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
示例：
http://blog.163.com/li_hx/blog/static/183991413201461853637715/

18 索引误区：
1) 不使用 NOT IN 和 <> 操作
NOT IN 和 <> 操作都不会使用索引将进行全表扫描。
mysql> explain extended select key1 from tk1 where key1 <> 2;
+----+-------------+-------+-------+---------------+--------+---------+------+------+----------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra
|
+----+-------------+-------+-------+---------------+--------+---------+------+------+----------
| 1 | SIMPLE | tk1 | range | tk1_i1 | tk1_i1 | 5 | NULL | 1000 | 100.00 | Using where;
Using index |
+----+-------------+-------+-------+---------------+--------+---------+------+------+----------
1 row in set, 1 warning (0.00 sec)
mysql> explain extended select * from tk1 where key1 <> 2;
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tk1 | ALL | tk1_i1 | NULL | NULL | NULL | 1001 | 99.90 | Using where |
+----+-------------+-------+------+---------------+------+----

 

2) 索引不会包含有 NULL 值的列
复合索引中只要有一列含有 NULL 值，那么这一列对于此复合索引就是无效的。所
以我们在数据库设计时不要让字段的默认值为 NULL 。
mysql> explain extended select key_part2 from tk1 where key_part2=2;
+----+-------------+-------+-------+---------------+----------+---------+------+------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | tk1 | index | NULL | tk1_i123 | 15 | NULL | 1000 | 100.00 | Using where; Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> insert into tk1 values(5000, 5000,5000,5000, NULL,5000,5000);
Query OK, 1 row affected (0.04 sec)
mysql> explain extended select key_part2 from tk1 where key_part2=2;
+----+-------------+-------+-------+---------------+----------+---------+------+------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | tk1 | index | NULL | tk1_i123 | 15 | NULL | 1001 | 100.00 | Using where; Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

8 暂存中间结果的三种优化方式

1 利用物化视图临时储存确定的查询结果 / 统计结果：
create algorithm=temptable view my_view as
select * from t1, t2;
select a, b from my_view where ...;
2 或者把结果永久储存到特定的表中 :
create table my_result select * from t1, t2;
3 或者把结果暂时存在临时表中 :
sql_buffer_result ： If set to 1, sql_buffer_result forces results from SELECT stat ements to be put into temporary tables. This helps MySQL free the table locks earl y and can be beneficial in cases where it takes a long time to send results to the client.

9 控制查询优化器

SET [GLOBAL|SESSION] optimizer_switch='command[,command]...';
batched_key_access={on|off}
block_nested_loop={on|off}
engine_condition_pushdown={on|off}
firstmatch={on|off}
index_condition_pushdown={on|off}
index_merge={on|off}
index_merge_intersection={on|off}
index_merge_sort_union={on|off}
index_merge_union={on|off}
loosescan={on|off}
materialization={on|off}
mrr={on|off}
mrr_cost_based={on|off}
semijoin={on|off}
subquery_materialization_cost_based={on|off}
use_index_extensions={on|off}

10 重新组织数据

1 物理数据分布 -- 重新组织表数据：
方法 1 ： OPTIMIZE TABLE
方法 2 ：特定表，常使用 'expr1, expr2’ 排序，则‘ ALTER TABLE ... ORDER BY
expr1, expr2’
2 查询优化逻辑使用的统计数据 -- 分析表数据 :
ANALYZE TABLE
3 查询优化逻辑使用的统计数据 -- 调整系统参数：
innodb_stats_transient_sample_pages ： The number of index pages to sample when estimating cardinality and other statistics for an indexed column, such as those calculated by ANALYZE TABLE. If your database has many large tables,consider using a higher value for innodb_stats_transient_sample_pages than if you have mostly smaller tables

11 连接技术

1 避免子查询：使用子查询上拉技术，人工优化子查询。
WHERE 子句中的 IN/ALL/ANY/SOME 子查询可以交给优化器优化
2 慎用不同的连接语义：慎用各种外连接、嵌套连接
3 明确连接条件 :
ON 子句指名连接列 , 尽量在主键和唯一键上做等值连接
WHERE 子句尽量利用索引中的主键索引和唯一索引
4 控制表的连接个数 :
7 表连接 , 可交给优化器处理
7 表以上的连接 , 考虑连接的必要性和连接条件

12 其他

1 使用语义优化技术：熟悉表结构和应用需求，利用语义优化技术书写
查询语句（设计阶段就需要开始考虑）
2 使用 hint: 强迫优化器变更行为（优化器不是万能的，多数时候可信）
3 查询语句中慎用函数：特别检察查询语句中是否使用了函数，尤其是
值不稳定的函数（对于每行元组，值总在变化），尽量不用