mysql索和使用引的创建_mysql索引创建和使用细节

MySQL [test_db]> show create tabletest_usersG;*************************** 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.04 sec)

MySQL [test_db]> explain select * from test_users where user_id ="1273656";

+----+-------------+------------+------------+------+---------------+-----------+---------+-------+------+----------+-------+

| 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.05 sec)

MySQL [test_db]> explain select * from test_users where user_id =1273656;

+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+

| 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.04 sec)