MySQL

最新推荐文章于 2025-07-09 12:04:45 发布

原创最新推荐文章于 2025-07-09 12:04:45 发布 · 462 阅读

4 ·

CC 4.0 BY-SA版权

Java 专栏收录该内容

27 篇文章

订阅专栏

本文详细介绍了数据库操作的关键概念，包括左(外)连接、等值连接、自然连接、SQL注入预防、索引管理、事务处理、并发控制、锁机制、乐观锁与悲观锁的区别，以及数据库性能优化技巧。

数据库

01.左(外)连接：

以左表为主表，返回左表里的所有行，并和右表连接；

即使右表没有和左表对应的行，也要返回null。

select* from Student s left join StudentScore sc on s.id=sc.id

02.等值连接：从关系R与S的笛卡尔积中选择A、B属性列相等的元祖

自然连接：等值连接的结果去掉重复的属性列（比较的是同名属性）

03.mysql修改表用 alter

alter table User add uid int; 增加uid属性，类型为int

alter table User alter column uid varchar; 将uid改为varchar类型

https://snailclimb.top/JavaGuide/#/database/%E4%B8%80%E5%8D%83%E8%A1%8CMySQL%E5%91%BD%E4%BB%A4

04.通配符%表示任意长度的字符串，_表示单个字符，like是模糊查询，用like进行模糊查询，第一个为通配符不会走索引

select name from User where name like ‘a%b';

order by id DESC 按id降序 ASC升序

select count(distinct id) from User; 指定distinct短语，去重。

05.三范式

1NF：属性不可分，无重复的列
2NF：属性完全依赖于主键，消除了部分依赖
3NF：属性不依赖于其他非主属性，消除了传递依赖

06.一张自增表里面总共有 7 条数据，删除了最后 2 条数据，重启 MySQL 数据库，又插入了一条数据，此时 id 是几

表类型如果是 MyISAM ，那 id 就是 8
表类型如果是 InnoDB ，那 id 就是 6

InnoDB 表只会把自增主键的最大 id 记录在内存中，所以重启之后会导致最大 id 丢失

07.MyISAM 和 InnoDB 的区别有哪些？

MyISAM 表不支持事务、不支持行级锁、不支持外键。 InnoDB 表支持事务、支持行级锁、支持外键。

08.sql注入：将恶意的sql查询或添加语句插入到应用的输入参数中，再在后台sql服务器上解析进行的攻击

where username=”or 1=1#" and .... #表示注释，后面的被忽略

PrepareStatement能预处理，并防止sql注入，占位符从1开始，"?"无法扩展

09.MyISAM 只支持表锁，InnoDB 支持表锁和行锁，默认为行锁。

表级锁：开销小，加锁快，不会出现死锁。锁定粒度大，发生锁冲突的概率最高，并发量最低。
行级锁：开销大，加锁慢，会出现死锁。锁力度小，发生锁冲突的概率小，并发度最高。

10.索引优缺点：

索引加快数据库的检索速度
索引降低了插入、删除、修改等维护任务的速度(虽然索引可以提高查询速度，但是它们也会导致数据库系统更新数据的性能下降，因为大部分数据更新需要同时更新索引)
唯一索引可以确保每一行数据的唯一性，通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能
索引需要占物理和数据空间

什么时候【要】创建索引

（1）表经常进行 SELECT 操作
（2）表很大(记录超多)，记录内容分布范围很广
（3）列名经常在 WHERE 子句或连接条件中出现

什么时候【不要】创建索引

（1）表经常进行 INSERT/UPDATE/DELETE 操作
（2）表很小(记录超少)
（3）列名不经常作为连接条件或出现在 WHERE 子句中

索引分类：

唯一索引：唯一索引不允许两行具有相同的索引值
主键索引：为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空
聚集索引(Clustered)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个
非聚集索引(Non-clustered)：非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于249个

添加PRIMARY KEY（主键索引）

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

添加UNIQUE(唯一索引)

ALTER TABLE `table_name` ADD UNIQUE ( `column` )

添加INDEX(普通索引)

ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

添加FULLTEXT(全文索引)

ALTER TABLE `table_name` ADD FULLTEXT ( `column`)

添加多列索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

MySQL中的索引可以以一定顺序引用多列，这种索引叫作联合索引。如User表的name和city加联合索引就是(name,city)，而最左前缀原则指的是，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到。如下：

select * from user where name=xx and city=xx ; ／／可以命中索引
select * from user where name=xx ; // 可以命中索引
select * from user where city=xx ; // 无法命中索引

11.数据库事务

事务是逻辑上的一组操作，要么都执行，要么都不执行。

事务的特性（ACID）：

原子性（Atomicity）：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
一致性（Consistency）：执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
隔离性（Isolation）：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
持久性（Durability）：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

12.并发事务带来的问题

脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。
不可重复读（Unrepeatableread）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

不可重复读的重点是修改，幻读的重点在于新增或者删除。

所谓的脏读，其实就是读到了别的事务回滚前的脏数据。比如事务B执行过程中修改了数据X，在未提交前，事务A读取了X，而事务B却回滚了，这样事务A就形成了脏读。

也就是说，当前事务读到的数据是别的事务想要修改成为的但是没有修改成功的数据。

13.事务的隔离级别

、

14.锁

对于UPDATE、DELETE、INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)
MyISAM在执行查询语句SELECT前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预

https://segmentfault.com/a/1190000015738121

InnoDB实现了以下两种类型的行锁。

共享锁（S锁）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
- 也叫做读锁：读锁是共享的，多个客户可以同时读取同一个资源，但不允许其他客户修改。
排他锁（X锁)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。
- 也叫做写锁：写锁是排他的，写锁会阻塞其他的写锁和读锁。

15.乐观锁和悲观锁

乐观锁是一种思想，具体实现是，表中有一个版本字段，第一次读的时候，获取到这个字段。处理完业务逻辑开始更新的时候，需要再次查看该字段的值是否和第一次的一样。如果一样更新，反之拒绝。之所以叫乐观，因为这个模式没有从数据库加锁，等到更新的时候再判断是否可以更新。
悲观锁是数据库层面加锁，都会阻塞去等待锁。

16.where ,group by, having

https://blog.youkuaiyun.com/weixin_34124577/article/details/85955515

当聚集函数遇到空值时，除了count(*)外，都跳过空值而不是处理空值。

聚集函数只能用于select子句和group by中的having子句。

where子句和having短语的区别在于作用对象不同，where子句作用于基本表或视图，从中选择合适的元组。having短语作用于组，从而选择满足条件的组。

17.start transaction 或begin 事务开启

commit 事务提交

rollback事务回滚

https://www.runoob.com/mysql/mysql-transaction.html

18.MySQL性能优化

使用comment从句添加表和列的备注
尽可能把所有列定义为not null；（索引null列占用更多空间，进行比较和计算时对null做处理）
金融类数据用decimal ，计算时不会丢失精度
避免使用select*
禁止使用不含字段的insert语句如用 insert into t(c1,c2,c3) values ('a','b','c'); 代替insert into values ('a','b','c');