当前位置：Gxlcms > 数据库问题 > mysql索引创建和使用细节

mysql索引创建和使用细节

时间：2021-07-01 10:21:17 帮助过：15人阅读

test_db]> show create table test_users\G; *************************** 1. row *************************** Table: test_users Create Table: CREATE TABLE `test_users` ( `uid` int(11) unsigned NOT NULL AUTO_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=utf8mb4 1 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)

可以发现第一条sql的 type = ref，key = testindex，第二条sql的 type = ALL，key = null也就是没用到任何索引而是全文扫描

未完待续...

mysql索引创建和使用细节

标签：partition 开发 from ssi 组合 pre pos 扫描 xpl

人气教程排行