38°

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

  上篇粗略记录当mysql字段类型是string,传入int类型参数后失效当问题。

  现在测试下mysql字段是int类型,传参string类型会发生什么。  

  题外话,最近膝盖手术后还在家养伤中,只怪自己以前骑车不注意休息保养,经常长途骑行出去玩,把膝盖骑费了(抽取积液+切除膝盖囊肿手术),搞得现在哪都去不了,已经一周没下楼走走。

【索引失效】

二. 单字段索引:字段是INT类型,传入string类型参数

MySQL [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` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`uid`),
  KEY `testindex` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1306001 DEFAULT CHARSET=utf8mb4
1 row in set (0.05 sec)

ERROR: No query specified

#开启profile MySQL [test_db]> set profiling =1; Query OK, 0 rows affected, 1 warning (0.03 sec)

MySQL [test_db]> select * from test_users where user_id = '930324'; Empty set (0.03 sec)

MySQL [test_db]> select * from test_users where user_id = 899242; Empty set (0.03 sec)

 MySQL [test_db]> set profiling=0;
  Query OK, 0 rows affected, 1 warning (0.04 sec)

MySQL [test_db]> show profiles;
+----------+------------+---------------------------------------------------+
| Query_ID | Duration   | Query                                             |
+----------+------------+---------------------------------------------------+
|        1 | 0.00034000 | select * from test_users where user_id = '930324' |
|        2 | 0.00034850 | select * from test_users where user_id = 899242   |
+----------+------------+---------------------------------------------------+
2 rows in set, 1 warning (0.04 sec)
#可以看到两种查询耗时基本持平 MySQL
[test_db]> explain select * from test_users where user_id = 899242; +----+-------------+------------+------------+------+---------------+-----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+---------------+-----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | test_users | NULL | ref | testindex | testindex | 4 | const | 1 | 100.00 | NULL | +----+-------------+------------+------------+------+---------------+-----------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.03 sec)

MySQL [test_db]> explain select * from test_users where user_id = '899242'; +----+-------------+------------+------------+------+---------------+-----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+---------------+-----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | test_users | NULL | ref | testindex | testindex | 4 | const | 1 | 100.00 | NULL | +----+-------------+------------+------------+------+---------------+-----------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.04 sec) #再用explain分析下,可见两种查询都是key=testindex

 

 上面针对的都是单字段索引,现在我们使用组合索引,对比下会有什么不一样。

 三. 组合索引

MySQL [test_db]> show create table test_log\G;
*************************** 1. row ***************************
       Table: test_log
Create Table: CREATE TABLE `test_log` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `log_id` int(11) unsigned NOT NULL DEFAULT '0',
  `rand_name` char(15) NOT NULL,
  `created_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `country` varchar(50) NOT NULL DEFAULT '',
  `short_country_name` char(5) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `c` (`country`),
  KEY `log` (`log_id`,`rand_name`,`country`)
) ENGINE=InnoDB AUTO_INCREMENT=6601004 DEFAULT CHARSET=utf8mb4
1 row in set (0.04 sec)

ERROR: No query specified

MySQL [test_db]> set profiling=1; Query OK, 0 rows affected, 1 warning (0.03 sec)

MySQL [test_db]> select * from test_log where log_id = '66423'; Empty set (0.04 sec)

MySQL [test_db]> select * from test_log where log_id = 987371; +--------+--------+--------------+---------------------+--------------------+--------------------+ | id | log_id | rand_name | created_time | country | short_country_name | +--------+--------+--------------+---------------------+--------------------+--------------------+ | 948373 | 987371 | 1ae53be9c1df | 2020-01-16 12:01:09 | 中国澳门特区 | MO | +--------+--------+--------------+---------------------+--------------------+--------------------+ 1 row in set (0.04 sec)

MySQL [test_db]> set profiling =0; Query OK, 0 rows affected, 1 warning (0.03 sec)

MySQL [test_db]> show profiles; +----------+------------+---------------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+---------------------------------------------------+ | 1 | 0.00034000 | select from test_users where user_id = '930324' | | 2 | 0.00034850 | select from test_users where user_id = 899242 | | 3 | 0.00464450 | select from test_log where log_id = '66423' | | 4 | 0.01399875 | select from test_log where log_id = 987371 | +----------+------------+---------------------------------------------------+ 4 rows in set, 1 warning (0.03 sec) #有没有发现什么不对劲的地方? #没错 该组合索引里面 log_id是int类型,string类型参数比int类型参数快,到底哪里出问题?

#explain分析下 MySQL [test_db]> explain select * from test_log where log_id = '66423'; +----+-------------+----------+------------+------+---------------+------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------+---------+-------+------+----------+-------+ | 1 | SIMPLE | test_log | NULL | ref | log | log | 4 | const | 1 | 100.00 | NULL | +----+-------------+----------+------------+------+---------------+------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.05 sec)

MySQL [test_db]> explain select * from test_log where log_id = 987371; +----+-------------+----------+------------+------+---------------+------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------+---------+-------+------+----------+-------+ | 1 | SIMPLE | test_log | NULL | ref | log | log | 4 | const | 1 | 100.00 | NULL | +----+-------------+----------+------------+------+---------------+------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.03 sec) #explain得到的结果都是一模一样!!! 而sql本身没什么区别只是一个查到数据一个没查到数据,那我再改下sql,让加单引号的能查到结果试试

MySQL [test_db]> set profiling = 1; Query OK, 0 rows affected, 1 warning (0.03 sec)

MySQL [test_db]> select * from test_log where log_id = '3642103'; +---------+---------+--------------+---------------------+--------------------+--------------------+ | id | log_id | rand_name | created_time | country | short_country_name | +---------+---------+--------------+---------------------+--------------------+--------------------+ | 3593106 | 3642103 | 37c0a4d8da6b | 2020-01-17 13:06:33 | 中国澳门特区 | MO | +---------+---------+--------------+---------------------+--------------------+--------------------+ 1 row in set (0.05 sec)

MySQL [test_db]> set profiling =0; Query OK, 0 rows affected, 1 warning (0.03 sec) MySQL [test_db]> show profiles; +----------+------------+---------------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+---------------------------------------------------+ | 1 | 0.00034000 | select from test_users where user_id = '930324' | | 2 | 0.00034850 | select from test_users where user_id = 899242 | | 3 | 0.00464450 | select from test_log where log_id = '66423' | | 4 | 0.01399875 | select from test_log where log_id = 987371 | | 5 | 0.01888925 | select from test_log where log_id = '3642103' | +----------+------------+---------------------------------------------------+ 5 rows in set, 1 warning (0.03 sec)
#耗时差别不大,我们猜测一下这里mysql默认将string类型转换成int类型了 #不相信的话,我们来验证下就知道了
MySQL
[test_db]> select
from test_log where log_id = 'a666f'; Empty set (0.04 sec)

MySQL [test_db]> select * from test_log limit 1; +----+--------+--------------+---------------------+--------------------------------+--------------------+ | id | log_id | rand_name | created_time | country | short_country_name | +----+--------+--------------+---------------------+--------------------------------+--------------------+ | 1 | 1 | c4ca4238a0b9 | 2020-01-16 11:54:25 | 中立区(沙特-伊拉克间) | NT | +----+--------+--------------+---------------------+--------------------------------+--------------------+ 1 row in set (0.04 sec)

MySQL [test_db]> update test_log set log_id=0 where id = 1; Query OK, 1 row affected (0.06 sec) Rows matched: 1 Changed: 1 Warnings: 0

MySQL [test_db]> select * from test_log where log_id = 'a666f'; +----+--------+--------------+---------------------+--------------------------------+--------------------+ | id | log_id | rand_name | created_time | country | short_country_name | +----+--------+--------------+---------------------+--------------------------------+--------------------+ | 1 | 0 | c4ca4238a0b9 | 2020-01-16 11:54:25 | 中立区(沙特-伊拉克间) | NT | +----+--------+--------------+---------------------+--------------------------------+--------------------+ 1 row in set (0.03 sec)

本文转载自博客园,原文链接:https://www.cnblogs.com/wscsq789/p/12208725.html

全部评论: 0

    我有话说: