mysql高级编1

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值，就是看你要调优的语句

查看 时候注意看这几个

主要是对这几个进行调优。