MySQL索引的一些小细

AsiaYe

发布于 2019-11-06 17:07:58

5300

发布于 2019-11-06 17:07:58

文章被收录于专栏：DBA随笔

MySQL索引的一些小细节

忙活了一天，晚上抽空做了几个关于索引和主键的小测试，记录一下，希望对大家有帮助。（所有测试都是在MySQL5.7的环境下测试的）

主键可不可以不设置not null和auto_increment?

看例子吧：

mysql>>create table index_test1(
    id int,
    name varchar(10),
    primary key (id));
Query OK, 0 rows affected (0.01 sec)

从结果来看，建表语句是可以的，但是当我们进行插入的时候，如果不指定id的值：

mysql>>insert into index_test1 (name) values  ('yeyz');  
ERROR 1364 (HY000): Field 'id' doesn't have a default value

不指定默认值，则会进行提醒。

如果不想要提醒，则只需要设置主键自增即可：

mysql>>create table index_test3 (
      id int auto_increment,
      name varchar(10) not null,
      primary key (id));
Query OK, 0 rows affected (0.01 sec)

mysql>>insert into index_test3 (name) values ('yeyz');
Query OK, 1 row affected (0.00 sec)

mysql>>insert into index_test3 (id,name) values (10,'yeyz');
Query OK, 1 row affected (0.00 sec)

mysql>>insert into index_test3 (name) values ('yyz');       
Query OK, 1 row affected (0.00 sec)

mysql>>select * from index_test3;
+----+------+
| id | name |
+----+------+
|  1 | yeyz |
| 10 | yeyz |
| 11 | yyz  |
+----+------+
3 rows in set (0.00 sec)

结论：

主键列可以不设置not null和auto_increment选项，但是这种情况下，插入不包含主键列的记录，会提示主键没有默认值。此时，需要单独提供主键的默认值。

如果主键列为int类型，并且包含auto_increment选项，则可以直接插入不包含主键列的记录。

主键为多个字段时，是否允许其中一个字段自增？

这个例子我看到结果的时候还是觉得挺神奇的，如下：

mysql>>create table index_test4 (
      id int auto_increment,
      name varchar(10) not null,
      primary key (name,id));
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

mysql>>create table index_test4 (
      id int auto_increment,
      name varchar(10) not null,
      primary key (id,name)); 
Query OK, 0 rows affected (0.02 sec)

当我们使用id和name两个字段作为主键的时候，如果id是自增列，那么设置主键为(name,id)则会报错，而设置主键为(id,name)则不会报错，这是一个很奇怪的现象。

然后，当我们进行数据插入的时候，如果只插入了name列，则主键列会自动补齐：

mysql >>insert into index_test4 (name) values ('yeyz');
Query OK, 1 row affected (0.00 sec)

mysql >>insert into index_test4 (id,name) values (2,'yeyz');
Query OK, 1 row affected (0.00 sec)

mysql--dba_admin@127.0.0.1:yeyztest 20:14:19>>select * from index_test4;
+----+------+
| id | name |
+----+------+
|  1 | yeyz |
|  2 | yeyz |
+----+------+
2 rows in set (0.00 sec)

如果只插入了主键列，则name列必须有默认值，没有默认值会报错，如下：

mysql >>insert into index_test4 (id) values (7);        
ERROR 1364 (HY000): Field 'name' doesn't have a default value

mysql >>alter table index_test4 modify name varchar(10) default 'abc';
Query OK, 5 rows affected (0.03 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql >>insert into index_test4 (id) values (7);                      
Query OK, 1 row affected (0.00 sec)

反向查找能用到索引么？

反向查找(!=)能用到索引么？答案是能。看例子：

mysql >>show create table index_test3\G
*************************** 1. row ***************************
       Table: index_test3
Create Table: CREATE TABLE `index_test3` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql >>select *from index_test3;
+----+------+
| id | name |
+----+------+
|  1 | yeyz |
| 10 | yeyz |
| 12 | yyz  |
+----+------+
3 rows in set (0.00 sec)

mysql >>explain select * from index_test3 where id!=1;
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table       | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | index_test3 | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    3 |   100.00 | Using where |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

可以看到，explain的结果中，type是range，则说明使用到了索引。

当字段有null值时，反向查找可能会得到错误结果

看看下面这个例子：

mysql >>select * from index_test3;
+----+------+
| id | name |
+----+------+
|  1 | yeyz |
|  3 | NULL |
|  4 | NULL |
|  5 | NULL |
|  6 | NULL |
| 10 | yeyz |
| 12 | yyz  |
| 13 | yeyz |
| 14 | yeyz |
| 15 | yyz  |
| 16 | yeyz |
| 17 | yeyz |
| 18 | yyz  |
| 19 | yeyz |
| 20 | yeyz |
| 21 | yyz  |
+----+------+
16 rows in set (0.00 sec)

mysql >>select *from index_test3 where name!='yeyz';
+----+------+
| id | name |
+----+------+
| 12 | yyz  |
| 15 | yyz  |
| 18 | yyz  |
| 21 | yyz  |
+----+------+
4 rows in set (0.00 sec)

这个查询，并没有过滤出来name的值为null的列。要想得到值为null的列，需要添加or这个关键字，如下：

mysql >>select *from index_test3 where name!='yeyz' or name is null;
+----+------+
| id | name |
+----+------+
|  3 | NULL |
|  4 | NULL |
|  5 | NULL |
|  6 | NULL |
| 12 | yyz  |
| 15 | yyz  |
| 18 | yyz  |
| 21 | yyz  |
+----+------+
8 rows in set (0.00 sec)

唯一索引包含null值的时候，还是会用到索引的。

废话不多说，直接看例子：

mysql >>show create table index_test6\G
*************************** 1. row ***************************
       Table: index_test6
Create Table: CREATE TABLE `index_test6` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql >>explain select * from index_test6 where id is null;
+----+-------------+-------------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | index_test6 | NULL       | ref  | id            | id   | 5       | const |    1 |   100.00 | Using index condition |
+----+-------------+-------------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

这里的index_test6包含唯一索引id，但是当我们使用id is null作为条件的时候，依旧使用到了索引。

最后，写一个需要注意的现象，当我们对唯一索引使用！=某一个特定值的方法进行匹配，不会使用索引，当唯一索引改为主键的时候，同样的语句就会使用到索引，还是以上面的index_test6为例，我们修改id列为主键，此时发现，同样的语句，就可以使用索引了，过程如下：

mysql >>show create table index_test6\G
*************************** 1. row ***************************
       Table: index_test6
Create Table: CREATE TABLE `index_test6` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql >>explain select * from index_test6 where id != 10;
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | index_test6 | NULL       | ALL  | id            | NULL | NULL    | NULL |   10 |    90.00 | Using where |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
#删除唯一索引，改为主键。
mysql >>alter table index_test6 modify id int not null auto_increment primary key;
Query OK, 10 rows affected (0.03 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql >>alter table index_test6 drop index `id`;     
Query OK, 10 rows affected (0.03 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql >>show create table index_test6\G
*************************** 1. row ***************************
       Table: index_test6
Create Table: CREATE TABLE `index_test6` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql >>explain select * from index_test6 where id != 10;
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table       | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | index_test6 | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    9 |   100.00 | Using where |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2019-10-30，如有侵权请联系 cloudcommunity@tencent.com 删除

云数据库 SQL Server