?

五、MYSQL IN 和 EXISTS的优化规则：

当B表的数据集小于A表的数据集时，用in优于exists，当A表的数据集系小于B表的数据集时，用exists优于in。

优化原则：in小表驱动大表，即小的数据集驱动大的数据集。

示例select * from A where id in(select if from B)等价于for select * from A where B 和for select * from A where A.id = B.id。

当B表的数据集必须小于A表的数据集时，用in优于exists。

select * from A where exists (select 1 from B where B.id = A.id)

当A表的数据集系小于B表的数据集时，用in优于exists。

?

六、mysql order by 语句用法与优化详解：

order by keyword 是用来给记录中的数据根据关键字进行分类的。

SELECT _name(s) FROM table_name ORDER BY column_name。

1).ORDER BY的索引优化。

SELECT [column1],[column2],...FROM[TABLE] ORDER BY [sort];

2).WHERE+ORDER BY的索引优化。

SELECT [column1],[column2],...FROM[TABLE] WHERE [CcolumnX] = [value] ORDER BY [sort];

建立一个联合索引(columnX,sort)来实现order by优化。如果columnX对应多个值，就无法用上面的索引来实现order by的优化。

3).WHERE+多个字段ORDER BY

SELECT * FROM [TABLE] WHERE uid = 1 ORDER BY x,y LIMIT 0,10；

建立索引(uid,x,y)实现order by的优化，比建立(x,y,uid)索引效果要要得多。

在有些情况下，mysql可以使用一个索引来满足order by子句，而不需要额外的排序。where 条件和order by使用相同的索引，并且order by的顺序和索引顺序相同，并且order by的字段都是升序或者都是降序。

select * from t1 order by key_part1,keypart2,...;

select * from t1 where key_part1 = 1order by key_part1 DESC,key_part2 DESC；

select * from t1 where order by key_part1 DESC,key_part2 DESC;

但是一下情况不适用索引：

①SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC；
--order by的字段混合ASC和DESC
②SELECT * FROM t1 WHERE key2=constant ORDER BY key1；
--用于查询行的关键字与ORDER BY中所使用的不相同
③SELECT * FROM t1 ORDER BY key1, key2；
--对不同的关键字使用ORDER BY

?

七、InnoDB基本优化配置：

InnoDB设置

1.innodb_buffer_pool_size —— 默认值为 128M. 这是最主要的优化选项,因为它指定 InnoDB 使用多少内存来加载数据和索引(data+indexes). 针对专用MySQL服务器,建议指定为物理内存的 50-80%这个范围. 例如,拥有64GB物理内存的机器,缓存池应该设置为50GB左右.
如果将该值设置得更大可能会存在风险,比如没有足够的空闲内存留给操作系统和依赖文件系统缓存的某些MySQL子系统(subsystem),包括二进制日志(binary logs),InnoDB事务日志(transaction logs)等.

2.innodb_log_file_size —— 默认值为 48M. 有很高写入吞吐量的系统需要增加该值以允许后台检查点活动在更长的时间周期内平滑写入,得以改进性能. 将此值设置为4G以下是很安全的. 过去的实践表明,日志文件太大的缺点是增加了崩溃时所需的修复时间,但这在5.5和5.6中已得到重大改进.

3.innodb_flush_method —— 默认值为 fdatasync. 如果使用 硬件RAID磁盘控制器, 可能需要设置为 O_DIRECT. 这在读取InnoDB缓冲池时可防止“双缓冲(double buffering)”效应,否则会在文件系统缓存与InnoDB缓存间形成2个副本(copy).
如果不使用硬件RAID控制器,或者使用SAN存储时, O_DIRECT 可能会导致性能下降.MySQL用户手册 和 Bug #54306 详细地说明了这一点.

4.innodb_flush_neighbors —— 默认值为 1. 在SSD存储上应设置为0(禁用) ,因为使用顺序IO没有任何性能收益. 在使用RAID的某些硬件上也应该禁用此设置,因为逻辑上连续的块在物理磁盘上并不能保证也是连续的.

5.innodb_io_capacity and innodb_io_capacity_max —— 这些设置会影响InnoDB每秒在后台执行多少操作. 如果你深度了解硬件性能(如每秒可以执行多少次IO操作),则使用这些功能是很可取的,而不是让它闲着.

有一个很好的类比示例: 假如某次航班一张票也没有卖出去 —— 那么让稍后航班的一些人乘坐该次航班,有可能是很好的策略,以防后面遇到恶劣的天气. 即有机会就将后台操作顺便处理了,以减少同稍后可能的实时操作产生竞争.

有一个很简单的计算: 如果每个磁盘每秒读写(IOPS)可以达到 200次, 则拥有10个磁盘的 RAID10 磁盘阵列IOPS理论上 =(10/2)* 200 = 1000. 我说它“很简单”,是因为RAID控制器通常能够提供额外的合并,并有效提高IOPS能力. 对于SSD磁盘,IOPS可以轻松达到好几千.

将这两个值设置得太大可能会存在某些风险,你肯定不希望后台操作妨碍了前台任务IO操作的性能. 过去的经验表明,将这两个值设置的太高,InnoDB持有的内部锁会导致性能降低(按我了解到的信息,在MySQL5.6中这得到了很大的改进).

innodb_lru_scan_depth - 默认值为 1024. 这是mysql 5.6中引入的一个新选项. Mark Callaghan 提供了 一些配置建议. 简单来说,如果增大了 innodb_io_capacity 值, 应该同时增加 innodb_lru_scan_depth.

复制(Replication)

假如服务器要支持主从复制,或按时间点恢复,在这种情况下,我们需要:

1.log-bin —— 启用二进制日志. 默认情况下二进制日志不是事故安全的(not crash safe),但如同我 以前的文章所说, 我建议大多数用户应该以稳定性为目标. 在这种情况下,你还需要启用: sync_binlog=1, sync_relay_log=1, relay-log-info-repository=TABLE and master-info-repository=TABLE.