MySQL基础

Peter2Peter2Peter
95 阅读13分钟

MySQL的重点

一、子查询

  • 非相关子查询：先执行子查询，再执行主查询。子查询独立于主查询，并且只执行一次。
  • 相关子查询：先执行主查询，再针对主查询返回的每一行数据执行子查询，如果子查询能够返回行，则这条记录就保留，否则就不保留。子查询依赖于主查询，执行多次。

二、事务隔离级别

1. 隔离级别

  • Read uncommitted  : 读未提交。会导致脏读，不可重复读，幻读。
  • Read committed: 读已提交。导致不可重复读，幻读。Oracle 默认。
  • Repeatable read：重复读。导致幻读。MySql 默认。
  • Serializable: 序列化。没有问题。
  • 隔离级别从小到大，但是效率逐步降低

2. 并发问题

  • 脏读， 一个事务读取了另外一个事务中没有提交（该事务回滚）的数据 （重点是回滚）*
  • 不可重复读（虚读）， 同一个事务中，相同的查询数据结果却不一样。 （重点是中间进行了修改）*
  • 幻读： 同一个事务，两次查询的数据记录数不一样，原因就是另外一个事务新增或者删除了第一个事务结果集里面的数据。 （重点是新增或者删除了记录）*

3. 查看和修改数据库的隔离级别

三、MySQL的顺序

图片1.png

图片2.png

四、MySQL架构

图片3.png

1. 连接层：连接处理，用户鉴权，安全管理

2. 服务层：

  • SQL界面: SQL语句
  • 解析器：语法分析，词法分析
  • 优化器：执行计划
  • 缓存：查询结果缓存，命中直接返回

3. 存储引擎层

对比项MyISAMInnoDB
主外键不支持支持
事务不支持支持
行表锁表锁（不适合高并发）行锁（适合高并发）
缓存只缓存索引，不缓存真实数据不仅缓存索引，还缓存数据，对内存要求高
表空间小大
关注点性能事务

五、索引

1. 索引定义：索引是为了帮助数据库更好地获得数据库的数据结构。优点是可以提高检索效率，降低排序成本。缺点是降低表更新的速度，并且占内存。

2. 索引分类：

  • 单值（列）索引和复合索引（一个索引包含多列）
  • 唯一索引
  • 主键索引和非主键索引
  • 聚簇索引和非聚簇索引
  1. 聚簇索引：索引和数据在一起：叶子节点存储的是数据。
  2. 非聚簇索引：索引和数据不在一起: 叶子节点存储的是数据指针。
  • 回表
  1. 聚簇索引不会回表，因为所需要的row 和叶子节点在一起。
  2. 非聚簇索引一般需要回表。但是如果索引值就是所需要的的值则不需要回表。

3. 索引原理

  • 索引结构

Hash索引（hash函数），B树，B+树

  • 区别：
  1. B树和B+树的区别：（1）B树种内部节点可以存储<k,v>, 但是B+树只有叶子节点存储<k,v>,内部节点存储的是k。（2）另外叶子节点有链接
  2. 选择B+树作为文件索引，而不是B树：（1）B树只适合随机查询，B+树适合随机和顺序。（2）B+树的磁盘读写代价更低，并且更加稳定
  3. Hash索引和B+树区别：（1）hash索引通过hash函数查询，然后回表查询；B+树则叶子节点查看是否需要回表查询（聚簇索引和非聚簇索引）（2）Hash 索引适合等值，不适合范围，没法排序；B+与之相反

4. 索引场景

  • 适合索引的场景
  1. 主键自动建立唯一索引
  2. 频繁查询条件的字段
  3. 查询中与其它表关联的字段，外键关系建立索引
  4. 单键/组合索引的选择问题，组合索引性价比更高
  5. 排序，统计或者分组的字段
  • 不适合索引的场景
  1. 记录太少
  2. 经常增删改
  3. Where 条件里用不到的字段不创建索引
  4. 过滤性不好的（比如性别）

5. MySQL索引

图片4.png

图片5.png

创建复合索引时，会将作为复合索引字段的值进行排序并放在B+树的最后一层中，同时还会将其对应的主键值放在其后。如：a b c d e  --->2 1 1 1 a。其中字段a为主键，字段bcd共同作为复合索引，此时存放在最后一层的数据就是：111（复合索引） 2（主键索引）

六、锁机制

图片6.png

MySQL 锁机制：

1. 定义：锁是计算机协调多个进程并发访问同一共享资源的一种机制。

2. 表锁与行锁

  • 定义：表锁是作用在整张表上面，行锁是作用在索引上的，哪怕你在建表的时候没有定义一个索引，InnoDB也会创建一个聚簇索引并将其作为锁作用的索引。另外MyISAM支持表锁，InnoDB支持行锁和表锁

  • 区别

n 表锁：加锁过程的开销小，加锁的速度快；不会出现死锁的情况；锁定的粒度大，发生锁冲突的几率大，并发度低。一般在执行DDL语句时会对整个表进行加锁，比如说 ALTER TABLE 等操作；如果对InnoDB的表使用行锁，被锁定字段不是主键，也没有针对它建立索引的话，那么将会锁整张表；表级锁更适合于以查询为主，并发用户少，只有少量按索引条件更新数据的应用，如Web 应用。

n 行锁：加锁过程的开销大，加锁的速度慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；最大程度的支持并发，同时也带来了最大的锁开销。在 InnoDB 中，除单个 SQL 组成的事务外，锁是逐步获得的，这就决定了在 InnoDB 中发生死锁是可能的。行级锁只在存储引擎层实现，而 MySQL 服务器层没有实现。 行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

3. MyISAM的锁机制

一般分为表共享锁（读锁）和表独占锁（写锁）。

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁。

如何显示加锁？LOCK tables XXX read local,XXX read local; XXX;Unlock tables

4. InnoDB的锁机制

  • InnoDB同时拥有表锁和行锁，但是表锁和行锁会相互冲突。当你要加表锁时，势必要先遍历该表的所有记录，判断是否有排他锁。这种遍历检查的方式显然是一种低效的方式，MySQL引入了意向锁，来检测表锁和行锁的冲突。
  • 意向锁也是表级锁，分为读意向锁（IS锁）和写意向锁（IX锁）。当事务要在记录上加上行锁时，要首先在表上加上意向锁。这样判断表中是否有记录正在加锁就很简单了，只要看下表上是否有意向锁就行了，从而就能提高效率。意向锁是 InnoDB 自动加的，不需要用户干预；对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及的数据集加上排他锁。
  • 如何加行锁？

n 共享锁（S）：select * from table_name where ... lock in share mode。此时其他 session 仍然可以查询记录，并也可以对该记录加 share mode 的共享锁。但是如果当前事务需要对该记录进行更新操作，则很有可能造成死锁。

n 排他锁（X）：select * from table_name where ... for update。其他session可以查询记录，但是不能对该记录加共享锁或排他锁，只能等待锁释放后在加锁。

n 行锁的类型

图片7.png

  • 记录锁（Record Lock）：记录锁最简单的一种行锁形式

  • 间隙锁（Gap Lock）：当我们使用范围条件而不是相等条件去检索，并请求锁时，InnoDB就会给符合条件的记录的索引项加上锁；而对于键值在条件范围内但并不存在（参考上面所说的空闲块）的记录，就叫做间隙，间隙锁。

  • 临键锁（Next-key Lock）：临键锁是记录锁与与间隙锁的结合

  • 插入意向锁（Insert Intention Lock）：插入意图锁是一种间隙锁，在行执行 INSERT 之前的插入操作设置。如果多个事务 INSERT 到同一个索引间隙之间，但没有在同一位置上插入，则不会产生任何的冲突。插入意向锁只会和 间隙或者 Next-key 锁冲突。

5. 乐观锁和悲观锁

  • 悲观锁：悲观锁是基于一种悲观的态度类来防止一切数据冲突，它是以一种预防的姿态在修改数据之前把数据锁住，然后再对数据进行读写
  • 乐观锁：乐观锁是对于数据冲突保持一种乐观态度，操作数据时不会对操作的数据进行加锁（这使得多个任务可以并行的对数据进行操作），只有到数据提交的时候才通过一种机制来验证数据是否存在冲突(一般实现方式是通过加版本号然后进行版本号的对比方式实现)

七、MySQL日志

图片8.png

八、优化器

1. 用法：EXPLAIN + SQL语句

2. 重要参数：

  • Type: system > const > eq_ref > ref(非唯一性索引扫描) > range > index > all
  • possible_key：可能用到的索引
  • key：实际使用的索引

九、SQL优化

1.  单表优化

  • 全职匹配我最爱：查询的字段按照顺序在索引中都可以匹配到
  • 最左前缀要遵守：过滤条件要使用索引必须按照索引建立时的顺序，依次满足
  • 带头大哥不能死：同上
  • 中间兄弟不能断：同上
  • 索引列上少计算：等号左边无计算
  • 范围之后全失效：使用范围查询后，如果范围内的记录过多，会导致索引失效
  • LIKE 百分写最右：模糊索引
  • 覆盖索引不写*
  • 不等空值还OR： 慎用NULL 和OR
  • 索引影响要注意
  • VARCHAR 引号不可丢: 特别是数字字符
  • SQL优化有诀窍

2.  多表优化

  • left join 时，左侧的为驱动表，加索引， right join 与之相反
  • inner join 时，mysql 会把小结果集的表选为驱动表（小表驱动大表），所以最好把索引建立在大表（数据较多的表）上

3.  排序分组优化

  • 要想在排序时使用索引（通过索引寻找），避免 Using filesort (文件排序）
  • 首先需要发生索引覆盖
  • 其次ORDER BY 后面字段的顺序要和复合索引的顺序完全一致
  • ORDER BY 后面的索引必须按照顺序出现，排在后面的可以不出现
  • 要进行升序或者降序时，字段的排序顺序必须一致。不能一部分升序，一部分降序，可以都升序或者都降序
  • 如果复合索引前面的字段作为常量出现在过滤条件中，排序字段可以为紧跟其后的字段

十、数据库优化

  • 优化shema、去除冗余字段
  • 优化sql语句，语法分析器
  • 添加索引
  • 添加缓存，memcached, redis
  • 主从复制，读写分离
  • 垂直拆分，根据你模块的耦合度，将一个大的系统分为多个小的系统
  • 水平切分，针对数量大的表，这一步最麻烦。

十一、分库分表

  • 垂直分表（单库多表）： 可以把一个宽表的字段按访问频次、是否是大字段的原则拆分为多个表，这样既能使业务清晰，还能提升部分性能。拆分后，尽量从业务角度避免联查，否则性能方面将得不偿失。将一张复杂的表分成用户表，和订单表，数据仍然在一台机器上。
  • 垂直分库（多库单表）：可以把多个表按业务耦合松紧归类，分别存放在不同的库，这些库可以分布在不同服务器，从而使访问压力被多服务器负载，大大提升性能，同时能提高整体架构的业务清晰度，不同的业务库可根据自身情况定制优化方案。但是它需要解决跨库带来的所有复杂问题。用户表和订单表在不同的机器上。
  • 水平分库（多库多表）：可以把一个表的数据(按数据行)分到多个不同的库，每个库只有这个表的部分数据，这些库可以分布在不同服务器，从而使访问压力被多服务器负载，大大提升性能。它不仅需要解决跨库带来的所有复杂问题，还要解决数据路由的问题(数据路由问题后边介绍)。单号订单为一台机器，双号为另一台机器
  • 水平分表：在同一个数据库内，把同一个表的数据按一定规则拆到多个表中。库内的水平分表，解决了单一表数据量过大的问题，分出来的小表中只包含一部分数据，从而使得单个表的数据量变小，提高检索性能。 （单号用户表， 用户单号北京地区，用户单号上海地区，用户单号广东地区）
  • 一般来说，在系统设计阶段就应该根据业务耦合松紧来确定垂直分库，垂直分表方案，在数据量及访问压力不是特别大的情况，首先考虑缓存、读写分离、索引技术等方案。若数据量极大，且持续增长，再考虑水平分库水平分表方案。

十二、主从复制

1、主从复制

主要涉及三个线程：binlog 线程、I/O 线程和 SQL 线程。

binlog 线程 ：负责将主服务器上的数据更改写入二进制日志（Binary log）中。

I/O 线程 ：负责从主服务器上读取二进制日志，并写入从服务器的中继日志（Relay log）。

SQL 线程 ：负责读取中继日志，解析出主服务器已经执行的数据更改并在从服务器中重放（Replay）。

图片9.png

2、读写分离

主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作

读写分离能提高性能的原因在于：

主从服务器负责各自的读和写，极大程度缓解了锁的争用

从服务器可以使用 MyISAM，提升查询性能以及节约系统开销

增加冗余，提高可用性

读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器

图片10.png

avatar
文章
阅读
粉丝