当前位置：网站首页>Mysql/stonedb - slow SQL - 2022-08-09 Q16 analysis

Mysql/stonedb - slow SQL - 2022-08-09 Q16 analysis

2022-08-10 00:32:00 【Emperor Zun Wu Shi】

摘要：

分析慢SQL-Q16

索引条件下推：

ICP(Index Condition Pushdown) - 索引条件下推

什么是ICP？

Why don't you ask another question,ICPWhat is the role of index conditional pushdown？ 一句话总结：索引条件下推ICP就是尽可量利用二级索引筛除不符合where条件的记录,如此一来减少需要回表继续判断的次数

With ICP enabled, and if parts of the WHERE condition can be evaluated by using only columns from the index, the MySQL server pushes this part of the WHERE condition down to the storage engine.The storage engine then evaluates the pushed index condition by using the index entry and only if this is satisfied is the row read from the table. ICP can reduce the number of times the storage engine must access the base table and the number of times the MySQL server must access the storage engine.

MySQL官方示例 - 初次体验ICP

示例如下,这个例子来自MySQL官方文档： Suppose：假设这个表有联合索引INDEX(zipcode, lastname, firstname)

SELECT * FROM people
  WHERE zipcode='95054'
  AND lastname LIKE '%etrunia%'
  AND address LIKE '%Main Street%';
  • 不用ICP,只使用最左匹配原则.那么只能使用联合索引的zipcode,回表记录不能有效去除.
  • 使用ICP,除了匹配zipcode的条件之外,额外匹配联合索引的lastname,看其是否符合where条件中的'%etrunia%',然后进行回表.如此一来,使用联合索引就可以尽可量排除不符合where条件的记录.这就是ICP优化的真谛. With Index Condition Pushdown, MySQL checks the lastname LIKE '%etrunia%' part before reading the full table row. This avoids reading full rows corresponding to index tuples that match the zipcode condition but not the lastname condition.

Why index pushdown should be used？
First join the indexidx_query(user_id, event_id, follower_id),The second search condition is  user_id in (...) and follower_id = 26407612.Fully indexable in unionidx_query上使用ICP,通过匹配user_id和follower_idBoth return to the table,Only used compared to the number of eligible recordsuser_idAfter filtering, the number of records returned to the table will be certain少很多.

但是根据explain的结果,Extra只有Using Where && key_len = 4（Note that only the first of the three fields of the joint index is useduser_id）The statement is only based onuser_id进行回表,因为每个用户user_id有非常多的follower_id,The records returned to the table will be very large,And so many records may be spread across multiple pages of the clustered index,这就是随机I/O啊.All at once the query statement becomes a slow query.

为什么没有使用？
按照对ICP的理解,It is to use the secondary index as much as possible to reduce the number of records returned to the table.在这个语句中,明明可以使用ICP,Why not use it？讲道理,It should be usedICP

DDL：

select
	p_brand,
	p_type,
	p_size,
	count(distinct ps_suppkey) as supplier_cnt
from
	partsupp,
	part
where
	p_partkey = ps_partkey
	and p_brand <> 'Brand#45'
	and p_type not like 'MEDIUM POLISHED%'
	and p_size in (49,
	14,
	23,
	45,
	19,
	3,
	36,
	9)
	and ps_suppkey not in (
	select
		s_suppkey
	from
		supplier
	where
		s_comment like '%Customer%Complaints%' )
group by
	p_brand,
	p_type,
	p_size
order by
	supplier_cnt desc,
	p_brand,
	p_type,
	p_size;

mysql> desc partsupp;
+---------------+---------------+------+-----+---------+-------+
| Field         | Type          | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| ps_partkey    | int(11)       | NO   | PRI | NULL    |       |
| ps_suppkey    | int(11)       | NO   | PRI | NULL    |       |
| ps_availqty   | int(11)       | NO   |     | NULL    |       |
| ps_supplycost | decimal(15,2) | NO   |     | NULL    |       |
| ps_comment    | varchar(199)  | NO   |     | NULL    |       |
+---------------+---------------+------+-----+---------+-------+
5 rows in set (0.01 sec)


mysql> desc part;
+---------------+---------------+------+-----+---------+-------+
| Field         | Type          | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| p_partkey     | int(11)       | NO   | PRI | NULL    |       |
| p_name        | varchar(55)   | NO   |     | NULL    |       |
| p_mfgr        | char(25)      | NO   |     | NULL    |       |
| p_brand       | char(10)      | NO   |     | NULL    |       |
| p_type        | varchar(25)   | NO   |     | NULL    |       |
| p_size        | int(11)       | NO   |     | NULL    |       |
| p_container   | char(10)      | NO   |     | NULL    |       |
| p_retailprice | decimal(15,2) | NO   |     | NULL    |       |
| p_comment     | varchar(23)   | NO   |     | NULL    |       |
+---------------+---------------+------+-----+---------+-------+
9 rows in set (0.00 sec)


mysql> desc supplier;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| s_suppkey   | int(11)       | NO   | PRI | NULL    |       |
| s_name      | char(25)      | NO   |     | NULL    |       |
| s_address   | varchar(40)   | NO   |     | NULL    |       |
| s_nationkey | int(11)       | NO   |     | NULL    |       |
| s_phone     | char(15)      | NO   |     | NULL    |       |
| s_acctbal   | decimal(15,2) | NO   |     | NULL    |       |
| s_comment   | varchar(101)  | NO   |     | NULL    |       |
+-------------+---------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

分析:

explain查询计划分析:

mysql> explain select
    -> p_brand,
    -> p_type,
    -> p_size,
    -> count(distinct ps_suppkey) as supplier_cnt
    -> from
    -> partsupp,
    -> part
    -> where
    -> p_partkey = ps_partkey
    -> and p_brand <> 'Brand#45'
    -> and p_type not like 'MEDIUM POLISHED%'
    -> and p_size in (49,
    -> 14,
    -> 23,
    -> 45,
    -> 19,
    -> 3,
    -> 36,
    -> 9)
    -> and ps_suppkey not in (
    -> select
    -> s_suppkey
    -> from
    -> supplier
    -> where
    -> s_comment like '%Customer%Complaints%' )
    -> group by
    -> p_brand,
    -> p_type,
    -> p_size
    -> order by
    -> supplier_cnt desc,
    -> p_brand,
    -> p_type,
    -> p_size\G
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    20
Current database: tpch

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: partsupp
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 8000000
     filtered: 100.00
        Extra: Using where with pushed condition (not(<in_optimizer>(`tpch`.`partsupp`.`ps_suppkey`,`tpch`.`partsupp`.`ps_suppkey` in ( <materialize> (/* select#2 */ select `tpch`.`supplier`.`s_suppkey` from `tpch`.`supplier` where (`tpch`.`supplier`.`s_comment` like '%Customer%Complaints%') ), <primary_index_lookup>(`tpch`.`partsupp`.`ps_suppkey` in <temporary table> on <auto_key> where ((`tpch`.`partsupp`.`ps_suppkey` = `materialized-subquery`.`s_suppkey`)))))))(t0) Pckrows: 2, susp. 2 (0 empty 0 full). Conditions: 1; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: part
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: tpch.partsupp.ps_partkey
         rows: 1
     filtered: 40.00
        Extra: Using where with pushed condition ((`tpch`.`part`.`p_brand` <> 'Brand#45') and (not((`tpch`.`part`.`p_type` like 'MEDIUM POLISHED%'))) and (`tpch`.`part`.`p_size` in (49,14,23,45,19,3,36,9)))(t0) Pckrows: 31, susp. 31 (0 empty 0 full). Conditions: 3
*************************** 3. row ***************************
           id: 2
  select_type: SUBQUERY
        table: supplier
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 100000
     filtered: 11.11
        Extra: Using where with pushed condition (`tpch`.`supplier`.`s_comment` like '%Customer%Complaints%')(t0) Pckrows: 2, susp. 2 (0 empty 0 full). Conditions: 1
3 rows in set, 1 warning (0.02 sec)

Conditional push up:
 


	Using
where
with pushed condition (not(<in_optimizer>(`tpch`.`partsupp`.`ps_suppkey`,
`tpch`.`partsupp`.`ps_suppkey` in ( <materialize> (/* select#2 */
select
	`tpch`.`supplier`.`s_suppkey`
from
	`tpch`.`supplier`
where
	(`tpch`.`supplier`.`s_comment` like '%Customer%Complaints%') ),
<primary_index_lookup>(`tpch`.`partsupp`.`ps_suppkey` in <temporary
table
	> on
	<auto_key>
where
	((`tpch`.`partsupp`.`ps_suppkey` = `materialized-subquery`.`s_suppkey`)))))))(t0) Pckrows: 2,
susp. 2 (0 empty 0 full). Conditions: 1;
	Using temporary;
	Using filesort

核心函数:

Tianmu::core::ColumnBinEncoder::EncoderText_UTF::Encode

调用堆栈:

(gdb) bt
#0  my_strnxfrm_unicode (cs=0x487dc20 <my_charset_utf8mb4_general_ci>, dst=0x7f4f8166be20 "", dstlen=16, nweights=16, 
    src=0x7f50256c3000 "Brand#13Brand#13Brand#42Brand#34Brand#32Brand#24Brand#11Brand#44Brand#43Brand#54Brand#25Brand#33Brand#55Brand#13Brand#15Brand#32Brand#43Brand#11Brand#23Brand#12Brand#33Brand#43Brand#35Brand#52Brand#55"..., srclen=8, flags=64) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119-20220805/strings/ctype-utf8.c:5146
#1  0x00000000030035e9 in Tianmu::common::strnxfrm (collation=..., src=0x7f4f8166be20 "", src_len=16, 
    dest=0x7f50256c3000 "Brand#13Brand#13Brand#42Brand#34Brand#32Brand#24Brand#11Brand#44Brand#43Brand#54Brand#25Brand#33Brand#55Brand#13Brand#15Brand#32Brand#43Brand#11Brand#23Brand#12Brand#33Brand#43Brand#35Brand#52Brand#55"..., dest_len=8) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119-20220805/storage/tianmu/common/mysql_gate.cpp:45
#2  0x00000000030273cd in Tianmu::core::ColumnBinEncoder::EncoderText_UTF::Encode (this=0x7f4f816c57f0, buf=0x7f4f8166be20 "", buf_sec=0x7f4f8166be6b "", vc=0x7f4f80a5a580, mit=..., 
    update_stats=false) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119-20220805/storage/tianmu/core/column_bin_encoder.cpp:941
#3  0x0000000003023785 in Tianmu::core::ColumnBinEncoder::Encode (this=0x7f4f814e4930, buf=0x7f4f8166be20 "", mit=..., alternative_vc=0x0, update_stats=false)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119-20220805/storage/tianmu/core/column_bin_encoder.cpp:169
#4  0x000000000300a2bb in Tianmu::core::GroupTable::PutGroupingValue (this=0x7f72adf6e2e8, col=0, mit=...)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119-20220805/storage/tianmu/core/group_table.h:85
#5  0x000000000300a629 in Tianmu::core::GroupByWrapper::PutGroupingValue (this=0x7f72adf6e220, gr_a=0, mit=...)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119-20220805/storage/tianmu/core/groupby_wrapper.h:82
#6  0x0000000003007254 in Tianmu::core::AggregationAlgorithm::AggregatePackrow (this=0x7f72adf6e580, gbw=..., mit=0x7f72adf6dee0, cur_tuple=0)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119-20220805/storage/tianmu/core/aggregation_algorithm.cpp:547
#7  0x0000000003005b74 in Tianmu::core::AggregationAlgorithm::MultiDimensionalGroupByScan (this=0x7f72adf6e580, gbw=..., [email protected]: 7422784, [email protected]: 0, sender=0x0, 
    limit_less_than_no_groups=false) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119-20220805/storage/tianmu/core/aggregation_algorithm.cpp:280
#8  0x00000000030053ca in Tianmu::core::AggregationAlgorithm::Aggregate (this=0x7f72adf6e580, just_distinct=false, [email protected]: -1, [email protected]: 0, sender=0x0)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119-20220805/storage/tianmu/core/aggregation_algorithm.cpp:196
#9  0x0000000002df1e3e in Tianmu::core::TempTable::Materialize (this=0x7f4f8165fd80, in_subq=false, sender=0x7f4f816b1cc0, lazy=false)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119-20220805/storage/tianmu/core/temp_table.cpp:1972
#10 0x0000000002d3a414 in Tianmu::core::Engine::Execute (this=0x6de5390, thd=0x7f4f800125f0, lex=0x7f4f80014918, result_output=0x7f4f80a3f3c0, unit_for_union=0x0)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119-20220805/storage/tianmu/core/engine_execute.cpp:426
#11 0x0000000002d395b6 in Tianmu::core::Engine::HandleSelect (this=0x6de5390, thd=0x7f4f800125f0, lex=0x7f4f80014918, [email protected]: 0x7f4f80a3f3c0, setup_tables_done_option=0, 
    [email protected]: 0, optimize_after_tia[email protected]: 1, [email protected]: 1, with_insert=0)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119-20220805/storage/tianmu/core/engine_execute.cpp:232
#12 0x0000000002e21e47 in Tianmu::dbhandler::TIANMU_HandleSelect (thd=0x7f4f800125f0, lex=0x7f4f80014918, [email protected]: 0x7f4f80a3f3c0, setup_tables_done_option=0, [email protected]: 0, 
    [email protected]: 1, [email protected]: 1, with_insert=0)
    at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119-20220805/storage/tianmu/handler/ha_rcengine.cpp:82
#13 0x0000000002462f6a in execute_sqlcom_select (thd=0x7f4f800125f0, all_tables=0x7f4f8000f548) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119-20220805/sql/sql_parse.cc:5182
#14 0x000000000245c2ee in mysql_execute_command (thd=0x7f4f800125f0, first_level=true) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119-20220805/sql/sql_parse.cc:2831
#15 0x0000000002463f33 in mysql_parse (thd=0x7f4f800125f0, parser_state=0x7f72adf6feb0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119-20220805/sql/sql_parse.cc:5621
#16 0x00000000024591cb in dispatch_command (thd=0x7f4f800125f0, com_data=0x7f72adf70650, command=COM_QUERY) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119-20220805/sql/sql_parse.cc:1495
#17 0x00000000024580f7 in do_command (thd=0x7f4f800125f0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119-20220805/sql/sql_parse.cc:1034
#18 0x000000000258accd in handle_connection (arg=0xcc94f80) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119-20220805/sql/conn_handler/connection_handler_per_thread.cc:313
#19 0x0000000002c71102 in pfs_spawn_thread (arg=0x7334f20) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119-20220805/storage/perfschema/pfs.cc:2197
#20 0x00007f72fcf95ea5 in start_thread () from /lib64/libpthread.so.0
#21 0x00007f72fb1cab0d in clone () from /lib64/libc.so.6

火焰图:

对比mysql/innodb:

火焰图:

 

原网站

版权声明
本文为[Emperor Zun Wu Shi]所创，转载请带上原文链接，感谢
https://yzsam.com/2022/222/202208092208124664.html

边栏推荐

猜你喜欢

随机推荐