MySQL [test_db]> show create tabletest_users\G;*************************** 1. row ***************************
Table: test_usersCreate Table: CREATE TABLE`test_users` (
`uid`int(11) unsigned NOT NULLAUTO_INCREMENT,
`username`char(15) NOT NULL,
`created_time`timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`user_id` char(11) NOT NULL DEFAULT '0',PRIMARY KEY(`uid`),KEY `testindex` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1306001 DEFAULT CHARSET=utf8mb41 row in set (0.04sec)
ERROR: No query specified
#开启profile
MySQL[test_db]> set profiling=1;
Query OK,0 rows affected, 1 warning (0.03sec)
#开始查询
MySQL[test_db]> select * from test_users where user_id='443587';
Emptyset (0.04sec)
MySQL[test_db]> select * from test_users where user_id=97737;
Emptyset (0.14sec)
#关闭profile
MySQL[test_db]> set profiling=0;
Query OK,0 rows affected, 1 warning (0.03sec)
#explain查看一下
MySQL[test_db]> explain select * from test_users where user_id='443587';+----+-------------+------------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | test_users | NULL | ref | testindex | testindex | 44 | const | 1 | 100.00 | NULL |
+----+-------------+------------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.05sec)
MySQL[test_db]> explain select * from test_users where user_id=97737;+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | test_users | NULL | ALL | testindex | NULL | NULL | NULL | 306078 | 10.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 3 warnings (0.04sec)
#以上可见当使用user_id匹配int类型时,key=null,索引失效
#再看profile分析结果,可见加单引号比起不加单引号快上10倍左右
MySQL[test_db]>show profiles;+----------+------------+-------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------------+
| 1 | 0.01234100 | select * from test_users where user_id='443587' |
| 2 | 0.10183000 | select * from test_users where user_id=97737 |
+----------+------------+-------------------------------------------------+
2 rows in set, 1 warning (0.04sec)
#再看更详细的分析
MySQL[test_db]> show profile cpu,block io,swaps for query 1;+----------------------+----------+----------+------------+--------------+---------------+-------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | Swaps |
+----------------------+----------+----------+------------+--------------+---------------+-------+
| starting | 0.000088 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| checking permissions | 0.000006 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| Opening tables | 0.000021 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| init | 0.003386 | 0.001000 | 0.000000 | 240 | 0 | 0 |
| System lock | 0.000027 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| optimizing | 0.000011 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| statistics | 0.007039 | 0.000000 | 0.000000 | 592 | 0 | 0 |
| preparing | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| executing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| Sending data | 0.001661 | 0.000000 | 0.000000 | 176 | 0 | 0 |
| end | 0.000008 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| query end | 0.000011 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| closing tables | 0.000012 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| freeing items | 0.000044 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| cleaning up | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | 0 |
+----------------------+----------+----------+------------+--------------+---------------+-------+
15 rows in set, 1 warning (0.03sec)
MySQL[test_db]> show profile cpu,block io,swaps for query 2;+----------------------+----------+----------+------------+--------------+---------------+-------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | Swaps |
+----------------------+----------+----------+------------+--------------+---------------+-------+
| starting | 0.000081 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| checking permissions | 0.000006 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| Opening tables | 0.000022 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| init | 0.002129 | 0.000000 | 0.000000 | 72 | 0 | 0 |
| System lock | 0.000010 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| optimizing | 0.000009 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| statistics | 0.000028 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| preparing | 0.000014 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| executing | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| Sending data | 0.099419 | 0.092986 | 0.000000 | 400 | 0 | 0 |
| end | 0.000016 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| query end | 0.000012 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| closing tables | 0.000026 | 0.001000 | 0.000000 | 0 | 0 | 0 |
| freeing items | 0.000054 | 0.000000 | 0.000000 | 0 | 0 | 0 |
| cleaning up | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | 0 |
+----------------------+----------+----------+------------+--------------+---------------+-------+
15 rows in set, 1 warning (0.04sec)
#通过对比可以发现主要耗时在sending data,而其他地方相差不大
#mysql官网对sending data对解释
#Sending data:The threadis reading and processing rows for a SELECT statement, and sending data to the client.
#Because operations occurring during this state tend to perform large amounts of disk access (reads), it is often the longest-running state over the lifetime ofa given query.
#大意即是:线程正在为一个select语句读取和处理行,并且发送数据到客户端。因为这期间操作倾向于大量的磁盘访问(读取),所以这常是整个查询周期中运行时间最长的阶段。