MySQL索引、执行计划及慢日志记录

作者

昨天更新了WordPress5.0，也安装了Classic Editor，不过为了提前适应Gutenberg编辑器，研究了快一个小时的Gutenberg的使用，所以写本文的时候已经卸载了Classic Editor尝试着用Gutenberg来写博客了！
MySQL索引、执行计划及慢日志记录

0x00 MySQL索引相关知识

1.索引简介

索引在MySQL中的作用就是加速查找及对数据进行约束的数据结构。当然索引也是有一定的负面作用的，首先索引的创建及维护都需要一定的时间成本，如果在创建数据表的时候就设定了索引，那么创建这个表格的时候也会略慢（可以忽略），其次索引也是存在于一个“索引文件”中的，因而也会带来额外的硬盘开销，最后呢对数据的增删改成都会对“索引文件”进行改动，也就是进行增删改查的SQL效率会变低（可以忽略）。
建立索引的原则：

  • 对经常进行更新的表，切勿建立过多的索引。
  • 数据量较小的表格，由于遍历时间也非常短，索引可能会带来负面的效果。
  • 对于非重复数据较小的字段不宜建立索引，如性别字段。

2.索引的分类

MySQL中常用索引大概可以分为四类：普通索引、主键索引、唯一索引及组合索引（即多列为索引）。

  • 普通索引：仅仅是为了加速查找。
  • 主键索引：加速查找和唯一约束（不能为NULL）
  • 唯一索引：加速查找和唯一约束（可以为NULL）
  • 联合索引：联合主键索引、联合唯一索引、联合普通索引

查看一个表格中有哪些索引及其类型可以通过命令：

Code example:
1
show index from 表名;

a.普通索引操作

Code example:
1
2
3
4
5
6
7
8
9
10
11
12
#创建表+普通索引
create table tb1(
    id int auto_increment primary key,name char(32),
    email char(64),index ix_name (name));
#删除普通索引
drop index 索引名 on 表名;
drop index ix_name on tb1;
#添加普通索引
create index 索引名 on 表名（字段);
create index ix_name on tb1(name);

b.主键索引

Code example:
1
2
3
4
5
6
7
8
9
10
#创建表+主键索引
create table tb1(
    id int auto_increment primary key,name char(32),
    email char(64),index ix_name (name));
#删除主键索引
alter table 表名 drop primary key;
#添加主键索引
alter table 表名 add primary key(字段);

c.唯一索引

Code example:
1
2
3
4
5
6
7
8
9
10
11
#创建表+唯一索引
create table tb1(
    id int auto_increment primary key,name char(32),
    email char(64),index ix_name (name),
    unique ix_email(email));
#删除唯一索引
alter table tb1 drop index ix_email;
#添加唯一索引
create unique index ix_email on tb1(email);

d.联合索引

Code example:
1
2
#添加联合索引
create index ix_name_email on tb1(name,email);

如果频繁的使用多列的条件进行查询时，那么组合索引的性能会好与多个索引，如where name=’xzymoe’ and email=’xzymoe#email.com’
联合索引存在一个“最左前缀匹配”规则。从上面创建索引可以看出创建索引的位置为(name,email)。

Code example:
1
2
3
4
5
6
7
#走联合索引
select * from tb1 where name='xzymoe'；
select * from tb1 where name='xzymoe' and email='[email protected]'；
#不走联合索引
select * from tb1 where email='[email protected]'；
select * from tb1 where email='[email protected]' and name='xzymoe'；

3.索引类型

索引创建的时候有基于2种不同算法的索引，分别是Hash类型和BTree类型。
Hash类型索引：将表中的所有值转换为一个Hash值，放到索引表中，并加上数据存储地址。其优点为，查询单值比较快，但查询范围则效率不高。
BTree类型索引：类似于二叉树，也是平时使用索引时候常用的类型。

4.关于索引的名词

覆盖索引：在索引文件中直接获取数据。

Code example:
1
2
#id为索引列，那么直接通过id取结果
select * from tb1 where id=10；

合并索引：把多个单列索引合并使用。

Code example:
1
2
#如name和email列都是索引
select * from tb1 where name='xzymoe' and email='[email protected]'；

从效率的角度来看联合索引比合并索引效率要高。

0x01 高效率命中索引

为了使SQL能更快的显示出结果，那么走索引是必须的东西，如何才能高效的命中索引呢？当然就得避开那些会跳过索引而去慢慢扫描表格的SQL写法了。
1.避免使用’like’的写法。

Code example:
1
2
#无法命中索引
select * from tb1 where name like 'xzy*';

2.避免使用函数。

Code example:
1
2
#无法命中索引
select * from tb1 where reverse(name)='xzymoe';

3.避免使用or。

Code example:
1
2
#无法命中索引（name是索引列而email不是索引列）
select * from tb1 where name='xzymoe' or email='[email protected]';

4.数据类型不一致，如320和’320’。
5.！=、<、>，但是如果操作列是主键列话，还是可以命中索引。
6.oder by：当根据索引排序时候，选择的映射如果不是索引，则不走索引。即select取的列，和order by的列不一致则不走索引；主键除外。

Code example:
1
2
3
4
5
6
#无法命中索引
select name from tb1 order by email desc;
#依旧命中索引
select * from tb1 order by id desc;
select email from tb1 order by email desc;

7.就是之前提到的联合索引中的“最左前缀匹配”规则。

0x02 执行计划

对于表中数据量较大的时候，可能由于SQL语句写的质量较差，导致执行时间过长，因而可以在执行SQL语句之前，通过explain命令来对SQL语句的执行情况进行一个预估，从而进一步的优化SQL语句提供了依据。
MySQL索引、执行计划及慢日志记录
可能由于数据量过小（15w和4w行数据，请原谅用中文命名，没办法！！工作需要）通过explain发现时间上的预估竟然都是0秒，这个显然是不正确的。不过从Type上可以明显看出ref变为了ALL，因而执行效率变低很过。
其执行效率为：
all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const

  • ALL：全表扫描
  • INDEX：全索引扫描
  • RANGE：对索引列进行范围查找
  • INDEX_MERGE：合并索引
  • REF：根据索引查找一个或多个值
  • EQ_REF：连接时使用primary key或unique类型
  • CONST：常量
  • SYSTEM：系统，CONST的一个特例

0x03 慢日志记录

通过show variables like ‘%query%’，可以搜索出几个和慢日志记录有关的配置参数。

  • slow_query_log = OFF    #是否开启慢日志记录
  • long_query_time = 2    #时间限制，执行超过此时间的SQL将被记录
  • slow_query_log_file = /usr/slow.log    #日志文件
  • log_queries_not_using_indexes = OFF    #使用索引的搜索是否被记录

对慢日志进行配置的时候可以通过基于内存的配置及修改配置文件进行配置。

Code example:
1
2
3
#基于内存
set global 配置参数;
set global show_query_log = ON;

基于配置文件的慢日志设置，将在配置文件中进行修改，修改后需要重启MySQL后方可生效。

0x04 关于分页性能解决方案

提起分页这个话题，首先就会想到limit这个sql词汇。如果一页只显示10个数据的话，那么就是limit 0,10，之后就是limit 11,10。当翻到几百万页的时候，那么limit 1000000,10可不仅仅是扫描了表格的这10行数据，而是从第一行开始扫描到1000010数据，因而效率也非常的低下。
观察了下主流网站的页面翻页设计，一般分为只有上下页，或者上下页中间还有个5~10个页面，形如上一页 [30] [31] [32] [33][34] [下一页]，仅仅显示上下的几页而已。为什么这样效率较高呢？因为他通过记录当前页码的max_id和min_id。通过与max_id或min_id进行加减即可获得上下页的表格数据了。

Code example:
1
2
3
4
5
6
7
8
9
#获取下一页
select * from tb1 where id > max_id limit 10;
#获取上一页
select * from tb1 where id < min_id limit 10 desc;
#从32页翻到34页
select * from userinfo3 where id in (
    select id from (select id from tb1 where id > max_id limit 20)
as N order by N.id desc limit 10)

值得注意的是，不能使用between…and…来写sql，因而id不一定是连续。

发表评论