mysql基础面试题

Mysql

索引的基本原理

索引用来快速地寻找那些具有特定值的记录。如果没有索引，一般来说执行查询时遍历pingshang整张表。
索引的原理：就是把无序的数据变成有序的查询。

1，把创建了索引的列的内容进行排序。
2，对排序结果生成倒排表
3，在倒排表内容上拼上数据地址链
4，在查询的时候，先拿到倒排表内容，在取出数据地址链，从而拿到具体数据

mysql聚族和非聚族索引的区别

都是B+树的数据结构
聚族索引：将数据存储与索引放到了一块、并且是按照一定的顺序组织的，找到索引也就找到了数据，数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的
非聚族索引：叶子节点不存储数据、存储的是数据行地址，也就是说根据索引查找到数据行的位置再取磁盘查找数据，这个就有点类似一本书的目录，比如我们要找第三章第一节，那我们先在这个目录里面找，找到对应的页码看文章。

优势：
1，查询通过聚族索引可以直接获取数据，相比非聚族需要第二次查询（非覆盖索引的情况下）效率高。
2，聚族索引对于范围查询的效率很高，因为其数据是按照大小排列的
3，聚族索引适合用在排序的场合，非聚族索引不适合。

劣势：
1，维护索引很昂贵，特别是插入新行或者主键被更新导致要分页（page splist）的时候。建议在大量插入新行后，选在负载较低的时间段，通过OPTIMIZE TABLE优化表，因为必须被移动的行数据可能造成岁表。使用独享表空间可以弱化碎片。
2，表因为使用UUID（随机ID）作为主键，使数据存储稀疏，这就会出现聚族索引有可能比全表扫描更慢，所以建议使用int的auto_increment作为主键
3，如果主键比较大的话，那辅助索引将会变得更大，因为辅助索引的叶子存储的是主键值，过长的主键值，会导致非叶子节点占用更多的物理空间。

InnoDB中一定有主键，主键一定是聚族索引，不手动设置、则会使用unique索引，没有unique索引，则会使用数据库内不得一个行得隐藏id来当作主键索引。在聚族索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找，非聚族索引都是辅助索引，像复合索引、前缀索引、唯一索引、辅助索引叶子节点存储的不再是行的物理位置，而是主键值

MyISM使用的是非聚族索引，没有聚族索引，非聚族索引的两颗B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树存储了主键，辅助索引B+树存储了辅助健。表数据存储独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个健没有任何差别。由于索引树是独立的，通过辅助健检索无需访问主键的索引树。

如果涉及到大数据量的排序、全表扫描、count之类的操作的话，还是MyIsam占优势些，因为索引占空间小，这些操纵是在内存中完成的。

mysql索引的数据结构，各自优劣

索引的数据结构和具体的存储引擎的实现有关，在MySQL中使用较多的索引有Hash索引，B+树索引等，InnoDB存储引擎的默认实现为：B+树索引。对于哈希表来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录的时候，可以选择哈希索引，查询性能最快，其余大部分场景，建议选择BTree索引。

B+树：
B+树是一个平衡的多叉树，从根节点到每个叶子节点的高度差不超过1，而且同层级的节点间有指针相互链接。在B+树上的常规检索，从根节点到叶子节点的搜索效率基本相当，不会出现大幅波动，而且基于索引的顺序扫描时，也可以利用双向指针左右移动，效率非常高。因此，B+树索引被广泛应用于数据库、文件系统等场景。

索引设计的原则

查询更快、占用空间更小
1，适合索引的列是出现在where子句中的列，或者连接子句中指定的列
2，基数较小的表，索引效果较差，没有必要在此列建立索引
3，使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量的索引空间，如果搜索词超过索引前缀长度，则使用索引排除不匹配的行，然后检查其余行是否可能匹配。
4，不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只支持保持需要的索引有利于查询即可。
5，定义有外键的数据列一定要建立索引。
6，更新频繁的字段不适合创建索引。
7，若是不能有效区分数据的列不适合做索引（如性别，男女未知，最多也就三种，区分度实在太低）
8，尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加（a,b）的索引，那么只需要修改原来的索引即可。
9，对于那些查询很少涉及的列，重复值比较多的列不要建立索引。
10，对于定义为text、image和bit的数据类型不要建立索引。

锁的类型有哪些

基于锁的属性分类：共享锁、排他锁。
基于锁的粒度分类：行级锁（INNODB），表级锁（INNODB、MYISAM），页级锁（BDB引擎）、记录锁、间隙锁、临健锁。

基于锁的状态分类：意向共享锁、意向排它锁

• 共享锁（Share Lock）
  共享锁又称读锁，简称S锁：当一个事务为数据加上读锁之后，其他事务只能对该数据加读锁，而不能对数据加写锁，直到所有的读锁释放之后其他事务才能对其进行持写锁。共享锁的特性主要是为了支持并发的读取数据，读取数据的时候不支持修改，避免出现重复度的问题。

• 排他锁（eXclusive Lock）
  排他锁又称写锁，简称X锁：当一个事务为数据加上写锁时，其他要求将不能再为数据加任何锁，直到该锁释放之后，其他事务才能对数据进行加锁。排他锁的目的是在数据修改时候，不允许其他人同时修改，也不允许其他人读取。避免了出现脏数据和脏读的问题。

• 表锁
  表锁是指上锁的时候锁住的整个表，当下一个事务访问该表的时候，必须等前一个事务释放了锁才能进行访问：
  特点：粒度大，加锁简单，容易冲突。

• 行锁
  行锁是指上锁的时候锁住的是表的某一行或多行记录，其他事务访问同一张表时，只有被锁住的记录不能访问，其他记录可正常访问。

• 记录锁
  记录锁也属于行锁的一种，只不过记录锁的范围只是表中的某一条记录，记录锁是事务在加锁后锁住的只是表的某一条记录。
  精准条件命中，并且命中的条件字段是唯一索引
  加了记录锁之后数据可以避免数据在查询的时候被修改的重复读问题，也避免了在修改的事务在未提交前被其他事务读取的脏读问题。

• 页锁
  页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快。但冲突多，行级冲突少，但速度慢。

• 间隙锁
  属于行锁中的一种，间隙锁是在事务加锁后其锁住的是表记录的某一个区间，当表的相邻ID之间出现空隙则会形成一个区间，遵循左开右闭原则。
  范围查询并且查询未命中记录，查询条件必须命中索引、间隙锁只会出现在RR的事务级别中。
  触发条件：防止幻读问题，事务并发的时候，如果没有间隙锁，就会发生如下图的问题，在同一个事务里，A事务的两次查询的结果会不一样。

比如表里面的数据ID为 1，4，5，7，10，那么会形成以下几个间隙区间，-n-1区间，1-4区间，7-10区间，10-n区间

• 临建锁

InnoDB存储引擎的锁的算法

Record lock:单个行记录上的锁
Gap lock：间隙锁，锁定一个范围，不包括记录本身
Next-key lock：record+gap 锁定一个范围，包括记录本身。
相关知识点：

1. innodb对于行的查询使用next-key lock
2. Next-locking keying为了解决Phantom Problem幻读问题
3. 当查询的索引含有唯一属性时，将next-key lock降级为record key
4. Gap锁设计的目的是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生
5. 有两种方式显式关闭gap锁：（除了外键约束和唯一性检查外，其余情况仅使用record lock） A.
   将事务隔离级别设置为RC B. 将参数innodb_locks_unsafe_for_binlog设置为1

慢sql优化

在业务系统中，除了使用主键进行的查询，其他的都会在测试库上测试其耗时，慢查询的统计主要由运维在做，会定期将业务中的慢查询反馈给我们。
慢查询的优化首先要搞明白原因？
是查询条件没有命中索引？
是load了不需要的数据列？
还是数据量太大？

所以优化也是针对这三个方向来的

• 首先分析语句，看看是否load了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写。
• 分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引。
• 如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表。

事务的基本特性和隔离级别

事务基本特性ACID分别是：
原子性指的是一个事务中的操作要么全部成功，要么全部失败。
一致性指的是数据库总是从一个一致性的状态转换到另外一个一致性的状态。
隔离性指的是一个事务的修改在最终提交前，对其他事务是不可见的。
持久性指的是一旦事务提交，所做的修改就会永久保存到数据库中。

隔离性有4个隔离级别，分别是：

• read uncommit 读未提交：可能会读到其他事务未提交的数据，也叫脏读。
  用户本来应该读取到id=1的用户age应该是10，结果读取到了其他事务还没有提交的事务，结果读取结果age=20，这就是脏读。
• read commit读已提交：两次读取结果不一致，叫做不可重复度。
  不可重复读解决了脏读的问题，他只会读取已经提交的事务。
  用户开启事务id=1用户，查询到age=10，再次读取发现结果=20，在同一个事务里同一个查询读取到不同的记过叫做不可重复度。
• repeatable read可重复度，这是mysql的默认级别，就是每次读取结果都一样，但是有可能产生幻读。
• serialzable串行;一般是不会使用的，他会给每一行读取的数据加锁，会导致大量超时和锁竞争的问题。

脏读：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。

不可重复读（Non-repeateble）：在一个事务的两次查询中数据笔数不一致，这可能是两次查询过程中插入了一个事务更新的原有的数据。

幻读：在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列（Row）数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是他先前所没有的。

ACID靠什么保证

A原子性由undo log日志保证，它记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的sql。

C一致性由其他三大特征保证、程序代码要保证业务上的一致性

I隔离性 由MVCC来保证。

D 持久性由内存+redo log来保证，mysql修改数据同时在内存和redo log记录这次操作，宕机的时候可以从redo log恢复。

InnoDB redo log写盘，InnoDB事务进入prepare状态。如果前面prepare成功，binlog写盘，再继续将事务日志持久化binlog，如果持久化成功，那么InooDB事务则进入commit状态（再redo log里面写一个commit记录）

什么是MVCC

多版本并发控制：读取数据时通过一种类似快照的方式将数据保存下来，这样读锁和写锁不冲突了，不同的事务session会看到自己特定的版本，版本链。
MVCC只在READ COMMITTED和REPETABLE READ两个隔离级别下工作。其他两个隔离级别和MVCC不兼容，因为READ UNCOMMITED总是读取最新的数据行，而不是符合当前事务版本的数据行。而SERIALIZABLE则会对所有读取的行都加锁。

聚族所以记录中有两个必要的隐藏列：
trx_id:用来存储每次对某条

分表后非sharding_key的查询怎么处理

1，可以做一个mapping表，比如这时候商家要查询订单列表怎么办呢？ 不带user_id查询的话你总不能全表扫描吧？ 所以我们可以做一个映射关系表，保存商家和用户的关系，查询的时候先通过商家查询到用户列表，再通过user_id去查询。

2，宽表，对数据实时性要求不是很高的场景，比如查询订单列表，可以把订单表同步到离线（实时）数仓，再基于数仓去做成一张宽表，再基于其他如es提供查询服务。

3，数据量不是很大的话，比如后台的一些查询之类的，也可以通过多线程扫表，然后再聚合结果的方式来做。或者异步的形式也是可以的。

union：
排序字段是唯一索引：
首先第一页的查询：将各表的结果集体合并，然后再次排序

第二页及以后的查询，需要传入上一页排序字段的最后一个值，及排序方式。

根据排序方式，及这个值进行查询。如排序字段date，上一页最后值为3，排序方式降序。查询的时候sql为select …from table where date<3 order by date desc limit 0,10。这样再将几个表的结果合并并排序即可。

mysql 主从同步原理

mysql主从同步原理的过程：
Mysql的主从复制中主要有三个线程： master（binlog dump thread）、slave（I/O thread、SQL thread），Master 一条线程和Slave中的两条线程。

主节点binlog，主从复制的基础是主库记录数据库的所有变更记录到binlog。binlog是数据库服务器启动的那一刻起，保存所有修改数据库结构或内容的一个文件。

• 主节点log dump线程，当binlog有变动时，log dump线程读取其内容并发发送给从节点。

• 从节点I/O线程接收binlog内容，并将其写入到relay log文件中。

• 从节点的SQL线程读取relay log文件内容对数据更新进行重放，最终保证主从数据库的一致性。

注：主节点使用binlog文件+position偏移量来定位主从同步的位置，从节点会保存其已接收到的偏移量，如果从节点发送宕机重启，则会自动从position的位置发起同步。

由于mysql默认的复制方式是异步的，主库把日志发送给从库后不关心从库是否已经处理，这样会产生一个问题就是假设主库挂了，从库处理失败了，这时候从库升为主库后，日志就丢失了。由此产生两个概念。

全同步复制
主库写入binlog后强制同步日志到从库，所有的从库都执行完后才返回给客户端，但是很显然这个方式的话性能会受到严重影响。

半同步复制
和全同步不同的是，半同步复制的逻辑是这样，从库写入日志成功后返回ACK确认给主库，主库收到至少一个从库的确认就认为写操作完成。

简述mysql中索引类型对数据库的性能的影响

• 普通索引
  允许被索引的数据包含重复的值
• 唯一索引
  可以保证数据记录的唯一性
• 主键
  是一种特殊的索引，在一张表中只能定义一个主键索引，主键用于唯一标识一条记录，使用关键字Primary key来创建
• 联合索引
  索引可以覆盖多个数据列，如像Index（columnA，columnB）索引。
• 全文索引
  通过建立倒排索引，可以极大的提高检索效率，解决判断字段是否包含的问题，是目前搜索引擎使用的一种关键技术。可以通过Alter table table_name add fulltext （column）；创建全文索引
  索引可以极大的提高数据的查询速度。
  通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

但是会降低插入、删除、更新表的速度，因为在执行这些写操作时，还要操作索引文件
索引需要占用物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，