mysql 优化（2）

--cfq,deadline,noop
--radi10,xfs,ext4
--innodb_flush_log_at_trx_commit=0/1/2
--sys_binlog=1/n

CPU的利用特点
• <5.1，多核心支持较弱
• 5.1，可利用4个核
• 5.5，可利用24个核
• 5.6，可利用64个核
• 每个连接对应一个线程，每个并发query只能使用到一个核

内存利用特点
• 类似ORACLE的SGA、PGA模式，注意PGA不宜分配过大
• 内存管理简单、有效。在高TPS、高并发环境下，可增加物理内存以减少物理IO，提高并发性能
• 官方分支锁并发竞争比较严重，MariaDB、Percona进行优化
• 有类似ORACLE library cache的query cache，但效果不佳，建议关闭
• 执行计划没有缓存（类似ORACLE的library cache）
• 通常内存建议按热点数据总量的15%-20%来规划，专用单实例则可以分配物理内存的50~70%左右
• 类似K-V简单数据，采用memcached、Redis等NOSQL来缓存

SQL设计优化建议
• 简单SQL比复杂SQL高效
• 业务逻辑封装成存储过程
• 尽量不用子查询
• 减少使用复杂表连接
• 查询条件中不使用函数
• 用括号确定AND、OR优先级
• 查询条件加引号可能导致类型转换
• 不用FOR UPDATE和LOCK IN SHARE MODE，防止锁扩大

适合redis、memcached的场景：
1、频繁重复的简单，数据变更很少的SQL
2、计数器类统计，每次++；
3、缓存一些不经常变化的数据；
4、频繁数据库查询结果缓存；
5、业务端临时处理结果，汇总后批量回写；
6、空结果缓存，避免数据库雪崩；

适合mongodb的场景：
1、灵活schema需求；
2、LBS应用；--地理位置应用
3、长内容缓存；
4、日志存储、分析；
5、节点快速扩展；
6、存储BSON数据；

通过引入应用层优化
• SELECT * =&get; SELECT `cols`
• ORDER BY x LIMIT largenum, n =&get; 子查询，用主键等方式关联
• LIMIT largenum，真的需要吗，可能会把网卡打满
• 消减不必要的业务
• 动态改造成静态，或者降低更新频率
• 使用连接池
• 多个读节点负载均衡

该表有2个索引：--where 后面 in在有order by的时候，转换为or
KEY `index1` (`emp_no`,`from_date`),
KEY `index2` (`emp_no`)

** select * from salaries where emp_no = 10001 order by from_date --可以完全利用联合索引，emp_no用于索引过滤，同时from_date可以利用索引完成排序
** select * from salaries where emp_no in(10001, 10010) order by from_date --不可以完全利用联合索引，in在执行过程中被转换成or，只有emp_no可用于索引过滤，from_date无法利用索引完成排序
(system@127.0.0.1:3306) [test]> explain select *from test.t1 where c3 in ('A','C') order by c4 desc;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | idx_c3_c4     | idx_c3_c4 | 195     | NULL |    2 |   100.00 | Using index condition; Using filesort |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

(system@127.0.0.1:3306) [test]> explain select *from test.t1 where c3 ='C' order by c4 desc;
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ref  | idx_c3_c4     | idx_c3_c4 | 195     | const |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

(system@127.0.0.1:3306) [test]> explain select *from test.t1 where c3 in ('A','C');
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t1    | NULL       | range | idx_c3_c4     | idx_c3_c4 | 195     | NULL |    2 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+

【重要结论】使用索引的ORDER BY
下列的几个查询都会使用索引来解决 ORDER BY 或 GROUP BY 部分：
SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;
SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2;
SELECT * FROM t1 WHERE key_part1=constant GROUP BY key_part2;
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;
SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;

不完全使用到整个索引的ORDER BY：
* 对不同的索引键做 ORDER BY ：
SELECT * FROM t1 ORDER BY key1, key2; -- 无法使用任何一个索引完成排序
* 在非连续的索引键部分上做 ORDER BY：
SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2; --无法利用key_part2进行排序
* 同时使用了 ASC 和 DESC：
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC; --只能利用key_part1进行排序
* 用于搜索记录的索引键和做 ORDER BY 的不是同一个：
SELECT * FROM t1 WHERE key2=constant ORDER BY key1; -- 自动选择key1、key2中效果较好的一个，且未必准确
* 如果在WHERE和ORDER BY的栏位上应用表达式(函数)时，则无法利用索引来实现order by的优化
SELECT * FROM t1 ORDER BY YEAR(logindate) LIMIT 0,10;
* 多表连接时，ORDER BY 中的字段不都全是来自第一个const表中（也就是说，在 EXPLAIN 分析的结果中的第一个表的连接类型不是 const）。
* ORDER BY 和 GROUP BY 表达式不一样，不能用到同一个索引。
* 表索引中的记录不是按序存储。例如，HASH 和 HEAP 表就是这样

如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。
如果不能，可以尝试下面的策略：
• 增加sort_buffer_size变量的大小。
• 增加read_rnd_buffer_size变量的大小。
• 更改tmpdir指向具有大量空闲空间的专用文件系统，并且/tmp挂载在虚拟共享内存上，用tmpfs，效率更高。

满足GROUP BY子句的最一般的方法是扫描整个表并创建一个新的临时表，表中每个组的所有行应为连续的，然后使用该临时表来找到组并应用累积函数(如 果有)。
用通俗的语言就是，建立一个临时表。然后利用mysql内部算法。算出来结果
一般GROUP BY 优化分为2种优化方式：
1、松散索引扫描
2、紧凑索引扫描
松散索引扫描其实就是:
通过该访问方法，MySQL使用某些关键字排序的索引类型的 属性。该属性允许使用 索引中的查找组而不需要考虑满足所有WHERE条件的索引中的所有关键字。既然该访问方法只考虑索引中的关键字的一小 部分，它被称为松散索引列表。
例如：
desc select emp_no from salaries where emp_no > 4096 group by emp_no;
这里的Using index for group-by 表示松散索引
using tempoary; using filesort
紧凑索引扫描，也就是索引扫描或一个范围索引扫描，取决于查询条件。其实就是联合索引的应用。
select emp_no from salaries where emp_no > 490060 and from_date > '2010-01-01' group by emp_no;
2、在非连续的索引键部分上做 group by
SELECT * FROM t1 WHERE key2=constant group by key_part2;

满足松散索引扫描条件：
• 查询针对一个表。
• GROUP BY 使用索引的最左前缀。
• 只可以使用MIN()和MAX()聚集函数，并且它们均指向相同的列。

【tips】
MySQL中如果使用GROUP BY会自动包含一个ORDER BY 操作。--8.0取消
但是如果不需要的话，则会比较消耗资源。
那么，在整个语句最后加上ORDER BY NULL，就可以绕开这个没有必要的操作了。

强制主键：| Handler_read_rnd_next | 11551615 | -- 不走索引，相当于是随机扫描
强制二级索引：| Handler_read_next | 11551631 | -- 走indx_createDate_giftId索引，相当于是顺序扫描