1、执行时间长原因:
1、语句写的烂。
2、索引失效(单值索引,多值索引)
3、关联查询太多join。
4、服务器调优及参数设置。
2.1
索引是一种数据结构。索引的目的在于提高查找效率,类似字典。可以理解为“排好序的快速查找数据结构”
2、explain的解析
(1)、id
id相同时从上往下执行,id不同时,如果是子查询,值越大先执行。
(2)、select_type(查询类型)
分为SIMPLE、primary、subquery、deriued、union、union result。
SIMPLE---》简单的select,查询中不包括子查询或者union。
primary---》查询中包括一些复杂的子查询,最外层最后加载。
subquery---》在select或者where列表中包含子查询。
deriued---》在FROM列表中包含的子查询被标记为DERIVED(衍生),MySQL会递归执行这些子查询,把结果放在临时表里。
union---》若第二个SELECT出现在UNION之后,则被标记为UNION;若UNION包含在FROM子司的子查询中,外层SELECT将被标记为: DERIVED。
union result---》从UNION表获取结果的SELECT。两个UNION结果集合并。
(3)、type
显示查询使用了任何类型,从最好到最坏一次是:system>const>eq_ref>ref>range>index>all.
(4)、possible_keys、key
possible_keys对应这张表的索引,应该使用的索引有哪些。
key实际用到的索引是那些。
(5)row
对应这张表优化之后实际查询的行数数量。这个用的越少越好。
(6)、Extra
包括一下几种情况
执行顺序:t2,t1,t3,延伸表,
索引的建立
单表
在单表中把索引建在经常查询的字段上面。
双表
使用一个left join时,把索引建在右表,如果是right join把索引建在左表,
如left join b on b.id=a.id,那么把索引建在b表的id。
right join a on a.id=b.id,那么把索引建在a表的id。
三表
三表也是一样,把索引建在进行left 或者right的表中。
要记住“永远要使用小表去驱动大表”。
索引失效
(1)、全值匹配我最爱.
(2)、最佳左前缀法则【如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。】例如,一张表中有三个索引,分别是1/2/3.在查询的条件中1字段不能没有,简单就是带头大哥不能死。而且不能跳过【中间不能断】,条件中只有1/3、索引也会失效。
(3)、不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描
(4)、存储引擎不能使用索引中范围条件右边的列【就是索引中使用了范围进行查询,那么范围后面的用到的索引也会跟着失效】索引有name,age,phone,select name from a where name='test' and age<22 and phone='110',索引有效的只有name,从age之后包括phone也会一起失效。
(5)、尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少使用select *。
(6)、mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描。
(7)、 is null ,is not null也无法使用索引。
(8)、like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作【写like百分号加在右边'abd%'不会失效】,解决使用'%abc%',使用覆盖索引,索引建在name,age,select name,age from user where name like'%abc%'.如果查询条件中字段多于索引字段那么也会失效。
(9)、字符串不加单引号索引失效
(10)、少用or,用它来连接时会索引失效。
全值匹配我最爱,最左前缀要遵守;
带头大哥不能死,中间兄弟不能断;
索引列上少计算,范围之后全失效;
Like百分写最右,覆盖索引不写星;
不等空值还有or,索引失效要少用;
VAR引号不可丢,SQL高级也不难!
(11)、索引字段在order by中,索引name、age、sex、phone
select* from a where name='aa' and sex='1'and age=11 order by age,phone.用到2个索引,不会重排。
select* from a where name='aa' and sex='1' order by age,phone.用到2个索引, 会重排。
查询语句调优
步骤
1、explain+sql分析语句。
2、show profile。查询sql在服务器里面执行细节和生命周期。
语句调优1in和exists
select * from a where id in(select id from b).当b表数量小于a表时用in
select * from a where exists(select 1 from b where a.id=b.id).当a表数量小于b表时用exists。简单理解就是,
order by
索引字段是age和phone
1、还是符合最佳左前缀法则【如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。】例如,一张表中有三个索引,分别是1/2/3.在查询的条件中1字段不能没有,简单就是带头大哥不能死。而且不能跳过【中间不能断】,条件中只有1/3、索引也会失效。
2、排序尽量使用索引定义好的。
如果使用order by age asc,phone desc ,会出现filesort,性能减低。
如果一定要使用不同于索引建立好的排序,那么查询的字段不要使用*号,第二如果确定是buffer缓冲大小造成查询慢,扩大mysq配置文件的sort_buffer_size的大小。第三尝试提高max_length_for_sort_data,提高这个参数,会增加用改进算法的概率。但是如果设的太高,数据总容量超出sort_buffer_size的概率就增大,明显症状是高的磁盘I/O活动和低的处理器使用率.
3、慢查询
mysql默认没有开启慢查询日志,需要手动设置这关参数。如果不是调优需要的话,一般不建议开机慢查询日志,因为开启之后或多或少带来一定的性能影响。慢查询日志支持将日志记录写到文件中。
(1)查看是否开启慢查询日志。
show variables like '%slow_query_log%';查看慢日志的状态
show variables like '%long_query_time%';查看sql执行多久算慢查询
value是OFF就是关闭状态。
开启
set global show_query_log=1;只对当前mysql服务有效,如果重启之后就不生效,如果要实现永久生效,要修改配置文件,在my.cnf文件中添加
slow__query_log=1
slow_query_log_file=/var/lib/mysql/home-slow.log 注意这个后面的文件是主机-show.log,
开启了之后,可以在/var/lib/mysql/home-slow.log这个文件中记录有哪些慢查询的语句,都会记录在这个文件中。(定位问题)
(4)使用explain查看语句查询信息报告。
(5)、profile
使用下面这个查看profile是否开启,因为mysql默认是关闭的状态。
show variables like 'profiling';
开启profiling=on;
set profiling=on;
show profiles ;//查询系统执行的sql语句和每个语句执行的时间
show profile cpu,block io for query 1;后面这个数字是上一个语句查询得到的queryid值,就是看你要调优的语句
查看 时候注意看这几个
主要是对这几个进行调优。