不得不了解的MySQL底层原理

MYSQL底层原理

order by 是怎样运行的

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB;

这时，你的SQL语句可以这样写：

select city,name,age from t where city='杭州' order by name limit 1000;

全字段排序

给city字段加上了索引，避免了全表扫描。但是order by name究竟会发生呢？

在这种a情况下会发生全字段排序，将所有满足city='杭州'的行全部取到内存中来，然后按照字段name进行排序，返回前1000行给客户端，如果内存空间不足，还需要借助磁盘临时文件来辅助排序。

使用临时文件进行辅助排序，其实使用是归并排序。MYSQL将需要排序的数据分成n份，每一份单独排序后放到临时文件中，然后将这n个临时文件再合并成一个有序的大文件。

rowid 排序

上面的算法有一个问题，那就是如果一行返回的字段太多的话，会占用大量的内存，同样的内存下能放下的行数也很少，要分成很多临时文件。

如果MYSQL认为排序的单行长度太大怎么办？

新的算法要放入sort_buffer的字段，只有要排序的字段(name字段)和主键id。

流程会变成以下这种:

从索引’city’中找到满足条件的主键id,再根据主键取出name,id这两个字段，放入sort_buffer中，重复上述操作知道索引’city’不满足where语句条件，对sort_buffer中的数据进行排序，取排序结果的前limit x的前x行，并按照id的值回表取出city,name和age这三个需要的字段返回给客户端。

这种方式你会发现需要的内存虽然变小了，但是多了一次回表的操作

全字段排序 VS rowid排序

MYSQL的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。

对于InnoDB来说，rowid排序要回表多造成了磁盘读，因此不会被优先选择。

但是，如果city和namea经常被查询并要order by的话，那其实也可以创建一个city和name的联合索引。

覆盖索引

覆盖索引指的是索引上的信息已经满足查询请求，不再需要回到主键索引上去取数据。

---

JOIN的’性能’到底如何

JOIN连接算法

MYSQL8.0中支持两种JOIN算法用于表之间的关联：

• Nested Loop Join;
• Hash Join; (其实Hash Join 在OLTP业务中很少用到，主要了解Nested Loop Join就行)

Nested Loop Join

for each row r in R with matching condition:

    lookup index idx_s on S where index_key = r

    if (found)

    send to client

在上述算法中，表R被成为驱动表，表R中通过where条件过滤出的数据会在表S对应的索引上进行一一查询，如果表R的数据量不大，上述算法非常高效。

Left Join驱动表一定是左表，Right Join驱动表一定是右表，但是对于Inner Join情况却有所不同，需要依情况而定。一般而言，谁需要的查询的数据量越少，谁就是驱动表

OLTP业务能不能写JOIN?

我只能说，相信MYSQL优化器的能力,但是要确保JOIN的索引都已经添加了，不然就变成全表扫描了。