MySQL基础（面试）

最新推荐文章于 2024-07-16 21:03:47 发布

Moment_007

最新推荐文章于 2024-07-16 21:03:47 发布

阅读量305

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/Moment_007/article/details/105157226

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

MySQL基础（面试）

一、数据库的三大范式
二、数据类型
三、存储引擎
- - 1. 常用的存储引擎
四、索引
五、锁
六、存储过程

一、数据库的三大范式

第一范式： 每个列不可以再拆分；
第二范式： 在第一范式的基础上，非主键列完全依赖于主键，而不是主键的一部分（主要针对于联合主键而言）；
第三范式： 在第二范式的基础上，非主键列完全只依赖于主键，不依赖于其他非主键（也就是消除了传递依赖关系）。
如：C 实际上是直接依赖于B，传递依赖于A 的，也就是说，通过A，可以唯一确定B，然后通过B来确定的C，这样就叫做不满足第三范式。

参考文章

二、数据类型

整数类型： 包括TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分别是1、2、3、4、8 个字节。注意：如INT(11)，这个11 只是显示长度，没有任何意义。
实数类型： 包括FLOAT、DOUBLE、DECIMAL。DECIMAL 的存储比BIGINT 还要大，能存储精确的小数，但效率会稍微差些，可以理解成用字符串进行处理。
字符串类型： 包括CHAR、VARCHAR、TEXT、BLOB。VARCHAR 存储可变长字符串，会使用额外的1或2个字节存储字符串长度。VARCHAR 和CHAR 存储的内容超出长度时都会被截断。CHAR 适合存储很短的字符串，或者所有的值都接近同一个长度。
日期和时间类型： timestamp 的效率会高于datetime。
枚举类型（ENUM）： 把不重复的数据存储为一个预定义的集合

三、存储引擎

1. 常用的存储引擎

InnoDB 引擎： 提供数据库的ACID 事务支持，且还提供行级锁和外键的约束。
MyISAM 引擎： 不提供事务支持，也不支持行级锁和外键。
MEMORY 引擎： 所有的数据存储在内存中，数据处理速度快，但安全性不高。

四、索引

1. 索引的优缺点

优点：

可以加快数据的检索速度；
通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

缺点：

创建索引和维护索引都需要耗费时间，当对表中的数据进行增、删、改时，索引也需要动态的维护，会降低增、删、改的执行效率。
索引需要占用物理空间。

2. 索引的创建原则

最左匹配原则，这是组合索引非常重要的原则，MySQL会一直向右匹配知道遇到范围查询（>、<、between、like）就停止匹配；（=和in可以乱序，查询优化器会帮你优化成索引可以识别的形式）
较为频繁作为查询条件的字段才去创建索引；
更新频繁的字段不适合创建索引；
若是不能有效区分数据的列不适合做索引列（如性别：男、女、未知）。
尽量在以前的索引上扩展索引，不要新建索引；
对于查询中很少涉及到的列，重复值比较多的列不要建立索引；
定义有外键的数据列一定要建索引；
对于定义为text、image和bit 的数据类型列不要建立索引。

3. 索引的创建方式

新建表的时候创建；
ALTER TABLE table_name ADD INDEX index_name(colum_name);
CREATE INDEX index_name ON table_name(colum_name);

4. 创建索引需要注意事项

非空字段：应该指定列为NOT NULL，除非你想存储NULL。mysql中，含有空值的列很难进行查询优化。
取值离散大的字段：用COUNT() 来统计，返回值越大说明离散程度高；
索引字段越小越好：数据库的数据存储以页为单位，一页存储的数据越多一次IO 操作获取的数据越大，效率越高。

5. B树和B+ 树的区别

B树可以将键和值放在内部节点和叶子节点；B+ 树中，内部节点是键，没有值，叶子节点同时存放键和值。
B+ 树的叶子节点有一条链相连，B 树的叶子节点各自独立。

6. 数据库为什么使用B+ 树而不是B 树

B 树只适合随机检索，而B+ 树支持随机检索和顺序检索；
B+ 树的查询效率更加稳定；
B 树在提高了磁盘IO 性能的同时并没有解决元素遍历的效率低下的问题；
B+ 树增删文件（节点）时，效率更高。

7. 聚簇索引与非聚簇索引

聚簇索引：将数据存储与索引放在一块，找到索引也找到了数据；
非聚簇索引：将数据存储与索引分隔开，索引结构的叶子节点指向了数据的对应行。

8. 非聚簇索引一定会回表查询吗

不一定，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那就不再进行回表查询。

9. 为什么需要注意联合索引中的顺序

联合索引：MySQL 可以使用多个字段同时建立一个索引，叫做联合索引。在联合索引中如果想要命中索引则需要按照建立索引的字段顺序挨个使用，否则无法命中索引。

五、锁

共享锁 & 排他锁

数据库的增、删、改操作默认都会加排他锁，而查询不会加任何锁。

排他锁： 对某一资源进行加排它锁，自身可以进行增、删、改、查，其他人无法进行任何操作。
eg：select * from student where id = 1 for update；

MySQL 中，行级锁并非直接锁记录，而是锁索引。如果操作的是主键索引，则MySQL 就会锁定这条主键索引；如果操作的是非主键索引，MySQL 会先锁住非主键索引再锁定相关的主键索引。所以如示例的例子如果id 没有加索引则会变为加表锁。

共享锁： 多个事务对同一数据可以共享一把锁，都能访问数据，但是只能读不能修改。
eg：select * from student where id = 1 lock in share mode；

死锁发生：

T1:begin tran
     select * from table lock in share mode
     update table set column1='hello'

T2:begin tran
     select * from table lock in share mode
     update table set column1='world'

当 T1 准备执行 update 时，根据锁机制，T1 的共享锁需要升级到排他锁才能执行接下来的 update。在升级排他锁前，必须等 table 上的其它共享锁（T2）释放，同理，T2 也在等 T1 的共享锁释放。于是死锁产生了。

乐观锁 & 悲观锁

说明：乐观锁 & 悲观锁是针对查询（select）语句。 比如例子：减库存出现负数。

排它锁（for update）是悲观锁的一种实现方式，这里不再说明。
乐观锁需要靠表设计和代码来实现：

一般在该商品表中添加version 版本字段或者timestamp 时间戳字段
查询后执行的更新操作变成如下（多了版本号条件）：
update table set name = “zhangsan” where id = 1 and version = 22;
如果更新操作失败，表示更新操作之前程序已经更新过，可以尝试重试来保证更新成功。为了尽可能更新失败，可以合理调整重试次数（阿里巴巴手册规定重试次数不低于三次）。

查询情况较多的情况下建议使用乐观锁，如果写入居多且对吞吐要求不高可使用悲观锁。

“加锁的查询操作”：加过排他锁的数据行在其他事务中是不能修改的，也不能通过for update或lock in share mode的加锁方式查询，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。