那些网站可以做淘宝店铺推广,高端网站建设如何收费,音乐网站建设教程,技能培训班有哪些课程刘老师群里,看到一位小友 问MYSQL 45讲林晓斌的回答 大意是一个组合索引 (a,b,c) 条件 a 5 and a 10 and b123, 这样的情况下是如何? 林老师给的回答是 A5 ,然后下推B123
小友 问 为什么不是先 进行范围查询,然后在索引下推 b123? 然后就…刘老师群里,看到一位小友 问MYSQL 45讲林晓斌的回答 大意是一个组合索引 (a,b,c) 条件 a 5 and a 10 and b123, 这样的情况下是如何? 林老师给的回答是 A5 ,然后下推B123
小友 问 为什么不是先 进行范围查询,然后在索引下推 b123? 然后就没有然后了.... 说真的,不是我有意踩林老师, 我只是说MYSQL 45 讲吃个半饱, 大脑半醒半睡,好比晚上2点睡,早上被8点闹钟催醒. 上午在公司里梦游状态样.
极客这种课程,视乎给人感觉不全面,不细致.相对于等同价格的书来说,性价比太低了.
以前买了一本ORACLE ACE写的一本MYSQL入门的书.书中把BINLOG CACHE 归类于共享内存. 高鹏(八怪)说BINLOG CAHCE是线程的内存. ACE 看来就是个荣誉技术编辑总编.
MYSQL 产生大量数据的过程
我们做个实验,用上面链接的表和数据!
添加个组合索引 KEY idx_age_income_education (age,income_year,top_education) 我们还是先讲下索引下推是什么鬼? 在很早很早以前 MYSQL 分为一阴一阳两面. SERVER层负责阳的一面,引擎层负责阴的一面.
在这里我们记住一点就是服务层server负责过虑结果集, 只要执行计划有WHERE字眼,说明服务层执行了过滤操作, 另外ROWFILER % 也可以窥爱一下. 引擎层返回服务层要的数据! 一个SQL有多个WHERE 条件,我们看哪个条件能命中引擎层的二级索引. 我们就把这个条件传给引擎层.引擎层通过这个条件筛选数据,然后返回,服务层再用剩余的条件,进一步筛选过滤(FILTER)记录,积累到NET_BUF满后就发生给客户.
引擎层一般会预读,大约是100条件记录,然后一条,一条给服务层,服务层判断一条记录,再问引擎要一条.
上面一般过程,不必牢记! 重点是 为什么不把服务层过滤条件,全拿到引擎层做呢? 其实都是内存操作,在引擎层还是服务层差距不大.
那为什么要ICP呢? 所以重点是索引, 是服务层把更多的条件,下推到索引上.是引擎上的二级索引.
通过索引过滤掉更多不符合条件的记录. 这样减少去读聚集索引!
一般二级索引都被内存缓存,聚集索引相对较大,不易缓存在内存里.读聚集索引可能要发生IO操作. 能通过ICP优化,能更多减少不必要的IO操作! MYSQL 专业叫法是 读聚集索引, ORACLE 叫法是 回表! 回表和读聚集索引功能是类似的, 回表操作是直接从索引获得物理ID,直接定位到表具体行.而MYSQL读二级索引获得逻辑ID,还要通过主键聚集索引,根节点,分支节点,再到页节点,多了两次IO操作. 每个逻辑ID都要多两次IO操作. 比回表多了很多次IO操作.再说MYSQL是16K一个页,ORACLE是8K一个页. 优化思路是一样的,实现细节是有区别的. 算法一样,数据结构不一样. 作为MYSQL DBA. 如果还有OCP,COM,ACE头衔,自然不能说回表,太LOW! MySQL服务层负责SQL语法解析、生成执行计划等并调用存储引擎层去执行数据的存储和检索。
索引下推的下推其实就是指将部分上层服务层负责的事情交给了下层引擎层去处理。
我们来具体看一下在没有使用ICP的情况下MySQL的查询 存储引擎读取索引记录 根据索引中的主键值定位并读取完整的行记录 存储引擎把记录交给Server层去检测该记录是否满足WHERE条件。
使用ICP的情况下查询过程 存储引擎读取索引记录不是完整的行记录 判断WHERE条件部分能否用索引中的列来做检查条件不满足则处理下一行索引记录 条件满足使用索引中的主键去定位并读取完整的行记录就是所谓的回表 存储引擎把记录交给Server层Server层检测该记录是否满足WHERE条件的其余部分。
我们还可以看一下执行计划 看到Extra一列里Using index condition这就是用到了索引下推。 只能用于range、 ref、 eq_ref、ref_or_null访问方法 只能用于InnoDB和 MyISAM存储引擎及其分区表 对InnoDB存储引擎来说索引下推只适用于二级索引也叫辅助索引; 我们使用下面SQL 看下执行计划 根据上面说只要EXTAR using index condition 使用索引条件 这英文取得让人误会. 为啥不多加个单词using index pushdown condition
select * from dba_test.personal_identity_info where age 35 and income_year 10000 and income_year 20000 and top_education大学 ;
-- NO ICP key_len10 rows75 filtered8.28 ExtraUsing where select * from dba_test.personal_identity_info where age 35 and age 65;
-- ICP key_len1 rows206 filtered100 ExtraUsing index condition select * from dba_test.personal_identity_info where age 35 and age 65;
-- ICP key_len1 rows196 filtered100 ExtraUsing index condition select * from dba_test.personal_identity_info where age 35 and age 65 and top_education大学;
-- NO ICP key_len10 rows75 filtered19.6 ExtraUsing where select * from dba_test.personal_identity_info where age 35 and age 65 and income_year 10000 ;
-- ICP key_len1 rows196 filtered33.33 ExtraUsing index conditionselect * from dba_test.personal_identity_info where age 35 and age 65 and income_year 10000;
-- ICP key_len6 rows206 filtered33.33 ExtraUsing index conditionselect * from dba_test.personal_identity_info where age 35 and income_year 10000 and income_year 20000 and top_education大学 ;
-- ICP key_len6 rows1 filtered7.50 ExtraUsing index conditionselect * from dba_test.personal_identity_info where income_year 10000 and income_year 20000 and top_education大学 ;-- NO ICP key_len10 rows75 filtered11.11 ExtraUsing where
从上面八种情况,或许可以推导出,只要WHERE条件命中了组合索引第一个字段.
它一定会走索引! 其它条件命中组合索引其它字段,也能走索引.
ICP条件1:WHERE条件命中索引第一个字段.
ICP条件2:WHERE其它条件能命中组合索引其它字段,不过不能有等值查询
select * from dba_test.personal_identity_info where age 35 and age 65 and top_education大学;
-- NO ICP key_len10 rows75 filtered20.60 ExtraUsing where select * from dba_test.personal_identity_info where age between 35 and 65 and top_education大学;
-- NO ICP key_len10 rows75 filtered20.60 ExtraUsing where
另外两个情况下,还是其它WHERE条件命中组合索引且等值 ICP就失效
我的MYSQL 是 8.0.24. 索引下推是开启的
select optimizer_switch;
/*
index_mergeon,index_merge_unionon,index_merge_sort_unionon,
index_merge_intersectionon,engine_condition_pushdownon,
index_condition_pushdownon,mrron,
mrr_cost_basedon,block_nested_loopon,batched_key_accessoff,
materializationon,semijoinon,
loosescanon,firstmatchon,duplicateweedouton,
subquery_materialization_cost_basedon,
use_index_extensionson,condition_fanout_filteron,derived_mergeon,
use_invisible_indexesoff,skip_scanon,hash_joinon,
subquery_to_derivedoff,prefer_ordering_indexon,
hypergraph_optimizeroff,derived_condition_pushdownon
*/set optimizer_switchindex_condition_pushdownoff;
set optimizer_switchindex_condition_pushdownon;
我们还可以explain formattree 看的更清楚 explain FORMATtree select * from dba_test.personal_identity_info where age 35 and age 65 and top_education大学 and income_year 10000;
/*
- Filter: ((personal_identity_info.age 35) and (personal_identity_info.age 65) and (personal_identity_info.income_year 10000)) (cost7.24 rows5)- Index lookup on personal_identity_info using idx_personal_identity_info_top_education (top_education大学) (cost7.24 rows75)
*/ explain FORMATtree select * from dba_test.personal_identity_info where age 35 and age 65 and top_education大学 and income_year 10000;
/*
- Filter: ((personal_identity_info.age 35) and (personal_identity_info.age 65) and (personal_identity_info.income_year 10000)) (cost7.26 rows5)- Index lookup on personal_identity_info using idx_personal_identity_info_top_education (top_education大学) (cost7.26 rows75)
*/explain FORMATtree select * from dba_test.personal_identity_info where age 35 and income_year 10000 and income_year 20000 and top_education大学 ;
/*
- Filter: ((personal_identity_info.age 35) and (personal_identity_info.income_year 10000) and (personal_identity_info.income_year 20000)) (cost7.37 rows6)- Index lookup on personal_identity_info using idx_personal_identity_info_top_education (top_education大学) (cost7.37 rows75)
*/explain FORMATtree select * from dba_test.personal_identity_info where age 35 and income_year 10000 and income_year 20000 and top_education大学 ;
/*
- Index range scan on personal_identity_info using idx_age_income_education, with index condition: ((personal_identity_info.age 35) and (personal_identity_info.income_year 10000) and (personal_identity_info.income_year 20000) and (personal_identity_info.top_education 大学)) (cost0.71 rows1)
*/explain FORMATtree select * from dba_test.personal_identity_info where age 35 and age 65;
/*
- Index range scan on personal_identity_info using idx_age_income_education, with index condition: ((personal_identity_info.age 35) and (personal_identity_info.age 65)) (cost88.46 rows196)
*/
explain FORMATtree select * from dba_test.personal_identity_info where age 35 and income_year 10000 ;
/*
- Index range scan on personal_identity_info using idx_age_income_education, with index condition: ((personal_identity_info.age 35) and (personal_identity_info.income_year 10000)) (cost3.86 rows8)
*/
前三个没有下推,后三个下推了,从中可推导出,ICP可以推进多个条件.
另外 推导出
ICP条件3:WHER条件命中组合索引第一个字段且是等值也生效. 看起来条件2和条件3有点冲突,其实不冲突! 一般来说,命中索引的只有一个WHER条件.
这个经验来自ORACLE,MYSQL通过EXPLAIN FORMATTREE是看不出来的.
这样只能跟踪源码才可知,跟踪源码是件很累的事情,成本高收益低!
以上胡说八道 此刘老师,不是那个刘老师! 那个刘老师太那个了,200号人捐款4.2万.
说是他自己用个脚本换来的,然后捐给武汉.自己独占了荣誉.
也没感谢大家捐款,也没在公号列出感谢名单.培训也就是培训脚本
如何使用! 说白了就是PPT宣传你的脚本有多么多么厉害.
online脚本套用ORACLE官方脚本SQLHC.
好像 搞得大家200号人 没有良心没有善心,就冲着你的牛X脚本来的?
还搞个PDF污蔑我. 只能忽悠没有脑子的小年轻!
脚本有鸟用,谁敢把来历不明的脚本,用在生产环境中?
8千行再套用个SQLHC,我没有精力去分析代码,
早就扔在上上家公司的办公电脑里!
