趣谈同步/异步、阻塞/非阻塞

原文链接

常见概念

CPU执行程序

一个CPU在一个时刻只能运行一个程序,但是因为CPU的执行效率高速度快,每次程序切换快人类肉眼是无法察觉的,所以给人感觉是在同时运行,比如你看网页同时听歌,单核CPU并不是不可以多任务处理,只是单核CPU的多任务能力相对多核CPU较弱而已,看网页和听歌都是对CPU资源占用非常少的应用。

并发

并发:一个时间段内,有多个程序在同一个CPU上运行,但是在任意时刻只有一个程序在CPU上运行。

并发是指一段时间内CPU上执行的程序数。(可以实现高并发)

并行

并行:一个时刻有多个程序在多个CPU上同时运行,并行的概念是对多核CPU来言的,每个CPU独立运行自己的程序,互不干扰。

并行是指多个CPU在同一个时刻运行多个程序。(无法实现高并行)

同步

同步:同步就是协同步调,按预定的先后次序进行运行。可理解为进程或线程A和B一块配合,A执行到一定程度时要依靠B的某个结果,于是停下来,示意B运行,B依言执行,再将结果给A,A再继续操作。好比一个函数调用在没结束前原来的函数啥都不能做,粗略理解为:在等待一件事情的处理结果时, 对方是否提供通知服务, 如果对方不提供通知服务, 则为同步。

同步是指在调用IO操作或者等待的时候,必须等待IO操作完成或者等待完成才返回的调用方式。

异步

异步:好比一个函数调用后,原来的函数继续干自己的事情,等那个函数干完后,借助某种手段通知原来的函数执行结果。也是一种目的,一般是通过多线程技术去实现。粗略理解为:在等待一件事情的处理结果时, 对方是否提供通知服务, 如果对方提供通知服务, 则为异步。

异步是指代码在进行IO操作或者等待的时候,不必等待IO操作完成或者等待完成就直接返回的调用方式。

同步异步是一种消息通信机制,类似于发一个消息给另一个线程或者另一个协程让他去执行某个操作,提交数据后会得到一个future,后期通过这个future拿到结果

阻塞

阻塞:阻塞调用是指调用结果返回之前,当前线程会被挂起。调用线程只有在得到结果之后才会返回。粗略理解为:在等待一件事情的处理结果时, 你是否还去干点其他的事情, 如果不去, 则为阻塞。

非阻塞

非阻塞:在不能立刻得到结果之前,该调用不会阻塞当前线程。粗略理解为:在等待一件事情的处理结果时, 你是否还去干点其他的事情, 如果去了, 则为非阻塞。

阻塞非阻塞是一种函数调用机制,重点关注程序代码在等待调用结果(消息,返回值)时的状态

举例说明

同步的定义看起来跟阻塞很像,但是同步跟阻塞是两个概念, 同步调用的时候,线程不一定阻塞,调用虽然没返回,但它还是在运行状态中的,CPU很可能还在执行这段代码,而阻塞的话,它就肯定不在CPU中跑这个代码了。这就是同步和阻塞的区别。同步是肯定可以在,阻塞是肯定不在。

异步和非阻塞的定义比较像,两者的区别是异步是说调用的时候结果不会马上返回,线程可能被阻塞起来,也可能不阻塞,两者没关系。非阻塞是说调用的时候,线程肯定不会进入阻塞状态。

上面两组概念,就有4种组合。

同步阻塞调用:得不到结果不返回,线程进入阻塞态等待。

同步非阻塞调用:得不到结果不返回,线程不阻塞一直在CPU运行。

异步阻塞调用:去到别的线程,让别的线程阻塞起来等待结果,自己不阻塞。

异步非阻塞调用:去到别的线程,别的线程一直在运行,直到得出结果。

任务:浪子要完成烧水泡茶和拖地。

并发:在半个小时内,浪子完成了烧水和拖地和泡茶三个任务。

并行:浪子把小桃红叫过来,浪子拖地,小桃红烧水泡茶。

同步:浪子在烧水的时候,时时刻刻盯着烧水壶,水烧开了就去泡茶。(所谓同步异步,只是对于水壶而言。)

异步:浪子买了个智能烧水壶,水烧开了会滴滴叫,浪子在烧水的时候,可以选择去拖地或者干小桃红,然后水开了滴滴叫,浪子就去泡茶。(所谓同步异步,只是对于水壶而言。)

阻塞:浪子先烧水,等水烧开了,然后泡茶,泡完茶了就去拖地。(所谓阻塞非阻塞,只是对于浪子而言。)

非阻塞:浪子先烧水,然后去拖地,水烧开了就去泡茶。(所谓阻塞非阻塞,只是对于浪子而言。)

同步阻塞:浪子在烧水的时候,时时刻刻盯着烧水壶,水烧开了就去泡茶,泡完茶就去拖地。

同步非阻塞:浪子先烧水,然后拖地,时不时去看看水烧开了没,烧开了就泡茶。

异步阻塞:浪子买了个智能烧水壶,浪子开始烧水,然后浪子坐在那里等水壶滴滴叫,什么都不干。

异步非阻塞:浪子买了个智能烧水壶,浪子开始烧水,然后去拖地,水开了滴滴叫,浪子就去泡茶。

同步异步和阻塞非阻塞要分开来看。

同步异步关心的是“消息通知机制”。

比如你打电话去书店问你这里有没有某某书。

同步的做法是,老板会说让你等一下,我找找。这时整个通信过程会在一次通话中完成。

异步的做法是,老板说我找一下,迟点在回复你。此时通信过程分成两次通话完成。

而阻塞非阻塞关心的是“程序在等待调用结果(消息,返回值)时的状态”。

同样是上面的例子。

阻塞的做法是你打电话问了之后,就一直拿着电话在等老板的回复,等待期间其他什么都不干。

而非阻塞则是你先放下电话,等老板来回复才回来继续这个通话。

那么剩下的就很好理解。

同步阻塞就是你一直在那里等,老板也不挂掉电话而是直到他找到或者没找到再回复你。

异步阻塞就是,尽管老板已经说了找到了再另行通知你,你仍就是停在那里,什么都不干的等电话

同步非阻塞就是,老板没挂断电话,但是你仍旧去干其他事情了,不在电话前等着他。

异步非阻塞就是大家都不等在电话前。

餐厅来了10个顾客,为了提供最佳消费体验,不让顾客等待,为每个顾客分配一个服务员。服务员给顾客安排好座位,把菜单交给顾客,然后在一边等待顾客点餐。点完餐后把订单交给厨房,然后在厨房等待大厨烹饪。菜做好后,将菜送到桌上,然后在桌旁静静的看着顾客吃。。。

这就是同步,为了保证“实时”的服务,需要有一个专门的人员时刻等待。

有人说了,这尼玛不是有病么!哪个餐厅这么干?可是传统的web服务器就是这样的呢,比如apache。



现实中的餐厅为了节省成本,当然不会这么做,也许两个服务员就足够了。给新进来的顾客安排好座位,菜单交给他,然后就可以去忙活其他的顾客了。等顾客点好菜喊一声,赶紧冲过来下单。。。

这就是前文所说的异步方式,服务员不需要等待顾客完成全部任务,就“返回”了。

那有这种方式的web服务器么?当然有了,也是近年来的新趋势,如nginx、tornado等。

MySQL性能影响因素和优化

出处:分分钟解决 MySQL 查询速度慢与性能差

一、什么影响了数据库查询速度

1.1 影响数据库查询速度的四个因素

sql查询速度

服务器硬件

网卡流量

磁盘IO

1.2 风险分析

QPS:Queries Per Second意思是“每秒查询率”,是一台服务器每秒能够相应的查询次数,是对一个特定的查询服务器在规定时间内所处理流量多少的衡量标准。

TPS:TransactionsPerSecond的缩写,也就是事务数/秒。它是软件测试结果的测量单位。客户机在发送请求时开始计时,收到服务器响应后结束计时,以此来计算使用的时间和完成的事务个数。

Tips:最好不要在主库上数据库备份,大型活动前取消这样的计划。

  1. 效率低下的sql:超高的QPSTPS
  2. 大量的并发:数据连接数被占满(max_connection默认100,一般把连接数设置得大一些)。
    并发量:同一时刻数据库服务器处理的请求数量
  3. 超高的CPU使用率:CPU资源耗尽出现宕机。
  4. 磁盘IO:磁盘IO性能突然下降、大量消耗磁盘性能的计划任务。解决:更快磁盘设备、调整计划任务、做好磁盘维护。

1.3 网卡流量:如何避免无法连接数据库的情况

  1. 减少从服务器的数量(从服务器会从主服务器复制日志)
  2. 进行分级缓存(避免前端大量缓存失效)
  3. 避免使用select * 进行查询
  4. 分离业务网络和服务器网络

1.4 大表带来的问题(重要

1.4.1 大表的特点

  1. 记录行数巨大,单表超千万
  2. 表数据文件巨大,超过10G

1.4.2 大表的危害

1.慢查询:很难在短时间内过滤出需要的数据
2.查询字区分度低 -> 要在大数据量的表中筛选出来其中一部分数据会产生大量的磁盘io -> 降低磁盘效率

2.对DDL影响:

    建立索引需要很长时间:

  • MySQL -v<5.5 建立索引会锁表
  • MySQL -v>=5.5 建立索引会造成主从延迟(mysql建立索引,先在组上执行,再在库上执行)

    修改表结构需要长时间的锁表:会造成长时间的主从延迟(『480秒延迟』)

1.4.3 如何处理数据库上的大表

分库分表把一张大表分成多个小表

    难点:

  1. 分表主键的选择
  2. 分表后跨分区数据的查询和统计

1.5 大事务带来的问题(重要

1.5.1 什么是事务

事务是数据库系统区别于其它一切文件系统的重要特性之一

事务是一组具有原子性的SQL语句,或是一个独立的工作单元

1.5.2事务的ACID属性

1、原子性(atomicity):全部成功,全部回滚失败。银行存取款。

2、一致性(consistent):银行转账的总金额不变。

3、隔离性(isolation):

    隔离性等级:

  • 未提交读(READ UNCOMMITED脏读,两个事务之间互相可见;
  • 已提交读(READ COMMITED)符合隔离性的基本概念,一个事务进行时,其它已提交的事物对于该事务是可见的,即可以获取其它事务提交的数据。
  • 可重复读(REPEATABLE READInnoDB的默认隔离等级。事务进行时,其它所有事务对其不可见,即多次执行读,得到的结果是一样的!
  • 可串行化(SERIALIZABLE) 在读取的每一行数据上都加锁,会造成大量的锁超时和锁征用,严格数据一致性且没有并发是可使用。

    查看系统的事务隔离级别:show variables like '%iso%';
    开启一个新事务:begin;
    提交一个事务:commit;
    修改事物的隔离级别:set session tx_isolation='read-committed';

4、持久性(DURABILITY):从数据库的角度的持久性,磁盘损坏就不行了

======================================================
隔离级别               脏读          不可重复读   幻读 
======================================================

未提交读(Read uncommitted)    可能          可能                 可能

已提交读(Read committed)       不可能       可能                  可能

可重复读(Repeatable read)       不可能       不可能               可能

可串行化(Serializable )             不可能       不可能               不可能

======================================================

·未提交读(Read Uncommitted):允许脏读,也就是可能读取到其他会话中未提交事务修改的数据

·提交读(Read Committed):只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)

·可重复读(Repeated Read):可重复读。在同一个事务内的查询都是事务开始时刻一致的,InnoDB默认级别。在SQL标准中,该隔离级别消除了不可重复读,但是还存在幻象读

·串行读(Serializable):完全串行化的读,每次读都需要获得表级共享锁,读写相互都会阻塞

    redo log机制保证事务更新的一致性持久性

1.5.3 大事务

运行时间长,操作数据比较多的事务;

    风险:锁定数据太多,回滚时间长,执行时间长。

  1. 锁定太多数据,造成大量阻塞和锁超时;
  2. 回滚时所需时间比较长,且数据仍然会处于锁定;
  3. 如果执行时间长,将造成主从延迟,因为只有当主服务器全部执行完写入日志时,从服务器才会开始进行同步,造成延迟。

    解决思路:

  1. 避免一次处理太多数据,可以分批次处理;
  2. 移出不必要的SELECT操作,保证事务中只有必要的写操作。

二、什么影响了MySQL性能(非常重要

2.1 影响性能的几个方面

  1. 服务器硬件。
  2. 服务器系统(系统参数优化)。
  3. 存储引擎。 
    MyISAM: 不支持事务,表级锁。
    InnoDB: 支持事务,支持行级锁,事务ACID
  4. 数据库参数配置。
  5. 数据库结构设计和SQL语句。(重点优化)

2.2 MySQL体系结构

    分三层:客户端->服务层->存储引擎

  1. MySQL插件式的存储引擎,其中存储引擎分很多种。只要实现符合mysql存储引擎的接口,可以开发自己的存储引擎!
  2. 所有跨存储引擎的功能都是在服务层实现的。
  3. MySQL的存储引擎是针对表的,不是针对库的。也就是说在一个数据库中可以使用不同的存储引擎。但是不建议这样做。

2.3 InnoDB存储引擎

    MySQL5.5及之后版本默认的存储引擎InnoDB

2.3.1 InnoDB使用表空间进行数据存储。

show variables like 'innodb_file_per_table

  如果innodb_file_per_table 为 ON 将建立独立的表空间,文件为tablename.ibd;

  如果innodb_file_per_table 为 OFF 将数据存储到系统的共享表空间,文件为ibdataX(X为从1开始的整数);

  .frm :是服务器层面产生的文件,类似服务器层的数据字典,记录表结构

2.3.2 (MySQL5.5默认)系统表空间与(MySQL5.6及以后默认)独立表空间

    1.1 系统表空间无法简单的收缩文件大小,造成空间浪费,并会产生大量的磁盘碎片。

    1.2 独立表空间可以通过optimeze table 收缩系统文件,不需要重启服务器也不会影响对表的正常访问。

    2.1 如果对多个表进行刷新时,实际上是顺序进行的,会产生IO瓶颈。

    2.2 独立表空间可以同时向多个文件刷新数据。

强烈建立对Innodb 使用独立表空间,优化什么的更方便,可控。

2.3.3 系统表空间的表转移到独立表空间中的方法

    1、使用mysqldump 导出所有数据库数据(存储过程、触发器、计划任务一起都要导出 )可以在从服务器上操作。

    2、停止MYsql 服务器,修改参数(my.cnf加入innodb_file_per_table),并删除Inoodb相关文件(可以重建Data目录)。

    3、重启MYSQL,并重建Innodb系统表空间。

    4、 重新导入数据。

    或者 Alter table 同样可以的转移,但是无法回收系统表空间中占用的空间。

2.4 InnoDB存储引擎的特性

2.4.1 特性一:事务性存储引擎及两个特殊日志类型:Redo Log 和 Undo Log

  1. Innodb 是一种事务性存储引擎
  2. 完全支持事务的ACID特性。
  3. 支持事务所需要的两个特殊日志类型:Redo Log 和Undo Log

    Redo Log:实现事务的持久性(已提交的事务)。
    Undo Log:未提交的事务,独立于表空间,需要随机访问,可以存储在高性能io设备上。

Undo日志记录某数据被修改前的值,可以用来在事务失败时进行rollbackRedo日志记录某数据块被修改后的值,可以用来恢复未写入data file的已成功事务更新的数据。

2.4.2 特性二:支持行级锁

  1. InnoDB支持行级锁。
  2. 行级锁可以最大程度地支持并发。
  3. 行级锁是由存储引擎层实现的。

2.5 什么是锁

2.5.1 锁

锁的主要作用是管理共享资源的并发访问

锁用于实现事务的隔离性

2.5.2 锁类型

共享锁(也称读锁)

独占锁(也称写锁)

2.5.3 锁的粒度

MySQL的事务支持不是绑定在MySQL服务器本身而是与存储引擎相关

表级锁/行级锁

    将table_name加表级锁命令:lock table table_name write写锁会阻塞其它用户对该表的‘读写’操作,直到写锁被释放:unlock tables

  1. 锁的开销越大,粒度越小,并发度越高。
  2. 表级锁通常是在服务器层实现的。
  3. 行级锁是存储引擎层实现的。innodb的锁机制,服务器层是不知道的

2.5.4 阻塞和死锁

    (1)阻塞是由于资源不足引起的排队等待现象。
    (2)死锁是由于两个对象在拥有一份资源的情况下申请另一份资源,而另一份资源恰好又是这两对象正持有的,导致两对象无法完成操作,且所持资源无法释放。

2.6 如何选择正确的存储引擎

    参考条件:

  1. 事务
  2. 备份(Innobd免费在线备份)
  3. 崩溃恢复
  4. 存储引擎的特有特性

    总结:Innodb大法好。
    注意:尽量别使用混合存储引擎,比如回滚会出问题在线热备问题。

2.7 配置参数

2.7.1 内存配置相关参数

确定可以使用的内存上限。

内存的使用上限不能超过物理内存,否则容易造成内存溢出;(对于32位操作系统,MySQL只能试用3G以下的内存。)

确定MySQL的每个连接单独使用的内存。

sort_buffer_size #定义了每个线程排序缓存区的大小,MySQL在有查询、需要做排序操作时才会为每个缓冲区分配内存(直接分配该参数的全部内存);
join_buffer_size #定义了每个线程所使用的连接缓冲区的大小,如果一个查询关联了多张表,MySQL会为每张表分配一个连接缓冲,导致一个查询产生了多个连接缓冲;
read_buffer_size #定义了当对一张MyISAM进行全表扫描时所分配读缓冲池大小,MySQL有查询需要时会为其分配内存,其必须是4k的倍数;
read_rnd_buffer_size #索引缓冲区大小,MySQL有查询需要时会为其分配内存,只会分配需要的大小。

    注意:以上四个参数是为一个线程分配的,如果有100个连接,那么需要×100。

MySQL数据库实例:

 ①MySQL是单进程多线程(而oracle是多进程),也就是说MySQL实例在系统上表现就是一个服务进程,即进程;

 ②MySQL实例是线程和内存组成,实例才是真正用于操作数据库文件的;

一般情况下一个实例操作一个或多个数据库;集群情况下多个实例操作一个或多个数据库。

    如何为缓存池分配内存:
    Innodb_buffer_pool_size,定义了Innodb所使用缓存池的大小,对其性能十分重要,必须足够大,但是过大时,使得Innodb 关闭时候需要更多时间把脏页从缓冲池中刷新到磁盘中;

总内存-(每个线程所需要的内存*连接数)-系统保留内存

    key_buffer_size,定义了MyISAM所使用的缓存池的大小,由于数据是依赖存储操作系统缓存的,所以要为操作系统预留更大的内存空间;

select sum(index_length) from information_schema.talbes where engine='myisam'

    注意:即使开发使用的表全部是Innodb表,也要为MyISAM预留内存,因为MySQL系统使用的表仍然是MyISAM表。

    max_connections 控制允许的最大连接数, 一般2000更大。
    不要使用外键约束保证数据的完整性。

2.8 性能优化顺序

数据库结构设计和SQL语句

数据库存储引擎的选择和参数配置

系统选择及优化

硬件升级

三、十大优化建议

  1. 尽量避免在列上进行运算,这样会导致索引失效。例如原句为:
    SELECT * FROM t WHERE YEAR (d) >= 2011;
    优化为:
    SELECT * FROM t WHERE d >= 『2011-01-01』;
  2. 使用JOIN时,应该用小结果集驱动大结果集。同时把复杂的JOIN查询拆分成多个Query。因为JOIN多个表时,可能导致更多的锁定和堵塞。例如:
    SELECT * FROM a JOIN b ON a.id = b.aid LEFT JOIN c ON c.time = a.date LEFT JOIN d ON c.pid = b.aid LEFT JOIN e on e.cid = a.did;
  3. 注意LIKE模糊查询的使用,避免%%。例如原句为:
    SELECT * FROM t WHERE name LIKE 『%de%』;
    优化为:
    SELECT * FROM t WHERE name >= 『de』 AND name < 『df』;
  4. 仅列出需要查询的字段,这对速度不会有明显影响,主要考虑节省内存。例如原句为:
    SELECT * FROM member;
    优化为:
    SELECT id, name, pwd FROM member;
  5. 使用批量插入语句节省交互。例如原句为:
    INSERT INTO t (id, name) VALUES (1, 『a』);
    INSERT INTO t (id, name) VALUES (2, 『b』);
    INSERT INTO t (id, name) VALUES (3, 『c』);
    优化为:
    INSERT INTO t (id, name) VALUES (1, 『a』), (2, 『b』), (3, 『c』);

6.LIMIT的基数比较大的时候使用BETWEEN。例如原句为:
SELECT * FROM article ORDER BY id LIMIT 1000000, 10;
优化为:
SELECT * FROM article where id BETWEEN 1000000 and 1000010 ORDER BY id;

7. 不要使用rand函数获取多条随机记录。例如:
SELECT * FROM t ORDER BY rand() LIMIT 20;
使用下面语句代替:
SELECT * FROM t as t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM t) – (SELECT MIN(id) FROM t)) + (SELECT MIN(id) FROM t)) as id) as t2 WHERE t1.id>=t2.id ORDER BY t1.id LIMIT 1;

8. 避免使用NULL。

9. 不要使用COUNT(id),而应该使用COUNT(*)。

10. 不要做无谓的排序操作,而应尽可能在索引中完成排序。

MySQL EXPLAIN 结果参数注解

1. 用法

EXPLAIN 适用于 SELECT,DELETE,INSERT,REPLACE和UPDATE 语句,使用的时候直接在执行语句前面加 EXPLAIN 即可。

2. 输出格式

  • id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.
  • select_type: 执行语句的类型.
  • table: 查询的是哪个表
  • partitions: 匹配的分区
  • type: join 类型
  • possible_keys: 此次查询中可能选用的索引
  • key: 此次查询中确切使用到的索引.
  • ref: 哪个字段或常数与 key 一起被使用
  • rows: 显示此查询一共扫描了多少行. 这个是一个估计值.
  • filtered: 表示此查询条件所过滤的数据的百分比
  • extra: 额外的信息

3. 参数详解

select_type

select_type 表示了数据库语句操作的类型, select 类型常用取值有:

  • SIMPLE, 表示此查询不包含 UNION 查询或子查询
  • PRIMARY, 表示此查询是最外层的查询
  • UNION, 表示此查询是 UNION 的第二或随后的查询
  • DEPENDENT UNION, UNION 中的第二个或后面的查询语句, 取决于外面的查询
  • UNION RESULT, UNION 的结果
  • SUBQUERY, 子查询中的第一个 SELECT
  • DEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果

type

type 字段比较重要, 它提供了判断查询是否高效的重要依据依据. 通过 type 字段, 我们判断此次查询是 全表扫描 还是 索引扫描 等

type 常用的取值有:

  • system: 表中只有一条数据. 这个类型是特殊的 const 类型
  • const: 针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可
    例如下面的这个查询, 它使用了主键索引, 因此 type 就是 const 类型的
  • eq_ref: 此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查询的比较操作通常是 =, 查询效率较高
  • ref: 此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了 最左前缀 规则索引的查询
  • range: 表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录. 这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中.
    当 type 是 range 时, 那么 EXPLAIN 输出的 ref 字段为 NULL, 并且 key_len 字段是此次查询中使用到的索引的最长的那个
  • index: 表示全索引扫描(full index scan), 和 ALL 类型类似, 只不过 ALL 类型是全表扫描, 而 index 类型则仅仅扫描所有的索引, 而不扫描数据
    index 类型通常出现在: 所要查询的数据直接在索引树中就可以获取到, 而不需要扫描数据. 当是这种情况时, Extra 字段 会显示 Using index

通常来说, 不同的 type 类型的性能关系如下:
ALL < index < range ~ index_merge < ref < eq_ref < const < system
ALL 类型因为是全表扫描, 因此在相同的查询条件下, 它是速度最慢的.
index 类型的查询虽然不是全表扫描, 但是它扫描了所有的索引, 因此比 ALL 类型的稍快
后面的几种类型都是利用了索引来查询数据, 因此可以过滤部分或大部分数据, 因此查询效率就比较高了.

possible_keys

possible_keys 表示 MySQL 在查询时, 能够使用到的索引. 注意, 即使有些索引在 possible_keys 中出现, 但是并不表示此索引会真正地被 MySQL 使用到. MySQL 在查询时具体使用了哪些索引, 由 key 字段决定.

key_len

表示查询优化器使用了索引的字节数. 这个字段可以评估组合索引是否完全被使用, 或只有最左部分字段被使用到.
key_len 的计算规则如下:

  • 字符串
    • char(n): n 字节长度
    • varchar(n): 如果是 utf8 编码, 则是 3 n + 2字节; 如果是 utf8mb4 编码, 则是 4 n + 2 字节
  • 数值类型:
    • TINYINT: 1字节
    • SMALLINT: 2字节
    • MEDIUMINT: 3字节
    • INT: 4字节
    • BIGINT: 8字节
  • 时间类型
    • DATE: 3字节
    • TIMESTAMP: 4字节
    • DATETIME: 8字节
  • 字段属性: NULL 属性 占用一个字节. 如果一个字段是 NOT NULL 的, 则没有此属性

rows

rows 也是一个重要的字段. MySQL 查询优化器根据统计信息, 估算 SQL 要查找到结果集需要扫描读取的数据行数.
这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好.

Extra

EXplain 中的很多额外的信息会在 Extra 字段显示, 常见的有以下几种内容:

  • Using filesort
    当 Extra 中有 Using filesort 时, 表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大
  • Using index
    「覆盖索引扫描」, 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错
  • Using temporary
    查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化