mysql优化技巧

一、通过连表查询有时候通过子查询先刷选掉一部分数据后再进行查询有时候sql的执行性能会更高。
如下table1先筛掉大量数据后再进行关联查询

SELECT
	* 
FROM
	table1
	LEFT JOIN table2 ON table1.c_table2_id = table2.c_id 
GROUP BY
	table1.c_column1

SELECT
	* 
FROM
	( SELECT * FROM table1 GROUP BY table1.c_column1 ) AS t
	LEFT JOIN table2 ON t.c_table2_id = table2.c_id

以上优化为实际优化的例子。业务需求为：要将table1的多条数据分组并成一条数据导入到新表中（新表涉及到table2的字段）。当然以上sql实际上为简化版（省略了insert语句和其他的一些条件）。按照第一条sql执行了50秒还没执行完。第二条sql执行了1秒多就执行完毕。以上的优化和实际的业务相关。这样子的前提是table1的数据很多，而且条件又可以筛除掉table1的大量数据才能使用这条sql优化。

二、有时候mysql添加冗余条件能够提高查询的性能
怎么叫添加冗余条件呢？这个一般需要与实际的业务场景相结合。比如查询某个部门里某个人今天的考勤状态(t_attendance考勤表，里面有字段人员id、部门id、出勤情况、考勤时间…，索引有组合索引部门id-考勤时间、普通索引人员id)

SELECT
	* 
FROM
	t_attendance 
WHERE
	c_person_id = ***
	AND c_date = '***'

这条sql很明显走的是人员id的索引，但是随着天数的上升，这条sql的性能会越来越差。比如一天生成的考勤数据有四条，那么一位工作了5年的老员工生成的考勤数据就有可能达到40000+的数据（当然只是举个例子，换成网站点击次数会不会让你觉得数据量大一些）这样的的扫描行数明显太多了。
这个时候如果业务里面的参数是有人员的部门id的话那么改写成这样子就很很大的提升性能

SELECT
	* 
FROM
	t_attendance 
WHERE
	c_person_id = ***
	AND c_department_id = ***
	AND c_date = '***'

由于部门里面的人数是有限的，而且只查一天走了组合索引部门id-考勤时间。因此扫描行数就有可能变成部门人数*4。
当然，如果你拿不到部门id或者觉得将普通索引人员id改为组合索引人员id-考勤时间后这条索引的使用率会很高，那么就改成组合索引人员id-考勤时间吧。（添加的索引列越多，数据做更新、删除、插入操作的性能越低，因为需要重建索引）
可能上面的例子需要很结合业务，可能看的不是很懂。那么比如相亲网站需要经常查询性别和年龄。于是原先创建了组合索引“性别-年龄”。（ps：虽然索引的区分度不高，但是有时候如果使用的频率高的话可以创建，如下图解释）

引用自《高性能mysql》5.4.1

而现在添加了一个需要了，我只要筛选年龄的，不需要筛选性别了，那怎么办。

SELECT
	* 
FROM
	t_people 
WHERE
	c_sex IN ( '男', '女' ) 
	AND c_age = ***

这样子因为添加了一个条件就用上索引了

三、时刻注意sql语句中的or

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB；
delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=1000000)do
    insert into t values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

1.组合索引的情况下使用or。
例：索引：idx_a_b 查询条件只有“a or b”
结论：索引生效，但是实际效果和全表扫描一样。
分析：因为系统需要分别扫描字段b和a，而字段b无法生效索引（组合索引的最左前缀），所以必须全表扫描一下字段b。至于为什么明明是全表扫描，但是explain显示生效了索引，我也不是很明白，统计了一下时间其实是和不使用索引，全表扫描的时间是一致的

2.or字段都带有单列索引
例：索引：idx_a, idx_b 查询条件只有“a or b”
结论：索引生效，使用索引混合的方式（index_merge）
分析：因为系统需要分别扫描字段b和a，刚好又a和b分别的单列索引，所以系统分开扫描，分别扫描了a和b的索引，最后将结合融合。
注意：a可能扫描的某行和b扫描的某行是一致的（如：a = 5, b = 5），a扫过该行后，b仍然会扫描该行，最终扫描的行数是2

3.or字段不都带有单列索引
例：索引：idx_a 查询条件“a or id or b”
结论：索引不生效，全表扫描
分析：因为系统需要分别扫描字段b和a和id，id和a都有单列索引，b没有，系统必须去全表扫描发现符合b的情况。因此索引不生效。

4.连表情况下在where子句中的or

CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB；
-- 自己随便插一些数据吧

在讲这个之前必须先明白连表的过程：先过一下where子句，然后将得到的结果根据根据ON里面的条件进行融合。
那么在where子句中的or情况又分为下面的几种情况
①or的字段都是在单个表中，参考上面的三种情况
②or的字段在多个表中，我们可以发现索引不生效
索引：单列索引a，b，c

SELECT * FROM t LEFT JOIN t2 ON t.id = t2.id  WHERE t.a = 5 OR t.b = 5 OR t2.c = 5

分析：由于是跨表or where条件是无法判断刷选之后再连表的结果是正确的

t.id	t2.id	a	b	c
5	5			5
6	6	5
7	7		5

如上面查询语句本来是要查询id等于5，6，7的情况，但是如果是先用where刷选t表的话是选出了id等于5和6的情况，t2表是id等于5的情况，那么结果就是id等于6，7但是c字段都为null的情况，所以与预期不符，所以他的实际查询过程应该是先做全表扫描并根据on左连，然后再过where条件，但实际上连表后是一个新的表，所以不会用到索引。
解决办法：很头疼，用不上索引了，那有扫描办法呢？试试这条语句

SELECT * FROM t LEFT JOIN t2 ON t.id = t2.id  WHERE t.a = 5 OR t.b = 5
UNION
SELECT * FROM t LEFT JOIN t2 ON t.id = t2.id  WHERE t2.c = 5

确保了or字段没有跨表，用上了索引，然后再将两次的条件加一下，再去重，就可以得出正确结果了。
如果你的结果可以不用去重，那么用上UNION ALL代替UNION效率会更高

四、用SHOW INDEX FROM t_table查看索引的区分度
我们创建索引需要保证的基本原则是区分度要高那么怎么看效果呢？SHOW INDEX FROM t_table看帮你查看已创建了的索引的区分度情况

五、update/delete条件筛选一定要加上索引，不然即使更新一行数据也会锁表
自己可以试一下
表t（id，c, d）

-- 会话1，开启事物，并不要提交事物
BEGIN;
	UPDATE t 
	SET a = 1 
WHERE
	b = 1;
-- 会话2
UPDATE t 
SET a = 2 
WHERE
	b =2

会话2会阻塞，直到会话1提交了事物，这证明会话1的操作锁了整个表。如果加上索引就会退化成行锁或者间隙锁

六、update和delete语句后面尽量加limit，知道结果为1条的也要加上limit。
加limit有两个好处：
1、防止误删或误更新数据。
2、减小间隙锁的锁区间，减少查询要遍历的行数（详情可看mysql语句执行中的锁情况）。

七、group by后面的字段要尽量使用上索引
这里有两点可以注意一下：
1、单表才能用上group by后面字段的索引,多表的话要看mysql的优化器选择了哪个表做为驱动表
2、group by无法加索引优化，且不需要排序的情况下，在group by后面加order by null会有奇效

1、单表才能用上group by后面字段的索引,多表的话要看mysql的优化器选择了哪个表做为驱动表
例：（t（id, c）t2(id, c)）

-- 注意mysql的优化器不一定会按照你左联或者右连来选择驱动表，可以用SHOW WARNINGS来看优化器的执行计划
-- 驱动表t2
SELECT
	t.a 
FROM
	t
	RIGHT JOIN t2 ON t2.c = t.a
GROUP BY
	t.a;
-- 驱动表t
SELECT
	t.a 
FROM
	t
	LEFT JOIN t2 ON t2.c = t.a 
GROUP BY
	t.a

explain的结果
t2做为驱动表

t为驱动表

除了上面的方法，还可以

SELECT
	tt.a 
FROM
	( SELECT t.a FROM t GROUP BY t.a ) tt 
	RIGHT JOIN t2 ON t2.c = tt.a

但是可明显也多了张临时表，所以如果结合开篇的第一点就会有很大的性能提升。
2、group by无法加索引优化，且不需要排序的情况下，在group by后面加order by null会有奇效。

SELECT
	t.c 
FROM
	t
	RIGHT JOIN t2 ON t2.c = t.c 
GROUP BY
	t.c 
ORDER BY NULL

我们看到少了排序操作，原因是group by后默认按group by的字段进行排序，使用order by null就告诉mysql说我不需要排序

八、简单的查看索引的执行情况（innodb）

SELECT * FROM `performance_schema`.`table_io_waits_summary_by_index_usage`

详细可看mysql查看索引执行情况