关于mysql索引的基础学习笔记

关于本文

算是自己在查找网上资料的时候看到的和学到的各种笔记的整理，着重讲述入门用法和基础原理，实践部分较少，进阶用法基本没有。还是先感谢已经写了不少资料的大佬们。

什么是数据库索引

关于索引在日常生活中的概念已经不用多说，书本目录，楼层区域图的划分都算是索引，而数据库的索引的目的其实也一样，都是为了增加查找速度。
在数据库中，索引的实现是通过数据结构。当然，这种数据结构数据库已经给你准备好了，大多数情况下你不需要自己去编写一个索引数据结构。一般情况下只需要使用sql语句中的一些关键字就能很轻松的创建并使用索引。
另外需要注意的地方是，索引是建立在“列”基础上的，要想建立索引就必须对某一列或者多列创建索引，而不是对整条表创建索引
最后一点，不同的数据引擎对索引有不同的支持。详细情况可以查看我的这篇博客

不建议使用索引的情况

需要注意的是，索引并不是加的越多越好。索引的建立也是需要消耗额外的空间以及数据库的性能，因此应该只在必要的时候建立索引。有几个明显的场合不应建立索引，比如下面几种情况：

• 查询中很少使用到的列 不应该创建索引,如果建立了索引然而还会降低mysql的性能和增大了空间需求.
• 当表的修改(UPDATE,INSERT,DELETE)操作远远大于检索(SELECT)操作时不应该创建索引,这两个操作是互斥的关系。一般如果改的操作：读的操作>1：10的话，建议对应列不加索引。
• 数据唯一性差（一个字段的取值只有几种时）的字段不要使用索引。比如性别，只有两种可能数据。意味着索引的二叉树级别少，多是平级。这样的二叉树查找无异于全表扫描。
• 数据没有达到一定量级的情况。

mysql索引的数据结构

mysql数据库中的数据结构一般包含B-Tree和哈希表两种。

B-Tree

默认使用的是B-tree作为索引的数据结构。B树适合在磁盘等直接存储设备上组织动态查找表，文件的组织方式便是B树或B+树。他在查询和动态操作方面效率很高。而且B-Tree可以用来排序，适用于< ,<= ,= ,>= ,>,between,in等比对关键字。
本次博客的下面所举的大部分例子也主要是以这种数据结构为基础

哈希表

哈希表是另外一种你可能看到用作索引的数据结构-这些索引通常被称为哈希索引。使用哈希索引的原因是，在寻找值时哈希表效率极高。所以，如果使用哈希索引，对于比较字符串是否相等的查询能够极快的检索出的值。例如这个查询(SELECT * FROM Employee WHERE Employee_Name = ‘Jesus’) 就可以受益于创建在Employee_Name 列上的哈希索引。哈系索引的工作方式是将列的值作为索引的键值（key），和键值相对应实际的值（value）是指向该表中相应行的指针。在哈系索引的中查询一个像“Jesus”这样的值，明显要比扫描全表获得值为“Jesus”的行的方式快很多。
不过 mysql的InnoDB引擎有自己内置的哈希索引优化。
但是哈希索引有一些非常麻烦的限制，具体如下：

• 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
• 哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。例如，在数据列(A, B)上建立哈希索引，如果查询只有数据列A，则无法使用该索引。
• 哈希索引只支持等值比较查询，包括=、in()、<=>。不支持任何范围查询，例如where price > 100。
• 访问哈希索引的数据非常快，除非有很多哈希冲突。如果哈希冲突很多的话，一些索引维护操作的代价也很高。

这就是为什么哈希索引通常不是数据库索引的默认数据结构-因为在作为索引的数据结构时，其不像B-Tree那么灵活

mysql索引的种类

普通索引

普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件(WHERE column = …)或排序条件(ORDER BY column)中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列(如一个整数类型的数据列)来创建索引。
添加方式：CREATE INDEX indexName ON mytable(username(length));
或者：ALTER table tableName ADD INDEX indexName(columnName)

唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
创建方式：CREATE UNIQUE INDEX indexName ON mytable(username(length))
或：ALTER table mytable ADD UNIQUE [indexName] (username(length))

主键索引

这个就是我们所说的主键。不允许有空值且必须唯一。
添加方式：CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) );
或：ALTER TABLE table_name ADD CONSTRAINT PRIMARY KEY pk_name (列名);;

组合索引

如果一个表里你想给三个字段都加上索引，比如这里有a，b，c三个字段，建立索引的话就是index_a，index_b，index_c三个索引。为了进一步提高效率，我们可以建立组合索引，将a,b,c三个看成一个整体。一个索引最多可以有15个列组成。
创建方法：ALTER TABLE tablename ADD INDEX index_name(a,b,c);
当然组合索引有一个需要注意的地方，那就是索引满足“最左前缀”规则。举个例子，下面这几条sql语句可以用到索引：

1. select * from tablename where a="xxxxx"
2. select * from tablename where a="xxxxx" and b="xxxx"
3. select * from tablename where a="xxxxx" and b="xxxx" and c="xxxxx"

但下面这几种用法就无法使用索引：

1. select * from tablename where b="xxxx"
2. select * from tablename where c="xxxx"
3. select * from tablename where b="xxxx" and c="xxxx"
4. select * from tablename where a="xxxx" and c="xxxx"

也就是说，要查就得像链表一样一串那样从头开始一串查下去，中间不能断开，也不能跳过头结点。
为什么说组合索引就会比三个普通索引的效率高呢。加入我们创建了index_a，index_b，index_c三个索引，在执行的时候，虽然此时有了三个索引，但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。再说简单点，实际上只执行了一个索引。那么自然效率肯定比不过组合索引。

全文索引

全文索引并不是所有的数据引擎都能用（实际上前面说的索引也并不是所有的数据引擎都能用，只不过因为常用的数据引擎支持所以没有提），在mysql5.6.24版本之前，innoDB是无法使用的，之后就可以了。不知道什么是数据引擎的同学可以看我的这篇笔记。
全文索引相比上面集中来说可以算是另类了。全文索引是以“单词”为基本单位，并且有一套自己的查询语法。MySQL默认的分词是所有非字母和数字的特殊符号都是分词符(外国人嘛)。。另外一点，MySQL不支持中文全文索引，原因很简单：与英文不同，中文的文字是连着一起写的，中间没有MySQL能找到分词的地方，截至目前MySQL5.6版本是如此。要想对中文使用全文搜索，就必须在一开始存进数据库之前先对中文的格式进行一定的修饰，然后再存进数据库。
文本字段上的普通索引只能加快对出现在字段内容最前面的字符串(也就是字段内容开头的字符)进行检索操作。如果字段里存放的是由几个、甚至是多个单词构成 的较大段文字，普通索引就没什么作用了。这种检索往往以LIKE %word%的形式出现，这对MySQL来说很复杂，如果需要处理的数据量很大，响应时间就会很长。
这类场合正是全文索引(full-text index)可以大显身手的地方。在生成这种类型的索引时，MySQL将把在文本中出现的所有单词创建为一份清单，查询操作将根据这份清单去检索有关的数 据记录。全文索引即可以随数据表一同创建，也可以等日后有必要时再使用
创建方式：ALTER TABLE tablename ADD FULLTEXT(column1, column2)
具体的使用方法可以参考这篇博客

索引使用注意

1.在经常需要搜索的列上,可以加快索引的速度

2.主键列上可以确保列的唯一性

3.在表与表的join连接条件上加上索引,可以加快连接查询的速度

4.在经常需要排序(order by),分组(group by)和的distinct 列上加索引 可以加快排序查询的时间, (单独order by 用不了索引，索引考虑加where 或加limit)

5.在一些where 之后的 < <= > >= BETWEEN IN 以及某个情况下的like 建立字段的索引(B-TREE)

6.like语句的 如果你对nickname字段建立了一个索引.当查询的时候的语句是 nickname lick ‘%ABC%’ 那么这个索引讲不会起到作用.而nickname lick ‘ABC%’ 那么将可以用到索引

7.索引不会包含NULL列,如果列中包含NULL值都将不会被包含在索引中,复合索引中如果有一列含有NULL值那么这个组合索引都将失效,一般需要给默认值0或者 ’ ‘字符串

8.使用短索引,如果你的一个字段是Char(32)或者int(32),在创建索引的时候指定前缀长度 比如前10个字符 (前提是多数值是唯一的..)那么短索引可以提高查询速度,并且可以减少磁盘的空间,也可以减少I/0操作.

9.不要在列上进行运算,这样会使得mysql索引失效,也会进行全表扫描

10.选择越小的数据类型越好,因为通常越小的数据类型通常在磁盘,内存,cpu,缓存中 占用的空间很少,处理起来更快

总结

总的来说，索引不要乱用，只在必要的场合和必要的条件下达到一定量级的数据后，才使用索引。

参考资料

非常感谢这些前辈们写的资料，我这篇博客也是因为有了这些前辈们的资料才能够完成
https://www.zhihu.com/question/67094336/answer/250034118
https://blog.csdn.net/kaka1121/article/details/53395628
http://www.runoob.com/mysql/mysql-index.html
http://www.jb51.net/article/95575.htm
https://www.cnblogs.com/heyonggang/p/6610526.html
https://www.cnblogs.com/chenshishuo/p/5030029.html