SQL优化
. 常用指令的优化
- load导入大量数据时，
- DISABLE KEYS关闭唯一检索（适用于MyISAM）；
-
将导入的数据按主键的顺序排列；设置UNIQUE_CHECK=0；设置自动提交。
-
INSERT语句
- 使用多值数据，即value(),(),(),(),(),(),().. 减少与数据库的连接。
- 使用INSERT DELAYED，将数据放入内存队列中。
-
优化ORDER BY
-
慢查询相关
show variables like 'show_query_log'
set global slow_query_log=on;
set global slow_query_log_file='XX' //文件的位置
set global log_queries_not_using_indexes=on;//没有使用索引
set global long_query_time=1; //超过一秒日志分析：
mysqldumpslow -t 3 /var/log/mysql.log
//pt-query-digest
wget percona.com/get/pt-query-digest
chmod u+x pt-query-digest
mv /root/pt-query-digest /usr/bin/
pt-query-digest /var/log/mysql.log |more
//输出到文件
pt-query-digest slow-log >slow-log.report
//输出到数据库表
pt-query-digest --user=root –password=ouyc --review h=localhost,D=test,t=query_review--create-review-table slow.log优化方案
- 通过show status，show profiles等观察SQL执行频率, 占用时间长，IO及没命中索引的SQL。
- 索引问题，B树索引（大部分都支持），HASH索引（memory支持）。
法一：定期分析表和检查表。analyze table ; check
法二：定期优化表。optimize table - 通过Explain分析低效的sql语句。
type:const最好，ALL最差 - count (id>3 and null) as counter;
count()对null不计数。 - 子查询改成join形式，存在多对一时，使用distinct去重。
- limit 排序；
使用索引或者主键
先过滤，即使用where id >min and id <= max order by
优化数据对象
存储引擎：
MyISAM: 应用时以读和插入操作为主，只有少量的更新和删除，并且对事务的完整性，并发性要求不是很高的.
Innodb： 事务处理，以及并发条件下要求数据的一致性。除了插入和查询外，包括很多的更新和删除。
对于支持事务的InnoDB类型的表来说，影响速度的主要原因是AUTOCOMMIT默认设置是打开的，而且程序没有显式调用BEGIN 开始事务，导致每插入一条都自动提交。
可以在执行sql前调用begin，多条sql形成一个事物（即使autocommit打开也可以），将大大提高性能。
Memory：数据保存在RAM,快速访问数据。要求表不能太大或者对MySQL异常终止后不用恢复数据的。
数据类型
char范围是0～255，varchar最长是64k，但是注意这里的64k是整个row的长度，要考虑到其它的column，还有如果存在not null的时候也会占用一位，对不同的字符集，有效长度还不一样，比如utf8的，最多21845，还要除去别的column，但是varchar在一般情况下存储都够用了。如果遇到了大文本，考虑使用text，最大能到4G。
索引
索引应建立在那些将用于JOIN, WHERE判断和ORDER BY排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引。
MySQL server服务器配置优化
- key_buffer_size设置索引块的缓存大小
- table_cache数据库打开表的缓存数量 ，每个连接进来，都会至少打开一个表缓存。
因此table_cache和max_connections有关， 例如 对于200个并行运行的连接，应该让表的缓存至少是200 *N
N 是可以执行查询的一个连接中的表的最大数。 - innodb_buffer_pool_size等innodb参数的设置
磁盘IO优化
- RAID
- 使用符号链接 分布I/O
MYSQL在默认的情况下，数据库和数据表都存放在参数datadir定义的目录下，这样如果不使用RAID或者逻辑卷，所有的数据都存放在一个磁盘设备上，无法发挥多磁盘并 行读写的优势。
将表和数据库从数据库目录移动到其它的位置并且用指向新位置的符号链接进行替换。
- 推荐的方法只需要将数据库通过符号链接指到不同的磁盘。符号链接表仅作为是 最后的办法。符号链接一个数据库的方法是，首先在一些有空闲空间的硬盘上创建一个目录，然后从 MySQL 数据目录中创建它的一个符号链接。
应用优化
- 使用连接池
- 减少对mysql的访问，使用mem缓存等
- 负载均衡，复制分流查询操作
- 分布式cluster 数据库架构
分库分表
- 水平划分
- 垂直拆分
反范式设计（冗余结构设计）
反范式设计的概念 : 无外键，无连表查询。
便于分布式设计，允许适度冗余，为了容量扩展允许适度开销。
基于业务自由优化，基于i/o 或查询设计，无须遵循范式结构设计。
冗余结构设计所面临的典型场景
原有展现程序涉及多个表的查询，希望精简查询程序
数据表拆分往往基于主键，而原有数据表往往存在非基于主键的关键查询，无法在分表结构中完成。
存在较多数据统计需求（count, sum等），效率低下。
冗余设计方案
1）基于展现的冗余设计
2）基于展现的冗余设计
冗余表要点：
数据一致性，简单说，同增，同删，同更新。
可以做全冗余，或者只做主键关联的冗余，比如通过用户名查询uid，再基于uid查询源表。
