1. Mysql背景

mysql 是一个最流行并且典型的关系型数据库，体积小，速度快，成本低。

2. 数据库三大范式

第一范式：每个列都不可以再拆分
第二范式：在第一范式基础上，非主键列完全依赖于主键，而不是依赖主键的一部分
第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键

3. 事务的四大特性（ACID）

原子性（Automicity）：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么都完成，要么都不起作用。
一致性（consistency）：执行事务前后，数据保持一致性，多个事务对同一个数据读取的结果是相同的。
隔离性（isolation）：并发访问数据库时，一个用户的事务不会被其他事务干扰。各并发事务之间数据库是独立的。
持久性（durability）：一个事务被提交后，它对数据库中数据的改变是持久的，即使数据发生改变故障也不应该对其有任何影响。

4. SQL语句主要分为哪几类？

数据库定义语句DDL：CREATE、DROP、ALER 创建、删除、修改表
- 主要为以上操作即对逻辑结构等有操作的，其中包括表结构，视图和索引
数据库查询语句DQL SELECT
- 查询操作
数据操纵语言DML INSERT、 UPDATE、DELETE
- 主要对数据进行非查询操作
数据控制功能DCL GRANT、REVOKE、COMMIT、ROLLBACK
- 对数据库安全性、完整性操作，理解为权限控制等。

5. MySQL 分库分表的目的是？

分库分表就是为了解决由于数据量过大而导致数据库性能降低的问题，将原来独立的数据库拆分成若干数据库组成，将数据大表拆分成若干数据表组成，使得单一数据库、单一数据表的数据量变小，从而达到提升数据库性能的目的。

6. 什么是锁？

锁是计算机协调多个进程或线程并发访问某一资源的机制。
锁保证数据并发访问的一致性、有效性
锁冲突也是影响数据库并发访问性能的一个重要因素。
锁是Mysql在服务器层和存储引擎层的并发控制。
加锁是消耗资源的，锁的各种操作，包括获得锁、检测锁是否已解除、释放锁等。

7. 乐观锁和悲观锁

悲观锁
- 悲观锁是认为操作的数据大概率会有冲突，所以在修改数据之前会把数据锁住，不允许其它事务进行读写，然后再自己修改提交事务后再释放锁
- 悲观锁是使用数据库提供的共享锁或排他锁实现，但因为加锁释放锁过程会造成消耗，所以性能不高。
乐观锁
- 乐观锁是对数据冲突保持乐观态度，操作数据的时候不会对数据加锁，只有到数据提交的时候才会通过一种机制来验证是否冲突。
- 乐观锁是一种并发类型的锁，其本身不是对数据加锁，而是通过业务实现锁的功能，一般是对数据增加版本号，修改数据会验证两次拿到的版本号是否一致，MVCC也是一种乐观锁。

8. 什么是死锁？怎么解决？

死锁是指两个或多个事务在同一资源上的相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。
表锁是没有死锁的，主要是行级锁死锁。
常见解决死锁的方法
- 1. 如果不同程序会并发存取多个表，尽量约定以相同顺序的访问表，可以降低死锁机会。
- 1. 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率。
- 1. 对于非常容易产生死锁的业务部分，可以升级锁粒度，通过表级锁来减少死锁产生。

9. 什么是脏读？幻读？不可重复读？

脏读：某个事务已更新一份数据，另一个事务在此时读取了同一个数据，由于某些原因，前一个事务进行了事务回滚(RollBack)操作，则后一个事务所读取的数据就不是正确的。
幻读：在一个事务的两次范围查询中数据行数不一样，例如：一个事务查询了几列数据，另一个事务在此时插入了几列数据，先前的事务在接下来的查询中，就会发现有几列是之前没有的。
不可重复读：在一个事务的两次查询中数据不一致，这可能是两次查询过程中插入了一个事务更新原有的数据。

10. 数据库的四大隔离等级

Read Uncommitted(读未提交)
- 指一个事务可以读取另一个事务还没有提交的数据，但会有脏读、幻读、不可重复读
Read Committed(读提交)
- 一个事务要等另一个事务提交后才能读取数据。但会有不可重复读、幻读
Repeatable Read(重复读)
- 在一个事务开始读取数据时，另外的事务修改这条数据也不会影响当前事务的数据（MVCC）。但会有幻读
Serializable(串行化)
- 数据库隔离最高等级，事务按照串行顺序执行(锁表)。可以避免脏读、不可重复读、幻读。但是效率低
Mysql默认使用的是：Repeatable Read（重复读），Oracle 默认是 Read Committed
- 由于序列化(Serializable)串行的效率低，而 RU 有脏读现象就抛弃了默认选项
- RC 和 RR 由于binlog 存储的是除开select、show以外的数据更新语句，RC 会导致如果事务1 先写入语句准备更新，而事务2先提交，可能会导致事务2 先被记录下来，而事务1后记录。

CREATE TABLE t1 (  
     a int(11) DEFAULT NULL,  
     b int(11) DEFAULT NULL,  
     KEY a (a)  
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1;  
   insert into t1 values(10,2),(20,1);

- 该图导致主从复制时，从库的数据就会出现两个 (11,2)的数据，而主库的数据为 (11,2)和(20,2)
- 所以为了防止这个问题出现，选用了RR做为数据库的默认隔离等级。

11. 视图的特点

视图的列可以来自不同的表，是表的抽象和在逻辑意义上建立的新关系
视图是由基本表产生的虚拟表。视图的建立和删除不影响基础表
视图的内容更新(新增、删除、修改)数据，会影响基础表
视图来自多个表时，不允许添加和删除数据
视图操作包括：创建、查看、删除、修改

12. SQL的生命周期

应用服务器与数据库建立连接
数据库进行拿到请求的sql语句
解析并生成执行计划，开始执行
读取数据到内存中并进行逻辑处理
通过第一步连接，通知到客户端
关闭连接，释放资源

13. Mysql数据库CPU飙高到100%怎么处理？

当cpu飙升到100%时，操作系统使用top观察是否时mysqld占用导致
如果不是，找出占用高的进程，进行相关处理
如果是mysqld造成的，使用show precesslist 看看session情况，是否由消耗资源的sql在运行。找出消耗高的sql，观察是否需要处理索引缺失，数据量太大等的问题
一般来说，杀掉这些线程后，需要观察cpu是否降低，进行慢sql分析，然后重新跑sql
也有可能突然之间请求过多导致cpu飙升导致的，需要分析为何连接数飙高，看是否缓存失效而做出相应调整，比如限制连接数等。

14. mysql主从复制解决了哪些问题？

主从复制的作用是：
- 主数据库出现问题，可以切换到从数据库。可以进行数据库层面的读写分离(主写从读)，可以进行数据库备份等。
- 数据分布：在不同的地理位置分布数据备份
- 负载均衡：降低单个服务器的压力
- 高可用和故障切换：帮助应用程序避免单点失败
- 升级测试：可以使用更高版本的mysql作为从库

15. mysql的读写分离

使用一主多从的架构进行使用（提供吞吐量）
- 主数据库进行更新数据，从库进行读取数据，在主库更新提交数据时会写入binlog的二进制文件，并会fork出一个线程进行复制到从库中。

import random
from flask import Flask
from flask_sqlalchemy import SQLAlchemy, SignallingSession, get_state
from sqlalchemy import orm
from sqlalchemy.sql.dml import UpdateBase

app = Flask(__name__)

# 设置单个数据库URI (用于建表)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:mysql@192.168.105.140:3306/test31'

# 设置多个数据库的URI (用于数据操作)
app.config['SQLALCHEMY_BINDS'] = {
    'master': 'mysql://root:mysql@192.168.105.140:3306/test31',
    'slave1': 'mysql://root:mysql@192.168.105.140:8306/test31',
    'slave2': 'mysql://root:mysql@192.168.105.140:3306/test31'
}

# 其他配置
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_ECHO'] = True


# 1. 自定义Session类, 继承SignallingSession, 并重写get_bind方法
class RoutingSession(SignallingSession):
    def __init__(self, db, autocommit=False, autoflush=True, **options):
    	# 先完成父类方法的默认操作
        super(RoutingSession, self).__init__(db, autocommit, autoflush, **options)
        # 每个Session(请求), 随机一次从库，可以保证每次请求中查询时会使用同一个从库，提高效率并且减少数据不一致的错误现象
        self.slave = random.choice(['slave1', 'slave2'])

    def get_bind(self, mapper=None, clause=None):
        """每次数据库操作(增删改查及事务操作)都会调用该方法, 来获取对应的数据库引擎(访问的数据库)"""

        state = get_state(self.app)

        if mapper is not None:  # 如果该操作中涉及的模型类和数据表建立了映射
            try:
                # SA >= 1.3
                persist_selectable = mapper.persist_selectable
            except AttributeError:
                # SA < 1.3
                persist_selectable = mapper.mapped_table

            info = getattr(persist_selectable, 'info', {})
            bind_key = info.get('bind_key')  # 查询模型类是否指定了访问的数据库

            if bind_key is not None:  # 如果该模型类已指定数据库, 使用指定的数据库
                return state.db.get_engine(self.app, bind=bind_key)

        if self._flushing or isinstance(clause, UpdateBase):  # 如果模型类未指定数据库, 判断是否为写操作 该属性为True
            print('写操作')
            return state.db.get_engine(self.app, bind='master')
        else:

            print('读操作: ', self.slave)
            return state.db.get_engine(self.app, bind=self.slave)


# 2. 自定义SQLALchemy类, 重写create_session方法
class RoutingSQLAlchemy(SQLAlchemy):
    def create_session(self, options):
        return orm.sessionmaker(class_=RoutingSession, db=self, **options)


# 创建组件对象
db = RoutingSQLAlchemy(app)


# 构建模型类
class User(db.Model):
    __tablename__ = 't_user'
    # __bind_key__ = 'master'  # 指定模型访问的数据库
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column('username', db.String(20), unique=True)
    age = db.Column(db.Integer, default=0, index=True)


@app.route('/')
def index():
    """增加数据"""

    print('---读-----------')

    users = User.query.all()
    for user in users:
        print(user.id, user.name, user.age)

    # print('---写-----------')
    #
    # user1 = User(name='zs', age=20)
    # db.session.add(user1)
    # db.session.commit()

    print('---读-----------')

    users = User.query.all()
    for user in users:
        print(user.id, user.name, user.age)

    return "index"


if __name__ == '__main__':
    # 重置所有继承自db.Model的表
    # 如果模型类没有设置__bind_ky__属性(指定对应的数据库), 则DDL操作 根据SQLALCHEMY_DATABASE_URI 指定的数据库进行处理
    db.drop_all()
    db.create_all()
    app.run(debug=True, host='0.0.0.0')

16. 慢sql

慢sql开启：
- 开启慢sql查询日志，设置超过几秒为慢sql语句，抓取sql语句。通过slow_query_log表示是否开启慢sql
使用explain分析慢查询

- id：反映的是表的读取顺序或查询中执行select子句的顺序
- - id相同，执行顺序是由上至下的
- - id不同，如果是子查询，id序号会递增，id值越大优先级越高，越先被执行
- - id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行
- select_type：表示select的类型，主要用于区别普通查询、联合查询、子查询等复杂查询
- - simple：简单的select查询，查询中不包含子查询或union
- - primary：查询中若包含任何复杂的子部分，最外层查询标记为primary
- - subquery：select或where列表中的子查询
- - derived（衍生）：在from列表中包含的子查询，MySQL会递归执行这些子查询，把结果放在临时表里
- - union：若第二个select出现在union后，则被标记为union；若union包含在from子句的子查询中，外层select将被标记为derived
- - union result：union后的结果集
- table：显示这一步所访问数据库中表名称（显示这一行的数据是关于哪张表的），有时不是真实的表名字，可能是第几步执行的结果的简称
- type：对表的访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。常见的访问类型有ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）
- - ALL：Full Table Scan，MySQL将遍历全表以找到匹配的行
- - index：Full Index Scan，index与ALL的区别为index类型只遍历索引树
- - range：索引范围扫描，返回一批只检索给定范围的行，使用一个索引来选择行，一般就是在where语句中出现between、< 、>、in等的查询。这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引
- - ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而它可能会找到多个符合条件的行，所以应该属于查找和扫描的混合体
- - eq_ref：类似ref，区别在于使用的索引是唯一索引，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描。简单来说，就是多表连接中使用primary key或者unique key作为关联条件
- - const、system：当MySQL对查询某部分进行优化并转换为一个常量时，使用这些类型访问。如果查询条件用到常量，那么通过索引一次就能找到，常在使用primary key或unique的索引中出现。system是const类型的特例，当查询的表只有一行的情况下使用
- - NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成
- possible_keys：指出MySQL可能使用哪个索引在该表中找到行，查询涉及的字段上若存在索引，则该索引将被列出，但不一定会被查询使用。
- key：显示MySQL实际决定使用的索引，如果没有选择索引，则显示是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX或者IGNORE INDEX。查询中若使用了覆盖索引（select后要查询的字段刚好和创建的索引字段完全相同），则该索引仅出现在key列表中。
- key_len：显示索引中使用的字节数。
- ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
- rows：显示MySQL根据表统计信息以及索引选用的情况，估算找到所需的记录要读取的行数
- Extra：该列包含MySQL解决查询的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息
- - Using where：不用读取表中所有信息，仅通过索引就可以获取所需数据，发生在对表的全部请求列都是同一个索引部分的时候，表示MySQL服务器将在存储引擎检索行后再进行过滤
- - Using temporary：表示MySQL需要使用临时表来存储结果集，MySQL在对查询结果排序时使用临时表，常见于排序（order by）和分组查询（group by）
- - Using filesort：当Query中包含order by操作而且无法利用索引完成的排序操作称为“文件排序”，创建索引时会对数据先进行排序，出现using filesort一般是因为order by后的条件导致索引失效，最好进行优化
- - Using join buffer：表明使用了连接缓存，比如说在查询的时候，多表join的次数非常多，就将配置文件中缓冲区的join buffer调大一些。如果出现了这个值，应该注意，根据查询的具体情况可能需要添加索引来改进
- - Using index：只使用索引树中的信息，而不需要进一步搜索读取实际的行来检索表中的列信息。相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率好。覆盖索引：select后的数据列只从索引就能取得，不必读取数据行，且与所建索引的个数（查询列小于等于索引个数）、顺序一致。如果要用覆盖索引，就要注意select的列只取需要用到的列，不用select *，同时如果将所有字段一起做索引会导致索引文件过大，性能会下降
- - Using Index Condition：表示进行了ICP优化

17. 聚集索引与非聚集索引

聚集索引

又叫聚簇索引，数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。
聚集索引表记录的排列顺序和索引的排列顺序保持一致，所以查询效率相当快。只要找到第一个索引记录的值，其余的连续性的记录也一定是连续存放的。聚集索引的缺点就是修改起来比较慢，因为它需要保持表中记录和索引的顺序需要一致，在插入新记录的时候就会对数据也重新做一次排序。在数据表创建上INNODB聚集索引存储的是一个文件，后缀为.frmInnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。

非聚集索引

又叫二级索引，也就是除了主键以外的索引，该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。
非聚集索引定义了表中记录的一些逻辑顺序，但记录的物理和索引不一定保持一致，两种索引都采用B+树的结构，非聚集索引的叶子层并不和数据叶相互重叠，而是采用叶子层包含一个指向表中的记录指针。非聚集索引的缺点就是索引的层次比较多，但是不会造成数据的重排。在数据表创建上Myisam存储为三个文件.frm、.MYD、.MYI，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。
非聚簇索引查询数据需要先查询到聚簇索引的key，然后再用这个key去查询真正的数据(这个过程叫回表)，也就是说聚簇索引是需要查询两次。

覆盖索引

指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。

聚集索引和非聚集索引区别

相同点：内部都是B+树，高度平衡，叶子节点存放着所有的数据。
不同点：
- 聚集索引的叶子节点存放是一整行的信息。
- 聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个。
- 聚集索引存储记录是在物理上连续存在，而非聚集索引是逻辑上的连续，物理存储不连续。
- 聚集索引查询数据速度快，插入数据慢，范围查询快。
- 非聚集索引查询慢，插入快。

优劣势

优点
- 1. 可以把相关数据保存在一起。如：实现电子邮箱时，可以根据用户ID来聚集数据，这样只需要从磁盘读取少量的数据页就能获取某个用户全部邮件，如果没有使用聚集索引，则每封邮件都可能导致一次磁盘IO
- 1. 数据访问更快，聚集索引将索引和数据保存在同一个btree中，因此聚集索引中获取数据通常比在非聚集索引中查找要快。
- 1. 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。
缺点：
- 1. 聚簇数据最大限度地提高了IO密集型应用的性能，但如果数据全部放在内存中，则访问的顺序就没有那么重要了，聚集索引也没有什么优势了
- 1. 插入速度严重依赖于插入顺序，按照主键的顺序插入是加载数据到innodb表中速度最快的方式，但如果不是按照主键顺序加载数据，那么在加载完成后最好使用1optimize table`命令重新组织一下表
- 1. 更新聚集索引列的代价很高，因为会强制innodb将每个被更新的行移动到新的位置
- 1. 基于聚集索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂的问题，当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作，页分裂会导致表占用更多的磁盘空间

18. Gap Lock

简称为间隙锁
作用：保证可重复读的情况下不出现幻读。
行为：关键就是锁住索引树的叶子节点之间的间隙，防止新的记录插入到间隙中。

19. B树和B+树的区别

B树：是一种多路平衡的查找树，每一个节点都有可能存储数据，B树的子树数量 = 关键字数量 + 1
B+树：在B树的结构上做了一些改变，所有数据只存在叶子节点上。并且通过双向链表的形式，将所有的叶子节点全部串联起来。B+树的子树数量等于它的关键字数量。
非叶子节点只存储键值信息
所有叶子节点之间都有一个链指针
数据记录都存放在叶子节点中

20. 为什么B树不适合mysql查询

选择B树或者B+树作为数据库或者文件系统的索引，是为了减少磁盘的IO次数。
MongoDB 选择的就是B树作为索引，每一个节点都有可能存储数据，所以单次查询更快。
Mysql 作为关系型数据库，采用的是B+树索引进行存储数据的，原因是关系型数据库，数据关联比较强，加上B+树的所有叶子节点使用链表串联的，所以就很容易在区间遍历上或者是全部遍历。

21. 为什么Mysql索引结构采用B+树

从磁盘I/O效率方面来看：B+树的非叶子节点不存储数据，所以树的每一层可以存储更多的索引数据，B+树在层高相等的情况下，比B树存储的数据更多，可以间接减少磁盘的I次数。
从范围查询的效率来看：在Mysql中范围查询是一个比较常用的操作，而B+树的所有数据存在叶子节点使用双向链表的方式进行串联，所以在范围查询时，B+树只需要查询两个节点就可以了，而B树需要查询所有的节点
从全表扫描来看：B+树只需要扫描所有的叶子节点，而B树需要遍历所有节点
从自增ID方面来看：基于B+树的索引结构，可以更好的去避免增加数据带来的叶子节点分裂从而导致大量运算的问题。

21. 执行Sql响应比较慢，有哪些排查思路和解决方案？

1. 没有索引或者SQL没有命中索引导致的索引失效。
- 打开慢查询语句，收集到慢查询sql，使用Explain执行计划查询SQL是否命中索引，发现没有命中索引的话，要么新建索引，要么优化SQL语句，保证SQL走索引进行执行，使用到索引时，需要符合最左匹配原则。
- - 最左匹配原则：例如建立索引 (a、b、c)那么可以使用的索引为 (a)，（a、b），（a、b、c）如果使用ac索引的话就不能够走索引，但是最左前缀匹配原则，mysql会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配。例如，如果建立(a、b、c、d)：a=1 and b=2 and c>3 and d=4 这个时候索引走到c就断了，就用不到d的索引。但是如果建立 (a、b、d、c) 上述就可以使用到索引。
1. 单表数据量过多，导致查询瓶颈。
- 对表进行拆分
- - 水平切分：将一个数据量过大的表，根据业务主键切分为多张小表。
- - 垂直切分：将单表的多个列，进行拆分。
- 对库进行拆分
- - 将表拆分后的若干个小表进行分库，然后根据业务主键，将请求路由到不同的数据库实例进行访问。然后让每一个数据库实例承担一定的流量，达到性能的提升。
1. 网络原因或者机器负载过高。
- 进行读写分离(主写从读)
- - 将主库用来进行写操作通过binlog进行从库的备份复制，如果流量过大，可以通过增加从库负载能力，从而总体提升数据库的性能。
1. 热点数据导致单点负载不均衡。
- - 增加缓存，将查询比较频繁的数据存放在缓存中，可以通过Redis、Elasticsearch、MongoDb进行数据缓存。