phpmysqliselect技巧_MySQL这些底层实行事理早已成必备了

Server层：包括连接器、查询缓存、剖析器、优化器、实行器等，涵盖MySQL的大多数核心做事功能，以及所有的内置函数（如日期、韶光、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

存储引擎层：是一个可插拔的设计，也便是我们可以随意选择详细的存储引擎。
server端通过API与存储引擎进行通信，这些接口屏蔽了不同存储引擎的差异。
支持 InnoDB、MyISAM、Memory 等多个存储引擎。
从 MySQL 5.5 版本开始， InnoDB 成为了 MySQL 的默认存储引擎。

下面，借助一张《MySQL实战45讲》的图，来阐明全体实行流程

连接器

第一步，你会先连接到这个数据库上，这时候接待你的便是连接器。
连接器卖力跟客户端建立连接、获取权限、坚持和管理连接。
连接命令一样平常是这么写的：

mysql -h$ip -P$port -u$user -p

我们可以通过 TCP/IP ，命名管道和共享内存 ，Unix域套接字文件 等办法进行连接的建立。
当连接到做事器时，做事器会对密码，用户名，进行验证。

如果用户名或密码不对，你就会收到一个 “Access denied for user” 的缺点，然后客户端程序结束实行。

如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。
之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。

连接建立完成后，你就可以实行 select 语句了。
实行逻辑就会来到第二步：查询缓存。

MySQL 拿到一个查询要求后，会先到查询缓存看看，之前是不是实行过这条语句。
之前实行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。
key 是查询的语句，value 是查询的结果。
如果你的查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端。

但是大多数情形下我会建议你不要利用查询缓存，为什么呢？由于查询缓存每每弊大于利。
查询缓存的失落效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。

好在 MySQL 也供应了这种“按需利用”的办法。
你可以将参数 query_cache_type 设置成 OFF，关闭查询缓存。
也可以用 SQL_CACHE 显式指定，像下面这个语句一样：

mysql> selectSQL_CACHE from T whereID=10；

MySQL 8.0 版本直接将查询缓存的整块功能删掉了，也便是说 8.0 开始彻底没有这个功能了。

如果没有命中查询缓存，就要开始真正实行语句了。
首先，MySQL 须要知道你要做什么，因此须要对 SQL 语句做解析。

剖析器先会做“词法剖析”。
你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 须要识别出里面的字符串分别是什么，代表什么。

MySQL 从你输入的 “select” 这个关键字识别出来，这是一个查询语句。
它也要把字符串 “T” 识别成 “表名T”，把字符串 “ID” 识别成 “列ID”。

做完了这些识别往后，就要做“语法剖析”。
根据词法剖析的结果，语法剖析器会根据语法规则，判断你输入的这个SQL语句是否知足 MySQL 语法。

如果你的语句不对，就会收到 “You have an error in your SQL syntax” 的缺点提醒，比如下面这个语句 select 少打了开头的字母 “s”。

mysql> elect from t where ID=1;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL serverversionfor the rightsyntax touse near'elect from t where ID=1'at line 1

一样平常语法缺点会提示第一个涌现缺点的位置，以是你要关注的是紧挨着 “use near” 的内容。

经由了剖析器，MySQL就知道你要做什么了。
在开始实行之前，还要先经由优化器的处理。

优化器是在表里面有多个索引的时候，决定利用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。
比如你实行下面这样的语句，这个语句是实行两个表的 join：

mysql> select from t1 join t2 using(ID) where t1.c=10and t2.d=20;

1、既可以先从表 t1 里面取出 c=10 的记录的 ID 值，再根据 ID 值关联到表 t2，再判断 t2 里面 d 的值是否即是 20。

2、也可以先从表 t2 里面取出 c=20 的记录的 ID 值，再根据 ID 值关联到 t1，再判断 t1 里面 c 的值是否即是 10。

这两种实行方法的逻辑结果是一样的，但是实行的效率会有不同，而优化器的浸染便是决定选择利用哪一个方案。

优化器阶段完成后，这个语句的实行方案就确定下来了，然后进入实行器阶段。

MySQL 通过剖析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了实行器阶段，开始实行语句。

开始实行的时候，要先判断一下你对这个表 T 有没有实行查询的权限，如果没有，就会返回没有权限的缺点，如下所示。

mysql> select from T whereID=10;ERROR 1142 (42000): SELECT command denied touser'b'@'localhost'fortable'T'

如果有权限，就打开表连续实行。
打开表的时候，优化器就会根据表的引擎定义，去利用这个引擎供应的接口。

比如我们这个例子中的表 T 中，ID 字段没有索引，那么实行器的实行流程是这样的：

1、调用 InnoDB 引擎接口取这个表的第一行，判断ID值是不是10，如果不是则跳过，如果是则将这行存在结果集中；

2、调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的末了一行。

3、实行器将上述遍历过程中所有知足条件的行组成的记录集作为结果集返回给客户端。

对付有索引的实行逻辑也差不多，第一次调用“取知足条件的第一行”这个接口 ，然后循环取“知足条件的下一行”这个接口 。
这些接口存储引擎已经定义好了。

慢查询日志有个rows_examined的字段表示语句实行过程扫描了多少行，便是实行器每次调用引擎获取数据时累加的，但有时候，实行器调用一次，引擎扫描了多行，以是这个数和行数并不是完备关联的。

mysql的存储引擎是一个可插拔的设计，server层通过设定的一些api和存储引擎交互，存储引擎去实现这些api。
常见的存储引擎有InnoDB，myISAM,memory。

特点：

InnoDB数据库文件：

缺陷：

优点：

MyISAM引擎存储文件：

适用场景：MySQL5.5.5前默认的数据库引擎，在只读（或者写较少）、表较小（可以接管永劫光进行修复操作）的场景适用

所有的数据都保存在内存中，不须要进行磁盘I/O。
Memory表的构造在重启往后还会保留，但数据会丢失。

Memroy表在很多场景可以发挥好的浸染:

1.myISAM只缓存了索引块，减少了缓存换入换出的频率。

2.myISAM的表构造非聚簇索引，而InnoDB是聚簇索引，InnoDB的二级索引须要找到id回表查一级索引，而myISAM所有的索引直接指向数据行的存储位置offset。

3.INNODB还须要掩护MVCC同等；虽然你的场景没有，但他还是须要去检讨和掩护，而myisam 表锁.捐躯了写性能,提高了读性能.

一条sql语句在innodb底层的实行细节，如图所示。

用例：将id=1 更新为 id=2

1、更新数据

1-1、将id=1的信息写入回滚段 undo log 中；

1-2、undo log在系统表空间或在undo log 表空间，它本身的数据页也在缓冲池中，此时undo log的物理页数据变革也须要写 redo log ，但这不是主流程；

1-3、将缓冲池中 id=1改为 id=2；

2、将数据页的变革写 redo log 中，redo log根据配置 写：批量顺序写/主动写/被动写 磁盘

3、将数据变革SQL写binlog 日志，写入备库成功通过配置决定，写入：收到数据成功/relay log成功/实行成功；

4、提交事务；

以上是数据更新的粗略步骤。

我们首先实行一条sql：explain select from user where id =2;，实行后可以看到实行的结果如下：

id字段

id表示实行select查询语句的序号，它是sql实行的顺序的标识，sql按照id从大到小实行，id相同的为一组，从上到下实行。

「type字段表示的sql关联的类型或者说是访问的类型」。
从这个字段中我们可以确定这条sql查找数据库表的时候，查找记录的大概范围是怎么样的，直接就能表示sql的效率问题。

type字段的类型也是有比较多，紧张常见节制的有以下几个：system、const 、eq_ref 、ref 、range 、index 、ALL。
它的性能表示是从高到低。
system > const > eq_ref > ref > range > index > ALL，下面就来详细的说一说这属性。

system是const的特例，「表示表中只有一行记录」，这个险些不会涌现，也作为理解。

const表示通过索引一次就查找到了数据，一样平常const涌如今「唯一索引或者主键索引中利用等值查询」，由于表中只有一条数据匹配，以是查找的速率很快。
如：explain select from user where id =2;

eq_ref

「eq_ref表示利用唯一索引或者主键索引扫描作为表链接匹配条件，对付每一个索引键，表中只有一条记录与之匹配」。
例如：explain select from user left join role_user on user.id = role_user.user_id left join role on role_user.role_id=role.id;

ref

ref性能比eq_ref差，「也表示表的链接匹配条件，也便是利用哪些表字段作为查询索引列上的值」。
ref与eq_ref的差异便是：eq_ref利用的是唯一索引或者主键索引。
ref扫描后的结果可能会找到多条符合条件的行数据，实质上是一种索引访问，返回匹配的行。
例如：explain select from user where name = '张三';

这里type为ALL，解释name这个字段没有走索引，以是我们须要给name字段添加普通索引

在次实行，可以创造name走了普通索引。

range

「range利用索引来检索给定例模的行数据，一样平常是在where后面利用between、<>、in等查询语句就会涌现range」例如：explain select from user where id > 2;

index

「index表示会遍历索引树」，index回避ALL速率快一些，但是涌现index解释须要检讨自己的索引是否利用精确。
例如：explain select id from user;

ALL

「ALL与index的差异便是ALL是从硬盘中读取，而index是从索引文件中读取」，ALL全表扫描意味着Mysql会从表的头到尾进行扫描，这时候表示常日须要增加索引来进行优化了，或者说是查询中并没有利用索引作为条件进行查询 例如：explain select from user ;

Extra字段

该字段显示的是sql查询的额外信息，紧张有以下几种情形：Using index、Using where、Using temporary、Using temporary、Using join buffer、Impossible where、Select tables optimized away

Using index

表示查询的列被索引覆盖，这个是查询性能比较高的表示，即所要查询的信息搜在索引里面可以得到，不用回表，索引被精确的利用 例如：explain select id from user where id =2;

如果同时涌现了using where，表示索引用于实行索引键值的查找；若是没有涌现using where，则表示索引用于读取数据，而非实行查询的动作。

表示只用到了索引下推的条件。
即在组合索引中涵盖的列中覆盖了搜索条件，那么会在筛选出来前先用条件过滤一遍，再去回表，减少回表次数。
详细索引下推可以去查看innodb特性章节。

该属性与Using index相反，查询的列并没有被索引覆盖，where条件后面利用的是非索引的前导列，它仅仅是利用了where条件而已。
例如：explain select user. from user,role,role_user where user.id = role_user.user_id and role.id=role_user.role_id;

Using temporary

「Using temporary表示利用了临时表存储中间的结果，一样平常在对结果排序的时候会利用临时表」。
例如：排序order by 和分组查询group by。
例：explain select from (select name from user union select name from role) a where a.name = '张三' ;

Using filesort

Using filesort表示文件排序，解释Mysql对数据利用了外部的索引进行排序，并没有利用表中的索引进行排序。
例如：explain select from user order by name;

Using join buffer

Using join buffer表示利用连接缓存。
例如：explain select user. from user,role,role_user where user.id = role_user.user_id and role.id=role_user.role_id ;

它强调在获取连接条件时，并没有利用索引，而是利用连接缓冲区来存储中间结果，若是涌现该值，一样平常解释须要添加索引来进行优化了。

表示select语句没有遍历表或者索引就返回数据了。
例如：explain select min(id) from user ;

在Extra字段中还有其它的属性，但是险些都没见过的，不涌现，以是哪些就讲解，有兴趣的可以自己去理解，这里只列出这些常见的。

select from T where age=12andnameLike'小%';

像这样一条语句查询，如果走的是age索引，我们的实行流程是这样的。

这里是不是和我们想象的不一样，以为server层会查到所有结果，统一返回给客户真个，实际上不是，下面

再先容

实际上，mysql的结果会写入到net_buffer中，这块内存的大小是参数net_buffer_length决定的，默认16k。
重复获取行，直到写满net_buffer就发送，然后连续写。
而客户端那边对数据的聚合，是靠mysql的连接驱动来组装结果的。
也便是mysql是边写边发的。

count(0),count(),count(字段名)有啥差异？这个是我们大家关注的问题。

count(0)相称于是要统计全表的数据，优化器会选择一个非主键索引的最小二级索引。
为啥这样呢？由于主键索引里面有记录行，会导致一页中能存储的记录行更少。
而二级索引页存储了所有行的信息，只须要遍历更少的额页就能统计出我们须要的全行数。

count(字段名)是统计表中，该字段不为null的所有记录数的总和，逻辑上就不同了。

SELECTCOUNT() FROM t;

在实行上述查询时，server层会掩护一个名叫count的变量，然后：

SELECT FROM t ORDERBY key1 LIMIT5000, 1;

上面这条语句的意思是，以key1正序的结果中，跳过5000行，之后查询1行返回给客户端。

如果利用idx_key1实行上述查询，那么MySQL会这样处理：

从上述过程中我们可以看到，由于MySQL中是在实际向客户端发送记录前才会去判断LIMIT子句 是否符合哀求，以是如果利用二级索引实行上述查询的话，意味着要进行5001次回表操作。
server层在进行实行操持剖析的时候会以为实行这么多次回表的本钱太大了，还不如直接全表扫描 +filesort快呢，以是就选择了后者实行查询。

limit语句的实行效率也太低了，如果提高呢？limit的局限在于须要跳过很多没用的行，如果翻页太深，比如limit 50000，1.须要跳过50000行，那也太坑了，如何避免？

可以借助主键的游标cursor。
如果是对一张表的跑批，要翻页多次跑出所有的数据。
我们初始方案可能是

SELECT FROM t ORDERBYidLIMIT0, 100;--第一页SELECT FROM t ORDERBYidLIMIT100, 100;--第二页SELECT FROM t ORDERBYidLIMIT100, 100;--第三页

采取了游标后,每次都取最新的100条，不须要有跳过的行，详细的数据靠保存上一页的自增id cursor来定位。

SELECT FROM t whereid>0ORDERBYidLIMIT0, 100;--第一页SELECT FROM t whereid>100ORDERBYidLIMIT0, 100;--第二页SELECT FROM t whereid>200ORDERBYidLIMIT0, 100;--第三页

利用游标的方案，一定要记得对游标列排序

selectname,age,city from staff where city = '深圳'orderby age limit10;

这样一条sql语句，看一下实行操持，会创造 Extra 这个字段的 Using filesort表示用到排序。

那么他是怎么排序的呢？

MySQL 会给每个查询线程分配一块小内存，用于排序的，称为 sort_buffer。
什么时候把字段放进去排序呢，实在是通过idx_city索引找到对应的数据，才把数据放进去啦。

img

上面实行语句，整体的实行流程便是：

img

如果数据太多，内存放不下怎么办？

实际上，sort_buffer的大小是由一个参数掌握的：sort_buffer_size。
如果要排序的数据小于sort_buffer_size，排序在sort_buffer 内存中完成，如果要排序的数据大于sort_buffer_size，则借助磁盘文件来进行排序。

利用了磁盘临时文件，全体排序过程又是若何的呢？

TPS: 借助磁盘临时小文件排序，实际上利用的是归并排序算法。

rowid 排序便是，只把查询SQL须要用于排序的字段和主键id，放到sort_buffer中。
那怎么确定走的是全字段排序还是rowid 排序排序呢？

实际上有个参数掌握的。
这个参数便是max_length_for_sort_data，它表示MySQL用于排序行数据的长度的一个参数，如果单行的长度超过这个值，MySQL 就认为单行太大，就换rowid 排序。
我们可以通过命令看下这个参数取值。

利用rowid 排序的话，全体SQL实行流程又是若何的呢？

实行示意图如下：

img

比拟一下全字段排序的流程，rowid 排序多了一次回表。

我们如何优化order by语句呢？

比如我们可以建立联合索引idx_city_age，这样查询的数据就不须要用到内存排序了，在索引树上便是我们想要的顺序效果。

img

ref：order by详解

explainselect city ,count() asnumfrom staff groupby city;

这样一条group by语句

group by 怎么就利用到临时表和排序了呢？我们来看下这个SQL的实行流程

explainselect city ,count() asnumfrom staff groupby city;

我们一起来看下这个SQL的实行流程哈

这个流程的实行图如下：

img

group by会默认排序，如果不肯望排序，可以显示指定不排序。

explainselect city ,count() asnumfrom staff groupby city orderbynull;

img

要利用到疏松索引扫描实现 GROUP BY，须要至少知足以下几个条件：

为什么疏松索引扫描的效率会很高?

由于在没有WHERE子句，也便是必须经由全索引扫描的时候， 疏松索引扫描须要读取的键值数量与分组的组数量一样多，也便是说比实际存在的键值数目要少很多。
而在WHERE子句包含范围判断式或者等值表达式的时候， 疏松索引扫描查找知足范围条件的每个组的第1个关键字，并且再次读取尽可能最少数量的关键字。

和疏松索引扫描的差异是须要读取所有知足条件的索引值，之后取数据完成操作。
Extra中不显示for group-by 在 MySQL 中，首先会选择考试测验通过疏松索引扫描来实现 GROUP BY 操作，当创造某些情形无法知足疏松索引扫描实现 GROUP BY 的哀求之后，才会考试测验通过紧凑索引扫描来实现。
（比如GROUP BY 条件字段并不连续或者不是索引前缀部分的时候）

当无法找到得当的索引可以利用的时候，就不得不先读取须要的数据，然后通过临时表来完成 GROUP BY 操作。
Extra：Using temporary; Using filesort

mysql的join算法叫做Nested-Loop Join（嵌套循环连接）

而这个Nested-Loop Join有三种变种，下面分别先容下

这个算法相称大略、直接。
即驱动表中的每一条记录与被驱动表中的记录进行比较判断（便是个笛卡尔积）。
对付两表联接来说，驱动表只会被访问一遍，但被驱动表却要被访问到好多遍

假设R为驱动表，S被驱动表，用伪代码表示一下这个过程便是这样：

for r in R # 扫描R表（驱动表） for s in S # 扫描S表（被驱动表） if (r and s satisfy the join condition) # 如果r和s知足join条件 output result # 返回结果集

以是如果R有1万条数据，S有1万条数据，那么数据比较的次数1万 1万 =1亿次，这种查询效率会非常慢。

这个是基于索引进行连接的算法

它哀求被驱动表上有索引，可以通过索引来加速查询。

假设R为驱动表，S被驱动表，用伪代码表示一下这个过程便是这样：

For r in R # 扫描R表 for s in Sindex # 查询S表的索引（固定3~4次IO，B+树高度） if (s == r) # 如果r匹配了索引s output result # 返回结果集Block Nested-Loop

这个算法较Simple Nested-Loop Join的改进就在于可以减少被驱动表的扫描次数

由于它利用Join Buffer来减少内部循环读取表的次数

假设R为驱动表，S被驱动表，用伪代码表示一下这个过程便是这样：

for r in R # 扫描表R store p from R in Join Buffer # 将部分或者全部R的记录保存到Join Buffer中，记为p for s in S # 扫描表S if (p and s satisfy the join condition) # p与s知足join条件 output result # 返回为结果集

可以看到比较Simple Nested-Loop Join算法，Block Nested-LoopJoin算法仅多了一个所谓的Join Buffer

为什么这样就能减少被驱动表的扫描次数呢？

下图比较更好地阐明了Block Nested-Loop Join算法的运行过程

img

可以看到Join Buffer用以缓存联接须要的列（以是再次提醒我们，最好不要把作为查询列表，只须要把我们关心的列放到查询列表就好了，这样还可以在join buffer中放置更多的记录呢，是不是这个道理哈，哈哈）

然后以Join Buffer批量的形式和被驱动表中的数据进行联接比较。

如果被驱动表的数据超过了join buffer的size，那么就会把它分成多个join buffer文件块，每条驱动表数据都和多个块数据联表。

关于Join Buffer

总结

在选择Join算法时，会有优先级：

Index Nested-LoopJoin > Block Nested-Loop Join > Simple Nested-Loop Join

当不该用Index Nested-Loop Join的时候，默认利用Block Nested-Loop Join。

利用Block Nested-Loop Join算法须要开启优化器管理配置的optimizer_switch的设置block_nested_loop为on，默认为开启。

通过上面的大略先容，可以总结出以下几种优化思路

1.用小结果集驱动大结果集，减少外层循环的数据量

2.如果小结果集和大结果集连接的列都是索引列，mysql在join时也会选择用小结果集驱动大结果集，由于索引查询的本钱是比较固定的，这时候外层的循环越少，join的速率便越快。

3.为匹配的条件增加索引：争取利用Index Nested-Loop Join，减少内层表的循环次数

4.增大join buffer size的大小：当利用Block Nested-Loop Join时，一次缓存的数据越多，那么外层表循环的次数就越少，减少不必要的字段查询：

5.当用到Block Nested-Loop Join时，字段越少，join buffer 所缓存的数据就越多，外层表的循环次数就越少；

好了，本日就分享这么多。

原文链接：https://mp.weixin.qq.com/s/g02t-Mgp6zf8KIuoIBp3gw

标签：索引实行