呼和浩特网站建设电话,网页游戏平台软件,哪个地方可学习网站建设,wordpress不填标题无法发布索引是提高MySQL查询性能的一个重要途径#xff0c;但过多的索引可能会导致过高的磁盘使用率以及过高的内存占用#xff0c;从而影响应用程序的整体性能。应当尽量避免事后才想起添加索引#xff0c;因为事后可能需要监控大量的SQL才能定位到问题所在#xff0c;而且添加索… 索引是提高MySQL查询性能的一个重要途径但过多的索引可能会导致过高的磁盘使用率以及过高的内存占用从而影响应用程序的整体性能。应当尽量避免事后才想起添加索引因为事后可能需要监控大量的SQL才能定位到问题所在而且添加索引的时间肯定是远大于初始添加索引所需要的时间可见索引的添加也是非常有技术含量的。
1-为什么要使用索引
在数据库中我们为什么需要使用索引呢一般有以下几点原因 1索引大大减少存储引擎需要扫描的数据量 2索引可以帮助我们进行排序避免使用临时表 3索引可以把随机IO变为顺序IO
2-索引的数据结构 通常我们所说的索引是指B-Tree索引它是目前关系型数据库中查找数据最为常用和有效的索引大多数存储引擎都支持这种索引。使用B-Tree这个术语是因为MySQL在CREATE TABLE或其它语句中使用了这个关键字但实际上不同的存储引擎可能使用不同的数据结构比如InnoDB就是使用的BTree。 BTree中的B是指balance意为平衡。需要注意的是B树索引并不能找到一个给定键值的具体行它找到的只是被查找数据行所在的页接着数据库会把页读入到内存再在内存中进行查找最后得到要查找的数据。 理解BTree时只需要理解其最重要的两个特征即可第一所有的关键字可以理解为数据都存储在叶子节点Leaf Page非叶子节点Index Page并不存储真正的数据所有记录节点都是按键值大小顺序存放在同一层叶子节点上。其次所有的叶子节点由指针连接。
3-高性能索引策略
前缀索引如果列很长通常可以索引开始的部分字符这样可以有效节约索引空间从而提高索引效率。
联合索引
1一个联合索引可以相当于几个索引例如a,b建立联合索引 select * from tablename where a’xxx’ and b’xxx’ (可以使用索引) select * from tablename where a’xxx’ (可以使用索引) select * from tablename where b’xxx’ (不可以使用索引) 2联合索引已经对第二个键值进行了排序处理例如(userid,buy_date)联合索引前提是已经先按照userid条件查询。 查询用户userid1的最近3次购买记录 select * from buy_log where userid1 order by buy_date desc limit 3 根据该联合索引取出的数据无须再对buy_date做一次额外的排序操作。如果extra选项中出现using filesort就是需要额外的一次排序才能完成查询。 对于联合索引a,b,c来说下列语句同样可以直接通过联合索引得到结果 select * from tablename where a’xxx’ order by b; select * from tablename where a’xxx’ and b’xxx’ order by c
联合索引的顺序选择1经常被使用列优先2选择性高的列优先3宽度小的列优先
覆盖索引
InnoDB存储引擎支持覆盖索引covering index即从辅助索引中就可以查询的记录而不需要查询聚集索引中的记录。 覆盖索引优点 1辅助索引不包含整行记录的所有信息故其大小远小于聚集索引因此可以减少大量的IO操作。 查看执行计划Extra列的Using index就是表示优化器进行了覆盖索引操作。
2对某些统计问题而言。 此外在通常情况下诸如(a,b)的联合索引一般是不可以选择b中的所谓的查询条件。但是如果是统计操作并且是覆盖索引则优化器会进行选择。 例如select count(*) from buy_log where buy_date ’2011-11-01’ and buy_date’2013-12-02’ 表buy_log有userid,buy_date联合索引这里只根据buy_date进行查询一般情况下是不能使用该联合索引的但是这句sql是查询统计操作并且可以利用到覆盖索引的信息因此优化器会选择联合索引。
MRR优化Multi-Range Read
MRR从mysql5.6版本开始支持MRR优化的目的就是为了减少磁盘的随机访问并且将随机访问转化为较为顺序的数据访问这对于IO-bound类型的sql查询可带来性能极大的提升。MRR优化可适用于rangrefeq_ref类型查询。 MRR优化的好处 1MRR使数据访问变得较为顺序。在查询辅助索引时首先根据得到的查询结果按照主键进行排序并且按照主键排序的顺序进行书签查找。 2减少缓冲池中页被替换的次数 3批量处理对键值的查询操作。
例如 Select * from salaries where salary10000 and salary40000 (salarys上有辅助索引idx_s) 不启用MRR特性Extra列显示 using index condition 如果启用MRR特性Extra除了显示 using index condition还会显示using MRR
此外MRR还可以将某些范围查询拆分为键值对以此来进行批量的数据查询。这样做的好处是在拆分的过程中直接过滤一些不符合查询条件的数据。 例如select * from t where key_part11000 and key_part12000 and key_part210000 表t有key_part1, key_part2的联合索引因此索引根据key_part1key_part2的位置关系进行排序。若没有MRR此时查询类型为rangesql优化器会先讲将key_part1大于1000并且key_part1小于2000的数据都取出即使key_part2不等于10000取出数据后再根据key_part2进行过滤这会导致无用数据被取出尤其是有大量的数据并且key_part2不等于10000。 如果启用了MRR优化优化器会先将查询条件进行拆分然后再进行数据查询。 启用MRR show variables like optimizer_switch Mysql5.7默认mrr_cost_base默认标记是on mrr_cost_base标记表示是否通过cost_base的方式来选择是否启用mrr。如果将mrr设为onmrr_cost_base设为off则总是启用MRR优化。 index_condition_pushdownICP ICP是mysql5.6版本开始支持的之前版本的mysql数据库不支持ICP当进行索引查询时首先根据索引来查找记录然后再根据where条件来过滤记录。在支持ICP后mysql数据库在取出索引的同时判断是否可以进行where条件过滤也就是将where的部分过滤操作放在了存储引擎层前提是where的过滤条件是要改索引可以覆盖到的范围。在某些查询下可以大大减少上层sql层对记录的索取fetch从而提高数据库整体性能。 ICP优化支持range、ref、eq_ref、ref_or_null类型的查询支持myisam和InnoDB当优化器选择ICP优化时可在执行计划列Extra看到Using index condition 提示。
