MySQL总结

1. 索引

1.1 建立索引

create table customer
(
    id            int(10) auto_increment,
    customer_no   varchar(20),
    customer_name varchar(20),
    primary key (id),
    unique idx_customer_no (customer_no),
    key idx_customer_name (customer_name),
    key idx_customer_no_name (customer_no, customer_name)
);

#创建主键索引
alter table customer add primary key (id);
#删除主键索引
alter table customer drop primary key;
#创建唯一索引
alter table customer add unique idx_customer_no(customer_no);
#删除唯一索引
drop index idx_customer_no on customer;
#创建单一索引
alter table customer add index idx_customer_name(customer_name);
#删除单一索引
drop index idx_customer_name on customer;
#创建联合索引
alter table customer add index idx_customer_no_name (customer_no, customer_name);
#删除联合索引
drop index idx_customer_no_name on customer;

1.2 Explain

1.2.1 id

select 查询的序列号，表示查询中执行select子句或操作表的顺序。

• id相同时，执行顺序由上至下；
• id不同，如果是子查询，id的序号会递增，id值越大优先级越高，则先被执行；
• id相同和不同都存在时，id相同的可以理解为一组，从上往下顺序执行，所有组中，id值越大，优先级越高越先执行；

explain select * from t1,t2,t3 where t1.id = t2.id and t2.id = t3.id;

1.2.2 select_type

查询的类型，常见值有：

• SIMPLE：简单的select查询，查询中不包含子查询或者UNION
• PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为Primary
• DERIVED：在FROM列表中包含的子查询被标记为DERIVED(衍生)，MYSQL会递归执行这些子查询，把结果放在临时表里
• SUBQUERY：在SELECT或WHERE列表中包含了子查询

explain select t2.* from t2 where t2.id = (select t3.id from t3);

1.2.3 table

显示这一行的数据是关于哪张表的

1.2.4 type

访问类型排序

• ALL：将遍历全表以找到匹配的行；
• System：表只有一行记录(等于系统表)，这是const类型的特例，平时不会出现，可以忽略不计；
• Const：表示通过索引一次就找到了，const用于比较primary key或者unique索引，因为只匹配一行数据，所以很快，如将主键置于where列表中，mysql就能将该查询转换为一个常量；
• ef_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描；
• Ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体；
• Range：只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现了between、<、>、in等的查询这种范围扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引；
• Index：index与all的区别为index类型只遍历索引树，这通常比all快，因为索引文件通常比数据文件要小，也就是说虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘中读的；
  最好到最差的依次是：system>const>eq_ref>ref>range>index>all，一般来说，最好保证查询能达到range级别，最好能达到ref；

explain select * from t1 where content = 't1value';

explain SELECT * from (select t1.id from t1 where id = 1) t;

explain select t1.*,t2.* from t1 join t2 on t1.id = t2.id;

alter table t1 add index idx_t1_content(content);
explain select * from t1 where content = 't1value';

explain select * from t2 where id > 0;

explain select * from t1;

两个字段都建立了索引

1.2.5 possible_keys

显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段如果存在索引，则该索引也会被列出来，但不一定会被实际查询使用到；

1.2.6 key

查询实际使用的索引，如果为null，则没有使用索引

explain select * from t2 where id = 1;

1.2.7 ref

显示索引的哪一列被使用了，哪些列或常量被用于查找索引列上的值；

explain select t2.*,t3.* from t2,t3 where t2.id = t3.id;

t3.id在进行连表查询的时候，引用了t2的id列

1.2.8 rows

rows列显示mysql认为它执行查询时必须检查的行数，一般越少越好；

1.2.9 extra

一些常见的重要的额外信息：

• Using filesort：mysql无法利用索引来完成排序操作，排序时没有使用索引
• Using temporary：mysql对查询结果排序时使用临时表，常见于排序order by和分组查询，分组时没有使用索引
• Using index：表示索引被用来执行索引键值的查找，避免访问了表的数据行，效率不错
• Using where：表示使用了where过滤

2 查询优化

2.1 索引失效

• 最佳左前缀法则：如果索引了多列，要遵循最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列
• 索引列上做任何计算、函数操作，会导致索引失效而转向全表扫描
• 存储引擎不能使用索引中范围条件右边的列
• mysql在使用不等于时无法使用索引会导致全表扫描
• is null可以使用索引，但是is not null无法使用索引
• like以通配符开头会使索引失效导致全表扫描
• 字符串不加单引号索引会失效
• 使用or连接时索引失效

alter table students add index idx_sname_age_score(sname,age,score);

explain select * from students where sname = '小明';
explain select * from students where sname = '小明'and score = 100;

下面两条sql语句使用的索引是等效的，score并没有使用到索引

explain select * from students where left(sname,2) = '小明';

下面两条sql语句使用的索引是等效的，score并没有使用到索引，范围条件右边的列索引失效

explain select * from students where sname = '小明' and age = 22;
explain select * from students where sname = '小明' and age > 22 and score = 100;

索引失效

explain select * from students where sname != '小明';

explain select * from students where sname is null ;

explain select * from students where sname is not null;

explain select * from students where sname like '%明';

explain select * from students where sname = 123;

explain select * from students where sname = '小明' or age = 22;

建议：对于单值索引，尽量选择针对当前查询字段过滤性更好的索引；对于组合索引，当前where查询中过滤性更好的字段在索引字段顺序中位置越靠前越好；对于组合索引，尽量选择能够包含在当前查询中where子句中更多字段的索引；尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的；

2.2 排序优化

1.尽量避免使用Using FileSort方式排序；
2.order by语句使用索引最左前列或使用where子句与order by子句条件组合满足索引最左前列，order by能使用索引最左前缀；
3.where子句如果出现索引范围查询会导致order by索引失效；
where与order by组合使用，满足最左前缀可以使用索引

2.3 单表排序优化

举例：

explain select id, author_id from article where category_id = 1 and comments > 1 order by views desc limit 1;

加索引，category_id ，comments 生效，views 失效

alter table article add index idx_ccv(category_id,comments,views);

改进

drop index idx_ccv on article;
alter table article add index idx_cv(category_id,views);

2.4 关联查询优化

内连接时，mysql会自动把小结果集的选为驱动表，所以大表的字段最好加上索引，左外连接时，左表会全表扫描，所以右边大表字段最好加上索引，右外连接同理；我们最好保证被驱动表上的字段建立了索引；

create table if not exists class(
    id int primary key auto_increment,
    card int
);
create table if not exists book(
    bookid int primary key auto_increment,
    card int
);

explain select * from class left join book on class.card = book.card;

左外连接，左表(驱动表)会被全表扫描，不可避免，一般左表选小表，这里可以给book表的card字段建立索引

alter table book add index idx_card(card);

2.5 分组查询优化

explain select count(*),sname from students group by sname;

加索引

alter table students add index  idx_saa(sname,age,score);

explain select count(*),sname from students where sname = '小明' and  age = 22 group by sname;

sname索引失效

explain select count(*),sname from students where sname = '小明' and  age > 22 group by sname;

3 慢查询日志

3.1 慢查询日志简介

mysql的慢查询日志是mysql提供的一种日志记录，用来记录在mysql中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的sql，则会被记录到慢查询日志中，可以由它来查看哪些sql超出了我们最大忍耐时间值；

3.2 慢查询日志使用

默认情况下，mysql数据库没有开启慢查询日志，需要手动设置参数；
查看是否开启：show variables like ‘%slow_query_log%’;
开启日志：set global slow_query_log = 1;
设置时间：set global long_query_time = 1;
查看时间：show variables like ‘long_query_time%’;
查看超时的sql记录日志：mysql的数据文件夹下；
一般不建议开启慢查询日志；