MySQL索引

MySQL的Innodb存储引擎的索引分为聚集索引和非聚集索引两大类

创新互联专注于茂名网站建设服务及定制，我们拥有丰富的企业做网站经验。 热诚为您提供茂名营销型网站建设，茂名网站制作、茂名网页设计、茂名网站官网定制、小程序定制开发服务，打造茂名网络公司原创品牌,更为您提供茂名网站排名全网营销落地服务。

特点：B+树叶子节点存储行数据

一个表中，必须有一个聚集索引，只能有一个聚集索引，Innodb通常把一个表的主键索引作为聚集索引，如果没有主键InnoDB会选择一个唯一索引代替。如果没有这样的索引，InnoDB会隐式的定义一个主键来作为聚集索引，这个字段为6个字节，类型为长整形。

利用主键索引查找行数据是最快的，建议使用自增主键原因是利于索引树的构建（主键自增写入时新插入的数据不会影响到原有页，插入效率高；但是如果主键是无序的或者随机的，那每次的插入可能会导致原有页频繁的分裂，影响插入效率）

特点：B+树叶子节点存储主键ID

一个表中可以有多个非聚集索引，每个非聚集索引即是一棵B+树

通过非聚集索引查找数据时，需要先在非聚集索引上找到主键ID，再从聚集索引获取行数据，这个过程就称之为回表

B树索引中的B树实际上是B+树，至于为什么使用B+树而不使用B树或者红黑树的原因在另外的文章中有提及。

特点：

特点：类似JDK中的HashMap，但无法支持范围查询

特点：使用的算法仍然是B树索引，不同的就是索引列的值必须唯一

对于普通索引来说，查找到满足条件的第一个记录后，需要查找下一个记录，直到碰到第一个不满足条件的记录。

对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索，提升索引性能

另外插入行时会构建该唯一索引，假如索引值重复将插入失败，适合业务上做唯一性检验

通过建立倒排索引，可以极大的提升检索效率,解决判断字段是否包含的问题，但是业务上一般都不采用这种索引，而是使用ES处理全文搜索需求

仅对某个特定字段建立的索引，如(biz_id)

对多个字段建立的索引，如(biz_id,type)

我要对mysql中的数据建立倒排索引应该怎么

先确认自己的mysql服务进程mysqld在运行着，可以使用ps aux | grep mysql看看

Gemfile中加入gem 'mysql2'

确认mysql帐号密码正确，一般安装好的都是mysql默认都是用户名root，无密码，这样是可以直接登录的

你需要先使用mysql链接mysqld（第一步开启的服务端），之后手动创建blog_db数据库，rails是不会自动创建mysql的数据库的（里面的各个表你不需要创建，这是active_record的工作）。

看你error log应该是mysqld没运行！

MYSQL存储引擎InnoDB（二十三）：排序索引构建

InnoDB在创建或重建索引时执行批量加载，而不是一次插入一个索引记录。这种索引创建方法也称为排序索引构建。空间索引不支持排序索引构建。

索引构建分为三个阶段。在第一阶段， 扫描聚集索引，生成索引条目并添加到排序缓冲区。当排序缓冲区变满时，条目将被排序并写入临时中间文件。此过程也称为 “运行”。在第二阶段，将一个或多个运行写入临时中间文件，对文件中的所有条目执行合并排序。在第三个也是最后一个阶段，排序后的条目被插入到 B-tree中。

在引入排序索引构建之前，使用插入 API 将索引条目一次插入 B 树中的一条记录。此方法涉及打开 B 树 游标以查找插入位置，然后使用 乐观插入将条目插入 B 树页面。如果由于页面已满而导致插入失败， 则将执行悲观插入，这涉及打开 B-tree 游标并根据需要拆分和合并 B-tree 节点以找到条目空间。这种“自上而下”的弊端建立索引的方法是搜索插入位置的成本以及 B 树节点的不断拆分和合并。

排序索引构建使用“自下而上”建立索引的方法。使用这种方法，对最右侧叶页的引用保存在 B 树的所有级别。分配必要 B 树深度的最右侧叶页，并根据其排序顺序插入条目。一旦叶页已满，就会将节点指针附加到父页，并为下一次插入分配一个兄弟叶页。这个过程一直持续到所有条目都被插入，这可能导致插入到根级别。分配同级页时，释放对先前固定叶页的引用，新分配的叶页成为最右边的叶页和新的默认插入位置。

要为将来的索引增长留出空间，您可以使用innodb_fill_factor变量来保留一定百分比的 B 树页面空间。例如，设置 innodb_fill_factor为 80 会在排序索引构建期间保留 B 树页面中 20% 的空间。此设置适用于 B 树的叶子页面和非叶子页面。它不适用于用于 TEXT或 BLOB条目的外部页面。保留的空间量可能与配置不完全相同，因为innodb_fill_factor值被解释为提示而不是硬限制。

全文索引支持排序索引构建 。以前，SQL 用于将条目插入全文索引。

对于压缩表，以前的索引创建方法将条目附加到压缩页和未压缩页。当修改日志（表示压缩页面上的可用空间）变满时，将重新压缩压缩页面。如果由于空间不足而导致压缩失败，则页面将被拆分。使用排序索引构建，条目仅附加到未压缩的页面。当一个未压缩的页面变满时，它就会被压缩。自适应填充用于确保在大多数情况下压缩成功，但如果压缩失败，则会拆分页面并再次尝试压缩。这个过程一直持续到压缩成功。

在排序索引构建期间禁用重做日志记录。相反，有一个 检查点来确保索引构建可以承受意外退出或失败。检查点强制将所有脏页写入磁盘。在排序索引构建期间，页面清理线程会定期收到信号以刷新 脏页，以确保可以快速处理检查点操作。通常，当干净页面的数量低于设置的阈值时，页面清理线程会刷新脏页面。对于排序索引构建，脏页会被及时刷新以减少检查点开销并行化 I/O 和 CPU 活动。

排序索引构建可能会导致 优化器统计信息与以前的索引创建方法生成的统计信息不同。统计数据差异是由于用于填充索引的算法不同造成的。

Mysql建立索引经验

在实际开发中使用数据库时，难免会遇到一些大表数据，对这些数据进行查询时，有时候SQL会查询得特别慢，这时候，有经验的老师傅会告诉你，你看一下哪几个字段查的多，加一个索引就好了。

那么，怎么合理地建立索引呢？这里分享一下我的一些经验，如有不妥之处，欢迎批评指正。

1、不要盲目建立索引 ， 先分析再创建

索引虽然能大幅度提升我们的查询性能，但也要知道，在你进行增删改时，索引树也要同样地进行维护。所以，索引不是越多越好，而是按需建立。最好是在一整块模块开发完成后，分析一下，去针对大多数的查询，建立联合索引。

2、使用联合索引尽量覆盖多的条件

这是说在一个慢sql里假如有五个where ，一个 order by ，那么我们的联合索引尽量覆盖到这五个查询条件，如果有必要，order by 也覆盖上 。

3、小基数字段不需要索引

这个意思是，如果一张表里某个字段的值只有那么几个，那么你针对这个字段建立的索引其实没什么意义，比如说，一个性别字段就两种结果，你建了索引，排序也没什么意思（也就是索引里把男女给分开了）

所以说，索引尽量选择基数大的数据去建立，能最大化地利用索引

4、长字符串可以使用前缀索引

我们建立索引的字段尽量选择字段类型较小的，比如一个varchar(20)和varchar(256)的，我们在20的上面建立的索引和在256上就有明显的差距（字符串那么长排序也不好排呀，唉）。

当然，如果一定是要对varchar(256)建立索引,我们可以选择里面的前20个字符放在索引树里（这里的20不绝对，选择能尽量分辨数据的最小字符字段设计），类似这样KEY index(name(20),age,job) ，索引只会对name的前20个字符进行搜索，但前缀索引无法适用于order by 和 group by。

5、对排序字段设计索引的优先级低

如果一个SQL里我们出现了范围查找，后边又跟着一个排序字段，那么我们优先给范围查找的字段设置索引，而不是优先排序。

6、如果出现慢SQL，可以设计一个只针对该条SQL的联合索引。

不过慢SQL的优化，需要一步步去进行分析，可以先用explain查看SQL语句的分析结果，再针对结果去做相应的改进。explain的东西我们下次再讲。

PS：在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是 执行这条SQL。

Mysql建索引的注意事项

1.最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(、、between、like)就停止匹配，比如a = 1 and b = 2 and c 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

3.尽量选择区分度高的列作为索引，区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。

4.索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。

5.尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

1."一个顶三个"。建了一个(a,b,c)的复合索引，那么实际等于建了(a),(a,b),(a,b,c)三个索引，因为每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，这可是不小的开销！

2.覆盖索引。同样的有复合索引（a,b,c），如果有如下的sql: select a,b,c from table where a=1 and b = 1。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一

3.索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select * from table where a = 1 and b =2 and c = 3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W*10%=100w 条数据，然后再回表从100w条数据中找到符合b=2 and c= 3的数据，然后再排序，再分页；如果是复合索引，通过索引筛选出1000w *10% *10% *10%=1w，然后再排序、分页，哪个更高效，一眼便知

倒排索引

倒排索引(Inverted Index)：

倒排索引从逻辑结构和基本思路上讲非常简单，下面我们通过具体实例来进行说明，使得大家能够对倒排索引有一个宏观而直接的感受。

中文和英文等语言不同，单词之间没有明确的分隔符号，所以首先要用分词系统将文档自动切分成单词序列，这样每个文档就转换为由单词序列构成的数据流。

为了系统后续处理方便，需要对每个不同的单词赋予唯一的单词编号，同时记录下哪些文档包含这个单词，在处理结束后，我们可以得到最简单的倒排索引（参考图4）。

图4中，“单词ID”一列记录了每个单词对应的编号，第2列是对应的单词，第3列即每个单词对应的倒排列表。比如：单词“谷歌”，其中单词编号为1，倒排列表为{1,2,3,4,5}，说明文档集合中每个文档都包含了这个单词。

之所以说图4的倒排索引是最简单的，是因为这个索引系统只记载了哪些文档包含某个单词。而事实上，索引系统还可以记录除此之外的更多信息。

图5是一个相对复杂些的倒排索引，与图4所示的基本索引系统相比，在单词对应的倒排列表中不仅记录了文档编号，还记载了单词频率信息，即这个单词在某个文档中出现的次数。之所以要记录这个信息，是因为词频信息在搜索结果排序时，计算查询和文档相似度是一个很重要的计算因子，所以将其记录在倒排列表中，以方便后续排序时进行分值计算。

在图5所示的例子里，单词“创始人”的单词编号为7，对应的倒排列表内容有（3;1），其中3代表文档编号为3的文档包含这个单词，数字1代表词频信息，即这个单词在3号文档中只出现过1次，其他单词对应的倒排列表所代表的含义与此相同。

实用的倒排索引还可以记载更多的信息，图6所示的索引系统除了记录文档编号和单词词频信息外，额外记载了两类信息——即每个单词对应的文档频率信息（图6的第3列）及单词在某个文档出现位置的信息。

文档频率信息代表了在文档集合中有多少个文档包含某个单词，之所以要记录这个信息，其原因与单词频率信息一样，这个信息在搜索结果排序计算中是一个非常重要的因子。

而单词在某个文档中出现位置的信息并非索引系统一定要记录的，在实际的索引系统里可以包含，也可以选择不包含这个信息，之所以如此，是因为这个信息对于搜索系统来说并非必要，位置信息只有在支持短语查询的时候才能够派上用场。

以单词“拉斯”为例：其单词编号为8，文档频率为2，代表整个文档集合中有两个文档包含这个单词，对应的倒排列表为{(3;1;4),(5;1;4)}，其含义为在文档3和文档5出现过这个单词，单词频率都为1，单词“拉斯”在这两个文档中的出现位置都是4，即文档中第4个单词是“拉斯”。

图6所示的倒排索引已经是一个非常完备的索引系统，实际搜索引擎的索引结构基本如此，区别无非是采取哪些具体的数据结构来实现上述逻辑结构。

有了这个索引系统，搜索引擎可以很方便地响应用户的查询。比如：用户输入查询词 “Facebook”，搜索系统查找倒排索引，从中可用读出包含这个单词的文档，这些文档就是提供给用户的搜索结果。

而利用单词词频信息、文档频率信息即可对这些候选搜索结果进行排序，计算文档和查询的相似性，按照相似性得分由高到低排序输出，此即为搜索系统的部分内部流程。

单词词典是倒排索引中非常重要的组成部分，它用来维护文档集合中出现过的 所有单词 的相关信息，同时用来记载某个 单词对应的倒排列表在倒排文件中的位置信息 。在支持搜索时，根据用户的查询词，去单词词典里查询，就能够获得相应的倒排列表，并以此作为后续排序的基础。

对于一个规模很大的文档集合来说，可能包含几十万甚至上百万的不同单词，能否快速定位某个单词，这直接影响搜索时的响应速度，所以需要高效的数据结构来对单词词典进行构建和查找， 常用的数据结构包括 哈希加链表 结构和 树形 词典结构。

B树（或者B+树）是另外一种高效查找结构，图8是一个 B树结构示意图。B树与哈希方式查找不同，需要字典项能够按照大小排序（数字或者字符序），而哈希方式则无须数据满足此项要求。

B树形成了层级查找结构，中间节点用于指出一定顺序范围的词典项目存储在哪个子树中，起到根据词典项比较大小进行导航的作用，最底层的叶子节点存储单词的地址信息，根据这个地址就可以提取出单词字符串。

具体的大家可以看看[ ;fps=1 )

分享文章：mysql倒排索引怎么建,java倒排索引
本文地址：http://azwzsj.com/article/dsgigij.html