mysql 索引

索引的优点

  减少服务器需要扫描的数据量
  避免排序和临时表
  将随机IO变为顺序IO 

索引分类

    主键索引、唯一索引、普通索引、全文索引、组合索引

索引的数据结构

    B树索引

查找过程
   1、根据根节点照片磁盘块1读入内存（第1次IO）
   2、比较关键29在16~34之间得到指针P2
   3、根据P2指针找到磁盘块3读入内存(第2次IO)
   4、比较关键29在25~31之间得到指针P2
   5、根据P2指针找到磁盘块8读入内存(第3次IO)
   6、在磁盘块8关键字列表中找到关键字29
缺点：每个节点是包含key和data，每个缓存页的空间是有限的这样会导致存储key的数量变小。当存的数据量很大时会导致树的深度较大，增加查询磁盘的IO次数

  B+树索引
     

   B+树索引是对B树索引的优化，非叶子节点存储key叶子节点存key和data，每个节点可以包含更多的key 降低了树的高度。叶子节点之前相互两两链接顺序、范围查询效率更高。

  1、innodb是通过B+树对主键构建索引的，叶子节点中存储数据，如果没有主键会选择唯一键，若果没有唯一键会选择一个6位的row_id作为主键 
  2、如果创建的索引在其他字段上，那么叶子节点中存储的是主键。然后通过主键找到对应的记录，这一过程叫回表。
   

  
hash索引

     只有精确匹配索引所有列的查询才有效，在mysql中，只有memory的存储引擎显式支持哈希索引，哈希索引自身只需存储对应的hash值，所以索引的结构十分紧凑，哈希索引查找的速度非常快。
     缺点：哈希索引数据并不是按照索引值顺序存储的所以无法进行排序。哈希索引是使用索引列的全部内容来计算哈希值所以不支持部分列匹配查找。哈希索引不支持任何范围查询。当出现哈希索引冲突的时候，存储引擎必须遍历链表中的所有行指针，逐行进行比较，直到找到所有符合条件的行，哈希冲突比较多的话，维护的代价也会很高
名词

    回表：根据普通索引查询得到的叶子节点是主键，根据主键查询主键B+树索引得到完整行记录。      覆盖索引：根据普通索引查询只需得到主键列或索引包含查询的所有列
    最左匹配：组合索引查询时根据创建索引顺序从左往右匹配，若查询条件没有最左边第一列则不会使用索引
    索引下推：name like '张%’and age >10 检索，MySQL5.6版本之前，会对匹配的数据进行回表查询。5.6版本后，会先过滤掉age<10的数据，再进行回表查询，减少回表率，提升检索速度(name、age建立的组合索引)

聚簇索引与非聚簇索引

    聚簇索引：是一种数据格式将数据和键值紧凑的存放在一起。优点：数据和索引在同一棵树中访问数据更快、使用覆盖索引可以直接使用叶子节点中的主键值。缺点：聚簇索引列更新的代价高，会强制将每个被更新的行移动到新的位置。
    非聚簇索引：只存储主键值。优点：每个节点可以存储更多的key当索引包含查询中的列时采用索引覆盖不会产生回表效率高。缺点：当查询列中存在索引不包含的列时需要回表降低查询效率。

组合索引

    对a、b、c建立组合索引 ALTER TABLE `table_name` ADD INDEX index_name (`a`, `b`, `c`) 以  下查询使用索引情况

优化细节
   1、当使用索引列进行查询的时候尽量不要使用表达式
   2、尽量使用主键查询，而不是其他索引，因此主键查询不会触发回表查询
   3、使用索引来排序
   4、union all,in,or都能够使用索引，但是推荐使用in
   5、强制类型转换会全表扫描
   6、单索引字段数不允许超过5个（组合索引）
   7、创建索引的列，不允许为null
   8、优化数据访问：查询性能低下的主要原因是访问的数据太多，某些查询不可避免的需要筛选大量的数据，我们可以通过减少访问数据量的方式进行优化  （只返回需要的列、是否有扫描大量的数据行、对多次查询相同数据做查询缓存）
    9、将外连接转化成内连接，内连接的效率要高于外连接(内联获取的数据量要少于外联获取的数据量)
    10、子查询的优化最重要的优化建议是尽可能使用关联查询代替(子查询会产生零时表再和外部表join)