文章目录1.事务1.1

事务是一组操作的集合事务会把所有操作作为一个整体一起向系统提交或撤销操作请求即这些操作要么同时成功要么同时失败。

基本操作

设置事务提交方式1为自动提交0为手动提交该设置只对当前会话有效

SET

原子性(Atomicity)事务是不可分割的最小操作但愿要么全部成功要么全部失败一致性(Consistency)事务完成时必须使所有数据都保持一致状态隔离性(Isolation)数据库系统提供的隔离机制保证事务在不受外部并发操作影响的独立环境下运行持久性(Durability)事务一旦提交或回滚它对数据库中的数据的改变就是永久的

1.2

问题描述脏读一个事务读到另一个事务还没提交的数据不可重复读一个事务先后读取同一条记录但两次读取的数据不同幻读一个事务按照条件查询数据时没有对应的数据行但是再插入数据时又发现这行数据已经存在

并发事务隔离级别

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。

存储引擎是基于表而不是基于库的所以存储引擎也可以被称为表引擎。

相关操作

模型支持事务行级锁提高并发访问性能支持外键约束保证数据的完整性和正确性

xxx.ibd:

引擎的每张表都会对应这样一个表空间文件存储该表的表结构frm、sdi、数据和索引。

参数innodb_file_per_table决定多张表共享一个表空间还是每张表对应一个表空间

知识点

引擎的表数据是存储在内存中的受硬件问题、断电问题的影响只能将这些表作为临时表或缓存使用。

存放在内存中速度快hash索引默认

特点InnoDBMyISAMMemory存储限制64TB有有事务安全支持--锁机制行锁表锁表锁Btree索引支持支持支持Hash索引--支持全文索引支持5.6版本之后支持-空间使用高低N/A内存使用高低中等批量插入速度低高高支持外键支持--

2.5

在选择存储引擎时应该根据应用系统的特点选择合适的存储引擎。

对于复杂的应用系统还可以根据实际情况选择多种存储引擎进行组合。

InnoDB:

如果应用对事物的完整性有比较高的要求在并发条件下要求数据的一致性数据操作除了插入和查询之外还包含很多的更新、删除操作则

InnoDB

如果应用是以读操作和插入操作为主只有很少的更新和删除操作并且对事务的完整性、并发性要求不高那这个存储引擎是非常合适的。

Memory:

将所有数据保存在内存中访问速度快通常用于临时表及缓存。

Memory

的缺陷是对表的大小有限制太大的表无法缓存在内存中而且无法保障数据的安全性

MyISAM

慢查询日志记录了所有执行时间超过指定参数long_query_time单位秒默认10秒的所有SQL语句的日志。

MySQL的慢查询日志默认没有开启需要在MySQL的配置文件/etc/my.cnf中配置如下信息

开启慢查询日志开关

设置慢查询日志的时间为2秒SQL语句执行时间超过2秒就会视为慢查询记录慢查询日志

long_query_time2

更改后记得重启MySQL服务日志文件位置/var/lib/mysql/localhost-slow.log

查看慢查询日志开关状态

默认关闭可以通过set语句在session/global级别开启

profiling

子句或者操作表的顺序id相同执行顺序从上到下id不同值越大越先执行select_type表示

SELECT

SIMPLE简单表即不适用表连接或者子查询、PRIMARY主查询即外层的查询、UNIONUNION中的第二个或者后面的查询语句、

SUBQUERYSELECT/WHERE之后包含了子查询等type表示连接类型性能由好到差的连接类型为

NULL、system、const、eq_ref、ref、range、index、allpossible_key可能应用在这张表上的索引一个或多个Key实际使用的索引如果为

NULL则没有使用索引Key_len表示索引中使用的字节数该值为索引字段最大可能长度并非实际使用长度在不损失精确性的前提下长度越短越好rowsMySQL认为必须要执行的行数在InnoDB引擎的表中是一个估计值可能并不总是准确的filtered表示返回结果的行数占需读取行数的百分比filtered的值越大越好

4.索引

高效获取数据的数据结构有序。

在数据之外数据库系统还维护着满足特定查找算法的数据结构这些数据结构以某种方式引用指向数据这样就可以在这些数据结构上实现高级查询算法这种数据结构就是索引。

优缺点

提高数据检索效率降低数据库的IO成本通过索引列对数据进行排序降低数据排序的成本降低CPU的消耗

索引列也是要占用空间的索引大大提高了查询效率但降低了更新的速度比如

INSERT、UPDATE、DELETE

索引结构描述BTree最常见的索引类型大部分引擎都支持B树索引Hash底层数据结构是用哈希表实现只有精确匹配索引列的查询才有效不支持范围查询R-Tree(空间索引)空间索引是

MyISAM

引擎的一个特殊索引类型主要用于地理空间数据类型通常使用较少Full-Text(全文索引)是一种通过建立倒排索引快速匹配文档的方式类似于

Lucene,

索引InnoDBMyISAMMemoryBTree索引支持支持支持Hash索引不支持不支持支持R-Tree索引不支持支持不支持Full-text5.6版本后支持支持不支持

B-Tree

以一棵最大度数max-degree指一个节点的子节点个数为55阶的

b-tree

的基础上增加一个指向相邻叶子节点的链表指针就形成了带有顺序指针的

Hash

哈希索引就是采用一定的hash算法将键值换算成新的hash值映射到对应的槽位上然后存储在hash表中。

如果两个或多个键值映射到一个相同的槽位上他们就产生了hash冲突也称为hash碰撞可以通过链表来解决。

Hash索引只能用于对等比较、in不支持范围查询betwwn、、、…无法利用索引完成排序操作查询效率高通常只需要一次检索就可以了效率通常要高于

BTree

B-Tree无论是叶子节点还是非叶子节点都会保存数据这样导致一页中存储的键值减少指针也跟着减少要同样保存大量数据只能增加树的高度导致性能降低相对于

Hash

分类含义特点关键字主键索引针对于表中主键创建的索引默认自动创建只能有一个PRIMARY唯一索引避免同一个表中某数据列中的值重复可以有多个UNIQUE常规索引快速定位特定数据可以有多个全文索引全文索引查找的是文本中的关键词而不是比较索引中的值可以有多个FULLTEXT

InnoDB

Index)将数据存储与索引放一块索引结构的叶子节点保存了行数据必须有而且只有一个二级索引(Secondary

Index)将数据与索引分开存储索引结构的叶子节点关联的是对应的主键可以存在多个

演示图

如果存在主键主键索引就是聚集索引如果不存在主键将使用第一个唯一(UNIQUE)索引作为聚集索引如果表没有主键或没有合适的唯一索引则

InnoDB

备注id为主键name字段创建的有索引答第一条语句因为第二条需要回表查询相当于两个步骤。

InnoDB

答假设一行数据大小为1k一页中可以存储16行这样的数据。

InnoDB

的指针占用6个字节的空间主键假设为bigint占用字节数为8.

1024其中

如果索引关联了多列联合索引要遵守最左前缀法则最左前缀法则指的是查询从索引的最左列开始并且不跳过索引中的列。

如果跳跃某一列索引将部分失效后面的字段索引失效。

跳过的话后面的排序就无从说起了。

最左前缀法则在用select的时候和放的位置是没有关系的只要存在就行。

联合索引中出现范围查询,

17799990015;这是可以的。

字符串类型字段使用时不加引号索引将失效。

如explain

select

17799990015;此处phone的值没有加引号模糊查询中如果仅仅是尾部模糊匹配索引不会是失效如果是头部模糊匹配索引失效。

如explain

select

评估使用索引比全表更慢则不使用索引。

因为只要有一个没有索引另外一个用不用索引都没有意义都要进行全表扫描。

所以就无需用索引。

SQL

是优化数据库的一个重要手段简单来说就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

例如使用索引

还会自己权衡运行速度去更改force就是无论如何都强制使用该索引。

覆盖索引回表查询

尽量使用覆盖索引查询使用了索引并且需要返回的列在该索引中已经全部能找到减少

select

index;查找使用了索引但是需要的数据都在索引列中能找到所以不需要回表查询

覆盖索引

如果在生成的二级索引辅助索引中可以一次性获得select所需要的字段不需要回表查询。

如果在聚集索引中直接能找到对应的行则直接返回行数据只需要一次查询哪怕是select

id,

namexxx;也只需要通过辅助索引(name)查找到对应的id返回name和name索引对应的id即可只需要一次查询

id,

status由于数据量大需要对以下SQL语句进行优化该如何进行才是最优方案

select

解给username和password字段建立联合索引则不需要回表查询直接覆盖索引。

username和password字段建立联合索引的叶子节点挂的就是

前缀索引

text等时有时候需要索引很长的字符串这会让索引变得很大查询时浪费大量的磁盘IO影响查询效率此时可以只降字符串的一部分前缀建立索引这样可以大大节约索引空间从而提高索引效率。

语法create

前缀长度可以根据索引的选择性来决定而选择性是指不重复的索引值基数和数据表的记录总数的比值索引选择性越高则查询效率越高唯一索引的选择性是1这是最好的索引选择性性能也是最好的。

求选择性公式

tb_user;前缀索引中是有可能碰到相同的索引的情况的因为选择性可能不为1所以使用前缀索引进行查询的时候mysql

会有一个回表查询的过程确定是否为所需数据。

如图中的查询到lvbu6之后还要进行回表回表完再查xiaoy看到xiaoy是不需要的数据则停止查下一个。

show

在业务场景中如果存在多个查询条件考虑针对于查询字段建立索引时建议建立联合索引而非单列索引。

单列索引情况

多条件联合查询时MySQL优化器会评估哪个字段的索引效率更高会选择该索引完成本次查询。

4.5

针对于数据量较大且查询比较频繁的表建立索引针对于常作为查询条件where、排序order

by、分组group

by操作的字段建立索引尽量选择区分度高的列作为索引尽量建立唯一索引区分度越高使用索引的效率越高如果是字符串类型的字段字段长度较长可以针对于字段的特点建立前缀索引尽量使用联合索引减少单列索引查询时联合索引很多时候可以覆盖索引节省存储空间避免回表提高查询效率要控制索引的数量索引并不是多多益善索引越多维护索引结构的代价就越大会影响增删改的效率如果索引列不能存储NULL值请在创建表时使用NOT

NULL约束它。

当优化器知道每列是否包含NULL值时它可以更好地确定哪个索引最有效地用于查询

5.SQL

如果一次性需要插入大批量数据使用insert语句插入性能较低此时可以使用MySQL数据库提供的load指令插入。

客户端连接服务端时加上参数

设置全局参数local_infile为1开启从本地加载文件导入数据的开关

set

执行load指令将准备好的数据加载到表结构中先要把表建立起来。

load

数据组织方式在InnoDB存储引擎中表数据都是根据主键顺序组织存放的这种存储方式的表称为索引组织表Index

organized

但是如果主键是乱序插入的话就会导致需要插入的位置为中间的位置会有页分裂的过程。

页分裂页可以为空也可以填充一般也可以填充100%每个页包含了2-N行数据如果一行数据过大会行溢出根据主键排列。

页合并当删除一行记录时实际上记录并没有被物理删除只是记录被标记flaged为删除并且它的空间变得允许被其他记录声明使用。

当页中删除的记录到达

MERGE_THRESHOLD默认为页的50%InnoDB会开始寻找最靠近的页前后看看是否可以将这两个页合并以优化空间使用。

MERGE_THRESHOLD合并页的阈值可以自己设置在创建表或创建索引时指定

主键设计原则

满足业务需求的情况下尽量降低主键的长度二级索引的叶子节点保存的就是主键所以主键小占用的空间也就会少。

插入数据时尽量选择顺序插入选择使用

AUTO_INCREMENT

做主键或者是其他的自然主键如身份证号占用的空间大。

业务操作时避免对主键的修改

5.3

filesort通过表的索引或全表扫描读取满足条件的数据行然后在排序缓冲区

sort

by字段全部使用升序排序或者降序排序则都会走索引但是如果一个字段升序排序另一个字段降序排序则不会走索引explain的extra信息显示的是Using

index,

根据排序字段建立合适的索引多字段排序时也遵循最左前缀法则尽量使用覆盖索引多字段排序一个升序一个降序此时需要注意联合索引在创建时的规则ASC/DESC如果不可避免出现filesort大数据量排序时可以适当增大排序缓冲区大小

5.4

在分组操作时可以通过索引来提高效率分组操作时索引的使用也是满足最左前缀法则的

如索引为idx_user_pro_age_stat则句式可以是select

...

优化方案一般分页查询时通过创建覆盖索引能够比较好地提高性能可以通过覆盖索引加子查询形式进行优化

此语句耗时很长

优化方案自己计数如创建key-value表存储在内存或硬盘或者是用redis

如果count函数的参数count里面写的那个字段不是NULL字段值不为NULL累计值就加一最后返回累计值用法count(*)、count(主键)、count(字段)、count(1)count(主键)跟count()一样因为主键不能为空count(字段)只计算字段值不为NULL的行count(1)引擎会为每行添加一个1然后就count这个1返回结果也跟count()一样count(null)返回0

count(主键)InnoDB引擎会遍历整张表把每行的主键id值都取出来返回给服务层服务层拿到主键后直接按行进行累加主键不可能为空count(字段)没有not

null约束的话InnoDB引擎会遍历整张表把每一行的字段值都取出来返回给服务层服务层判断是否为null不为null计数累加有not

null约束的话InnoDB引擎会遍历整张表把每一行的字段值都取出来返回给服务层直接按行进行累加count(1)InnoDB

引擎并不会把全部字段取出来而是专门做了优化不取值服务层直接按行进行累加

按效率排序count(字段)

的行锁是针对索引加的锁不是针对记录加的锁并且该索引不能失效否则会从行锁升级为表锁。

update

test;这句由于name没有索引所以会把整张表都锁住进行数据更新解决方法是给name字段添加索引就可以由表锁变成行锁。

6.锁

锁是计算机协调多个进程或线程并发访问某一资源的机制。

在数据库中除传统的计算资源CPU、RAM、I/O的争用以外数据也是一种供许多用户共享的资源。

如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题锁冲突也是影响数据库并发访问性能的一个重要因素。

从这个角度来说锁对数据库而言显得尤其重要也更加复杂。

NOTE

全局锁锁定数据库中的所有表。

表级锁每次操作锁住整张表。

行级锁每次操作锁住对应的行数据。

6.1

全局锁就是对整个数据库实例加锁加锁后整个实例就处于只读状态后续的DML的写语句DDL语句已经更新操作的事务提交语句都将被阻塞。

其典型的使用场景是做全库的逻辑备份对所有的表进行锁定从而获取一致性视图保证数据的完整性。

6.2

表级锁每次操作锁住整张表。

锁定粒度大发生锁冲突的概率最高并发度最低。

应用在MyISAM、InnoDB、BDB等存储引擎中。

lock所有的事物都只能读当前加锁的客户端也只能读不能写不能写

2.表独占写锁write

lock对当前加锁的客户端可读可写对于其他的客户端不可读也不可写。

读锁不会阻塞其他客户端的读但是会阻塞写。

写锁既会阻塞其他客户端的读又会阻塞其他客户端的写。

元数据锁meta

data

lockMDLMDL加锁过程是系统自动控制无需显式使用在访问一张表的时候会自动加上。

MDL锁主要作用是维护表元数据的数据一致性在表上有活动事务的时候不可以对元数据进行写入操作。

在MySQL5.5中引入了MDL当对一张表进行增删改查的时候加MDL读锁共享;当对表结构进行变更操作的时候加MDL写锁排他。

意向锁:

为了避免DML在执行时加的行锁与表锁的冲突在InnoDB中引入了意向锁使得表锁不用检查每行数据是否加锁使用意向锁来减少表锁的检查。

一个客户端对某一行加上了行锁那么系统也会对其加上一个意向锁当别的客户端来想要对其加上表锁时便会检查意向锁是否兼容若是不兼容便会阻塞直到意向锁释放。

意向锁兼容性

意向共享锁IS与表锁共享锁read兼容与表锁排它锁write互斥。

意向排他锁lX与表锁共享锁read及排它锁write都互斥。

意向锁之间不会互斥。

6.3

行级锁每次操作锁住对应的行数据。

锁定粒度最小发生锁冲突的概率最低并发度最高。

应用在InnoDB存储引擎中。

InnoDB的数据是基于索引组织的行锁是通过对索引上的索引项加锁来实现的而不是对记录加的锁。

对于行级锁主要分为以下三类

行锁Record

Lock锁定单个行记录的锁防止其他事务对此行进行update和delete。

在RCread

commit

read隔离级别下都支持。

间隙锁GapLock锁定索引记录间隙不含该记录确保索引记录间隙不变防止其他事务在这个间隙进行insert产生幻读。

在RR隔离级别下都支持。

比如说

两个临近叶子节点为

Lock行锁和间隙锁组合同时锁住数据并锁住数据前面的间隙Gap。

在RR隔离级别下支持。

共享锁S允许一个事务去读一行阻止其他事务获得相同数据集的排它锁。

排他锁X允许获取排他锁的事务更新数据阻止其他事务获得相同数据集的共享锁和排他锁。

SQL行锁类型说明insert排他锁自动加锁update排他锁自动加锁delete排他锁自动加锁select不加任何锁select

lock

针对唯一索引进行检索时对已存在的记录进行等值匹配时将会自动优化为行锁。

InnoDB的行锁是针对于索引加的锁不通过索引条件检索数据那么InnoDB将对表中的所有记录加锁此时就会升级为表锁。

间隙锁/临键锁-演示

索引上的等值查询唯一索引给不存在的记录加锁时优化为间隙锁。

索引上的等值查询普通索引向右遍历时最后一个值不满足查询需求时next-key

lock

退化为间隙锁。

索引上的范围查询唯一索引–会访问到不满足条件的第一个值为止。

注意间隙锁唯一目的是防止其他事务插入间隙。

间隙锁可以共存一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。

7.事务原理

事务是一组操作的集合它是一个不可分割的工作单位事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求即这些操作要么同时成功要么同时败。

具有ACID四大特征。

原子性一致性持久性这三大特性由

重做日志记录的是事务提交时数据页的物理修改是用来实现事务的持久性。

log

file前者是在内存中后者在磁盘中。

当事务提交之后会把所有修改信息都存到该日志文件中用于在刷新脏页到磁盘发生错误时进行数据恢复使用。

个人理解

是顺序写性能处理的够快直接刷到ibd中是随机写性能慢。

所以脏页是在下一次读的时候或者后台线程采用一定的机制进行刷盘到ibd中。

undo

回滚日志用于记录数据被修改前的信息作用包含两个提供回滚和MVCC多版本并发控制。

undo

log记录物理日志不一样它是逻辑日志。

可以认为当delete一条记录undo

log中会记录一条对应的insert记录反之亦然当update一条记录时它记录一条对应相反的update记录。

当执行rollback时就可以从undo

Undo

log采用段的方式进行管理和记录存放在前面介绍的rollback

log

读取的是记录的最新版本读取时还要保证其他并发事务不能修改当前记录会对读取的记录进行加锁。

对于我们日常的操作如

select…lock

update、update、insert、delete排他锁都是一种当前读。

8.2

简单的select不加锁就是快照读快照读读取的是记录数据的可见版本有可能是历史数据不加锁是非阻塞读。

Read

Committed每次select都生成一个快照读。

Repeatable

Read开启事务后第一个select语句才是快照读的地方。

Serializable快照读会退化为当前读。

8.3

Control多版本并发控制。

指维护一个数据的多个版本使得读写操作没有冲突快照读为MySQL实现MVCC提供了一个非阻塞读功能。

MVCC的具体实现还需要依赖于数据库记录中的三个隐式字段、undo

MVCC

log回滚日志在insert、update、delete的时候产生的便于数据回滚的日志。

undo

RC隔离级别下在事务中每一次执行快照读时生成ReadView。

RR隔离级别下在事务中第一次执行快照读时生成ReadView后续会复用。

MVCC