数据库索引灵魂拷问（2）

本文主要是介绍数据库索引灵魂拷问（2），对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

继上一篇分析的索引问题后，我们知道了索引的作用和结构，这一篇文章将继续围绕索引的一些类型知识点进行分析

索引分为聚簇索引和非聚簇索引，那么这两者之间是有什么区别吗

上篇的B+树结构我们知道，叶子节点存放着一行的数据，这个是区分两者的重要特征，如图所示，左边的图是InnoDB的主键索引和二级索引，右边为MyISAM的索引，我们可知，对于MyISAM而言，叶结点包含索引字段值及指向数据页数据行的逻辑指针，也就是说表的数据和索引是分隔开的，主键索引和二级索引并无差 别，查找数据的时候需要根据索引存放的数据行指针进一步查找。对于InnoDB而言，索引有主键索引和普通索引的分别，聚簇索引根据主键来构建，叶子节点存在这一行的数据，普通索引存放这主键的以及构成索引的字段，也就是说在select查找的时候，如果是用的主键索引，则无需回表查询，可以直接返回所查的数据，如果使用的是普通索引查询，如果查询的字段恰好为字段本身，也无需回表查询，如果查询的字段不在构成索引字段本身内，则需要根据主键回表查询其他的字段。由此可见，对于mysql来说 （select *）本身也是会影响查询效率，最好是只查需要的字段，覆盖索引，避免回表

下面分析一下常见的索引类型

• 主键和唯一索引

对于一个表来说，唯一索引可以有多个，但是只有一个主键。主键就是唯一索引，但是唯一索引不一定是主键，唯一索引可以为空，但是空值只能有一个，主键不能为空,对于多列组成的唯一索引，需要保证具有唯一性

• 联合索引

联合索引在日常数据库使用的时候也是经常被使用到的，我们以（a,b,c） 为例子构建一个联合索引，实际上构建了(a),(a,b),(a,b,c)三个索引，联合索引 有“最左前缀”原则，遇到范围查询(>、<、between、like)就会停止匹配。接下来就分析一下常见的几个问题。

CREATE TABLE `test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `a` varchar(15) NOT NULL,
  `b` varchar(15) NOT NULL,
  `c` varchar(15) NOT NULL,
  `d` varchar(15) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_a_b_c` (`a`,`b`,`c`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

1. 如果颠倒a、b、c顺序是否会使用到索引

EXPLAIN SELECT * FROM test WHERE c='test' AND a='test' AND b='test'

可见MySQL会自动对查询的列进行调整，不影响索引的使用

2. 如果放弃最左原则，也就是不查a，直接查询b、c

EXPLAIN SELECT * FROM test WHERE c='test' AND b='test'

说明不使用第一个查询列的时候是无法触发索引的

根据上面的结果我们现在反推一些查询语句的时候应该如何建立索引

SELECT * FROM test WHERE a > 1 and b = 2

　　如果上面这个查询语句建立的是（a，b）的时候是只能使用到a索引，如果是建立（b，a）的索引，MySQL的优化器会帮我们调整，从而使用到索引

EXPLAIN SELECT * FROM test WHERE a IN (1,7,9) and b > 1

　　还是对(a，b)建立索引，因为IN在这里可以视为等值引用，不会中止索引匹配，所以还是(a,b)

SELECT * FROM test WHERE a > 1 and b = 2 and c > 3;

　　对于上面的语句而言,建立（b,c）还是（b,a）都差不多，因为无法全部都涉及到，所以需要看具体使用情况

随便分析一个索引和排序的问题吧

EXPLAIN SELECT * FROM test WHERE  a =1 ORDER BY b;
EXPLAIN SELECT * FROM test WHERE  a >1 ORDER BY b;

　　上面建立了(a,b)的索引，当a = 1的时候，b相对有序，可以避免再次排序，而第二个语句是一个范围查询，这个范围内b值是无序的，没有必要对(a,b)建立索引

共同进步，学习分享

欢迎大家关注我的公众号【写代码的小杨】，相关文章、学习资料都会在里面更新，整理的资料也会放在里面。

觉得写的还不错的就点个赞，加个关注呗！点关注，不迷路，持续更新！！！

这篇关于数据库索引灵魂拷问（2）的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！