mysql联合索引和索引优化的理解

一时失言乱红尘 2022-04-16 04:16 278阅读 0赞

首先介绍联合索引的最左原则，即假设某张表test，有四个字段，id，a，b，c；id是主键，a和b是联合索引，建表和值如下：

CREATE TABLE `test` (
      `id` int(11) NOT NULL,
      `a` varchar(32) NOT NULL DEFAULT '',
      `b` varchar(32) NOT NULL DEFAULT '',
      `c` varchar(255) NOT NULL DEFAULT '',
      PRIMARY KEY (`id`),
      KEY `index1` (`a`,`b`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    
    BEGIN;
    INSERT INTO `test` VALUES (1, 'd', 'dd1', '');
    INSERT INTO `test` VALUES (2, '2', '2', '');
    INSERT INTO `test` VALUES (3, '1', 'dd1', '');
    INSERT INTO `test` VALUES (4, 'de', '3', '');
    INSERT INTO `test` VALUES (12, '12', '12', '');
    INSERT INTO `test` VALUES (13, '11', '1dd1', '');
    INSERT INTO `test` VALUES (14, '1d', '1dd1', '');
    INSERT INTO `test` VALUES (15, '1de', '13', '');
    INSERT INTO `test` VALUES (23, '1', 'dd1', '');
    INSERT INTO `test` VALUES (32, '12', '12', '');
    INSERT INTO `test` VALUES (33, '11', '1dd1', '');
    INSERT INTO `test` VALUES (34, '1d', '1dd1', '');
    INSERT INTO `test` VALUES (35, '1de', '13', '');
    COMMIT;

则在最左原则下，where语句在如下两种情况下会走联合索引：

where  a = 'a'

where a='a' and b='b'

而where b='b'一般情况下是不应该走索引的。为什么说是一般呢？下面这个sql走了索引：

explain  select b  from test where    b='2' ，这是为什么呢，下面开始探索

sql1  
explain  select b  from test where    b='2'   
sql2  
explain  select b,c  from test where    b='2'  
sql3  
explain  select b  from test where    b='2' order by  id  
sql4  
explain  select b,c  from test where    b='2' order by id  
sql5  
explain  select a,b  from test where  a='1' and b='2' order by  id  
sql6  
explain  select a  from test where  a='1'  order by  id  
sql7  
explain  select a  from test where  id=2   
sql8  
explain  select a  from test where  id=2 and a = '2' order by  id  
sql9  
explain  select id  from test where  a='1'  order by  id  
sql10  
explain select a from test

sql11

explain select c from test  order by a,b

索引使用规则如下：  
1不是仅仅通过where子句判断是否走索引，会从where、select和order by的字段中同时查找哪些字段可以使用索引  
sql1中使用了where条件b所在的联合索引，sql10和sql1使用了select字段a，b所在的联合索引，sql4使用了orderby字段id的主键索引

2针对联合索引(a,b)，用where b='b'查询的时候，一定不会走索引这句话是错误的，如果查询的所有字段在联合索引中，注意是所有字段（这种情况有一个单独的称谓：覆盖索引，参考[这里][Link 1]），此时不要求最左原则，即select a  
select a,b 或select b 均会走索引  
select b会走索引：sql1可以证明会走索引，sql2证明必须是select的所有字段均在联合索引中  
解释：  
这个是因为查询的全部字段在联合索引中，所以直接走联合索引并直接取值会比较快，但是如果同时包含了不在联合索引中的字段，则不会走联合索引，sql2证明

3一般（没有用||）不会同时使用多个不同的索引，即可能走多个索引(联合索引是一个索引)时，实际仅仅走一个索引  
这个在sql8中可以证明，当然你可能存在疑惑，是不是因为数据量较少的原因，所以没有走两个索引呢？  
笔者在数据量较大的表（20000左右）中做了实验，数据量大也是只会走一个索引，解释的话这个可以参考https://segmentfault.com/q/1010000003880137;但是存在下面情况会使用索引：

explain  select  \* from dau\_baseinfo where  add\_time='2018-09-21 17:18:16' || update\_time='2018-09-21 17:18:18'  
对add\_time和update\_time分别建了索引，并且数据量比较大的情况下。

4索引的优先顺序  
where条件字段>select中的字段  
sql7，sql9可以说明  
select中的字段适用于联合索引（即联合索引的最左原则）或别的单独索引>order by中的字段  
sql6，sql5可以说明  
order by中的字段>select中的所有字段不适用于联合索引，但查询的所有字端都在联合索引中  
sql3可以说明

5上面提到了order by的字段会走索引，但是不是所有的索引都会走呢？

sql4可以证明，但是你执行sql11会发现并没有走索引，这是因为id作为主键使用的是聚集索引，但是sql11中a,b使用的是一般的索引，那么就涉及到 聚集索引 和 一般索引的区别：

聚集索引：

**索引中键值的逻辑顺序决定了表中相应行的物理顺序（索引中的数据物理存放地址和索引的顺序是一致的）**，可以这么理解：只要是索引是连续的，那么数据在存储介质上的存储位置也是连续的。  
比方说：想要到字典上查找一个字，我们可以根据字典前面的拼音找到该字，注意拼音的排列时有顺序的。

> 打个比方：当我们想要找“啊”这个字，然后又想找“不”这个字，根据拼音来看“b”一定在”a“的后面。

聚集索引就像我们根据拼音的顺序查字典一样，可以大大的提高效率。在经常搜索一定范围的值时，通过索引找到第一条数据，根据物理地址连续存储的特点，然后检索相邻的数据，直到到达条件截至项。

非聚集索引

**索引的逻辑顺序与磁盘上的物理存储顺序不同**。非聚集索引的键值在逻辑上也是连续的，但是表中的数据在存储介质上的物理顺序是不一致的，即记录的逻辑顺序和实际存储的物理顺序没有任何联系。索引的记录节点有一个数据指针指向真正的数据存储位置。

> 非聚集索引就像根据偏旁部首查字典一样，字典前面的目录在逻辑上也是连续的，但是查两个偏旁在目录上挨着的字时，字典中的字却很不可能是挨着的。

总结一下：一条记录的存储物理位置是由聚集索引的值（即id）所决定，聚集索引的值（即id）可以直接定位记录的物理位置并且取到记录的值；但是非聚集索引与记录存储的物理位置不一致，只是会有一个指针指向数据存储的物理位置；所以用聚集索引（即id）定位记录的物理位置可以直接定位，但是非聚集索引需要经过指针移动才能定位数据的物理位置。

先这样吧，估计还有遗漏的点，欢迎补充。

[Link 1]: https://www.cnblogs.com/chengJAVA/p/6113691.html