首页  思维导图  详情



 



Mysql

2023-11-15 22:19:52   0  举报





仅支持查看

商业战略

模板推荐

作者其他创作

大纲/内容

索引底层数据结构

概念：帮助Mysql高效获得数据的排好序的数据结构。

数据结构

Innodb和mylsam都使用B+树

按照有序排列，叶子节点高度相同

所有索引元素不重复

非叶子节点只包含索引不包含数据,叶子节点即包括索引也包括数据

叶子节点包含所有的索引和数据(即存在冗余节点）

叶子节点之间有双向指针连接

弃用的数据结构

二叉树

在某些极端情况下，比如单边增长的数据，二叉树实际上就是个链表，对数据查询没有什么实际性的帮助

红黑树(平衡二叉树)

在大数据量下会导致树的高度不可控，会使io次数过多影响效率

B树

结构图

特点

按照有序排列，叶子节点高度相同

所有索引元素不重复

每个节点包含数据和索引且不包含冗余节点

底层叶子节点之间没有指针

弃用原因

因为非叶子节点会存在数据，非叶子节点存储的节点就会变少(因为mysql一次io会取出一个节点,一个节点最多就是一页的大小，一个数据页的默认大小为16kb)，同样的数据量相比B+树层高会高很多。而且叶子节点之间没有指针，范围查找效率会很低。

hash

对索引的key进行一次hash计算就可以定位出数据存储的位置。很多时候Hash索引要比B+ 树索引更高效。

但是仅能满足 “=”，“IN”，不支持范围查询。还会有hash冲突问题。

结构图

联合索引结构图

为什么建议InnoDb表一定建主键

如果不设置主键，mysql需要自己去找第一个非空唯一索引作为聚簇索引，如果找不到，mysql则会自己维护一个隐藏列(rowid)建立聚簇索引。 当我们设置了主键，如果业务场景查询条件是主键时，则查询效率会很快。如果没建立主键，就还需要进行回表操作，影响查询效率。

为什么推荐使用整型自增的主键

使用整型的主键比uuid占用空间少，相比而言B+树的磁盘页存储的数据量更多。

整型比较大小，效率是优于varchar类型的uuid

因为B+树是有序的，如果使用非自增插入，则需要在中间插入，可能会导致节点分列，插入效率会慢

为什么非主键索引叶子节点存储的是innodb的聚簇索引的值

节约空间

数据一致性

Tips

数据库的存储引擎是在表层次失效的而不是在数据库层面生效的

mylsam的索引文件和数据文件是放在一块的，所以mylsam不区分主键索引和辅助索引，叶子节点存放的都是索引字段和数据文件的地址。而Innodb只有一个聚集索引，聚集索引存放的是全部的数据，而其余的索引叶子节点存放的都是主键值

回表即通过辅助索引查到之后，根据辅助索引存放的主键值回到innodb的聚集索引去找数据的操作

索引存储在安装目录data下面，每个库对应一个目录。myisam的每个表对应.frm .myd .myi文件。Innodb每个表对应.frm .ibd文件

最左前缀原则：在联合索引中，查询从索引的最左前列开始并且不跳过索引中的列（顺序可以颠倒，因为mysql会对sql进行优化，使其符合联合索引顺序）

explain

简介：使用EXPLAIN关键字可以模拟优化器执行SQL语句，执行查询会返回执行计划的信息，而不是执行这条SQL

变种

explain extended

会在 explain 的基础上额外提供一些查询优化的信息。紧随其后通过 show warnings 命令可以得到优化后的查询语句，从而看出优化器优化了什么。额外还有 filtered 列，是一个百分比的值，rows *filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数（前一个表指 explain 中的id值比当前表id值小的表）。

explain partitions

相比 explain 多了个 partitions 字段，如果查询是基于分区表的话，会显示查询将访问的分区。

各列

id列的编号是select的序列号，有几个select就有几个id，并且id的顺序是按 select 出现的顺序增长的。

id越大的执行优先级越大，id相同则从上到下执行，id为null最后执行。

select_type

simple

简单查询

primary

复杂查询最外层的查询

subQuery

select后的子查询(不在from字句中)

derived

包含在from内的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义）

union

在union中的第二个select语句

table

表示该行查询的哪个表，当 from 子句中有子查询时，table列是<derivenN>格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。 当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的 select 行id。

partitions

如果查询是基于分区查询，partitions表示访问的分区

type

Null

表示mysql在优化阶段就能拿到数据，在执行阶段用不着再去访问表或索引

system

指一个表中只有一条数据。是const的一条特例。

const

查找的只有一条数据。指查找的主键索引和唯一索引时，且条件为一个常量。

eq_ref

唯一性索引扫描，即主键索引或唯一索引的字段作为查询条件时，但是查询条件不是常量，而是变量的时候

ref

非唯一性索引扫描，不使用唯一索引，而是使用普通索引或唯一性索引的部分前缀。可能会查询出多条记录

range

范围扫描，查询的是索引的一个范围。

index

遍历二级索引就能拿到想要的数据，即使用覆盖索引。只需要去遍历二级索引的叶子节点，而不用去扫描主键索引的叶子节点。

all

扫描主键索引的叶子节点。

possible_key

可能用到的索引

key

这一列显示mysql实际采用哪个索引来优化对该表的访问。

如果没有使用索引，则该列是 NULL。

如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index。

key_len

mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列

计算规则

字符串

char(n)

如果存汉字长度就是 3n 字节

varchar(n)

如果存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度，因为varchar是变长字符串

数值

tinyint：1字节

smallint：2字节

int：4字节

bigint：8字节

时间类型

date：3字节

timestamp：4字节

datetime：8字节

ref

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）。我理解的是一个查询的where条件用到的常量或者变量

rows

这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。

filtered

该列是一个百分比的值，rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数（前一个表指explain 中的id值比当前表id值小的表）。

extra

Using index

使用覆盖索引

定义：整个查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值。覆盖索引并不是一种索引，而是一种查询方式

Using where

使用 where 语句来处理结果，并且查询的列未被索引覆盖。也就是没有走索引 ，需要全表扫描，这种的一般需要优化。

Using index condition

查询的列不完全被索引覆盖，where条件中是一个索引前一部分的范围

Using temporary

mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。

Using filesort

将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。

Select tables optimized away

使用某些聚合函数（比如 max、min）来访问存在索引的某个字段时

..........

索引优化

索引下推（Index Condition Pushdown，ICP）

若只用到了覆盖索引的前n个索引，mysql5.6之前会根据前几个索引匹配到的数据，逐一进行回表操作，得到数据之后。再去匹配是否符合剩余条件。 mysql5.6引入索引下推，在索引的遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。

为什么范围查找比如(>,<)没有使用索引下推优化

估计应该是Mysql认为范围查找过滤的结果集过大，like KK% 在绝大多数情况来看，过滤后的结果集比较小，所以这里Mysql选择给 likeKK% 用了索引下推优化，当然这也不是绝对的，有时like KK% 也不一定就会走索引下推。

trace工具

如何使用

开启关闭

开启：set session optimizer_trace=enabled=on,end_markers_in_json=on; 关闭：set session optimizer_trace=enabled=off; 开启trace工具会对MySQL的性能造成一定的影响，所以建议在需要分析SQL语句的执行计划生成过程时才开启trace工具；

执行要分析的sql语句

查询执行计划执行

SELECT * FROM information_schema.OPTIMIZER_TRACE;

结构分析

sql准备阶段（sql格式化）

子主题

sql分析优化阶段

关键节点即rows_estimation,可以看到使用哪种查询方式的成本(cost)

sql执行阶段

优化原则

最左前缀法则: 如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列（顺序可以颠倒，因为mysql会对sql进行优化，使其符合联合索引顺序）。

不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描。比如经典错误，字符串类型不加引号，mysql底层会执行类型转换的操作而无法走索引。

存储引擎不能使用索引中范围条件右边的列，范围前的列和范围的列都能走索引，范围后的列不能走索引。因为范围后的列是无序的。可以多想想B+树的结构。

尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少 select * 语句。也就是尽量扫描二级索引而不要扫描主键索引

mysql在使用不等于（！=或者<>），not in ，not exists 的时候无法使用索引会导致全表扫描。

< 小于、 > 大于、 <=、>= 这些，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引，也就是mysql底层的成本计算。

is null,is not null 一般情况下也无法使用索引

like以通配符开头（'%abc...'）mysql索引失效会变成全表扫描操作(优化方式：使用覆盖索引，查询字段必须是建立覆盖索引字段。虽然效率也不高)

字符串不加单引号索引失效

少用or或in，用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引，详见范围查询优化

没走索引原因：mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。

like KK%相当于=常量，%KK和%KK% 相当于范围

大部分情况下mysql都会用一个索引，所以尽量建覆盖索引,建索引最好最多不要超过三个。读多写少可以适当多建两个。

各种优化

order by/group by

原理及优化

MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。

order by满足两种情况会使用Using index。

order by语句使用索引最左前列

使用where子句与order by子句条件列组合满足索引最左前列。

尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。能用覆盖索引尽量用覆盖索引

group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中的限定条件就不要去having限定了。

如果order by的条件不在索引列上，就会产生Using filesort。

Using filesort文件排序原理

单路排序

是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；用trace工具可以看到sort_mode信息里显示< sort_key, additional_fields >或者< sort_key,packed_additional_fields >

双路排序（又叫回表排序模式）

是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；用trace工具可以看到sort_mode信息里显示< sort_key, rowid >

使用哪种

MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式。 如果 字段的总长度小于max_length_for_sort_data ，那么使用 单路排序模式； 如果 字段的总长度大于max_length_for_sort_data ，那么使用 双路排序模式。

优化建议

代码先行，索引后上

联合索引尽量覆盖条件

不要在小基数字段上建立索引（比如性别等）

长字符串我们可以采用前缀索引

where与order by冲突时优先where（因为大多数情况基于索引进行where筛选往往可以最快速度筛选出你要的少部分数据，然后做排序的成本可能会小很多）

基于慢sql查询做优化

分页查询优化

自增且连续的主键排序的分页查询

可以通过范围查询进行优化。例：EXPLAIN select * from employees where id > 90000 limit 5;

根据非主键字段排序的分页查询

通过内连接来进行优化。例：select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;

join关联优化

两种算法

嵌套循环连接 Nested-Loop Join(NLJ) 算法：一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集。

基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法：把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比。

MySQL对于被驱动表的关联字段没索引的关联查询，一般都会使用 BNL 算法。如果有索引一般选择 NLJ 算法，有索引的情况下 NLJ 算法比 BNL算法性能更高

优化方式

关联字段加索引，让mysql做join操作时尽量选择NLJ算法

小表驱动大表，写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去mysql优化器自己判断的时间

straight_join解释：straight_join功能同join类似，但能让左边的表来驱动右边的表，能改表优化器对于联表查询的执行顺序。 straight_join只适用于inner join，并不适用于left join，right join。（因为left join，right join已经代表指定了表的执行顺序） 尽可能让优化器去判断，因为大部分情况下mysql优化器是比人要聪明的。使用straight_join一定要慎重，因为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱

小表明确定义：在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

in和exsits优化

select * from A where id in (select id from B) in：当B表的数据集小于A表的数据集时，in优于exists

select * from A where exists (select 1 from B where B.id = A.id) exists：当A表的数据集小于B表的数据集时，exists优于in

count(*)查询优化

字段有索引：count(*)≈count(1)>count(字段)>count(主键 id) //字段有索引，count(字段)统计走二级索引，二级索引存储数据比主键索引少，所以count(字段)>count(主键 id)

字段无索引：count(*)≈count(1)>count(主键 id)>count(字段) //字段没有索引count(字段)统计走不了索引，count(主键 id)还可以走主键索引，所以count(主键 id)>count(字段)

优化方式

查询mysql自己维护的总行数

对于myisam存储引擎的表做不带where条件的count查询性能是很高的，因为myisam存储引擎的表的总行数会被mysql存储在磁盘上，查询不需要计算。对于innodb存储引擎的表mysql不会存储表的总记录行数(因为有MVCC机制)，查询count需要实时计算

show table status like "表名"

如果只需要知道表总行数的估计值可以用如下sql查询，性能很高，但并不精确

将总数维护到Redis里

插入或删除表数据行的时候同时维护redis里的表总行数key的计数值(用incr或decr命令)，但是这种方式可能不准，很难保证表操作和redis操作的事务一致性

增加数据库计数表

插入或删除表数据行的时候同时维护计数表，让他们在同一个事务里操作

mysql事务

为了解决多事务并发问题，数据库设计了事务隔离机制、锁机制、MVCC多版本并发控制隔离机制、日志机制，用一整套机制来解决多事务并发问题。

概念：事务是一组操作要么全部成功，要么全部失败，目的是为了保证数据最终的一致性。

属性：

原子性(Atomicity)：当前事务的操作要么同时成功，要么同时失败。原子性由undo log日志来实现。

一致性(Consistent) ：使用事务的最终目的，由其它3个特性以及业务代码正确逻辑来实现。

隔离性(Isolation) ：在事务并发执行时，他们内部的操作不能互相干扰。隔离性由MySQL的各种锁以及MVCC机制来实现。

持久性(Durable) ：一旦提交了事务，它对数据库的改变就应该是永久性的。持久性由redo log日志来实现。

并发带来的问题

更新丢失(Lost Update)或脏写：当两个或多个事务选择同一行数据修改，有可能发生更新丢失问题，即最后的更新覆盖了由其他事务所做的更新。

脏读（Dirty Reads): 事务A读取到了事务B已经修改但尚未提交的数据

不可重复读（Non-Repeatable Reads）:事务A内部的相同查询语句在不同时刻读出的结果不一致

幻读（Phantom Reads): 事务A读取到了事务B提交的新增数据

事务隔离级别

“脏读”、“不可重复读”和“幻读”,其实都是数据库读一致性问题,必须由数据库提供一定的事务隔离机制来解决。

tips

数据库的事务隔离越严格,并发副作用越小,但付出的代价也就越大,因为事务隔离实质上就是使事务在一定程度上“串行化”进行,这显然与“并发”是矛盾的。

不同的应用对读一致性和事务隔离程度的要求也是不同的,比如许多应用对“不可重复读"和“幻读”并不敏感,可能更关心数据并发访问的能力。

Mysql默认的事务隔离级别是可重复读，用Spring开发程序时，如果不设置隔离级别默认用Mysql设置的隔离级别，如果Spring设置了就用已经设置的隔离级别

查看当前数据库的事务隔离级别: show variables like 'tx_isolation';

设置事务隔离级别：set tx_isolation='REPEATABLE-READ';

锁分类

性能来分

乐观锁(用版本对比或CAS机制)

悲观锁：所以每次在拿数据的时候都会上锁

粒度来分

表锁

每次操作锁住整张表。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；一般用在整表数据迁移的场景。

页锁

只有BDB存储引擎支持页锁，页锁就是在页的粒度上进行锁定，锁定的数据资源比行锁要多，因为一个页中可以有多个行记录。当我们使用页锁的时候，会出现数据浪费的现象，但这样的浪费最多也就是一个页上的数据行。页锁的开销介于表锁和行锁之间，会出现死锁。锁定粒度介于表锁和行锁之间，并发度一般。

行锁

每次操作锁住一行数据。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高

对数据库的操作类型来分

读锁（共享锁，S锁(Shared)）：针对同一份数据，多个读操作可以同时进行而不会互相影响

写锁（排它锁，X锁(eXclusive)）：当前写操作没有完成前，它会阻断其他写锁和读锁，数据修改操作都会加写锁，查询也可以通过for update加写锁

意向锁：（Intention Lock）：又称I锁，针对表锁，主要是为了提高加表锁的效率，是mysql数据库自己加的。当有事务给表的数据行加了共享锁或排他锁，同时会给表设置一个标识，代表已经有行锁了，其他事务要想对表加表锁时，就不必逐行判断有没有行锁可能跟表锁冲突了，直接读这个标识就可以确定自己该不该加表锁。特别是表中的记录很多时，逐行判断加表锁的方式效率很低。而这个标识就是意向锁。

意向共享锁，IS锁，对整个表加共享锁之前，需要先获取到意向共享锁。

意向排他锁，IX锁，对整个表加排他锁之前，需要先获取到意向排他锁

注意：

InnoDB的行锁实际上是针对索引加的锁(在索引对应的索引项上做标记)，不是针对整个行记录加的锁。并且该索引不能失效，否则会从行锁升级为表锁。(RR级别会升级为表锁，RC级别不会升级为表锁)

关于RR级别行锁升级为表锁的原因分析： 因为在RR隔离级别下，需要解决不可重复读和幻读问题，所以在遍历扫描聚集索引记录时，为了防止扫描过的索引被其它事务修改(不可重复读问题) 或 间隙被其它事务插入记录(幻读问题)，从而导致数据不一致，所以MySQL的解决方案就是把所有扫描过的索引记录和间隙都锁上，这里要注意，并不是直接将整张表加表锁，因为不一定能加上表锁，可能会有其它事务锁住了表里的其它行记录。

间隙锁(Gap Lock)

间隙锁，锁的就是两个值之间的空隙，间隙锁是在可重复读隔离级别下才会生效。Mysql默认级别是repeatable-read，有幻读问题，间隙锁是可以解决幻读问题的。

临键锁（next-key锁）

next-key锁其实包含了记录锁（行锁）和间隙锁，即锁定一个范围，并且锁定记录本身，InnoDB默认加锁方式是next-key 锁

MVCC机制

简介

同样的sql查询语句在一个事务里多次执行查询结果相同，就算其它事务对数据有修改也不会影响当前事务sql语句的查询结果。 这个隔离性就是靠MVCC(Multi-Version Concurrency Control)机制来保证的，对一行数据的读和写两个操作默认是不会通过加锁互斥来保证隔离性，避免了频繁加锁互斥，而在串行化隔离级别为了保证较高的隔离性是通过将所有操作加锁互斥来实现的。

Mysql在读已提交和可重复读隔离级别下都实现了MVCC机制

undo日志版本链

undo日志版本链是指一行数据被多个事务依次修改过后，在每个事务修改完后，Mysql会保留修改前的数据undo回滚日志，并且用两个隐藏字段trx_id和roll_pointer把这些undo日志串联起来形成一个历史记录版本链。

read-view

一致性视图read-view由执行查询时所有未提交事务id数组（数组里最小的id为min_id）和已创建的最大事务id（max_id）组成，事务里的任何sql查询结果需要从对应版本链里的最新数据开始逐条跟read-view做比对从而得到最终的快照结果。

RR隔离级别，事务里每次执行查询操作readview都是使用第一次查询时生成的readview，也就是都是以第一次查询时当时数据库里所有事务提交状态来比对数据是否可见，当然可以实现每次查询的可重复读的效果了。

RC隔离级别，事务里每次执行查询操作readview都会按照数据库当前状态重新生成readview，也就是每次查询都是跟数据库里当前所有事务提交状态来比对数据是否可见，当然实现的就是每次都能查到已提交的最新数据效果了。

注意：begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个修改操作或加排它锁操作(比如select...for update)的语句，事务才真正启动，才会向mysql申请真正的事务id，mysql内部是严格按照事务的启动顺序来分配事务id的。

总结： MVCC机制的实现就是通过read-view机制与undo版本链比对机制，使得不同的事务会根据数据版本链对比规则读取同一条数据在版本链上的不同版本数据。

版本链比对规则

如果 row 的 trx_id 落在绿色部分( trx_id<min_id )，表示这个版本是已提交的事务生成的，这个数据是可见的；

如果 row 的 trx_id 落在红色部分( trx_id>max_id )，表示这个版本是由将来启动的事务生成的，是不可见的(若 row 的trx_id 就是当前自己的事务是可见的）；

如果 row 的 trx_id 落在黄色部分(min_id <=trx_id<= max_id)

若 row 的 trx_id 在视图数组中，表示这个版本是由还没提交的事务生成的，不可见(若 row 的 trx_id 就是当前自己的事务是可见的)；

若 row 的 trx_id 不在视图数组中，表示这个版本是已经提交了的事务生成的，可见。

对于删除的情况可以认为是update的特殊情况，会将版本链上最新的数据复制一份，然后将trx_id修改成删除操作的trx_id，同时在该条记录的头信息（record header）里的（deleted_flag）标记位写上true，来表示当前记录已经被删除，在查询时按照上面的规则查到对应的记录如果delete_flag标记位为true，意味着记录已被删除，则不返回数据。

rr级别下：不管进行什么操作read-view是不变的，select操作是快照读（历史版本）；insert、update和delete之后，自己修改的那条数据可以查到最新的，但是其他的数据还是根据read-view一致性视图进行的操作。

Mysql底层架构

Server层

连接器

navicat,mysql front,jdbc,SQLyog等客户端要向mysql发起通信都必须先跟Server端建立通信连接，而建立连接的工作就是有连接器完成的。

分析器

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

分析器先会做“词法分析”。你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是什么，代表什么。 MySQL 从你输入的"select"这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别成“表名 T”，把字符串“ID”识别成“列 ID”。

做完了这些识别以后，就要做“语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。 如果你的语句不对，就会收到“You have an error in your SQL syntax”的错误提醒，比如下面这个语句 from 写成了 "rom"。 select * fro test where id=1; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'fro test where id=1' at line 1

执行器

开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限，如果没有，就会返回没有权限的错误，如下所示 (在工程实现上，如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证)。 select * from test where id=10; 如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

查询缓存

MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。如果你的查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端。 如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。你可以看到，如果查询命中缓存，MySQL 不需要执行后面的复杂操作，就可以直接返回结果，这个效率会很高。

大多数情况查询缓存就是个鸡肋，为什么呢？

因为查询缓存往往弊大于利。查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。 因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低。

一般建议在静态表里使用查询缓存，什么叫静态表呢？就是一般我们极少更新的表。比如，一个系统配置表、字典表，那这张表上的查询才适合使用查询缓存。好在 MySQL 也提供了这种“按需使用”的方式。你可以将my.cnf参数 query_cache_type 设置成 DEMAND。

存储引擎层

Innodb日志机制

BinLog 是追加写入，写完一个日志文件再写下一个日志文件，不会覆盖使用，而Redo Log 是循环写入，日志空间的大小是固定的，会覆盖使用。 Bin Log 一般用于主从复制和数据恢复，并且不具备崩溃自动恢复的能力，而 Redo Log 是在服务器发生故障后重启 MySQL，用于恢复事务已提交但未写入数据表的数据。