MySql数据库索引的学习

一、索引的本质：索引是数据结构

在数据库中，除了数据外，还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构的基础上实现高级查找算法。这种数据结构，就是索引。

二、MySql数据库的索引实现：

1、MyISAM索引实现：

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。

下图是MyISAM引擎的主索引的原理图：

可以看出MyISAM引擎的索引文件仅仅保存数据记录的地址。MyISAM索引文件和数据文件是分离的。

MyISAM引擎不仅可以使用主键建立主索引，还可以不使用主键来建立辅助索引。主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。

MyISAM引擎的索引方式也叫做“非聚集”的。之所以叫“非聚集”索引，是因为它的索引文件中只保存数据记录的地址，不包含完整的数据记录。

MyISAM引擎中索引检索的算法为按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

2、InnoDB索引的实现：

InnoDB也使用B+Tree作为索引结构，但它与MyISAM索引是有区别的：

第一个区别是它的数据文件本身就是索引文件，不像MyISAM索引那样数据文件与索引文件是分离的。

在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

因为InnoDB的索引结构B+Tree的叶子节点包含了完整的数据记录，所以InnoDB索引这种索引又叫作“聚集”索引。

因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

第二个区别是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。

如下图所示：

聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

 

三、最左前缀原理与相关优化

接下来介绍什么样的查询会使用到索引，这个问题和B+Tree中的“最左前缀原理”有关：

以employees.titles表为例，假设titles表的主索引有三个，分别为<emp_no, title, from_date>

情况一：全列匹配。即查询条件用到所有索引列。

SELECT * FROMemployees.titles WHERE emp_no='10001' AND title='Senior Engineer' ANDfrom_date='1986-06-26';

这种情况索引是可以被用到的。理论上索引对顺序是敏感的，但是由于MySQL的查询优化器会自动调整where子句的条件顺序以使用适合的索引。

情况二：最左前缀匹配

SELECT * FROMemployees.titles WHERE emp_no='10001';

当查询条件使用索引的左边连续一个或几个列时，如<emp_no>或<emp_no,title>，也可以被用到，但是只能用到一部分，即条件所组成的最左前缀。

情况三：查询条件用到了索引中列的精确匹配，但是中间某个条件未提供

SELECT * FROMemployees.titles WHERE emp_no='10001' AND from_date='1986-06-26';

对于这种情况，因为title未提供，所以查询只用到了索引的第一列，而后面的from_date虽然也在索引中，但是由于title不存在而无法和左前缀连接，因此需要对结果进行扫描过滤from_date。

情况四：查询条件没有指定索引第一列

SELECT * FROMemployees.titles WHERE from_date='1986-06-26';

由于不是最左前缀，这样的查询显然用不到索引。它会扫描整个表的记录，然后根据查询条件来过滤。

情况五：匹配某列的前缀字符串

SELECT * FROMemployees.titles WHERE emp_no='10001' AND title LIKE 'Senior%';

这种情况可以用到索引，如果通配符%不出现在开头，则可以用到索引，但根据具体情况不同可能只会用其中一个前缀

情况六：范围查询

SELECT * FROMemployees.titles WHERE emp_no < '10010' and title='Senior Engineer';

对于这种情况，范围列可以用到索引（必须是最左前缀），但是范围列后面的列无法用到索引。同时，索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引。

当查询条件中有两个或多个范围列时，比如下面这样：

SELECT * FROM titlesWHERE emp_no < '10010' AND title='Senior Engineer' AND from_date BETWEEN '1986-01-01'AND '1986-12-31';

则索引对第二个范围列无能为力。

在此解释一下什么是多值匹配和范围匹配：

多值匹配是指：用到IN，比如title IN ('Senior Engineer', 'Staff', 'Engineer')

范围匹配是指：当然是用到范围符号了，比如>、<等。同时也要注意用到BETWEENAND 不一定就是范围匹配。

 

四、索引选择性与前缀索引：

一般两种情况下不建议使用索引：

第一种情况是：表记录比较少。如果表中记录较少，没必要建索引，让查询做全表扫描就好了。

第二种情况是：索引的选择性较低。

何谓索引的选择性：就是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值：Index Selectivity =Cardinality / #T

显然选择性的取值范围为(0, 1]，选择性越高的索引价值越大，这是由B+Tree的性质决定的。

 

前缀索引：有一种与索引选择性有关的索引优化策略叫做前缀索引，就是用列的前缀代替整个列作为索引key，当前缀长度合适时，可以既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销

举例：ALTER TABLE employees.employees ADD INDEX`first_name_last_name4` (first_name, last_name(4));

使用first_name,和last_name这两列创建复合索引，因为这样得出的索引选择性比较高，如果只是用一列可能选择性偏低。

同时我们仅使用last_name字段的前4个字符，缩短了索引的长度，效率更高。

前缀索引的缺点：前缀索引兼顾索引大小和查询速度，但是其缺点是不能用于ORDER BY和GROUP BY操作。

 

五、使用InnoDB存储引擎的主键选择与插入优化：

在使用InnoDB存储引擎时，如果没有特别的需要，最好使用一个与业务无关的自增字段作为主键。

这是因为InnoDB使用聚集索引，数据记录本身被存于主索引（一颗B+Tree）的叶子节点上。如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。这样就会形成一个紧凑的索引结构，并且由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。

如果使用非自增主键做索引，每次插入主键的值近似于随机，那么数据库会化很多开销来使新纪录插入到合适的位置，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构。

下面详细说说为什么要创建索引？以及在哪创建索引？这两个问题：

为什么要创建索引？

创建索引可以大大提高系统的性能。

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

　　　　②可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

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

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

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

增加索引有如此多的优点，为什么不对表中的每一个列创建一个索引呢？因为，增加索引也有许多不利的方面

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

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

　　　　③当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

在哪建索引？

可以在下面这些列上创建索引：

　　①在经常需要搜索的列上，可以加快搜索的速度；

　　②在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；

　　③在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；

       ④在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；

　　⑤在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

　　⑥在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

不可以在下面这些列上创建索引：

　　①对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，            反而降低了系统的维护速度和增大了空间需求。

　　②对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比             例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。

　　③对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少,不利于使用索引。

　　④当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引             时，会提高修改性能，降低检索性能。因此，当修改操作远远多于检索操作时，不应该创建索引。