MySQL

Ymq
121 阅读12分钟

MySQL

  1. 数据库三大范式

    第一范式（1NF）：第一范式要求表中的每一列只包含原子值，原子值是不可分割的值，不能进一步细分。这意味着必须将重复的值组分隔到各自的表中。
    第二范式（2NF）：第二范式要求表中的每个非键列仅依赖于主键。这意味着任何不是主键一部分的列都必须在功能上依赖于整个主键。
    第三范式（3NF）：第三范式要求数据库中没有传递依赖关系。这意味着，如果非键列依赖于另一个非键列，则必须将其从表中删除并放置在单独的表中。

  2. MySQL存储引擎MyIsam和InnoDB有哪些区别。

    1.InnoDB 支持事务，MyISAM 不支持事务
    2.对一个包含外键的 InnoDB 表转为 MYISAM 会失败, MyISAM不支持外键
    3.InnoDB 不保存表的具体行数需要全表扫描，而MyISAM 用一个变量保存了整个表的行数
    4.InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。一个更新语句会锁住整张表,导致其他查询和更新都会被阻塞，因此并发访问受限 5.系统崩溃后，MyISAM恢复起来更困难，能否接受，不能接受就选 InnoDB
    6.InnoDB的主键索引的叶子节点存储的是行数据，因此主键索引非常高效。
    7.MyISAM索引的叶子节点存储的是行数据地址，需要一次寻址的操作才能获取到数据。

    备份及恢复:
    MyISAM: 数据是以文件的形式存储，所以在跨平台的数据转移中会很方便,在备份和恢复时可单独针对某个表进行操作
    InnoDB: 拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了

  3. 什么是索引？都有哪些类型？有什么优点和缺点？

    索引是数据库中为了提高数据查询效率而创建的一种数据结构。主要作用是帮助查询进行快速定位，而不需要扫描整个数据表。

    索引可以分为多种类型，其中常见的类型包括：B-Tree 索引、Hash 索引、Bitmap 索引
    索引有以下优点：提高查询效率，可以快速定位数据、提高数据的读取速度、减小数据的读取负荷
    索引也有以下缺点：需要额外的空间存储索引、索引的更新操作会变慢、索引会使数据的写入变慢

    哈希索引：
    优点：1.索引本身只存储 hash code，所以结构很紧凑，并且查找速度很快
    限制：
    1.索引中的 hash code 是顺序存储的，但是 hash code 对应的数据并不是顺序的，所以无法用于排序
    2.不支持部分索引列匹配查找，因为哈希索引是使用索引列的全部内容来计算 hash code
    3.只支持等值比较，不支持范围查询
    4.如果哈希冲突严重时，必须遍历链表中所有行指针
    5.哈希冲突严重的话，索引维护操作的代价也很高

  4. B树和B+树的区别，聚集索引和非聚集索引的区别。什么是回表，怎么减少回表？

    B树：二叉树，每个结点只存储一个关键字，等于则命中，小于走左结点，大于走右结点；
    B-树：多路搜索树，每个结点存储M/2到M个关键字，非叶子结点存储指向关键字范围的子结点；所有关键字在整颗树中出现，且只出现一次，非叶子结点可以命中；
    B+树：在B树基础上，为叶子结点增加链表指针，所有关键字都在叶子结点中出现，非叶子结点作为叶子结点的索引；B+树总是到叶子结点才命中；
    B+树的优点：
    1. B+树能显著减少IO次数，提高效率
    2. B+树的查询效率更加稳定，因为数据放在叶子节点
    3. B+树能提高范围查询的效率，因为叶子节点指向下一个叶子节点

    聚集索引:
    产生原因:
    当给表加上主键的时候，这个表就会转化成我们刚刚所说的B+树的形状，相当于整个表就变成了一个索引，所以这就成为聚集索引
    查询原理:
    每次查找的时候，都是根据主键进行查找的，也就是非叶子结点中存储的就是主键中的数据，每次查找都要找到相应的叶子结点，然后取到相应的数据
    缺点:
    有了聚集索引，那么查询自然是快了许多，但是实际上为增，删等写入数据的操作变慢
    原因是因为每次写入操作都需要进行平衡二叉树的判断，修改树的结构，这样肯定是浪费了时间的

    非聚集索引:
    产生原因:
    每次当我们定义一个字段(非主键)为非聚集索引，那么数据库就会将这个表中的该字段复制一份构成一颗B+树,这棵B+树中的叶子结点存储的是相应的主键，非叶子结点存储当然是索引指针了
    我们定义多个字段分别为非聚集索引，那么就会生成多颗B+树，并且是不互相干扰的
    查询原理:
    非聚集索引的查找其实就是最终查找到相应的主键，然后再通过聚集索引树去查找数据——所以可以看做是二次查 找

    回表：
    非主键索引进行查询，select所要获取的字段不能通过非主键索引获取到，需要通过非主键索引获取到的主键，从聚集索引再次查询一遍，获取到所要查询的记录，这个查询的过程就是回表。
    减少回表：
    1. 缓存：通过缓存常用数据，减小访问数据库的频率，从而减少回表。
    2. 批量请求：通过一次请求获取多个数据，减少回表的次数。
    3. 建立联合索引：建立索引以加快数据查询速度。

  5. 为什么会有索引失效的情况，索引调优有哪些方法？

    SQL语句没有使用到索引，回表查询整张表。

    索引调优：
    1. 最左前缀法则、尽量不使用函数或+、-、!=、 <、>、is null、or
    2. like 导致索引失效：like "%张三"
    3. 使用主键进行order by
    4. explain分析SQL语句、查看可能用到的索引进行优化
    type: 依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL(一般来说，得保证查询达到range级别，最好达到ref)

  6. MySQL中的锁有哪些？什么是next-key lock？

    行锁表锁：只有明确知道主键，才会执行行锁，否则执行表锁。没有索引会使用表锁

    1. 表级锁：对整张表上锁，不允许其他线程读写数据，主要使用的是LOCK TABLES语句。
    2. 行级锁：锁定表中的某一行数据，不允许其他线程读写该行数据，主要使用的是SELECT...FOR UPDATE语句。行锁在 InnoDB 中是基于索引实现的，所以一旦某个加锁操作没有使用索引，那么该锁就会退化为表锁。

    表锁： 意向锁：意向锁的主要作用是判断表里是否有记录被加锁，也就是表里是否有行级锁。当需要加一个表级锁时，需要判断表里是否有独占锁，如果没有意向锁就需要遍历表里所有记录。

    行锁： 记录锁：为一个行的数据上锁，主要使用的是UPDATE语句。 间隙锁：存在于非唯一索引中，使用间隙锁锁住的是一个区间(不包含下标两个行)，而不仅仅是这个区间中的每一条数据。 临键锁：(next-key lock)通过临建锁可以解决幻读的问题。每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。InnoDB 中行级锁是基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。

  7. 乐观锁与悲观锁。

    乐观锁和悲观锁是数据库并发控制的两种策略。 1.乐观锁是一种更为宽松的并发控制策略，它基于一种无阻塞的思想，即相信在大多数情况下，并发冲突是不会发生的。乐观锁通常通过版本号等机制来实现，在更新数据时，会先检查数据的版本号是否与预期的版本号一致，如果不一致说明该数据已经被其他事务修改，此时更新操作会失败。 2.悲观锁是一种更为严格的并发控制策略，它基于一种假设最坏情况的思想，即并发冲突总是会发生。悲观锁通常通过互斥锁等机制实现，在访问数据时，会先对数据加锁，在该事务结束前保持锁定状态，阻止其他事务对数据进行修改。

  8. Mysql的事务隔离级别有哪些？MVCC是什么？

    读未提交、读已提交、可重复读、可串行化 MVCC是MySQL等数据库系统中提供的一种并发控制机制。在使用MVCC时，每个事务看到的数据版本是独立的，互不干扰。MVCC的核心思想就是维护多个版本的数据，每个事务仅能看到在事务开始时快照的版本。这样就可以实现高效的并发控制。

    组成:
    1. 表的隐藏字段：记录最近修改事务id字段(trx_id)、及上个版本的数据地址字段(roll_pointer)、row_id隐藏的主键在表没有指定主键时
    2. undo log：记录数据各个版本修改历史即事务链
    3. Read View：读试图，用于判断哪些版本可见
    read View包含字段：
    trx_list: 表示在生成 ReadView 时当前系统中活跃的读写事务的 事务id 列表
    up_limit_id: 表示在生成 ReadView 时当前系统中活跃的读写事务中最小的 事务id
    low_limit_id: 表示生成 ReadView 时系统中应该分配给下一个事务的 id 值
    trx_id: 表示生成该 ReadView 的事务的 事务id

    版本未提交，不可见；
版本已提交，但是是在视图创建后提交的，不可见；
版本已提交，而且是在视图创建前提交的，可见。

  9. 脏读，不可重复读，幻读是什么？是怎么解决的。

    脏读，幻读，不可重复读是数据库事务隔离级别中的三种读问题。

    1. 脏读：在一个事务中读取了另一个事务未提交的数据。 mvcc+悲观锁或乐观锁
    2. 不可重复读：一个事务多次读同一数据，但是由于其他事务的修改，每次读到的数据不同。mvcc
    3. 幻读：一个事务读到了另一个事务中新插入的数据，或者读不到另一个事务中删除的数据。mvcc+next-key locks 通过提高数据库的隔离级别，比如使用更高的事务隔离级别。

  10. MySQL删除数据时的一些注意事项。

    1. 删除数据之前，先备份数据，以防止误删。
    2. 使用truncate语句比delete语句更高效，但是注意truncate语句不支持回滚。
    3. 在删除数据之前，应该评估一下该删除的数据的影响，以防止误删。
    4. 在删除数据时，可以使用LIMIT限制删除数量，防止意外删除过多的数据。
    5. 对于外键关联的表，需要考虑删除时的外键约束，防止数据不一致。
    6. 删除数据之后，应该对数据进行评估，确保删除后数据的一致性。

  11. MySQL使用时的一些经验和优化。(索引怎么用，数据量大的时候如何切分，写SQL语句的一些习惯)

    索引使用：
    使用合适的索引，能够大大加快查询的速度
    索引的字段要尽量在第一个条件中使用，否则索引就无法生效
    对于经常使用的字段和频繁查询的字段需要建立索引
    数据量大时的切分：
    在数据量很大时可以考虑数据切分，比如按时间，地域等进行切分
    切分数据能够减小单表数据量，减少数据存储的开销，提高查询的效率
    写SQL语句的一些习惯：
    使用合适的语句，如使用update instead of delete
    设置合适的语句超时时间，避免因语句执行时间过长导致数据库压力
    使用explain语句诊断语句的执行计划，避免使用不合适的语句

  12. 分库分表

    分库分表的顺序应该是先垂直分，后水平分。
    垂直切分: 拆分字段创建新表，一般是表中的字段较多，将不常用的，数据较大，长度较长（比如text类型字段）的拆分到“扩展表“。
    水平切分：表里面的数据分多张，table1、table2、table3，按照某种规则（范围,HASH取模等），切分到多张表里面去。 但是这些表还是在同一个库中，所以库级别的数据库操作还是有IO瓶颈。
    缺点：分库分表后，就成了分布式事务了。如果依赖数据库本身的分布式事务管理功能去执行事务，将付出高昂的性能代价； 如果由应用程序去协助控制，形成程序逻辑上的事务，

avatar
文章
阅读
粉丝