mysql总结(SQL优化)
一、explain返回列简介
explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。
1、type常用关键字
system > const > eq_ref > ref > range > index > all system:表仅有一行,基本用不到; const:表最多一行数据配合,主键查询时触发较多; eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型; ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取; range:只检索给定范围的行,使用一个索引来选择行。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符,用常量比较关键字列时,可以使用range; index:该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小; all:全表扫描; 实际sql优化中,最后达到ref或range级别
2、Extra常用关键字
Using index:只从索引树中获取信息,而不需要回表查询 Using where:Where子句用于限制哪一行匹配下一个表或发送到客户。除非你专门从表里索取或检查所有行,如果Extra值不为Using where并且表联接类型为ALL或者index,查询可能会有一些错误,需要回表查询 Using temporary:mysql常建一个临时表来容纳结果,典型情况如查询包含可以按不同情况列出列的Group by和Order by子句时。
二、触发索引代码示例
1、建表语句+联合索引
CREATE TABLE `student` ( `id` int(10) NOT NULL, `name` varchar(20) NOT NULL, `age` int(10) NOT NULL, `sex` int(11) DEFAULT NULL, `address` varchar(100) DEFAULT NULL, `phone` varchar(100) DEFAULT NULL, `create_time` timestamp NULL DEFAULT NULL, `update_time` timestamp NULL DEFAULT NULL, `deleted` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `student_union_index` (`name`,`age`,`sex`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2、使用主键查询
EXPLAIN SELECT id,name,age,sex,address,phone from student where id = 1 const表示只有一行数据匹配
3、使用联合索引查询
EXPLAIN SELECT id,name,age,sex,address,phone from student where name = "迪迦" and age > 10 and sex = 1 使用联合索引,但其中一个条件使用> range: 只检索给定范围的行,使用一个索引来选择行。
4、联合索引,但与索引顺序不一致
EXPLAIN SELECT id,name,age,sex,address,phone from student where sex = 1 and name = "迪迦" and age > 10 因为mysql优化器的缘故,与索引顺序不一致,也会触发索引,但实际项目中尽量顺序一致。
5、联合索引,order by
EXPLAIN SELECT id,name,age,sex,address,phone from student where name = "迪迦" and sex = 1 order by age ref: 对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。 where和order by一起使用时,不要跨索引列使用
三、单表sql优化
1、删除student表中的联合索引后进行查询
explain select name from student where age = 12 and sex in (0,1) 触发全表扫描,必须优化sql
2、添加索引
alter table student add index student_union_index(name,age,sex); explain select name from student where age = 12 and sex in (0,1) index: 触发索引了,不进行全表扫描,只对索引树进行全树扫描 优化一点,但效果不是很好,因为type是index类型,extra中依然存在using where
3、更改索引顺序
因为sql的编写过程: select distinct ... from ... join ... on ... where ... group by ... having ... order by ... limit ... 解析过程: from ... on ... join ... where ... group by ... having ... select distinct ... order by ... limit ...
所以可能是联合索引建的顺序问题,导致触发索引的效果不好。试一下 更改顺序,添加索引: alter table student add index student_union_index2(age, sex, name) 删除不用的旧索引: drop index student_union_index on student 索引改名: alter table student rename index student_union_index2 to student_union_index 继续查询 explain select name from student where age = 12 and sex in (0,1) 更改索引顺序后,type等级发生了变化,由index变为ref,对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取; in会导致索引失效,所以触发using where,进而导致回表查询。
4、去掉in
explain select name from student where age = 12 and sex = 1 order by age 优化完成。
5、小结
1、保持索引的定义和使用顺序一致性 2、索引需要逐步优化 3、将含in的范围查询,放到where条件的最后,防止索引失效
四、双表sql优化
1、建表语句
CREATE TABLE `student` ( `id` int(10) NOT NULL, `name` varchar(20) NOT NULL, `age` int(10) NOT NULL, `sex` int(11) DEFAULT NULL, `address` varchar(100) DEFAULT NULL, `phone` varchar(100) DEFAULT NULL, `create_time` timestamp NULL DEFAULT NULL, `update_time` timestamp NULL DEFAULT NULL, `deleted` int(11) DEFAULT NULL, `teacher_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `teacher` ( `id` int(11) DEFAULT NULL, `name` varchar(100) DEFAULT NULL, `course` varchar(100) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2、左连接查询
explain select s.name,t.name from student s left join teacher t on s.teacher_id = t.id where t.course = '数学'
联合查询时,小表驱动大表。小表也称为驱动表。其实就相当于双重for循环,小表就是外循环,第二张(大表)就是内循环 虽然最终的循环结果都是一样的,都是循环一样的次数,但是对于双重循环来说,一般建议将数据量小的循环放外层,数据量大的放内层。 student数据有4条。teacher数据有3条。 则teacher应该为驱动表。则前边的表为驱动表
explain select teacher.name,student.name from teacher left join student on teacher.id = student.id where teacher.course = '魂技'
优化一般是需要索引的,索引一般建在经常使用的字段上 由sql语句可知,teacher表的id字段使用较为频繁 left join on 一般给左表加索引 alter table teacher add index teacher_index(id) alter table teacher add index teacher_course(course)
3、小结
1、小表驱动大表 2、索引建立在经常查询的字段上 3、sql优化,需要通过explain推测
五、避免索引失效的一些原则
1、复合索引,不要跨列或无序使用(最佳左前缀) 2、符合索引,尽量使用全索引匹配 3、不要在索引上进行任何操作,例如对索引进行(计算。函数,类型转换),索引失效 4、复合索引不能使用不等于(!=或<>)或is null(is not null),否则索引失效 5、尽量使用覆盖索引(using index) 6、like尽量以常量开头,不要以%开头,否则索引失效;如果必须使用%name%进行查询,可以使用覆盖索引挽救,不用回表查询时可以触发索引 7、尽量不要使用类型转换,否则索引失效 8、尽量不要使用or,否则索引失效
六、一些其他优化方法
1、exist和in
select name from student exist/in (子查询) 如果主查询的数据集大,则使用in 如果子查询的数据集大,则使用exist
2、order by优化
using filesort有两种算法:单路排序、双路排序(根据IO的次数) MySQL4.1之前,默认使用双路排序;双路:扫描两次磁盘(①从磁盘读取排序字段,对排序字段进行排序;②获取其它字段)。 MySQL4.1之后,默认使用单路排序;单路:只读取一次(全部字段),在buffer中进行排序。但单路排序会有一定的隐患(不一定真的是只有一次IO,有可能多次IO)。 注意:单路排序会比双路排序占用更多的buffer。 单路排序时,如果数据量较大,可以调大buffer的容量大小。 set max_length_for_sort_data = 1024;单位是字节byte。 如果max_length_for_sort_data值太低,MySQL底层会自动将单路切换到双路。 太低指的是列的总大小超过了max_length_for_sort_data定义的字节数。 提高order by查询的策略: 选择使用单路或双路,调整buffer的容量大小; 避免select * from student;(① MySQL底层需要对*进行翻译,消耗性能;② *永远不会触发索引覆盖 using index); 符合索引不要跨列使用,避免using filesort; 保证全部的排序字段,排序的一致性(都是升序或降序);