MySQL索引

1. 什么事索引
   索引(Index)是帮助MySQL高效获取数据的数据结构，可以得到索引的本质：索引是数据结构

索引的目的在于提高查询效率，可以类比字典。

如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从上往下找到y字母，在找到剩下的sql。如果没有索引，那么你可能需要a----z，如果我想找到Java开头的单词呢？或者Oracle开偶的单词呢？是不是觉得如果没有索引，这个事情根本无法完成？

你可以简单理解为“排好序的快速查找的数据结构”。

索引会影响WHERE和ORDER BY

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图就是一种可能的索引方式实例:

为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。

一般来锁索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在硬盘上

我们平常所说的索引，如果没有特别指明，都是指B数（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+数索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈稀索引（hash index）等。

2. 优势

   • 类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本
   • 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

3. 劣势

   • 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表示，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
   • 索引只是提高效率的一个因素，如果你的MySQL有大数据的表，就需要花时间研究建立最优秀的索引，或者优化查询语句

4. 索引分类

   1. 单值索引：即一个索引只包含当个列，一个表可以有多个单列索引
   2. 唯一索引：索引列的值必须唯一，但允许有空值
   3. 复合索引：即一个索引包含多个类

5. 基本语法

   1. 创建：
      CREATE [NUIQUE] INDEX indexName ON mytabel(columnname(length))
      ALTER mytabel ADD [UNIQUE] INDEX [indexName] ON (columnname(length))
      2.删除:
      DROP INDEX [indexName] ON mytable
      3.查看:
      SHOW INDEX FROM table_name\G

6. 索引结构

   1. BTREE索引：3层的B+数可以表示上百万的数据,如果上百万的数据查找只需要3次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发送一次IO，那么总共需要百万次的IO，显然成本非常非常高。
      2.HASH索引
      3.full-text全文索引
      4.R-Tree索引

7. 那些情况需要创建索引

   1. 主键自动建立唯一索引
   2. 频繁作为查询条件的字段应该创建索引
   3. 查询中与其它表关联的字段，外键关系建立索引
   4. 频繁更新的字段不适合创建索引，因为每次更新不单单更新了记录还会更新索引
   5. where条件里用不到的字段不要创建索引
   6. 单键、组合索引的选择（在高并发下倾向创建组合索引）
   7. 查询中排序的字段，排序字段若通过索引区访问将大大提高排序速度
   8. 查询中统计或者分组字段

8. 那些情况不要创建索引

   1. 表记录太少
   2. 经常增删改的表
   3. 注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果

9. 性能分析
   MySQL Query OPtimizer

10. 主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计划

11. 当客户端向MySQL请求一条Query，命令解析器模块完成请求分类，区别出是SELECT并转发给MySQL Query Optimizer时，MySQL Query Optimizer 首先会对整条Query进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对Query中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析Query中的Hint信息（如果有），看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint或Hint信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行写相应的计算分析，然后再得出最后的执行计划。

MySQL常见瓶颈

1. CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
2. IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
3. 服务器硬件的性能瓶颈：top，free，iostat和vmstat来查看系统的性能状态

Explain
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，分析你的查询语句或是表结构的性能瓶颈

1. 表的读取顺序
2. 数据读取操作的操作类型
3. 那些索引可以使用
4. 那些索引被实际使用
5. 表之间的引用
6. 每张表有多少行被优化器查询

怎么用: explain + SQL语句