一条SQL查询语句如何执行的？？？

首先介绍一下MySQL：

mysql是关系数据库,但是说白了是一个软件。在我们复杂的软件世界中,大概分为那么两种软件,一种是CPU密集型,一种是I/O密集型。有这个思想很重要,比如面试官经常问你：

1. redis为什么是单线程;

2. 大数据word count用mapduce好吗;

这类问题,首先你就是要区分是CPU密集型还是I/O密集型。而作为一个数据库,显然是一个I/O密集型的软件。如何设计一个I/O密集型软件呢?

1. 你需要接收来自客户端的连接吧?

2. 是不是可以把一些经常访问的数据缓存起来?

3. SQL语句如何被我们要设计的系统识别呢?

4. 数据以何种方式在我们的软件中进行组织?

5. …

等等诸多问题。比如说数据结构redis中如何组织,答案是用了各种巧妙的数据结构,包括：双端链表、调表等。为什么redis单线程,因为它的瓶颈在I/O又不在计算,所以单线程啊,这个对应它的连接设计。而mysql如何设计呢?

MySQL分为两个部分：

           server层和存储引擎层

server层

连接池

当有client连接server端的时候,已经有连接被cache在连接池中,而不是每次来都创建一个连接。还需要管理授权,password user之类。
我们都有使用过mysql的经验

mysql -u root -p123456

其实建立了一个mysql-shell的客户端连接。可以通过如下命令来查看。

mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
|  2 | root | localhost | NULL | Query   |    0 | init  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)

一般用程序连接数据库时,用长连接,避免由于短连接对于频繁创建、销毁带来的性能损耗。但是长连接过多又会导致mysql消耗内存很快。

建立连接后如果长时间处于空闲状态，"show processlist" 命令可以看到到处于sleep状态的连接。
若规定时间内无活动，则会自动断开连接。规定时间 由 wait_timeout控制，默认为8小时。断开后，再次发请回会提示 "Lost connection to MySQL server during query" ,只能重新连接。

防止数据库中出现占用大量内存的情况，可以用一下方法解决：

1. 定期断开长连接或者占用内存过大的连接。
2. MySQL5.7及以上版本，每次执行一个较大的操作，可执行 "mysql_reset_connection"命令来初始化连接资源，该操作不会重连和重新获取授权，只是恢复到刚建立连接的状态。

service & utilitis

包括mysql的主备信息用具,分区工具等等。这个话题较为宏大,我们后面再谈。

Caches

如果是查询语句，在cache中命中结果则返回；相反的如果是更新语句在执行更新的时候,cache要被更新。而caches的功能在我们平时的软件设计中也经常用到,我们常用在读多、更新少的场景上，但是在很多时候缓存都是不命中的，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。因此，一般查询缓存是关闭的。而在mysql 8.0+版本中，查询缓存已经被移除掉了。

SQL parser

SQL词法分析、语法分析。这个和编译原理中我们说的词法和语法分析没啥区别。如果你的sql写得有问题,就是这个部分报出来的啦。

Optimizer

经过SQL parser。mysql就理解你的意图了,但是执行的时候如果有多个索引，我们先查那个呢? 这就是你在网上搜索到怎么建mysql索引的文章大堆大堆的原因，因为选择不同的索引，显然效率是不一样的。而如何看mysql是如何执行的呢?可以使用explain查看。那面试官问你为何一个查询慢,很显然你应该用explain命令查看执行的情况。

mysql> explain select * from Student;

存储引擎层

mysql中只有一个sever层，有多个存储引擎层。这点非常重要,因为后面会讲到mysql中的log。存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）。

可以通过如下命令来查看mysql 支持的存储引擎。

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

mysql 的默认存储引擎是InnoDB, 重点掌握两种即可。而谈及存储引擎,我们最重要的是索引,因为它直接决定了数据如何存取。

MyISAM索引实现

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图：

Primary Key

 

这里设表一共有三列，假设我们以Col1为主键，则图8是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

 

Secondary Key

 

同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

InnoDB索引实现

第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

Primary key

 

图10是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，图11为定义在Col3上的一个辅助索引：

Secondary Key

 

这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

 

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。