为Mysql创立最优的索引ITeye - 千亿集团

为Mysql创立最优的索引ITeye

2019-01-11 01:31:22 | 作者: 辰沛 | 标签: 索引,查询,运用 | 浏览: 802

索引关于杰出的功用十分要害,尤其是当数据规划越来越大的时分,索引的对功用的影响越发重要。 索引常常会被误解乃至疏忽,而且常常被糟糕的规划。 索引优化应该是对查询功用优化最有用的手法了,索引能够简略将查询功用进步几个数量级,最优的索引会比 较好的索引功用要好2个数量级。 1 索引的类型 (1) B-Tree 不出意外,这儿说到的索引都是指 B-Tree索引,InnoDB 则运用 B+Tree索引 1 B-Tree索引的约束 1 假如不是依照索引的最左列开始查找,则无法运用索引。假定一张表索引中包括了last_name,first_name,birthday三列, 这个组合索引无法用于查找first_name为bill的人,也无法查找某个特定生日的人,由于这两列都不是最左数据列。 2 不能越过索引中的列 这个组合索引无法用于查找last_name为fuck ,birthday为特定日期的数据,由于越过了某些索引。假如不指定first_name mysql只能运用索引的榜首列。 3 假如查询中有某个列的查询规模,则其右边一切列都无法运用索引优化查询。 例如 where last_name="fuck" and first_name like "kk%" and birthday = 2015-06-18 这个查询只能运用索引的前两列,由于这儿like是一个规模条件。假如规模查询列值的数量有限,能够经过运用多个等于条件 来替代规模。 综上 索引列的次序是多么重要。在优化的时分,能够运用不同的次序完结不同类型要求的优化。 (2) 哈希索引 存储引擎会对一切的索引列核算出一个哈希码,哈希码存储在索引中,一同在哈希表中保存指向每个数据行的指针。 哈希槽是有次序的,数据行确是紊乱的。查询指令:select name from test wnere fname = peter; mysql先核算peter的哈希值,并运用该值寻觅对应的记载指针,之后比较其值是否为peter以保证精确。 哈希索引的约束 1 哈希索引的数据并不是依照索引值次序存储的,所以无法用于排序。 2 哈希索引也不支撑部分索引列匹配查找,由于哈希索引始终是运用索引列的悉数来核算哈希值的。例如 索引列(A,B) 只查询数据列A 则无法运用该索引。 3 Hash索引只支撑等值查询,不支撑规模查询。 4 假如hash抵触许多的话,一些索引保护操作的价值也会很高。 由于这些约束,hash索引只适宜某些特定的场合,而一旦适宜hash索引,则他带来的功用进步将十分显着。 比方在数据仓库中有一种星型schema,就十分适宜hash索引。 InnoDB 引擎有一个特别的功用叫做 自适应哈希索引。当InnoDb留意到某些hash索引值被运用的十分频频, 则她会在内存中依据B-Tree索引之上再创立一个hash索引,这样就让B-Tree索引也具有哈希索引的一些长处, 比方快速的hash查找。这是一个可封闭的内部的 用户无法控制 装备的行为。 2 索引的长处 1 索引大大削减额服务器需呀扫描的数据量 2 索引能够协助服务器防止排序和暂时表 3 索引能够将随机I/O变为次序I/O. 3 索引是最好的处理方案吗? 索引并不总是最好的东西,总的来说只要党一切带来的收益大于其带来的额定作业时,索引才是有用的。 关于十分小的表,全表扫描会更有用,关于大中型的表,索引就会十分有用,可是关于特大型的表,树立索引和运用的价值 将随之添加,在这种状况下则需求一种技能能够直接区分出查询需求的一组数据,而不是一条一条记载的匹配,例如 运用分区技能。这将在后面章节评论。 假如表的数量十分多,能够树立一个元数据信息表,用来查询需求用到的某些特性。例如履行那些需求聚合多个 运用散布在多个表的数据查询,则需求记载哪个用户信息存储在哪个表中的元数据,这样在查询时就能够直接疏忽那些 不包括指定用户信息的表。关于大型体系,这是一个常用的技巧,事实上,Infobright 便是运用相似的完结 关于TB等级的数据,定位单条记载的含义不大,所以常常会运用块等级元数据技能带替代索引。 4 制止犯下的过错 索引不是独立的,这是混蛋逻辑。 制止犯下面的过错, 1 select id from table where id +1 = 4; 这类sql 彻底能够防止,这样的查询不能被mysql解析,也不会运用到查询。 2 select name form table where sum(keg) - sum(lo) 为什么咱们要把核算放进查询条件? 莫非咱们脑子进水了吗? 5 前缀索引 alter table table_name add key (name(7)) 前缀索引更小更快,可是前缀索引order by 和group by 也无法运用前缀索引做掩盖扫描 6 多列索引 世人常鄙陋,一个常见的过错便是为每个列创立独立的索引,或许依照过错的次序创立多列索引。 create table t (q1 int,q2 int,q3 int key(q1),key(q2),key(q3)) 这种索引战略是十分过错的,这样一来最好的状况下也只能做到一星,也便是把一切契合的数据找出来,有时假如无法 规划一个三星索引,还不如疏忽掉where子句,集中精力优化索引列的次序,或许创立一个全掩盖索引。 咱们的schema规划的真他妈的烂! 假如几年后的我规划出这种表,还不如去吃屎。 select name from table_name where id1 = 1 or id2 = 1; 假如在老版别的mysql中将会履行的比较糟糕,可是在新版别中(5.0之后) 将会运用两个索引扫描的联合。 explain select name from table_name where id1 = 1 or id2 = 1 \G; 格式化之后将会看到运用了联合。 索引兼并战略是一种优化的成果,实际上反映的是表上的索引建的有多糟糕。 索引兼并需求留意的工作: 1 当呈现服务器对多个索引做相交操作时(多个and条件) 一般意味着需求一个包括一切相关列的多列索引,而不是 多个独立的单列索引。 2 服务器对多个索引做联合操作时,需求消耗更多的cpu和内存资源。 所以,假如再履行方案中看到有索引兼并,应该好好检查一下查询和表的结构。 7 挑选适宜的索引列次序 1 当不需求考虑排序和分组时 将挑选性最高的列放在最前面一般是最好的。 2 剖析查询 select name from table_name where sid = 2 and cid = 120; 咱们该创立一个联合索引(sid,cid)仍是倒置一下次序?先来履行一下 select sum(sid=2),sum(cid=120) from table_name; sum(sid=2):2100 sum(cid=120) :30 依据经历规律 cid的挑选性更高,cid应该放在前面 select count(distinct sid)/count(*),count(distinct cid)/count(*),count(*) from table_name\G; cid的挑选性更高,cid应该放在前面 alter table table_name add key(cid,sid); 8 聚簇索引 怎么创立聚簇索引 假如没有界说主键,InnoDB会挑选一个仅有的非空索引替代。假如没有这样一个索引,InnoDB会隐式的界说一个主键 来作为聚簇索引。 聚簇索引的长处 1 能够把相关的数据保存在一同。 2 数据拜访更快。聚簇索引将索引和数据保存在同一个B-Tree中,因而更快速查询。 3 运用掩盖索引扫描的查询能够直接运用叶节点中的主键值。 当然他也有自己的缺陷,自己去找。 9 掩盖索引 假如索引的叶子节点中现已包括要查询的数据,那么久没有必要再回表查询。假如一个索引包括一切需求查询的字段的 值,咱们就称之为 掩盖索引。掩盖索引时十分有用的东西,能够极大的进步功用。假如查询只需求扫描索引 而无需回表,会带来许多优点: 1 索引条目一般远小于数据行巨细,假如只需求读取索引,那么mysql就会极大地削减数据拜访量。这对缓存的负载十分重要,由于这种状况下 呼应时刻大部分话费在数据复制上,索引相对较小,更简略悉数放入内存中。 2 由于索引是依照列值次序存储的,所以关于I/O密集型的规模查询会比随机从磁盘读取每一行数据的I/O要上的多。 3 一些存储引擎MyISAM在内存中只缓存索引,数据则依靠操作体系缓存,因而要拜访数据需求一次体系调用,这或许会 导致严峻的功用问题,尤其是那些体系调用占了数据拜访中的最大开支的场景。 4 由于InnoDB聚簇索引,掩盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值, 所以假如二级主键能够掩盖查询,则能够防止对主键索引的二次查询。 不是一切的索引都能够成为掩盖索引。掩盖索引必需求存储索引列的值, 当主张一个被索引掩盖的查询,也叫做索引掩盖查询时,在explain 的extra列能够看到 Using index 的信息, 例如在表tab 有一个多列索引(std,fld) ,mysql 只需求拜访这两列,就能够运用这个索引锁掩盖索引。 select std,fld from tab; 过错事例 select * from tab_temp where keyd = 405 and title like %8899%; 这儿索引无法掩盖该查询,也便是说这条查询无法运用掩盖索引查询,原因如下: 1 没有任何索引能够掩盖这个查询,由于查询从表中挑选了一切的列,二没有任何索引掩盖了一切的列。 2 mysql 不能再索引中履行like操作,这是底层存储引擎api的约束,mysql5.5版别只允许在索引中做简略比较操作。 mysql 能在索引中做最左前缀匹配的like操作,由于该操作能够转化简略的比较操作,可是假如是通配符 最初的like比较。存储引擎就无法做比较匹配,这种状况下mysql服务器只能提取数据行的值而不是索引值来做比较。 咱们能够经过从头规划索引和调整sql 来运用掩盖索引。 规划联合索引(keyd,title,proid) select * from tab join(select proid from tab where keyd = 405 and title like %8899%)as t on t.proid=tab.proid; explain 一下 会发现 extra 列里 呈现了Using index . 运用了部分掩盖索引查询。 内部查询中运用了掩盖索引,结合外部一同完结查询。 InnoDB 能够凭借主键列来完结掩盖索引查询。 例如表 txt 主键列 t_id,索引列 t_name, 查询 select t_id,t_name from txt where t_name =jk; explain 一下会发现 extra 列呈现了Using index ,也便是说这条查询运用了掩盖索引。 由于InnoDB 二级索引的叶子节点都包括了主键的值,这意味着InnoDB二级索引能够运用包括的主键完结掩盖索引查询。mysql5.6版别 索引条件推送这个特性 现已在很大程度上完善了这种查询办法。 10 运用索引扫描来做排序 1 假如或许,触及索引时应该尽或许的使索引既能满意排序又用于查找行。 只要当索引的列次序和order by 子句的次序彻底一致,而且一切列的排序方向都相一起,mysql才干够运用索引来对成果做排序。 假如需求相关多张表,只要当order by子句引证的字段悉数为榜首个表时,才干运用索引做排序。 有一种状况,能够在不满意索引的最左前缀的状况下运用索引排序。 在表 rental 存在索引(rental_date,i_id,c_id) select rental_date,i_id,c_id from rental where rental_date = 2015-06-18 order by i_id,c_id; 检查履行方案 explain ,没有发现filesort文件排序。即便没有满意索引的最左前缀的要求,也能够用于索引排序,这是由于索引的榜首类被指定为常量。 再看示例 select rental_date,i_id,c_id from rental where rental_date = 2015-06-18 order by i_id; 检查履行方案 ,相同运用了索引排序,这是由于索引的榜首列运用了常量,中心一列与榜首列形成了组合,满意了索引最左前缀的要求,运用了索引排序。 下面看看不能运用索引排序的查询 索引的榜首列是规模查询 无法运用索引排序 where rental_date 2015-06-18 order by i_id,c_id; 索引列的次序满意最左前缀,可是正序或反序不一致。 where rental_date = 2015-06-18 order by i_id desc,c_id asc; 不满意最左前缀要求 where rental_date = 2015-06-18 order by cun,i_id; 运用索引做排序的一个最重要的做法是当查询一同有order by 字句和limit字句的时分。 11 紧缩索引(前缀紧缩) 紧缩索引,望文生义便是索引的紧缩,先保存索引块中的榜首个值,然后将其他值和榜首个值进行比较得到相同前缀的字节数和 剩下的不同后缀部分,把这部分存储起来即可。例如索引快的榜首个值是 fuck,第二个值是fuckyou,那么第二个前缀的紧缩值 是相似于 "4,you".这样的存储办法能够让索引所占的空间只占用之前的十分之一,可是关于cpu密集型的运用,其价值便是会变得适当慢 关于一些倒序操作,功率会下降的更凶猛。 12 冗余和重复索引 重复索引是指在相同的列上依照相同的次序创立相同类型的索引。应该防止这类索引,发现之后应该当即移除。 例如create table test(id,primary key,a int,unique(id),index(id)),这儿重复了三次索引, 在同一列树立类型相同的索引,MySQL的仅有约束和主键约束都是经过索引完结的。 可是假如类型不同,并不算重复索引,因而 key(col) 和 fulltextkey(col) 是有很好的理由一起存在的。 冗余索引和重复索引有所不同,假如创立了(A,B),再创立索引(A) 便是冗余索引, 由于这仅仅前一个索引的前缀索引,因而(A,B)也能够作为索引(A)来运用,可是假如在创立(B,A)则不是冗余索引,索引 (B) 也不算冗余索引,由于(B)不是(A,B)的最左前缀索引。 冗余索引一般发作在为表添加新索引的时分,例如,有人或许会添加一个新的索引(A,B) 而不是扩展现已存在的索引(A). 还有一种状况是将一个索引扩展至(A,ID) 其间ID是主键,关于InnoDB来说,主键现已包括在二级索引中了,所以这种战略是一种重复。 大多数状况下都不需求冗余索引,尽量在已有的索引上扩展。 过多的索引会让查询的本钱更高,刺进的时刻更久,处理冗余和重复的索引的办法很简略,删去就能够。 能够凭借东西 common_schema ,该东西经过剖析表结构来找出冗余和重复的索引。 主张运用percona东西箱中的pt-upgrade东西来仔细检查方案中的索引改变。 关于服务器或许永久不会运用的索引,主张彻底删去。 最有用的办法是在percona Server 或许MariaDB中先翻开userstates服务器变量, 然后让服务器正常运转一段时刻,再经过查询information_schema.index_statistics就能 查到每个索引的运用频率。 别的还能够运用percona Tookit中的pt-index-usage,该东西能够读取查询日志,并对日志中的 每条查询explain操作,然后打印出索引和查询的陈述。这个东西不只能够找出哪些索引是未运用的,还能够了解查询的履行方案。 该东西还能够将成果写入到MySQL的表中。 13 索引和锁 关于InnoDB、索引和锁有一些很少人知道的细节:InnoDB在二级索引史上运用同享(读)锁 但拜访主键索引需求排它(写)锁。这消除了运用掩盖索引的或许性,而且使得select for update 比 lock in share mode或非确定查询要慢许多。
版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表千亿集团立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章

阅读排行

  • 1

    mysql的count函数优化ITeye

    索引,优化,情况
  • 2
  • 3

    DB2锁机制ITeye

    机制,程序,数据
  • 4

    oracle功能优化总结ITeye

    索引,视图,优化
  • 5

    oracle存储进程ITeye

    存储,进程,数据
  • 6

    Oracle中rownum、rowid、rowITeye

    相同,记载,排名
  • 7
  • 8
  • 9

    解析oracle的rownum(转载)ITeye

    查询,记载,运用
  • 10