http://dev.mysql.com/doc/refman/5.6/en/optimizing-innodb-storage-layout.html
数据库优化学习心得:
数据库的优化主要在这几个方面，硬件优化、系统优化、RDBMS优化、数据库设计优化、数据库开发优化。
一、硬件优化
硬件是所有软件的载体是所有软件的基础。硬件优化效果最明显，最直接。主要包括以下几个方面：
1.CPU:个数/核数/频率/线程数/一级cache/二级cache
尤其在内存数据库里，性能的主要瓶颈在cpu上，在cpu的cache上。因为内存很快，cpu的cache落后于内存，cpu和内存不匹配了。
CPU在这几个参数上越大越好了。
2.内存:容量与64-bits/带宽。内存越大越好。
3.I/O:seek(>100次/秒)/read/write(>10–20MB/s)。
I/O一直是磁盘数据库的最大瓶颈，它的性能对数据库影响很明显，如果不考虑成本的话使用SSD硬盘当然是最好了。
4.网络:带宽/传输协议.
带宽也和重要尤其是集群数据库、分布式数据库，它们的瓶颈主要在网络带宽上。
二、系统优化
在os层numa关闭时，打开bios层的numa会影响性能，QPS会下降15-30%；
在bios层面numa关闭是，无论os层面的numa是否打开，都不会影响性能。
三、RDBMS优化
对于RDBMS库级别的主要是参数优化。对于MySQL而言主要有以下几方面的参数需要注意。
1.存储引擎优化:正确选择存储引擎
1)InnoDB：对于事物性系统要选择InnoDB存储引擎.尤其是业务上有并发事务，必须要选择InnoDB存储引擎。OLTP系统选择InnoDB存储引擎。
2)MyISAM：对于查询性系统要选择MyISAM存储引。业务上以读为主的系统，比如OLAP系统，要选择MyISAM存储引擎。
2.innodb_buffer_pool_size:
caching data and indexes in memory，可配置为可用内存的（非物理内存）的50% - 60%.
这个参数是必须要优化的。
3.innodb_buffer_pool_instances：
配合innodb_buffer_pool_size使用，把 buffer 分区，增加并发度.
将innodb_buffer_pool划分为不同的instance.即每个instance都是一个小的buffer_pool,每个instance都有独立的LRU、FLUSH、FREE.独立的mutex控制。
作用：降低锁的争用，降低LRU的查询。对于大内存，建议分出多个instance，减少锁，提高并发。
4.innodb_log_files_in_group：日志文件组个数
5.innodb_log_file_size：日志文件大小，设置大点好,写入减少check point,文件大小不能超过4G.
6.innodb_log_buffer_siz：
在写入innodb事务日志文件(innodb_log_file)之前,要先写入log buffer里,log buffer写满了之后再刷到磁盘文件里(log_file).以便提高性能.
所以这个参数不能太小了.有频繁的大事务应该增加innodb_log_buffer_size大小.
7.同一个库中表不要太多：受操作系统打开文件数限制。
设置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.)
8.启用查询缓存：适用于特定场景.并不一定有效，使用当中受到限制。建议不用。看上去很美好，其实不然。
设置query_cache_type和query_cache_size参数。
适用于,如果相同的查询很多，且DML操作不频繁。
If you often have recurring queries for tables that are not updated frequently,enable the query cache:
9.使用长连接：避免频繁使用短连接.推荐启用连接池组件。连接的创建和销毁是要大量消耗资源的。
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.
秘诀：
磁盘类数据库，瓶颈在IO；
必须优先调整和IO有关的参数。
解决IO瓶颈的方法，就是缓存；
必须优先调整和缓存相关的参数。
四、数据库设计优化
1. 逻辑设计优化：
优化ER图，以第三范式为标准，但是如果所有的表设计都严格按照第三范式反而会使性能大幅度下降。
因为第三范式虽然保证了数据的完整性和一致性，减少了冗余，但是在一些统计业务的场景下，一个简单查询业务往往需要关联多张表才能查询出结果，这样会招致性能的下降。
所以在某些查询业务场景下，需要在表上创建一些冗余的字段，来减少表的关联，从而提高性能，即用空间换时间。
但是冗余字段的创建是有讲究的，尽量是一些key。
表结构符合第三范式：更新类应用可让表的个数多些单表的列少一些；分析类的应用可让表个数少些单表的列多些。
2.物理设计优化：
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倍）
3. 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) 主键列的数据类型不要太长：浪费存储空间和内存，浪费其他索引的空间和内存。但是在集群环境下，建议使用bigint。
InnoDB里创建索引的时候，会自动把主键列补充到索引的后面。
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
4 架构优
主从架构配合读写分离：复制技术，master完成写操作，slave完成读操作.
1) 优化读写操作
2) 提高备份速度，减少对master的影响
五、数据库开发优化
1.数据获取方式的优化：
一次获取的数据尽量少：查询获取数据,尽量带WHERE条件精确指定获取范围,且一次获取的数据量要少（应用层开发阶段必须注意）数据获取，
遵循的基本原则：
少：不要全表扫描。要用什么取什么
准：带where条件获取，用谁取谁
快：加索引到where条件涉及的列上，用谁则快速取到谁
减少关联：没有直接联系，不要硬拉郎配。减少耦合，减少关联。
2.不用select *： 臭名昭著，远远避之。获取什么列就明确指定列名。查询语句中出现的列名，最好是索引的一部分。
3.LIMIT x：在满足应用需求的情况下可限制元组个数.
4.使用语义优化技术：熟悉表结构和应用需求，利用语义优化技术书写查询语句（设计阶段就需要开始考虑）.
5.使用hint: 强迫优化器变更行为（优化器不是万能的，多数时候可信）.
6.查询语句中慎用函数：特别检察查询语句中是否使用了函数，尤其是值不稳定的函数（对于每行元组，值总在变化），尽量不用.
7.连接技术优化
1)尽量避免子查询：使用子查询上拉技术，人工优化子查询。WHERE子句中的IN/ALL/ANY/SOME子查询可以交给优化器优化
2)慎用不同的连接语义：慎用各种外连接、嵌套连接.
3)明确连接条件:
ON子句指名连接列,尽量在主键和唯一键上做等值连接.
WHERE子句尽量利用索引中的主键索引和唯一索引.
4)控制表的连接个数:
7表连接,可交给优化器处理.
7表以上的连接,考虑连接的必要性和连接条件.
8.合理使用数据库临时表。
9.合理使用物化视图。
六、运维之表优化
1.重新组织数据
1)物理数据分布,重新组织表数据：
方法1：OPTIMIZE TABLE
方法2：特定表，常使用 expr1, expr2 排序，则 ALTER TABLE ... ORDER BY expr1, expr2
2)查询优化逻辑使用的统计数据,分析表数据:
ANALYZE TABLE
OPTIMIZE TABLE copies the data part of the table and rebuilds the indexes. The benefits come from improved packing of data within indexes, and reduced fragmentation within the tablespaces and on disk. The benefits vary depending on the data in each table. You may find that there are significant gains for some and not for others, or that the gains decrease over time until you next optimize the table. This operation can be slow if the table is large or if the indexes being rebuilt do not fit into the buffer pool. The first run after adding a lot of data to a table is often much slower than later runs.
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,
onsider using a higher value for innodb_stats_transient_sample_pages than if you have mostly smaller tables
七、索引优化
索引优化既属于物理优化、开发优化又属于运维优化，这里单独提出。
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作用在索引列上：
1) 利用索引进行GROUPBY、ORDERBY、DISTINCT 操作（考虑在这些谓词后面的对象上建立索引）
2) 避免使用随机数等不确定的对象做排序分组操作，如不用：ORDER BY RAND()
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’