数据库查询优化

转：https://www.cnblogs.com/personsiglewine/p/13199007.html

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 ：
3. key_buffer_size ： Index blocks for MyISAM tables are buffered and are shared
   by all threads ，可配置为可用内存的 10-%-20%
4. 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 加大日志文件和日志缓存ÿ