蹊源的Java笔记—Mysql数据库

前言

前段时间我们花了比较多的时间对​​Spring​​​相关的知识点进行了整理，本期博客将带领各位同学来了解一下​​Mysql​​数据库的必备知识点。

Spring可参考我的博客​​蹊源的Java笔记—Spring​​Redis服务器可参考我的博客蹊源的Java笔记—Redis服务器

正文

Mysql

Mysql是我们最常用的关系性数据库。

Mysql的逻辑结构

组件：客户端、核心服务、存储引擎

蹊源的Java笔记—Mysql数据库_蹊源

Mysql查询的5个过程过程：

  1. 客户端向​​MySQL​​服务器发送一条查询请求
  2. 服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段
  3. 服务器进行​​SQL​​解析、预处理、再由优化器生成对应的执行计划
  4. ​​MySQL​​​根据执行计划，调用存储引擎的​​API​​来执行查询
  5. 将结果返回给客户端，同时缓存查询结果

Mysql的初始化
Mysql的初始化是通过加载 ​​/etc/my.cnf​​文件完成初始化加载。

MySql的物理模块

  • 日志文件：​​undo.log​​​、​​redo.log​​​、​​binlog​​
  • 数据文件 ： 通常存储在​​mysql/data​​ 目录
  • 其他文件：如​​mysql.sock​​ 这个文件是用来服务器与客户端进行套接字连接的文件

binlog：​​binlog​​​是二进制日志文件,用于记录​​mysql​​​的数据更新或者潜在更新，​​mysql​​​主从复制中就是依靠的​​binlog​​。

undo.log: ​​undo​​​日志用于存放数据修改被修改前的值，如果这个修改出现异常，可以使用​​undo​​日志来实现回滚操作，保证事务的原子性。

redo.log: 用于记录 数据修改后的记录，顺序记录 ,可以使用​​redo​​实现重做操作，保证事务的持久性。

Mysql存储过程
存储过程指的是，一组为了完成特定功能的 ​​​SQL​​ 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次 编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

Mysql触发器
触发器是一段能自动执行的程序，是一种特殊的存储过程，触发器和普通的存储过程的区别是： 触发器是当对某一个表进行操作时触发。诸如：​​​update​​​、​​insert​​​、​​delete​​ 这些操作的时候，系统 会自动调用执行该表上对应的触发器。

Mysql表结构设计原则

  1. 通常来说把可为​​NULL​​的列改为​​NOT NULL​​不会对性能提升有多少帮助，只是如果计划在列上创建索引，就应该将该列设置为​​NOT NULL​​。
  2. 对整数类型指定宽度，并不会影响存储。​​INT​​使用32位（4个字节）存储空间，那么它的表示范围已经确定，所以​​INT(1)​​和​​INT(3)​​对于存储和计算是相同的，​​INT(3)​​只是在存储的过程中比如10，会存在成010，但计算是一样的。
  3. 通常来讲，没有太大的必要使用​​DECIMAL​​数据类型。即使是在需要存储财务数据时，仍然可以使用​​BIGINT​​。比如需要精确到万分之一，那么可以将数据乘以一百万然后使用​​BIGINT​​存储。
  4. ​​timestamp​​​使用4个字节存储空间，​​datetime​​使用8个字节存储空间。但是​​timestamp​​局限性很大。

Varchar(100) 可以存取多少汉字或者英文字符
具体还是要看版本的，一个字符占用3个字节 ，一个汉字（包括数字）占用3个字节=一个字符

  • 4.0版本以下，​​varchar(100)​​，指的是100字节，如果存放UTF8汉字时，只能存33个（每个汉字3字节）
  • 5.0版本以上，​​varchar(100)​​​，指的是100字符，无论存放的是数字、字母还是​​UTF8​​​汉字（每个汉字3字节），都可以存放100个。
    ​​​UTF8​​​编码中一个汉字（包括数字）占用3个字节 英文占1个字节
    ​​​GBK​​编码中一个汉字（包括数字）占用2个字节 英文占1个字节

单行可定义的最大长度
​​​MySQL​​​要求一个行的定义长度不能超过65535个字节，
所以​​​UTF8​​​一个​​Varchar​​最大长度不能超过 21845个字符 （65535/3）

Mysql存储引擎

常见的有以下四种：

  • InnoDB:唯一支持外键，支持事务，用于大规模活跃数据查询 (行锁)
  • MyISAM:不支持外键和事务，支持数据压缩,强调数据快速读取，用于冷数据查询。（表锁）
  • Memory:默认使用​​HASH​​索引，数据存储在内存中，读取速度快。（表锁）
  • Archive:存储引擎使用行锁来实现高并发插入操作，但是它不支持事务，其设计目标只是提供高速的插入和压缩功能。(行锁)

InnoDB主键和MyISAM主键之间的区别

  • InnoDB主键是聚集索引，​​InnoDB​​​的​​B+​​​树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值，所以​​InnoDB​​会有回表操作。
  • MyISAM主键是非聚集索引，​​MyISAM​​​的​​B+​​​树主键索引和辅助索引的叶子节点都是数据文件的地址指针。​​MyISAM​​不会有回表操作。

数据存储方式的差异
​​​Innodb​​​存储文件有​​frm​​​、​​ibd​​​，而​​MyIsam​​​是​​frm​​​、​​MYD​​​、​​MYI​​

  • Innodb的存储：​​frm​​​是表定义文件，​​ibd​​是数据文件（索引和数据是在一起的）
  • MyISAM的存储：​​frm​​​是表定义文件，​​myd​​​是数据文件，​​myi​​是索引文件（索引和数据文件是分离）

MyISAM结构

蹊源的Java笔记—Mysql数据库_数据库_02

表级锁
不会出现死锁，发生锁冲突几率高，并发低（不同表的读取会阻塞，写操作会阻塞其他操作）。
场景：适用那些更新数据不频繁的情况。
表级锁的两种模式： 表共享读锁、表独占写锁

  • 表共享读锁：对​​MyISAM​​表的读操作，不会阻塞其它进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。
  • 表独占写锁：对​​MyISAM​​表的写操作，会阻塞其它进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。

行级锁
行级锁，会出现死锁，发生锁冲突几率低，并发高。
实现原理： 通过给索引上的索引项加锁来实现的，当索引失效的会导致行级锁升级成表级锁。
场景：当数据会增、删、改的情况下，为了保证数据一致性，需要加上排它锁。（​​​mvcc​​使得这些操作不会影响）

  • 行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了。（行级锁是借助索引来实现的，所以建立索引能避免升级成表级锁，进而提升性能 ）
  • 两个事务不能锁同一个索引。
  • ​​insert​​​，​​delete​​​，​​update​​​在事务中都会自动默认加上排它锁。（也可以选择手段去添加 ​​sql​​​后 ​​for update​​）

页级锁
​​​mysql5.1​​​之前​​BDB​​​引擎支持页级锁。
开销和加锁时间介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般

间隙锁（串行化使用它预防幻读）
当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，​​​InnoDB​​​会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内并不存在的记录，叫做间隙
​​​InnoDB​​也会对这个"间隙"加锁，这种锁机制就是所谓的间隙锁

-- 用户A update user set count=8 where id>2 and id<6`
-- 用户B update user set count=10 where id=5;

​​id​​​不在 ​​（id>2 and id <6​​）,但是同样会被A阻塞，只有当A提交后 ，B的更新操作会执行。

mvcc多版本并发控制

  • mvcc是一种行级锁的变种，它通过多版本的方式实现了非阻塞的读操作（修改、删除、新增时不会加入排他锁）。
  • mvcc是适用隔离级别为 读未提交和可重复读两种隔离级别

select操作：

  1. ​​InnoDB​​只查找版本早于（包含等于）当前事务版本的数据行。可以确保事务读取的行，要么是事务开始前就已存在，或者事务自身插入或修改的记录。
  2. 行的删除版本要么未定义，要么大于当前事务版本号。可以确保事务读取的行，在事务开始之前未删除。

insert操作：将新插入的行保存当前版本号为行版本号。

delete操作：将删除的行保存当前版本号为删除标识。

update操作：变为​​insert​​​和​​delete​​​操作的组合，​​insert​​​的行保存当前版本号为行版本号，​​delete​​则保存当前版本号到原来的行作为删除标识。

蹊源的Java笔记—Mysql数据库_蹊源_03


repeatable-read 可重复读的实现依赖于​​mvcc​​​机制。（在一个事务中的两次​​select​​​中 穿插另一个事务一次​​update​​，两次结果是一致的）

​​mvcc​​为读未提交提供语句级别的快照（历史版本），为可重复读提供事务级别的快照（历史版本）。

​​mvcc​​的实现是通过每行记录中隐藏的创建时间和删除时间来实现的。（这里的时间存储的是系统版本号）

乐观锁和悲观锁
乐观并发控制(乐观锁)和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。
乐观锁和悲观锁都是一种技术手段，而不是锁的种类。

对于mysql数据库：

  • 实现乐观锁的方式：通过​​version​​字段来实现。
  • 实现悲观锁的方式：​​for update​​ 排他锁来实现。

数据库索引

索引的类别

  • PRIMARY KEY： 主键索引，这意味着索引值必须是唯一的，且不能为​​NULL​​。
  • UNIQUE:唯一索引，创建索引的值必须是唯一的，但是NULL可能会出现多次。
  • INDEX: 普通索引，索引值可出现多次。
  • FULLTEXT: 全文索引，其底层实现时倒排索引。

知识点：
1.普通索引和唯一索引的区别：

  • 查找时:普通索引查到符合条件的项后会继续查找下一项，如果下一项不符合再返回；唯一索引查到符合条件的项后之间返回。
  • 更新时：普通索引找到位置直接更新，先查找是否唯一，再找到位置然后再更新。
  • 唯一索引为了确保其唯一性需要加载所有数据页到内存，所以普通索引的性能是优于主键索引的。

在实际的场景中尽可能少用唯一索引，多使用普通索引。

change buffer
当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致的情况下。​​​InnoDB​​​会将这些更新操作先记录在​​change buffer​​​，当进行​​merge​​​操作时会将​​change buffer​​​中的操作记录应用到对应的数据页中，这种方式减少了​​IO​​操作，提示了性能。

merge触发条件：

  • 访问对应原数据页，会触发​​merge​​​，将 ​​change buffer​​的操作记录应用到对应的数据页中
  • 后台线程会定时执行​​merge​​操作

使用change buffer的注意点：

  • 普通索引有​​change buffer​​​而唯一索引没有，因为​​change buffer​​无法保证唯一性。
  • ​​change buffer​​​适用于写多读少的场景，可以减少​​IO​​操作。
  • ​​InnoDB​​都是以页为单位将数据读入内存，通常一个数据页可能有上千条记录。

使用索引的注意事项

  1. 表的主键和外键必须有索引
  2. 经常出现​​where​​字句的字段
  3. 经常要查询的列
  4. 选择性高的字段
  5. 经常用户排序的字段
  6. 数据超过300以上
  7. 表的索引最好不要超过5个
  8. 存在索引的字段不要进行函数操作

索引失效的情况：

  1. 对于组合索引，存在“左原则”，如果筛选条件没有​​name​​​，那么只要​​city​​是不生效的
  2. 使用​​like​​查询，使用 前导模糊查询，如‘%aa’或‘%aa%’,索引是无效的
  3. 条件中有​​or​​，部分字段没有建立索引时，索引是无效的
  4. 数据库中的数据表数据过少
  5. 如果列类型是字符串，条件要用 ''包围，否则无效
  6. 使用​​is null​​​或者 ​​is not null​​时，索引无效
  7. 显式转换、隐式转换、函数操作
  8. ​​not in​​​ 索引不生效，​​in​​ 取值范围小索引是生效的，取值范围比较大时索引失效。（也是数据的量引起的）
  9. 当匹配的数据超过总数据的30%左右，索引将失效（重复的数据过多）

回表查询

  • 聚集索引：通常为主键索引（聚集索引，物理地址也是连续的）
  • 普通索引：其他索引（一个表最多有249个普通索引，索引需要大量的硬盘空间和内存）
select * from t where name='lisi';

（1）先通过普通索引定位到主键值（即​​id​​​）；
（2）在通过聚集索引（即​​​id​​）定位到行记录；

回表查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低。

联合索引：遵循左原则

  • 索引本质是一棵​​B+Tree​​，联合索引(col1, col2,col3)也是。
  • 其非叶子节点存储的是第一个关键字的索引（只有先定位到其非叶子节点才能进一步定位到叶子节点），而叶节点存储的则是三个关键字col1、col2、col3三个关键字的数据，且按照col1、col2、col3的顺序进行排序。
alert table test add INDEX `sindex` (`a`,`b`,`c`)
select a,b,c from test where a=1 and b = 1 #索引生效
select a,b,c from test where b=1 and c = 1 #索引失效

覆盖索引

覆盖索引，只用从索引中就能够取得，不必读取数据行，我们通过普通索引定位到主键值的时候就可以 引叶子节点存储了对应的联合索引的字段或者主键字段，所以不需要进行回表操作。

覆盖索引的有效的两种情况：

  • 查询条件使用的是普通索引，查询结果是主键。
  • 查询条件满足联合索引，查询结果是对应联合索引的字段（可以包含主键）
alert table test add INDEX `sindex` (`a`,`b`,`c`)
#索引生效,并且覆盖索引生效
select id,a,b,c from test where a=1

通常使用覆盖索引可以优化​​limit​​分页查询。

索引下推
对​​​select * from table where name like 'hello%’and age >10​​ 检索:

MySQL5.6版本之前，会对匹配的数据进行回表查询。
MySQL5.6版本之后会：先过滤掉​​age<10​​的数据，再进行回表查询，减少回表率，提升检索速度。

B+树索引和哈希索引

  • ​​InnoDB​​​和​​MyISAM​​​默认使用B+树进行索引,​​Memory​​​默认使用​​Hash​​进行索引
  • B+树索引：底层是平衡二叉树+有序数据链表（B+树的所有的叶子节点都带有指向下一个节点的指针，形成一个有序链表）组成，可以使用二分法检索数据，并且保证全局有序，可以支持范围查找。
  • 哈希索引：通过​​Hash​​​函数，计算​​Hash​​值，在表中找到对应的数据。

B+树索引和哈希索引的比较：

  • 如果是等值查询，哈希索引有绝对优势
  • ​​Hash​​不支持范围查找
  • 哈希索引不支持排序，和模糊查询
  • 存在大量重复键，由于存在哈希碰撞问题，哈希索引的性能比较低

索引为什么使用B+树，不使用红黑树或B树

  • B+树相对与B树，它的所有数据都保存在叶子节点中，相同的磁盘页可以容纳更多的节点元素，进而可以减少​​IO​​操作。
  • 所有查询都是查找到叶子节点，查询性能稳定。
  • 所有叶子节点会形成有序链表，便于范围检索。

查询缓存

1.尽量使用小表而不使用大表
2.启动缓存的情况下 写入数据尽可能一次写入 重复写入会导致服务器崩溃
3.尽量不要在数据库或者表的基础上使用查询缓存 只针对一些查询语句进行缓存

  • ​​query_cache_type=DEMAND​​
  • 在进行缓存的SQL语句 加上​​SQL_CACHE​​​ 其他加上​​SQL_NO_CACHE​​ 查看查询缓存的相关配置
show variables like '%query_cache%'

蹊源的Java笔记—Mysql数据库_数据库_04


查询缓存参数说明：

  • have_query_cache：当前的 ​​MySQL​​ 版本是否支持“查询缓存”功能。
  • query_cache_limit：​​MySQL​​ 能够缓存的最大查询结果，查询结果大于该值时不会被缓存。默认值是 1048576(1MB)。
  • query_cache_min_res_unit：查询缓存分配的最小块（字节）。默认值是 4096（4KB）。
  • query_cache_size：为缓存查询结果分配的总内存。
  • query_cache_type：默认为 ​​on​​​，可以缓存除了以 ​​select sql_no_cache​​ 开头的所有查询结果。
  • query_cache_wlock_invalidate：如果该表被锁住，是否返回缓存中的数据，默认是关闭的。

​​mysql​​​是默认开启查询缓存的，​​sql​​加上这句不启用查询缓存：

  • 一个更新频率非常低而只读查询频率非常高的场景下，打开查询缓存
  • 对于频繁更新的数据库，建议关闭查询缓存
select * from tb_user SQL_NO_CACHE

分表分区

水平分表

  • 将一张表拆分成多张表，每张表的结构是一样（使用的是同一个​​FRM​​​文件，但每一子表存放在在同一个磁盘中一个独立​​MYD​​​文件和一个独立的​​MYI​​​文件，​​MRG​​文件记录分表信息）
  • 利用主表作为查询的接口（没有数据文件），表一表二作为存储数据的实际表单
  • 决定数据放在那一张实际的表，往往采用对​​ID​​​取模（即求余）或者对业务主键​​hash​​运算，确保有关联性的数据在同一个子表中。
  • ​​merge​​来分表，是最简单的一种方式

以对​​ID​​取模的方式举例：

向表一插入数据：insert into tb_member1(id,name,sex) select id,name,sex from member where id%2=0; //这里区分表一表二
向表二插入数据：insert into tb_member2(id,name,sex) select id,name,sex from member where id%2=1;
查看一下主表的数据：select * from tb_member;

水平分区

  • 分区一张表的数据分成N多个区块，这些区块可以在同一个磁盘上，也可以在不同的磁盘上，但实际上还是同一张表，只是把​​MYD​​​、​​MYI​​​同时切分了很多份，​​Par​​文件会记录分区信息；
  • 分区比较容易实现​​partition by​​​，水平分区支持​​range​​​分区、​​list​​​分区、​​hash​​分区等方式
  • 采用那种方式进行分区取决于业务，比如以权益中心为例：通常来说我们希望同一个用户的数据会存在同一个物理表中，可以采用以手机号进行​​hash​​的方式进行分区
向表插入数据：insert into member(id,name,sex) values (1,’luo’,’男’)；
查看一下主表的数据：select * from tb_member;

分表分库
分表分库是一种分区+分表结合起来的方式：

  • 原本存储于一个库的数据分块存储到多个库上；
  • 把原本存储于一个表的数据分块存储到多个表上；
  • 分库可以解决单台服务器性能不够，或者成本过高问题；
  • 分表分库可以是水平分表分库，也可以是垂直分表分库；

分表分库常见的问题
跨库join
当数据分到不同的库上，一般是禁止跨库​​​join​​的，一般会采用以下方式：

  • 全局表：所谓全局表，就是有可能系统中所有模块都可能会依赖到的一些表。比较类似我们理解的“数据字典”。为了避免跨库​​join​​查询，我们可以将这类表在其他每个数据库中均保存一份
  • 字段冗余：比如“订单表”中保存“卖家Id”的同时，将卖家的“​​Name​​”字段也冗余，这样查询订单详情的时候就不需要再去查询“卖家用户表”。
  • 数据同步：使用​​ETL​​工具(数据迁移)做表数据同步，定时A库中的tab_a表和B库中tbl_b有关联，可以定时将指定的表做同步。

跨库事务

由于数据存储到了不同的库上，数据库事务管理出现了困难。如果依赖数据库本身的分布式事务管理功能去执行事务，将付出高昂的性能代价；所以实际上我们只会对历史数据进行分表分库拆分。

优化Mysql的其他建议：

  • 使用​​join​​来代替子查询
  • 使用联合(​​UNION​​)来代替手动创建的临时表
  • 添加一定的冗余字段，减少频繁的​​join​​联表查询(在订单表中，‘客户名称’字段就是冗余字段，加了这个字段，就需要在客户信息表修改（客户名称改变）的时候，多做一个更新订单表中‘客户名称’字段的动作。)
  • 使用外键：锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联，必要情况下可以舍弃外键提升性能。
  • 使用​​explain​​关键字来优化查询语句和表结构

explain的扩展

查询用户表

EXPLAIN SELECT * from tb_user WHERE id ="1"

蹊源的Java笔记—Mysql数据库_数据库_05

type字段的值及其含义：

  • all：全表扫描
  • index：按照索引的顺序进行全表扫描
  • range： ​​range​​​指的是有范围的索引扫描，相对于​​index​​​的全索引扫描，它有范围限制，因此要优于​​index​​​。出现了​​range​​，则一定是基于索引的。
  • ref： 查找条件列使用了索引而且不为主键和​​unique​​，存在重复值。
  • ref_eq： 使用了主键或者唯一性索引进行查找的情况。
  • const： 将一个主键放置到​​where​​​后面作为条件查询，​​mysql​​优化器就能把这次查询优化转化为一个常量。

通过explain我们可以：

  1. 表的读取顺序
  2. 数据读取操作的操作类型
  3. 哪些索引可以使用
  4. 哪些索引被实际使用
  5. 表之间的引用
  6. 每张表有多少行被优化器查询

通过3、4我们就可以知道哪些索引可能存在失效的情况。

蹊源的Java笔记—Mysql数据库_mysql优化_06