深入理解mysql的联合索引

最近在学习MySQL的存储引擎和索引的知识。看了许多篇介绍MyISAM和InnoDB的索引的例子，都能理解。

像这张索引图：

PS:该图来自大神张洋的《MySQL索引背后的数据结构及算法原理》一文。

但许多文章讲述的都是单列索引，我很好奇 联合索引对应的结构图是怎样的。

比方说联合索引 (col1, col2,col3)，我知道在逻辑上是先按照col1进行排序再按照col2进行排序最后再按照col3进行排序。因此如果是select * from table where col1 = 1 and col3 = 3的话，只有col1的索引部分能生效。但是其物理结构上这个联合索引是怎样存在的，我想不懂。

上网查阅了许多资料，总算有点眉目了。

假设这是一个多列索引(col1, col2,col3)，对于叶子节点，是这样的：

PS:该图改自《MySQL索引背后的数据结构及算法原理》一文的配图。

也就是说，联合索引(col1, col2,col3)也是一棵B+Tree，其非叶子节点存储的是第一个关键字的索引，而叶节点存储的则是三个关键字col1、col2、col3三个关键字的数据，且按照col1、col2、col3的顺序进行排序。

配图可能不太让人满意，因为col1都是不同的，也就是说在col1就已经能确定结果了。自己又画了一个图（有点丑），col1表示的是年龄，col2表示的是姓氏，col3表示的是名字。如下图：

PS:对应地址指的是数据记录的地址。

如图，联合索引(年龄, 姓氏,名字)，叶节点上data域存储的是三个关键字的数据。且是按照年龄、姓氏、名字的顺序排列的。

因此，如果执行的是：

select * from STUDENT where 姓氏='李' and 名字='安'

或者

select * from STUDENT where 名字='安'

那么当执行查询的时候，是无法使用这个联合索引的。因为联合索引中是先根据年龄进行排序的。如果年龄没有先确定，直接对姓氏和名字进行查询的话，就相当于乱序查询一样，因此索引无法生效。因此查询是全表查询。

如果执行的是：

select * from STUDENT where 年龄=1 and 姓氏='李'

那么当执行查询的时候，索引是能生效的，从图中很直观的看出，age=1的是第一个叶子节点的前6条记录，在age=1的前提下，姓氏=’李’的是前3条。因此最终查询出来的是这三条，从而能获取到对应记录的地址。

如果执行的是：

select * from STUDENT where 年龄=1 and 姓氏='黄' and 名字='安'

那么索引也是生效的。

而如果执行的是：

select * from STUDENT where 年龄=1 and 名字='安'

那么，索引年龄部分能生效，名字部分不能生效。也就是说索引部分生效。

因此我对联合索引结构的理解就是B+Tree是按照第一个关键字进行索引，然后在叶子节点上按照第一个关键字、第二个关键字、第三个关键字…进行排序。

而之所以会有最左原则，是因为联合索引的B+Tree是按照第一个关键字进行索引排列的。

联合索引在B+树上的结构介绍

前言

SQL模式影响MySQL支持的SQL语法和执行的数据验证检查。

MySQL服务器可以在不同的SQL模式下运行，并且可以针对不同的客户端以不同的方式应用这些模式，具体取决于sql_mode系统变量的值。DBA可以设置全局SQL模式以匹配站点服务器 *** 作要求，并且每个应用程序可以将其会话SQL模式设置为其自己的要求。

模式会影响MySQL支持的SQL语法以及它执行的数据验证检查。这使得在不同环境中使用MySQL以及将MySQL与其他数据库服务器一起使用变得更加容易。

下面话不多说了，来一起看看详细的介绍吧

设置SQL模式

要在运行时更改SQL模式，请sql_mode使用以下SET

语句设置全局或会话

系统变量

SET

GLOBAL

sql_mode

=

'modes'

SET

SESSION

sql_mode

=

'modes'

模式列表

模式

注释

ALLOW_INVALID_DATES

无效日期会生成错误

ERROR_FOR_DIVISION_BY_ZERO

除0错误

NO_BACKSLASH_ESCAPES

禁止使用反斜杠字符（\）作为字符串中的转义字符。启用此模式后，反斜杠就像其他任何一个普通字符一样。

NO_UNSIGNED_SUBTRACTION

在整数值之间减去（其中一个是类型）

UNSIGNED，默认情况下会产生无符号结果。如果结果否则为负，则会导致错误

NO_ZERO_IN_DATE

'0000-00-00'

则允许并且插入产生警告

ONLY_FULL_GROUP_BY

select

内指定字段必须出现在

groupby

中，否则错误

STRICT_TRANS_TABLES

为事务存储引擎启用严格的SQL模式，并在可能的情况下为非事务性存储引擎启用。

STRICT_ALL_TABLES

为所有存储引擎启用严格SQL模式。无效的数据值被拒绝。

详情请参考

https://dev.mysql.com/doc/ref...

严格SQL模式

MySQL服务器可以在不同的SQL模式下运行，并且可以针对不同的客户端以不同的方式应用这些模式，具体取决于sql_mode系统变量的值。在严格SQL模式下，服务器会将某些警告升级为错误。

严格SQL模式适用于以下语句

ALTER

TABLE

CREATE

TABLE

CREATE

TABLE

...

SELECT

DELETE

INSERT

LOAD

DATA

LOAD

XML

SELECT

SLEEP()

UPDATE

在存储的程序中，如果在严格模式生效时定义了程序，则列出的类型的单个语句将以严格的SQL模式执行。

严格的SQL模式适用于以下错误，表示输入值无效或缺失的一类错误。如果值具有错误的列数据类型或可能超出范围，则该值无效。如果要插入的新行不包含其定义中NOT

NULL没有显式DEFAULT子句的列的值，则缺少值。

ER_BAD_NULL_ERROR

ER_CUT_VALUE_GROUP_CONCAT

ER_DATA_TOO_LONG

ER_DATETIME_FUNCTION_OVERFLOW

ER_DIVISION_BY_ZERO

ER_INVALID_ARGUMENT_FOR_LOGARITHM

ER_NO_DEFAULT_FOR_FIELD

ER_NO_DEFAULT_FOR_VIEW_FIELD

ER_TOO_LONG_KEY

ER_TRUNCATED_WRONG_VALUE

ER_TRUNCATED_WRONG_VALUE_FOR_FIELD

ER_WARN_DATA_OUT_OF_RANGE

ER_WARN_NULL_TO_NOTNULL

ER_WARN_TOO_FEW_RECORDS

ER_WRONG_ARGUMENTS

ER_WRONG_VALUE_FOR_TYPE

WARN_DATA_TRUNCATED

致谢

感谢你看到这里，希望本篇文章可以帮到你，谢谢。

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对脚本之家的支持。

您可能感兴趣的文章:如何开启mysql中的严格模式学习SQL语句(强大的group

by与select

from模式)老生常谈MYSQL模式匹配

REGEXP和like的用法Mysql

SQL服务器模式介绍PHP基于单例模式实现的mysql类NoSQL反模式

-

文档数据库篇mysql中binlog_format模式与配置详细分析mysql启用skip-name-resolve模式时出现Warning的处理办法

你好，很高兴回答你的问题。

你的这个问题通过关联查询就可以解决。

select * from 文章表,评论表 where 文章表.文章id=评论表.文章id order by 文章表.文章id

如果有帮助到你，请点击采纳。

---