MySQL索引讲解

1、索引本质

Mysql官方表示，索引（Index）是帮助MySQL高效获取数据排好序的数据结构，所以其实说到底，索引其实就是数据结构

2、为什么要用索引

索引用于快速找出某个列中有一特定值的行，不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多，如果表中查询的列有一个索引，MySql能够快速到达一个位置去搜索文件，而不必查看所有的数据，那么将会节省很大一部分时间。
你可以理解为书的目录，当我们要从一本书中找到某一章节在第几页的时候，就可以通过目录俩查询，这样比较快

优点
1、索引大大减小了服务器需要扫描的数据量
2、索引可以帮助服务器避免排序和临时表
3、索引可以将随机IO变成顺序IO
4、索引对于InnoDB（对索引支持行级锁）非常重要，因为它可以让查询锁更少的元组。在MySQL5.1和更新的版本中，InnoDB可以在服务器端过滤掉行后就释放锁，但在早期的MySQL版本中，InnoDB直到事务提交时才会解锁。对不需要的元组的加锁，会增加锁的开销，降低并发性。 InnoDB仅对需要访问的元组加锁，而索引能够减少InnoDB访问的元组数。但是只有在存储引擎层过滤掉那些不需要的数据才能达到这种目的。一旦索引不允许InnoDB那样做（即索引达不到过滤的目的），MySQL服务器只能对InnoDB返回的数据进行WHERE操作，此时，已经无法避免对那些元组加锁了。如果查询不能使用索引，MySQL会进行全表扫描，并锁住每一个元组，不管是否真正需要。
关于InnoDB、索引和锁：InnoDB在二级索引上使用共享锁（读锁），但访问主键索引需要排他锁（写锁）

缺点
1、虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存索引文件。
2、建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。
3、如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
4、对于非常小的表，大部分情况下简单的全表扫描更高效；

使用原则
1、对于经常更新的表就避免对其进行过多的索引，对于经常查询的字段应该创建索引
2、数据量小的表最好不要使用索引，由于数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，这样索引可能就不会产生优化效果
3、在不同值少的字段上不要建立索引，比如用户表上性别字段，只有男女两个值，这种字段就不适合建立索引。

3、可用来作索引的数据结构

• 二叉树
• 红黑树
• Hash表
• BTree
• B+ Tree

3.1 二叉树

假设现在有一个表，表里面有col1和col2两个字段，里面的数据如上图

• 不使用索引，比如说现在执行sql语句 select * from t where col2 = 89，需要访问磁盘6次，当数据量很大的时候，大大的降低了查询效率

• 这时，使用二叉树作为索引的数据结构，给col2字段加上索引，还是执行sql语句select * from t where col2=89这一行的数据，现在只需要查询两次就可以了
• 如果使用col1作为索引，（这里col1字段的值类似于自然主键），那么二叉树会退化成为链表，使用索引就没有什么意义了
  

3.2红黑树

• 红黑树，又称之为自适应平衡二叉树，当左右两边的深度之差大于2时，会自动调节，也就是我们通常所说的左旋和右旋
• 但是使用红黑树用来作索引的数据结构的话，当存储数据达到百万级别，红黑树的层级会变得特别高，这样也是会影响效率的

3.3 hash表

• 在大多数情况下，不会使用hash表，会存在一个hash冲突的问题
• hash可用于精准查询，但无法进行范围查询
• 但是hash访问速度很快

3.4 BTree

• 叶子节点具有相同的深度，叶子节点的指针为空
• 所有索引元素不重复
• 节点中的数据索引从左到右递增排序

3.5B+Tree

• 非叶子节点不存储data，只存储索引（冗余），这样就可以存放更多的索引了
• 叶子节点包含所有的索引字段
• 叶子节点之间用指针连接，提高区间访问性能
  举例说明：假如说我要查询大于20的数据，那么使用B+数作为数据结构的时候，我先查找到20所在的位置，然后遍历后续的叶子节点即可。但是如果使用B树的话，我先找到20，找到后遍历，到49后我又要重新从根节点再查一次，因为它区间与区间之间没有连接起来
  
  简单计算一下：
  假设mysql数据库设置每次读取的内存大小为16KB，一个节点大约可以存储16KB/(8B+6B)个索引，差不多是1170个索引。（这里假设索引的大小为8B，指针大小为6B），而我们的非叶子节点不存储数据，这样就可以存放更多的索引了。尽管树的结构只有三层，却可以存放2000多万条数据了（假设data占1KB），1170117016

4、索引的分类

4.1、普通型索引

这是最基本的索引类型，而且它没有唯一性之类的限制。普通索引可以通过以下几种方式创建：

（1）创建索引，例如CREATE INDEX 索引的名字 ON tablename (列名1，列名2,...);
（2）修改表，例如ALTER TABLE tablename ADD INDEX 索引的名字 (列名1，列名2,...);
（3）创建表的时候指定索引，例如CREATE TABLE tablename ( [...], INDEX 索引的名字 (列名1，列名2,...) );

4.2、唯一索引

这种索引和前面的“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一。唯一性索引可以用以下几种方式创建：

（1）创建索引，例如CREATE UNIQUE INDEX 索引的名字 ON tablename (列的列表);
（2）修改表，例如ALTER TABLE tablename ADD UNIQUE 索引的名字 (列的列表);
（3）创建表的时候指定索引，例如CREATE TABLE tablename ( [...], UNIQUE 索引的名字 (列的列
表) );

4.3、主键索引

主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”。如果你曾经用过AUTO_INCREMENT类型的列，你可能已经熟悉主键之类的概念了。
主键一般在创建表的时候指定，例如“CREATE TABLE tablename ( […], PRIMARY KEY (列的列表) ); ”。但是，我们也可以通过修改表的方式加入主键，例如“ALTER TABLE tablename ADD PRIMARY KEY (列的列表); ”。每个表只能有一个主键。 （主键相当于聚合索引，是查找最快的索引）

4.4、单列索引和多列索引

索引可以是单列索引，也可以是多列索引。
（1）单列索引就是常用的一个列字段的索引，常见的索引。
（2）多列索引就是含有多个列字段的索引
alter table student add index sy(name,age，score);
索引sy就为多列索引，多列索引在以下几中情况下才能有效：
select * from student where name=‘jia’ and age>=‘12’ //where条件中含有索引的首列字段和
第二个字段
select * from student where name=‘jia’ //where条件中只含有首列字段
select * from student where name=‘jia’ and score<60//where条件中含有首列字段和第三个字
段
总结：多列索引只有在where条件中含有索引中的首列字段时才有效

4.5、选择索引列

应该怎样选择索引列，首先要看查询条件，一般将查询条件中的列作为索引