MySQL约束及索引

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 在特定的数据库引擎下才有,mylsam快速定位数据

1.索引分类

1.主键索引 (primary key)

唯一标识,主键不可重复,只能有一个列作为主键

2.唯一索引 (unique key)

避免重复的列出现,唯一索引可以重复,多个列都可以标识位 唯一索引

3.常规索引(key/index)

默认的,index,key关键字来设置

4.全文索引(fullText)

在特定的数据库引擎下才有,mylsam
快速定位数据

5.基础语法

在这里插入图片描述

2.建表测试

CREATE TABLE `app_user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT '' COMMENT '用户昵称',
`email` varchar(50) NOT NULL COMMENT '用户邮箱',
`phone` varchar(20) DEFAULT '' COMMENT '手机号',
`gender` tinyint(4) unsigned DEFAULT '0' COMMENT '性别(0:男;1:女)',
`password` varchar(100) NOT NULL COMMENT '密码',
`age` tinyint(4) DEFAULT '0' COMMENT '年龄',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'

批量插入数据:100w

DROP FUNCTION IF EXISTS mock_data;
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i < num DO
  INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`, `age`)
   VALUES(CONCAT('用户', i), '24736743@qq.com', CONCAT('18', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
  SET i = i + 1;
END WHILE;
RETURN i;
END;
SELECT mock_data();

索引效率测试

无索引

SELECT * FROM app_user WHERE name = '用户9999'; -- 查看耗时
SELECT * FROM app_user WHERE name = '用户9999';
SELECT * FROM app_user WHERE name = '用户9999';

mysql> EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'\G
*************************** 1. row ***************************
          id: 1
select_type: SIMPLE
       table: app_user
  partitions: NULL
        type: ALL
possible_keys: NULL
        key: NULL
    key_len: NULL
        ref: NULL
        rows: 992759
    filtered: 10.00
      Extra: Using where
1 row in set, 1 warning (0.00 sec)

创建索引

CREATE INDEX idx_app_user_name ON app_user(name);

测试普通索引

mysql> EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'\G
*************************** 1. row ***************************
          id: 1
select_type: SIMPLE
       table: app_user
  partitions: NULL
        type: ref
possible_keys: idx_app_user_name
        key: idx_app_user_name
    key_len: 203
        ref: const
        rows: 1
    filtered: 100.00
      Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)

mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)

mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)

3.索引准则

索引不是越多越好

不要对经常变动的数据加索引

小数据量的表建议不要加索引

索引一般应加在查找条件的字段

约束

1.外键概念

外键:给关联字段创建关联就是添加外键,简单来说外键就是被约束条件

切记:作为外键一定要和关联主键的数据类型保持一致

--记录老师的数据表

CREATE TABLE Teachers(

id TINYINT PRIMARY KEY auto_increment,

name VARCHAR (20),

age INT,

gender boolean

);

--给老师添加数据

INSERT INTO Teachers (name,age,gender) VALUES ("Lily",12,0),

("Linta",14,0),

("Zahi",22,0),

("Paul",20,1),

("Jeans",21,1);

--创建学生的表

CREATE TABLE Students(

id INT PRIMARY KEY auto_increment,

name VARCHAR(20),

Teacher_id TINYINT,

FOREIGN KEY (Teacher_id) REFERENCES Teachers(id)

)ENGINE=INNODB;

--给学生添加数据

INSERT INTO Students (name,Teacher_id) VALUES ("ViewIn1",2),

("ViewIn2",4),

("ViewIn3",1),

("ViewIn4",3),

("ViewIn5",2),

("ViewIn6",3),

("ViewIn7",2),

("ViewIn8",4);

以上就是给Students的Teacher_id绑定外键为Teachers的id,这样就能将每一个学生与老师对应起来

2.关联约束

其中,Teacher是母表,Students是子表,无法单独删除母表中的字段,因为被子表约束了

要删除母表中的字段,必须将该字段的约束解除,即子表的外键不再对应该字段

因此可以选择修改作为外键的数据,来删除母表的某字段

比如要删除字段1,将Students中id=4和id=6(原本对应母表id=3)的Teacher_id值设置为4

—>这样子表中就没有与母表id=3的字段有关联了

update Students set Teacher_id=4 where id=4 or id=6;

—>之后再删除母表id=3的字段就不受影响了

delete from Teachers where id=3;

—>此时插入一个学生,绑定外键为3就会报错

INSERT INTO Students (name,Teacher_id) values ("ZaHuw",3);

3.添加与删除外键

给现成的表加外键

例:给Students创建一个外键,取名为aaa,外键字段为Teacher_id,将该外键关联到表Teachers的id字段

ALTER TABLE Students ADD CONSTRAINT aaa

FOREIGN KEY(Teacher_id)

REFERENCES Teachers(id);

2)删除已有的外键

ALTER TABLE Students drop foreign key aaa;

4.集联删除

设置集联删除:这样就不会被限制删除了;

如果删除了外键的字段,那它对应的外键也会跟着被删除;

如果删除了母表的字段,它对应的子表字段也会跟着被删除。

CREATE TABLE Students3(

id INT PRIMARY KEY auto_increment,

name VARCHAR(20),

Teacher_id TINYINT,

FOREIGN KEY (Teacher_id) REFERENCES Teachers(id) on DELETE CASCADE

)ENGINE=INNODB;

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
相关文章
|
2月前
|
存储 SQL 关系型数据库
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
|
2月前
|
存储 关系型数据库 MySQL
MySQL数据库索引的数据结构?
MySQL中默认使用B+tree索引,它是一种多路平衡搜索树,具有树高较低、检索速度快的特点。所有数据存储在叶子节点,非叶子节点仅作索引,且叶子节点形成双向链表,便于区间查询。
92 4
|
4月前
|
存储 关系型数据库 MySQL
阿里面试:MySQL 一个表最多 加几个索引? 6个?64个?还是多少?
阿里面试:MySQL 一个表最多 加几个索引? 6个?64个?还是多少?
阿里面试:MySQL 一个表最多 加几个索引? 6个?64个?还是多少?
|
1月前
|
存储 安全 关系型数据库
MySQL数据库约束条件概述及其重要性讨论。
正确地实现并管理好各类紧缩条件将直接影响到企业信息管理水平与服务质量,在当今大数据背景下更显得格外重要;任何设计师都需要深刻理解其原理与运作机晰承担起责任使得所托管资料安全稳固同时又具备良好伸缩灵活度迎合日益复杂商务需求变动.
88 11
|
6月前
|
关系型数据库 MySQL 数据库
Mysql的索引
MYSQL索引主要有 : 单列索引 , 组合索引和空间索引 , 用的比较多的就是单列索引和组合索引 , 空间索引我这边没有用到过 单列索引 : 在MYSQL数据库表的某一列上面创建的索引叫单列索引 , 单列索引又分为 ● 普通索引:MySQL中基本索引类型,没有什么限制,允许在定义索引的列中插入重复值和空值,纯粹为了查询数据更快一点。 ● 唯一索引:索引列中的值必须是唯一的,但是允许为空值 ● 主键索引:是一种特殊的唯一索引,不允许有空值 ● 全文索引: 只有在MyISAM引擎、InnoDB(5.6以后)上才能使⽤用,而且只能在CHAR,VARCHAR,TEXT类型字段上使⽤用全⽂文索引。
|
2月前
|
存储 SQL 关系型数据库
MySQL 核心知识与索引优化全解析
本文系统梳理了 MySQL 的核心知识与索引优化策略。在基础概念部分,阐述了 char 与 varchar 在存储方式和性能上的差异,以及事务的 ACID 特性、并发事务问题及对应的隔离级别(MySQL 默认 REPEATABLE READ)。 索引基础部分,详解了 InnoDB 默认的 B+tree 索引结构(多路平衡树、叶子节点存数据、双向链表支持区间查询),区分了聚簇索引(数据与索引共存,唯一)和二级索引(数据与索引分离,多个),解释了回表查询的概念及优化方法,并分析了 B+tree 作为索引结构的优势(树高低、效率稳、支持区间查询)。 索引优化部分,列出了索引创建的六大原则
|
3月前
|
存储 关系型数据库 MySQL
MySQL覆盖索引解释
总之,覆盖索引就像是图书馆中那些使得搜索变得极为迅速和简单的工具,一旦正确使用,就会让你的数据库查询飞快而轻便。让数据检索就像是读者在图书目录中以最快速度找到所需信息一样简便。这样的效率和速度,让覆盖索引成为数据库优化师傅们手中的尚方宝剑,既能够提升性能,又能够保持系统的整洁高效。
112 9
|
4月前
|
机器学习/深度学习 关系型数据库 MySQL
对比MySQL全文索引与常规索引的互异性
现在,你或许明白了这两种索引的差异,但任何技术决策都不应仅仅基于理论之上。你可以创建你的数据库实验环境,尝试不同类型的索引,看看它们如何影响性能,感受它们真实的力量。只有这样,你才能熟悉它们,掌握什么时候使用全文索引,什么时候使用常规索引,以适应复杂多变的业务需求。
103 12
|
8月前
|
存储 关系型数据库 MySQL
MySQL索引学习笔记
本文深入探讨了MySQL数据库中慢查询分析的关键概念和技术手段。
582 81
|
5月前
|
SQL 存储 关系型数据库
MySQL选错索引了怎么办?
本文探讨了MySQL中因索引选择不当导致查询性能下降的问题。通过创建包含10万行数据的表并插入数据,分析了一条简单SQL语句在不同场景下的执行情况。实验表明,当数据频繁更新时,MySQL可能因统计信息不准确而选错索引,导致全表扫描。文章深入解析了优化器判断扫描行数的机制,指出基数统计误差是主要原因,并提供了通过`analyze table`重新统计索引信息的解决方法。
134 3

推荐镜像

更多