MySQL索引理解和应用
索引是什么
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。索引的目的在于提高查询效率，对相关列使用索引是提高SELECT操作性能的最佳途径。在生活中也有非常多的索引案例，比如我们书籍的目录，目录里包含了章节和页码的映射关系，有了目录我们就可以方便快速的定位到我们想要阅读的章节。
索引的种类
MySQL索引主要包含普通索引、唯一索引、主键索引、外键索引、复合索引和全文索引这6大种类。
普通索引
普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件(WHERE column = …)或排序条件(ORDER BY column)中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列(如一个整数类型的数据列)来创建索引。
唯一索引
普通索引允许被索引的数据列包含重复的值。如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。这么做的好处在于：一是简化了MySQL对这个索引的管理工作，这个索引也因此而变得更有效率；二是MySQL会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了；如果是，MySQL将拒绝插入那条新记录。也就是说，唯一索引可以保证数据记录的唯一性。
主键索引
必须为主键字段创建一个索引，这个索引就是所谓的”主键索引”，不允许有空值。主索引与唯一索引的唯一区别是：前者在定义时使用的关键字是PRIMARY而不是UNIQUE。一个表只能有一个主键。
外键索引
如果为某个外键字段定义了一个外键约束条件，MySQL就会定义一个内部索引来帮助自己以最有效率的方式去管理和使用外键约束条件。
复合索引
索引可以覆盖多个数据列，如像INDEX(columnA, columnB)索引。这种索引的特点是MySQL可以有选择地使用一个这样的索引。如果查询操作只需要用到columnA数据列上的一个索引，就可以使用复合索引INDEX(columnA, columnB)。不过，这种用法仅适用于在复合索引中排列在前的数据列组合。比如说，INDEX(A, B, C)可以当做A或(A, B)的索引来使用，但不能当做B、C或(B, C)的索引来使用。
全文索引
文本字段上的普通索引只能加快对出现在字段内容最前面的字符串(也就是字段内容开头的字符)进行检索操作。如果字段里存放的是由几个、甚至是多个单词构成的较大段文字，普通索引就没什么作用了。这类场合正是全文索引(full-text index)可以大显身手的地方。在生成这种类型的索引时，MySQL将把在文本中出现的所有单词创建为一份清单，查询操作将根据这份清单去检索有关的数据记录。
索引的优点
-
大大加快数据的检索速度，这也是创建索引的最主要的原因。 -
在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。 -
创建唯一性索引，保证数据库表中每一行数据的唯一性。 -
加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。 -
通过使用索引，可以在查询的过程中使用隐藏优化器，提高系统的性能。
索引的缺点
-
索引可以加快数据检索操作，但会使数据修改操作变慢。每修改数据记录，索引就必须刷新一次。为了在某种程序上弥补这一缺陷，许多SQL命令都有一个DELAY_KEY_WRITE项。
-
索引还会在硬盘上占用相当大的空间。因此应该只为最经常查询和最经常排序的数据列建立索引。
建索引应该遵循的原则
从上面的索引分类和索引优缺点我们可以很明显的看出，索引能够为我们带来很多好处，但这个好处建立的前提是规范的索引，因此这里提炼一些实践当中非常常用的索引规约。
尽量设置字段的默认值为非null
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。更为重要的是，对于较短的键值，索引高速缓存中的块能容纳更多的键值，因此，MySQL也可以在内存中容纳更多的值。这增加了找到行而不用读取索引中较多块的可能性。
牢记最左前缀匹配原则
在创建一个 n 列的索引时，实际是创建了MySQL可利用的n个索引。多列索引可以起到建立多个索引的作用，因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
选择区分度高的列作为索引
区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0。
索引列不要参与计算
如果在索引列上进行运算查询，这将导致索引失效而进行全表扫描，这也就失去了建立索引的意义了。同理NOT IN、<>、LIKE查询操作也是无法应用到索引的，应该尽量避免使用, LIKE操作比较特殊，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。