「数据库索引」MySQL数据库索引

数据库索引

索引是什么

索引有哪些结构

数据库有哪些索引

唯一索引

聚簇索引与非聚簇索引

全文索引

使用索引一定能提高查询性能吗？

哪些情况下设置了索引但是无法使用

哪些情况下需要设置索引、哪些情况下不需要

什么情况下应该使用组合索引而非单独索引

my sql中索引是如何组织数据的存储的

在MySQL 5.6中，对索引做了哪些优化？

索引是什么

索引是对数据库表中一个或多个列的值进行排序的结构，是帮助MySQL高效获取数据的数据结构

你也可以这样理解：索引就是加快检索表中数据的方法。数据库的索引类似于书籍的索引。在书籍中，索引允许用户不必翻阅完整本书就能迅速地找到所需要的信息。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。

Mysql数据库几个基本的索引类型：普通索引、唯一索引、主键索引、全文索引

1.索引加快数据库的检索速度

2.索引降低了插入、删除、修改等维护任务的速度

3.唯一索引可以确保每一行数据的唯一性

4.通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能

5.索引需要占物理和数据空间

索引有哪些结构

Hash索引和B+ Tree索引（5.7版本后InnoDB默认）

问：为什么采用B+ 树吗？这和Hash索引比较起来有什么优缺点吗？

答：因为Hash索引底层是哈希表，哈希表是一种以key-value存储数据的结构，所以多个数据在存储关系上是完全没有任何顺序关系的，所以，对于区间查询是无法直接通过索引查询的，就需要全表扫描。所以，哈希索引只适用于等值查询的场景。而B+ 树是一种多路平衡查询树，所以他的节点是天然有序的（左子节点小于父节点、父节点小于右子节点），所以对于范围查询的时候不需要做全表扫描

1、哈希索引适合等值查询，但是无法进行范围查询

2、哈希索引没办法利用索引完成排序

3、哈希索引不支持多列联合索引的最左匹配规则

4、如果有大量重复键值的情况下，哈希索引的效率会很低，因为存在哈希碰撞问题

数据库有哪些索引

在MySql数据库中，有四种索引：聚集索引（主键索引）（聚簇索引）、普通索引、唯一索引以及全文索引（FUNLLTEXT INDEX）

索引又可分为聚簇索引和非聚簇索引两种

唯一索引

一种索引，不允许具有索引值相同的行，从而禁止重复的索引或键值。系统在创建该索引时检查是否有重复的键值，并在每次使用 INSERT 或 UPDATE 语句添加数据时进行检查。

CREATE unique clusterED INDEX myclumn_cindex ON mytable(mycolumn)

聚簇索引与非聚簇索引

可以理解为主键索引与普通索引

聚簇索引：是对磁盘上实际数据重新组织以按指定的一个或多个列的值排序的算法。特点是存储数据的顺序和索引顺序一致，且一个表只能有一个聚簇索引，因为物理存储只能有一个顺序。主键索引一般都是聚簇索引

非聚簇索引：表数据存储顺序与索引顺序无关。对于非聚簇索引，叶结点包含索引字段值及指向数据页数据行的逻辑指针，其行数量与数据表行数据量一致。非聚簇索引记录的物理顺序与逻辑顺序没有必然的联系，与数据的存储物理结构没有关系；一个表对应的非聚簇索引可以有多条，根据不同列的约束可以建立不同要求的非聚簇索引；

一般情况下主键会默认创建聚簇索引，且一张表只允许存在一个聚簇索引。因为物理存储只能有一个顺序。

聚簇索引的叶子节点就是数据节点（Innodb的B+树的主键对应的数据节点），而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。

聚簇索引主键的插入速度要比非聚簇索引主键的插入速度慢很多。

相比之下，聚簇索引适合排序，非聚簇索引不适合用在排序的场合。因为聚簇索引本身已经是按照物理顺序放置的，排序很快。非聚簇索引则没有按序存放，需要额外消耗资源来排序。

建立聚簇索引的语句：

CREATE CLUSTER INDEX index_name ON table_name(column_name1,...);

问：主键索引查询只会查一次，而非主键索引一定需要回表查询多次吗？

答：通过覆盖索引也可以只查询一次

覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。

当一条查询语句符合覆盖索引条件时，MySQL只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后再返回表操作，减少I/O提高效率。

如，表covering_index_sample中有一个普通索引 idx_key1_key2(key1,key2)。

当我们通过SQL语句：select key2 from covering_index_sample where key1 = 'keytest';的时候，就可以通过覆盖索引查询，无需回表。

全文索引

全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用【分词技术】等多种算法智能分析出文本文字中关键词的频率和重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。

select * from 表名 where 标题 like '%xxx%' or 内容 like '%xxx%' or 作者 like '%xxx%';

这种搜索效率无比底下

全文索引是为了使得“关键词搜索”功能更加的高效能。

我们有这么一张数据表：

文章id 文章标题文章内容

1 超级塞亚人我是超级塞亚人我喜欢吃苹果，我不是天朝的人，也不是地球人

2 天朝大国我大天朝威武，我大天朝13亿人，我大天朝

3 我喜欢游泳游泳有很多好方法

4 动画片我儿子喜欢看动画片，尤其是七龙珠，因为里面有塞亚人，而且塞亚人喜欢吃苹果,他们不是地球人

5 运动我喜欢运动，喜欢跑步，喜欢游泳，喜欢健身，喜欢xxoo

6 打炮我是一个二战的老兵，这是我的回忆录，我最幸福的时光就是在天朝吃着苹果打炮

7 。。。

8 。。。

9 。。。

然后，根据以上的文章内容，如果建立了一个索引文件（这里忽略索引文件的数据结构，仅仅以一种易于理解的方式呈现）：

关键词文章id

塞亚人 1,4

苹果 1,4,6

天朝 1,2,6

地球 1,4

游泳 3,5

七龙珠 4

喜欢 1,4,5,6

那么当我想搜索 “塞亚人”的时候，这个索引文件直接告诉我在文章id为1和4的文章里有这个词。

这个索引文件就是“全文索引”。

如何使用全文索引和分词的方式来帮助优化你的搜索呢？

需要工作的程序：索引程序，分词程序，数据库。

工作原理：

1、索引程序从数据库读取数据，比如上面例子中的数据表，索引程序通过sql语句：select 文章id,文章标题，文章内容 from 文章表.获得文章的相关数据

2、索引程序对需要索引的内容进行“分词”，而这里的分词就是调用分词程序啦！

3、索引程序对分好词的一个个词条加入索引文件。

在你写的代码里，原来到数据库－－－－like %xxx%－－－－－的语句就变成了到索引文件里去查找，从而找到相应的数据（这点相信你已经理解啦！）

创建全文索引的两种方法：

1.在建表语句中

2.在已知表中

ALTER TABLE article ADD FULLTEXT INDEX fulltext_article(title,content);

具体如何使用全文索引呢？

不用全文索引时的写法：SELECT * FROM article WHERE content LIKE ‘%查询字符串%’;

使用全文索引：SELECT * FROM article WHERE MATCH(title,content) AGaiNST (‘查询字符串’);

注意：

1、MySql自带的全文索引只能对英文进行全文检索，目前无法对中文进行全文检索。如果需要对包含中文在内的文本数据进行全文检索，我们需要采用Sphinx（斯芬克斯）/Coreseek技术来处理中文。

2、使用MySql自带的全文索引时，如果查询字符串的长度过短将无法得到期望的搜索结果。MySql全文索引所能找到的词默认最小长度为4个字符。另外，如果查询的字符串包含停止词，那么该停止词将会被忽略。

3、如果可能，请尽量先创建表并插入所有数据后再创建全文索引，而不要在创建表时就直接创建全文索引，因为前者比后者的全文索引效率要高。

使用索引一定能提高查询性能吗？

通常,通过索引查询数据比全表扫描要快，但是我们也必须注意到它的代价.

索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时,索引本身也会被修改。这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5次的磁盘I/O.

索引不但会使得插入和修改的效率降低，而且在查询的时候，有一个查询优化器，太多的索引会让优化器困惑，可能没有办法找到正确的查询路径，从而选择了慢的索引。

索引范围查询(INDEX RANGE SCAN)适用于两种情况:

1.基于一个范围的检索,一般查询返回结果集小于表中记录数的30%

2.基于非唯一性索引的检索

3.直接晋升为覆盖索引，避免多次查表

哪些情况下设置了索引但是无法使用

根本原因是查询优化器决定不使用索引：

一条SQL语句的查询，可以有不同的执行方案，至于最终选择哪种方案，需要通过优化器进行选择，选择执行成本最低的方案。在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案。这个成本最低的方案就是所谓的执行计划。优化过程大致如下：

1、根据搜索条件，找出所有可能使用的索引

2、计算全表扫描的代价

3、计算使用不同索引执行查询的代价

4、对比各种执行方案的代价，找出成本最低的那一个

有时候查询语句没有按照索引的要求来也会导致无法使用索引，如下：

对单字段建了索引，where条件多字段。
建立组合索引，where条件单字段。与上面情况正好相反。INDEX(a,b,c)，当条件为a或a,b或a,b,c或a,c时都可以使用索引，但是当条件为b,c时将不会使用索引。也就是说不是使用的第一部分，则不会使用索引。如果是INDEX(a,b)，即使查询的where是b,a，由于sql优化器的优化作用，会把b,a换成a,b，这样就可以走索引了。如果是index(a,b,c),查询是(a,b,c,d)不会走索引
条件中用or，即使其中有条件带索引，也不会使用索引查询（这就是查询尽量不要用or的原因，用in）（注意：使用or，又想索引生效，只能将or条件中的每个列都加上索引，这样查询时每个列都会单独使用它们自己的索引）
like的模糊查询的模糊词在字符串前面，比如以%或_开头，索引失效。
在使用不等于(is null、is not null、!= 、<>)的时候无法使用索引会导致全表扫描。
类型错误，如字段类型为varchar，where条件用number。
对索引应用内部函数，这种情况下应该建立基于函数的索引。
索引列不能是表达式(id+1=5)的一部分，也不能是函数的参数
如果MySQL预计使用全表扫描要比使用索引快，则不使用索引

哪些情况下需要设置索引、哪些情况下不需要

需要：

1).主键自动建立唯一索引

2).频繁作为查询条件的字段应该创建索引

3).查询中与其它表关联的字段，外键关系建立索引

4).单键/组合索引的选择问题(在高并发下倾向创建组合索引)

5).查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

6).查询中统计或者分组字段

不需要：

1).表记录太少

2).经常增删改的表(因为不仅要保存数据，还要保存一下索引文件) 索引本来是一种事先在写的阶段形成一定的数据结构，从而使得在读的阶段效率较高的方式，但是如果一个字段是写多读少，则会降低写的速度。

3).数据重复且分布平均的表字段（比如性别），因此应该只为最经常查询和最经常排序的数据列建立索引。

4).where条件里用不到的字段不创建索引

什么情况下应该使用组合索引而非单独索引

假设有条件语句A=a AND B=b，如果A和B是两个单独的索引，在AND条件下只有一个索引起作用，对于B则要逐个判断，而如果使用组合索引(A, B)，只要遍历一棵树就可以了，大大增加了效率。但是对于A=a OR B=b，由于是或的关系，因而组合索引是不起作用的，因而可以使用单独索引，这个时候，两个索引可以同时起作用。

MySQL中索引是如何组织数据的存储的

假如有如下数据表：

对于表中每一行数据，索引中包含了last_name、first_name、dob列的值，下图展示了索引是如何组织数据存储的。

可以看到，索引首先根据第一个字段来排列顺序，当名字相同时，则根据第三个字段，即出生日期来排序，正是因为这个原因，才有了索引的“最左原则”。

在MySQL 5.6中，对索引做了哪些优化？

一个比较重要的点：Index Condition Pushdown（索引下推）

MySQL 5.6引入了索引下推优化，默认开启，使用SET optimizer_switch = 'index_condition_pushdown=off';可以将其关闭。官方文档中给的例子和解释如下：

people表中（zipcode，lastname，firstname）构成一个索引

SELECT * FROM people WHERE zipcode='95054' AND lastname LIKE '%etrunia%' AND address LIKE '%Main Street%';

如果没有使用索引下推技术，则MySQL会通过zipcode='95054'从存储引擎中查询对应的数据，返回到MySQL服务端，然后MySQL服务端基于lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断数据是否符合条件。

如果使用了索引下推技术，则MYSQL首先会返回符合zipcode='95054'的索引，然后根据lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断索引是否符合条件。如果符合条件，则根据该索引来定位对应的数据，如果不符合，则直接reject掉。有了索引下推优化，可以在有like条件查询的情况下，减少回表次数。

MySQL数据库索引