正文
mysql多列索引怎么查 mysql添加多列索引
小程序:扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序
【扫一扫了解最新限行尾号】
复制小程序
mysql 多列索引 怎么查询
假如你有一个表,
SQL CREATE TABLE test_tab (2 id INT,
3 name VARCHAR(10),
4 age INT,
5 val VARCHAR(10)6 );你的业务,有一个查询,是
SELECT * FROM test_tab WHERE name = 一个外部输入的数据
刚开始,数据不多的时候,执行效果还不错。
随着数据量的增加,这个查询,执行起来,越来越慢了。
然后在 name 上面 建立了索引
CREATE INDEX idx_test4_name ON test_tab (name );
这样, 可以加快前面那个查询的速度。
但是,某天,你执行了下面这个SQL, 发现速度又慢了
SELECT * FROM test_tab WHERE age = 25
为啥呢? 因为 age 字段上面,没有索引
索引只在 name 上面有
换句话说, 也就是 WHERE 里面的条件, 会自动判断,有没有 可用的索引,如果有, 该不该用。
多列索引,就是一个索引,包含了2个字段。
例如:CREATE INDEX idx_test_name_age ON test_tab (name, age);那么SELECT * FROM test_tabWHEREname LIKE '张%'
AND age = 25
这样的查询,将能够使用上面的索引。
多列索引,还有一个可用的情况就是, 某些情况下,可能查询,只访问索引就足够了, 不需要再访问表了。例如:SELECTAVG( avg ) AS 平均年龄FROMtest_tabWHEREname LIKE '张%'
这个时候, name 与 age 都包含在索引里面。 查询不需要去检索表中的数据。
MySql 索引(聚集索引,辅助索引,联合索引,覆盖索引..)
引入一个面试问题:
看完以下以后再回顾,会发现迎刃而解
Mysql 可以为每一张表设置 存储引擎 这里我们只说 InnoDB 存储引擎.
由于实际情况,数据页只能按照一棵 B+树 进行排序, 因此每张表只能拥有一个 聚集索引(即 主键)。
栗子:
每个叶子节点的索引行中包含了一个书签(bookmark). 该书签是用来告诉 InnoDB存储引擎哪里可以找到该索引对应的数据行或者说 行数据! 由于InnoDB存储引擎表, 是按照主键来构建的, 所以 ,该书签内其实包含或者说指向了 数据行所对应的聚集索引键
也就是说 辅助索引的 叶结点保存了 指向对应数据的 聚集索引, 可以通过该聚集索引 找到对应的数据行
辅助索引的存在并不影响数据在聚集索引中的组织,因为每张表上可以有多个辅助索引。
当通过辅助索引来寻找数据时,InnoDB 存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引(聚集索引)的主键,然后再通过聚集索引找到一个完整的数据行。
例如:
聚集索引辅助索引关系:
: 又叫做组合索引 , 辅助索引的一种 , 和普通创建索引的方式一样,不同的是 可以同时添加多列来作为索引项;
从本质上来说,联合索引也是一课B+树
个人理解: 所谓最左原则, 是因为 存储引擎构建组合索引时 是根据最左边的那一列索引项进行排序的 ,所以使用组合索引,必须满足 条件中必须存在 最左边那一列的索引项,这样 才可以找到对应的索引,继而 去寻找对应的数据
: 又叫做 索引覆盖,InnoDB中支持覆盖索引,即 从辅助索引中就可以得到查询的记录,而不需要查询聚集索引中的记录。
比如 这里没有根据最左原则使用组合索引,但是 优化器依然进行选择
共勉,欢迎指导谢谢~
深入理解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+树上的结构介绍
mysql怎么查看索引是不是多列索引
sql会先过滤出last_name符合条件mysql多列索引怎么查的记录,在其基础上在过滤first_name符合条件mysql多列索引怎么查的记录。
如果mysql多列索引怎么查我们分别在last_name和first_name上创建两个列索引,mysqlmysql多列索引怎么查的处理方式就不一样mysql多列索引怎么查了,它会选择一个最严格的索引来进行检索,可以理解为检索能力最强的那个索引来检索,另外一个利用不上了,这样效果就不如多列索引了。
关于mysql多列索引怎么查和mysql添加多列索引的介绍到此就结束了,不知道你从中找到你需要的信息了吗 ?如果你还想了解更多这方面的信息,记得收藏关注本站。