MySQL（八）------知识点整理

- 日理万妓 2023-07-08 02:49 7阅读 0赞

## 一、事务四大特性 ##

原子性，事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；

隔离性，并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；

一致性，执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；

持久性，一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

## 二、数据库隔离级别 ##

产生的问题有三种

脏读：事务B读取事务A还没有提交的数据

不可重复读：两次事务读的数据不一致

幻读:事务A修改了数据，事务B也修改了数据，这时在事务A看来，明明修改了数据，咋不一样

## 三、MYSQL的两种存储引擎区别（事务、锁级别等等），各自的适用场景 ##

字母意思上，引擎是发动机的核心部分。存储引擎即是mysql保存数据的核心技术.

MySQL存储引擎是MySQL服务的核心组件，它规定了数据存储时的存储结构。

mysql5.5之后默认的存储引擎为InnoDB，在此之前默认存储引擎是MyIsam

<table> 
 <thead> 
  <tr> 
   <th>特点</th> 
   <th>MyIsam</th> 
   <th>InnoDB</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>锁机制</td> 
   <td>表锁</td> 
   <td>行锁</td> 
  </tr> 
  <tr> 
   <td>事务</td> 
   <td>不支持</td> 
   <td>支持</td> 
  </tr> 
  <tr> 
   <td>外键</td> 
   <td>不支持</td> 
   <td>支持</td> 
  </tr> 
  <tr> 
   <td>B树索引</td> 
   <td>支持</td> 
   <td>支持</td> 
  </tr> 
  <tr> 
   <td>哈希索引</td> 
   <td>不支持</td> 
   <td>支持</td> 
  </tr> 
  <tr> 
   <td>全文索引</td> 
   <td>支持</td> 
   <td>不支持（5.6.4之后开始支持）</td> 
  </tr> 
 </tbody> 
</table>

**1、存储结构上区别**

**1.1、.frm文件**

存储数据表的框架结构，文件名与表名相同，每个表对应一个同名frm文件，与操作系统和存储引擎无关，即不管MySQL运行在何种操作系统上，使用何种存储引擎，都有这个文件。

**1.2、innodb数据表文件**

**InnoDB表由共享表空间、日志文件组（redo文件组）、表结构定义文件组成**。

#查看是哪种类型的表空间
    show variables like '%innodb_file_per_table%'
    
    #开启独占表空间
    innodb_file_per_table = ON

若将**innodb\_file\_per\_table**设置为on，则系统将为每一个表单独的生成一个**table\_name.ibd**的文件（独占表空间），在此文件中，存储与该表相关的**数据、索引、表的内部数据字典**信息。

**共享表空间以及独占表空间都是针对数据的从物理意义上来讲**

**共享表空间**: 会把表集中存储在一个系统表空间里。即每一个数据库的所有表的数据，索引文件全部放在一个文件中。该文件目录默认的是服务器的数据目录。 默认的文件名为:ibdata1 初始化为10M。

**独占表空间**: 每一个表分别创建一个表空间，这时。在对应的数据库目录里每一个表都有.ibd文件(这个文件包括了单独一个表的数据内容以及索引内容)。

**1.3、MyISAM数据库表文件**

MyISAM表是独立于操作系统的，这说明可以轻松地将其从Windows服务器移植到Linux服务器；每当我们建立一个MyISAM引擎的表时，就会在本地磁盘上建立三个文件，文件名就是表名。例如，我建立了一个MyISAM引擎的test表，那么就会生成以下三个文件：

MyISAM：当建立一个MyISAM引擎的表时，就会在本地磁盘上建立三个文件。

table.frm，存储表定义；  
table.MYD，存储数据；  
table.MYI，存储索引

PS：table创建的表名称。

**2、存储空间上区别**

MyISAM：可被压缩，存储空间较小。

InnoDB：需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

MyISAM的索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。能加载更多索引，而Innodb是索引和数据是紧密捆绑的，没有使用压缩从而会造成Innodb比MyISAM体积庞大不小。

**3、可移植性、备份及恢复上区别**

MyISAM：数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。

InnoDB：免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了。

**4、事务支持**

MyISAM：强调的是性能，每次查询具有原子性,其执行数度比InnoDB类型更快，但是不提供事务支持。

InnoDB提供事务支持事务，外部键（foreign key）等高级数据库功能。

**5、AUTO\_INCREMENT**

MyISAM：可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，他可以根据前面几列进行排序后递增。

InnoDB：InnoDB会自动创建Auto\_Increment类型字段的索引，一般习惯应用于主键，即主键索引。InnoDB中必须包含只有该字段的索引。引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列。

**6、表锁差异**

MyISAM：只支持表级锁，只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁。

nnoDB：支持事务和行级锁，是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表， 例如update table set num=1 where name like “%aaa%”

**7、全文索引**

MyISAM：支持 FULLTEXT类型的全文索引。不支持中文。

InnoDB：不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。

**8、表主键**

MyISAM：允许没有任何索引和主键的表存在，索引都是保存行的地址。

InnoDB：如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值

**9、表的具体行数**

MyISAM：保存有表的总行数，如果select count(\*) from table;会直接取出出该值。

InnoDB：没有保存表的总行数，如果使用select count(\*) from table；就会遍历整个表，消耗相当大，但是在加了wehre条件后，myisam和innodb处理的方式都一样。

**10、 CURD操作**

MyISAM：如果执行大量的SELECT，MyISAM是更好的选择。

InnoDB：如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。

DELETE 从性能上InnoDB更优，但DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除，在innodb上如果要清空保存有大量数据的表，最好使用truncate table这个命令来删除所有数据。

**使用场景建议**

MyISAM适合：  
(1)做很多count 的计算；  
(2)插入不频繁，查询非常频繁；  
(3)没有事务。

InnoDB适合：  
(1)可靠性要求比较高，或者要求事务；  
(2)表更新和查询都相当的频繁，并且表锁定的机会比较大的情况。

## 四、谈谈你对MySQL索引的理解？ ##

在mysql中支持有两种类型，最常用的也是默认的Btree类型，其次就是最容易被忽略的Hash类型

**1、hash索引**

hash 索引结构的特殊性，其检索效率非常高，Hash索引可以根据数据的hash值直接定位到索引数据的存储位置，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引。（hash索引通过hash值快速定位，而B-Tree需要从树根到叶子节点查询，因此hash查询效率高于B-Tree）

可 能很多人又有疑问了，既然 Hash 索引的效率要比 B-Tree 高很多，为什么大家不都用 Hash 索引而还要使用 B-Tree 索引呢？任何事物都是有两面性的，Hash 索引也一样，虽然 Hash 索引效率高，但是 Hash 索引本身由于其特殊性也带来了很多限制和弊端，主要有以下这些。

**（1）Hash 索引仅仅能满足"=",“IN"和”<=>"查询，不能使用范围查询。即 ：like查询不能使用hash**

由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。

**（2）Hash 索引无法被用来避免数据的排序操作。**

由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算；

**（3）Hash 索引不能利用部分索引键查询。**

对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。

**（4）Hash 索引在任何时候都不能避免表扫描。**

前面已经知道，**Hash 索引是将索引键通过 Hash 运算**之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。(先找到相同hash值后，再比较实际这些列的数据进行比较）

**（5）Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。**

对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。

> innodb引擎有一个特殊的功能叫做自适应哈希索引，当innodb注意到某些索引值被使用的非常频繁时，它会在内存中基于btree索引之上再创建一个哈希索引，这样就让btree索引也具有哈希索引的一些优点，比如：快速的哈希查找，这是一个全自动的，内部的行为，用户无法控制或者配置，不过如果有必要，可以选择关闭这个功能（innodb\_adaptive\_hash\_index=OFF，默认为ON）。

**2、BTree 索引**

**2.1、MyISAM索引实现**

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址

下图是MyISAM索引的原理图  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI5OTE0ODM3_size_16_color_FFFFFF_t_70_pic_center]

这里设表一共有三列，假设我们以Col1为主键，则图8是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI5OTE0ODM3_size_16_color_FFFFFF_t_70_pic_center 1]  
同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

**2.2、InnoDB索引实现**

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI5OTE0ODM3_size_16_color_FFFFFF_t_70_pic_center 2]

图10是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，图11为定义在Col3上的一个辅助索引：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI5OTE0ODM3_size_16_color_FFFFFF_t_70_pic_center 3]  
这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

**MySQL的BTree索引使用的是B树中的B+Tree，但对于主要的两种存储引擎的实现方式是不同的**。

MyISAM: B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。

InnoDB: 其数据文件本身就是索引文件。相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）”。

而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。  
因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

## 四、MySQL数据库优化方式？ ##

[MySQL（五）------数据库性能优化][MySQL_------]

## 五、Mysql如何为表字段添加索引？ ##

1.添加PRIMARY KEY（主键索引）

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

2.添加UNIQUE(唯一索引)

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

3.添加INDEX(普通索引)

ALTER TABLE `table_name` ADD INDEX index\_name ( `column` )

4.添加FULLTEXT(全文索引)

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

5.添加多列索引

ALTER TABLE `table_name` ADD INDEX index\_name ( `column1`, `column2`, `column3` )

## 六、为什么索引能提高查询速度? ##

索引是帮助MySQL高效获取数据的数据结构

数据库查询是数据库最主要的功能之一，我们都希望查询数据的速度尽可能的快，因此数据库系统的设计者会从查询算法的角度进行优化。

最基本的查询算法当然是顺序查找，当然这种时间复杂度为O(n)的算法在数据量很大时显然是糟糕的，于是有了二分查找、二叉树查找等。

但是二分查找要求被检索数据有序，而二叉树查找只能应用于二叉查找树，但是数据本身的组织结构不可能完全满足各种数据结构。

所以，在数据之外，数据库系统还维护者满足特定查找算法的数据结构，这些数据结构以某种方式引用数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

目前大部分数据库系统及文件系统都采用B-Tree和B+Tree作为索引结构。

## 七、什么是最左前缀原则？ ##

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 ; // 无法命中索引

这里需要注意的是，查询的时候如果两个条件都用上了，但是顺序不同，如 city= xx and name ＝xx，那么现在的查询引擎会自动优化为匹配联合索引的顺序，这样是能够命中索引的。

由于最左前缀原则，在创建联合索引时，索引字段的顺序需要考虑字段值去重之后的个数，较多的放前面。ORDER BY子句也遵循此规则。

## 八、如何进行大表优化？ ##

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

1.  限定数据的范围  
    务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内；
2.  读/写分离  
    经典的数据库拆分方案，主库负责写，从库负责读；
3.  垂直分区  
    根据数据库里面数据表的相关性进行拆分。 例如，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。

简单来说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。 如下图所示，这样来说大家应该就更容易理解了。 数据库垂直分区

垂直拆分的优点： 可以使得列数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。  
垂直拆分的缺点： 主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂；  
4. 水平分区  
保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。 水平拆分可以支撑非常大的数据量。

水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。

数据库水平拆分

水平拆分可以支持非常大的数据量。需要注意的一点是：分表仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义，所以 水平拆分最好分库 。

水平拆分能够 支持非常大的数据量存储，应用端改造也少，但 分片事务难以解决 ，跨节点Join性能较差，逻辑复杂。《Java工程师修炼之道》的作者推荐 尽量不要对数据进行分片，因为拆分会带来逻辑、部署、运维的各种复杂度 ，一般的数据表在优化得当的情况下支撑千万以下的数据量是没有太大问题的。如果实在要分片，尽量选择客户端分片架构，这样可以减少一次和中间件的网络I/O。

下面补充一下数据库分片的两种常见方案：

客户端代理： 分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现。 当当网的 Sharding-JDBC 、阿里的TDDL是两种比较常用的实现。  
中间件代理： 在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。 我们现在谈的 Mycat 、360的Atlas、网易的DDB等等都是这种架构的实现。

## 九、分库分表之后,id 主键如何处理？ ##

为要是分成多个表之后，每个表都是从 1 开始累加，这样是不对的，我们需要一个全局唯一的 id 来支持。

生成全局 id 有下面这几种方式：

UUID：不适合作为主键，因为太长了，并且无序不可读，查询效率低。比较适合用于生成唯一的名字的标示比如文件的名字。

数据库自增 id : 两台数据库分别设置不同步长，生成不重复ID的策略来实现高可用。这种方式生成的 id 有序，但是需要独立部署数据库实例，成本高，还会有性能瓶颈。

利用 redis 生成 id : 性能比较好，灵活方便，不依赖于数据库。但是，引入了新的组件造成系统更加复杂，可用性降低，编码更加复杂，增加了系统成本。

Twitter的snowflake算法 ：Github 地址：https://github.com/twitter-archive/snowflake。

美团的Leaf分布式ID生成系统 ：Leaf 是美团开源的分布式ID生成器，能保证全局唯一性、趋势递增、单调递增、信息安全，里面也提到了几种分布式方案的对比，但也需要依赖关系数据库、Zookeeper等中间件。感觉还不错。美团技术团队的一篇文章：https://tech.meituan.com/2017/04/21/mt-leaf.html 。

## 十、并发事务会带来哪些问题? ##

在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对同一数据进行操作）。并发虽然是必须的，但可能会导致以下的问题。

脏读（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）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。  
不可重复读和幻读区别：

不可重复读的重点是修改比如多次读取一条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除比如多次读取一条记录发现记录增多或减少了

## 十一、谈谈锁机制与InnoDB锁算法？ ##

MyISAM和InnoDB存储引擎使用的锁：

MyISAM采用表级锁(table-level locking)。  
InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁  
表级锁和行级锁对比：

表级锁： MySQL中锁定 粒度最大 的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和 InnoDB引擎都支持表级锁。  
行级锁： MySQL中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。  
详细内容可以参考： MySQL锁机制简单了解一下：\[https://blog.csdn.net/qq\_34337272/article/details/80611486\](

## 十二、订单表数据量越来越大导致查询缓慢, 如何处理？ ##

分库分表. 由于历史订单使用率并不高, 高频的可能只是近期订单, 因此, 将订单表按照时间进行拆分, 根据数据量的大小考虑按月分表或按年分表. 订单ID最好包含时间(如根据雪花算法生成), 此时既能根据订单ID直接获取到订单记录, 也能按照时间进行查询。

## 十三、简要说一下数据库范式？ ##

第一范式（1NF）：强调的是列的原子性，即数据库表的每一列都是不可分割的原子数据项。

第二范式（2NF）：要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。（在1NF基础上消除非主属性对主键的部分函数依赖）

第三范式（3NF）：任何非主属性不依赖于其它非主属性。（在2NF基础上消除传递依赖）

**通俗的理解**

第一范式就是属性不可分割，每个字段都应该是不可再拆分的。比如一个字段是姓名（NAME），在国内的话通常理解都是姓名是一个不可再拆分的单位，这时候就符合第一范式；但是在国外的话还要分为FIRST NAME和LAST NAME，这时候姓名这个字段就是还可以拆分为更小的单位的字段，就不符合第一范式了。

第二范式就是要求表中要有主键，表中其他其他字段都依赖于主键，因此第二范式只要记住主键约束就好了。比如说有一个表是学生表，学生表中有一个值唯一的字段学号，那么学生表中的其他所有字段都可以根据这个学号字段去获取，依赖主键的意思也就是相关的意思，因为学号的值是唯一的，因此就不会造成存储的信息对不上的问题，即学生001的姓名不会存到学生002那里去。

第三范式就是要求表中不能有其他表中存在的、存储相同信息的字段，通常实现是在通过外键去建立关联，因此第三范式只要记住外键约束就好了。比如说有一个表是学生表，学生表中有学号，姓名等字段，那如果要把他的系编号，系主任，系主任也存到这个学生表中，那就会造成数据大量的冗余，一是这些信息在系信息表中已存在，二是系中有1000个学生的话这些信息就要存1000遍。因此第三范式的做法是在学生表中增加一个系编号的字段（外键），与系信息表做关联。

## 十四、一千万条数据的表, 如何分页查询？ ##

select \* from stu limit 2,4;

即：取stu表中第3至第6条，共4条记录。

select \* from stu limit 5;

即：取stu表中前5条，共5条记录。

1）limit语句的查询时间与起始记录的位置成正比  
2）mysql的limit语句是很方便，但是对记录很多的表并不适合直接使用。

**对limit分页问题的性能优化方法**

利用表的覆盖索引来加速分页查询

SELECT * FROM product WHERE ID > =(select id from product limit 866613, 1) limit 20

然, 这种情况下ID必须是有序的, 这也是有序ID的好处之一.

## 十五、唯一索引比普通索引快吗, 为什么？ ##

唯一索引不一定比普通索引快, 还可能慢.

查询时, 在未使用limit 1的情况下, 在匹配到一条数据后, 唯一索引即返回, 普通索引会继续匹配下一条数据, 发现不匹配后返回. 如此看来唯一索引少了一次匹配, 但实际上这个消耗微乎其微. 更新时, 这个情况就比较复杂了. 普通索引将记录放到change buffer中语句就执行完毕了. 而对唯一索引而言, 它必须要校验唯一性, 因此, 必须将数据页读入内存确定没有冲突, 然后才能继续操作. 对于写多读少的情况, 普通索引利用change buffer有效减少了对磁盘的访问次数, 因此普通索引性能要高于唯一索引.

## 十六、常用的Mysql复制架构有哪些？ ##

1）一主多从 在主库读取请求压力非常大的场景下, 可以通过配置一主多从复制架构实现读写分离, 把大量对实时性要求不是特别高的读请求通过负载均衡分布到多个从库上, 降低主库的读取压力，在主库出现异常宕机的情况下, 可以把一个从库切换为主库继续提供服务 。

2）多级复制 一主多从的架构能够解决大部分读请求压力特别大的场景的需求, 考虑到 MysQL的复制是主库“推送” Binlog日志到从库,主库的 I/0压力和网络压力会随着从库的增加而增长(每个从库都会在主库上有一个独立的 Binlog Dump线程来发送事件), 而多级复制架构解决了一主多从场景下,主库额外的 I/0和网络压力。

3）双主复制/Dual Master 其实就是主库 Master和 Master2互为主从， client客户端的写请求都访问主库 Master,而读请求可以选择访问主库 Master或 Master2。

## 十七、怎么验证 MySQL 的索引是否满足需求？ ##

使用 explain 查看 SQL 是如何执行查询语句的，从而分析你的索引是否满足需求。 explain 语法：explain select \* from table where type=1。

## 十八、说一下 MySQL 的行锁和表锁？ ##

MyISAM 只支持表锁，InnoDB 支持表锁和行锁，默认为行锁。 表级锁：开销小，加锁快，不会出现死锁。锁定粒度大，发生锁冲突的概率最高，并发量最低。 行级锁：开销大，加锁慢，会出现死锁。锁力度小，发生锁冲突的概率小，并发度最高。

## 十九、说一下乐观锁和悲观锁？ ##

乐观锁：每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。

悲观锁：每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻止，直到这个锁被释放。 数据库的乐观锁需要自己实现，在表里面添加一个 version 字段，每次修改成功值加 1，这样每次修改的时候先对比一下，自己拥有的 version 和数据库现在的 version 是否一致，如果不一致就不修改，这样就实现了乐观锁。

## 二十、MySQL 问题排查都有哪些手段？ ##

使用 show processlist 命令查看当前所有连接信息。 使用 explain 命令查询 SQL 语句执行计划。 开启慢查询日志，查看慢查询的 SQL。

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

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

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

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

## 二十二、说一下 ACID 是什么？ ##

Atomicity（原子性）：一个事务（transaction）中的所有操作，或者全部完成，或者全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。即，事务不可分割、不可约简。

Consistency（一致性）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设约束、触发器、级联回滚等。

Isolation（隔离性）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

Durability（持久性）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

## 二十三、char与varchar的区别 ##

char 是一种固定长度的字符串类型  
varchar 是一种可变长度的字符串类型

## 二十四、什么是触发器，MySQL都有哪些触发器？ ##

触发器是指一段代码，当触发某个事件时，自动执行这些代码

MySQL 数据库中有六种触发器：

Before Insert  
After Insert  
Before Update  
After Update  
Before Delete  
After Delete  
使用场景：

可以通过数据库中的相关表实现级联更改  
实时监控表中字段的更改做出相应处理  
注意：滥用会造成数据库及应用程序的维护困难

## 二十五、FLOAT和DOUBLE的区别是什么？ ##

FLOAT 类型数据可以存储至多 8 位十进制数，占 4 字节  
DOUBLE 类型数据可以存储至多 18 位十进制数，占 8字节

## 二十六、索引的种类有哪些？ ##

普通索引：最基本的索引，没有任何约束限制。  
唯一索引：和普通索引类似，但是具有唯一性约束，可以有 null  
主键索引：特殊的唯一索引，不允许有 null，一张表最多一个主键索引  
组合索引：多列值组成一个索引，用于组合搜索，效率大于索引合并  
全文索引：对文本的内容进行分词、搜索  
覆盖索引：查询列要被所建的索引覆盖，不必读取数据行

## 二十七、索引对性能有哪些影响？ ##

优点：

减少数据库服务器需要扫描的数据量  
帮助数据库服务器避免排序和临时表  
将随机 I/O 变顺序I/O  
提高查询速度  
唯一索引，能保证数据的唯一性

缺点：

索引的创建和维护耗时随着数据量的增加而增加  
对表中数据进行增删改时，索引也要动态维护，降低了数据的维护速度  
增大磁盘占用

## 二十八、MySQL创建和使用索引的注意事项？ ##

适合创建索引的列是出现在 WHERE 或 ON 子句中的列，而不是出现在 SELECT 关键字后的列  
索引列的基数越大，数据区分度越高，索引的效果越好  
对字符串列进行索引，可制定一个前缀长度，节省索引空间  
避免创建过多的索引，索引会额外占用磁盘空间，降低写操作效率  
主键尽可能选择较短的数据类型，可减少索引的磁盘占用，提高查询效率  
联合索引遵循前缀原则  
LIKE 查询，%在前不到索引，可考虑使用 ElasticSearch、Lucene 等搜索引擎  
MySQL 在数据量较小的情况可能会不使用索引，因为全表扫描比使用索引速度更快  
关键词 or 前面的条件中的列有索引，后面的没有，所有列的索引都不会被用到  
列类型是字符串，查询时一定要给值加引号，否则索引失效  
联合索引要遵从最左前缀原则，否则不会用到索引

## 二十九、创建MySQL联合索引应该注意什么？ ##

联合索引要遵从最左前缀原则，否则不会用到索引  
Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。  
如索引是 index (a,b,c)，可以支持 a 或 a,b 或 a,b,c 3种组合进行查找，但不支持 b,c 进行查找

## 三十、列值为NULL时，查询是否会用到索引？ ##

MySQL 中存在 NULL 值的列也是走索引的

计划对列进行索引，应尽量避免把它设置为可空，因为这会让 MySQL 难以优化引用了可空列的查询，同时增加了引擎的复杂度

## 三十一、Mysql中exists和in的区别 ##

下面将主查询的表称为外表；子查询的表称为内表。exists 与 in 的主要区别如下：

子查询使用 exists，会先进行主查询，将查询到的每行数据循环带入子查询校验是否存在，过滤出整体的返回数据；子查询使用 in，会先进行子查询获取结果集，然后主查询匹配子查询的结果集，返回数据  
外表内表相对大小情况不一样时，查询效率不一样：两表大小相当，in 和 exists 差别不大；内表大，用 exists 效率较高；内表小，用 in 效率较高。  
不管外表与内表的大小，not exists 的效率一般要高于 not in，跟子查询的索引访问类型有关。

## 三十二、说一些索引失效的情况 ##

如果条件中有 or，即使其中有部分条件是索引字段，也不会使用索引  
复合索引，查询条件不使用索引前面的字段，后续字段也将无法使用索引  
以 % 开头的 like 查询  
索引列的数据类型存在隐形转换  
where 子句里对索引列有数学运算  
where 子句里对索引列使用函数  
MySQL 引擎估算使用全表扫描要比使用索引快，则不使用索引

## 三十三、举例说明数据库死锁 ##

加锁顺序不一致可能会导致死锁：

1、事务 1 持有 id = 1 的行锁，更新 id = 2 的行数据；事务 2 持有 id = 2 的行锁，更新 id = 1的行数据

![在这里插入图片描述][20200815020746522.png_pic_center]

2、在范围查询更新时，加锁是一条记录一条记录挨个加锁的，数据行被加锁顺序不一样也会导致死锁

事务1                                        
    update table set name = 'A' where id <100;
    
    事务2
    update table set name = 'B' where age > 25;

## 三十四、MySQL中如何避免死锁？ ##

尽量以相同的顺序来访问索引记录和表

业务上能够接受幻读和不可重复读，考虑降低锁的级别到 Read committed，降低死锁发生的概率  
添加合理的索引，走索引避免为每一行加锁，降低死锁的概率

在事务中一次锁定所需要的所有资源，如 MyISAM 引擎的表锁

避免大事务，尽量将大事务拆成多个小事务来处理

尽量避免同时并发对同一表进行读写操作，特别是执行加锁且操作数据量较大的语句

设置锁等待超时参数

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI5OTE0ODM3_size_16_color_FFFFFF_t_70_pic_center]: https://img-blog.csdnimg.cn/20200815000014180.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI5OTE0ODM3,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI5OTE0ODM3_size_16_color_FFFFFF_t_70_pic_center 1]: https://img-blog.csdnimg.cn/20200815000313607.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI5OTE0ODM3,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI5OTE0ODM3_size_16_color_FFFFFF_t_70_pic_center 2]: https://img-blog.csdnimg.cn/20200815000442691.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI5OTE0ODM3,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI5OTE0ODM3_size_16_color_FFFFFF_t_70_pic_center 3]: https://img-blog.csdnimg.cn/20200815000502560.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI5OTE0ODM3,size_16,color_FFFFFF,t_70#pic_center
[MySQL_------]: https://blog.csdn.net/qq_29914837/article/details/104867421
[20200815020746522.png_pic_center]: https://img-blog.csdnimg.cn/20200815020746522.png#pic_center