关于索引
“索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址，在数据十分庞大的时候，索引可以大大加快查询的速度，这是因为使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据”
1.使用索引的好处
创建索引可以大大提高系统的性能。
第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性
第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因
第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义
第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间
第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能
2.索引的原理
数据在磁盘上是以块的形式存储的。为确保对磁盘操作的原子性，访问数据的时候会一并访问所有数据块。磁盘上的这些数据块与链表类似，即它们都包含一个数据段和一个指针，指针指向下一节点（数据块）的内存地址，而且它们都不需要连续存储（即逻辑上相邻的数据块在物理上可以相隔很远）
鉴于很多记录只能做到按一个字段排序，所以要查询某个未经排序的字段，就需要使用线性查找，即要访问N/2个数据块，其中N指的是一个表所涵盖的所有数据块。如果该字段是非键字段（也就是说，不包含唯一值），那么就要搜索整个表空间，即要访问全部N个数据块
然而，对于经过排序的字段，可以使用二分查找，因此只要访问log2N个数据块。同样，对于已经排过序的非键字段，只要找到更大的值，也就不用再搜索表中的其他数据块了。这样一来，性能就会有实质性的提升
3.什么时候建立索引
索引是建立在数据库表中的某些列的上面。因此，在创建索引的时候，应该仔细考虑在哪些列上可以创建索引，在哪些列上不能创建索引。一般来说，应该在这些列上创建索引，例如：在经常需要搜索的列上，可以加快搜索的速度；在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度
同样，对于有些列不应该创建索引。一般来说，不应该创建索引的这些列具有下列特点：
第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统维护速度和增大了空间需求。
第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
第三，对于那些定义为text，image和bit数据类型的列不应该增加索引。这是因为这些列的数据量要么相当大，要么取值很少。
第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。
索引数据结构
平衡树
BTree索引
节点存储数据的索引和指向数据的指针，非叶子节点还包括指向向下磁盘块的指针
B+Tree索引
非叶子节点只存索引和指向向下的指针，叶子节点存储索引和指向数据的指针（有的叶子节点还可以存在指向相邻数据索引的指针）
mysql采用的是B+Tree，原因在于B+Tree非叶子节点只存索引，单个节点的内存占用会小一些，总体占用的内存相对小一些，同时在查询时由于节点内存相对较小，可以减少加载读取索引表时的IO次数
索引的优缺点
优势：可以快速检索，减少I/O次数，加快检索速度；根据索引分组和排序，可以加快分组和排序
劣势：索引本身也是表，因此会占用存储空间，一般来说，索引表占用的空间的数据表的1.5倍；索引表的维护和创建需要时间成本，这个成本随着数据量增大而增大；构建索引会降低数据表的修改操作（删除，添加，修改）的效率，因为在修改数据表的同时还需要修改索引表
索引的分类
主键索引：即主索引，根据主键建立索引，不允许重复，不允许空值，一般在建表的时候同时建立
唯一索引：用来建立索引的列的值必须是唯一的，允许空值
普通索引：用表中的普通列构建的索引，没有任何限制
全文索引：用大文本对象的列构建的索引
组合索引：用多个列组合构建的索引，这多个列中的值不允许有空值（在Mysql中遵循“最左前缀原则”，把最常用作为检索或排序的列放在最左，依次递减）
索引的实现原理
MySQL支持诸多存储引擎，而各种存储引擎对索引的支持也各不相同，因此MySQL数据库支持多种索引类型，如BTree索引、B+Tree索引，哈希索引、全文索引等等
1.哈希索引
只有memory存储引擎支持哈希索引，哈希索引引用索引列的值计算该值的hashCode，然后再hashCode相应的位置存储该值所在行数据的物理位置，因为使用散列算法，因此访问速度非常快，但是一个值只能对应一个hashCode，而且是散列的分布方式，因此哈希索引不支持范围查找和排序的功能
2.全文索引
FULLTEXT（全文）索引，仅可用于MyISAM和InnoDB，针对较大的数据，生成全文索引非常的消耗时间和空间。对于文本的大对象，或者较大的CHAR类型的数据，如果使用普通索引，那么匹配文本前几个字符还是可行的，但是想要匹配文本中间的几个单词，那么就要使用LIKE%word%来匹配，这样需要很长的时间来处理，响应时间会大大增加，这种情况，就可使用时FULLTEXT索引了，在生成FULLTEXT索引时，会为文本生成一份单词的清单，在索引时及根据这个单词的清单来索引。FULLTEXT可以在创建表的时候创建，也可以在需要的时候用ALTER或者CREATE INDEX来添加
索引的使用策略
1.什么时候要使用索引？
主键自动建立唯一索引
经常作为查询条件在WHERE或者ORDER BY语句中出现的列要建立索引
作为排序的列要建立索引
查询中与其他表关联的字段，外键关系建立索引
高并发条件下倾向组合索引
用于聚合函数的列可以建立索引，例如使用了max(column_1)或者count(column_1)时的column_1就需要建立索引
2.什么时候不要使用索引
经常增删改的列不要建立索引
有大量重复的列不建立索引
表记录太少不要建立索引，只有当数据库里已经有了足够多的测试数据时，它的性能测试结果才有实际参考价值。如果在测试数据库里只有几百条数据记录，它们往往在执行完第一条查询命令之后就被全部加载到内存里，这将使后续的查询命令都执行得非常快，不管有没有使用索引。只有当数据库里的记录超过1000条、数据总量也超过了MySQL服务器上的内存总量时，数据库的性能测试结果才有意义
3.索引失效的情况
在组合索引中不能有列的值为NULL，如果有，那么这一列对组合索引就是无效的
在一个SELECT语句中，索引只能使用一次，如果在WHERE中使用了，那么在ORDER BY中就不要用了
LIKE操作中，’%aaa%‘不会使用索引，也就是索引会失效，但是'aaa%'可以使用索引
在索引的列上使用表达式或者函数会使索引失效，例如 select * from users where YEAR(adddate) < 2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成 select * from users where adddate < '2007-01-01'。其他通配符同样，也就是说，在查询条件中使用正则表达式时，只有在搜索模板的第一个字符不是通配符的情况下才能使用索引
在查询条件中使用不等于，包括<、>和!=会导致索引失效。特别的是如果对主键索引使用!=则不会使索引失效，如果对主键索引或者整数类型的索引使用<或者>不会使索引失效
在查询条件中使用IS NULL或者IS NOT NULL会导致索引失效
字符串不加单引号会导致索引失效。更准确的说是类型不一致会导致失效，比如字段email是字符串类型，使用WHERE email = 99999则会导致失效，应该改为WHERE email = '99999'
在查询条件中使用OR连接多个条件会导致索引失效，除非OR链接的每个条件都加上索引，这时应该改为两次查询，然后用UNION ALL连接起来
如果排序的字段使用了索引，那么select的字段也要是索引字段，否则索引失效。特别的是如果排序的是主键索引则select *也不会导致索引失效
尽量不要包括多列排序，如果一定要，最好为这队列构建组合索引
索引的优化
1.最左前缀
索引的最左前缀和B+Tree中的“最左前缀原理”有关，举例来说就是如果设置了组合索引<col1, col2, col3>，那么以下3种情况可以使用索引：col1，<col1, col2>，<col1, col2, col3>，其他的列，比如<col2, col3>， <col1, col3>，col2，col3等等都是不能使用索引的
根据最左前缀原则，我们一般把排序分组频率最高的列放在最左边，以此类推
2.带索引的模糊查询优化
使用LIKE进行模糊查询的时候，'%aaa%'不会使用索引，也就是索引会失效。如果是这种情况，只能使用全文索引来进行优化
3.为检索的条件构建全文索引，然后使用
SELECT * FROM tablename MATCH(index_column) ANGAINST('word')
4.使用短索引
对字符串列进行索引，如果可能应该指定一个前缀长度
例如，有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是唯一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作
聚簇索引和非聚簇索引
主键索引往往是聚簇索引，其他索引通常是非聚簇索引
其他
索引优化
1.独立的列
在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用索引
2.多列索引
在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好
3.索引列的顺序
让选择性最强的索引列放在前面
索引的选择性是指：不重复的索引值和记录总数的比值。最大值为1，此时每个记录都有唯一的索引与其对应。选择性越高，每个记录的区分度越高，查询效率也越高
4.前缀索引
对于BLOB、TEXT和VARCHAR类型的列，必须使用前缀索引，只索引开始的部分字符
前缀长度的选取需要根据索引选择性来确定
5.覆盖索引
索引包含所有需要查询的字段的值
具有以下优点：
索引通常远小于数据行的大小，只读取索引能大大减少数据访问量
一些存储引擎（MyISAM）在内存只缓存索引，而数据依赖于操作系统来缓存。因此，只访问索引可以不使用系统调用（通常比较费时）
对于InnoDB引擎，若辅助索引能够覆盖查询，则无需访问主索引
索引的优点
大大减少了服务器需要扫描的数据行数
帮助服务器避免进行排序和分组，以及避免创建临时表（B+Tree索引是有序的，可以用于ORDER BY和GROUP BY操作）。临时表主要是在排序和分组过程中创建，不需要排序和分组，也就不需要创建临时表
将随机I/O变为顺序I/O（B+Tree索引是有序的，会将相邻的数据都存储在一起）
索引的使用条件
对于非常小的表，大部分情况下简单的全表扫描比建立索引更高效
对于中到大型的表，索引就非常高效
但是对于特大型的表，建立和维护索引的代价将会随之增长。这种情况下，需要用到一种技术可以直接区分出需要查询的一组数据，而不是一条记录一条记录地匹配，例如可以使用分区技术
注意
在根据主索引搜索时，直接找到key所在的节点即可取出数据
在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引
因此在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂
参考资料
为什么数据库索引查询会快 - xiaobaxiing - 博客园
深入理解MySQL索引原理和实现——为什么索引可以加速查询？_tongdanping的博客-CSDN博客_mysql 索引是怎么实现的?
