2024届java面试准备--mysql

索引

索引的含义

数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询，更新数据库中表的数据。索引的实现通常使用B树和变种的B+树（MySQL常用的索引就是B+树）。除了数据之外，数据库系统还维护为满足特定查找算法的数据结构，这些数据结构以某种方式引用数据，这种数据结构就是索引。简言之，索引就类似于书本，字典的目录。

• 主键索引：一张表只能有一个主键索引，主键索引列不能有空值和重复值

• 唯一索引：唯一索引不能有相同值，但允许为空

• 普通索引：允许出现重复值

• 组合索引：对多个字段建立一个联合索引，减少索引开销，遵循最左匹配原则

• 全文索引：myisam引擎支持，通过建立倒排索引提升检索效率，广泛用于搜索引擎

创建索引

CREATE  [UNIQUE | FULLTEXT]  INDEX  索引名 ON  表名(字段名) [USING 索引方法]；

说明：
UNIQUE:可选。表示索引为唯一性索引。
FULLTEXT:可选。表示索引为全文索引。
INDEX和KEY:用于指定字段为索引，两者选择其中之一就可以了，作用是一样的。
索引名:可选。给创建的索引取一个新名称。
字段名1:指定索引对应的字段的名称，该字段必须是前面定义好的字段。
注：索引方法默认使用B+TREE。

索引的作用与缺点

作用

通过创建索引，可以再查询的过程中，提高系统的性能

通过创建唯一性索引，可以保持数据库表中每一行数据的唯一性

在使用分组和排序子句进行数据检索时，可以减少查询中分组和排序的时间

缺点

创建索引和维护索引要耗费时间，而且时间随着数据量的增加而增大

索引需要占用物理空间，如果要建立聚簇索引，所需要的空间会更大

在对表中的数据进行增删改时需要耗费较多的时间，因为索引也要动态地维护

索引的使用场景

应创建索引的场景

1. 经常需要搜索的列上
2. 作为主键的列上
3. 经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度
4. 经常需要根据范围进行搜索的列上
5. 经常需要查询条件（where）、排序（order by）、分组（group by）的列上
6. 如果是字符串类型，字符串长度比较长，可以针对字段的特点，建立前缀索引

索引的底层原理

抛开其他的数据库索引实现，主讲MySQL的索引底层实现，其底层是通过B+树来实现的数据结构存储。
数据结构存储，决定了数据查找和操作时的效率，包括时间复杂度和空间复杂度，而在取舍的时候，也无非就是时间换空间，空间换时间的权衡罢了，所以，这就很好的解释了，为什么MySQL在索引的底层设计上，选用了B+树，而没有选用B-树，或是红黑树，AVL树等等其他数据结构。总之，就是使用B+树作为索引的结构存储，能在I/O性能上得到一个较大的优势。

B-Tree

B-树是一种多路自平衡的搜索树，它类似普通的平衡二叉树，不同的一点是B-树允许每个节点有更多的子节点。B-Tree相对于AVLTree缩减了节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效率。
注：B-Tree就是我们常说的B树
那么m阶B-Tree是满足下列条件的数据结构：
所有键值分布在整棵树中
搜索有可能在非叶子节点结束，在关键字全集内做一次查找，性能逼近二分查找
每个节点最多拥有m个子树
根节点至少有2个子树
分支节点至少拥有m/2颗子树（除根节点和叶子节点外都是分支节点）
所有叶子节点都在同一层，每个节点最多可以有m-1个key，并且以升序排列
但同时B-Tree也存在问题：
每个节点中有key，也有data，而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小。
当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率

B+Tree

B+Tree是在B-Tree基础上的一种优化，InnoDB存储引擎就是用B+Tree实现其索引结构。它带来的变化点：
B+树每个节点可以包含更多的节点，这样做有两个原因，一个是降低树的高度。另外一个是将数据范围变为多个区间，区间越多，数据检索越快
非叶子节点存储key，叶子节点存储key和数据
叶子节点两两指针相互链接（符合磁盘的预读特性），顺序查询性能更高

​ B+树的磁盘读写代价低，更少的查询次数，查询效率更加稳定，有利于对数据库的扫描

​ B+树是B树的升级版，B+树只有叶节点存放数据，其余节点用来索引。索引节点可以全部加入内存，增加查询效率，叶子节点可以做双向链表，从而提高范围查找的效率，增加索引的范围

​ 在大规模数据存储的时候，红黑树往往出现由于树的深度过大而造成磁盘IO读写过于频繁，进而导致效率低下的情况。所以，只要我们通过某种较好的树结构减少树的结构尽量减少树的高度，B树与B+树可以有多个子女，从几十到上千，可以降低树的高度。

注：MySQL的InnoDB存储引擎在设计时是将根节点常驻内存，因此力求达到树的深度不超过3，也就是说I/O不需要超过3次。
通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构，因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找的分页查找，另一种是从根节点开始，进行随机查找。

B-树和B+树的区别

B+树内节点不存储数据，所有数据存储在叶节点导致查询时间复杂度固定为log n

B-树查询时间复杂度不固定，与Key在树中的位置有关，最好为O(1)

B+树叶节点两两相连可大大增加区间访问性，可使用在范围查询等

B+树更适合外部存储（存储磁盘数据）。由于内节点无data域，每个节点能索引的范围更大更精确。

四种树

二叉树：索引字段有序，极端情况会变成链表形式

AVL数：树的高度不可控

B数：控制了树的高度，但是索引值和data都分布在每个具体的节点当中，若要进行范围查询，要进行多次回溯，IO开销大

B+树：非叶子节点只存储索引值，叶子节点再存储索引+具体数据，从小到大用链表连接在一起，范围查询可直接遍历不需要回溯7

B+树的优势

（1）IO代价更低。B+树由于非叶子节点中不存放data，因此可以存放更多的索引值（单个大节点的容量固定，每个小单位size变小了），从而使得树的高度更低，磁盘IO次数更少。
（2）查询效率稳定。B+树由于所有data都放在叶子节点中，因此每次查询都要走完整的根节点到叶子节点的路径，所有查询的路径长度相同，查询效率更加稳定。
（3）更利于范围查询。B+树叶子节点之间有指针，注意是双向的指针，更利于范围查询。

Innodb和Myisam引擎

Myisam： 支持表锁，适合读密集的场景，不支持外键，不支持事务，索引与数据在不同的文件

Innodb： 支持行、表锁，默认为行锁，适合并发场景，支持外键，支持事务，索引与数据同一文件

• InnoDB使用的都是聚簇索引
• Myisam使用的都是非聚簇索引

存储引擎的选择

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。

1. InnoDB:是Mysql的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。
2. MyISAM:如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。（日志相关数据、电商中足迹、评论相关数据）
3. MEMORY:将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。（通常用来做缓存）

InnoDB和MyISAM区别

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

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

3. InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

   也就是说：InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。

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

5. Innodb不支持全文索引，而MyISAM支持全文索引，在涉及全文索引领域的查询效率上MyISAM速度更快高；PS：5.7以后的InnoDB支持全文索引了

6. MyISAM表格可以被压缩后进行查询操作

7. InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁

   InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上。潜台词是，如果访问没有命中索引，也无法使用行锁，将要退化为表锁。

8. InnoDB表必须有唯一索引（如主键）（用户没有指定的话会自己找/生产一个隐藏列Row_id来充当默认主键），而Myisam可以没有

9. Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI

InnoDB事务

一致性、持久性：（redo log）

重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。

该日志文件由两部分组成:重做日志缓冲（redo log buffer)以及重做日志文件（(redo log file) ,前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中,用于在刷新脏页到磁盘,发生错误时,进行数据恢复使用。

原子性：（undo log）

回滚日志，用于记录数据被修改前的信息，作用包含两个:提供回滚和MVCC(多版本并发控制)。

undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。

Undo log销毁: undo log在事务执行时产生，事务提交时，并不会立即删除undo log，因为这些日志可能还用于MVCC。

Undo log存储: undo log采用段的方式进行管理和记录，存放在前面介绍的 rollback segment回滚段中，内部包含1024个undo log
segment。

MVCC

当前读

读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如:
select …lock in share mode(共享锁)，select … for update、update、insert、delete(排他锁)都是一种当前读。
快照读
简单的select (不加锁）就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。

Read Committed:每次select，都生成一个快照读。

Repeatable Read:开启事务后第一个select语句才是快照读的地方。

Serializable（串行化）:快照读会退化为当前读。

MVCC

全称Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo log日志、readView。

三个隐式字段：

undo log日志：

回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。

当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除。

而update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会立即被删除。

readView：

不同的隔离级别，生成ReadView的时机不同:

READ COMMITTED:在事务中每一次执行快照读时生成Readview。

REPEATABLE READ:仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView

聚簇索引和非聚簇索引 （聚集索引和二级索引）

​ 聚簇索引： 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据（主键索引）

​ 非聚簇索引： 将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置（辅助索引）

​ 聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。

聚集索引选取规则：

1. 如果存在主键，主键索引就是聚集索引。
2. 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
3. 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

哈希索引

​ 哈希索引用索引列的值计算该值的hashCode，然后在hashCode相应的位置存执该值所在行数据的物理位置，因为使用散列算法，因此访问速度非常快，但是一个值只能对应一个hashCode，而且是散列的分布方式，因此哈希索引不支持范围查找和排序的功能

为什么用B+树索引而不用哈希索引？

哈希索引，建立的是索引值的哈希值和物理磁盘地址之间的映射
（1）哈希冲突多的时候，性能也不一定就比B+树好
（2）哈希索引不支持范围查询，只能点对点查询，哈希运算前的索引值和哈希运算后的哈希值顺序并不一定一样
（3）哈希索引不能利用部分索引键查询，哈希索引在计算哈希值的时候是组合索引键合并后再一起计算哈希值，而不是单独计算哈希值，所以通过组合索引的前面一个或几个索引键进行查询的时候，哈希索引也无法被利用

为什么InnoDB推荐用整型自增主键，而不是uuid？

（1）uuid占用空间更多。uuid是随机字符串，占用空间更多，整型更少。
（2）uuid排序不如整型容易。uuid是字符串，而节点中的索引值需要排序，显然整型排序更容易。
（3）整型自增插入时可避免节点频繁分裂。插入数据时，自增主键对B+树结构影响很小，由于是递增，往后加就行，而uuid是随机的，可能插到中间，如果前面节点已经满了，会导致节点分裂（页分裂）、树结构调整等大量耗费性能的操作。

索引失效场景有哪些

（1）当联合索引不满足最左匹配原则，相当于创建多列索引，没有最左优先，那么联合查询也就失效（如果使用了<或者>右边的索引将会失效改成>=或者<=就正常）

（2）在查询时，使用错误的模糊查询（如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。）

（3）当列使用运算操作和函数时，索引就失效了

（4）列使用了类型转换，也会导致索引失效（例如字符串类型不加引号进行查询）

（5）使用了is not null，那么索引就会失效（不固定取决于当前数据库表中的数据分布如果表中都有数据或者极少数没有数据使用is null走索引 使用is not null不走索引因为根据表中数据的量来决定如果量多就走全局扫描）

（6）or连接：如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

事务

事务4大特性

事务4大特性： 原子性、一致性、隔离性、持久性

​ 原⼦性： 事务是最⼩的执⾏单位，不允许分割。事务的原⼦性确保动作要么全部完成，要么全不执行

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

​ 隔离性： 并发访问数据库时，⼀个⽤户的事务不被其他事务所⼲扰，各并发事务之间数据库是独⽴的；

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

事务靠什么保证：

• 原子性：由undolog日志保证，他记录了需要回滚的日志信息，回滚时撤销已执行的sql

• 一致性：由其他三大特性共同保证，是事务的目的

• 隔离性：由MVCC保证

• 持久性：由redolog日志和内存保证，mysql修改数据时内存和redolog会记录操作，宕机时可恢复

事务隔离级别

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

读已提交： 允许读取并发事务已经提交的数据，可以阻⽌脏读，但是幻读或不可重复读仍有可能发⽣。

可重复读： 同⼀字段的多次读取结果都是⼀致的，除⾮数据是被本身事务⾃⼰所修改，可以阻⽌脏读和不可重复读，会有幻读。

串行化： 最⾼的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执⾏，这样事务之间就完全不可能产⽣⼲扰。

隔离级别	并发问题
读未提交	可能会导致脏读、幻读或不可重复读
读已提交	可能会导致幻读或不可重复读
可重复读	可能会导致幻读
可串行化	不会产⽣⼲扰

隔离级别	脏读	不可重复读	幻读
读取未提交	√	√	√
读取已提交	×	√	√
可重复读	×	×	√
可串行化	×	×	×

1、脏读：脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。
例如：
张三的工资为5000,事务A中把他的工资改为8000,但事务A尚未提交。
与此同时，
事务B正在读取张三的工资，读取到张三的工资为8000。
随后，
事务A发生异常，而回滚了事务。张三的工资又回滚为5000。
最后，
事务B读取到的张三工资为8000的数据即为脏数据，事务B做了一次脏读。

2、不可重复读：是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。
例如：
在事务A中，读取到张三的工资为5000，操作没有完成，事务还没提交。
与此同时，
事务B把张三的工资改为8000，并提交了事务。
随后，
在事务A中，再次读取张三的工资，此时工资变为8000。在一个事务中前后两次读取的结果并不致，导致了不可重复读。

3、幻读：是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。
例如：
两个cmd窗口开启事务 在第一个窗口中进行查询id=3，没有数据，此时在第二个窗口进行插入id=3，在第一个窗口中也进行插入id=3的操作显示已经存在，但是再查询id=3也还是没有数据

默认隔离级别-RR

默认隔离级别： 可重复读；

​ 同⼀字段的多次读取结果都是⼀致的，除⾮数据是被本身事务⾃⼰所修改；

​ 可重复读是有可能出现幻读的，如果要保证绝对的安全只能把隔离级别设置成SERIALIZABLE；这样所有事务都只能顺序执行，自然不会因为并发有什么影响了，但是性能会下降许多。

​ 第二种方式，使用MVCC解决快照读幻读问题（如简单select），读取的不是最新的数据。维护一个字段作为version，这样可以控制到每次只能有一个人更新一个版本。

select id from table_xx where id = ? and version = V
update id from table_xx where id = ? and version = V+1

​ 第三种方式，如果需要读最新的数据，可以通过GapLock+Next-KeyLock可以解决当前读幻读问题，

select id from table_xx where id > 100 for update;
select id from table_xx where id > 100 lock in share mode;

RR和RC使用场景

​ 事务隔离级别RC(read commit)和RR（repeatable read）两种事务隔离级别基于多版本并发控制MVCC(multi-version concurrency control）来实现。

	RC	RR
实现	多条查询语句会创建多个不同的ReadView	仅需要一个版本的ReadView
粒度	语句级读一致性	事务级读一致性
准确性	每次语句执行时间点的数据	第一条语句执行时间点的数据

并发事务带来哪些问题？

脏读： 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是没有被提交的，那么事务读到的这个数据是“脏数据”
丢失修改： 一个事务修改一个数据的时，另外一个事务也读取到这个数据，当第一个事务对他进行修改后，第二个事务也进行了修改，这样第一个事务的修改结果就丢失了，因此被称为丢失修改
不可重复读: 指一个事务内多次读同一个事务，在这个事务还没有结束的时候，另外一个事务也访问该数据。那么第一事务的两次读取数据之间，由于第二个事务的修改导致一个事务内两次读到的数据是不太一样的情况，因此称为不可重复读。
幻读: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻卷一样，所以称为幻读。

应该如何解决？

并发事务可能造成：脏读、不可重复读和幻读等问题 ，这些问题其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决，解决方案如下：

加锁：在读取数据前，对其加锁，阻止其他事务对数据进行修改。例如，读的时候加共享锁，此时其他事物无法修改相应的数据，写的时候加排他锁，禁止其他事物读写操作，但是这种做法性能较差。基于性能考虑MySQL提供了数据多版本并发控制(MVCC)，也称为多版本数据库：不用加任何锁， 通过一定机制生成一个数据请求时间点的一致性数据快照(Snapshot)， 并用这个快照来提供一定级别 (语句级或事务级) 的一致性读取，从用户的角度来看，好象是数据库可以提供同一数据的多个版本。

不可重复读和幻读的区别：

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

事务的两种方式

1. 声明式

在配置文件中设置以下6项

(1)、required

如果客户端没有事务　在bean中新起一个事务

如果客户端有事务bean 中就加进去

(2)、 requiresNew

不管客户端有没有事务服务器段都新起一个事务

如果客户端有事务就将事务挂起

(3)、supports

如果客户端没有事务服务端也没有事务

如果客户端有事务服务端就加一个事务

(4)、mandatcry

如果客户端没有事务服务端就会报错

如果客户端有事务服务端就加事务

(5)、notSupported

不管客户端有没有事务服务端都没有事务

如果客户端有事务服务端就挂起

(6)、never

不管客户端有没有事务服务端都没有事务

如果客户端有事务就报错

2. 编程式事务

Javax.transaction.UserTranscation

JTA 事务可以精确到事务的开始和结束

InnoDB 如何开启手动提交事务？

InnoDB 默认是自动提交事务的，每一次 SQL 操作(非 select 操作)都会自动提交一个事务，如果要手动开启事务需要设置set autocommit=0禁止自动提交事务，相当于开启手动提交事务。

在 InnoDB 中设置了 autocommit=0，添加一条信息之后没有手动执行提交操作，请问这条信息可以被查到吗？

autocommit=0 表示禁止自动事务提交，在添加操作之后没有进行手动提交，默认情况下其他连接客户端是查询不到此条新增数据的。

mysql优化

1.索引优化

索引是加速数据库查询的关键。在设计表结构时，应该根据查询的需求添加合适的索引。常用的索引包括主键、唯一索引、普通索引、联合索引、前缀索引（vachar、text这种长的数据并且只需要前几个区分度就很高）等。

同时，要避免过多的索引，因为每个索引都需要占用存储空间，会影响写入性能。

2.查询优化

优化查询语句是提高MySQL性能的重要手段。要尽可能使用索引，避免全表扫描。同时，要避免使用子查询，尽可能使用连接查询；避免在查询中使用“%”通配符；避免多余的字段等等。

3.数据库表结构优化

合理的表结构可以提高查询效率和减少存储空间。应该避免使用大字段，如TEXT、BLOB等，因为这些字段会占用大量的存储空间。同时，应该避免冗余字段，避免更新和维护时的复杂性。

​ ①单库不超过200张表

​ ②单表不超过500w数据

​ ③单表不超过40列

​ ④单表索引不超过5个

4.缓存优化

使用缓存可以大大减轻MySQL数据库的压力，提高查询效率。常用的缓存技术包括Memcached和Redis等。

5.分区优化

对于数据量较大的表，可以使用分区技术将表分成多个部分。这样可以提高查询效率，同时降低了单个表的存储空间和索引大小。

6.配置优化

MySQL的参数配置会影响MySQL的性能。需要根据实际情况进行调整，包括缓冲区、连接数、线程数、查询缓存等等。

7.硬件优化

硬件设备也会影响MySQL的性能。要选择更快速的硬件设备，如更快的磁盘、更快的CPU和更多的内存等等。同时，要根据实际情况来决定使用RAID、SSD等技术。

DQL语法

逻辑存储结构

1. 插入数据

#客户端连接服务端时，加上参数--local-infile

mysql --local-infile -u root-p

#设置全局参数local_infile为1，开启从本地加载文件导入数据的开关

set global local_infile = 1;

#执行load指令将准备好的数据，加载到表结构中

load data local infile '/root/sql1.loginto table tb_user fields terminated by ',' lines terminated by '\n';

主键顺序插入性能高于乱序插入

2. 主键优化

满足业务需求的情况下，尽量降低主键的长度。

插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。

尽量不要使用UUID做主键或者是其他自然主键，如身份证号。

业务操作时，避免对主键的修改。

3. order by优化

1. Using filesort 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSot排序。
2. Using index :通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。

#没有创建索引时，根据age, phone进行排序
explain select id,age,phone from tb_user order by age , phone;
#创建索引
create index idx_user_age_phone_aa on tb_user(age,phone);
#创建索引后，根据age, phone进行升序排序
explain select id,age,phone from tb_user order by age , phone;
#创建索引后，根据age, phone进行降序排序
explain select id,age,phone from tb_user order by age desc , phone desc ;
#根据age, phone进行降序一个升序，一个降序
explain select id,age,phone from tb_user order by age asc , phone desc;
#创建索引
create index idx_user_age _phone_ad on tb_user(age asc ,phone desc);
#根据age, phone进行降序一个升序，一个降序
explain select id,age,phone from tb_user order by age asc , phone desc;

根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。

尽量使用覆盖索引（查询的字段在联合索引中可以直接查询到不需要进行回表查询）。

多字段排序,一个升序一个降序，此时需要注意联合索引在创建时的规则(ASC/DESC)。

如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size(默认256k)。

4. group by优化

在分组操作时，可以通过索引来提高效率。

分组操作时，索引的使用也是满足最左前缀法则的。

#执行分组操作，根据profession字段分组
explain select profession , count(*) from tb_user group by profession;
#创建索引
Create index idx_user_pro_age_sta on tb_user(profession , age , status);
#执行分组操作，根据profession字段分组
explain select profession , count(*) from tb_user group by profession;
#执行分组操作，根据profession字段分组
explain select profession , count(*) from tb_user group by profession,age;

5. limit 优化

一个常见又非常头疼的问题就是limit 2000000,10，此时需要MySQL排序前2000010记录，仅仅返回2000000-2000010的记录，其他记录丢弃，查询排序的代价非常大。

优化思路:一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

explain select * from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a where t.id = aid;

6. count 优化

count的几种用法

count(主键): InnoDB引擎会遍历整张表，把每一行的主键id值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加(主键不可能为null)。

count(字段): 没有not null约束: InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。

​ 有not null约束: InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。

count ( 1): InnoDB引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。

count (*): InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累

按照效率排序的话，count(字段)此count(主键id)< count(1)≈count(*)，所以尽量使用count()

MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高;

InnoDB引擎就麻烦了，它执行count(*)的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

7. update优化

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁,并且该索引不能失效，否则会从行锁升级为表锁。

如果update过程中where条件是没有索引的将会从行锁升级为表锁。

如果where条件是有索引的就会是正常的行锁。

集群

1、主从复制过程

MySQl主从复制：

• 原理：将主服务器的binlog日志复制到从服务器上执行一遍，达到主从数据的一致状态。
• 过程：从库开启一个I/O线程，向主库请求Binlog日志。主节点开启一个binlog dump线程，检查自己的二进制日志，并发送给从节点；从库将接收到的数据保存到中继日志（Relay log）中，另外开启一个SQL线程，把Relay中的操作在自身机器上执行一遍
• 优点：
  • 作为备用数据库，并且不影响业务
  • 可做读写分离，一个写库，一个或多个读库，在不同的服务器上，充分发挥服务器和数据库的性能，但要保证数据的一致性

binlog记录格式： statement、row、mixed

​ 基于语句statement的复制、基于行row的复制、基于语句和行（mix）的复制。其中基于row的复制方式更能保证主从库数据的一致性，但日志量较大，在设置时考虑磁盘的空间问题

2、数据一致性问题

“主从复制有延时”，这个延时期间读取从库，可能读到不一致的数据。

缓存记录写key法：

​ 在cache里记录哪些记录发生过的写请求，来路由读主库还是读从库

异步复制：

​ 在异步复制中，主库执行完操作后，写入binlog日志后，就返回客户端，这一动作就结束了，并不会验证从库有没有收到，完不完整，所以这样可能会造成数据的不一致。

半同步复制：

​ 当主库每提交一个事务后，不会立即返回，而是等待其中一个从库接收到Binlog并成功写入Relay-log中才返回客户端，通过一份在主库的Binlog，另一份在其中一个从库的Relay-log，可以保证了数据的安全性和一致性。

全同步复制：

​ 指当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响。

3、集群架构

Keepalived + VIP + MySQL 主从/双主

​ 当写节点 Master db1 出现故障时，由 MMM Monitor 或 Keepalived 触发切换脚本，将 VIP 漂移到可用的 Master db2 上。当出现网络抖动或网络分区时，MMM Monitor 会误判，严重时来回切换写 VIP 导致集群双写，当数据复制延迟时，应用程序会出现数据错乱或数据冲突的故障。有效避免单点失效的架构就是采用共享存储，单点故障切换可以通过分布式哨兵系统监控。

**架构选型：**MMM 集群 -> MHA集群 -> MHA+Arksentinel。

4、故障转移和恢复

转移方式及恢复方法

1. 虚拟IP或DNS服务 （Keepalived +VIP/DNS  和 MMM 架构）

​ 问题：在虚拟 IP 运维过程中，刷新ARP过程中有时会出现一个 VIP 绑定在多台服务器同时提供连接的问题。这也是为什么要避免使用 Keepalived+VIP 和 MMM 架构的原因之一，因为它处理不了这类问题而导致集群多点写入。

2. 提升备库为主库（MHA、QMHA）

​ 尝试将原 Master 设置 read_only 为 on，避免集群多点写入。借助 binlog server 保留 Master 的 Binlog；当出现数据延迟时，再提升 Slave 为新 Master 之前需要进行数据补齐，否则会丢失数据。

锁

分类：

MySQL中的锁，按照锁的粒度分，分为以下三类:

1. 全局锁:锁定数据库中的所有表。

2. 表级锁:每次操作锁住整张表。

3. 行级锁:每次操作锁住对应的行数据。

全局锁：

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。

其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

表级锁：

表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中。

对于表级锁，主要分为以下三类:

1. 表锁

   对于表锁，分为两类:

   1.表共享读锁( read lock)：读锁不会阻塞其他客户端的读，但是会阻塞写

   2.表独占写锁（write lock )：写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写

   语法:

   1. 加锁:lock tables表名… read/write。
   2. 释放锁: unlock tables /客户端断开连接。

2. 元数据锁( meta data lock,MDL)

   MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性。

   在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁(共享);当对表结构进行变更操作的时候，加MDL写锁(排他)。

3. 意向锁

1．意向共享锁（lS）:与表锁共享锁(read）兼容，与表锁排它锁（write)互斥。

2．意向排他锁（IX)∶与表锁共享锁( read)及排它锁(write)都互斥。意向锁之间不会互斥。

行级锁：

行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在lnnoDB存储引擎中。

InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类:

1. 行锁(Record Lock)∶锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。

   lnnoDB实现了以下两种类型的行锁:
   1．共享锁(S）∶允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。

   2．排他锁（X)∶允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。

   默认情况下，InnoDB在REPEATABLE READ事务隔离级别运行，InnoDB使用next-key锁进行搜索和索引扫描，以防止幻读。

   1. 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。

   2. InnoDB的行锁是针对于索引加的锁，不通过g引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时就会升级为表锁。

2. 间隙锁(Gap Lock):锁定索引记录间隙（(不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。

3. 临键锁(Next-Key Lock)∶行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。

   默认情况下，InnoDB在REPEATABLE READ事务隔离级别运行，InnoDB使用next-key锁进行搜索和索引扫描，以防止幻读。

   1．索引上的等值查询(唯一索引)，给不存在的记录加锁时,优化为间隙锁。

   2． 索引上的等值查询(普通索引)，向右遍历时最后一个值不满足查询需求时，next-key lock退化为间隙锁。

   3．索引上的范围查询(唯一索引)–会访问到不满足条件的第一个值为止。

其他问题

MySQL有哪些锁

基于粒度：

• 表级锁：对整张表加锁，粒度大并发小

• 行级锁：对行加锁，粒度小并发大

• 间隙锁：间隙锁，锁住表的一个区间，间隙锁之间不会冲突只在可重复读下才生效，解决了幻读

基于属性：

• 共享锁：又称读锁，一个事务为表加了读锁，其它事务只能加读锁，不能加写锁

• 排他锁：又称写锁，一个事务加写锁之后，其他事务不能再加任何锁，避免脏读问题

$和#

#占位符的特点

1. MyBatis处理 #{ } 占位符，使用的 JDBC 对象是PreparedStatement 对象，执行sql语句的效率更高。
2. 使用PreparedStatement 对象，能够避免 sql 注入，使得sql语句的执行更加安全。
3. #{ } 常常作为列值使用，位于sql语句中等号的右侧；#{ } 位置的值与数据类型是相关的。

$占位符的特点

1. MyBatis处理 ${ } 占位符，使用的 JDBC 对象是 Statement 对象，执行sql语句的效率相对于 #{ } 占位符要更低。
2. ${ } 占位符的值，使用的是字符串连接的方式，有 sql 注入的风险，同时也存在代码安全的问题。
3. ${ } 占位符中的数据是原模原样的，不会区分数据类型。
4. ${ } 占位符常用作表名或列名，这里推荐在能保证数据安全的情况下使用 ${ }。

数据库三范式具体是什么

第—范式:每个列都不可以再拆分。

第二范式:在第一范式的基础上,非主键列完全依赖于主键,而不能是依赖于主键的一部分。

第三范式:在第二范式的基础上,非主键列只依赖于主键,不依赖于其他非主键。

Mysql内连接、左连接、右连接的区别

内连接取量表交集部分，左连接取左表全部右表匹部分，右连接取右表全部坐表匹部分

where和having的区别？

where是约束声明，having是过滤声明，where早于having执行，并且where不可以使用聚合函数，having可以