什么是索引
索引用来快速地寻找那些具有特定值的记录，所欲mysql索引都以B-树的形式保存的。如果没有索引，执行查询时mysql必须从一个记录开始扫描整个表的所有记录，直至找到符合要求的记录。表里面的记录数量越多，这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引，mysql无需扫描任何记录即可迅速得到自己目标记录所在的位置。如果表中有1000个记录，通过索引查找记录至少比顺序扫描记录快100倍。
索引的分类
主键索引
数据库表经常有一列或多列组合，其值唯一标识表中的每一行。该列称为标的主键。在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对书籍的快速访问。
唯一索引
唯一索引是不允许其中任何两行具有相同索引值的索引。当现有数据中存在重复的键值时，大多数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。
全文索引
mysql中的全文搜因是FultLeXT类型的索引，只能用于InnoDB和MyISAM表，只能为CHAR,VARCHAR,TEXT列创建。
普通索引
普通索引的唯一任务是加快数据的访问速度。因此，应该只为那些最经常出现在查询条件或排序条件中的数据列创建索引。只要有可能，就应该选择一个数据最整齐，最紧凑的数据列来创建索引。
空间索引
空间索引介于空间操作算法和空间对象之间，它通过筛选作用，大量与特定空间操作无关的空间对象被排除，从而提高空间操作的速度和效率。
索引的代价
1.占用磁盘空间；
2.对DML语句的效率影响；
3.增删会对索引影响，因为索引要重新整理；
索引的优点
1.通过创建索引，可以在查询数据的过程中，提高系统的性能；
2.通过创建唯一索引，可以保证数据表中每一行数据的唯一性；
3.在使用分组和排序子句进行数据检索时，可以减少查询中分组和排序的时间；
索引的缺点
1.创建索引和维护要耗费时间，而且时间随着数据量的增加而增大；
2.索引需要占用物理空间，如果要建立聚簇索引，所需要的空间会更大；
3.在对表中的数据进行增加删除和修改时需要耗费较多的时间，因为索引也要动态维护；
在哪些列上可以创建索引
1.经常需要搜索的列；
2.作为主键的列上；
3.经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
4.经常需要根据范围进行搜索的列上；
5.经常需要排序的列上；
6.经常使用在where子句上面的列上；
不应该在哪些列上创建索引
1.查询中很少用到的列；
2.对于那些具有特定数据的列，比如性别；
3.对于那些数据量相当大的列；
4.当对修改性能的要求远远大于搜索性能时，因为当增加索引时，会提高搜索性能，但是会降低修改性能；
索引的实现原理
数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询，更新数据表中的数据。索引的实现通常使用B树机器变种B+树。
在数据之外，数据库系统还维护者满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。
上图展示了一种可能的索引方式。左边是数据表，一共有两列七条数据，最左边的数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快Col2的查找，可以维护一个右边所示的查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在0（log2n）的复杂度内获取到相应数据。
1）B 树
B 树中每个节点包含了键值和键值对于的数据对象存放地址指针，所以成功搜索一个对象可以不用到达树的叶节点。
成功搜索包括节点内搜索和沿某一路径的搜索，成功搜索时间取决于关键码所在的层次以及节点内关键码的数量。
在 B 树中查找给定关键字的方法是：首先把根结点取来，在根结点所包含的关键字 K1,…,kj 查找给定的关键字（可用顺序查找或二分查找法），若找到等于给定值的关键字，则查找成功；否则，一定可以确定要查的关键字在某个 Ki 或 Ki+1 之间，于是取 Pi 所指的下一层索引节点块继续查找，直到找到，或指针 Pi 为空时查找失败。
2）B+ 树
B+ 树非叶节点中存放的关键码并不指示数据对象的地址指针，非也节点只是索引部分。所有的叶节点在同一层上，包含了全部关键码和相应数据对象的存放地址指针，且叶节点按关键码从小到大顺序链接。如果实际数据对象按加入的顺序存储而不是按关键码次数存储的话，叶节点的索引必须是稠密索引，若实际数据存储按关键码次序存放的话，叶节点索引时稀疏索引。
B+ 树有 2 个头指针，一个是树的根节点，一个是最小关键码的叶节点。
所以 B+ 树有两种搜索方法：
一种是按叶节点自己拉起的链表顺序搜索。
一种是从根节点开始搜索，和 B 树类似，不过如果非叶节点的关键码等于给定值，搜索并不停止，而是继续沿右指针，一直查到叶节点上的关键码。所以无论搜索是否成功，都将走完树的所有层。
B+ 树中，数据对象的插入和删除仅在叶节点上进行。
这两种处理索引的数据结构的不同之处：
a，B 树中同一键值不会出现多次，并且它有可能出现在叶结点，也有可能出现在非叶结点中。而 B+ 树的键一定会出现在叶结点中，并且有可能在非叶结点中也有可能重复出现，以维持 B+ 树的平衡。
b，因为 B 树键位置不定，且在整个树结构中只出现一次，虽然可以节省存储空间，但使得在插入、删除操作复杂度明显增加。B+ 树相比来说是一种较好的折中。
c，B 树的查询效率与键在树中的位置有关，最大时间复杂度与 B+ 树相同(在叶结点的时候)，最小时间复杂度为 1(在根结点的时候)。而 B+ 树的时候复杂度对某建成的树是固定的。可以扫描2的次方。
创建索引
1.创建普通索引
CREATE INDEX accountname ON accounts(accountname)
【create index 索引名称 on 表名(列名)】
如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。
2.创建唯一索引
CREATE UNIQUE INDEX indexNames ON accounts(balance)
【create UNIQUE index 索引名称 on 表名（列名）】
3.创建索引实现提高查询速度
3.1 创建一张表（数据要多）
3.2 在没有添加索引的情况下查询数据
SELECT * FROM emp WHERE empno='100002'
3.3 添加索引
ALTER TABLE emp ADD INDEX (empno)
3.4 添加索引后再次查询数据
数据查询缓存和缓冲区
1.默认query_cache是开启的，如果查询缓存开启了，第一次查询数据的时候，读取的是数据文件，第二次执行查询缓存，所以第二次查询的速度快；
SHOW GLOBAL VARIABLES LIKE '%query_cache%'
2.如果表使用的是InnoDB，第一次查询会走数据文件，第二次查询会走buffer_pool；
SHOW VARIABLES LIKE '%storage_engine%'
3.存储的引擎不同，索引生成的文件也不同
myisam生成三个文件
frm：表结构文件
MYD：数据文件
MYI：索引文件
InnDB生成二个文件
frm：表结构文件
ibd：索引+数据文件
MySQL explain执行计划解读
1.查询一个没有创建索引的列
EXPLAIN SELECT * FROM emp WHERE ename='dejKYN'
2.查询一个创建索引的列
EXPLAIN SELECT * FROM emp WHERE empno='100002'
EXPLAIN列的解释
table：
显示这一行数据是关于那张表的
type：
这是重要的列，显示连接使用了何种类型，从最好到最差的连接类型为：const，eq_reg，ref，range，indexhe，ALL
const：表中的一个记录的最大值能够匹配这个查询（索引可以是主键或唯一索引）。因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待；
eq_reg：在连接中，mysql在查询的时候，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或唯一键的全部时使用；
ref：这个连接类型之后在查询使用了不是唯一或主键的键或者这些类型的部分时发生，对于之前的表每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少，越少越好；
range：这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况；
index：这个连接类型对前面的表中的每一个记录联合进行完全扫描；
ALL：这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免；
possible_keys :
显示可能应用在这张表中的索引，如果为空，没有可能的索引。可以为相关的域从where语句中选择一个合适的语句；
key：
实际使用索引，如果为NULL，则没有使用索引。很少情况下，mysql会选择优化不足的索引，这种情况下，可以在select语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX来强制mysql忽略索引；
key_len：
使用的索引的长度，在不损失精确性的情况下，长度越短越好；
ref：
显示索引的那一列被使用了，如果可能的话，是一个常数；
rows：
mysql认为必须检查的用来返回请求数据的行数；
Extra：
关于mysql如何解析查询的额外消息；