mysql 索引创建_mysql索引创建和使用细节(一)

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语句读取和处理行，并且发送数据到客户端。因为这期间操作倾向于大量的磁盘访问(读取)，所以这常是整个查询周期中运行时间最长的阶段。