知识整理-MySQL篇

二俊
208 阅读8分钟

一、MySQL

1. MySQL索引

  1. 什么是索引

    高效获取数据的数据结构，相当于书的目录，使用B+树结构，索引是存储在磁盘文件中的（可能单独的索引文件中，也可能和数据一起存储在数据文件中）

  2. 索引的分类

    1. 单列索引

    • 普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数 据更快一点。

    • 唯一索引：索引列中的值必须是唯一的，但是允许为空值。

    • 主键索引：是一种特殊的唯一索引，不允许有空值

    1. 组合索引

    • 在表中的多个字段组合上创建的一个索引
    • 组合索引的使用，需要遵循最左前缀原则（最左匹配原则）。

  3. 索引的优缺点

    优点：快速数据的查询速度

    缺点：空间换时间，索引也需要占空间

    ​ 创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加

2. MySQL存储引擎
存储引擎优点缺点
InnoDB5.5版本后MySQL默认数据库，支持事务，比MyISAM处理速度稍慢非常复杂，性能较一些简单的引擎要差一点儿。空间占用比较多。
MyISAM高速引擎，拥有极高的插入，查询速度不支持事务，不支持行锁、崩溃后数据不容易修复
Memory内存存储引擎，拥有极高的插入，更新和查询效率占用和数据量成正比的内存空间，只在内存上保存数据，意味着数据可能会丢失

最常用的是InnoDB和MyISAM，InnoDB和MyISAM存储引擎区别

类别InnoDBMyISAM
存储文件.frm 表定义文件
.idb 数据文件和索引文件		.frm 表定义文件
.myd 数据文件
.myi 索引文件
锁表锁、行锁表锁
事务支持不支持
索引结构B+ Tree（聚簇索引，叶子节点存储主键，所以有回表操作）B+ Tree（非聚簇索引，叶子结点存储地址的指针）
3. 并发事务带来的问题？
  • 脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
  • 丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。 例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。
  • 不可重复读（Unrepeatableread）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
  • 幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。
4. 事务的隔离级别
  • READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
  • READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
  • REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
  • SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。

5. 事务的四大特性

事务是逻辑上的一组操作，要么都执行，要么都不执行。

  1. 原子性（Atomicity）： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
  2. 一致性（Consistency）： 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
  3. 隔离性（Isolation）： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
  4. 持久性（Durability）： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。
6. 大表优化
  1. 限定数据的范围
  2. 读/写分离（主库负责写，从库负责读；）
  3. 垂直分区（数据表列的拆分，把一张列比较多的表拆分为多张表。）
  4. 水平分区（保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，）
7. 分库分表之后,id 主键如何处理？
  1. UUID：不适合作为主键，因为太长了，并且无序不可读，查询效率低。比较适合用于生成唯一的名字的标示比如文件的名字。
  2. 数据库自增 id : 两台数据库分别设置不同步长，生成不重复ID的策略来实现高可用。这种方式生成的 id 有序，但是需要独立部署数据库实例，成本高，还会有性能瓶颈。
  3. 利用 redis 生成 id : 性能比较好，灵活方便，不依赖于数据库。但是，引入了新的组件造成系统更加复杂，可用性降低，编码更加复杂，增加了系统成本。
  4. Twitter的snowflake算法 ：Github 地址：github.com/twitter-arc…
  5. 美团的Leaf分布式ID生成系统 ：Leaf 是美团开源的分布式ID生成器，能保证全局唯一性、趋势递增、单调递增、信息安全，里面也提到了几种分布式方案的对比
8. 一条SQL语句在MySQL中如何执行的
  1. MySQL 主要分为 Server 层和引擎层，Server 层主要包括连接器、查询缓存、分析器、优化器、执行器，同时还有一个日志模块（binlog），这个日志模块所有执行引擎都可以共用,redolog 只有 InnoDB 有。
  2. 引擎层是插件式的，目前主要包括，MyISAM,InnoDB,Memory 等。
  3. SQL 等执行过程分为两类，一类对于查询等过程如下：权限校验---》查询缓存---》分析器---》优化器---》权限校验---》执行器---》引擎
  4. 对于更新等语句执行流程如下：分析器----》权限校验----》执行器---》引擎---redo log prepare---》binlog---》redo log commit
9. MySQL高性能优化
  1. 数据库设计规范
    1. 所有表必须使用 Innodb 存储引擎
    2. 数据库和表的字符集统一使用 UTF8，避免转化造成索引失效（emoji 表情的需要，字符集需要采用 utf8mb4 字符集。）
    3. 尽量控制单表数据量的大小,建议控制在 500 万以内。
    4. 谨慎使用 MySQL 分区表
    5. 禁止在数据库中存储图片,文件等大的二进制数据
    6. 禁止在线上做数据库压力测试
  2. 数据库字段设计规范
    1. 优先选择符合存储需要的最小的数据类型
    2. 避免使用 TEXT,BLOB 数据类型，最常见的 TEXT 类型可以存储 64k 的数据
    3. 尽可能把所有列定义为 NOT NULL
    4. 同财务相关的金额类数据必须使用 decimal 类型
  3. 索引设计规范
    1. 限制每张表上的索引数量
    2. 禁止给表中的每一列都建立单独的索引
    3. 每个 Innodb 表必须有个主键
    4. 常见索引列建议（出现在 SELECT、多表 join 的关联列）
    5. 科学选择索引列的顺序
      1. 区分度最高的放在联合索引的最左侧
      2. 尽量把字段长度小的列放在联合索引的最左侧
      3. 使用最频繁的列放到联合索引的左侧
  4. 数据库开发规范
    1. 避免数据类型的隐式转换
    2. 充分利用表上已经存在的索引
    3. 禁止使用 SELECT * 必须使用 SELECT <字段列表> 查询
    4. 避免使用子查询，可以把子查询优化为 join 操作
    5. 避免使用 JOIN 关联太多的表
    6. 拆分复杂的大 SQL 为多个小 SQL
  5. Explain优化SQL语句
avatar
文章
阅读
粉丝