MySQL索引 - 《MySQL》

索引的原理：

索引是帮助mysql高效获取数据的数据结构，提取句子主干，就可以得到索引的本质：索引是数据结构;
- 目的在于提高查询效率，类比字典，可以进行首字母查询等；
- 索引就是满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向数据），可以在这些数据结构上实现高级查找算法。
通过不断缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序事件，也就是通过同一种查找方式来锁定数据；
索引是物理数据页存储，在数据文件中（InnoDB ibd文件），利用数据页（page）存储。索引可以加快检索速度，但是同时也会降低增删改查操作速度，索引维护需要代价。

相关算法

二分查找法：也叫作折半查询法，是在有序数组中查找指定数据的搜索算法。
- 首先定位left和right两个指针；
- 计算（left+right）/2；
- 判断除以2后索引位置值与目标值的大小对比；
- 索引位置大于目标值就-1，right移动；如果小于目标值就+1，left移动。

索引

B+树索引可以分为聚集索引和辅助索引（非聚集索引），索引时在存储引擎层实现的。索引每个页的大小都为16KB，且不能更改。
InnoDB B+树索引
- 聚集索引：根据主键创建一颗B+树，聚集索引的叶子节点存放了表中的所有记录。
  - 按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据。
- 辅助索引：也叫二级索引，根据索引列创建一颗B+树，但在B+Tree的叶子节点中只存了索引列和主键的信息。二级索引占用的空间会比聚集索引小很多，通常创建辅助索引就是为了提升查询效率。一个表InnoDB只能创建一个聚集索引，但是可以创建多个辅助索引。与聚集索引不同的是，其叶子节点不仅存放索引键值，以及该索引键值指向的主键。
  - 如果通过辅助索引来查找数据，那么当找到辅助索引的叶子节点后，很有可能还需要根据主键值查找聚集索引来得到数据（书签查找）。
  - 如果辅助索引是一个包含主键的联合索引，那么不需要一个额外的列来存放主键值。辅助索引会选择通过联合索引中的主键进行查找。
- 联合索引：对表上的多个列进行索引,联合索引的创建方法与单个索引的创建的方法一样，不同之处仅在于有多个索引列。复合索引可以替代多个单一索引，相比多个单一索引复合索引所需的开销更小。
- 回表查询：InnoDB索引有聚集索引和辅助索引。聚集索引的叶子节点存储行记录，InnoDB必须要有该索引且只有一个。辅助索引的叶子节点存储的是主键值和索引字段值，通过辅助索引无法直接定位行记录，通常这种情况下，需要扫描两遍索引树。先通过辅助索引定位主键值，然后再通过聚集索引定位行记录，这就是回表查询，它的性能比扫一遍索引树低。通过索引查询主键值，然后再去聚集索引查询记录信息。【面试题】
- 覆盖索引：InnoDB存储引擎支持覆盖索引（索引覆盖），即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。使用覆盖索引的好处是辅助索引中就可以得到查询记录，而不需要查询聚集索引中的记录。
  - 只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快，这就叫做索引覆盖。
  - 实现索引覆盖最常见的方法就是：将被查询的字段，建立到组合索引。

索引的高选择性

并不是所有的查询都需要创建索引的，一般都是访问表中的记录数少于全表的15%的行数，索引才有意义。
一般使用SHOW INDEX语句中的Cardinality列来观察；该值表示索引中唯一只记录数量的预估值。
对于查询SELECT FROM TABLE WHERE a=xxx and b=xxx，显然是可以使用（a，b）这个联合索引的。但是对于单个的a列查询SELECT FROM TABLE WHERE a=xxx 也可以使用这个（a，b）索引。但是对于b列的查询SELECT * FROM TABLE WHERE b=xxx，不可以使用这棵B+树索引。
建立合适的索引
- 一个最重要的原则是最左前缀原理，这个涉及联合索引（索引可以以一定的顺序引用多个列，这种索引叫做联合索引）一个联合索引是一个有序元组，其中各个元素均为数据表的一列。索引最左原则就是组合索引中都是按照左边的列开始匹配的，如果没有使用到最左边的列的索引，后面的索引就不会生效；
- 索引并不是越多越好，索引文件本身要消耗存储空间，同时索引会加重插入、删除和修改记录时的负担，mysql在运行时也要消耗资源维护索引。
- 使用InnoDB索引时，一般都是采用自增字段作为主键；
索引的常用技巧：
- 最左前缀匹配原则，mysql会一直向右匹配直到遇到范围查询（>、<、between、like）就会停止匹配；
  - 比如：a=1 and b=2 c > 3 and d=4，如果是建立（a，b，c，d）索引的话d索引就会失效了，如果是建立（a，b，d，c）则索引都会使用得到；
- 索引对于=号和in的匹配是可以乱序的；
- 尽量选择区分度高的列作为索引；
- 索引的列不能参与计算；B+Tree存的都是数据表中的字段值，当进行检索时，需要把所有元素都应用函数才能比较，成本太大了；
- 尽量扩展索引，而不是新建索引（单一索引可以变为组合索引）；
索引失效的情况
- like以%开头，该字段上的索引就会失效，但是以%结尾则不影响索引的使用；
- or语句前后没有同时使用索引，那么索引会失效，只有两边查询的字段均为索引字段时，才会生效；
- 组合索引，如果没有使用到最左列的索引，索引就会失效【不符合最左前缀匹配原则】；
- 数据类型出现隐式转换，如varchar不加单引号就会自动转换成int类型，那么这个字段上的索引就会失效；
- 隐式字符编码转换会使得索引失效；
- 如果在索引字段上使用IS NULL 或者 IS NOT NULL，索引有可能会失效；
- 对于某一列含有NULL值，该列的索引是否有效？
  - 虽然可以在含有NULL的列上使用索引，但NULL和其他数据还是有区别的，不建议列上允许为NULL，最好设置为NOT NULL，并给一个默认值。
- 在索引字段上使用not、<>、!=这种操作符号的时候，索引失效；
- 利用where查询数据，查到的数据记录超过数据表所有记录的15%，索引会失效；
慢查询优化
- explain：可以对SELECT语句进行分析，并输出SELECT执行的详细信息，供开发人员有针对性的优化。
  - select_type：表示查询类型，最常见的查询类型的SIMPLE；
    - SIMPLE:表示查询语句不包含子查询或union；
    - PRIMARY:表示此查询是最外层的查询；
    - UNION:表示此查询是UNION的第二个或后续的查询；
    - DEPENDENT UNION：UNION中的第二个或后续的查询语句，使用了外面查询结果；
    - UNION RESULT ：UNION的结果；
    - SUBQUERY: SELECT子查询语句；
    - DEPENDENT SUBQUERY:SELECT子查询语句依赖外层查询的结果；
  - type：表示存储引擎查询数据时采用的方式。通过此属性可以判断出查询是全表扫描还是基于索引的部分扫描。
    - ALL：表示全表扫描，性能最差；
    - index：表示基于索引的全表扫描，先扫描索引再扫描全表数据。
    - range：表示使用索引范围查询，使用> >= < <= in等；
    - ref：表示使用非唯一索引进行单值查询；
    - eq_ref：一般情况下出现在多表join查询，表示前面表的每一个记录，都只能匹配后面表的一行结果；
    - const：表示使用主键或唯一索引做等值查询，常量查询；
    - NULL：表示不用访问表，速度最快；
  - possible_keys：表示查询时能够使用到的索引，显示的是索引名称；
  - key：表示查询时真正使用到的索引，显示的是索引名称。
  - rows：MySQL查询优化器会根据统计信息，估算SQL要查询到结果需要扫描多少行记录。原则上rows是越少效率越高，可以直观的了解到SQL效率高低。
  - key_len：表示查询使用了索引的字节数量。可以判断是否全部使用了组合索引。
  - Extra：表示很多额外的信息，各种操作会在Extra提示相关信息：
    - Using where：表示查询需要通过索引回表查询数据；
    - Using index：表示查询需要通过索引，索引就可以满足所需数据。
    - Using filesort：表示查询出来的结果需要额外排序，数据量小在内存，大的话在磁盘，因此有Using filesort建议优化；
    - Using temprorary：查询使用到了临时表，一般出现于去重、分组等操作；
- 慢查询定位：
  - 开启慢查询日志查看MYSQL数据库是否开启了慢查询日志和慢查询日志文件的存储位置：show variables like slow_query_log%
  - 开启慢查询日志：

SET global slow_query_log = ON;
SET global slow_query_log_file = ‘OAK-slow.log’;
SET global log_queries_not_using_indexes = ON;
SET long_query_time = 10;

     - long_query_time：指定慢查询的阈值，单位是s；
     - log_queries_using_indexes：表示会记录没有使用索引的查询SQL；
  - 查看慢查询日志：文本方式查看，直接使用文本编辑器打开slow.log日志即可；

慢查询优化：
- 一般依据SQL语句的执行时间判断为慢查询；
- 使用了索引，但是依然进行全表扫描，那么索引就会失去意义；
- 关注于索引是否减少了查询扫描的数据行数，需要考虑索引过滤性，过滤性好，执行速度才会快；
- 提高索引过滤性：索引过滤性与索引字段、表的数据量、表设计结构都有关。
- 慢查询原因：
  - 全表扫描：explain分析type属性all全索引扫描
  - index索引过滤性不好：
    - 靠索引字段选型、数据量和状态、表设计频繁的回表查询开销；
    - 尽量少用select*，使用覆盖索引；
  - 分页优化
    - 利用覆盖索引优化；
    - 利用子查询优化；