Mysql面试

Mysql

1. 事务四大特性（ACID）

原子性：

• 原子性是指事务包含的操作要么全部成功，要么全部失败回滚。

一致性：

• 事务开始前和结束后，数据库的完整性约束没有被破坏。比如A向B转账，不可能A扣了钱，B没收到。

隔离性：

• 隔离性是当多个用户并发的访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，事务不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程当中，B不能向这张银行卡上转账。

持久性：

• 事务对数据库的操作是永久性的。持久性的保证是通过原子性和隔离性来保证的。

1.1 如何保证原子性能：

要么全部完成，要么全部取消，当对数据库进行操作时，根据业务逻辑，我们会操作个表中的数据，当一步操作出错时，会让该事务中的所有操作都回滚，数据还是未操作时的样子。

begin; -- 开始一个事务
update table set A = A - 100; 
update table set B = B + 100;
-- 其他读写操作
commit; -- 提交事务

在begin和commit之间的操作都完成后，才将结果提交给数据库保存，只要有一条失败，就撤销之前的操作，这要就保证了同生共死.

1.2 如何保证隔离性（Isolation）：

有多个事务同时进行，结束时，最终的结果应该是相同的
原子性的问题解决了，但是在另外的事务中同时修改数据，数据的一致性就会被破坏，所以要有隔离机智，一个事务完成后，另一个事务才能开始，要加上互斥锁。
具体步骤：
1.先获得锁，然后才能修改数据A
2.修改并提交事务之后释放锁，给下一个要修改数据A的事务
3.然后第二个事务开始修改数据并提交。
对于同一个数据，只有一个事务能持有互斥锁，没有锁的事务，需要等待其他事务释放锁
只有当事务提交或者回滚后，才能释放锁。在这期间，其他事务只能读取数据。

2.事务的并发？事务隔离级别，每个级别会引发什么问题，MySQL默认是哪个级别？

在实际开发中，为了提升性能，事务会以较低的隔离级别运行，事务的隔离级别可以通过隔离事务属性来指定。

事务的并发问题

1. 脏读: 事务A读取B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
2. 不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果因此本事务先后两次读到的数据结果会不一致。
3. 幻读：幻读解决了不重复读，保证了同一个事务里，查询的结果都是事务开始时的状态（一致性）。

例如：事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作 这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。 而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有跟没有修改一样，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。

**小结：**不可重复读和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或者删除，解决不可重复读的问题只需要锁住满足条件的行，解决幻读需要锁表。

事务的隔离级别

​

事务隔离级别	脏读	不可重复读	幻读
读未提交 read-uncommitted	是	是	是
不可重复读 read-committed	否	是	是
可重复读 repeatable-read	否	否	是
串行化 serializable	否	否	否

• 读未提交：另一个事务修改了数据，但尚未提交，而本事务中的SELECT会读到这些未被提交的数据脏读
• 不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果因此本事务先后两次读到的数据结果会不一致。
• 可重复读：在同一个事务里，SELECT的结果是事务开始时时间点的状态，因此，同样的SELECT操作读到的结果会是一致的。但是，会有幻读现象
• 串行化：最高的隔离级别，在这个隔离级别下，不会产生任何异常。并发的事务，就像事务是在一个个按照顺序执行一样

MySQL默认的事务隔离级别为repeatable-read（可重复读）

• MySQL支持四种事务隔离级别
• 事务的隔离级别要得到底层数据库引擎的支持, 而不是应用程序或者框架的支持.
• Oracle 支持的 2 种事务隔离级别：READ_COMMITED , SERIALIZABLE

事务与锁

• 事务隔离级别为： 读未提交时，写数据只会锁住相应的行。
• 事务隔离级别为：可重复读时，写数据会锁住整张表。
• 事务隔离级别为：串行化时，读写数据都会锁住整张表。

间隙锁：

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。作用，防止幻读，满足隔离级别。

在mysql的innoDB存储引擎中，如果更新操作是针对一个区间的，那么它会锁住这个区间内所有的记录，比如update xxx where id between a and b那么它会锁住a到b之间所有记录，注意是所有记录，甚至这个记录并不存在也会被锁住，这个时候，如果另外一个连接需要插入一条记录到a到b之间，那么它就必须等到上一个事务结束。

隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大，鱼和熊掌不可兼得啊。对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed，它能够避免脏读取，而且具有较好的并发性能。尽管它会导致不可重复读、幻读这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。

3. MySQL常用的两种存储引擎

虽然MySQL里的存储引擎不只是MyISAM与InnoDB这两个，但常用的就是两个。

两种存储引擎的大致区别表现在：

• InnoDB支持事务，MyISAM不支持，这一点是非常之重要。事务是一种高级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原，而MyISAM就不可以了。
• InnoDB支持外键，MyISAM不支持。
• MyISAM适合查询以及插入为主的应用。
• InnoDB适合频繁修改以及涉及到安全性较高的应用。
• 从MySQL5.5.5以后，InnoDB是默认引擎。
• InnoDB中不保存表的行数，如select count(*) from table时，InnoDB需要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count(*)语句包含where条件时MyISAM也需要扫描整个表。
• 对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立联合索引。
• DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的 删除，效率非常慢。MyISAM则会重建表。
• InnoDB支持行锁（某些情况下还是锁整表，如 update table set a=1 where user like '%lee%'。

关于MySQL数据库提供的两种存储引擎，MyISAM与InnoDB选择使用：

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

• InnoDB支持事务，而MyISAM不支持事务

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

• 如果你的应用程序对查询性能要求较高，就要使用MyISAM了。MyISAM索引和数据是分开的，而且其索引是压缩的，可以更好地利用内存。所以它的查询性能明显优于INNODB。压缩后的索引也能节约一些磁盘空间。MyISAM拥有全文索引的功能，这可以极大地优化LIKE查询的效率。

• InnoDB引擎，默认的是B+树。

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

• MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

• 针对Innodb,Myisam存储引擎，索引和实际数据都是存储在磁盘的，只不过在进行数据读取的时候会优先把索引加到内存中。

MySQL的MyISAM与InnoDB两种存储引擎在，事务、锁级别，各自的适用场景？

事务处理上方面

• MyISAM：强调的是性能，每次查询具有原子性,其执行数度比InnoDB类型更快，但是不提供事务支持。
• InnoDB：提供事务支持事务，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。

锁级别

• MyISAM：只支持表级锁，用户在操作MyISAM表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。
• InnoDB：支持事务和行级锁，是innodb的最大特色。行锁大幅度提高了多用户并发操作的性能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。

4. 查询语句不同元素（where、jion、limit、group by、having等等）执行先后顺序？

1. 查询中用到的关键词主要包含六个，并且他们的顺序依次为 select--from--where--group by--having--order by

   其中select和from是必须的，其他关键词是可选的，这六个关键词的执行顺序与sql语句的书写顺序并不是一样的，而是按照下面的顺序来执行

   • from:需要从哪个数据表检索数据
   • where:过滤表中数据的条件
   • group by:如何将上面过滤出的数据分组
   • having:对上面已经分组的数据进行过滤的条件
   • select:查看结果集中的哪个列，或列的计算结果
   • order by :按照什么样的顺序来查看返回的数据

   1. where 与having的区别？

      where 后面要跟的是数据表里的存在的字段，如果我把ag换成avg(price)也是错误的！因为表里没有该字段。
      而having只是根据前面查询出来的是什么就可以后面接什么,无查询，没办法使用。

2. from后面的表关联，是自右向左解析 而where条件的解析顺序是自下而上的。

也就是说，在写SQL文的时候，尽量把数据量小的表放在最右边来进行关联（用小表去匹配大表），而把能筛选出小量数据的条件放在where语句的最左边 （用小表去匹配大表）。

5. MySQL B+Tree索引和Hash索引的区别？

• Hash索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位;
• B+树索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问;

Hash索引

对于hash索引来说，底层的数据结构就是Hash表，因此绝大多数需求为单调记录的时候，可以选择hash索引，查询速度快，其余大部分场景，建议使用B+Tree索引。

• Hash索引无法被用来避免数据的排序操作，因为Hash值的大小关系并不一定和Hash运算前的键值完全一样；
• Hash索引不能利用部分索引键查询，对于组合索引，Hash索引在计算Hash值的时候是组合索引键合并后再一起计算Hash值，而不是单独计算Hash值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash索引也无法被利用；
• Hash索引在任何时候都不能避免表扫描，由于不同索引键存在相同Hash值，所以即使取满足某个Hash键值的数据的记录条数，也无法从Hash索引中直接完成查询，还是要回表查询数据；
• Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B+树索引高。

B+Tree索引

常用的InnoDB引擎中默认使用的是B+树索引，它会实时监控表上索引的使用情况，如果认为建立哈希索引可以提高查询效率，则自动在内存中的“自适应哈希索引缓冲区”建立哈希索引（在InnoDB中默认开启自适应哈希索引），通过观察搜索模式，MySQL会利用index key的前缀建立哈希索引，如果一个表几乎大部分都在缓冲池中，那么建立一个哈希索引能够加快等值查询。

B+树索引和哈希索引的明显区别是：

如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值；当然了，这个前提是，键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据；

如果是范围查询检索，这时候哈希索引就毫无用武之地了，因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索；

同理，哈希索引没办法利用索引完成排序，以及like ‘xxx%’ 这样的部分模糊查询（这种部分模糊查询，其实本质上也是范围查询）；

哈希索引也不支持多列联合索引的最左匹配规则；

B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题。

在大多数场景下，都会有范围查询、排序、分组等查询特征，用B+树索引就可以了。

B+ Tree的叶子节点都可以存哪些东西吗？

InnoDB的B+ Tree可能存储的是整行数据，也有可能是主键的值。

那这两者有什么区别吗？

在 InnoDB 里，索引B+ Tree的叶子节点存储了整行数据的是主键索引，也被称之为聚簇索引。而索引B+ Tree的叶子节点存储了主键的值的是非主键索引（辅助索引），也被称之为非聚簇索引。

6.sql查询语句确定创建哪种类型的索引，如何优化查询

• 性能优化过程中，选择在哪个列上创建索引是最重要的步骤之一，可以考虑使用索引的主要有两种类型的列：在where子句中出现的列，在join子句中出现的列。
• 使用短索引，如果对字符串列进行索引，应该指定一个前缀长度，可节省大量索引空间，提升查询速度。
• 利用最左前缀,顾名思义，就是最左优先，在多列索引，有体现:(ALTER TABLE people ADD INDEX lname_fname_age (lame,fname,age)😉，所谓最左前缀原则就是先要看第一列，在第一列满足的条件下再看左边第二列，以此类推
• 不要过度建索引，只保持所需的索引。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。
• 在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。

7. 聚集索引和非聚集索引区别？

聚合索引(clustered index) / 非聚合索引(nonclustered index)

区别

数据跟索引存储在一起的叫做聚簇索引，没有存储在一起的叫非聚簇索引。

聚集索引

聚集索引表记录的排列顺序和索引的排列顺序一致，所以查询效率快，只要找到第一个索引值记录，其余就连续性的记录在物理也一样连续存放。聚集索引对应的缺点就是修改慢，因为为了保证表中记录的物理和索引顺序一致，在记录插入的时候，会对数据页重新排序。

聚集索引类似于新华字典中用拼音去查找汉字，拼音检索表于书记顺序都是按照a~z排列的，就像相同的逻辑顺序于物理顺序一样，当你需要查找a,ai两个读音的字，或是想一次寻找多个傻(sha)的同音字时，也许向后翻几页，或紧接着下一行就得到结果了。

innodb存储引擎在进行数据插入的时候，数据必须要跟某一个索引列存储在一起，这个索引列可以是主键，如果没有主键选择唯一键，如果没有唯一键，选择6字节的rowid来进行存储。数据必定是跟某一个索引绑定在一起的，绑定数据的索引叫做聚簇索引，其他索引的叶子节点中存储的数据不再是整行记录，而是聚簇索引的ID值。

innodb中既有聚簇索引也有非聚簇索引。

myisam中只有非聚簇索引。

非聚集索引

非聚集索引制定了表中记录的逻辑顺序，但是记录的物理和索引不一定一致，两种索引都采用B+树结构，非聚集索引的叶子层并不和实际数据页相重叠，而采用叶子层包含一个指向表中的记录在数据页中的指针方式。非聚集索引层次多，不会造成数据重排。

非聚集索引类似在新华字典上通过偏旁部首来查询汉字，检索表也许是按照横、竖、撇来排列的，但是由于正文中是a~z的拼音顺序，所以就类似于逻辑地址于物理地址的不对应。同时适用的情况就在于分组，大数目的不同值，频繁更新的列中，这些情况即不适合聚集索引。

8.数据库三范式

什么是设计范式？

设计数据库表的依据，按照这个三范式设计的表不会出现数据冗余（关键点）；

第一范式：

任何一张表都应该有主键，并且每一个字段原子性不可再分。

第二范式：

建立在第一范式的基础上，所有非主键字段应该完全依赖于主键，不能产生部分依赖。

第三范式：

建立在第二范式的基础上，所有非主键字段直接依赖于主键，不能产生依赖传递。

9.使用explain优化sql和索引？

对于复杂、效率低的sql语句，我们通常是使用explain sql 来分析sql语句，这个语句可以打印出，语句的执行。这样方便我们分析，进行优化，MySQL提供的Explain，用于显示SQL执行的详细信息，可以进行索引的优化。

比较重要的参数有三个： type,key,Extra

• table：显示这一行的数据是关于哪张表的
• type：查询对应的类型（该列非常重要），显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL，越往下效率越低，我们平时最起码达到range级别.
  • all: full table scan ;MySQL将遍历全表以找到匹配的行；
  • index ： index scan; index 和 all的区别在于index类型只遍历索引；
  • range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值的行，常见与between ，< ,>等查询；
  • ref：非唯一性索引扫描，返回匹配某个单独值的所有行，常见于使用非唯一索引即唯一索引的非唯一前缀进行查找；
  • eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常用于主键或者唯一索引扫描；
  • const，system：当MySQL对某查询某部分进行优化，并转为一个常量时，使用这些访问类型。如果将主键置于where列表中，MySQL就能将该查询转化为一个常量。
• possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
• key： 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MySQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MySQL忽略索引
• key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好
• ref：显示索引的哪一列被使用了，如果可能的话，是一个常数
• rows：MySQL认为必须检查的用来返回请求数据的行数
• Extra：关于MySQL如何解析查询的额外信息。但这里可以看到的坏的例子是Using temporary和Using filesort，意思MySQL根本不能使用索引，结果是检索会很慢。

10. MySQL慢查询怎么解决？

• slow_query_log 慢查询开启状态。
• slow_query_log_file 慢查询日志存放的位置（这个目录需要MySQL的运行帐号的可写权限，一般设置为MySQL的数据存放目录）。
• long_query_time 查询超过多少秒才记录。

10.1SQL语句执行慢的原因

1. 硬件问题。如网络速度慢，内存不足，I/O吞吐量小，磁盘空间满了等。

2. 没有索引或者索引失效。（一般在互联网公司，DBA会在半夜把表锁了，重新建立一遍索引，因为当你删除某个数据的时候，索引的树结构就不完整了。所以互联网公司的数据做的是假删除.一是为了做数据分析,二是为了不破坏索引 ）

3. 数据过多（分库分表）

4. 服务器调优及各个参数设置（调整my.cnf）

10.2 分析原因时，一定要找切入点

1.先观察，开启慢查询日志，设置相应的阈值（比如超过3秒就是慢SQL），在生产环境跑上个一天过后，看看哪些SQL比较慢。

2.Explain和慢SQL分析。比如SQL语句写的烂，索引没有或失效，关联查询太多（有时候是设计缺陷或者不得以的需求）等等。

3.Show Profile是比Explain更近一步的执行细节，可以查询到执行每一个SQL都干了什么事，这些事分别花了多少秒。

4.找DBA或者运维对MySQL进行服务器的参数调优。

11. MySQL都有什么锁，死锁判定原理和具体场景，死锁怎么解决？

MySQL都有什么锁

MySQL有三种锁的级别：页级、表级、行级。

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
• 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
• 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

什么情况下会造成死锁

• 所谓死锁: 是指两个或两个以上的进程在执行过程中。
• 因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去。
• 此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等竺的进程称为死锁进程。
• 表级锁不会产生死锁.所以解决死锁主要还是针对于最常用的InnoDB。

死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。

那么对应的解决死锁问题的关键就是：让不同的session加锁有次序。

死锁的解决办法

• 查出的线程杀死 kill

SELECT trx_MySQL_thread_id FROM information_schema.INNODB_TRX;
复制代码

• 设置锁的超时时间

Innodb 行锁的等待时间，单位秒。可在会话级别设置，RDS 实例该参数的默认值为 50（秒）。

生产环境不推荐使用过大的 innodb_lock_wait_timeout参数值

该参数支持在会话级别修改，方便应用在会话级别单独设置某些特殊操作的行锁等待超时时间，如下：

set innodb_lock_wait_timeout=1000; —设置当前会话 Innodb 行锁等待超时时间，单位秒。
复制代码

12.MySQL 高并发环境解决方案？

MySQL 高并发环境解决方案 分库 分表 分布式 增加二级缓存。

需求分析：互联网单位 每天大量数据读取，写入，并发性高。

• 现有解决方式：水平分库分表，由单点分布到多点数据库中，从而降低单点数据库压力。
• 集群方案：解决DB宕机带来的单点DB不能访问问题。
• 读写分离策略：极大限度提高了应用中Read数据的速度和并发量。无法解决高写入压力。

13.建立索引

索引

索引是一种数据结构，能够帮助我们快速的检索数据库中的数据。数据库的索引类似大学图书馆书目索引，可以提高数据检索的效率，降低数据库的IO成本。

索引数据结构的演化

1. 如果一个表中没有添加索引，那么查询的时候，只能按照条件一行一行查询。为了提高查询效率，采用了各种方式：
2. 对数据进行Hash（散列）运算，然后将哈希结果作为文件指针，再到数据文件中获取到数据。底层数据结构是Hash表，对单条数据查询比较友好，但是无法解决范围查询的问题。
3. 二叉树是一种比顺序结构更加高效地查找目标元素的结构，但是由于索引大部分都是递增的，导致大部分情况二叉树只有右子树，效率跟不加索引效果相同。
4. 红黑树继承了二叉树的优点，而且解决了上面二叉树遇到的自增整形索引的问题，如果我们有很多数据，那么树的深度依然会很大，加大读取次数，对我们的磁盘寻址不利，依然会花费很多时间查找（这是因为每一层都是一次IO）。
5. BTree的结构可以弥补红黑树的缺点，解决数据量过大时整棵树的深度过长的问题。相同数量的数据只需要更少的层，相同深度的树可以存储更多的数据，查找的效率自然会更高。但如果范围查的话，BTree结构每次都要从根节点查询一遍，效率会有所降低，因此在实际应用中采用的是另一种BTree的变种B+Tree（B+树）。
6. B+树结构没有在所有的节点里存储记录数据，而是只在最下层的叶子节点存储，上层的所有非叶子节点只存放索引信息，这样的结构可以让单个节点存放下更多索引值，提高命中目标记录的几率。这种结构会在上层非叶子节点存储一部分冗余数据，但是这样的缺点都是可以容忍的，因为冗余的都是索引数据，不会对内存造成大的负担，3到4层的数据能够支持千万级别的数据存储。
7. 联合索引：单列索引其实也可以看做联合索引，索引列为1的联合索引，从下图就可以看出联合索引的底层存储跟单列索引时类似的，区别在于联合索引是每个树节点中包含多个索引值，在通过索引查找记录时，会先将联合索引中第一个索引列与节点中第一个索引值进行匹配，匹配成功接着匹配第二个索引列和索引值，直到联合索引的所有索引列都匹配完；如果过程中出现某一个索引列与节点相应位置的索引值不匹配的情况，则无需再匹配节点中剩余索引列，前往下一个节点。

索引优点：

索引是一种数据结构，例如B-Tree，这种数据结构是需要额外的写入和存储为代价来提高表上数据检索的速度。一旦建立了索引后，数据库中查询优化器使用索引来快速定位数据，然后就无需扫描表中给定查询的每一行了。其中，当使用主键或唯一键创建表时，MySQL会自动创建名为PRIMARY的特殊索引, 该索引称为聚簇索引。PRIMARY索引是比较特殊的，这个索引本身与数据一起存储在同一个表中。另外除PRIMARY索引之外的其他索引称为二级索引或非聚簇索引。

索引缺点：

索引的优点显而易见是可以加速查询，但创建索引也是有代价的。首先每建立一个索引都要为它建立一棵B+树，会占用额外的存储空间；其次当对表中的数据进行增加、删除、修改时，索引也需要动态的维护，降低了数据的维护速度。所以我们创建索引时还是需要根据业务来考虑的，一个表中建议不要加过多索引。

创建索引

1. 当使用主键或唯一键创建表时，MySQL会自动创建名为PRIMARY的特殊索引, 该索引称为聚簇索引。

2. 也可以建表后使用 alter table 或 create index 语句创建索引。

   Alter table citydemo(表名)add key(city(字段));

索引的选择

查询快，占用空间更小

1. 适合索引的列处在where子句中的列，或者连接子句中指定的列。
2. 基数较小的表，索引效果差，没有必要在此列中建立索引。
3. 定义有外键的数据一定要建立索引。
4. 使用短索引，如果对长字符串列进行索引，应该指定一个前缀的长度，这样能够节省大量索引空间。如果索引超过了最大长度，那么我们可以排除一部分，然后检查其余行是否匹配。
5. 更新频繁的字段不适合建立索引，图片，性别，不经常使用的字段不建议创建索引。

索引失效

1. 有or必全有索引;要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
2. 复合索引未用左列字段;
3. like以%开头;
4. 需要类型转换;
5. where中索引列有运算;
6. where中索引列使用了函数;
7. 如果mysql觉得全表扫描更快时（数据少）;

数据库中常见四种索引类型

index：普通索引，数据可以重复。

Fulltext:全文索引，用来对大表的文本域（char,varchar,text）进行索引，语法和普通索引一样。

Unique: 唯一索引，唯一索引要求所有记录都唯一。

Primary key：主键索引，也就是在唯一索引的基础上，相应的列必须为主键。

在InnoDB中，索引类型分为主键索引和非主键索引，主键索引也为称为聚簇索引，叶子节点存放的是整行数据；而非主键索引被称为二级索引，叶子节点存放的主键的值。

14.如何提升数据库性能

1. 读写分离，提高数据库的性能，读写分离主要目的是提高系统吞吐量。某些网站同一时间有大量的读操作和较少的写操作。同时，读操作对数据的实时性要求并没有那么高。在此前提下，可以这么设计解决方案。

15.常用SQL命令

15.1delete 和truncate用法区别

数据库怎么删除整个表，delete和truncate用法区别：

它们的区别在于:

• truncate删除表的同时会清空并重新设置自增列，而delete则不会
• truncate不会影响事务

15.2 创建数据库表

create table sys_notice(id int(4),
                        title varchar(150),
                        type char(3),
                        status char(3),
                        time datetime);

15.3插入数据表

insert into `sys_notice`(`id`,`type`,`status`) values(1,2,0);

15.4查找元素

select type from sys_notice where id =1;

15.5 更新元素

update sys_notice set type = `3` where id =1;

15.6 删除数据

delete from sys_notice where id =1;

15.7 内连接

select sys_notice.id from sys_notice inner join sys on sys_notice = sys.id;

16.回表，索引覆盖，最左匹配，索引下推

16.1回表

#ID,name,age,gender
#ID主键，name普通索引
select * from table where name ='zhangsan';

先根据name B+树匹配到对应的叶子节点，查询到对应行记录的ID值，再根据ID去ID的B+树中检索整行记录，这个过程就称之为回表，回表速度比较慢，要尽量避免回表操作。

16.2索引覆盖

#ID,name,age,gender
#ID主键，name普通索引
select ID,name from table where name ='zhangsan';

根据name的值去name B+树检索对应的记录，能获取到ID的属性值，索引的叶子节点中包含了查询的所有列，此时不需要回表，这个过程叫做索引覆盖，using index的提示信息。推荐使用覆盖索引，在某些场景中，可以考虑将要查询的所有列都变成组合索引，此时会使用索引覆盖，加快查询效率。

16.3最左匹配

创建索引的时候可以选择多个列来共同组成索引，此时叫做组合索引或者联合索引，要遵循最左匹配原则：

#ID,name,age,gender
#ID主键，name,age组合索引
select ID,name from table where name ='zhangsan' and age=12;#走组合索引
select ID,name from table where name ='zhangsan';#走组合索引
select ID,name from table where age=12;#不会走组合索引
select ID,name from table where name ='zhangsan' and age=12;#会走组合索引，这是因为mysql中有优化器，自动把name和age的顺序调换。

16.4索引下推

#ID,name,age,gender
#ID主键，name,age组合索引
select * from table where name ="zhangsan" and age =12;

没有索引下推之前：

先根据name从存储引擎中拉取数据到server层，然后server层中对age进行数据过滤。

有了索引下推之后：

根据name和age两个条件来做数据筛选，将筛选之后的结果返回给server层。

17.如何回答面试中问到的优化问题？

1. 加索引
2. 看执行计划
3. 优化sql语句
4. 分库分表
5. 表结构设计

工作中做过很多sql的优化，一般的优化我们并不是出现了问题才进行优化的，在进行数据库建模和数据库设计的时候会预先考虑到一些优化问题，比如字段的类型，长度等等，包括创建合适的索引等方式，但是这种方式只是提前的预防，并不一定能解决所有的问题，所以当我们生产环境中已经出现sql问题之后我会从数据库的性能监控，索引的创建和维护，sql语句的调整，参数的设置，架构的调整等多个方面去进行综合考虑，性能监控的会选择show profiles,performenace_schema来进行监控，索引。。。参数。。。，在我最近做的一个项目中，出现了XXX问题，我通过分析执行计划以及XXX的方式顺利解决了这个问题，并且在公司做了技术分享，详细了解对应数据的知识，在另外一个项目中。。。。

18.MVCC(多版本并发控制)

MVCC 在 MySQL InnoDB 中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读。

MVCC和Java中CAS类似，不加锁达到锁的效果；

基础知识：

当前读：读取的是数据的最新版本，总是读取到最新的数据。

例如下面都是例子都是当前读

select ... lock in share mode
select ... for update
update
delete
insert

快照读： 读取的是历史版本的记录，快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；

例子:

select ...

18.1MVCC组成部分

18.1.1第一部分：隐藏字段

刚开始表结构，没有指定主键，需要使用隐藏主键。前三行对用户可见（蓝色），后三行对用户不可见（黄色）。

开始插入一条数据：

18.1.2第二部分，undolog

当不同事务对同一条记录做修改的时候，会导致该记录的undolog形成一个线性表，也就是链表，链表的链首是最新的历史记录，而链尾是最早的历史记录。

问题：现在有了事务4，那么事务4读取到的数据是哪一个版本的数据？一定有对应的规则，需要按照规则来进行判断读取。

18.1.3第三部分 readview

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PDZHlkMK-1636121992053)(/Users/suhang/Library/Application Support/typora-user-images/image-20210825113033322.png)]

提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；

例子:

select ...

18.1MVCC组成部分

18.1.1第一部分：隐藏字段

[外链图片转存中…(img-xvhTvlhz-1636121992050)]

刚开始表结构，没有指定主键，需要使用隐藏主键。前三行对用户可见（蓝色），后三行对用户不可见（黄色）。

[外链图片转存中…(img-DagbqAey-1636121992051)]

开始插入一条数据：

[外链图片转存中…(img-RRSfUh1n-1636121992051)]

18.1.2第二部分，undolog

[外链图片转存中…(img-doj9x1Bm-1636121992052)]

[外链图片转存中…(img-OdbqARZP-1636121992052)]

当不同事务对同一条记录做修改的时候，会导致该记录的undolog形成一个线性表，也就是链表，链表的链首是最新的历史记录，而链尾是最早的历史记录。

问题：现在有了事务4，那么事务4读取到的数据是哪一个版本的数据？一定有对应的规则，需要按照规则来进行判断读取。

18.1.3第三部分 readview

计算机网络
Java基础
垃圾回收
IO流
Mysql
异常
Spring