这篇文章主要介绍“MySQL特性有哪些”，在日常操作中，相信很多人在MySQL特性有哪些问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”MySQL特性有哪些”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

首先，咱们来看一下打开 ICP 与关闭 ICP 之间的性能区别，以下是测试过程：

准备数据：

createtableicp(idint,ageint,namevarchar(30),memovarchar(600))engine=innodb;altertableicpaddindexaind(age,name,memo);--let$i=100000while($i){--evalinsertintoicpvalues($i,1,'a$i',repeat('a$i',100))--dec$i}

PS: MySQL 有一个叫profile的东东，可以用来监视 SQL 语句在各个阶段的执行情况，咱们可以使用这个工具来观察 SQL 语句在各个阶段的运行情况，关于 profile 的详细说明可以参考官方文档。

打开 ICP 的性能测试：

setprofiling=on;setoptimizer_switch="index_condition_pushdown=on”;（defaultenabled）mysql>select*fromicpwhereage=999andnamelike'%999%';+------+------+------+------+|id|age|name|memo|+------+------+------+------+|NULL|999|999|999|+------+------+------+------+1rowinset(0.00sec)mysql>explainselect*fromicpwhereage=999andnamelike'%999%';+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+|1|SIMPLE|icp|ref|aind|aind|5|const|1|Usingindexcondition|+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+1rowinset(0.00sec)mysql>showprofiles;+----------+------------+-----------------------------------------------------------------+|Query_ID|Duration|Query|+----------+------------+-----------------------------------------------------------------+|1|0.00043550|select*fromicpwhereage=999andnamelike'%999%'||2|0.00043250|explainselect*fromicpwhereage=999andnamelike'%999%'|+----------+------------+-----------------------------------------------------------------+2rowsinset,1warning(0.00sec)mysql>showprofilecpu,blockioforquery2;+----------------------+----------+----------+------------+--------------+---------------+|Status|Duration|CPU_user|CPU_system|Block_ops_in|Block_ops_out|+----------------------+----------+----------+------------+--------------+---------------+|starting|0.000084|0.000000|0.000000|0|0||checkingpermissions|0.000011|0.000000|0.000000|0|0||Openingtables|0.000064|0.000000|0.000000|0|0||init|0.000046|0.000000|0.000000|0|0||Systemlock|0.000010|0.000000|0.000000|0|0||optimizing|0.000020|0.000000|0.000000|0|0||statistics|0.000082|0.000000|0.000000|0|0||preparing|0.000022|0.000000|0.000000|0|0||explaining|0.000021|0.000000|0.000000|0|0||queryend|0.000008|0.000000|0.000000|0|0||closingtables|0.000022|0.000000|0.000000|0|0||freeingitems|0.000031|0.000000|0.000000|0|0||cleaningup|0.000013|0.000000|0.000000|0|0|+----------------------+----------+----------+------------+--------------+---------------+13rowsinset,1warning(0.00sec)mysql>showsessionstatuslike'%handler%';+----------------------------+-------+|Variable_name|Value|+----------------------------+-------+|Handler_commit|2||Handler_delete|0||Handler_discover|0||Handler_external_lock|4||Handler_mrr_init|0||Handler_prepare|0||Handler_read_first|0||Handler_read_key|1||Handler_read_last|0||Handler_read_next|1||Handler_read_prev|0||Handler_read_rnd|0||Handler_read_rnd_next|42||Handler_rollback|0||Handler_savepoint|0||Handler_savepoint_rollback|0||Handler_update|0||Handler_write|39|+----------------------------+-------+18rowsinset(0.00sec)

关闭 ICP 的性能测试：

mysql>setoptimizer_switch="index_condition_pushdown=off”;mysql>select*fromicpwhereage=1andmemolike'%9999%';mysql>select*fromicpwhereage=999andnamelike'%999%';+------+------+------+------+|id|age|name|memo|+------+------+------+------+|NULL|999|999|999|+------+------+------+------+1rowinset(0.00sec)mysql>explainselect*fromicpwhereage=999andnamelike'%999%';+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+|1|SIMPLE|icp|ref|aind|aind|5|const|1|Usingwhere|+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+1rowinset(0.00sec)mysql>showprofiles;+----------+------------+-----------------------------------------------------------------+|Query_ID|Duration|Query|+----------+------------+-----------------------------------------------------------------+|1|0.00043550|select*fromicpwhereage=999andnamelike'%999%'||2|0.00043250|explainselect*fromicpwhereage=999andnamelike'%999%'||3|0.00081350|showsessionstatuslike'%handler%'||4|0.00010350|setoptimizer_switch="index_condition_pushdown=off"||5|0.00036525|select*fromicpwhereage=999andnamelike'%999%'||6|0.00032950|explainselect*fromicpwhereage=999andnamelike'%999%'|+----------+------------+-----------------------------------------------------------------+6rowsinset,1warning(0.00sec)mysql>showprofilecpu,blockioforquery5;+----------------------+----------+----------+------------+--------------+---------------+|Status|Duration|CPU_user|CPU_system|Block_ops_in|Block_ops_out|+----------------------+----------+----------+------------+--------------+---------------+|starting|0.000068|0.000000|0.000000|0|0||checkingpermissions|0.000007|0.000000|0.000000|0|0||Openingtables|0.000020|0.000000|0.000000|0|0||init|0.000032|0.000000|0.000000|0|0||Systemlock|0.000010|0.000000|0.000000|0|0||optimizing|0.000015|0.000000|0.000000|0|0||statistics|0.000088|0.000000|0.000000|0|0||preparing|0.000017|0.000000|0.000000|0|0||executing|0.000003|0.000000|0.000000|0|0||Sendingdata|0.000049|0.000000|0.000000|0|0||end|0.000005|0.000000|0.000000|0|0||queryend|0.000007|0.000000|0.000000|0|0||closingtables|0.000008|0.000000|0.000000|0|0||freeingitems|0.000024|0.000000|0.000000|0|0||cleaningup|0.000014|0.000000|0.000000|0|0|+----------------------+----------+----------+------------+--------------+---------------+15rowsinset,1warning(0.00sec)mysql>showsessionstatuslike'%handler%';+----------------------------+-------+|Variable_name|Value|+----------------------------+-------+|Handler_commit|4||Handler_delete|0||Handler_discover|0||Handler_external_lock|8||Handler_mrr_init|0||Handler_prepare|0||Handler_read_first|0||Handler_read_key|2||Handler_read_last|0||Handler_read_next|2||Handler_read_prev|0||Handler_read_rnd|0||Handler_read_rnd_next|58||Handler_rollback|0||Handler_savepoint|0||Handler_savepoint_rollback|0||Handler_update|0||Handler_write|54|+----------------------------+-------+18rowsinset(0.00sec)

测试结论：由以上测试情况可以看到，在二级索引是复合索引且前面的条件过滤性较低的情况下，打开 ICP 可以有效的降低 server 层和 engine 层之间交互的次数，从而有效的降低在运行时间。

5.6 之前，在 SQL 语句的执行过程中，server 层通过 engine 的 api 获取数据，然后再进行 where_cond 的判断（具体判断逻辑在:evaluate_join_record），每一条数据都需要从engine层返回server层做判断。我们回顾一下上面把 ICP 关掉的测试，可以看到Handler_read_next的值陡增，其原因是第 1 个字段区分度不高，且 memo 字段无法使用索引，造成了类似 index 扫描的的情况，性能较低。

5.6 之后，在利用索引扫描的过程中，如果发现 where_cond 中含有这个 index 相关的条件，则将此条件记录在 handler 接口中，在索引扫描的过程中，只有满足索引与handler接口的条件时，才会返回到 server 层做进一步的处理，在前缀索引区分度不够，其它字段区分度高的情况下可以有效的减少 server & engine之间的开销，提升查询性能。

我们在上小节提到，index condition down 所用的条件是记在handler接口中的，咱们分析一下“记录”的过程是如何实现的。

首先，优化器计算代价后会生成一个 JOIN_TAB 的左支树，每一个 JOIN_TAB 包含相关表的指针、表的读取方式、访问表所包含的索引等信息，优化器会在make_join_readinfo中对JOIN_TAB中表的访问方式进行相应的修正，并进一步将 where cond 中和索引相关的条件记录到 table 的句柄中，堆栈如下：

#0make_cond_for_index(cond=0x2b69680179e8,table=0x2b6968012100,keyno=0,other_tbls_ok=true)#1inpush_index_cond(tab=0x2b696802aa48,keyno=0,other_tbls_ok=true,trace_obj=0x2b696413ec30)#2inmake_join_readinfo(join=0x2b6968017db0,options=0,no_jbuf_after=4294967295)#3inJOIN::optimize(this=0x2b6968017db0)#4inmysql_execute_select(thd=0x3176760,select_lex=0x3179470,free_join=true)

其次，make_cond_for_index是一个递归的过程，对 where_cond中的每一个条件进行判断，对满足条件的 cond 重新组合成一个新的cond，最后将新的 cond 挂在table->file 下面（table->file 指的是操作物理表的接口函数，此变量为thd下私有的，不共享，共享的是tab->table->s），详细参考make_cond_for_index的详细实现，设置的堆栈如下：

#0ha_innobase::idx_cond_push(this=0x2b696800e810,keyno=0,idx_cond=0x2b69680179e8)#10x0000000000a60a55inpush_index_cond(tab=0x2b696802aa48,keyno=0,other_tbls_ok=true,trace_obj=0x2b696413ec30)#20x0000000000a6362finmake_join_readinfo(join=0x2b6968017db0,options=0,no_jbuf_after=4294967295)#30x0000000000d9b8bdinJOIN::optimize(this=0x2b6968017db0#40x0000000000a5b9aeinmysql_execute_select(thd=0x3176760,select_lex=0x3179470,free_join=true)

再次，server 层根据生成的 JOIN_TAB 读取engine层的内容，在engine读取的时候，会进行index_condition_pushdown的调用，即 ICP 的调用，堆栈如下：

#0Item_func_like::val_int(this=0x2b6978005a28)#10x0000000001187b66ininnobase_index_cond(file=0x2b696800e810)#20x0000000001393566inrow_search_idx_cond_check(mysql_rec=0x2b69680129f0<incompletesequence=""\361>,prebuilt=0x2b69680130f8,rec=0x2b692b56e4cf"\200",offsets=0x2b697008d450)#30x0000000001397e2binrow_search_for_mysql(buf=0x2b69680129f0<incompletesequence=""\361>,mode=2,prebuilt=0x2b69680130f8,match_mode=1,direction=0)#40x00000000011696b9inha_innobase::index_read(this=0x2b696800e810,buf=0x2b69680129f0<incompletesequence=""\361>,key_ptr=0x2b697800a660"",key_len=5,find_flag=HA_READ_KEY_EXACT)#50x00000000006ecc58inhandler::index_read_map(this=0x2b696800e810,buf=0x2b69680129f0<incompletesequence=""\361>,key=0x2b697800a660"",keypart_map=1,find_flag=HA_READ_KEY_EXACT)#60x00000000006d6bb4inhandler::ha_index_read_map(this=0x2b696800e810,buf=0x2b69680129f0<incompletesequence=""\361>,key=0x2b697800a660"",keypart_map=1,find_flag=HA_READ_KEY_EXACT)#70x00000000009a1870injoin_read_always_key(tab=0x2b697800a1b8)#80x000000000099d480insub_select(join=0x2b6978005df0,join_tab=0x2b697800a1b8,end_of_records=false)#90x000000000099c6c0indo_select(join=0x2b6978005df0)#100x00000000009980a4inJOIN::exec(this=0x2b6978005df0)#110x0000000000a5bac0inmysql_execute_select(thd=0x32801a0,select_lex=0x3282eb0,free_join=true)

可见在 ICP 的判断是调用相关item的函数的，虽然同是调用 server 层的函数，但是没有 ICP 的调用需要根据主建找到记录，然后再匹配，而有了 ICP 可以省略一次主键查找数据的过程，进而提升效率。

只支持 select 语句；

5.6 中只支持 MyISAM 与 InnoDB 引擎;

ICP的优化策略可用于range、ref、eq_ref、ref_or_null 类型的访问数据方法；

不支持主建索引的 ICP；

当 SQL 使用覆盖索引时但只检索部分数据时，ICP 无法使用，详细的分析可以参考bug#68554中 Olav Sandst?的分析，代码实现部分可以参考make_join_readinfo；

在查询的时候即使正确的使用索引的前Ｎ个字段（即遵循前缀索引的原则），还是会用到 ICP，无故的多了 ICP 相关的判断，这应该是一个退化的问题，例：

mysql>explainselect*fromicpwhereage=999andnamelike'999%';+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+|1|SIMPLE|icp|range|aind|aind|98|NULL|1|Usingindexcondition|+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+1rowinset(0.00sec)

PS: engine condition pushdown 是 NDB 使用的，其它引擎不支持。



补充：

如：

root@read02:28:07>show status like ‘Handler_read%’;
+———————–+——-+
| Variable_name | Value |
+———————–+——-+
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 61 |
+———————–+——-+
6 rows in set (0.41 sec)

Handler_read_first 代表读取索引头的次数，如果这个值很高，说明全索引扫描很多。

Handler_read_key代表一个索引被使用的次数，如果我们新增加一个索引，可以查看Handler_read_key是否有增加，如果有增加，说明sql用到索引。

Handler_read_next 代表读取索引的下列，一般发生range scan。

Handler_read_prev 代表读取索引的上列，一般发生在ORDER BY … DESC。

Handler_read_rnd 代表在固定位置读取行，如果这个值很高，说明对大量结果集进行了排序、进行了全表扫描、关联查询没有用到合适的KEY。

Handler_read_rnd_next 代表进行了很多表扫描，查询性能低下。

到此，关于“MySQL特性有哪些”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！