【MySQL】如何理解索引？
索引概述
什么是索引？
在数据之外，DB还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用数据，在这个数据结构的基础上可以实现高级查找算法，它们就是索引。索引是帮助MySQL高效获取数据的数据结构。
当数据量很大的时候，索引对性能的影响很大。当数据量很小，负载很低的时候，不恰当的索引可能对性能的影响不明显，但是如果数据量很大，性能会急剧下降。
一般来说索引本身也很大，不可能全部存在内存中，因此索引往往以索引文件的形式存储在磁盘上。如果没有指明，一般索引都是指B树（多路搜索树，不一定是二叉的）结构的索引。其中聚集索引、次要索引、复合索引、前缀索引、唯一索引默认都是使用B+树索引，统称索引。除了B+树索引还有哈希索引等。
索引的工作过程是什么样的？
示例：select * from emp where emp_id=1;如果emp_id有索引，则会使用索引找到emp_id为1的行。也就是MySQL先在索引上按值查找，然后返回所有包含该值的数据行。
优势V.S.劣势
- 优势：是提高检索的效率，降低数据库的IO成本。通过索引对数据进行排列，降低数据排序的成本，降低CPU的消耗。
- 劣势：实际上索引也是一张表，保存了主键和索引字段，并指向实体表的记录，所以索引列也是要占空间的。虽然索引大大提高了查询速度，同时会降低更新表的速度。因为更新表还需要更新索引信息。索引只是提高效率的一个因素，对于大数据量的表，需要花时间研究简历优质的索引，才能实现真正的优化查询。
索引的分类
- 单值索引：一个索引只有一个列，一个表可以有多个单值索引
- 唯一索引：索引列的值必须唯一，但允许有空值
- 复合索引：一个索引有多个列。
基本语法
- 创建1：CREATE [UNIQUE] INDEX indexname ON table(columnname(length));
- 创建2：ALTER table ADD [UNIQUE] INDEX[indexname] ON (columname(length));
- 删除：DROP INDEX [indexname] ON table;
- 查看：SHOW INDEX FROM table
添加索引的四种方式：
- ALTER TABLE tablename ADD PRIMARY KEY(columnname)：添加一个主键，索引值必须唯一，且不能为空。
- ALTER TABLE tablename ADD UNIQUE indexname(columname)：创建唯一索引，可以有多个null值。
- ALTER TABLE tablename ADD INDEX indexname(columnname)：添加普通索引，索引值可以出现多次。
- ALTER TABLE tablename ADD FULLTEXT indexname(columnname)：指定了索引为全文索引。
MySQL的索引结构
- B树索引：B树通常意味着所有的值都是按顺序存储的，且每个叶子页到根的距离相同。B树索引能够加快访问数据的速度，因为存储引擎不需要全表扫描来获取需要的数据，而是从索引的根节点开始搜索。根节点的槽种存放了指向子节点的指针，存储引擎根据这些指针向下查找。通过比较节点页的值和要查找的值来找到适合的指针，进入下层子节点。这些指针定义了子节点页种值的上限和下限，最终存储引擎要么找到了对应的值，要么记录不存在。
- 哈希索引
- 全文索引
- R树索引
什么时候需要创建索引？
- 主键自动建立唯一索引
- 频繁作为查询条件的字段
- 查询种与其他表关联的字段，外键关系建立索引
- 高并发一般倾向于创建组合索引
- 查询中排序的字段（排序字段如果用索引去查会提高排序的速度）
- 查询中统计或者分组的字段。
什么时候不要创建索引？
- 表中记录很少
- 频繁更新的字段不适合创建索引
- Where条件中用不到的字段不创建索引
- 数据重复且分布平均的字段。如果数据列有很多重复项，建立索引没有太大效果
索引的选择性是指索引列中不同值的数与表总数的比。索引的选择性越接近于1，索引的效率越高。
性能分析
MySQL中有专门优化select语句的优化器模块，可以通过计算分析系统收集到的统计信息，为Qurey提供其认为最优的执行计划。
当MySQL得到一条Qurey请求，命令解析器模块会完成请求的分类，区分出是select语句并转发给MySQL Query Optimizer，优化器会先对整条语句进行优化，处理掉常量表达式的预算，换算成常量值，并对查询条件进行简化和转换，去掉一些无用或显而易见的条件，调整结构。然后分析Qurey的Hint信息，看显式Hint信息能否完全确定Quurey的执行计划。如果没有Hint或者信息不足完成，则会读取涉及对象的统计信息，根据Qurey进行相应的计算分析，然后得出最后的执行计划。
MySQL的常见瓶颈：
- CPU：一般在数据装入内存或从磁盘读取的时候可能会发生CPU饱和
- IO：在装入数去大于内存容量的时候会发生磁盘IO瓶颈
- 服务器硬件的性能：可以通过top、free、iostat、vmstat来查看
EXPLAIN关键字
查看执行计划：使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而直到MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。
执行计划包含的信息：上图表头的字段
- id是select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序。id相同表示执行顺序由上到下。如果是子查询，id的序号会递增，id越大的优先执行。
- select_type：查询的类型，主要用于区别普通查询、联合查询、子查询等复杂查询。
- SIMPLE：简单的select查询，不包含子查询或者union
- PRIMARY：查询中如果包含任何复杂的子部分，最外层查询则被标记为PRIMARY
- SUBQUERY：select或where中的子查询
- DERIVED：在from列表中包含的子查询会被标记为DRIVED，MySQL会递归执行这些子查询，把结果放在临时表里。
- UNION：若第二个SELECT出现在UNION之后，则被标记为UNION。若UNION包含在FROM子句的子查询中，外层的SELECT将被标记为DERIVED。
- UNION_RESULT：从UNION表中获取结果的select
- type：显式查询使用了何种类型，从最好到最差：SYSTEM>CONST>EQ_REF>REF>RANGE>INDEX>ALL。一般要保证查询至少达到range级别，最好达到ref级别。
- SYSTEM：表只有一行记录（等于系统表），是CONST类型的特例，平时不会出现
- CONST：表示通过索引一次就找到了，CONST用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如果将主键置于where列表，MySQL就能将该查询转换为一个常量。
- EQ_REF：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描。
- REF：非唯一性索引扫描，返回匹配某个单独值的所有行，本质也是索引访问，返回匹配某个单独值的行，可能找到多个行，属于查找和扫描的混合体。
- RANGE：只检索给风范围的行，使用一个索引来选择行，key列显式使用了哪个索引，一般是where语句中出现了between、<、>、in等查询。只用扫描部分索引。
- INDEX：只遍历索引树，快于ALL，因为索引文件通常比数据文件小，也就是说虽然all和index都是读全表，但是index是从索引中读取，而all是从硬盘读取。
- ALL：遍历全表找到匹配行。
- possible_keys：显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。
- key：实际使用的索引，如果是null，则没有使用索引。如果查询中出现了覆盖索引，则索引只出现在key表中。（查询的字段和索引字段完全匹配）
- key_len：表示索引中使用的字节数，可查询中使用的索引的长度。在不损失精度的情况下，长度越短越好。显示的是最大可能长度而不是实际使用长度。根据表定义计算得到，而不是表内检索出来的。
- ref：显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。
- rows：根据表统计信息及索引选用情况，大致估算出找到所需记录所需读取的行数。
- extra：包含不适合在其他列显示但很重要的信息
- Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，mysql无法利用索引完成的排序操作叫做文件排序【需要优化】
- Using temporary：使用临时表保存中间结果，MySQL在对查询结果排序的时候使用临时表，常见于orderby和groupby。
- Using index：标识select操作使用了覆盖索引（select的数据列只用从索引中就能取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，不必根据索引再次读取数据文件，查询列被所建的索引覆盖），避免访问表的数据行，如果同时出现using where表明索引被用来执行索引键值的查找。
- Using where
- Using index
