MySql数据库知识整理

最新推荐文章于 2024-11-03 09:30:00 发布

qq_41054819

最新推荐文章于 2024-11-03 09:30:00 发布

阅读量42

点赞数

文章标签：数据库 mysql

本文链接：https://blog.csdn.net/qq_41054819/article/details/132963409

版权

本文详细介绍了数据库设计中的三大范式（第一、二、三范式），五大约束（主键、唯一性、默认值、非空和外键），以及数据库索引的概念、类型和分类，还涵盖了事务的定义、ACID特性、隔离级别和SQL语句的执行优化策略。

摘要由CSDN通过智能技术生成

1.数据库设计

1）三大范式

三大范式其实就是数据库建表的规范。

第一范式：要求一张表中的数据每一列都是不可分割的原子项数据。

需要根据实际需求，设计字段值不可再分解。比如“部门”和“角色”在需求中都较常用，应该分开两个字段，而不能把“部门角色”作为一个字段。

第二范式：消除部分依赖，要求一张表中的每一列都完全依赖于主键（针对于组合主键），也就是不会出现某一列只和部分主键相关。

比如在一个订单表中，有订单号，产品号，产品数量，产品价格，订单时间，订单金额。将订单号与产品号作为组合主键，但是订单表中的产品数量，和产品价格与主键中的订单号，产品号都有关，而订单时间与订单金额只与订单号有关，这就导致了部分依赖。为了消除部分依赖，所以需要进行分表。

第三范式：消除传递依赖，要求一张表中的每一列都和主键是直接依赖的，不是间接依赖。

比如在一个学生表中，有学号，姓名，性别，年龄，班主任姓名，班主任性别，班主任年龄。在这张表中实际上班主任性别与班主任年龄是直接依赖于班主任姓名的，而不是直接依赖于学号，这样就导致了传递依赖。此时可以进行分表分为学生表和教师表，将教师姓名作为学生表的外键。

2）五大约束

数据库五大约束：

1.primary KEY:设置主键约束；
2.UNIQUE：设置唯一性约束，不能有重复值；
3.DEFAULT 默认值约束，status INT(10) DEFAULT 0, status不输入是默认为0。
4.NOT NULL：设置非空约束，该字段不能为空；
5.FOREIGN key :设置外键约束

2.数据库索引

1）索引的含义和特点。

索引是一种特殊的数据库结构，索引由数据库表中一列或多列组合而成，可以用来快速查询数据库表中的特定记录。索引相当于新华字典的音序表，通过音序表就能直接查找，可以大大节省时间。通过索引，查询数据时可以不必读完记录的所有信息，而只是查询索引列。使用索引可以很大程度上提高数据库的查询速度，这样有效的提高了数据库系统的性能。

2）索引的存储类型

存储类型：

B型树（BTREE）索引
哈希（HASH)索引

Mysql的InnoDB和 MyISAM存储引擎支持BTREE索引，MEMORY存储引擎支持 HASH索引和BTREE索引，默认为Hash索引。

3）索引的分类

MySQL 的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。

普通索引：在创建普通索引时，不附加任何限制条件。这类索引可以创建在任何数据类型中，其值是否唯一和非空由字段本身的完整性约束条件决定。

唯一索引：使用UNIQUE参数可以设置索引为唯一性索引。在创建唯一性索引时，限制该索引的值必须是唯一的。主键就是一种特殊唯一性索引。

全文索引：使用FULLTEXT参数可以设置索引为全文索引。全文索引只能创建在CHAR、VARCHAR或TEXT类型的字段上。查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。（MySQL数据库从3.23.23版开始支持全文索引，但只有MyISAM存储引擎支持全文检索。）

单列索引：在表中的单个字段上创建索引。单列索引只根据该字段进行索引。单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。

多列索引：多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询。但是，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。例如，在表中的id、name和 grade字段上建立一个多列索引，那么，只有查询条件使用了id字段时该索引才会被使用。

空间索引：用SPATIAL参数可以设置索引为空间索引。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率。MySQL中的空间数据类型包括GEOMETRY和POINT、LINESTRING和POLYGON等。目前只有MyISAM存储引擎支持空间检索，而且索引的字段不能为空值。

4）索引的优缺点

索引的优点：可以提高检索数据的速度，这是创建索引的最主要的原因;对于有依赖关系的子表和父表之间的联合查询时，可以提高查询速度;使用分组和排序子句进行数据查询时，同样可以显著节省查询中分组和排序的时间。

索引的缺点是创建和维护索引需要耗费时间，耗费时间的数量随着数据量的增加而增加;索引需要占用物理空间，每一个索引要占一定的物理空间;增加、删除和修改数据时，要动态的维护索引，造成数据的维护速度降低了。

3.数据库事务

1）事务的定义

事务用于保证数据的一致性,它由一组相关的dml（数据操作语言）语句组成,该组的dml语句要么全部成功，要么全部失败。如:转账就要用事务来处理，用以保证数据的一致性。

2）事务的四大特性(ACID）

原子性（Atomicity）：原子性意味着数据库中的事务执行是作为原子，即不可再分。整个语句要么执行，要么不执行，不会有中间状态。
一致性（Consistency）：事务在开始和结束时，应该始终满足一致性约束。比如系统要求X+Y=200,那么事务如果改变了X的数值，则Y的数值也要相应修改来满足这样一致性要求。
隔离性（Isolation）：如果有多个事务同时执行，多个事务之间不需要知到对方的存在，并且执行时互不影响，事务之间需要序列化执行，有时间顺序。而且数据据库允许多个并发事务同时对其数据进行读写的能力，隔离性可以防止多个事务并发执行时，由于交叉执行而导致数据的不一致。
持久性（Durability）：事务的持久性是指事务运行成功之后，对系统状态的更新是永久的。即使出现了一些故障比如断电等，事务一旦提交，则持久化保存在数据库中。

3）事务并发带来的问题

脏读	A事务读到B事务没有提交的数据
幻读	一次事务中，两次读操作中，读到的数据行数不一致。读到了新增或者读不到删除的数据。
不可重复读	同一事务中，多次查询某个条件的数据，结果不一样
丢失修改（脏写）	多个事务同时对数据进行修改，其中一个事务的数据被另一个事务的操作覆盖，导致丢失修改。如果一个事务修改了另一个未提交事务修改过的数据，那就意味着发生了脏写

4）事务的隔离级别

Read uncommitted（未提交读）：一个事务在执行过程中可以看到其他事务没有提交的新插入的记录，而且能看到其他事务没有提交的对已有记录的更新。

Read committed（已提交读）：一个事务在执行过程中可以看到其他事务已经提交的新插入的记录，而且能看到其他事务已经提交的对已有记录的更新。

Repeatable read（可重复读）：一个事务在执行过程中可以看到其他事务已经提交的新插入的记录，但是不能看到其他其他事务对已有记录的更新。（Mysql默认的隔离级别为可重复读）

Serializable（串行化）：一个事务在执行过程中完全看不到其他事务对数据库所做的更新。（事务执行的时候不允许别的事务并发执行。事务串行化执行，事务只能一个接着一个地执行，而不能并发执行）。

四个事务隔离级别各自解决的问题：

隔离级别	丢失修改(脏写)	脏读	不可重复读	幻读
未提交读（Read uncommitted）	√	×	×	×
已提交读 (Read committed)	√	√	×	×
可重复读 (Repeatable Read)	√	√	√	×
串行化（Serializable）	√	√	√	√

4.SQL语句

4.1 SQL语句的执行顺序

from语句——指定数据源

on语句——关联条件筛选

join——关联表表操作

where ——条件过滤(分组前)

group by ——分组

聚合函数——执行函数

having ——条件过滤（分组后）

select ——指定查询结果

distinct——行去重

order by——排序

limit——返回指定行(分页操作)

4.2 join连接

1）inner join:如果表中有至少一个匹配，则返回行。

inner join的语法：

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

或

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;

2)left join：即使右表中没有匹配，也从左表返回所有的行。

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

或

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;

3)right join：即使左表中没有匹配，也从右表返回所有的行。

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

或

SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;

4)full join：只要其中一个表中存在匹配，则返回行。

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

5）Cross join:返回的是两张表的笛卡尔积

SELECT column_name(s)
FROM table1

CROSS JOIN table2

4.3 SQL优化技巧

1）避免使用select *,只查需要用到的列

查了很多数据，但是不用，白白浪费了数据库资源，比如：内存或者cpu。
多查出来的数据，通过网络IO传输的过程中，也会增加数据传输的时间。
select *不会走索引，会出现大量的回表操作，而从导致查询sql的性能很低。

2）用union all代替union

union：可以获取排重后的数据。

union all：以获取所有数据，包含重复的数据。

排重的过程需要遍历、排序和比较，它更耗时，更消耗cpu资源。

所以如果能用union all的时候，尽量不用union。

3）小表驱动大表

小表驱动大表，也就是说用小表的数据集驱动大表的数据集。

使用in关键字实现：(in 适用于左边大表，右边小表)

select 字段1,字段2,字段3,字段4

from order

where user_id in

(select id from user where status=2)

使用exists关键字实现：( exists适用于左边小表，右边大表。)

select 字段1,字段2,字段3,字段4 字段5,字段6,字段, from order

where exists

(select 1 from user where order.user_id = user.id and status=2)

4)批量插入

避免循环逐条插入

insert into order(id,code,user_id) values(123,'001',100)

insert into order(id,code,user_id) values(124,'002',100)

insert into order(id,code,user_id) values(125,'003',100)

使用批量插入数据的方法