MySQL篇

1.char、varchar的区别是什么？

varchar是变长而char的长度是固定的。如果你的内容是固定大小的，你会得到更好的性能。

2.数据库的三范式是什么

第一范式：列不可再分

第二范式：行可以唯一区分，主键约束

第三范式：表的非主属性不能依赖与其他表的非主属性 外键约束

且三大范式是一级一级依赖的，第二范式建立在第一范式上，第三范式建立第一第二范式上。

3.数据库引擎有哪些

如何查看mysql提供的所有存储引擎?

show engines;

image-20230830133400994

mysql常用引擎包括：MYISAM、Innodb、Memory、MERGE

  • MYISAM：全表锁，拥有较高的执行速度，不支持事务，不支持外键，并发性能差，占用空间相对较小，对事务完整性没有要求，以select、insert为主的应用基本上可以使用这引擎

  • Innodb:行级锁，提供了具有提交、回滚和崩溃回复能力的事务安全，支持自动增长列，支持外键约束，并发能力强，占用空间是MYISAM的2.5倍，处理效率相对会差一些

  • Memory:全表锁，存储在内存中，速度快，但会占用和数据量成正比的内存空间且数据在mysql重启时会丢失，默认使用HASH索引，检索效率非常高，但不适用于精确查找，主要用于那些内容变化不频繁的代码表

  • MERGE：是一组MYISAM表的组合

4.InnoDB与MyISAM的区别

  1. InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；

  2. InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；

  3. InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

  4. InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；

  5. Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高；

如何选择引擎？

如果没有特别的需求，使用默认的Innodb 即可。

MyISAM：以读写插入为主的应用程序，比如博客系统、新闻门户网站，不支持事务和行级锁，崩溃后无法安全恢复。

InnoDB引擎，最重要，使用最广泛的存储引擎。被用来设计处理大量短期事务，具有高性能和自动崩溃恢复的特性。

更新（删除）操作频率也高，或者要保证数据的完整性；并发量高，支持事务和外键。比如 OA自动化办公系统。

5.数据库的事务

什么是事务？： 多条sql语句，要么全部成功，要么全部失败

事务的特性：

数据库事务特性：原子性(Atomic)、一致性(Consistency)、隔离性(Isolation)、持久性(Durabiliy)。

简称ACID。

  • 原子性：组成一个事务的多个数据库操作是一个不可分割的原子单元，只有所有操作都成功，整个事务才会提交。任何一个操作失败，已经执行的任何操作都必须撤销，让数据库返回初始状态。

  • 一致性：事务操作成功后，数据库所处的状态和它的业务规则是一致的。即数据不会被破坏。如A转账100元给B，不管操作是否成功，A和B的账户总额是不变的。

  • 隔离性：在并发数据操作时，不同的事务拥有各自的数据空间，它们的操作不会对彼此产生干扰

  • 持久性：一旦事务提交成功，事务中的所有操作都必须持久化到数据库中。事务保证一组原子性的操作，要么全部成功，要么全部失败。一旦失败，回滚之前的所有操作。MySql采用自动提交，如果不是显式的开启一个事务，则每个查询都作为一个事务。

隔离级别控制了一个事务中的修改，哪些在事务内和事务间是可见的。四种常见的隔离级别：

「未提交读」（Read UnCommitted），事务中的修改，即使没提交对其他事务也是可见的。事务可能读取未提交的数据，造成脏读。

「提交读」（Read Committed），一个事务开始时，只能看见已提交的事务所做的修改。事务未提交之前，所做的修改对其他事务是不可见的。也叫不可重复读，同一个事务多次读取同样记录可能不同。

「可重复读」（RepeatTable Read），同一个事务中多次读取同样的记录结果时结果相同。

「可串行化」（Serializable），最高隔离级别，强制事务串行执行。

6.什么是触发器，MySQL中都有哪些触发器？

触发器是指一段代码，当触发某个事件时，自动执行这些代码。在MySQL数据库中有如下六种触发器：

1、Before Insert

2、After Insert

3、Before Update

4、After Update

5、Before Delete

6、After Delete

7.MySQL索引的注意事项

1、联合索引遵循前缀原则

WHERE a = 1 AND b = 2 AND c = 3
WHERE a = 1 AND b = 2
WHERE a = 1
#以上SQL语句可以用到索引
WHERE b = 2 AND c = 3
WHERE a = 1 AND c = 3
#以上SQL语句用不到索引

2、LIKE查询，%不能在前

WHERE name LIKE "%wang%"
#以上语句用不到索引，可以用外部的ElasticSearch、Lucene等全文搜索引擎替代。

3、列值为空（NULL）时是可以使用索引的，但MySQL难以优化引用了可空列的查询,它会使索引、索引统计和值更加复杂。可空列需要更多的储存空间，还需要在MySQL内部进行特殊处理。

4、如果MySQL估计使用索引比全表扫描更慢，会放弃使用索引，例如：

表中只有100条数据左右。对于SQL语句WHERE id > 1 AND id < 100，MySQL会优先考虑全表扫描。

5、如果关键词or前面的条件中的列有索引，后面的没有，所有列的索引都不会被用到。

6、列类型是字符串，查询时一定要给值加引号，否则索引失效，例如：列name varchar(16)，存储了字符串"100" WHERE name = 100;

以上SQL语句能搜到，但无法用到索引。

8.MySQL索引的原理

1、MySQL索引是用一种叫做聚簇索引的数据结构实现的，下面我们就来看一下什么是聚簇索引。

2、聚簇索引是一种数据存储方式，它实际上是在同一个结构中保存了B+树索引和数据行，InnoDB表是按照聚簇索引组织的（类似于Oracle的索引组织表）。

注：B+ 树是一种树数据结构，是一个n叉排序树，每个节点通常有多个孩子，一棵B+树包含根节点、内部节点和叶子节点。根节点可能是一个叶子节点，也可能是一个包含两个或两个以上孩子节点的节点。B+ 树通常用于数据库和操作系统的文件系统中。NTFS, ReiserFS, NSS, XFS, JFS, ReFS 和BFS等文件系统都在使用B+树作为元数据索引。B+ 树的特点是能够保持数据稳定有序，其插入与修改拥有较稳定的对数时间复杂度。B+ 树元素自底向上插入。

InnoDB通过主键聚簇数据，如果没有定义主键，会选择一个唯一的非空索引代替，如果没有这样的索引，会隐式定义个主键作为聚簇索引。

下图形象说明了聚簇索引表(InnoDB)和普通的堆组织表(MyISAM)的区别：

最常问的MySQL面试题三——每个开发人员都应该知道

对于普通的堆组织表来说（右图），表数据和索引是分别存储的，主键索引和二级索引存储上没有任何区别。

而对于聚簇索引表来说（左图），表数据是和主键一起存储的，主键索引的叶结点存储行数据，二级索引的叶结点存储行的主键值。

聚簇索引表最大限度地提高了I/O密集型应用的性能，但它也有以下几个限制：

1）插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键。

2）更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。

3）二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。二级索引的叶节点存储的是主键值，而不是行指针，这是为了减少当出现行移动或数据页分裂时二级索引的维护工作，但会让二级索引占用更多的空间。

解题方法

在一些MySQL索引基础考题中，我们可以轻松的通过索引基础和类型来解决此类问题，对于一些索引创建注意事项方面的考点，我们可以通过索引创建原则和注意事项来解决。

创建MySQL联合索引应该注意什么？

需遵循前缀原则

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

在MySQL里NULL值的列也是走索引的。当然，如果计划对列进行索引，就要尽量避免把它设置为可空，MySQL难以优化引用了可空列的查询,它会使索引、索引统计和值更加复杂。

以下语句是否会应用索引：

SELECT FROM users WHERE YEAR(adddate) < 2007;*

不会，因为只要列涉及到运算，MySQL就不会使用索引。

9.MyISAM索引实现？

MyISAM存储引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。MyISAM的索引方式也叫做非聚簇索引的，之所以这么称呼是为了与InnoDB的聚簇索引区分。

10.MyISAM索引与InnoDB索引的区别？

InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。

InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。

MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。

InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。

以下三条sql 如何建索引，只建一条怎么建？

WHERE a=1 AND b=1

WHERE b=1

WHERE b=1 ORDER BY time DESC

以顺序b,a,time建立联合索引，CREATE INDEX table1_b_a_time ON index_test01(b,a,time)。因为最新MySQL版本会优化WHERE子句后面的列顺序，以匹配联合索引顺序。

11.索引问题

索引是对数据库表中一个或多个列的值进行排序的结构，建立索引有助于快速获取信息。

你也可以这样理解：索引就是加快检索表中数据的方法。数据库的索引类似于书籍的索引。在书籍中，索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。索引包含一个或多个列的值。MySql只能高效的利用索引的最左前缀列。索引的优势：

  1. 减少查询扫描的数据量

  2. 避免排序和零时表

  3. 将随机IO变为顺序IO （顺序IO的效率高于随机IO）

「B-Tree」

使用最多的索引类型。采用B-Tree数据结构来存储数据（每个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的遍历）。B-Tree索引适用于全键值，键值范围，键前缀查找，支持排序。

B-Tree索引限制：

如果不是按照索引的最左列开始查询，则无法使用索引。

不能跳过索引中的列。如果使用第一列和第三列索引，则只能使用第一列索引。

如果查询中有个范围查询，则其右边的所有列都无法使用索引优化查询。

「哈希索引」

只有精确匹配索引的所有列，查询才有效。存储引擎会对所有的索引列计算一个哈希码，哈希索引将所有的哈希码存储在索引中，并保存指向每个数据行的指针。

哈希索引限制：

无法用于排序

不支持部分匹配

只支持等值查询如=，IN（），不支持 < >

「优化建议点」

注意每种索引的适用范围和适用限制。

索引的列如果是表达式的一部分或者是函数的参数，则失效。

针对特别长的字符串，可以使用前缀索引，根据索引的选择性选择合适的前缀长度。

使用多列索引的时候，可以通过 AND 和 OR 语法连接。

重复索引没必要，如（A，B）和（A）重复。

索引在where条件查询和group by语法查询的时候特别有效。

将范围查询放在条件查询的最后，防止范围查询导致的右边索引失效的问题。

索引最好不要选择过长的字符串，而且索引列也不宜为null。

mysql 有4种不同的索引：75

ALTER TABLE table_name ADD UNIQUE (column);

  1. 主键索引（PRIMARY）

数据列不允许重复，不允许为NULL，一个表只能有一个主键。

  1. 唯一索引（UNIQUE）

数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。

可以通过 创建唯一索引

可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引

  1. 普通索引（INDEX）

可以通过ALTER TABLE table_name ADD INDEX index_name (column); 创建普通索引

可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2,

column3); 创建组合索引

  1. 全文索引（FULLTEXT）

可以通过ALTER TABLE table_name ADD FULLTEXT (column); 创建全文索引

索引并非是越多越好，创建索引也需要耗费资源，一是增加了数据库的存储空间，二是在插入和删除时 要花费较多的时间维护索引7

索引加快数据库的检索速度

索引降低了插入、删除、修改等维护任务的速度

唯一索引可以确保每一行数据的唯一性

通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能

索引需要占物理和数据空间

12.SQL优化

1、查询语句中不要使用select *

2、尽量减少子查询，使用关联查询（left join,right join,inner join）替代

3、减少使用IN或者NOT IN ,使用exists，not exists或者关联查询语句替代

4、or 的查询尽量用 union或者union all 代替(在确认没有重复数据或者不用剔除重复数据时，union all会更好)

5、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

6、应尽量避免在 where子句中对字段进行 null值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num is null 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： select id from t where num=0

7、like ‘%xx%’尽量改成‘xx%’，以%开头无法命中索引，如确实要用’%xx%’可使用外部全文索引技术，如：Lucene 、Elasticsearch

8、使用explain对查询性能进行分析

13.简单说一说drop、delete与truncate的区别

SQL中的drop、delete、truncate都表示删除，但是三者有一些差别

delete和truncate只删除表的数据不删除表的结构

速度,一般来说: drop> truncate >delete

delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;

如果有相应的trigger,执行的时候将被触发. truncate,drop是ddl, 操作立即生效,原数据不放到rollback

segment中,不能回滚. 操作不触发trigger.

14.什么是视图

视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，试图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。

15.什么是内联接、左外联接、右外联接？

内联接（Inner Join）：匹配2张表中相关联的记录。

左外联接（Left Outer Join）：除了匹配2张表中相关联的记录外，还会匹配左表中剩余的记录，右表中未匹配到的字段用NULL表示。

右外联接（Right Outer Join）：除了匹配2张表中相关联的记录外，还会匹配右表中剩余的记录， 左表中未匹配到的字段用NULL表示。在判定左表和右表时，要根据表名出现在Outer Join的左右位置关系。

16.并发事务带来哪些问题?

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

  • **脏读（**Dirtyread）:当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。

  • **丢失修改（**Losttomodify）:指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。 例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。

  • **不可重复读（**Unrepeatableread）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

  • **幻读（**Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

不可重复读和幻读区别：

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

17.事务隔离级别有哪些?MySQL的默认隔离级别是?

SQL 标准定义了四个隔离级别：

  • **READ-UNCOMMITTED(**读取未提交)：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

  • **READ-COMMITTED(**读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。

  • **REPEATABLE-READ(**可重复读)：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。

  • **SERIALIZABLE(**可串行化)：最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

隔离级别脏读不可重复读幻读
READ-UNCOMMITTED√√√
READ-COMMITTED×√√
REPEATABLE-READ××√
SERIALIZABLE×××

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。我们可以通过

SELECT @@tx_isolation; 命令来查看

mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+

这里需要注意的是：与 SQL 标准不同的地方在于 InnoDB 存储引擎在 REPEATABLE-READ（可重读）事务隔离级别下使用的是Next-Key Lock 锁算法，因此可以避免幻读的产生，这与其他数据库系统(如SQL Server) 是不同的。所以说InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读） 已经可以完全保证事务的隔离性要求，即达到了 SQL标准的 SERIALIZABLE(可串行化)隔离级别。因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是 READ- COMMITTED(读取提交内容) ，但是你要知道的是InnoDB 存储引擎默认使用 REPEAaTABLE- READ（可重读）并不会有任何性能损失。

InnoDB 存储引擎在 分布式事务 的情况下一般会用到 **SERIALIZABLE(可串行化)**隔离级别。

18.大表如何优化？

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

1. 限定数据的范围

务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内；

2. 读/写分离

经典的数据库拆分方案，主库负责写，从库负责读；

3. 垂直分区

根据数据库里面数据表的相关性进行拆分。 例如，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。

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

image-20230830143250906

垂直拆分的优点： 可以使得列数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。

垂直拆分的缺点： 主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂；

4. 水平分区

保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。 水平拆分可以支撑非常大的数据量。

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

image-20230830143343342

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

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

如果实在要分片，尽量选择客户端分片架构，这样可以减少一次和中间件的网络I/O。

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

客户端代理： 分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现 当当网的Sharding-JDBC 、阿里的TDDL是两种比较常用的实现。

中间件代理： 在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。 我们现在谈的 Mycat、360的Atlas、网易的DDB等等都是这种架构的实现。

19.分库分表之后,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 。

20.mysql有关权限的表都有哪几个

MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，由mysql_install_db脚本初始化。这些权限表分别user，db，table_priv，columns_priv和host。下面分别介绍一下这些表的结构和内容

user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。db权限表：记录各个帐号在各个数据库上的操作权限。

table_priv权限表：记录数据表级的操作权限。

columns_priv权限表：记录数据列级的操作权限。

host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。

21.mysql有哪些数据类型

1、整数类型 ，包括TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分别表示1字节、2字节、3字节、4字节、8字节整数。任何整数类型都可以加上UNSIGNED属性，表示数据是无符号的，即非负整数。长度：整数类型可以被指定长度，例如：INT(11)表示长度为11的INT类型。长度在大多数场景是没有意义的，它不会限制值的合法范围，只会影响显示字符的个数，而且需要和UNSIGNED ZEROFILL属性配合使用才有意义。例子，假定类型设定为INT(5)，属性为UNSIGNED ZEROFILL，如果用户插入的数据为12的话，那么数据库实际存储数据为00012。

2、实数类型，包括FLOAT、DOUBLE、DECIMAL。DECIMAL可以用于存储比BIGINT还大的整型，能存储精确的小数。而FLOAT和DOUBLE是有取值范围的，并支持使用标准的浮点进行近似计算。计算时FLOAT和DOUBLE相比DECIMAL效率更高一些，DECIMAL你可以理解成是用字符串进行处理。

3、字符串类型，包括VARCHAR、CHAR、TEXT、BLOB VARCHAR用于存储可变长字符串，它比定长类型更节省空间。VARCHAR使用额外1或2个字节存储字符串长度。列长度小于255字节时，使用1字节表示，否则使用2字节表示。VARCHAR存储的内容超出设置的长度时，内容会被截断。CHAR是定长的，根据定义的字符串长度分配足够的空间。CHAR会根据需要使用空格进行填充方便比较。CHAR适合存储很短的字符串，或者所有值都接近同一个长度。CHAR存储的内容超出设置的长度时，内容同样会被截断。

使用策略：

对于经常变更的数据来说，CHAR比VARCHAR更好，因为CHAR不容易产生碎片。对于非常短的列，CHAR比VARCHAR在存储空间上更有效率。使用时要注意只分配需要的空间，更长的列排序时会消耗更多内存。尽量避免使用TEXT/BLOB类型，查询时会使用临时表，导致严重的性能开销。

4枚举类型（ENUM），把不重复的数据存储为一个预定义的集合。有时可以使用ENUM代替常用的字符串类型。ENUM存储非常紧凑，会把列表值压缩到一个或两个字节。ENUM在内部存储时，其实存的是整数。尽量避免使用数字作为ENUM枚举的常量，因为容易混乱。

排序是按照内部存储的整数

**5、日期和时间类型，**尽量使用timestamp，空间效率高于datetime， 用整数保存时间戳通常不方便处理。 如果需要存储微妙，可以使用bigint存储。 看到这里，这道真题是不是就比较容易回答了。

22.创建索引的三种方式，删除索引

image-20230830145305926

第二种方式：使用ALTER TABLE命令去增加索引

ALTER TABLE table_name ADD INDEX index_name (column_list);

ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。其中table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。

索引名index_name可自己命名，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。

第三种方式：使用CREATE INDEX命令创建

CREATE INDEX index_name ON table_name (column_list);

CREATE INDEX可对表增加普通索引或UNIQUE索引。（但是，不能创建PRIMARY KEY索引）

删除索引

根据索引名删除普通索引、唯一索引、全文索引： alter table 表名 drop KEY 索引名

alter table user_index drop KEY name;
alter table user_index drop KEY id_card;
alter table user_index drop KEY information;

删除主键索引： alter table 表名 drop primary key（因为主键只有一个）。这里值得注意的是，如果主键自增长，那么不能直接执行此操作（自增长依赖于主键索引）：

image-20230830145431193

需要取消自增长再行删除：

alter table user_index
-- 重新定义字段
MODIFY id int,
drop PRIMARY KEY

但通常不会删除主键，因为设计主键一定与业务逻辑无关。

23.锁

数据库通过锁机制来解决并发场景-共享锁（读锁）和排他锁（写锁）。读锁是不阻塞的，多个客户端可以在同一时刻读取同一个资源。写锁是排他的，并且会阻塞其他的读锁和写锁。简单提下乐观锁和悲观锁。

「乐观锁」，通常用于数据竞争不激烈的场景，多读少写，通过版本号和时间戳实现。

「悲观锁」，通常用于数据竞争激烈的场景，每次操作都会锁定数据。要锁定数据需要一定的锁策略来配合。

「表锁」，锁定整张表，开销最小，但是会加剧锁竞争。

「行锁」，锁定行级别，开销最大，但是可以最大程度的支持并发。但是MySql的存储引擎的真实实现不是简单的行级锁，一般都是实现了多版本并发控制（MVCC）。MVCC是行级锁的变种，多数情况下避免了加锁操作，开销更低。MVCC是通过保存数据的某个时间点快照实现的。

24.查询质量的三个重要指标

响应时间 （服务时间，排队时间）

扫描的行

返回的行

25.查询优化点

避免查询无关的列，如使用Select * 返回所有的列。

避免查询无关的行

切分查询。将一个对服务器压力较大的任务，分解到一个较长的时间中，并分多次执行。如要删除一万条数据，可以分10次84

执行，每次执行完成后暂停一段时间，再继续执行。过程中可以释放服务器资源给其他任务。

分解关联查询。将多表关联查询的一次查询，分解成对单表的多次查询。可以减少锁竞争，查询本身的查询效率也比较高。因为MySql的连接和断开都是轻量级的操作，不会由于查询拆分为多次，造成效率问题。注意count的操作只能统计不为null的列，所以统计总的行数使用count（*）。group by 按照标识列分组效率高，分组结果不宜出行分组列之外的列。关联查询延迟关联，可以根据查询条件先缩小各自要查询的范围，再关联。Limit分页优化。可以根据索引覆盖扫描，再根据索引列关联自身查询其他列。如

SELECT id,
       NAME,
       age
WHERE student s1
INNER JOIN (
	SELECT
	id
	FROM
	student
	ORDER BY
	age
	LIMIT 50,5
	) AS s2
ON s1.id = s2.id

Union查询默认去重，如果不是业务必须，建议使用效率更高的Union All

「补充内容」

1.条件中的字段类型和表结构类型不一致，mysql会自动加转换函数，导致索引作为函数中的参数失效。

2.like查询前面部分未输入，以%开头无法命中索引。

3.补充2个5.7版本的新特性：

generated column，就是数据库中这一列由其他列计算而得

CREATE TABLE triangle (sidea DOUBLE, sideb DOUBLE, area DOUBLE AS (sidea * sideb / 2));

insert into triangle(sidea, sideb) values(3, 4);

select * from triangle;

+-------+-------+------+

| sidea | sideb | area |

+-------+-------+------+

| 3

| 4

| 6

|85

+-------+-------+------+

支持JSON格式数据，并提供相关内置函数

CREATE TABLE json_test (name JSON);

INSERT INTO json_test VALUES('{"name1": "value1", "name2": "value2"}');

SELECT * FROM json_test WHERE JSON_CONTAINS(name, '$.name1');

来自JVM专家-达

关注explain在性能分析中的使用

EXPLAIN SELECT settleId FROM Settle WHERE settleId = "3679"

「select_type」，有几种值：simple（表示简单的select，没有union和子查询），primary（有子查询，最外面的select查询就是primary），union（union中的第二个或随后的select查询，不依赖外部查询结果），dependent union（union中的第二个或随后的select查询，依赖外部查询结果）「type」，有几种值：system（表仅有一行（=系统表），这是const连接类型的一个特例），const（常量查询）, ref(非唯一索引访问，只有普通索引)，eq_ref（使用唯一索引或组件查询），all（全表查询），index（根据索引查询全表），range（范围查询）

「possible_keys」: 表中可能帮助查询的索引

「key」，选择使用的索引

「key_len」，使用的索引长度

「rows」，扫描的行数，越大越不好

「extra」，有几种值：Only index（信息从索引中检索出，比扫描表快），where used（使用where限制），Using filesort （可能在内存或磁盘排序），Using temporary（对查询结果排序时使用临时表）