纯净、安全、绿色的下载网站

首页|软件分类|下载排行|最新软件|IT学院

当前位置:首页IT学院IT技术

MySQL 索引

当年明月123   2020-01-30 我要评论

MySql 索引

MySql 索引

首先MySql 支持多种存储引擎最为常用的是 innodbMyIsam 也需要了解其他的存储引擎包括 Archive 等等都要又个印象。

各种存储引擎对于索引的支持也不相同总结下来MySql 的索引主要由三种类型B 树索引Hash 索引全文索引。我们只关注 BTree 索引因为这是我们平常使用 MySql 时主要的打交道的方式。

MySql 中的 B 树索引的物理文件大多是以 Balance Tree 的结构来存储的也就是所有的实际的数据都存放于 Tree 的叶子节点中到任何一个叶子节点的最短路径长度都是完全相同的。各个数据库或者存储引擎都会对 B 树索引的存储结构稍加改造比如 innodb 的 B 树索引实际使用的 B+ 树也就是在 B 树的结构上做了很小的改动。除了在每一个叶子节点上存放索引的相关信息之外还存储了只想该叶子节点的后一个叶子节点的指针信息(增加了顺序访问)这也是为了加快检索多个相邻的叶子节点的效率考虑。

什么是索引

定义:索引是为了帮助 MySql 高效获得数据的数据结构。

目的:索引的目的当然是为了快速找到对应的数据了。就比如我们查字典一定是按照首个字母或者拼音去找而不是翻遍整个字典。

索引原理

索引的原理就是通过不断的缩小范围从而筛选出想要的结果从而避免对整个文件的查找。同时把随机的事件变为顺序的事件也就是我们总是通过同一种查找方式来锁定数据。

数据库的索引更为复杂因为不仅面临着等值查询还有范围查询(<>between)模糊查询(like)并集查询(or)多值匹配(in)等等。我们回想字典的例子能不能把数据分成段然后分段查询那?比如将 1000 条数据中的 1 到 100 分为第一段101 到 200 分为第二段...... 这样查第 105 条数据只需要查第一段就可以了。如果是 10000 条那怎么分段?稍微有算法基础的同学可能会想到搜索树平均复杂度是 logN性能不错但是有时为了提高性能会把部分数据读入内存中来计算我们知道访问磁盘的成本大概是访问内存的十万倍左右所以简单的搜索树并不能满足复杂的应用场景。

B+ 树索引结构

上面我们说过简单的搜索树并不能满足数据库的使用场景。我们需要索引做什么那?那就是每次查找数据时能把磁盘 IO 次数控制在一个很小的数量级最好是常数数量级。因此一个高度可控的多路搜索树 b+ 树产生了。

每个磁盘块中包含几个数据项(深蓝色)和指针(黄色)磁盘块1 包含数据项 17 和 35包含指针 P1P2P3P1 表示小于 17 的磁盘块P2 表示在 17 和 35 之间的磁盘块P3 表示大于 35 的磁盘块。

真实的数据存在于叶子节点即 3591013152829366075799099。非叶子节点不存储真实数据只存储搜索方向的数据项。

B+ 树的查找过程

如果我们想要查找 29那么首先会把磁盘1 家在到内存此时发生一次 IO内存中用二分查找确定 29 在 17 和 35 之间锁定磁盘块 1 的 P2 指针加载磁盘块 3 到内存发生第二次 IO。然后锁定磁盘块 8发生第三次 IO找到了 29。结束查询总共发生三次 IO。3 层的 B + 树可以表示上百万的数据所以上百万的数据的查询只需要三次 IO 就可以完成了如果没有索引每个数据项都要发生一次 IO那么就需要百万次的 IO成本非常高。

MySql 的索引实现

MySql 中索引是存储引擎级别的概念不同的存储引擎对索引的实现方式是不同的。我们主要针对 MyISAM 和 InnoDB 两个存储引擎的索引实现来讨论。

MyISAM 索引实现

MyISAM 引擎使用的是 B+ 树作为索引结构叶子节点的 data 域存放的是数据记录的地址。

假设我们以 Col1 为主键那么上图就是 MyISAM 表的主键索引。MyISAM 存储引擎中主键索引和辅助索引在结构上是没有任何区别的只是主索引要求 key 是唯一的而辅助索引的 key 可以重复。我们来看建立在 Col2 上的辅助索引。

总结:同样也是一颗 B+ 树data 域保存数据记录的地址。因此MyISAM 的索引的算法首先按照 B+ 树搜索算法搜索索引如果指定的 Key 存在则取出其中 data 域的值然后读取相关记录。

MyISAM 的索引方式也叫做 “非聚集” 的之所以这么称呼是为了与 InnoDB 的聚集索引区分的。

InnoDB 索引实现

虽然 InnoDB 也使用 B+ 树作为索引结构但是具体实现方式与 MyISAM 截然不同。

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

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

第二个与 MyISAM 索引的不同是 InnoDB 的辅助索引 data 域存储相应记录主键的值而不是地址。InnoDB 的所有辅助索引都引用主键作为 data 域。

这里以英文字符的 ASCII 码作为比较标准。聚集索引这种实现方式使得按主键搜索十分搞笑但是辅助索引搜索需要两遍索引:先检索辅助索引获得主键然后用主键索引检索记录。

熟悉了 InnoDB 的索引实现后就明白了为什么不建议使用过长的字段作为主键因为所有辅助索引都使用主索引过长的主索引会令辅助索引变的过大。再比如用非单调的字段作为 InnoDB 的主键不是个好主意非单调的主键造成插入新纪录时数据文件维持 B+ 树的特性而频繁的分裂调整使用自增字段作为主键是一个很好的选择。

InnoDB索引和MyISAM索引的区别:

一是主索引的区别InnoDB的数据文件本身就是索引文件。而MyISAM的索引和数据是分开的。

二是辅助索引的区别:InnoDB的辅助索引data域存储相应记录主键的值而不是地址。而MyISAM的辅助索引和主索引没有多大区别。

建立索引常用技巧

  1. 最左匹配原则非常重要的原则mysql 会一直向右匹配直到遇到范围查询(<>betweenlike)就停止匹配比如 a=1 and b = 2 and c > 3 and b = 4 如果建立(abcd)顺序的索引d 是用不到索引的如果建立(abdc)的索引都可以用到。
  2. = 和 in 可以乱序比如 a = 1 and b = 2 and c = 3 建立 (abc)所以可以任意顺序。
  3. 尽量选择区分度高的列作为索引。字段不重复的比例不能太小唯一键的区分度是 1而一些状态性别字段在大数据面前区分度就是 0。
  4. 索引不能参与计算。比如 from_unixtime(create_time) = ’2014-05-29’ 就不能使用到索引原因很简单b+ 树中存的都是数据表中的字段值但进行检索时需要把所有元素都应用函数才能比较显然成本太大应该把语句写成 create_time = unix_timestamp(’2014-05-29’)。

SQL 调优

一般要进行 SQL 调优那么就是有慢查询的 SQL系统或者 server 可以开启慢查询日志。

通过慢查询记录能够记录一些执行时间比较久的 SQL 语句找出这些语句不意味着工作结束了我们通常使用 explain 这个命令来查看这些 SQL 语句的执行计划查看该 SQL 语句有没有使用索引有没有做全表扫描。

mysql> explain select * from servers;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | servers | ALL  | NULL          | NULL | NULL    | NULL |    1 | NULL  |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
1 row in set (0.03 sec)

各个字段的含义:

  1. id:表示 SQL 执行的顺序的标识。
  2. select_type:表示产讯中每个 select 子句的类型。
  3. table:显示这一行的数据是关于哪张表的有时不是真实的表名字。
  4. type:表示MySQL在表中找到所需行的方式又称“访问类型”。常用的类型有: ALL, index, range, ref, eq_ref, const, system, NULL(从左到右性能从差到好)。
  5. possible_keys:指出MySQL能使用哪个索引在表中找到记录查询涉及到的字段上若存在索引则该索引将被列出但不一定被查询使用
  6. Key:key列显示MySQL实际决定使用的键(索引)如果没有选择索引键是NULL。
  7. key_len:表示索引中使用的字节数可通过该列计算查询中使用的索引的长度(key_len显示的值为索引字段的最大可能长度并非实际使用长度即key_len是根据表定义计算而得不是通过表内检索出的)。
  8. ref:表示上述表的连接匹配条件即哪些列或常量被用于查找索引列上的值。
  9. rows: 表示MySQL根据表统计信息及索引选用情况估算的找到所需的记录所需要读取的行数理论上行数越少查询性能越好。
  10. Extra:该列包含MySQL解决查询的详细信息。

EXPALIN只能解释SELECT操作其他操作要重写为SELECT后查看执行计划。


相关文章

猜您喜欢

  • Java解析XML文件的方式

    在项目里我们常常会把一些配置信息放到xml文件里或者各部门间会经过xml文件来交流业务数据所以有时分我..
  • 细数软件工程各阶段必不可少的那些图

    一、前言软件工程中规则软件生命周期由软件定义、软件开发和运转维护(也称为软件维护)3个时期组成每一个时期..

网友评论

Copyright 2020 www.Shellfishsoft.com 【贝软下载站】 版权所有 软件发布

声明:所有软件和文章来自软件开发商或者作者 如有异议 请与本站联系 点此查看联系方式