医院 网站建设 新闻,源代码查看WordPress文件,好听的公司名字,川制作官方网站一 概述 索引(index)是帮助MysQL高效获取数据的数据结构(有序)。在数据之外#xff0c;数据库系统还维护着满足特定查找算法的数据结构#xff0c; 这些数据结构以某种方式引用#xff08;指向#xff09;数据#xff0c;这样就可以在这些数据结构上实现高级查找算法数据库系统还维护着满足特定查找算法的数据结构 这些数据结构以某种方式引用指向数据这样就可以在这些数据结构上实现高级查找算法这种数据结构就是索引。 假如表中没有索引的查询情况
select * from user where age 45 执行select语句之后,它会从第一条记录开始依次向下查询匹配,当找到符合条件的记录7号时它会继续向下查找直到将整个表的记录查询匹配完毕这种查询称为全表扫描性能十分的低。 假如有索引的查询情况 数据结构二叉树查询age45的记录查询的方法首先将age45与结点36比对发现比36大,走右边然后与48比较比48小走左侧最后找到age4的记录。找到45以后通过索引建立的二叉树找到指向的数据。显然有索引的只需要匹配3次就定位到了数据搜索效率十分高效。 二 索引结构 MySQL的索引是在存储引擎层实现的不同的存储引擎有不同的结构主要包含以下几种:
在不同存储引擎中对索引的支持 我们平常所说的索引如果没有特别指明都是指B树结构组织的索引。
2-1 Btree索引结构
首先我们现了解一下二叉树 上图二叉树:一个结点最后只能有两个子节点左侧的结点比根结点小右侧的结点比根结点大。比如我们查找数据20与36比较比36小走左侧在与22比较比22小走左侧在于19大走右侧最终找到数据记录20.
如果维护二叉树时是顺序插入最终形成一个单向链表如果我们要查找17那么就会把整个链表走一次。 弊端二叉树缺点:顺序插入时会形成一个链表查询性能大大降低。大数据量情况下层级较深检索速度慢。 解决链表的顺序插入我们可以使用红黑树但是红黑树也是一个二叉树大数据量的情况下层级较深检索熟读仍然慢。
B-Tree树
上面的红黑树虽然解决了顺序插入的问题但是仍然不能满足大量的数据如果我们一个结点下面跟多个结点那么就可以提升查询的效率。
以一颗最大度数max-degree)为5(5阶)的b-tree为例(每个节点最多存储4个key5个指针):
模拟B-Tree树的形成 Data Structure Visualization
插入100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268 250数据为例。 2-2 BTree树
以一颗最大度数(max-degree)为4(4阶)的btree为例: 观察可以发现所有的元素都会出现在叶子结点上面的非叶子结点主要起到索引作用。在B树中所有的叶子结点形成了一个单向链表。
模拟B树的形成
插入100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268 250数据为例。 MySQL索引数据结构对经典的BTree进行了优化。在原BTree的基础上增加一个指向相邻叶子节点的链表指针就形成了带有顺序指针的BTree提高区间访问的性能。 2-3 Hash结构 哈希索引就是采用一定的hash算法将键值换算成新的hash值映射到对应的槽位上然后存储在hash表中。 比如上面的我们通过每一个人名name,通过我们的hash算法得到槽位值然后在这个槽位中存放这个keyname以及地址值。当有多个key指向一个槽位时会产生hash冲突我们可以通过链表来解决在一个槽位后面增加一个链表。 特点 hash索引只能用于对等比较(,in),不支持范围查询(between,,,...)无法利用索引完成排序操作查询效率高通常只需要一次检索就可以了效率高于Btree索引 在MysQL中支持hash索引的是Memory引擎而innoDB中具有自适应hash功能,hash索引是存储引擎根据BTree索引在指定条件下自动构建的。 为什么InnoDB存储引擎选择使用Btree索引结构
相对于二叉树层级更少搜索效率高对于B-tree无论叶子结点还是非叶子结点都会保存数据这样导致一页中存储的键值减少指针跟着减少要同样保存大量数据只能增加树的高度导致性能降低。相对Hash索引Btree支持范围收索以及排序操作。
三 索引分类 在InnoDB存储索引中更据索引的存储形式又可以分为以下两种 聚集索引的选取规则 如果存在主键主键索引就是聚集索引 如果不存在主键将使用第一个唯一索引作为聚集索引 如果表没有主键或没有合适的唯一索引则InnoDB会自动生成一个rowid作为隐藏的聚集索引。 执行SQL语句首先我们会通过二级索引查找到Arm的id为10,然后通过聚集索引查找到id为10对应的行的数据内容。这种查询我们称为回表查询。 四 索引的语法 -- 创建索引 create [UNIQUE | FULLTEXT ] INDEX index_name ON table_name(index_col_name,..)-- 查看索引 SHOW INDEX FROM table_name;-- 删除索引 Drop INDEX index_name ON table_name 首先我们已经有了一个tb_user表,在此基础上我们来实现创建索引的操作。 创建索引 五 SQL性能分析
5-1 查看执行频率
SQL执行频率 MySQL 客户端连接成功后通过 show [session|global] status 命令可以提供服务器状态信息。通过如下指令可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次 -- session 是查看当前会话 ; -- global 是查询全局数据 ; Com_delete: 删除次数 Com_insert: 插入次数 Com_select: 查询次数 Com_update: 更新次数 我们可以在当前数据库再执行几次查询操作然后再次查看执行频次看看 Com_select 参数会不会 变化。 通过上述指令我们可以查看到当前数据库到底是以查询为主还是以增删改为主从而为数据 库优化提供参考依据。 如果是以增删改为主我们可以考虑不对其进行索引的优化。 如果是以 查询为主那么就要考虑对数据库的索引进行优化了。 5-2 慢日志查询 慢查询日志记录了所有执行时间超过指定参数long_query_time单位秒默认10秒的所有SQL语句的日志。 MySQL的慢查询日志默认没有开启我们可以查看一下系统变量 slow_query_log。
