Skip to content

Latest commit

 

History

History
113 lines (73 loc) · 6.87 KB

mysql_index2.md

File metadata and controls

113 lines (73 loc) · 6.87 KB

Mysql索引进阶

  1. 聚簇索引

    Mysql的索引底层数据结构是用B+树，B树是绝对的平衡树，每个节点可以有多个子节点，可以变的更矮胖，而B+树则是更进一步，数据只存放在叶子节点，非叶子节点就可以存放更多的索引，会降低层级更加矮胖，加快搜索速度。

    而聚簇索引采用了一种特定的存储格式，非叶子节点存放索引，而叶子节点会把表中的每行数据都存储起来。目前InnoDB引擎中所有的主键都默认是聚簇索引，如果没有设置主键则隐式定义一个主键来当做聚簇索引，总之一定会有一个聚簇索引。

    1

    • 优点

      因为索引和数据在同一颗B+树上，所以查找起来效率很高，而且叶子节点的数据存放是排过序的，范围查找的效率也很高。

    • 缺点

      更新代价很大，因为排序会导致所有行都移动新的位置。因为数据存放在叶子节点时，实际是存放在页(在InnoDB存储引擎中，页是磁盘上的最小存储单元，默认16K)中，如果一个页放不小数据，就需要分裂成两个页面来存储数据，占用更多的空间。

  2. 非聚簇索引

    也称为二级索引，和聚簇索引不同的是，它只保存了索引本身的字段和行指针(也就是表中每行数据的主键值)，所以每次查询都是先在二级索引中查找到对应的行指针，再用行指针查询聚簇索引中对应存放主键索引和数据的节点，做了两次查询，这种二次查询也就是被称为"回表查询"。

  3. 覆盖索引

    上面说到二级索引会造成回表查询，所以可以使用一种叫做覆盖索引的方法来优化。

    比如table1表中有一个index1索引，里面包含了key1,key2,key3,key4四个字段，而查询的时候只需要key1,key2两个字段，那么只需要查询的时候指定只查询这两个字段就不会做回表查询，因为在索引中已经拿到了字段值，那么就不需要做回表查询了。

    • 优点

      避免了回表查询，提高了查询速度 索引数量一般会比数据数量要少，查询起来也快很多，只查询索引速度肯定会更快

  4. 最左前缀匹配

    在了解最左前缀匹配前需要先了解一下联合索引的概念，其实就是上面举的例子，一个索引里面包含多个字段，比如table1表中有一个index1索引，里面包含了key1,key2,key3,key4四个字段，这就是一个联合索引。

    而建立一个联合索引(key1,key2,key3,key4)，实际上是使用最左前缀匹配规则对应这四个索引(key1)，(key1,key2)，(key1,key2,key3)，(key1,key2,key3,key4)，总结就是先从左到右匹配，只要左边能匹配到索引就有效，右边不匹配没关系，但是左边不匹配，这个索引就不会生效，可以参考官方文档。

      // 下面这五种查询都可以使用到这个索引
  select * from table1 where key1 = 1;
  select * from table1 where key1 = 1 and key2 = 1;
  select * from table1 where key1 = 1 and key2 = 1 and key3 = 1;
  select * from table1 where key1 = 1 and key2 = 1 and key3 = 1 and key4 = 1;
  select * from table1 where key2 = 1 and key1 = 1;

  // 下面这两种就没办法使用到这种索引了
  select * from table1 where key2 = 1;
  select * from table1 where key2 = 1 and key3 = 1;

    • 优点

      因为最左前缀匹配的原因，建立了一个联合索引相当于建立了多个索引，节省了空间，因为索引也是要占据大量空间的 联合索引包含了多个字段，所以有时候查询索引就可以直接满足要查询的字段了，不需要回表查询了，也就是覆盖索引，大大的提高了效率 如果筛选条件有多个的时候联合索引可以直接在索引中过滤掉大量的数据，效率更好，单个索引过滤的数据量会小很多，后面的条件还要在大量数据中继续过滤，性能很低。

      // 假如表里面有100W条数据，假设每个条件都可以筛选出10%，一个条件就是10W
// 如果只有key1有索引，过滤出来的数据量就是10W，再在这10W里面过滤另外两个条件
// 但是如果是联合索引则是100W * 10% * 10% * 10%，筛选出1000条数据
select * from table1 where key1=1 and key2=2 and key3=3

  5. 查询优化器

    SQL语句的查询在底层执行的时候是会有不同的执行方案，需要通过优化器选择执行成本最低的方案，所以有时候明明有建立索引但是却不使用。

    在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案。优化过程大致如下：

    • 根据搜索条件，找出所有可能使用的索引
    • 计算全表扫描的代价
    • 计算使用不同索引执行查询的代价
    • 对比各种执行方案的代价，找出成本最低的那一个

  6. 索引下推(Index Condition Pushdown)

    这是在5.6版本新加入的优化，按官方文档的说法是只适合二级索引，也就是非聚簇索引，并且只适合联合索引。

    • 开启和关闭

      // 查询是开启状态，默认是开启的
SELECT @@optimizer_switch
// 开启
SET optimizer_switch = 'index_condition_pushdown=on';
// 关闭
SET optimizer_switch = 'index_condition_pushdown=off';

    • 优化机制

      前面说到二级索引里面只存储索引的字段，如果需要获取更多的数据就需要回表查询，也就是根据索引里的行指针查询聚簇索引里的数据行。

      假设table表有abc三个字段，只有a字段有设置索引key_a，如果不使用索引下推执行下列语句时，会先通过索引查询出聚簇索引里的所有行数据，再通过where条件过滤这些行数据，而使用索引下推时则是在查询索引时就先使用where条件过滤条不符合的数据，最后符合条件的小部分索引再执行回表，这大大减少了读取的效率，因为索引查询要快很多，提前在这步过滤掉不需要回表的数据会提高很多效率。

      select * from table where a <10;

      再比如下列语句，表中有INDEX (zipcode, lastname, firstname)索引，索引下推会先通过lastname过滤，再过滤zipcode，最终筛选出来的数据会进行回表过滤address。

      SELECT * FROM people
WHERE zipcode='95054'
AND lastname LIKE '%etrunia%'
AND address LIKE '%Main Street%';

      使用explain语句会发现使用索引下推时，Extra列中显示Using index condition而不是Using index。