坪山网站建设公司,装饰公司怎么拉客户,深圳专业画册设计机构,宁波有哪家公司做网站的本文直切主题#xff0c;针对InnoDB引擎描述索引及优化策略。在开始之前#xff0c;需要读者了解#xff1a;二叉查找树(包括2-3查找树、红黑树等数据结构)MySQL的InnoDB引擎基础知识索引初探要了解索引#xff0c;当然要了解其数据结构。树有很多应用#xff0c;流行的用…本文直切主题针对InnoDB引擎描述索引及优化策略。在开始之前需要读者了解二叉查找树(包括2-3查找树、红黑树等数据结构)MySQL的InnoDB引擎基础知识索引初探 要了解索引当然要了解其数据结构。树有很多应用流行的用法之一是包括UNIX和DOS在内的许多常用操作系统中的目录结构二叉查找树又是Java中两种集合类TreeSet和TreeMap实现的基础。那么对于数据库I/O是其性能瓶颈所在减少树的深度是直接有效的BTree和BTree应运而生。BTree和BTree(Balance-Tree多路搜索树非二叉) BTree BTree是一种查找树如同二叉查找树红黑树等都是为提高查找效率而产生的BTree也是如此可以把它看做二叉查找树的优化升级。二叉查找树的特点是每个非叶节点都最多只有两个子节点但是当数据量非常大时二叉查找树的深度过深搜索算法自根节点向下搜索时需要访问的节点也就变的相当多。如果这些节点存储在外存储器(磁盘)中每访问一个节点相当于就是进行了一次I/O操作随着树高度的增加频繁的I/O操作一定会降低查询的效率。BTree改二叉为多叉每个节点存储更多的指针信息以此达到减少树的深度、降低I/O操作数。 使用BTree结构可以显著减少定位记录时所经历的中间过程从而加快存取速度。 定义(对于一个m阶BTree)根节点至少有两个子节点(除非根结点为叶节点)每个节点有m-1个关键字并且以升序排列位于 m-1和m 关键字的子节点的值位于 m-1和m 关键字对应的值之间其它节点至少有m/2个子节点 特性关键字集合分布在整棵树中任何一个关键字出现且只出现在一个节点中搜索有可能在非叶节点结束其搜索性能等价于在关键字全集内做一次二分查找自动层次控制。 BTree InnoDB 存储引擎在绝大多数情况下使用BTree建立索引BTree也是关系型数据库中最为常用和有效的索引结构但是BTree索引并不能找到一个给定键对应的具体值它只能找到数据行对应的页然后正如上一节所提到的数据库把整个页读入到内存中并在内存中查找具体的数据行。 定义(其定义基本与 BTree同除了)所有叶节点之间都有一个链指针所有关键字都在叶子结点出现非叶子节点只存储键值信息数据记录都存放在叶节点中。 特性单节点可以存储更多的元素使得查询磁盘IO次数更少更加高效的单元素查找所有查询都要查找到叶子节点查询性能稳定叶子节点会包含所有的关键字以及指向数据记录的指针并且叶子节点本身是根据关键字的大小从小到大顺序链接范围查找性能更优。 区别 BTree是BTree的一种变形树它与BTree的差异在于BTree只有达到叶子结点才命中(BTree可以在非叶子结点命中)其性能也等价于在关键字全集做一次二分查找BTree树每个叶子节点都有双向指针BTree分支节点和叶节点均保存记录的关键码和记录的指针BTree分支节点只保存记录关键码的复制无记录指针。所有记录都集中在叶节点一层并且叶节点可以构成一维线性表便于连续访问和范围查询。聚集索引和辅助索引 数据库中的 BTree索引可以分为聚集索引(clustered index)和辅助索引(secondary index)它们之间的最大区别就是聚集索引中存放着一条行记录的全部信息而辅助索引中只包含索引列和一个用于查找对应行记录的“书签”。即在数据库的聚集索引中叶子节点直接包含卫星数据。在辅助索引(NonClustered Index)中叶节点带有指向卫星数据的指针。聚集索引 InnoDB使用了聚集索引存储数据。 与非聚集索引的区别则是聚集索引既存储了索引也存储了行值。当一个表有一个聚集索引它的数据是存储在索引的叶子页(leaf pages)上的。因此可以说InnoDB是基于索引的表。 当我们使用聚集索引对表中的数据进行检索时可以直接获得聚集索引所对应的整条行记录数据所在的页不需要进行第二次操作。索引的建立规则如果一个主键被定义了那么这个主键就是作为聚集索引如果没有主键被定义那么该表的第一个唯一非空索引被作为聚集索引如果没有主键也没有合适的唯一索引那么InnoDB内部会生成一个隐藏的主键作为聚集索引这个隐藏的主键是一个6个字节的列改列的值会随着数据的插入自增辅助索引 辅助索引也叫做非聚集索引叶节点不包含行的全部数据。除了包含关键字外还包含了一个标记这个标记用来告诉InnoDB引擎从哪里可以找到与索引相对应的行数据。由于InnoDB引擎是索引组织表因此这个标记就是相应的行数据的聚集索引关键字。 辅助索引的存在并不影响数据在聚集索引中的组织因此一个表可以有多个辅助索引。 使用辅助索引查找一条表记录的过程通过辅助索引查找到对应的关键字最后在聚集索引中使用关键字获取对应的行记录这也是通常情况下行记录的查找方式。使用建议聚集索引的优先选择列含有大量非重复值的列使用 between或需要经常排序的列列顺序和最常用的排序一致返回大量结果集的查询经常被 join 的列不建议的聚集索引列修改频繁的列低选择性的列如性别新增内容太过离散随机的列规范与建议命名规则表名_字段名需要加索引的字段要在where条件中如果where条件中是OR关系加索引不起作用能用小类型别用大类型字段索引 key_len 长度过大也会影响 SQL 性能。所以尽量不默认 null会占用字节、索引长度。常用的字段放在前面选择性高的字段放在前面对较长的字符数据类型的字段建索引优先考虑前缀索引如 index(url(64))只创建需要的索引避免冗余索引如index(a,b)index(a)使用联合索引以避免回表达到覆盖索引联合索引遵循最左原则索引不可滥用索引会占用存储空间并且增加数据更新操作的复杂度降低CUD(create/update/delate)效率 回表 先了解一个概念MySQL对 WHERE 中条件的处理根据索引使用情况分成三种index key, index filter, table filter 1. index key 用于确定SQL查询在索引中的连续范围(起始范围结束范围)的查询条件被称之为Index Key。由于一个范围至少包含一个起始与一个终止因此Index Key也被拆分为Index First Key和Index Last Key分别用于定位索引查找的起始以及索引查询的终止条件。 2. index filter 在使用 index key 确定了起始范围和介绍范围之后在此范围之内还有一些记录不符合 WHERE 条件如果这些条件可以使用索引进行过滤那么就是 index filter。 3. table filter WHERE 中的条件不能使用索引进行处理的只能访问table进行条件过滤了。 从普通索引查出主键索引然后查询出数据的过程叫做回表。回表一次就会执行一次查询所以避免回表是减少数据库压力、提高效率的有效手段。在InnoDB中使用联合索引配合主键索引可以直接返回结果而不需要回表查询。 联合索引(复合索引)与前缀索引(最左原则) Mysql从左到右的使用索引中的字段一个查询可以只使用索引中的一部份但只能是最左侧部分。例如索引是(a,b,c)可以支持 a | a,b | a,b,c 3种组合进行查找但不支持 b,c 进行查找。这是最左原则的第一层意思联合索引的多个字段中只有当查询条件为联合索引的第一个字段时索引才会有效。 条件 WHERE a LIKE perfix%; 索引也会有效。这是最左原则的第二层意思根据字段值最左若干个字符进行的模糊查询索引有效。 覆盖索引 覆盖索引是对联合索引的合理利用。 比如 SELECT a, b FROM table WHERE a wangnima; 如果我们已经创建了(a)或(a,b)的联合索引那么这条语句会直接从索引返回而不会发生回表。即创建索引的字段覆盖了查询字段。 如果执行 SELECT c FROM table WHERE a wangnima; 就会发生回表因为我们的辅助索引树中没有字段 c 的数据需要拿到主键索引的关键字去主键索引中回表查询。 但是需要注意的是索引虽好不可滥用。 索引下推(Index Condition Pushdown (ICP)) 结合在 回表 概念中引出的三种索引使用情况(index key, index filter, table filter)ICP 技术就是 index filter 技术。MySQL的架构分为服务器层和引擎层。 官方解释(https://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html) 索引条件下推(ICP)是对MySQL使用索引从表中检索行的情况的优化。如果没有ICP存储引擎将遍历索引以定位基表中的行并将它们返回到MySQL服务器该服务器将计算基表行的where条件。在启用ICP的情况下如果部分where条件可以通过只使用索引中的列来计算MySQL服务器会把where条件的这部分 推入 存储引擎。然后存储引擎通过使用索引条目来评估所推送的索引条件并且只有在满足该条件时才从表中读取行。ICP可以减少存储引擎必须访问基本表的次数和MySQL服务器必须访问存储引擎的次数。 根据官方的指导我们来做个验证 EXPLAIN SELECT * FROM people WHERE zipcode95054 AND lastname LIKE %lao% AND address LIKE %Main Street%; 官方解释 EXPLAIN使用“索引条件下推”时输出显示 Using index condition在 Extra列中。 假设一个表包含有关人员及其地址的信息并且该表的索引定义为 INDEX (zipcode, lastname, firstname)。如果我们知道一个人的zipcode价值但不确定姓氏我们可以这样搜索 SELECT * FROM people WHERE zipcode95054 AND lastname LIKE %etrunia% AND address LIKE %Main Street%; MySQL可以使用索引来扫描人 zipcode95054。第二部分(lastname LIKE %etrunia%)不能用于限制必须扫描的行数因此如果没有Index Condition Pushdown此查询必须为所有拥有的人检索完整的表行 zipcode95054。 使用索引条件下推MySQL lastname LIKE %etrunia%在读取整个表行之前检查该 部分。这样可以避免读取与索引元组相对应的完整行这些行匹配 zipcode条件而不是 lastname条件。 默认情况下启用索引条件下推。可以optimizer_switch通过设置index_condition_pushdown标志来控制 系统变量 SET optimizer_switch index_condition_pushdownoff; SET optimizer_switch index_condition_pushdownon;实践*注意语句中的“[ ··· ]”中括号指代变量书写时记得去掉普通索引这是最基本的索引它没有任何限制。它有以下几种创建方式1. 创建索引CREATE INDEX indexName ON mytable(username(length));如果不是字符类型的字段如int则不要指定length如果是CHARVARCHAR类型length可以不指定也可以小于字段实际长度如果是BLOB和TEXT类型必须指定 length。2. 修改表结构(添加索引)ALTER table tableName ADD INDEX indexName(columnName)3. 创建表的时候直接指定CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) );唯一索引它与前面的普通索引类似不同的就是索引列的值必须唯一但允许有空值。如果是组合索引则列值的组合必须唯一。它有以下几种创建方式1. 创建索引CREATE UNIQUE INDEX indexName ON mytable(username(length))2. 修改表结构ALTER table mytable ADD UNIQUE [indexName] (username(length))3. 创建表的时候直接指定CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) );删除索引的语法DROP INDEX [indexName] ON mytable;总结 合理利用索引对于提升数据库的性能、减轻数据库服务器的负担是最直接有效的手段。 其实索引的本质就是通过缩小范围、把随机事件变成顺序事件来筛选出最终结果同时可以总是用同一种查找方式来定位数据这样就可以兼顾高效率和稳定性。
