外贸网站建设怎么制作,做宣传图片的网站,海南网络科技有限公司,做明星粉丝网站10 索引
10.1 数据页存储结构
10.1.1 数据页的各个部分 在讲索引之前#xff0c;让我们看看一个单独的数据页是什么样子的 去除掉一些我们不太需要那么关注的部分后#xff0c;简化如下#xff1a; 也就是说平时我们在一个表里插入的一行一行的数据会存储在数据页里#… 10 索引
10.1 数据页存储结构
10.1.1 数据页的各个部分 在讲索引之前让我们看看一个单独的数据页是什么样子的 去除掉一些我们不太需要那么关注的部分后简化如下 也就是说平时我们在一个表里插入的一行一行的数据会存储在数据页里然后数据页里的每一行数据都会按照主键大小进行排序存储同时每一行数据都有指针指向下一行数据的位置组成单向链表。 10.1.2 页分裂
随着业务的发生我们的数据页一般会越来越大当大到一定程度的时候就需要再搞一个数据页了如下图所示 但是这一步骤并不是说简简单单多加一个数据页就 OK还需要保证新加的数据页中的每一行数据的主键值都要比前面的大才行所以数据行有可能会在数据页中挪动。具体如下图所示 10.2 索引页存储结构
10.2.1 概念
上一节我们讲了数据页的存储结构这一节我们继续学习索引页的存储结构。 我们先思考一个问题如果我们只有一般的数据页咱们怎么找到自己想要的数据呢是不是要将数据页全部遍历再在每一个页中通过二分查找查询数据。这么做实在是太慢了所以 MySQL 抽象出了一个索引页的概念它和一般的数据页差不多只不过存放的是最小主键值和页号。然后后续你查询主键值就可以在目录里二分查找直接定位到那条数据所属的数据页接着到数据页里二分查找定位那条数据就可以了如下图所示 但是随着数据页越来越多索引页也变得越来越多这个时候怎么办呢这个时候 MySQL 会抽象出一个更高层级的索引页它里面记录的是最小主键值和索引页号。 那么现在问题再次来了假如你最顶层的那个索引页里存放的下层索引页的页号也太多了怎么办呢此时可以再次分裂再加一层索引最后不断的向上加索引页看起来就像下面这个样子了也就是一颗 B树。 10.2.1 例子
最后我们以最简单最基础的主键索引来举例当你为一个表的主键建立起来索引之后其实这个主键的索引就是一颗 B树然后当你要根据主键来查数据的时候直接就是从 B树的顶层开始二分查找一层一层往下定位最终一直定位到一个数据页里在数据页内部的目录里二分查找找到那条数据。 10.3 聚簇索引
10.3.1 特点
我们上面介绍的B树索引它有两个特点 使用记录主键值的大小进行记录和页的排序 B树的叶子节点存储的是完整的用户记录 符合这两个特点的索引就是聚簇索引。在 InnoDB 存储引擎中聚簇索引就是数据的存储方式InnoDB 存储引擎会自动的为我们创建聚簇索引。
10.4 二级索引
10.4.1 概念
聚簇索引使用记录主键值的大小进行记录和页的排序他是和主键强关联的。但是如果查询的条件不是主键而是其他列呢这个时候就要请出咱们的二级索引了。 二级索引也是一颗B树但是它的数据页里存放的是主键目标字段值。换句话说将聚簇索引中的主键值替换成目标值段且叶子节点仅存储主键目标字段值这两个列的值那么他就是二级索引了。 当你要根据目标字段来查数据的时候直接就是从 B树的顶层开始二分查找一层一层往下定位最终一直定位到一个数据页里在数据页内部的目录里二分查找找到那条数据。但是这条数据只有主键目标字段值。 10.4.2 图解 10.4.3 回表
如果你的查询结果中还需要有其他值那么你得再根据主键在聚簇索引这个B树中再查找一次得到最终的结果这个过程叫做回表。 10.5 联合索引
联合索引也是一颗B树但是它的数据页里存放的是主键多个目标字段值。其他和二级索引类似。 10.6 覆盖索引与回表查询
10.6.1 概念
首先我们要明确一点覆盖索引并不是真正的索引他其实是一种基于索引的查询方式。
不管是二级索引还是联合索引如果你的查询结果中没有其他值只有索引值那么你得不需要再在聚簇索引这个B树中再查找一次只需要扫描当前索引的叶子节点就能得到结果这种就叫做覆盖索引。
10.6.2 例子 select xx1,xx2,xx3 from table order by xx1,xx2,xx3 基于xx1,xx2,xx3建立联合索引
这种情况下你仅仅需要联合索引里的几个字段的值那么其实就只要扫描联合索引的索引树就可以了不需要回表去聚簇索引里找其他字段了。
10.7 如何更好的建立索引
通过前文我们知道了索引其实是一颗一颗的B树那么接下来我们介绍一下B树索引适用的条件。
10.7.1 索引适用的条件
首先我们给出一张示例表如下 CREATE TABLE user ( id int(11) NOT NULL AUTO_INCREMENT COMMENT 主键, name varchar(32) NOT NULL COMMENT 用户名, age int(3) NOT NULL COMMENT 年龄, PRIMARY KEY (id), KEY index_name_age (name,age)) ENGINEInnoDB DEFAULT CHARSETutf8 COMMENTuser示例表; 这个例子中有两个索引一个是根据id排序的聚簇索引另一个是跟据name和age排序的二级索引。 注意二级索引是先跟据name排序如果name相同再根据age排序的。
10.7.1.1 全值匹配
例子 SELECT * FROM user WHERE name 第七人格 AND age 29;
解析 因为B树的数据页和记录先是按照name列的值进行排序的所以先可以很快定位name列的值是“第七人格”的记录位置。 在name列相同的记录里又是按照age列的值进行排序的所以在name列的值是“第七人格”的记录里又可以快速定位age列的值是’29’的记录。
101.7.1.2 匹配左边的列
【例子】 SELECT * FROM user WHERE name 第七人格;
解析 因为B树的数据页和记录先是按照name列的值进行排序的所以先可以很快定位name列的值是“第七人格”的记录位置。
【反例】 SELECT * FROM user WHERE age 29;
解析 因为B树的数据页和记录先是按照name列的值进行排序的所以先可以很快定位name列的值是“第七人格”的记录位置但是现在用age去找你想想能找到吗当然找不到了你都没找到第一层排序的name值怎么能找到下层的age呢 10.7.1.3 匹配列前缀
【例子】 SELECT * FROM user WHERE name like 第七%;
解析 因为B树的数据页和记录先是按照name列的值进行排序的那么的值是按照字符串排序的字符串本质是按照字符排序的这个例子中“第七”是被排好序了的也可以很快定位name列的值是“第七…”的记录位置。
【反例】 SELECT * FROM user WHERE name like %第七%;
或者 SELECT * FROM user WHERE name like %第七;
解析 “第七”并没有排好序所以无法使用索引。
10.7.1.4 匹配范围值
【例子】 SELECT * FROM user WHERE name Anna AND name Ziad;
解析 name能用到索引。
【例子】 SELECT * FROM user WHERE name Anna AND age 35;
解析 name能用到索引age不能。
【思考】 SELECT * FROM user WHERE name Anna AND age 35;
请读者思考上面可以使用到索引吗为什么 10.7.1.5 排序
【例子】 SELECT * FROM user ORDER BY name,age LIMIT 1;
解析 这个是可以用到索引的因为他是按照联合索引的字段顺序去进行order by排序的这样就可以直接利用联合索引树里的数据有序性到索引树里直接按照字段值的顺序去获取数据。
【反例】 SELECT * FROM user ORDER BY name ASC,age DESC;
解析 既有升序又有降序没办法使用索引。
10.7.1.6 分组
【例子】 SELECT name,age FROM user GROUP BY name , age;
解析 这个是可以用到索引的原因可以类比排序。 10.7.2 阿里巴巴索引规约 【强制】业务上具有唯一特性的字段即使是组合字段也必须建成唯一索引。 【强制】超过三个表禁止 join。需要 join 的字段数据类型保持绝对一致多表关联查询时 保证被关联的字段需要有索引。 【强制】在 varchar 字段上建立索引时必须指定索引长度没必要对全字段建立索引根据 实际文本区分度决定索引长度。 【强制】页面搜索严禁左模糊或者全模糊如果需要请走搜索引擎来解决。 【推荐】如果有 order by 的场景请注意利用索引的有序性。order by 最后的字段是组合索引的一部分并且放在索引组合顺序的最后避免出现 file_sort 的情况影响查询性能。 【推荐】利用覆盖索引来进行查询操作避免回表。 【推荐】利用延迟关联或者子查询优化超多分页场景。 【推荐】建组合索引的时候区分度最高的在最左边。 以上规约摘自阿里巴巴开发手册通过前面的学习我相信大家可以从更深层次理解这些规约了而不是一味的死记硬背。 10.8 执行计划与性能优化浅谈
10.8.1 总览
在生产中我们判断一个SQL写的好不好一般都是通过EXPLAIN关键字看他的执行计划的。
首先我们看看执行计划长什么样子我们执行以下SQL EXPLAIN SELECT * FROM user WHERE name 第七人格 AND age 29;
得到的执行计划如下 10.8.2 要素
10.8.2.1 id
这个字段对性能优化来说不太重要我们只需要知道在一个大的查询语句中每个SELECT关键字都对应一个唯一的id 就行了。 10.8.2.2 select_type
SELECT关键字对应的那个查询的类型也不重要。 10.8.2.3 table
表名意思就是查的哪个表也不是很重要。 10.8.2.4 partitions
匹配的分区信息我们接触不到这个知识点99.9%都是null。 10.8.2.5 type
针对单表的访问方法这个就非常重要了。 完整的类型如下systemconsteq_refreffulltextref_or_nullindex_mergeunique_subqueryindex_subqueryrangeindexALL 。 这里我们针对比较常见和重要的几种类型介绍一下。 const常量级一般出现这个就表示你写的SQL非常好性能非常快。哪些属于const呢比如select * from user where idx或者select * from user where namex这样的的语句直接就可以通过聚簇索引或者二级索引聚簇索引回表轻松查到你要的数据。 值的一提的是这里的二级索引必须是唯一索引。如果是普通索引那么就是ref了就如我们总览中的执行计划一样。当然ref也是一种非常快的查询方式。 range也是一种常见的查询方式一般出现在范围查询中比如我们前面举的一个例子select * from user where namex and name x假设name就是一个普通索引此时就必然利用索引来进行范围筛选一旦利用索引做了范围筛选那么这种方式就是range。 我们再回忆一下这个sqlselect name,age from user where age ‘29’因为age不是索引最左边的值所以它是没法从联合索引的根节点二分查找快速跳转的但是因为他的结果和条件都在索引里所以MySQL的优化器会直接扫描这个联合索引一个个的遍历。也就是说针对这种只要遍历二级索引就可以拿到你想要的数据而不需要回源到聚簇索引的访问方式就叫做index访问方式这种访问方式相对于前面几种就要慢的多。 最后再看看ALL顾名思义全表扫描一般属于我们要杜绝的情况。 10.8.2.6 possible_keys
可能用到的索引。 10.8.2.7 key
实际上使用的索引。 10.8.2.8 key_len
实际使用到的索引长度。 10.8.2.9 ref
当使用索引列等值查询时与索引列进行等值匹配的对象信息。 10.8.2.10 rows
预估的需要读取的记录条数这个值按道理来说越小越好。 10.8.2.11 filtered
某个表经过搜索条件过滤后剩余记录条数的百分比对于单表查询来说这个值没什么意义都是100%但是对于多表查询就有意义了越小越好。 10.8.2.12 Extra
一些额外的信息。比如Using whereUsing indexUsing filesort等等。我们用group by、union、distinct之类的语法的时候要是没法直接利用索引来进行分组聚合那么MySQL会直接基于临时表来完成会有大量的磁盘操作也就是会使用文件排序Using filesort。这种情况一般也是需要避免的。
