蹊源的Java笔记—Mysql数据库

蹊源的Java笔记—Mysql数据库

前言

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

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

正文

Mysql

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

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结构

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

• 表共享读锁：对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则保存当前版本号到原来的行作为删除标识。

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%'

查询缓存参数说明：

• 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"

type字段的值及其含义：

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

通过explain我们可以：

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

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