本文共 4275 字，大约阅读时间需要 14 分钟。

Mysql索引类型（物理存储角度）

InnoDB（聚集索引）	MyISAM（非聚集索引）
数据文件本身就是索引文件（主键索引的索引保存剩余列所有数据值）(辅助索引的索引保存主键数据值）	索引文件和数据文件是分离的；索引文件仅保存数据记录的地址
以主键聚集，要求必须有“主键”	可以没有主键
支持行级锁	只支持表级锁
支持事务	不支持事务

• Q：建立索引的目的？
• A：通过建立维护一定的数据结构，减少查询时间。 各种查询算法与其对应的数据结构：
  最基本的查询算法当然是顺序查找O(N)； 再来就是二分查找O(logn) ，有限制性是数据必须是有序的； 二叉排序树查找O(log2N)；
  哈希表查找，几乎是O(1)，基于哈希表，取决于冲突的多少；
  B+树O(h)=O(logdN)，相对于二叉树，树的深度降低。被优化的B+Tree还在叶子节点增加了顺序指针，如图。

• Q：索引为什么选择B+Tree数据结构？
• A：一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。
  索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级。 所以一般使用磁盘I/O次数评价索引结构的优劣。
  根据B+Tree的定义，可知检索一次最多需要访问h-1个节点（根节点常驻内存）。
  数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。
  每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，实现了一个node只需一次I/O。
  因为B树的搜索复杂度为O(h)=O(logdN)，所以树的出度d越大，深度h就越小，I/O的次数就越少。
  B+Tree恰恰可以增加出度d的宽度，因为每个节点大小为一个页大小，所以出度的上限取决于节点内key和data的大小：

`d出度max=floor(pagesize/(key大小+data大小+point指针大小))  //floor表示向下取整

由于B+Tree的非叶子节点去掉了data域，因此可以拥有更大的出度，从而拥有更好的性能。

所以，B+Tree中一次检索最多需要h-1次I/O（根节点常驻内存）。 一般实际应用中，出度d是非常大的数字，通常超过100，因此h非常小（通常不超过3）。 综上所述，如果我们采用B-Tree存储结构，搜索时I/O次数一般不会超过3次，所以用B-Tree作为索引结构效率是非常高的。

• Q：查找过程？上图如何查找数据项29？
• A：首先会把磁盘块1由磁盘加载到内存，此时发生一次IO；
  在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计；
  把磁盘块3由磁盘加载到内存，发生第二次IO； 29在26和30之间，锁定磁盘块3的P2指针； 通过指针加载磁盘块8到内存，发生第三次IO；
  同时内存中做二分查找找到29，结束查询，总计三次IO。

Mysql索引类型（数据结构角度）

B-Tree索引——B-Tree结构（可以用于范围查询）
Hash索引——Hash表结构（查找速度较快，仅支持<=>以及in操作）
全文索引——倒排索引结构（主要用于代替 like “%…%” 效率低下的问题）

Mysql索引类型（逻辑角度）

例，有一张用户表

用户编号 INT；用户姓名 VARCHAR(10)；用户身份证号码 VARCHAR(18) 用户电话 VARCHAR(10)；用户住址 VARCHAR(50)；用户备注信息 TEXT

INDEX(普通索引)最基本的索引，没有任何限制	姓名
UNIQUE(唯一索引)与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值	身份证号码（唯一的，不重复）
PRIMARY KEY（主键索引）是一种特殊的唯一索引，不允许有空值	用户编号（虽然编号也是一种唯一索引，但是Mysql的主键必须指定为PRIMARY KEY)
FULLTEXT(全文索引)用于在一篇文章中，检索文本信息；针对较大的数据，生成全文索引很耗时空间。	备注信息（但fulltext更用于搜索长篇文章）
组合索引,为了更多的提高mysql效率可建立组合索引，遵循“最左前缀”原则

• Q：数据库什么情况下用到索引呢？
• A： 一般来说，在WHERE和JOIN中出现的列需要建立索引。 MySQL只对
  <，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE(不能以%开头)才会使用索引。
• Q：不适合建立索引的字段？
• A：唯一性太差的字段不适合创建索引（如：性别）
  更新太频繁的字段（如：游戏在线状态，在线，离线，忙碌，游戏中等），因为数据的update操作将导致索引二叉树的更新

单列索引与组合索引

例如：

CREATE TABLE myIndex (id INT NOT NULL AUTO_INCREMENT,Name VARCHAR(50) NOT NULL,City VARCHAR(50) NOT NULL,Age INT NOT NULL,SchoolID INT NOT NULL, PRIMARY KEY (id));

数据库中满足name='gangtie’的数据有几十条，同时满足city='shanghai’和age='25’的只有一条

如果使用单列索引，运行以下sql：

SELECT id FROM myIndex WHERE Name='gangtie' AND City='shanghai' AND Age=25;

mysql首先查出满足name='gangtie’的结果，放到中间结果集；再在中间结果集中再次查询city条件。

建立组合索引：

ALTER TABLE myIndex ADD INDEX name_city_age (Name,City,Age);

建立这样的组合索引，其实是相当于分别建立了

Name，City，Age Name，City Name 这就是“最左前缀”的原则。 但是以下的sql仍然是可以使用到组合索引的，因为mysql内部的sql优化机制，会重新排列过滤条件。

SELECT id FROM myIndex WHERE Name='gangtie' AND Age=25 AND City='shanghai'；

而这个sql语句就不能使用到组合索引了。

SELECT id FROM myIndex WHERE Age=25 AND City='shanghai'；

普通索引与全文索引

普通索引：适合对数值比较，对范围过滤

全文索引：全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。

1. 与一般的索引立即更新不同，全文索引一般是定期维护索引的，所以不适合频繁更新的数据。
2. 对于较大的数据集，把数据添加到一个没有FULLTEXT索引的表，然后添加FULLTEXT索引。而不是把数据添加到一个已经有FULLTEXT索引的表。
3. 全文索引更类似于搜索引擎做的事情，而不是简单的WHERE条件匹配。
4. 在相同的列上同时创建全文索引和基于值对B-Tree索引不会有冲突，全文索引适用于MATCH AGAINST操作，而不是普通的WHERE条件操作。

CREATE TABLE articles (id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,title VARCHAR(200),body TEXT,INDEX (title)FULLTEXT (body))

使用普通索引：

select * from articles where body like ‘mysql%’;

使用全文索引：

select * from articles where match(title,body) against(‘database’);

原理：——建立倒排索引（反向索引）

例如有如下表： InnoDB中的倒排索引结构： full inverted index：{单词，（单词文档所在ID，具体文档中的位置）} Mysql中有两种全文索引：

1. 自然语言的全文索引

   默认情况下，或者使用 in natural language mode 修饰符。
   计算每一个文档对象和查询的相关度，根据相关度排序返回顺序。
   1.find key在文档中是否存在
   2.find key在文档中出现的次数
   3.find key在索引列的数量
   4.多少个文档包含该find key
   Q：如果我不去管相关度大小，只要有匹配的就返回结果，如何做呢？
   A：mysql到 4.0.1 时，可以使用 IN BOOLEAN MODE 修饰语来执行一个逻辑全文搜索

2. 布尔全文索引

   in boolean mode修饰符。
   自定义某个被搜索的词语的相关性；当编写一个布尔搜索查询时，可以通过一些前缀修饰符来定制搜索。
   例如：
   ‘+’ 必须包含该词
   ‘-’ 必须不包含该词
   ‘>’ 提高该词的相关性，查询的结果靠前
   ‘<’ 降低该词的相关性，查询的结果靠后
   (*) 星号 通配符，只能接在词后面

全文搜索引擎 VS 数据库全文索引

全文搜索引擎	数据库全文索引
全文搜索引擎更适用于非结构化文本；它们提供丰富的文本搜索功能；可以进行更复杂的相关性排名；	适用于结构化的数据存储于操作

全文搜索系统擅长高速搜索和大量数据的分析。 它们在处理多记录类型或事务处理方面不如关系数据库那么强大，但在许多情况下它们可能适合这些需求。

全文搜索引擎的过程：

1. 索引过程：
   1 有一系列被索引文件
   2 被索引文件经过语法分析和语言处理形成一系列词(Term) 。
   3 经过索引创建形成词典和反向索引表。
   4 通过索引存储将索引写入硬盘。
2. 搜索过程：
   a) 用户输入查询语句。
   b) 对查询语句经过语法分析和语言分析得到一系列词(Term) 。
   c) 通过语法分析得到一个查询树。
   d) 通过索引存储将索引读入到内存。
   e) 利用查询树搜索索引，从而得到每个词(Term) 的文档链表，对文档链表进行交，差，并得到结果文档。
   f) 将搜索到的结果文档对查询的相关性进行排序。
   g) 返回查询结果给用户。

es全文索引

自然语言搜索：

为什么要用全文搜索引擎：全文搜索引擎 VS 数据库管理系统

mysql索引

转载地址：http://xojxi.baihongyu.com/