MySQL之索引原理

二分查找法
B+Tree
索引分析与优化

索引分类

索引可以提升查询速度，会影响where查询，以及order by排序。

• 从索引存储结构划分：
  • B Tree索引、Hash索引、FULLTEXT全文索引、R Tree索引
• 从应用层次划分：
  • 普通索引、唯一索引、主键索引、复合索引
• 从索引键值类型划分：
  • 主键索引、辅助索引（二级索引）
• 从数据存储和索引键值逻辑关系划分：
  • 聚集索引（聚簇索引）、非聚集索引（非聚簇索引）

---

• 普通索引：
  • 创建方法：
    • 创建索引：create index 索引的名字 on 表名 (字段名);
    • 修改表时创建索引：alter table 表名 add index 索引的名字(字段名);
    • 创建表时创建索引：create table 表名 ([…],index 索引名字(字段名);
    • 查看索引命令：show index from 表名;
    • 删除索引命令：drop index 索引名字 on 表名;
• 唯一索引：
  • 与普通索引类似，但是索引字段的值必须唯一，但允许有空值。再创建或修改表时追加唯一索引，就会自动创建对应的唯一索引。
  • 创建方法：
    • create unique index 索引的名字 on 表名 (字段名);
    • alter table 表名 add unique index 索引的名字(字段名);
    • create table 表名 ([…],unique 索引的名字(字段名);
• 主键索引
  • 特殊的唯一索引，不允许有空值。
  • 创建方法：
    • create table 表名 ([…],primary key(字段名));
    • alter table 表名 add primary key(字段名);
• 复合索引
  • 可以在多个列上建立索引，也叫组合索引。复合索引在数据库操作期间所需的开销更小，可以代替多个单一索引。
  • 复合索引还有两个概念，一是窄索引，二是宽索引。窄索引指的是索引列为1-2列，宽索引为列超过2列，设计复合索引的重要原则是能用窄索引就不要用宽索引，因为窄索引比宽索引开销低，效率高。
  • 创建方法：
    • create index 索引名字 on 表名(字段1，字段2…);
    • alter table 表名 add index 索引名字 (字段1，字段2…);
    • create talbe 表名 ([…]index 索引名字(字段1，字段2…);
  • 使用复合索引时的注意事项：
    • 复合索引字段是有顺序的，在查询使用时按照索引字段的顺序使用。如果顺序不一致，索引不会生效。比如，创建索引是顺序 (name,age) ，使用索引时 (age,name) ,此时索引不会生效。
    • 何时使用复合索引，要根据where条件建立索引，注意不要过多使用索引，过多使用会对更新操作效率有很大影响。
    • 如果表已经建立了 (name,age) 索引，就没有必要单独建立 (name) 索引了；如果现在已经有了 (name) 索引，如果查询需要 (name,age) 索引，可以建立 (name,age) 复合索引，对于查询有一定提高，如果建立了 (name,age) 索引，此时 (name) 可以去掉，如果有一个宽索引不如拆分为几个窄索引。
• 全文索引：
  • 查询操作在数据量较少时，可以使用like进行模糊查询，但是对于大量的文本数据检索，效率很低，此时使用全文索引，查询速度会比like快很多倍。在MySQL5.6版本以前，全文索引只支持MyISAM引擎，从MySQL5.6开始也支持了InnoDB引擎。
  • 创建方法：
    • create fulltext index 索引名字 on 表名 (字段名);
    • alter table 表名 add fulltext 索引名字(字段名);
    • create table 表名 ([…],fulltext key 索引名字(字段名));
  • sql语句和like不同：
    • select * from 表名 where match(字段名) against(‘匹配内容’);
    • 全文索引必须在字符串、文本字段上建立
    • 全文索引字段值必须在最大字符最小字符之间的才会有效。（innodb ：3-84；myisam：4-84）
    • 全文索引字段值要进行切词处理，按 syntax 字符进行切割，比如“我是关羽 啊”，根据空格切割为“我是关羽”和“啊”
    • 全文索引匹配查询，默认是用的是等值匹配，比如“我是关羽”，只会查到“我是关羽”，不会查到“我是关羽羽”，如果需要可以用boolen模式查询“我是关羽*”。更多查询规则可以查看命令：show variables like ‘%ft%’;

---

索引原理

MySQL官方对索引的定义：
Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data. This is much faster than reading every row sequentially.
翻译过来：
索引用于快速查找具有特定列值的行。如果没有索引，MySQL 必须从第一行开始，然后读取整个表以查找相关行。桌子越大，成本越高。如果表有相关列的索引，MySQL 可以快速确定要在数据文件中间查找的位置，而无需查看所有数据。这比顺序读取每一行要快得多。

• 索引是物理数据页存储，在数据文件中（InnoDB，idb文件），利用数据页（page）存储。
• 索引可以加快检索速度，但是同时也会降低增删改的操作速度，索引维护需要代价。

---

• 二分查找法：

  • 二分查找法也叫做折半查找法，它是在有序数组中查找指定数据的搜索算法。它的优点是：等值查询、范围查询性能优秀，缺点是：新增、更新、删除数据维护成本高。
    • 二分查找步骤：

      • 定位low和high两个指针位置

      • 计算（low+Right）/ 2

      • 结果向下取整后，指针位置的值与查找值大小比较

      • 指针位置值大于目标值指针位置就-1；如果小于目标值指针位置就+1

        • 图示：

---

• B+Tree结构：

• • 非叶子节点不存储data数据，只存储索引值，使得能够存储更多的索引值
  • 叶子结点包含了所有的索引值和data数据
  • 叶子结点用指针连接，提高区间的访问速度，不必进行回旋查找

    • 和B树相比，B+树进行范围查找时，只需要查找定位两个节点的索引值，然后利用叶子结点的指针进行遍历即可。

      • 图示：
        

---

• 索引分析与优化

  • EXPLAIN
    
    • select_type：表示查询的类型，常用值如下：
      • SIMPLE：表示查询的语句不包含子查询或UNION（最常见的查询类型）
      • PRIMARY：表示此查询是最外层的查询
      • UNION：表示此查询是UNION的第二个或后续的查询
      • DEPENDENT UNION：UNION中的第二个或后续的查询语句，使用了外面的查询结果
      • UNION RESULT：UNION的结果
      • SUBQUERY：SELECT子查询语句
      • DEPENDENT SUBQUERY：SELECT子查询语句依赖外层查询的结果
    • type：表示存储引擎查询数据时采用的方式。通过它可以判断出查询的是全表扫描还是基于索引的部分扫描。常用值如下：效率依次增强
      • ALL：表示全表扫描，性能最差。
      • index：表示基于索引的全表扫描，先扫描索引再扫描全表数据。-> 基于排序的情况下，效率会比 ALL 高
      • range：表示使用索引范围查询，如：>、>=、<、<=、in 等
      • ref：表示使用非唯一索引进行单值查询，即普通索引
      • eq_ref：一般情况下出现在多表join查询，表示前面的表的每一个记录，都只能匹配后面表的一行结果
      • const：表示使用主键或唯一索引做等值查询->常量查询
      • NULL：表示不用访问表，速度最快
    • possible_keys：表示查询时能够使用到的索引，并不是真正使用
    • key：表示查询时真正使用的索引，显示的是索引名称
    • rows：表示MySQL查询优化器会根据统计信息，估算sql查询到结果需要扫描多少行记录。原则上是rows越少越好，直观反映了sql效率的高低
    • key_len：表示查询使用了索引的字节数量，可以判断是全部使用了组合索引，或只用到索引的最左部分的部分字段值

      • key_len 的计算规则如下：

      • 字符串类型：
        字符串长度跟字符集有关：latin1=1、gbk=2、utf8=3、utf8mb4=4
        char(n): n * 字符集长度

        varchar(n):n * 字符集长度+2字节

      • 数值类型：
        TINYINT=1、SMALLINT=2、MEDIUMINT=3、INT和FLOAT=4、BIGINT和DOUBLE=8

      • 时间类型：
        DATE：3、TIMESTAMP=4、DATETIME=8

      • 字段属性：
        NULL属性占用1个字节，如果一个字段设置了NOT NULL，则没有此项

    • Extra：表示很多额外的信息，各种操作会在Extra提示相关信息，常见几种如下：
      • Using where
        表示查询需要通过索引回表查询数据。
      • Using index
        表示查询需要通过索引，索引就可以满足所需数据。
      • Using filesort
        表示查询出来的结果需要额外排序，数据量小在内存，大的话在磁盘，因此有Using filesort建议优化，对CPU消耗较大
      • Using temprorary
        表示查询使用到了临时表空间，一般出现于去重、分组等操作

---

• 回表查询
  • 通过索引查询主键值，然后再去聚合索引查询记录信息

---

• 覆盖索引
  • 只需要在一棵索引树上就能获取sql所需的所有数据，无需回表，速度更快。

---

• 最左前缀原则

  • 复合索引使用时遵循最左前缀原则，即最左优先，比如 name和gender 为组合索引，那么查询中使用到了最左边的name就会使用索引，如果仅使用了第二个，那么索引就会失效。如图↓↓↓↓↓

  

  

  • 图1：查询所有数据
  • 图2：查询现在索引
  • 图3：创建符合索引 name——gender
  • 图4：遵循了最左前缀原则
  • 图5：未遵循最左前缀原则
  • 图6：遵循了最左前缀原则
  • 图7：遵循了最左前缀原则，MySQL优化器进行了优化，即对条件顺序进行了优化

---

• LIKE查询
  • %写在后面会起作用(最佳左前缀法则)
    select * from employee where name ‘关羽%’;
    通过 show variables like ‘%optimizer_switch%’; 查看，index_condition_pushdown=on，简称ICP，表示开启，索引条件过滤下沉到存储引擎层。

---

• NULL查询
  • 对MySQL来讲，NULL是特殊的值。比如：不能使用=，<，>这样的运算符，对NULL做算数运算的结果都为NULL，count是不会计算值为NULL的行。

---

其他章节 -> 跳转

end...