目录为什么要用索引索引是什么Mysql中的pageInnoDB引擎索引如何提高查找效率索引如何生效排序支持索引聚簇索引和非聚簇索引创建多个索引复合索引全文索引的简单介绍索引覆盖索引的缺点索引创建为什么要用索引一般来说我们使用一个普通字段的值去查找一列或者几列数据是要对表中的所有数据进行线性遍历的在数据很多的情况下这样效率非常低严重影响用户体验索引用以提升查找速度。索引是什么而要提升查询速度无非从两个方面入手数据结构/算法算法其实一定程度上受限于数据结构而索引主要是从数据结构入手来提高查询效率的即创建索引就是创建一个数据结构帮助提高查找效率。Mysql中的pageInnoDB引擎Mysql作为一个组织数据服务查询的应用IO需求要比一般程序更大因此OS以4KB为单位和磁盘进行交互对于Mysql来说还是有点太少为了进一步减少IO次数Mysql与磁盘进行交互的基本单位是16KB。Mysql服务实际上会在启动的时候申请一个默认为128M大小的缓冲区——buffer pool用以存放当前读入内存的数据。Mysql会以16KB为单位把磁盘数据读取到buffer pool也以16KB为单位把buffer pool中的数据写到内核缓冲区并在适当的时候刷新内核缓冲区。通过这种方式可以让Mysql实现以16KB为单位与磁盘进行交互。在buffer pool中哪些16KB块需要赶快刷新哪些16KB块被修改过哪些16KB块是已经存在这些问题告诉我们不单单要把数据加载到buffer pool还要对其进行管理而管理的最好方式就是先描述在组织。因此每个16KB的块都会对应一个管理它的结构体我们在数据库中也是以16KB的page为单位存放数据的索引如何提高查找效率我们已经知道Mysql 数据库InnoDB引擎中使用page与磁盘进行交互所以实际上可以推断出在其存储文件中数据也是按照Page存储的。不过这不是讨论的重点只有数据在内存中时才可以对其进行查询操作因此我们只需要关注buffer pool中的Page即可下面展示整个Mysql 数据库InnoDB引擎的Page结构我们可以从上图中推断出如下几点page分为两类一类存储数据和目录叶子结点一类只存储目录非叶子结点但是所有种类的page都有前后指针负责把同一层的节点相互连接。所有的page逻辑上组成一种树形结构这种结构叫做B树。在单个叶子结点内部由于有目录的存在可以一次性排除更多元素快速查找到目标元素。在多个叶子节点之间由于有第二层非叶子节点的存在我们可以较快速的查找到目标元素在哪个叶子结点里面找到后即可在叶子结点内部进行查询。如果数据量过多的话第二层非叶子结点也会过多那么其本身的查找也变成了效率比较低的线性遍历因此可以给第二层非叶子节点再次创建目录即第一层非叶子节点帮助快速查询。图中第二层非叶子结点中的每一个目录项都代表多个叶子结点的集合这样目录才能发挥作用但是上图中没有显示出这一点事实上本质就是使用目录的思想一次性排除更多的元素在这个过程中选择了B树这种数据结构。选择B树而不是B树作为数据结构有什么好处首先B树的非叶子节点不存放数据这样一来非叶子节点可以存放更多目录项那么整棵树就是“矮胖”类型显而易见一颗查找树又矮又胖的话查找次数会更少其次B树的叶子节点相连那么就可以很好的支持范围查询。比如说我们的查询语句是这样的select * from where id100那么如果结果集存放在多个叶子结点中就可以通过前后指针去到其他page找结果而不用再次从上至下查询一次使用树形结构可以只加载目标元素相关的节点进内存减少了IO次数当然这一点B树也做得到为什么不用其他数据结构链表是线性遍历查找速度很慢二叉搜索树可能退化成线性结构导致查找速度变慢AVL树和红黑树虽然可以维持搜索树的平衡但是这两种树毕竟只是二叉树而不是像B一样的多叉树所以会比较高意味着的IO次数会比较多而IO耗时是最多的。hash表存放单个数据这样肯定不行因为不支持范围查询而且不支持排序。可以想到用hash存放pagekey为page中最后一个数据的值这样来说可以支持范围查询排序但是增删的时候需要把一个page分裂成两个重新映射page的位置比较麻烦而且这样一来page一但过多线性遍历就会比较慢。索引如何生效显而易见只有使用创建索引时候使用的属性查找数据才会加快查找速度。因此可以给经查用于查找的属性列都建立索引排序支持索引我们可以发现当我们在创建表的时候指定一个主键的时候之后无论如何插入数据查询到的数据都是按照主键进行排序的这涉及到两点Mysql默认会使用主键建立一个索引如果你没有设置主键那么Mysql会使用隐藏的列充当主键建立索引总之建表之后一定会有一个索引。page内部以及page之间的数据项有序是使用目录加快查询速度的必要条件因此数据必须根据索引进行排序。聚簇索引和非聚簇索引索引是利用合适的数据结构加快查询的过程而聚簇索引和非聚簇索引的区别就是——数据本身是否存储在数据结构中。上面所说的InnoDB引擎索引就是一种聚簇索引而MyISAM引擎则采用的是飞聚簇索引即叶子结点存放指向数据的指针而非数据本身。创建多个索引如果表中有多个索引InnoDB引擎那么就会创建多个不同的索引数据结构也就是B树。但是他们不会都存放数据本身这样太浪费空间。一般来说只有主键索引或者隐藏列索引会存放数据普通索引存放的数据内容就是主键或者隐藏列在使用普通索引查找一个元素的时候先查找普通索引结构确定该普通索引的值对应的主键是谁然后再通过该值查找主键索引结构获取数据这个过程叫做回表查询当然如果是非聚簇索引就没那么多讲究因为他根本不存放数据多个索引结构可以很自然的共享数据注意只要存在主键主键索引就一定会被创建哪怕你是创建表成功后再添加主键mysql也会让主键索引替换隐藏列索引如果本来是非隐藏列索引会保留旧的索引而不是替换。复合索引复合索引就是利用多个属性字段构建同一个索引即同一颗B树而它只是排序规则和非复合索引不同以indexa,b,c为例先按照a排序相同的话再按照b排序。说白了复合索引就是使用新的排序规则构建B树复合索引的最左匹配原则使用复合索引查数据必须从左往右指明索引字段不能跳、不能插队否则复合索引就会失效。全文索引的简单介绍全文索引就是 MySQL 专门给「长文本关键词搜索」做的专用索引用来解决LIKE %关键词%巨慢的问题毕竟B树只支持左前缀模糊匹配----核心转化模糊查询 → 范围查询对于LIKE abc%等价于查询Key abc AND Key abd当对大量文字的字段进行检索时会使用到全文索引。MySQL提供全文索引机制但要求表的存储引擎必须是MyISAM而且默认的全文索引支持英文不支持中文。如果对中文进行全文检索可以使用sphinx的中文版(coreseek)。全文索引的数据结构不是B树。创建全文索引CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT, FULLTEXT (title,body) --把 title标题和 body正文两列合并成一个「大文本」一起做分词、一起建倒排索引。搜索时只要标题 或 正文里包含关键词就算匹配。 )engineMyISAM;使用全文索引SELECT * FROM articles WHERE MATCH (title,body) AGAINST (database);索引覆盖我们上面讲解过如果存在多个索引的话使用普通索引查询数据项要进行回表查询比较麻烦。而索引覆盖就是让普通索引page中的数据不止存放主键还存放该数据相关的一些其他常用字段值这样一来如果用户查询的字段都在这几个字段之内就不用回表查询了提高了检索速度。索引的缺点索引虽然提高了查找速度但也让我们除了维护数据还必须维护索引数据结构在插入或者删除以及修改某数据的时候都可能导致B树的结构发生变化而且有多少个索引就要更新多少次数据结构做多少次IO。这样来看索引让增删改的速度降低了索引创建-- 在创建表的时候直接在字段名后指定 primary key create table user1(id int primary key, name varchar(30)); -- 在创建表的最后指定某列或某几列为主键索引 create table user2(id int, name varchar(30), primary key(id)); -- 创建表以后再添加主键 create table user3(id int, name varchar(30)); alter table user3 add primary key(id); -- 在表定义时在某列后直接指定unique唯一属性。 create table user4(id int primary key, name varchar(30) unique); -- 创建表时在表的后面指定某列或某几列为unique create table user5(id int primary key, name varchar(30), unique(name)); -- 创建表后添加唯一键 create table user6(id int primary key, name varchar(30) alter table user6 add unique(name); --在表的定义最后指定某列为索引 create table user8(id int primary key, name varchar(20), email varchar(30), index(name) ); --创建完表以后指定某列为普通索引 create table user9(id int primary key, name varchar(20), email varchar(30)); alter table user9 add index(name); -- 创建一个索引名为 idx_name 的索引 create table user10(id int primary key, name varchar(20), email varchar(30)); create index idx_name on user10(name);附录——面试宝典介绍一下Mysql中的索引是什么索引就是将数据库中所有的数据按照某一列或者几列排序后构建出的的一种数据结构一般是B树。有什么用它的作用就是利用类似于书籍中目录的原理来提高数据库检索速度本质就是提高每一次判断能排除数据量。底层原理底层就是一颗B树。每个叶子节点存放的是一堆数据以及查找这些数据所用的目录每若干条数据对应一个目录项通过首先检索目录项确定模糊范围然后进行具体查找来提高节点内部查找数据的速度。当然如果是非主键索引有可能叶子结点不是存放数据而是存放主键非叶子节点存放的只有目录在叶子结点过多的时候遍历叶子结点本身就是低效的因此可以让每若干个叶子结点对应一个目录项通过首先检索目录项确定数据在哪几个叶子结点之间然后再确定具体的叶子结点继而确定叶子结点内具体的位置来提高检索速度。如果倒数第二层非叶子节点也过多那么就可以继续给这一层增加一层目录。所有的叶子结点是通过双向链表链接起来的这样可以很好的支持范围查询。为什么不选择其他数据结构相对于B树来说B树的非叶子节点不存放数据这样一来非叶子节点可以存放更多目录项那么就不需要太多层整棵树是比较矮胖的。显而易见一颗查找树又矮又胖的话查找次数会更少。其次B树的叶子节点相连那么就可以很好的支持范围查询。比如说我们的查询语句是这样的select * from where id100那么如果结果集存放在多个叶子结点中就可以通过前后指针去到其他page找结果而不用再次从上至下查询一次。使用树形结构可以只加载目标元素相关的节点进内存减少了IO次数当然这一点B树也做得到链表是线性遍历查找速度很慢二叉搜索树可能退化成线性结构导致查找速度变慢AVL树和红黑树虽然可以维持搜索树的平衡但是这两种树毕竟只是二叉树而不是像B一样的多叉树所以会比较瘦高意味着一次查找的时间多IO次数会比较多,并且范围查询也支持不好hash表存放单个数据这样肯定不行因为不支持范围查询而且不支持排序。可以想到用hash存放pagekey为page中最后一个数据的值这样来说可以支持范围查询排序但是增删的时候需要把一个page分裂成两个重新映射page的位置比较麻烦而且这样一来page一但过多线性遍历就会比较慢。索引的缺点有了新的数据结构必然要在增删改中维护这个数据结构那么拖慢每一次增删改的速度这是它做出的牺牲不过无伤大雅因为数据库中大部分都是查找而不是修改。什么是索引覆盖如果存在多个索引的话使用普通索引查询数据项要进行回表查询比较麻烦。而索引覆盖就是让普通索引page中的数据不止存放主键还存放该数据相关的一些其他常用字段值这样一来如果用户查询的字段都在这几个字段之内就不用回表查询了提高了检索速度。