MySQL如何高效查询

想要知道mysql如何高效查询，你必须对mysql的索引底层知识有一定的了解，下面以innodb为例。

在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。，InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。 每一个索引在 InnoDB 里面对应一棵 B+ 树。B+树的特性你可能需要单独了解一下，叶子节点有序存储数据，非叶子节点存储索引值。

查询性能提升方法一：应用中应该尽量使用主键查询

下面有一个例子，从索引存储数据方式 底层角度为你说明为什么要选择主键查询？

假设，我们有一个主键列为 ID 的表，表中有字段 k，并且在 k 上有索引

mysql> create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k))engine=InnoDB;

再假设插入这样五组数据（id,k）--> (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)。

主键id索引和非主键k索引的存储方式如下：

主键索引的叶子节点R（n）存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。

非主键k索引的叶子节点内容是主键id的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

例子：

如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；

如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。 也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

查询性能提升方法二：覆盖索引

根据上图，如果你需要查找3<k<5之间的id值，你会使用select * from T where k between 3 and 5。还是select ID from T where k between 3 and 5。答案肯定是第二种，但是我们往往在实际应用中可能忽略这个问题，因为两者都能达到我们的需求，我们没有过多的去考量。但其实这里选择第二种性能要高很多。

如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。 由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。 

查询性能提升方法三：最左前缀原则

假如，有这样一个以（name，age）组成的联合索引树。

如果你要查的是所有名字第一个字是“张”的人，你的 SQL 语句的条件是"where name like ‘张 %’"。这时，就能够用上这个索引，查找到第一个符合条件的记录是 ID3，然后向后遍历，直到不满足条件为止。

可以看到最左前缀原则可以用来查询某一范围内的数据，如果你有某一范围内的查询需求，可以试着在该字段上建立主键索引。

数据插入性能提升方法一：设置自增主键

我们从索引维护的角度来讲：

B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护。

以上面这个图为例，如果插入新的行 ID 值为 700，则只需要在 R5 的记录后面插入一个新记录。如果新插入的 ID 值为 400，就相对麻烦了，需要逻辑上挪动后面的数据，空出位置。

而更糟的情况是，如果 R5 所在的数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下，性能自然会受影响。 除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约 50%。

当然有分裂就有合并。当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。

自增主键是指自增列上定义的主键，在建表语句中一般是这么定义的： NOT NULL PRIMARY KEY AUTO_INCREMENT。 插入新记录的时候可以不指定 ID 的值，系统会获取当前 ID 最大值加 1 作为下一条记录的 ID 值。 也就是说，自增主键的插入数据模式，正符合了我们前面提到的递增插入的场景。每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。 而有业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高。

除了考虑性能外，我们还可以从存储空间的角度来看。假设你的表中确实有一个唯一字段，比如字符串类型的身份证号，那应该用身份证号做主键，还是用自增字段做主键呢？

由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键，那么每个二级索引的叶子节点占用约 20 个字节，而如果用整型做主键，则只要 4 个字节，如果是长整型（bigint）则是 8 个字节。 显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。