mysql索引

关于索引，需要明确点

1：索引是对数据库表中一列或多列的值进行排序的一种结构（通过缩小一张表中需要查询的记录/行的数目来加快搜索的速度。）。

2：创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

3：索引需要占物理空间。

4：表中的数据进行增、删、改的时候，索引也要动态的维护，这就降低了表的维护速度（执行增加、删除、更新操作操作的同时会对索引文件进行重新排序或更新）。

如何确认需要添加索引 

1：表中记录达到一定的条数，如300以上。

2：查询性能的要求，高于增删改。

如何确认哪些字段添加索引

1：SELECT：经常需要查询的列。

2：主键（mysql的Innode默认必须主键，所以使用Innodb时，主键索引一定存在的。）

3：:INNER JOIN、LEFT JOIN、RIGHT JOIN：经常作为表结合的条件的列。

4：WHERE：经常使用在WHERE子句中的列（根据范围（<，<=，=，>，>=，BETWEEN，IN）进行搜索的列，因为索引已经排序，其指定的范围是连续的，加快排序查询时间）。

5：ORDER BY：经常排序的列上创建索引。（原因：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间）。

6：索引应该建在维度高或选择性高的列创建索引，就是数据列中不重复值出现的个数,个数越大，维度就越高。要为维度高的列创建索引。如性别和年龄,那年龄的维度就高于性别，性别这样的列不适合创建索引,因为维度过低。

7：索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引（原因：较小的数据，索引文件更小，内存中也可以装载更多的索引键）。

8：较长的字符串使用前缀索引（确认前缀索引的长度：select count(distinct left(col_char,5)) / count(*) from t_table，得到前几个字母对标数据的覆盖率，覆盖率超过31%黄金值就可以使用前缀索引。红色的数字自行替换，找到一个最优的长度）。前缀字符并非越多越好，需要在索引的选择性和索引IO读取量中做出衡量。

9：使用组合索引,可以减少文件索引大小,在使用时速度要优于多个单列索引。

如何确认不应该添加索引

1：表非常小的情况下，不要添加索引，全文扫描更加。要记得索引要是一种资源的消耗。

2：数据列包含许多重复的内容，建立索引没有太大的效果，不要建立。

3：查询中很少使用或者参考的列，不要创建索引。（若列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。）

4：对于那些定义为text, image和bit数据类型的列不应该增加索引。

5：当修改性能要求远远高于检索性能时，不应该创建索引。（修改性能和检索性能是互相矛盾的）

索引失效的场景 

1：索引列有函数或表达式，将导致索引失效而进行全表扫描：SELECT id FROM t_tabel WHERE score-70>60或SELECT name FROM t_table WHERE LEFT(`name`,4) ='davi'。

3：索引列以通配符%开头（如：like ‘%abc’），则索引失效，直接全表扫描；若只是以%结尾，则不影响。

4：索引列的类型和条件值的类型不符合时：SELECT * FROM t_table WHERE type=1 （type列为varchar字符类型，用整数查找不走索引，需要使用type='1'，索引才会生效）。

5：条件有OR时，部分条件列为索引列，索引也不会生效。除非所有的条件列都是索引列，索引才会生效。

7：表中数据不多，只有几十几百条，MySQL评估使用全表扫描要比使用索引快,也不使用索引。

8：>、<范围查询：mysql 会一直向右匹配直到遇到索引搜索键使用>、<就停止匹配。一旦权重最高的索引搜索键使用>、<范围查询，那么其它>、<搜索键都无法用作索引。即索引最多使用一个>、<的范围列，因此如果查询条件中有两个>、<范围列则无法全用到索引。

9：联合索引中，where中索引列违背最左匹配原则，一定会导致索引失效。

10：order by 对主键索引排序会用到索引，其他的索引失效

 

索引的优点

1：索引大大减小了服务器需要扫描的数据量，从而大大加快数据的检索速度，这也是创建索引的最主要的原因。

2：索引可以帮助服务器避免排序和临时表。

3：索引可以将随机IO变成顺序IO。

4：索引对于InnoDB（对索引支持行级锁）非常重要，因为它可以让查询锁更少的元组，提高了表访问并发性。

5：InnoDB在二级索引上使用共享锁（读锁），但访问主键索引需要排他锁（写锁）。

6：通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

7：可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

8：在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

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

索引的缺点

1:：创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

2：索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间，如果需要建立聚簇索引，那么需要占用的空间会更大。

3：对表中的数据进行增、删、改的时候，索引也要动态的维护，这就降低了整数的维护速度。

复合索引的使用

1：最左前缀匹配原则：

即最左优先（查询条件精确匹配索引的左边连续一列或几列，则构建对应列的组合索引树），在检索数据时也从联合索引的最左边开始匹配。在复合索引中，索引第一位的column很重要，只要查询语句包含了复合索引的第一个条件,基本上就会使用到该复合索引（可能会使用其他索引）。在建复合索引的时候应该按照column的重要性从左往右建。

     假设A、B、C三个字段索引按A+B+C顺序创建的索引：

     A --走索引

    B --不走索引

    C --不走索引

    A + B 或 B + A -- 走索引

    B + C 或 C + B -- 不走索引

    A + B + C 或 B + C + A 或 C + B + A --走索引

覆盖索引与回表

1：覆盖索引：查询的字段包含在条件的索引键中（或是组合索引键中的其它字段）。覆盖索引的查询效率极高，原因在与其不用做回表查询。（查询的列数据作为索引树的键值，直接在索引树中得到反馈（存在于索引节点），不用遍历如InnoDB中的叶子节点（存放数据表各行数据）就可得到查询的数据（不用回表））。

2：回表：先定位主键值，再定位行记录（先通过普通索引定位到主键值，在通过聚集索引定位到行记录）

索引基数

在'show index from t_table'结果集中有一列为Cardinality的值，它的作用也非常的大，称之为：索引基数

索引基数：索引列的唯一值的个数，如果是复合索引就是唯一组合的个数。这个数值将会作为MySQL优化器对语句执行计划进行判定时依据。如果唯一性太小，那么优化器会认为这个索引对语句没有太大帮助，而不使用索引。cardinality值越大，就意味着，使用索引能排除越多的数据，执行也更为高效。

当有多个索引可用时，mysql会自动依据cardinality大的值来进行SQL索引选择优化。

假设数据库中有数据值100行，但是cardinality=89并不准确，是因为它不会自动更新，需要通过analyzetable来进行更新，示例如下：

mysql>analyze local table ad_article;

索引分类

按功能划分

1：主键索引：一张表只能有一个主键索引，不允许重复、不允许为 NULL。

2：唯一索引：数据列不允许重复，允许为 NULL 值，一张表可有多个唯一索引，但是一个唯一索引只能包含一列，比如身份证号码、卡号等都可以作为唯一索引。

3：普通索引：一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许 NULL 值插入。

4：全文索引：它查找的是文本中的关键词，主要用于全文检索。

按列数划分

1：单例索引：一个索引只包含一个列，一个表可以有多个单例索引。:

2：组合索引：一个组合索引包含两个或两个以上的列。查询的时候遵循 mysql 组合索引的 “最左前缀”原则，即使用 where 时条件要按照建立索引的时候字段的排列方式放置索引才会生效。:

物理分类

1：聚簇索引：聚簇是为了提高某个属性(或属性组)的查询速度，把这个或这些属性(称为聚簇码)上具有相同值的元组集中存放在连续的物理块。聚簇索引（clustered index）不是单独的一种索引类型，而是一种数据存储方式。这种存储方式是依靠B+树来实现的，根据表的主键构造一棵B+树且B+树叶子节点存放的都是表的行记录数据时，方可称该主键索引为聚簇索引。聚簇索引也可理解为将数据存储与索引放到了一块，找到索引也就找到了数据。

2：非聚簇索引：数据和索引是分开的，B+树叶子节点存放的不是数据表的行记录。

虽然InnoDB和MyISAM都默认使用B+树结构存储索引，但是只有InnoDB的主键索引才是聚簇索引，InnoDB中的辅助索引以及MyISAM使用的都是非聚簇索引。每张表只能拥有一个聚簇索引。

聚簇索引优缺点（InnoDB）
优点：

1：数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快:

2：聚簇索引对于主键的排序查找和范围查找速度非常快:

缺点：

1：插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键（主键列不要选没有意义的自增列，选经常查询的条件列才好，不然无法体现其主键索引性能）:

2：更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。:

3：二级索引访问：需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。
 

全文索引 FULLTEXT

全文索引：关键字的匹配来进行查询过滤，通过建立倒排索引,可以极大的提升检索效率,解决判断字段是否包含的问题。

FULLTEXT VS LIKE+%

使用LIKE+%确实可以实现模糊匹配，适用于文本比较少的时候。对于大量的文本检索，LIKE+%与全文索引的检索速度相比就不是一个数量级的。

FULLTEXT的支持情况

1：MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；

2：MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;

3：只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引:

4：InnoDB内部并不支持中文、日文等，因为这些语言没有分隔符。可以使用插件辅助实现中文、日文等的全文索引。

创建全文索引

1：建表的时候：FULLTEXT KEY keyname(colume1,colume2)  // 创建联合全文索引列

2：在已存在的表上创建:：create fulltext index keyname on xxtable(colume1,colume2);或者alter table xxtable add fulltext index keyname (colume1,colume2);

使用全文索引

全文索引有独特的语法格式，需要配合match 和 against 关键字使用

1：match()函数中指定的列必须是设置为全文索引的列:

2：against()函数标识需要模糊查找的关键字

3：建表文：

create table fulltext_test(
     id int auto_increment primary key,
     words varchar(2000) not null,a
     artical text not null,
     fulltext index words_artical(words,artical)
)engine=innodb default charset=utf8;

insert into fulltext_test values(null,'a','a');
insert into fulltext_test values(null,'aa','aa');
insert into fulltext_test values(null,'aaa','aaa');
insert into fulltext_test values(null,'aaaa','aaaa');

4:执行查询：
select * from fulltext_test where match(words,artical) against('a');
select * from fulltext_test where match(words,artical) against('aa');
select * from fulltext_test where match(words,artical) against('aaa');
select * from fulltext_test where match(words,artical) against('aaaa');

5：结果

发现只有aaa和aaaa才能查到记录，为什么会这样呢？
原因就是全文索引关键词长度阈值。
可以通过show variables like '%ft%';查看。可见InnoDB的全文索引的关键词最小索引长度 为3。上文使用的是InnoDB引擎建表，同时也解释为什么只有3a以上才有搜索结果。
设置关键词长度阈值，可以有效的避免过短的关键词，得到的结果过多也没有意义。

6：修改长度阈值
修改MySQL配置文件，在[mysqld]的下面追加以下内容，设置关键词最小长度为5。
[mysqld]
innodb_ft_min_token_size = 5
ft_min_word_len = 5
然后重启MySQL服务器（systemctl restart mysqld），还要修复索引（repair table 表名 quick;），不然参数不会生效

7：全文索引模式

为什么上文搜索关键字为aaa的时候，有且仅有一条aaa的记录，为什么没有aaaa的记录呢？
原因就是自然语言的全文索引 IN NATURAL LANGUAGE MODE
默认情况下，或者使用 IN NATURAL LANGUAGE MODE 修饰符时，match() 函数对文本集合执行自然语言搜索，上面的例子都是自然语言的全文索引。

自然语言搜索引擎将计算每一个文档对象和查询的相关度。这里，相关度是基于匹配的关键词的个数，以及关键词在文档中出现的次数。在整个索引中出现次数越少的词语，匹配时的相关度就越高。

MySQL在全文查询中会对每个合适的词都会先计算它们的权重，一个出现在多个文档中的词将有较低的权重(可能甚至有一个零权重)，因为在这个特定的集中，它有较低的语义值。否则，如果词是较少的，它将得到一个较高的权重，MySQL默认的阀值是50%。上面‘a’在每个文档都出现，因此是100%，只有低于50%的才会出现在结果集中；‘aaa’的权重值为1/2=50%，没有低于50%，因此’aaaa’不会出现在’aaa’结果集中。

这个机制也比较好理解，比如一个数据表存储的是一篇篇的文章，文章中的常见词、语气词等等，出现的肯定比较多，搜索这些词语就没什么意义了，需要搜索的是那些文章中有特殊意义的词，这样才能把文章区分开。
select * from fulltext_test where match(words,artical) against('aaa');
等价
select * from fulltext_test where match(words,artical) against('aaa'  IN NATURAL LANGUAGE MODE);

8：布尔全文索引 IN BOOLEAN MODE

在布尔搜索中，我们可以在查询中自定义某个被搜索的词语的相关性，这个模式和lucene中的BooleanQuery很像,可以通过一些操作符,来指定搜索词在结果中的包含情况。
建立如下表：
CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT (title,body)
) ENGINE=InnoDB
（1）：+ （AND）全文索引列必须包含该词且全文索引列（之一）有且仅有该词

（2）：- （NOT）表示必须不包含,默认为误操作符。如果只有一个关键词且使用了- ，会将这个当成错误操作，相当于没有查询关键词；如果有多个关键词，关键词集合中不全是负能符（~ -），那么-则强调不能出现。

举例：查找title,body列中有且仅有apple（是apple不是applexx 也不是 xxapple）但是不含有banana的记录
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+apple -banana' IN BOOLEAN MODE);

（3）*：> 提高该词的相关性，查询的结果靠前:

（4）：< 降低该词的相关性，查询的结果靠后

举例：返回同时包含apple（是apple不是applexx 也不是 xxapple）和banana或者同时包含apple和orange的记录。但是同时包含apple和banana的记录的权重高于同时包含apple和orange的记录。
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+apple +(>banana <orange)' IN BOOLEAN MODE);   

（5）：~ 异或，如果包含则降低关键词整体的相关性

举例：返回的记录必须包含apple（且不能是applexx 或 xxapple），但是如果同时也包含banana会降低权重（只出现apple的记录会出现在结果集前面）。但是它没有 +apple -banana 严格，因为后者如果包含banana压根就不返回。
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+apple ~banana' IN BOOLEAN MODE);

（6）：* 通配符，表示关键词后面可以跟任意字符

举例：返回的记录可以为applexx
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('apple*' IN BOOLEAN MODE);

（7）：空格 表示OR

举例：查找title,body列中包含apple（是apple不是applexx 也不是 xxapple）或者banana的记录，至少包含一个
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('apple banana' IN BOOLEAN MODE);  

（8）："" 双引号，效果类似like '%some words%'

举例：模糊匹配 “apple banana goog”会被匹配到，而“apple good banana”就不会被匹配
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"apple banana"' IN BOOLEAN MODE); 

9：InnoDB中FULLTEXT中文支持
InnoDB内部并不支持中文、日文等，因为这些语言没有分隔符。可以使用插件辅助实现中文、日文等的全文索引。

MySQL内置ngram插件便可解决该问题。
 FULLTEXT (title, body) WITH PARSER ngram
ALTER TABLE articles ADD FULLTEXT INDEX ft_index (title,body) WITH PARSER ngram;
CREATE FULLTEXT INDEX ft_index ON articles (title,body) WITH PARSER ngram;

InnoDB索引实现

InnoDB使用B+TREE存储数据，除了主键索引为聚簇索引，其它索引均为非聚簇索引。一个表中只能存在一个聚簇索引（主键索引），但可以存在多个非聚簇索引。

1：主键索引：B+树，叶子节点包含数据表中行记录就是聚簇索引（索引和数据是一块的）

主键索引结构分析：B+树单个叶子节点内的行数据按主键顺序排列，物理空间是连续的（聚簇索引的数据的物理存放顺序与索引顺序是一致的）；叶子节点之间是通过指针连接，相邻叶子节点的数据在逻辑上也是连续的（根据主键值排序），实际存储时的数据页（叶子节点）可能相距甚远。

2：辅助索引：在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行数据记录，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的Page Directory找到数据行。

3：InnoDB索引优化:：InnoDB中主键不宜定义太大，因为辅助索引也会包含主键列，如果主键定义的比较大，其他索引也将很大。如果想在表上定义 、很多索引，则争取尽量把主键定义得小一些。InnoDB 不会压缩索引。InnoDB中尽量不适用非单调字段作主键，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。
 

 Innodb主键索引图

 

 Innodb辅助索引图

 

MyISAM索引实现

MyISAM也使用B+Tree作为索引结构，但具体实现方式却与InnoDB截然不同。MyISAM使用的都是非聚簇索引。

1：MyISAM表的索引和数据是分开存储的，.MYD表数据文件 .MYI表索引文件

2：MyISAM辅助索引：在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。 MyISAM辅助索引也是非聚簇索引。

MyISAM主键索引图

 

InnoDB和MyISAM的索引检索过程

1：对于InnoDB和MyISAM而言，主键索引是根据主关键字来构建的B+树存储结构，辅助索引则是根据辅助键来构造的B+树存储结构，彼此的索引树都是相互独立的。

2：InnoDB辅助索引的访问需要两次索引查找，第一次从辅助索引树找到主键值，第二次根据主键值到主键索引树中找到对应的行数据。:

3：MyISM使用的是非聚簇索引，表数据存储在独立的地方，这两棵（主键和辅助键）B+树的叶子节点都使用一个地址指向真正的表数据。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。:

4：聚簇索引的叶子节点存放的是数据行（主键值也是行内数据），支持覆盖索引。

5：二级索引的叶子节点存放的是主键值或指向数据行的指针。

6：由于叶子节点(数据页)只能按照一棵B+树排序，故一张表只能有一个聚簇索引。辅助索引的存在不影响聚簇索引中数据的组织，所以一张表可以有多个辅助索引。

 

 

 

B+TREE 

是N叉树，层级小，非叶子节点不再存储数据，数据只存储在同一层的叶子节点上，B+树从根到每一个节点的路径长度一样。B树的查询/插入/修改/删除的平均时间复杂度都为O(log(n));

1：叶子之间，增加了链表，获取所有节点，不再需要中序遍历，使用链表的next节点就可以快速访问到

2：范围查找方面，当定位min与max之后，中间叶子节点，就是结果集，不用中序回溯（范围查询在SQL中用得很多，这是B+树比B树最大的优势）

3：叶子节点存储实际记录行，记录行相对比较紧密的存储（稠密索引），且是有序的，适合大数据量磁盘存储；非叶子节点存储记录的PK（叶子结点的索引（稀疏索引）），用于查询加速，适合内存存储

4：非叶子节点，不存储实际记录，而只存储记录的KEY的话，那么在相同内存的情况下，B+树能够存储更多索引

可以来初步计算一下：假设key、子树节点指针均占用4B，则B树一个节点占用4 + 4 = 8B，一页页面大小4KB，则N = 4 * 1024 / 8B = 512，一个512叉的B树，1000w的数据，深度最大 log(512/2)(10^7) 约等于4。对比二叉树如AVL的深度为log(2)(10^7) 约为24，相差了5倍以上！

假如一个节点大小是4KB，一个KEY有8字节，一页可以存4000/8=500个KEY，根据N叉树特点,就算一层500叉节点，则：

第一层树：1个节点，1*500KEY , 大小4K

第二层树：500节点 500*500=25万个KEY，500*4K=2M

第三层树：500 * 500节点 500*500*500=1.2亿KEY，500*500*4K=1G

如果没算错，1G空间，只用三层树结构，可以存1.2亿行数据的KEY，B+树牛掰不？

所以B+TREE索引只用占用很少的内存空间，却大大提升了查询效率（不论是单个查询、范围查询还是有序性查询），并且还减少了磁盘读写，所以好的算法与数据结构是可以省钱的。
 

 

B-TREE

B-树就是B树，多路搜索树，树高一层意味着多一次的磁盘I/O，查询/插入/修改/删除的平均时间复杂度都为O(log(n));

B树的特征：

1：关键字集合分布在整颗树中；

2：任何一个关键字出现且只出现在一个结点中；

3：搜索有可能在非叶子结点结束；

4：其搜索性能等价于在关键字全集内做一次二分查找；

5：自动层次控制；
 

HASH

哈希索引：基于哈希表实现，采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。查询/插入/修改/删除的平均时间复杂度都为O(1)。只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。:

由于Hash索引比较的是进行Hash运算之后的Hash值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的Hash算法处理之后的Hash值的大小关系，并不能保证和Hash运算前完全一样。

hash索引的限制：

1：哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。:

2：哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。:

3：哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。:

4：哈希索引只支持等值比较查询，包括=、IN()、<>（注意<>和<=>是不同的操作）。也不支持任何范围查询，例如WHERE price>100。

5：访问哈希索引的数据非常快，除非有很多哈希冲突（不同的索引列值却有相同的哈希值）。当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找到所有符合条件的行。:

如果哈希冲突很多的话，一些索引维护操作的代价也会很高。例如，如果在某个选择性很低（哈希冲突很多）的列上建立哈希索引，那么当从表中删除一行时，存储引擎需要遍历对应哈希值的链表中的每一行，找到并删除对应行的引用，冲突越多，代价越大。