![c4981412523ad1d7fd3935c9610df786.png](https://img-blog.csdnimg.cn/img_convert/c4981412523ad1d7fd3935c9610df786.png)
一、MySQL基本知识
1. MySQL简介
1.1 什么是MySQL
MySQL是一个开源的关系型数据库,由MySQL AB公司开发,目前已被Oracle收购。其遵循GPL协议,用户可根据需求定制化开发资金的MySQL。MySQL可移植性高,支持多种语言,例如:Java、PHP、C++、Python、Perl、Eiffel、Rubby等。
1.2 数据库和内存之间有什么区别?
内存的读取速度较快,但是存在断电丢失,不能永久保存的缺点,数据保存在数据库类似于文件保存在硬盘,可以永久保存,安全性较高,除非硬盘损坏,否则一般来说是不会发生数据丢失的情况,但是这种方式的读取操作速度相对直接读取内存要慢一些,数据存储在数据库时,一般我们使用结构化查询语句(SQL)对数据进行增删改查相关操作。
1.3 数据库结构化查询语言
其使用标准的SQL数据语言形式,可通过如下数据语言进行使用:
- DQL:数据查询语言,select、from、where;
- DML:数据操作语言,insert、delete、update;
- DDL:数据定义语言,create、alter、drop、truncate;
- DCL:数据控制语言,grant、rollback、commit
MySQL常用的SQL说明:
![e6be6c27cd6f50a6b1154c8b00df2bcf.png](https://img-blog.csdnimg.cn/img_convert/e6be6c27cd6f50a6b1154c8b00df2bcf.png)
MySQL支持大型数据库,支持5000万条记录的数据仓库,32位系统表文件最大支持4GB,64位系统表文件最大支持8TB。在200万条记录下不加索引性能依旧较好。
注意:
字符集需要安装之后立即修改,如果插入数据之后再进行修改则之前数据库、表格及数据依然为原编码格式!
在涉及数据库时,除非有足够的理由,否则我们都需要尽量遵守数据库三大范式。
1.4 数据库三大范式
什么是范式?
范式是开发人员在数据库设计的过程中,为了保证数据库存储性能所需要遵循的一些规范,而在这些规范中,有三个需要尤为关注的,也就是数据库三大范式。
- 第一范式
数据库中的每一列是不可拆分的,保证每一列的原子性,同时如果存在某两列属性一致或相似时,需要尽量合并成一列,避免产生冗余数据。
原子性的示例:
![c91fd3932321799acf356f1086e12e16.png](https://img-blog.csdnimg.cn/img_convert/c91fd3932321799acf356f1086e12e16.png)
假如之后需求增加需要根据省市县乡对数据进行分类时,显然错误示例中的数据是不能满足要求的。
避免冗余的示例
![18cc6854c336c45c87dfe794313602f3.png](https://img-blog.csdnimg.cn/img_convert/18cc6854c336c45c87dfe794313602f3.png)
- 第二范式
在满足第一范式的前提下,非主键列完全依赖于主键,而不是依赖于主键的一部分。换成人话就是每一行数据只与其中一列相关(一般这一列定义为主键或加索引),只要数据列中出现数据重复,就要对表进行拆分。
![d85770a259cbce4dab0dfaba07fab06e.png](https://img-blog.csdnimg.cn/img_convert/d85770a259cbce4dab0dfaba07fab06e.png)
![2bbc1e9912787bb41164135687857ca6.png](https://img-blog.csdnimg.cn/img_convert/2bbc1e9912787bb41164135687857ca6.png)
- 第三范式
在第三范式的基础上,要求表中每一列都要与主键直接相关,而不是间接相关。它与第二范式的区别在于是否拆分过表,第三范式是在已拆分过表的基础上规定表中的数据必须与主键直接相关。
当然,为了保证性能,即使没有遵从三大范式也是无可厚非的,所有设计都应该着重考虑其需求与性能,而不是设计本身。
1.5 数据库的五大约束
1.主键约束(Primay Key Coustraint):唯一性,非空性;
2.唯一约束 (Unique Counstraint):唯一性,可以空,但只能有一个;
3.默认约束 (Default Counstraint): 该数据的默认值;
4.外键约束 (Foreign Key Counstraint): 需要建立两表间的关系;
5.非空约束(Not Null Counstraint):设置非空约束,该字段不能为空。
2. 安装MySQL
2.1 Windows安装
以5.7.28为例:参考博文
MySQL 5.7.28安装教程--稳blog.csdn.net![b371c0224a2d51fe9de7dff2bd986cd1.png](https://img-blog.csdnimg.cn/img_convert/b371c0224a2d51fe9de7dff2bd986cd1.png)
2.2 Linux安装
2.2.1 docker安装
VMware虚拟机下CentOS 7 使用docker安装mysqlblog.csdn.net![932c1e317df17b8bff1700f74527ef4d.png](https://img-blog.csdnimg.cn/img_convert/932c1e317df17b8bff1700f74527ef4d.png)
2.2.2 yum 安装
yum install mysql
2.2.3 rpm安装
rpm -qa|grep mysql
rpm -ivh MySQL-client-xxx.linu.xxx.rpm
rpm -ivh MySQL-server-xxx.linu.xxx.rpm
2.3 Linux下常用命令
tips:
- 查看MySQL所属用户:cat /etc/passwd/|grep mysql
- 查看MySQL所属组:cat /etc/group/|grep mysql
- 查看MySQL当前服务状态:service mysql status
- 启动关闭MySQL: service mysql start、service mysql stop、service mysql restart
- 设置MySQL开机启动:chkconfig mysql on、chkconfig --list|grep mysql、cat /etc/inittab或者netsysv图形化操作
- 设置MySQL用户名密码:mysqladmin -u root -p 123456
- 进程查看:ps -ef|grep mysql
- 设置大小写不敏感:show variables like ’%lower_case_table_names%‘,修改my.cnf下lower_case_table_names=1,即大小写不敏感。
2.4 sql_mode
sql_mode定义了对MySQL中的语法校验规则,其默认值是空值,这种情况下是可以进行一些非法操作的,生产环境下必须将该值设置为严格模式,以下为sql_mode常用的值。
![d2fe0cbe8ffda630df444973b2adca06.png](https://img-blog.csdnimg.cn/img_convert/d2fe0cbe8ffda630df444973b2adca06.png)
2.4.1 查看和修改sql_mode
查看:select @@sql_mode
修改:set @@sql_mode='';
2.5 用户管理
![e6be6c27cd6f50a6b1154c8b00df2bcf.png](https://img-blog.csdnimg.cn/img_convert/e6be6c27cd6f50a6b1154c8b00df2bcf.png)
host:表示连接类型
- %表示所有远程通过TCP连接
- IP地址
- 机器名
- ::1 ipv6本机地址
- localhost
2.6 权限管理
![93fe5336a4772446fb0f142be8606f0d.png](https://img-blog.csdnimg.cn/img_convert/93fe5336a4772446fb0f142be8606f0d.png)
2.7 权限表说明
MySQL通过其内部表来控制数据库的角色与权限,这些表主要放在mysql数据库中,由mysql_install_db脚本进行初始化,下面是这些表的相关介绍。
![df85e0dea6c8365d57207e35ce7c9346.png](https://img-blog.csdnimg.cn/img_convert/df85e0dea6c8365d57207e35ce7c9346.png)
2.8 binlog录入格式
mysql-binlog是MySQL数据库的二进制日志,用于记录用户对数据库操作的SQL语句((除了数据查询语句)信息。可以使用mysqlbin命令查看二进制日志的内容。
MySQL主要有三种binlog格式,分别是statement、row和mixed。
- statement模式下,每一条会修改数据的sql都会记录在binlog中。不需要记录每一行的变化,减少了binlog日志量,节约了IO,提高性能。由于sql的执行是有上下文的,因此在保存的时候需要保存相关的信息,同时还有一些使用了函数之类的语句无法被记录复制。
- row模式下,不记录sql语句上下文相关信息,仅保存哪条记录被修改。记录单元为每一行的改动,基本上可以记录所有操作,但是由于操作较多,会导致大量行的改动(比如alter table),因此这种模式的文件保存的信息太多,日志量太大。
- mixed模式是上述两种的折中的方案,普通操作使用statement记录,当无法使用statement的时候使用row。
二、SQL优化
一般使用SQL操作数据库时,当遇到SQL执行时间太长怎么办呢?一般我们都是从下面四个方面进行查摆问题一一优化:
- 查询语句过于粗糙
- 索引失效,明明增加了单值索引或者复合索引,但是explain查看执行计划却发现依旧是全表扫描
- 系统设计问题导致join关联查询太多
- 硬件方面需要优化,各缓冲参数设置过小(比如order by所使用的sort_buffer_size)
在优化开始之前,我们需要了解MySQL的逻辑架构是怎样的,也就是MySQL是如何处理我们的SQL语句的呢?
1. MySQL架构分析
![e66e874fe60eec728790f62350d0ff29.png](https://img-blog.csdnimg.cn/img_convert/e66e874fe60eec728790f62350d0ff29.png)
上图为MySQL逻辑架构图,其主要分为连接层、服务层、引擎层及存储层。
- 连接层,MySQL最上层,它是一些客户端和连接服务,包含本地socket通信和基于客户端连接的类似tcp/ip通信,主要完成连接处理、授权认证及相关安全方案,该层引入了线程池的概念为安全认证客户端提供线程,例如驱动连接。
- 服务层:
![b755b1a961039fc563f12ed16ef591ec.png](https://img-blog.csdnimg.cn/img_convert/b755b1a961039fc563f12ed16ef591ec.png)
- 引擎层
可插拔式引擎的选择,负责MySQL中数据的存储和查询,目前MySQL最常用的两种数据库存储引擎为InnoDB与MyISAM,下表为二者的区别。
![4346a217346f2d2e9776b8973274a358.png](https://img-blog.csdnimg.cn/img_convert/4346a217346f2d2e9776b8973274a358.png)
![711a1ba537951c14d095cd2c500f3710.png](https://img-blog.csdnimg.cn/img_convert/711a1ba537951c14d095cd2c500f3710.png)
tips:
1、聚簇索引与非聚簇索引
聚簇索引:数据与索引是在同一B+Tree上的,非叶子节点存放的是索引(数据的指针),叶子节点存放的是数据,该索引类型必须有主键,数据可通过主键进行查询,之后建立的索引为辅助索引,辅助索引需要两次查询,先查询到主键,然后通过主键查询数据,所以主键最好设置为自增,否则主键过大会带来额外开销;
非聚簇索引:B+Tree的非叶子节点和叶子节点存放的都是数据的引用地址,数据文件与索引文件是分开保存的。
2、 特别注意:InnoDB的行锁是实现在索引上的,而不是锁在物理上,如果索引失效则行锁会退化为表锁。
3、InnoDB的主键索引的叶子节点直接存储行数据,因此主键索引非常高效。
4、MyISAM索引的叶子节点存储的是数据地址,需要进行二次寻址才能获取数据。
5、InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据,因此覆盖索引会提高查询性能
关于存储引擎的选择:
一般都默认使用InnoDB,MyISAM适合以读写插入为主的应用,如博客系统,新闻网站。InnoDB适合更新操作频率并发较高的场景,支持事务与外键。
- 存储层 数据库文件的数据存储在文件系统上的方式,并通过该存储方式与存储引擎交互。
2. SQL执行分析
2.1 SQL查询流程
通过对MySQL逻辑结构的分析,我们可以知道SQL查询的流程大致为:
- MySQL客户端通过协议与MySQL服务器建立连接,发送查询语句;
- MySQL检查查询缓存,缓存如果命中则直接返回,未命中则将语句交由解析器处理;
- 解析器通过关键字对SQL语句预处理,生成解析树并验证SQL语法,若语法通过则交由优化器;
- 优化器将SQL转化成执行计划并选择最好的执行;
- 优化器将最终的查询结果返回。
SQL手写为:
SELECT DISTINCT
FROM
JOIN ON
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT
优化器优化之后的顺序:
FROM
ON JOIN
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
LIMIT
tips:
- show engines可查看所有数据库引擎
- show variables like '%storage_engine%' 可查看当前默认数据库引擎
2.2 show profile
2.2.1 SQL执行周期
通过show profile可以查看SQL的详细执行周期,可以作为优化SQL最强有力的工具。
- 通过show variables like '%profiling%'查看是否开启该功能;
![993aff693edbaa70a8e9f2f88e1d60fa.png](https://img-blog.csdnimg.cn/img_convert/993aff693edbaa70a8e9f2f88e1d60fa.png)
- set profiling = 1;开启show profile;
- show profile cpu,block io for query Query_id;查询SQL详细执行信息。
![731c33a54553132c80cf5442cfe69a54.png](https://img-blog.csdnimg.cn/img_convert/731c33a54553132c80cf5442cfe69a54.png)
show profiles可以提供SQL执行的详细信息,但是实际中我们更多使用explain对SQL进行解释优化,查看SQL是否使用索引等等,下文更多是针对explain的使用进行介绍。
3. SQL索引优化
如何提高SQL执行速度?许多人第一反应就是增加索引,什么是索引呢?如何增加索引且增加的索引不会因为SQL问题导致失效呢?
我们知道MySQL底层使用B+树实现,索引作为数据的另一种表现形式同样也会耗费存储空间的。
如果单纯的为了提升某个字段的查询效率而在全表字段上都增加索引,那么无疑给存储空间带来了巨大负担。而且索引增加之后虽然给查询带来了便利,但是在增加和删除方面,为了维护索引而带来的工作量也是极为繁重的,这说明了不经研究而随意建立索引的方法是不可取的。
3.1 索引简介
3.1.1 什么是索引?
索引是帮助MySQL高效获取数据的数据结构,所以索引可以简单理解为排好序的快速查找数据结构,索引主要是对数据进行排序和加快查找。
3.1.2 索引的优缺点
优点:
- 提高数据检索的效率,降低数据库的IO成本;
- 索引是一种排好序的数据结构,通过索引进行排序可以降低数据的排序成本。
劣势:
- 降低更新表的速度,加了索引的字段在insert、update、delete时会带来额外的开销;
- 占用多余的空间。
3.1.3 索引的类型
- 前缀索引:使用字段的前部分字符建立索引,index(field(10)),要求前缀的标识度高,使用情况不多。
- 主键索引:数据不允许重复,不允许为空且一个表只能有一个主键,可通过下面SQL添加主键:
sql alter table table_name ADD primary key(column) using BTREE;
- 唯一索引:数据不允许重复,可为空,一个表可有多个唯一索引,创建方式:
sql alter table table_name ADD UNIQUE(column); //创建唯一索引 alter table table_name ADD UNIQUE(column1,column2); //创建唯一组合索引
- 普通索引:包括了一般的单值索引和符合索引,创建方式:
sql alter table table_name ADD INDEX idx_name(column); // 单值索引 alter table table_name ADD INDEX idx_name(column1,column2) //复合索引
- 全文索引:目前搜索引擎使用的关键技术,类似elasticsearch通过词库检索,创建方式:
sql alter table table_name ADD FULLTEXT(column); //创建全文索引
3.1.4 索引的算法
目前MySQL索引使用的算法有BTree算法和Hash算法,上面已经介绍了BTree算法,Hash算法就是通过散列函数将数据分散的存入 Hash表对应位置,如果发生Hash碰撞,则通过链表连接存储。
BTree算法是MySQL默认的索引算法,可以进行范围查询,其查询复杂度由树的广度所决定。
Hash算法只有在相等操作时,查询复杂度为O(1),其它时候复杂度由数据量决定。
3.2 适合创建索引的情况
- 主键自动建立唯一索引
- 频繁作为查询条件的字段需要建立索引
- 查询中与其他表关联的字段,外键关系建立索引
- 组合索引性价比优于单值索引
- 查询中排序的字段
- 查询中统计或者分组的字段
适合索引的列是出现在where子句中的列,或连接子句中的列,索引也是数据的一种,并不是索引越多越好。
3.3 不适合创建索引的情况
- 表记录太少
- 经常增删改的表或者字段
- where条件里用不到的字段不创建索引
- 字段中重复内容较多,过滤性不好的不适合建立索引
3.4 explain性能参数分析
在日常工作中,使用最为频繁的就是通过explain+SQL模拟优化器执行SQL语句,从而知道MySQL是如何处理SQL语句的,并根据情况分析SQL的性能瓶颈以优化SQL。
那么,通过explain我们可以获得什么信息呢?
- 表的读取顺序
- 数据读取操作的操作类型
- 哪些索引可以使用
- 哪些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询
3.4.1 explain参数
我们通过explain+SQL的方式可以获得如下所示的列表:
![0661809fb7d8591eac4167355f882607.png](https://img-blog.csdnimg.cn/img_convert/0661809fb7d8591eac4167355f882607.png)
可以看出主要参数即:id,select_type,table,partitions,type,possible_keys,key,key_len,ref,rows,filtered,Extra。
以下针对第一行参数进行详细分析:
- id:select查询的序列号,包含一组数字,表示查询中select子句或操作表的顺序,主要分以下三种情况:
- id相同,执行顺序由上至下;
- id不同,如果是子查询,id的序号会较大,此时优先级越高,也就越先被执行;
- id相同不同,同时存在时先根据不同定优先级,再由上至下进行执行;
- select_type:查询的类型,主要用于区别普通查询、联合查询、子查询等的复杂查询,具体参数如下:
- SIMPLE:简单的select查询,不包含子查询或者UNION;
- PRIMARY:查询中若包含其他子查询,则最外层会被标记为PRIMARY;
- SUBQUERY:select或者where中包含了子查询;
- DERIVED:From中包含的子查询,临时表;
- UNION:第二个select出现在UNION之后则会被标记为UNION;
- UNION RESULT:从UNION中获取结果的select;
- table:显示改行的数据来源于哪张表
- partitions:是否分区
- type:优化器定义的访问类型,从最好到最差依次是scerria(system>const>ref_eq>ref>range>index>all),具体含义如下:
- system:表中只有一行记录,相当于const类型的特例,平时不会出现;
- const:通过索引一次就找到了,const用于比较primary key或者unique索引;
- eq_ref:唯一性索引,对于每个索引键,表中只有一条记录与之匹配,简单地说是const是直接按主键或唯一键读取,eq_ref用于联表查询的情况,按联表的主键或唯一键联合查询;
- ref:非唯一性索引,返回匹配某个值的所有行(一值找多行);
- range:索引给定范围的行,例如where中的between、in,这种范围索引较全表扫描要好,因为范围决定了不用全表扫描;
- index:full index,全表索引扫描,效果与all的区别就是index扫描的是全表的索引,而all扫描的是数据;
- all:全表数据扫描,匹配到之后返回结果。
一般来说,保证查询达到range或者ref效果为佳。
- possible_keys:显示可能应用在这张表中的索引,一个或多个。查询所涉及字段若存在索引,则索引将被列出,但不一定被使用
- key:查询中实际使用的索引,如果没有则为null,查询中如果使用了覆盖索引,则索引和查询的字段一一吻合
- key_len:表示索引使用到的字节数,可通过该值计算查询中使用的索引长度。在不损失精度的情况下,该值越小越好。该值为索引最大可能长度,并非实际使用长度。
- ref:显示索引哪一列被使用了,哪些列或者常数被用于索引进行查询(最好是常数)。也就是说key 列是实际使用的 index , 但 index 可能建立在数据表的若干列上。ref 列列出具体哪些列或常数被使用了。
- rows:根据表统计信息及索引使用情况大致估算除所需要读取的行数
- filtered:返回结果的行占需要读到的行(rows列的值)的百分比,因为对于join操作,前一个表的结果集大小直接影响了循环的次数
- Extra:包含不适合在其他列中显示但十分重要的额外信息,其主要会有以下几个常数:
![d01dcbeedcf6ce6c7e4f5ec71aaad19a.png](https://img-blog.csdnimg.cn/img_convert/d01dcbeedcf6ce6c7e4f5ec71aaad19a.png)
以上就是对explain参数的分析,通过对这些参数的学习,将使得我们以后在SQL优化方面无论遇到什么问题都能对症下药,迎刃而解。
3.5 索引失效情况分析
有时候我们明明在字段上建立了索引,但是explain之后发现优化器并没有使用到索引,那什么情况下会导致索引失效?写SQL语句时应该注意些什么才能避免索引失效呢?
3.5.1 索引失效
以下是常见导致索引失效的几种情况:
- 最左前缀法则:建立的复合索引在使用时没有遵循最左前缀法则,例如索引使用了(a,b,c)三个字段,但是使用的时候a没有使用到,索引失效;
- 在索引字段上进行计算,函数处理,类型转换等二次操作;
- 范围条件右边的列索引失效,即范围之后全失效,例如索引为(id,age,name),使用时如果出现where id=xx and age>10 and name=xx,则age之后索引失效;
- 尽量使用覆盖索引,避免select *,即复合索引的字段与查询字段一一吻合;
- !=或<>会导致索引失效;
- is null,is not null会导致索引失效;
- 使用like模糊查询时如果%出现在最左边则索引失效,解决办法就是使用覆盖索引;
- 字符串不加单引号;
- 多次使用or会导致索引失效。
以上就是会造成MySQL索引失效的一些情况,为了避免这些情况的出现而造成索引失效,建议:
- 对于单值索引,尽量选择针对查询过滤性更好的字段来建立索引,例如性别只有男、女、未知的三类就没有必要建立索引;
- 复合索引过滤性最好的字段要放在最前面;
- 复合索引尽可能包含查询字段中where子句中更多的字段;
- 通过分析统计信息和调整SQL查询的写法来选择建立合适的索引。
三、常见问题
1. MySQL数据库默认使用什么数据结构?为什么不使用BTree?
MySQ默认使用的引擎为InnoDB,InnoDB使用的底层数据结构是B+Tree。下面分别介绍BTree和B+Tree。
![6736d7521d0d18fedb8d590f082ba492.png](https://img-blog.csdnimg.cn/img_convert/6736d7521d0d18fedb8d590f082ba492.png)
BTree每个节点由三部分组成:key,指针以及data数据,每个叶子节点最少包含一个可以和两个指针,叶子节点的指针均为null
![c98fa68aca119adc3c09aea8e0223267.png](https://img-blog.csdnimg.cn/img_convert/c98fa68aca119adc3c09aea8e0223267.png)
B+Tree每个节点由两部分组成:key和指针,叶子节点没有指针,包含key和数据。
- 对于单索引的查询,BTree的查询速度优于B+Tree。
- B+Tree叶子节点之间相互连接,做范围查询时,只需要遍历叶子结点即可,而B树却需要反复进行树的遍历。
- 在实际应用中B+Tree中间节点不保存数据,所以磁盘页能容纳更多元素,使得树本身的广度更大,而树的广度直接影响着查询速度。
2. 数据库事务
数据库事务是对数据库一个不可分割的操作,其执行结果必须使数据库从一种一致性状态变到另一种一致性状态,即操作要么成功要么失败,不存在成功一半或者失败一半的情况。
MySQL
默认的数据库引擎InnoDB
就是支持事务操作的,在软件开发中,通常当我们使用@Transitional
来开启事务时, 需要注意数据库是否支持事务操作,否则@Transitional
是无效的。
2.1 事务特性
MySQL
数据库对事务的操作遵循以下四个特性(ACID
):
- 原子性(
Atomicity
):事务是一个不可分割的最小执行单位,其中的操作要么全部成功要么全部失败,倘若某一事务中的某一操作失败,则该事务所有操作都进行回滚; - 一致性(
Consistency
):事务使得系统从一个一致的状态转换到另一个一致状态,也就是说事务的执行不能破坏数据库设计的约束条件,例如当银行卡里面的余额为0时,就不能继续发起取钱的操作,其中根据一致性不同程度,又可以分为 - 强一致性:读操作可以立即读到提交的更新数据;
- 弱一致性:读操作不一定立即就能读到提交更新的数据,这种情况存在读操作的不一致窗口,即读操作读到最新数据的时间间隔;
- 最终一致性:保证最终都能读到事务更新的最新值;
- 单调一致性:某一进程读到更新值,那么后续不会读到更早的值;
- 会话一致性:在客户端与服务器的交互会话中,保证可以读到更新后的最新值;
- 隔离性(
Isolation
):并发访问数据库时,各事务之间的操作相互隔离,但依据隔离的程度不同,会存在以下几种情况: - 脏读:一个事务读取到了另一个事务未提交的数据;
- 幻读或虚读:一个事务在两次查询之中存在数据不一致的情况,幻读是由于并发事务增加记录导致的,不能通过加锁解决,只能将数据库隔离级别提至最高
Serialization
; - 不可重复读:同一事务对同一份数据读取到的结果不一致,可通过加锁以防止其它事务更新;
为了避免以上情况,可调整数据库关于事务的隔离级别,从低到高分别是:
- 读未提交(
Read Uncommitted
): 事务A可以读取到事务B未提交的结果,所有并发事务问题都会发生; - 读已提交(
Read Committed
): 只要事务提交后,其它事务才能读取到,可避免脏读; - 可重复读(
Repeated Read
): 在同一事务中,对于同一份数据的读取结果总是相同的,除非数据是被本身事务修改。可避免脏读和不可重复读,也是MySQL
默认的隔离级别; - 可串行化(
Serialization
):事务串行化执行,最高隔离级别,牺牲了系统的并发性。可以解决并发事务的所有问题。 - 持久性:事务提交之后,对数据库中的数据影响是持久的。
3. 锁
3.1 锁的粒度
在关系型数据库中,可按照锁的粒度将锁分为行级锁、表锁与页锁。
InnoDB支持行锁和表锁,但是需要注意,当出现索引失效的情况时,InnoDB的行锁会退化为表锁,适合并发操作。
MyISAM默认使用表锁,读取数据快,不支持事务。
- 行锁是粒度最细的一种锁,支持对当前数据行加锁,可减少数据库操作的冲突,但开销较大,可能出现死锁;
- 表锁是对整张表进行加锁,实现简单,开销较小,不会出现死锁,并发度相对较低;
- 页锁开销介于行锁和表锁之间,粒度介于二者之间,可能出现死锁;
3.2 锁的类别
按照类别可以将数据库锁分为共享锁和排他锁。
- 共享锁又叫做读锁,用于对需要读取数据的用户加锁,可同时为同一数据加多个读锁;
- 排他锁又叫做写锁,用于给需要写入数据的用户加锁,同时只能加一个写锁,也就是同时只能有一个用户修改数据;
还可以将锁大致分为乐观锁和悲观锁。
- 乐观锁:假设不一定会发生并发冲突,只在提交操作时检查是否违反数据完整性。可通过增加版本号或CAS(CompareAndSwap)来具体实现,适合读多写少的场景;
- 悲观锁:假设会发生并发冲突,在查询数据时就对数据上锁,适合写多读少的场景;
3.3 死锁
死锁是由于两个或多个事务在同一资源上相互占用,并同时请求对方资源而造成的一种僵局,如果如外力介入,则这两个事务将会一直循环。
如何解决死锁?
- 同一事务尽可能一次锁定其所需的所有资源,避免与其它事务争抢;
- 约定访问资源的顺序;
- 升级锁的粒度,行锁提升至表锁,减少冲突;
3. 大数据表的优化
单表数据量较大时,数据库的性能会明显下降,除了对SQL和索引的优化之外,还可以从以下方面进行优化:
- 限定数据范围:比如添加日期字段,查询的时候就增加日期范围,降低查询变量的blocks;
- 读写分离:搭建数据库集群,主库负责写,从库负责读,使用负载均衡降低数据库压力;
- 增加数据库的查询缓存;
- 对数据表进行垂直拆分或水平拆分;
4. 视图
视图是一种虚拟表,其数据通过对数据表进行SQL查询来动态获取,其数据来源可以是一个或多个基本表。视图的建立和删除不影响基本表,但对视图内容的更新会直接影响基本表,且当数据来自多个基本表时,不允许添加和删除数据。
- 优点
- 查询简单
- 提供了一种数据安全机制
- 缺点
- 性能受限于查询SQL
- 修改限制
5. 存储过程
存储过程是一段预编译、可复用的SQL语句。
- 优点
- 执行效率高
- 直接存储与数据库,使用方便
- 安全性较高,使用一般都需要对应权限
- 缺点
- 移植不方便
- 需要查询编译
- 维护成本高
6. 触发器
触发器是用户定义在关系表上由事件触发的特殊存储过程。
- 优点
- 实时监控表中的数据变化并及时做出相应处理;
- 缺点
- 维护成本高、迁移困难;
触发器类型:
- Before Insert
- After Insert
- Before Update
- After Update
- Before Delete
- After Delete
完!