共计 6665 个字符，预计需要花费 17 分钟才能阅读完成。
这篇文章主要介绍“MySQL 特性有哪些”，在日常操作中，相信很多人在 MySQL 特性有哪些问题上存在疑惑，丸趣 TV 小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”MySQL 特性有哪些”的疑惑有所帮助！接下来，请跟着丸趣 TV 小编一起来学习吧！
ICP 测试
首先，咱们来看一下打开 ICP 与关闭 ICP 之间的性能区别，以下是测试过程：
准备数据：
create table icp(id int, age int, name varchar(30), memo varchar(600)) engine=innodb; alter table icp add index aind(age, name, memo); --let $i= 100000 while ($i)
{ --eval insert into icp values($i, 1, a$i , repeat( a$i , 100)) --dec $i }PS: MySQL 有一个叫 profile 的东东，可以用来监视 SQL 语句在各个阶段的执行情况，咱们可以使用这个工具来观察 SQL 语句在各个阶段的运行情况，关于 profile 的详细说明可以参考官方文档。
打开 ICP 的性能测试：
set profiling=on;
set optimizer_switch= index_condition_pushdown=on”; （default enabled）mysql select * from icp where age = 999 and name like %999%
+------+------+------+------+
| id | age | name | memo |
+------+------+------+------+
| NULL | 999 | 999 | 999 |
+------+------+------+------+
1 row in set (0.00 sec)
mysql explain select * from icp where age = 999 and name like %999%
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
| 1 | SIMPLE | icp | ref | aind | aind | 5 | const | 1 | Using index condition |
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
mysql show profiles;
+----------+------------+-----------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------------------------------------------------+
| 1 | 0.00043550 | select * from icp where age = 999 and name like %999% |
| 2 | 0.00043250 | explain select * from icp where age = 999 and name like %999% |
+----------+------------+-----------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
mysql show profile cpu,block io for query 2;
+----------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000084 | 0.000000 | 0.000000 | 0 | 0 |
| checking permissions | 0.000011 | 0.000000 | 0.000000 | 0 | 0 |
| Opening tables | 0.000064 | 0.000000 | 0.000000 | 0 | 0 |
| init | 0.000046 | 0.000000 | 0.000000 | 0 | 0 |
| System lock | 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 |
| query end | 0.000008 | 0.000000 | 0.000000 | 0 | 0 |
| closing tables | 0.000022 | 0.000000 | 0.000000 | 0 | 0 |
| freeing items | 0.000031 | 0.000000 | 0.000000 | 0 | 0 |
| cleaning up | 0.000013 | 0.000000 | 0.000000 | 0 | 0 |
+----------------------+----------+----------+------------+--------------+---------------+
13 rows in set, 1 warning (0.00 sec)
mysql show session status like %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 |
+----------------------------+-------+
18 rows in set (0.00 sec)关闭 ICP 的性能测试：
mysql set optimizer_switch= index_condition_pushdown=off”;
mysql select * from icp where age = 1 and memo like %9999%
mysql select * from icp where age = 999 and name like %999%
+------+------+------+------+
| id | age | name | memo |
+------+------+------+------+
| NULL | 999 | 999 | 999 |
+------+------+------+------+
1 row in set (0.00 sec)
mysql explain select * from icp where age = 999 and name like %999%
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | icp | ref | aind | aind | 5 | const | 1 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)
mysql show profiles;
+----------+------------+-----------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------------------------------------------------+
| 1 | 0.00043550 | select * from icp where age = 999 and name like %999% |
| 2 | 0.00043250 | explain select * from icp where age = 999 and name like %999% |
| 3 | 0.00081350 | show session status like %handler% |
| 4 | 0.00010350 | set optimizer_switch= index_condition_pushdown=off |
| 5 | 0.00036525 | select * from icp where age = 999 and name like %999% |
| 6 | 0.00032950 | explain select * from icp where age = 999 and name like %999% |
+----------+------------+-----------------------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)
mysql show profile cpu,block io for query 5;
+----------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000068 | 0.000000 | 0.000000 | 0 | 0 |
| checking permissions | 0.000007 | 0.000000 | 0.000000 | 0 | 0 |
| Opening tables | 0.000020 | 0.000000 | 0.000000 | 0 | 0 |
| init | 0.000032 | 0.000000 | 0.000000 | 0 | 0 |
| System lock | 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 |
| Sending data | 0.000049 | 0.000000 | 0.000000 | 0 | 0 |
| end | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
| query end | 0.000007 | 0.000000 | 0.000000 | 0 | 0 |
| closing tables | 0.000008 | 0.000000 | 0.000000 | 0 | 0 |
| freeing items | 0.000024 | 0.000000 | 0.000000 | 0 | 0 |
| cleaning up | 0.000014 | 0.000000 | 0.000000 | 0 | 0 |
+----------------------+----------+----------+------------+--------------+---------------+
15 rows in set, 1 warning (0.00 sec)
正文完
