MySQL存储引擎、索引及SQL优化

MySQL存储引擎

        在MySQL中创建表时可以选择存储引擎。有几种不同的存储引擎，但最常用的是MyISAM和InnoDB，它们都是不同MySQL版本的默认存储引擎

        如果在创建表时没有指定存储引擎，那么将使用MySQL版本的默认引擎

        在5.5.5之前的MySQL版本中，MyISAM是默认值，但是在5.5.5之后的版本中，InnoDB是默认值。

    区别：

• InnoDB较新，MyISAM较老
• InnoDB更复杂，而MyISAM更简单
• InnoDB在数据完整性方面更加严格，而MyISAM比较松散
• InnoDB为插入和更新实现了行级锁，而MyISAM实现了表级锁
• InnoDB有事务，而MyISAM没有
• InnoDB有外键和关系限制，而MyISAM没有
• InnoDB有更好的崩溃恢复，而MyISAM在系统崩溃时无法恢复数据完整性
• MyISAM有全文搜索索引，而InnoDB没有
• InnoDB采用聚簇索引，MyISAM采用非聚簇索引

总结：

        MyISAM结构相对简单，查询效率更高，支持全文索引，InnoDB不支持全文索引且查询效率没有MyISAM高；但InnoDB支持事务，行锁，外键。因此在InnoDB在写密集型(插入、更新)表中更快，因为它利用行级锁，并且只保留对正在插入或更新的同一行的更改，而MyISAM适合存储数据多、查询多的场景。

数据库索引

        索引就是对数据库表中一个或多个列的值进行排序的结构，帮助快速获取数据。索引在数据库中的作用类似于目录在书籍中的作用，用来快速提高查找信息的速度。

        MySQL中索引有很多种，我们经常能听到一会儿又什么唯一索引、主键索引，一会儿又什么hash索引、B+树索引，那么到底都是什么关系呢。

        首先，这是按照不同角度来进行划分的。按照逻辑角度来进行划分，MySQL中索引主要包括普通索引、唯一索引、主键索引、组合索引和全文索引等。按照数据结构角度来进行划分，有可以分为hash索引、B+树索引等，从物理存储角度来划分，又可以划分为聚集索引和非聚集索引。

hash索引

        哈希索引（hash index）基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。对于hash冲突的，采用链表的方式解决冲突。

        哈希索引单条记录查询的效率很高，时间复杂度为1。但Hash索引并不是最常用的数据库索引类型，比如Mysql的Innodb引擎就不支持hash索引。

Hash索引弊端

           Hash索引仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询。哈希索引只支持等值比较查询，包括＝、 IN 、<=>  (注意<>和＜＝＞是不同的操作）。 也不支持任何范围查询，例如WHERE price > 100。由于Hash索引比较的是进行Hash运算之后的Hash值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的Hash算法处理之后的Hash值的大小关系，并不能保证和Hash运算前完全一样。

           Hash索引无法用来数据的排序操作。由于Hash索引中存放的是经过Hash计算之后的Hash值，而且Hash值的大小关系并不一定和Hash运算前的键值完全一样，所以数据库无法利用索引进行数据任何排序运算;

           Hash索引不能利用部分索引键查询。对于组合索引，Hash索引在计算Hash值的时候是组合索引键合并后再一起计算Hash值，而不是单独计算Hash值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash索引也无法被利用。

           Hash索引在任何时候都不能避免表扫描。前面已经知道，Hash索引是将索引键通过Hash运算之后，将 Hash运算结果的Hash值和所对应的行指针信息存放于一个Hash表中，由于不同索引键存在相同Hash值，所以即使取满足某个Hash键值的数据的记录条数，也无法从Hash索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。

           Hash索引遇到大量Hash值相等的情况后性能并不一定就会比BTree索引高。对于选择性比较低的索引键，如果创建Hash索引，那么将会存在大量记录指针信息存于同一个Hash值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。

B+树索引

        B+树索引是MySQL的默认索引的数据结构，那么MySQL为什么选择B+树做索引数据结构呢？ 

         B+树的磁盘读写代价更低：B+树的内部节点（非叶子节点）的关键字（键值对）不保存数据信息（data），只保存索引值（key），因此每个关键字相对B树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，树就能更加矮胖一些，相对IO读写次数就降低了。

        B+树的查询效率更加稳定：非叶子节点不保存data信息，所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

        B+树更便于遍历：由于B+树的数据都存储在叶子结点中，非叶子结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引。

        B+树更适合基于范围的查询：B+树所有叶子节点构成了一个有序表，在查询大小区间的数据更方便，并且数据紧密性高，缓存的命中率也会比B树高。

聚簇索引和非聚簇索引

         MySQL的InnoDB索引数据结构说是B+树，主键索引叶子节点的值存储的就是MySQL的数据行，普通索引的叶子节点的值存储的是主键值，这是了解聚簇索引和非聚簇索引的前提。

聚簇索引

         聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称之为数据页。

        一般建表会有一个自增主键做聚簇索引，没有的话MySQL会 默认创建。在一张表上最多只能创建一个聚集索引，因为真实数据的物理顺序只能有一种。

        我们在平时在使用过程中创建的非主键索引都是辅助索引（非聚簇索引），辅助索引就是一个为了寻找主键索引的二级索引，先找到主键索引再通过主键索引站到数据。

优点：

        1.数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快。

        2.聚簇索引对于主键的排序查找和范围查找速度非常快。

缺点：

        1. 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键。

为什么会出现页分裂：

              这是因为聚簇索引采用的是B+树，而且每个节点都保存了该主键所对应行的数据，假设插入数据的主键是自增长的，那么根据二叉树算法会很快的把该数据添加到B+树中（叶子节点的末尾），而其他的节点不用动；但是如果插入的是不规则的数据，插入新记录时数据文件为了维持B+树的特性而频繁的分裂调整。

        2.更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。

        3.二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。

        聚簇索引的主键值，应尽量是连续增长的值，而不是要是随机值，不要用随机字符串或UUID，否则会造成大量的页分裂与页移动。

非聚簇索引（辅助索引）

        在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的记录数据信息，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的Page Directory找到数据行。

        Innodb辅助索引的叶子节点并不包含行记录的全部数据，叶子节点除了包含键值外，还包含了相应行数据的聚簇索引键。

　　辅助索引的存在不影响数据在聚簇索引中的组织，所以一张表可以有多个辅助索引。在innodb中有时也称辅助索引为二级索引。

回表：

        根据非主键索引查询到的结果并没有查找的字段值，此时就需要再次根据主键从聚簇索引的根节点开始查找，这样再次查找得到记录的过程叫回表。也就是需要进行二次查询。

        假如，一张covering_index_sample表中有id（主键），key1，key2，key3这4个字段，有一个普通索引 （非主键索引）idx_key1_key2(key1,key2)，这时select * from covering_index_sample where key1 = ‘keytest’，因为你虽然通过key1定位到了该条记录，但是key1不是聚簇索引，此时的 B+ 树的数据页中存放的仅仅是自己关联的索引和主键索引字段（id值），因此此时并不能获取到其他值，所以需要进行回表，通过这条记录的id值从维护id那个B+树上查找（聚簇索引），这样才能查出来完整的值。

非主键索引一定会回表查询多次吗？

        覆盖索引也可以只查询一次，覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。

        比如，表covering_index_sample中有一个普通索引 idx_key1_key2(key1,key2)。当通过SQL语句：select key2 from covering_index_sample where key1 = ‘keytest’;的时候，就可以通过覆盖索引查询，无需回表。

InnoDB索引实现

        InnoDB使用B+树作为索引结构。

        （1）主键索引

        MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

         上图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚簇索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。       

        （2）辅助索引

        InnoDB的所有辅助索引都引用主键作为data域。例如，下图为定义在Col3上的一个辅助索引：

         InnoDB 表是基于聚簇索引建立的。因此InnoDB 的索引能提供一种非常快速的主键查找性能。不过，它的辅助索引（Secondary Index， 也就是非主键索引）也会包含主键列，所以，如果主键定义的比较大，其他索引也将很大。如果想在表上定义 、很多索引，则争取尽量把主键定义得小一些。InnoDB 不会压缩索引。

索引失效

        1.查询条件中有or，且or条件中有列不是索引。因此如果要使用or，又想让索引有效，就需要将or条件上中的每列都加上索引。

         2.like查询是已%开头。like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。

         3.如果列类型是字符串，那一定要在查询条件中将数据使用引号引用起来，否则不会走索引。因为会进行类型转换，如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。        

         4.如果MySQL估计使用全表扫码比使用索引要快，则不使用索引。

        5.使用not, <>,!=符号。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。优化方法：key<>0 改为 key>0 or key<0。

        6.范围索引的右边索引会失效。

        7.在索引上做任何操作。对索引字段进行如计算、函数、（自动or手动）类型转换等操作，会导致索引失效从而全表扫描：
        explain select * from user where left(name,5) = 'zhangsan' and age = 20 and phone = '18730658760'。

        8.违反最左前缀原则。如果索引有多列，要遵守最左前缀法则：即查询从索引的最左前列开始并且不跳过索引中的列。什么意思呢？意思就是：组合索引，不是使用第一列索引时或者使用第一列索引但是调列了，索引失效。

        9.使用NULL进行判断。会导致数据库引擎放弃索引进行全表扫描。

SQL优化

        1.查询不要使用select *，需要哪些字段必须明确写明

        老生常谈的问题：为什么查询不要使用select *，需要哪些字段必须明确写明。

        （1）使用 * 查询，会查出多个我们不需要的字段，增加sql的执行时间，同时大量的多余字段，会增加网络开销。

        （2）使用 * 时，数据库会先去查自己的数据字典，明确 * 代表什么，这会在分析阶段造成大量开销。

        （3）select * 杜绝了索引覆盖的可能性，而基于MySQL优化器的“覆盖索引”策略又是速度极快，效率极高的。

         2.查询时走索引，尽量避免出现索引失效，进行全表扫描的情况。首先应考虑在 where 及 order by 涉及的列上建立索引。

        3.Update语句，如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志。

        4.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个。

        5.

参考文档：

        聚簇索引和非聚簇索引(通俗易懂 言简意赅) - 创天创世纪 - 博客园

        一分钟明白MySQL聚簇索引和非聚簇索引 - 阿伟~ - 博客园

        数据库SQL优化大总结之 百万级数据库优化方案 - 雲霏霏 - 博客园