Loading [MathJax]/jax/input/TeX/config.js
首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >专栏 >MySQL索引的一些小细

MySQL索引的一些小细

作者头像
AsiaYe
发布于 2019-11-06 09:07:58
发布于 2019-11-06 09:07:58
56600
代码可运行
举报
文章被收录于专栏:DBA随笔DBA随笔
运行总次数:0
代码可运行

//

MySQL索引的一些小细节

//

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

1

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

看例子吧:

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
mysql>>create table index_test1(
    id int,
    name varchar(10),
    primary key (id));
Query OK, 0 rows affected (0.01 sec)

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

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
mysql>>insert into index_test1 (name) values  ('yeyz');  
ERROR 1364 (HY000): Field 'id' doesn't have a default value

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

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

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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选项,则可以直接插入不包含主键列的记录。

2

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

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

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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列,则主键列会自动补齐:

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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列必须有默认值,没有默认值会报错,如下:

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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)

3

反向查找能用到索引么?

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

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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,则说明使用到了索引。

4

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

看看下面这个例子:

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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这个关键字,如下:

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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)

5

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

废话不多说,直接看例子:

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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列为主键,此时发现,同样的语句,就可以使用索引了,过程如下:

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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 删除

本文分享自 DBA随笔 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
暂无评论
推荐阅读
编辑精选文章
换一批
MYSQL 性能优化 index 函数,隐藏,前缀,hash 索引 使用方法(2)
接着上期说,MYSQL 8 的索引的方式相对MYSQL 5.7 有了很多的进步,其中最突出的就是添加的functional indexes , 那么这个功能到底是什么时候开始的有的。
AustinDatabases
2022/07/13
6050
MYSQL 性能优化  index  函数,隐藏,前缀,hash 索引 使用方法(2)
MySQL之索引
#alter添加语法:alter table 表名 add primary key(列名)
老油条IT记
2020/03/22
7160
MySQL 8.0 新特性的一些典型应用场景
测试机器:本地虚拟机 测试表 sys_test,数据行 780w 测试实例1:8.0.30 测试实例2:5.7.20
DBA札记
2023/08/04
5070
MySQL 8.0 新特性的一些典型应用场景
Mysql介绍
MySQL是一个关系型数据库管理系统,由瑞典MySQL AB 公司开发,目前属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一,在 WEB 应用方面,MySQL是最好的 RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件。 MySQL使用 C和 C++编写,并使用了多种编译器进行测试,保证了源代码的可移植性。 提供 TCP/IP、ODBC 和 JDBC等多种数据库连接途径。 MySQL 是开源的,所以不需要支付费用。 原生JSON支持(5.7 新增) 企业级的应用支持。
全栈程序员站长
2022/08/04
6450
Mysql介绍
MySQL避免插入重复记录的方法
mysql在存在主键冲突或者唯一键冲突的情况下,根据插入策略不同,一般有以下三种避免方法。 1、insert ignore 2、replace into 3、insert on duplicate key update
IT工作者
2022/01/26
2.6K0
浅析MySQL存储引擎序列属性
墨墨导读:为了达到标识的目的,许多应用程序需要生成唯一编号,比如:商品编号、交易流水号等。MySQL数据库同样能够支持这样的需求场景,AUTO_INCREMENT就是为MySQL实现序列的方式,它会自动生成序列编号。
数据和云
2020/09/14
1.6K0
MySQL 8.0 之不可见列
可以看到,我们的SQL里面创建了一个表t2的字段有id、name和age,其中,age字段设置了不可见属性。
AsiaYe
2021/06/09
1.6K0
MySQL 8.0 之不可见列
一道经典的MySQL面试题,答案出现三次反转
前几天偶然看到大家在讨论一道面试题,而且答案也不够统一,我感觉蛮有意思,在此就做一个解读,整个过程中确实会有几处反转。
jeanron100
2019/10/23
7340
一道经典的MySQL面试题,答案出现三次反转
MySQL基本操作
表是数据库存储数据的基本单位,由若干个字段组成,主要用来存储数据记录。表的操作 包括创建表、查看表、修改表和删除表。
星哥玩云
2022/08/18
2.1K0
MySQL基本操作
MySQL发号问题的分析和改进
关于发号器的使用,其实有一个大背景,那就是关于主键的一些设计问题,在MySQL中如果一张表没有主键,实际的数据处理就有点麻烦了。
jeanron100
2020/07/28
6560
再谈表的约束
auto_increment:当对应的字段,不给值,会自动的被系统触发,系统会从当前字段中已经有的最大值+1操作,得到一个新的不同的值。通常和主键搭配使用,作为逻辑主键。 自增长的特点:
南桥
2024/08/15
1510
再谈表的约束
MySQL中的全表扫描案例
这两天看到了两种可能会导致全表扫描的sql,这里给大家看一下,希望可以避免踩坑:
AsiaYe
2019/11/06
2.9K0
死锁案例--(添加联合索引和复合索引,以及添加普通索引使其走二级索引)
表锁是MySQL中最基本的锁策略,并且是开销最小的策略。表锁会锁定整张数据表,用户的写操作(插入/删除/更新)前,都需要获取写锁(写锁会相互阻塞);没有写锁时,读取用户才能获取读锁(读锁不会相互阻塞)。
用户14527
2022/07/03
2K0
死锁案例--(添加联合索引和复合索引,以及添加普通索引使其走二级索引)
Mysql 怎么产生隐藏主键 和 还要不要学MySQL
MySQL 要不要学的这个问题,回答是一定要学,继续学,哪怕不用。实际上最近有人已经问了这个问题了,还有人问ORACLE 要不要学的问题,我觉得这个些提问题的人,很奇怪,如果有觉得你有更值得要学的数据库,马上要用的数据库可以去学,没有必要问,ORACLE,MYSQL要不要学,你问我就会告诉你,学一定要学。
AustinDatabases
2024/03/21
2400
Mysql  怎么产生隐藏主键 和 还要不要学MySQL
SQL学习笔记三(补充-3)之MySQL完整性约束
是否可空,null表示空,非字符串 not null - 不可空 null - 可空
Jetpropelledsnake21
2019/02/15
1.6K0
replace into 解析
在支持业务过程中,经常遇到开发咨询replace into 的使用场景以及注意事项,这里做个总结,从功能原理注意事项上做个说明。
用户1278550
2018/08/09
1.7K0
数据库MySQL-列属性
字段值从1开始,每次递增1,自动增长的值就不会有重复,适合用来生成唯一的id。在MySQL中只要是自动增长列必须是主键
cwl_java
2020/03/27
3.4K0
2.MySQL表操作
表就相当于文件,表中的一条记录就相当于文件的一行内容,不同的是,表中的一条记录有对应的标题,称为表的字段
changxin7
2019/09/10
1.4K0
MySQL 约束
  非空约束(NOT NULL)指字段的值不能为空。对于使用了非空约束的字段,用户在添加数据时没有指定值,数据库系统就会报错。可以通过 CREATE TABLE 或 ALTER TABLE 语句实现非空。在表中某个列的定义后加上关键字 NOT NULL 作为限定词,来约束该列的取值不能为空。
Demo_Null
2020/09/28
3.4K0
MySQL 约束
关于MySQL中insert ignore,insert on duplicate和replace into,你可能没想过区别
在数据流转中或者日常的数据操作中,势必会有数据写入的过程,如果把一些数据写入一张数据库表中,如果写入量有100万,而重复的数据有90万,那么如何让这10%的数据能够更高更高效的写入。
jeanron100
2020/02/13
3.9K0
相关推荐
MYSQL 性能优化 index 函数,隐藏,前缀,hash 索引 使用方法(2)
更多 >
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档