mysql相关的概念

待我称王封你为后i 2023-09-27 15:37 108阅读 0赞

**目录**

1.事务的四大特性

2.并发问题

3.隔离级别

4.索引

5.分区

6.exist和in的区别

7.MySQL中int(10)和char(10)的区别？

8.truncate、delete与drop区别

9.having和where区别

10.MySQL单表多大进行分库分表？

11.大表查询慢怎么优化

12.count(1)、count(\*)和count(字段名)的区别

13.DATETIME 和 TIMESTAMP的区别

--------------------

### 1.事务的四大特性 ###

**事务特性ACID**：**原子性**（`Atomicity`）、**一致性**（`Consistency`）、**隔离性**（`Isolation`）、**持久性**（`Durability`）。

*  **原子性**是指事务包含的所有操作要么全部成功，要么全部失败回滚。
 *  **一致性**是指一个事务执行之前和执行之后都必须处于一致性状态。比如a与b账户共有1000块，两人之间转账之后无论成功还是失败，它们的账户总和还是1000。
 *  **隔离性**。跟隔离级别相关，如`read committed`，一个事务只能读到已经提交的修改。
 *  **持久性**是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

### 2.并发问题 ###

*  **脏读**是指在一个事务处理过程里读取了另一个未提交的事务中的数据。
 *  **不可重复读**是指在对于数据库中的某行记录，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，另一个事务修改了数据并提交了。
 *  **幻读**是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录。对幻读的正确理解是一个事务内的读取操作的结论不能支撑之后业务的执行。假设事务要新增一条记录，主键为id，在新增之前执行了select，没有发现id为xxx的记录，但插入时出现主键冲突，这就属于幻读，读取不到记录却发现主键冲突是因为记录实际上已经被其他的事务插入了，但当前事务不可见。

### 3.隔离级别 ###

*  **Serializable** (串行化)：通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。
 *  **Repeatable read** (可重复读)：MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行，解决了不可重复读的问题。
 *  **Read committed** (读已提交)：一个事务只能看见已经提交事务所做的改变。可避免脏读的发生。
 *  **Read uncommitted** (读未提交)：所有事务都可以看到其他未提交事务的执行结果

### 4.索引 ###

索引是存储引擎用于提高数据库表的访问速度的一种**数据结构**。

优点：

*  **加快数据查找的速度**
 *  为用来排序或者是分组的字段添加索引，可以加快分组和排序的速度
 *  加快表与表之间的连接

缺点：

*  建立索引需要**占用物理空间**
 *  会降低表的增删改的效率，因为每次对表记录进行增删改，需要进行**动态维护索引**，导致增删改时间变长

什么情况下需要建索引？

1.  经常用于查询的字段
2.  经常用于连接的字段建立索引，可以加快连接的速度
3.  经常需要排序的字段建立索引，因为索引已经排好序，可以加快排序查询速度

什么情况下不建索引？

1.  `where`条件中用不到的字段不适合建立索引
2.  表记录较少。比如只有几百条数据，没必要加索引。
3.  需要经常增删改。需要评估是否适合加索引
4.  **参与列计算**的列不适合建索引
5.  **区分度不高**的字段不适合建立索引，如性别，只有男/女/未知三个值。加了索引，查询效率也不会提高。

索引的设计原则？

*  对于经常作为查询条件的字段，应该建立索引，以提高查询速度
 *  为经常需要排序、分组和联合操作的字段建立索引
 *  索引列的**区分度越高**，索引的效果越好。比如使用性别这种区分度很低的列作为索引，效果就会很差。
 *  避免给"大字段"建立索引。尽量使用数据量小的字段作为索引。因为`MySQL`在维护索引的时候是会将字段值一起维护的，那这样必然会导致索引占用更多的空间，另外在排序的时候需要花费更多的时间去对比。
 *  尽量使用**短索引**，对于较长的字符串进行索引时应该指定一个较短的前缀长度，因为较小的索引涉及到的磁盘I/O较少，查询速度更快。
 *  索引不是越多越好，每个索引都需要额外的物理空间，维护也需要花费时间。
 *  频繁增删改的字段不要建立索引。假设某个字段频繁修改，那就意味着需要频繁的重建索引，这必然影响MySQL的性能
 *  利用**最左前缀原则**。

索引什么时候会失效？

导致索引失效的情况：

*  对于组合索引，不是使用组合索引最左边的字段，则不会使用索引
 *  以%开头的like查询如`%abc`，无法使用索引；非%开头的like查询如`abc%`，相当于范围查询，会使用索引
 *  查询条件中列类型是字符串，没有使用引号，可能会因为类型不同发生隐式转换，使索引失效
 *  判断索引列是否不等于某个值时
 *  对索引列进行运算
 *  查询条件使用`or`连接，也会导致索引失效

### 5.分区 ###

分区是把一张表的数据分成N多个区块。分区表是一个独立的逻辑表，但是底层由多个物理子表组成。

当查询条件的数据分布在某一个分区的时候，查询引擎只会去某一个分区查询，而不是遍历整个表。在管理层面，如果需要删除某一个分区的数据，只需要删除对应的分区即可。

分区一般都是放在单机里的，用的比较多的是时间范围分区，方便归档。只不过分库分表需要代码实现，分区则是mysql内部实现。分库分表和分区并不冲突，可以结合使用。

**range分区**，按照范围分区。比如按照时间范围分区。

**list分区，**list分区和range分区相似，主要区别在于list是枚举值列表的集合，range是连续的区间值的集合。对于list分区，分区字段必须是已知的，如果插入的字段不在分区时的枚举值中，将无法插入。

**hash分区，**可以将数据均匀地分布到预先定义的分区中。

### 6.exist和in的区别 ###

`exists`用于对外表记录做筛选。`exists`会遍历外表，将外查询表的每一行，代入内查询进行判断。当`exists`里的条件语句能够返回记录行时，条件就为真，返回外表当前记录。反之如果`exists`里的条件语句不能返回记录行，条件为假，则外表当前记录被丢弃。

select a.* from A awhere exists(select 1 from B b where a.id=b.id)
    复制代码

`in`是先把后边的语句查出来放到临时表中，然后遍历临时表，将临时表的每一行，代入外查询去查找。

select * from Awhere id in(select id from B)
    复制代码

**子查询的表比较大的时候**，使用`exists`可以有效减少总的循环次数来提升速度；**当外查询的表比较大的时候**，使用`in`可以有效减少对外查询表循环遍历来提升速度。

### 7.MySQL中int(10)和char(10)的区别？　 ###

int(10)中的10表示的是显示数据的长度，int类型已经决定了数据的范围。

而char(10)表示的是存储数据的长度。

### 8.truncate、delete与drop区别 ###

1.  `truncate`和不带`where`子句的`delete`、以及`drop`都会删除表内的数据。
2.  `drop`、`truncate`都是`DDL`语句（数据定义语言），执行后会自动提交。

**不同点：**

1.  truncate 和 delete 只删除数据不删除表的结构；drop 语句将删除表的结构被依赖的约束、触发器、索引；
2.  一般来说，执行速度: drop > truncate > delete。

### 9.having和where区别 ###

*  二者作用的对象不同，`where`子句作用于表和视图，`having`作用于组。
 *  `where`在数据分组前进行过滤，`having`在数据分组后进行过滤。

### 10.MySQL单表多大进行分库分表？ ###

1.  MySQL 单表数据量大于 2000 万行，性能会明显下降，考虑进行分库分表。
2.  阿里巴巴《Java 开发手册》提出单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。

事实上，这个数值和实际记录的条数无关，而与 MySQL 的配置以及机器的硬件有关。因为MySQL为了提高性能，会将表的索引装载到内存中。在InnoDB buffer size 足够的情况下，其能完成全加载进内存，查询不会有问题。但是，当单表数据库到达某个量级的上限时，导致内存无法存储其索引，使得之后的 SQL 查询会产生磁盘 IO，从而导致性能下降。当然，这个还有具体的表结构的设计有关，最终导致的问题都是内存限制。

因此，对于分库分表，需要结合实际需求，不宜过度设计，在项目一开始不采用分库与分表设计，而是随着业务的增长，在无法继续优化的情况下，再考虑分库与分表提高系统的性能。对此，阿里巴巴《Java 开发手册》补充到：如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。

至于MySQL单表多大进行分库分表，应当根据机器资源进行评估。

### 11.大表查询慢怎么优化 ###

当MySQL单表记录数过大时，数据库的性能会明显下降，一些常见的优化措施如下：

*  合理建立索引。在合适的字段上建立索引，例如在WHERE和ORDER BY命令上涉及的列建立索引，可根据EXPLAIN来查看是否用了索引还是全表扫描。
 *  建立分区。对关键字段建立水平分区，比如时间字段，若查询条件往往通过时间范围来进行查询，能提升不少性能。
 *  利用缓存。利用Redis等缓存热点数据，提高查询效率。
 *  限定数据的范围。比如：用户在查询历史信息的时候，可以控制在一个月的时间范围内。
 *  读写分离。经典的数据库拆分方案，主库负责写，从库负责读。
 *  通过分库分表的方式进行优化，主要有垂直拆分和水平拆分。

### 12.count(1)、count(\*)和count(字段名)的区别 ###

两者的主要区别是

1.  count(1) 会统计表中的所有的记录数，包含字段为null 的记录。
2.  count(字段名) 会统计该字段在表中出现的次数，忽略字段为null 的情况。即不统计字段为null 的记录。

接下来看看三者之间的区别。

执行效果上：

*  count(\*)包括了所有的列，相当于行数，在统计结果的时候，**不会忽略列值为NULL**
 *  count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，**不会忽略列值为NULL**
 *  count(字段名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，**即某个字段值为NULL时，不统计**。

执行效率上：

*  列名为主键，count(字段名)会比count(1)快
 *  列名不为主键，count(1)会比count(列名)快
 *  如果表多个列并且没有主键，则 count(1) 的执行效率优于 count(\*)
 *  如果有主键，则 select count(主键)的执行效率是最优的
 *  如果表只有一个字段，则 select count(\*)最优。

### 13.DATETIME 和 TIMESTAMP的区别 ###

`TIMESTAMP`和`DATETIME`都可以用来存储时间，它们主要有以下区别：

1.表示范围

*  DATETIME：1000-01-01 00:00:00.000000 到 9999-12-31 23:59:59.999999
 *  TIMESTAMP：'1970-01-01 00:00:01.000000' UTC 到 '2038-01-09 03:14:07.999999' UTC

`TIMESTAMP`支持的时间范围比`DATATIME`要小，容易出现超出的情况。

2.空间占用

*  TIMESTAMP ：占 4 个字节
 *  DATETIME：在 MySQL 5.6.4 之前，占 8 个字节 ，之后版本，占 5 个字节

3.存入时间是否会自动转换

`TIMESTAMP`类型在默认情况下，insert、update 数据时，`TIMESTAMP`列会自动以当前时间（`CURRENT_TIMESTAMP`）填充/更新。`DATETIME`则不会做任何转换，也不会检测时区，你给什么数据，它存什么数据。

4.`TIMESTAMP`比较受时区timezone的影响以及MYSQL版本和服务器的SQL MODE的影响。因为`TIMESTAMP`存的是时间戳，在不同的时区得出的时间不一致。

5.如果存进NULL，两者实际存储的值不同。

*  TIMESTAMP：会自动存储当前时间 now() 。
 *  DATETIME：不会自动存储当前时间，会直接存入 NULL 值。