mysql入门

最新推荐文章于 2022-04-16 14:30:57 发布

shuimuhanchen

最新推荐文章于 2022-04-16 14:30:57 发布

阅读量436

点赞数

分类专栏：数据处理文章标签： MySql 数据库原理事物的特性事物隔离级别数据库索引

本文链接：https://blog.csdn.net/yanyingnan1357/article/details/80039673

版权

数据处理专栏收录该内容

9 篇文章 0 订阅

订阅专栏

Mysql架构

存储引擎接口模块可以说是 MySQL 数据库中最有特色的一点，目前各种数据库产品中，基本上只有 MySQL可以实现其底层数据存储引擎的插件式管理。这个模块实际上只是一个抽象类，但正是因为它成功地将各种数据处理高度抽象化，才成就了今天 MySQL 可插拔存储引擎的特色。
注意：存储引擎是基于表的，而不是数据库。

mysql中的并发控制

读写锁

读锁：共享锁
写锁：排它锁

粒度锁

所谓锁的策略就是在锁的开销与数据安全之间的平衡问题。

表锁：粒度大
行级锁：粒度小

事务

事务指逻辑上的一组操作，组成这组操作的各个单元，要么全部成功，要么全部不成功。

事务的特性（ACID四个特性）

原子性：指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性：事务必须使数据库从一个一致性状态变换到另外一个一致性状态。转账前和转账后的总金额不变。
隔离性：事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，并发事务之间要相互隔离。
事务的隔离级别
- 赃读：指一个事务读取了另一个事务未提交的数据。关键在于未提交，例如转账。
- 不可重复读：在一个事务内读表中的某一行数据，多次读取结果不同。读取到了另一个事务提交后的数据。（update）
- 虚读（幻读）：是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致，例如事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。(insert)
持久性：一个事务一旦被提交，对数据库中数据的改变就是永久性的，即使数据库发生故障也不应该有任何影响。

数据库设置事务的隔离级别：

级别越高，性能越低，数据越安全（1 2 4 8表示级别。之所以用1 2 4 8是因为在内存中bit位方便存）
mysql查看当前的事务隔离级别：SELECT @@TX_ISOLATION;
mysql更改当前的事务隔离级别：SET TRANSACTION ISOLATION LEVEL 四个级别之一。
设置隔离级别必须在开启事务之前

1 READ UNCOMMITTED: 赃读、不可重复读、虚读都有可能发生。
2 READ COMMITTED: 避免赃读。不可重复读、虚读都有可能发生。（oracle默认的）
4 REPEATABLE READ:避免赃读、不可重复读。虚读有可能发生。（mysql默认）
8 SERIALIZABLE: 避免赃读、不可重复读、虚读。

死锁

等待对方释放锁，又同时持有对方的锁
事物1：
update …where id = 1
update …where id = 2

事物2：
update …where id = 2
update …where id = 1

事务日志

1、事务日志可以帮助提高事物效率。修改数据需要写两次磁盘，通常称之为预写式日志。

存储引擎修改表的数据时，只需要修改其内存拷贝，把修改行为持久到硬盘的日志中；
内存中被修改的数据根据持久的日志在后台慢慢刷会到磁盘；
因为事物日志是追加顺序io，这是快的本质。

2、redo log、binlog、undo log 区别与作用

redo log：通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。在数据库进行异常重启的时候，可以根据redo log日志进行恢复。
undo log：提供回滚和多个行版本控制(MVCC)。undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录（这不是跟数据恢复服务很像嘛）。另外，undo log也会产生redo log，因为undo log也要实现持久性保护。
binlog：redo log、undo log都是属于存储引擎层面的，binlog是属于MySQL Server层面的，又称为归档日志，属于逻辑日志，是以二进制的形式记录的是这个语句的原始逻辑，可以作为恢复数据使用，主从复制搭建。
binlog的三种格式：
1、ROW Level：记录的方式是行，即如果批量修改数据，记录的不是批量修改的SQL语句事件，而是每条记录被更改的SQL语句，因此，ROW模式的binlog日志文件会变得很“重”。
2、Statement level(默认)：记录每个修改数据的SQL语句，批量修改时记录的是批量修改的SQL语句事件。
3、Mixed：在Mixed模式下，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。
4、工作中如何选择binlog的模式：如果生产中使用MySQL的特殊功能相对少（存储过程、触发器、函数）。选择默认的Statement Level即可；如果生产中使用MySQL的特殊功能较多的，可以选择Mixed模式；如果生产中使用MySQL的特殊功能较多，又希望数据最大化一致，此时最好Row level模式；但是要注意，该模式的binlog非常“沉重”。

mysql中的事务

mysql默认自动提交事务。每条语句都处在单独的事务中。
也可以手动控制事务：
- 开启事务：start transaction | begin
- 提交事务：commit
- 回滚事务：rollback

MVCC（多版本并发控制）

可以认为MVCC是一个行级锁的变种。在很多情况下避免了加锁，只锁定必要的行，实现非阻塞读操作，开销更低。

实现原理：通过保存数据在某个时间点的快照，也就是说不管需要执行多长时间，每个事物看到的时间点数据都是一致的。
不同存储引擎的MVCC实现原理不同，典型的有乐观并发控制，悲观并发控制。
InnoDB的MVCC是通过在每行记录后保存两个隐藏列，一个保存创建时间，一个保存过期时间，当然不是真正的时间值而是系统版本号，每开启一个新的事物，版本号递增。以空间换性能。
MVCC只在 read committed 和 repeatable read 两个隔离级别下工作，其他两个级别与MVCC不兼容， read uncommitted总是读取最新数据行，serializable则会对所有读取行都加锁。

MySql存储引擎

查询MySql引擎
Engine参数：指存储引擎名称；
Support参数：说明MySQL是否支持该类引擎，YES表示支持；
Comment参数：指对该引擎的评论；
Transactions参数：表示是否支持事务处理，YES表示支持；
XA参数：表示是否分布式交易处理XA规范，YES表示支持；
Savepoints参数：表示是否支持保存点，以便事务回滚到保存点，YES表示支持。
查询默认引擎

热备份与冷备份：

热备份是在数据库运行的情况下，采用archivelog mode方式备份数据库的方法。即热备份是系统处于正常运转状态下的备份。优点是：
1．可在表空间或数据库文件级备份，备份的时间短。
2．备份时数据库仍可使用。
3．可达到秒级恢复（恢复到某一时间点上）。
4．可对几乎所有数据库实体做恢复
5．恢复是快速的，在大多数情况下在数据库仍工作时恢复。
冷备份发生在数据库已经正常关闭的情况下，当正常关闭时会提供给我们一个完整的数据库。冷备份时将关键性文件拷贝到另外的位置的一种说法。优点是：
1、是非常快速的备份方法（只需拷文件）
2、容易归档（简单拷贝即可）
3、容易恢复到某个时间点上（只需将文件再拷贝回去）
4、能与归档方法相结合，做数据库“最佳状态”的恢复。
5、低度维护，高度安全。

理解I/O

你需要取得数据在磁盘的某个地方，访问它会有同样的寻道和旋转时延，我们就把这种方式的IO叫做随机IO；
但是如果它刚好就在你刚才访问的那一个磁盘块的后面，磁头就能立刻遇到，不需等待，这种IO就叫顺序IO。

数据库连接池大小

项目中数据库连接池大小配置公式：连接数 = ((cpu核心数 * 2) + 有效磁盘数)

InnoDB

支持行级锁。
给Mysql的表提供了事务、回滚、崩溃修复能力、多版本并发控制（MVCC）的事务安全、间隙锁（可以有效的防止幻读的出现）
支持辅助索引、聚簇索引、自适应hash索引。
支持热备份，其它引擎不支持热备份。
还有InnoDB是Mysql上唯一一个提供了外键约束的引擎。
InnoDB存储引擎中，创建的表的表结构是单独存储的并且存储在.frm文件中。数据和索引存储在一起的并且存储在表空间中。

MyISAM

MyISAM支持的是表级锁。
MyISAM不支持事务、行级锁、更无法忍受的是崩溃后不能保证完全恢复（只能手动修复）。
MyISAM支持全文索引、压缩存放、空间索引（空间函数）、延迟更新索引键。
只能冷备份
MyISAM存储引擎的表存储成3个文件。文件的名字和表的名字相同。扩展名包含frm、MYD、MYI。其中frm为扩展名的文件存储表的结构；MYD为扩展名的文件存储数据，其是MYData的缩写；MYI为扩展名的文件存储索引，其为MYIndex的缩写。

选择合适的存储引擎

一般从四个方面的因素考虑：

事务：需要支持事物用innodb，有大量select查询用Myisam，它管理非事物表，提供高速存储和检索。
备份：如果可以定期关闭服务器执行备份，无所谓了，如果要在线热备份，用innodb。
崩溃恢复：用innodb。
独有特性：根据情况考虑。

schema与数据类型优化

选择优化的数据类型

任何字段如果为非负数，必须是unsigned：无符号值可以避免误存负数，且扩大了表示范围
小数类型为decimal，禁止使用float和double：float 和 double都存在精度损失的问题，很可能在比较值的时候，得到不正确的结果。如果存储的数据范围超过 decimal的范围，建议将数据拆成整数和小数并分开存储
如果存储的字符串长度几乎相等，使用char定长字符串类型，varchar是可变长字符串，不预先分配存储空间，长度不要超过5000，如果存储长度大于此值，定义字段类型为 text，独立出来一张表，用主键来对应
更小的数据类型往往更好。
简单地数据类型例如整型，比字符串需要的cpu周期更小。
尽量避免Null。通常情况下尽量指定列为 not null。
整数类型：从tinyint(8bit)到bigint(64bit)选择，并且可以用unsigned将整数范围扩大一倍。
decimal可以存储比bigint更大的数字。但主要作用是存储精确的小数。它只是一种存储格式，计算时会转化成double，因为需要更多的空间和计算开销，尽量在精确计算时用decima，在数据量比较大的时候，可以考虑用bigint代替它，乘上10的n次方去小数点即可避免浮点存储的不精确。
有时可以使用枚举代替字符串类型，存储时非常紧凑会根据列表值的数量压缩到一两个字节中，但是枚举最不好的地方是，字符串列表是固定的，添加或删除字符串必须使用alter table，因此未来可能改变的字符串不宜使用枚举存储。
IPV4地址怎么存？人们经常使用varchar(15)存，然而他们实际上是32为无符号整数，所以应该用无符号整数存储ip地址。inet_aton()和inet_ntoa()函数可以在这两种表示方法之间转换
选择标识符：整数类型往往是最好的选择（并且可以使用 auto_increment）；应该避免字符串类型，很消耗空间；
如果需要存储比秒更小的时间和日期怎么办？就用bigint存储微秒。
mysql五种时间类型比较：

特殊说明：TIMESTAMP 和时区相关，更能反映当前时间。当插入日期时，会先转换为本地时区后再存放；当查询日期时，会将日期转换为本地时区后再显示。所以不同时区的人看到的同一时间是不一样的，如果记录的日期需要让不同时区的人使用，最好使用 TIMESTAMP。

范式与反范式

在范式化的数据库中，每个实时数据会出现并且只出现一次，相反，反范式化数据库中，信息是冗余的。在实际应用中经常需要混合使用。

范式化的优缺点：更新操作快；只需要修改更少的数据；表小，更好的在内存执行，快。缺点是通常需要关联，代价昂贵，部分索引策略无效。
反范式化的优缺点：所有数据都在一张表中，避免了关联；能使用更有效地索引策略。缺点是数据冗余更新费劲。

利用缓存表与汇总表优化

加快alter table的操作速度

alter table的操作对于大表来说是很蛋疼的，它会新建一个表，插入数据，在删除旧表。慢！可以使用如下技巧：

在一台不提供服务的机器上执行操作，然后与提供服务的主库切换。
影子拷贝：创建一张与原表无关的新表，用重命名和删除操作交换两张表。（有些工具可以使用）
只修改.frm文件：创建表的时候，mysql会创建与表名相同的.frm文件保存表的定义，因为不同的存储引擎保存数据和索引的方式不同，但表的定义是在服务层统一处理的。因此可以用alter column语句直接修改.frm文件而不是用modify column。
为了高效的载入数据到MyISAM中，常用的技巧是：先禁用索引，载入数据，在开启索引，（对唯一索引无效，因为disable keys 只对非唯一索引有效。）索引工作被延迟到数据载入以后，这时可以通过排序来建立索引了，并且使得索引树碎片更少更紧凑。

索引及优化

不同的数据库引擎索引的实现方式也不同。
索引优化应该是对查询性能优化最有效的手段了。

索引的优点

大大减少服务器需要扫描的数据量。
帮助服务器避免排序和临时表。
可以将随机io变为顺序io。

索引类型

BTree索引：意味着所有值都是按顺序存储的。因此除了按值查找以外还可以用于order by操作（按序查找）。
使用限制：
- 如果不是按照索引最左列进行查找，则无法使用索引。
- 不能跳过索引中的列进行查找。
- 如果某列是像like一样的范围查询，那么右边所有的列都无法使用索引优化查找。
为什么不用AVL 树和红黑树或者B树？
- AVL 树和红黑树这些二叉树结构的数据结构可以达到最高的查询效率这是毋庸置疑的。但是AVL 数和红黑树基本都是存储在内存中才会使用的数据结构，那磁盘中会有什么不同呢？操作系统读写磁盘的基本单位是扇区，而文件系统的基本单位是簇(Cluster)。也就是说，磁盘读写有一个最少内容的限制，即使我们只需要这个簇上的一个字节的内容，我们也要含着泪把一整个簇上的内容读完。那么，现在问题就来了一个父节点只有 2 个子节点，并不能填满一个簇上的所有内容啊？那多余的内容岂不是要浪费了？由于 B+ 树分支比二叉树更多，所以相同数量的内容，B+ 树的深度更浅，深度代表什么？代表磁盘 io 次数啊！数据库设计的时候 B+ 树有多少个分支都是按照磁盘一个簇上最多能放多少节点设计的啊！所以，涉及到磁盘上查询的数据结构，一般都用 B+ 树啦。
- B-树每个节点都存储key和data，所有节点组成这棵树。B+树叶子节点包含所有内容，增加顺序访问指针，也就是每个叶子节点增加一个指向相邻叶子节点的指针便于全局遍历。而且它的非叶子节点不存储data，这样一个节点就可以存储更多的key。
存储引擎索引底层分析：
- MyISAM引擎的索引结构为B+Tree。
- 不支持事务，但是每次查询都是原子的；
- 支持表级锁，即每次操作是对整个表加锁；
- 存储表的总行数；
- 一个MYISAM表有三个文件：表结构文件（其它引擎也有这个文件）、索引文件、数据文件，因此采用非聚簇索引，索引文件的数据域存储指向数据文件的指针。
- 二级辅索引与主索引基本一致，但是辅索引不用保证唯一性。
- InnoDB引擎的索引结构同样也是B+Tree。
- 支持ACID的事务，支持事务的四种隔离级别；
- 支持行级锁及外键约束：因此可以支持写并发；
- 不存储总行数；
- Innodb的索引文件本身就是数据文件，即B+Tree的数据域存储的就是实际的数据，这种索引就是聚簇索引。这个索引的key就是数据表的主键，因此InnoDB表数据文件本身就是主索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。
- 二级辅索引的数据域存储主键的值（因此是非聚簇索引），因此从辅索引查找数据，需要先通过辅索引找到主键值（自适应哈希索引可以减少这样的重复工作）；最好使用自增主键，防止插入数据时，为维持B+树结构，文件的大调整。

InnoDB二级辅索引

哈希索引：
- 基于哈希表实现的，跟哈希表有关的特点就是快！
- innodb支持自适应哈希索引，在btree索引上再建立哈希索引。
- 因为哈希表无序，所以不能按序查找。
- 解决冲突依然用的是链表。
空间数据索引（R-Tree）：myisam引擎支持，无序前缀查询，会从所有维度索引数据。
全文索引：查找的是文本中的关键词而不是比较索引中的值。适用于match against操作而不是where。

高性能的索引策略

独立的列：索引列不能是表达式的一部分，当然也不能作为函数参数，否则索引失效
前缀索引和索引的选择性：有时需要索引很长的字符列，会变得大且慢，就使用前缀索引，但会降低索引的选择性（选择性高就是指可以过滤掉更多的行），另外，mysql无法使用其前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖索引扫描
多列索引：在多个列上建立独立的单列索引大部分情况下并不能提高查询性能。
选择合适的索引列顺序：过滤最快的列、区分度高的列放在最前面往往是好的；查询条件有= > 或 <，=号所连接的列放在前面
覆盖索引：
- 如果一个索引覆盖所有需要查询的字段值，就称之为覆盖索引。它能极大的提高性能，只需扫面索引无序回表。
- 由于innodb的聚簇索引，覆盖索引很有用，如果二级索引可以覆盖查询，则可以避免对主键的二次查询。
使用索引扫描做排序
压缩（前缀压缩）索引：myisam使用前缀压缩来减少索引大小让更多索引可以放入内存，在某些情况下可以极大提升性能。默认只压缩字符串，可以配置压缩整数。
冗余和重复索引：primary key ；unique(id)；index(id)都是通过索引实现的，因此同时创建了三个重读的索引。要避免。
未使用的索引：有一些服务器永远不用的索引可以利用一些工具定位查询，删除它。
索引和锁：在使用索引查找的时候，依然可能锁住一些不需要的行。从而影响性能。
业务上具有唯一特性的字段（包括组合字段）必须建立唯一索引，insert速度会变慢但是可以忽略不计，查找会快很多，也能防止脏数据
varchar字段加索引必须使用前缀索引，也就是指定索引长度，具体长度估计计算见：https://www.cnblogs.com/balfish/p/9003794.html
左模糊查询索引失效，因此要避免使用左模糊
有order by场景，注意利用索引的有序性，即order by最后的字段是组合索引的末尾字段，这样避免了file_sort，能提高查询性能，例如：where a=? and b=? order by c 索引是：（a,b,c）(当然如果 = 换为 > 或 < 等范围查询，索引有序性就无法利用了)
利用延迟关联或者子查询优化：
- 二级索引不是覆盖索引必须要根据主键回表的场景
  超多分页场景：MySQL并不是跳过offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行，那当 offset 特别大的时候，效率就非常的低下：SELECT a.* FROM 表 1 a, (select id from 表 1 where 条件 LIMIT 100000,20 ) b where a.id=b.id
  1）关联查询、子查询实际上是一回事儿，核心就是先快速定位需要获取的id段，然后再关联
  2）延迟关联优化：索引：（org_code，div_type）
  explain
  select * from group_build
  join
  (select id from group_build where org_code = ‘N11012’ and div_type = ‘101000000001’) b
  on
  group_build.id = b.id
  3）子查询优化：索引：（org_code，div_type）
  explain
  SELECT * from group_build,
  (select id from group_build where org_code = ‘N11012’ and div_type = ‘101000000001’) b
  where group_build.id = b.id
explain解析sql语句：
- type: 当前表内访问方式、连接类型，效率（const>eq_ref>ref>range>index>all）最少要range
  1）const 单表中最多只有一个匹配行(主键或者唯一索引)，在优化阶段即可读取到数据。
  2）ref 指的是使用普通的索引(normal index)。
  3）range 对索引进行范围检索。
  4）index，索引物理文件全扫描，速度非常慢，与全表扫描是小巫见大巫。
- possible_keys:可能使用到的索引
- key:经过优化器评估最终使用的索引
- ref:引用到的上一个表的列
- filtered:表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。
- Extra:额外的信息说明
  1）Using index condition：查找使用了索引，但是需要回表查询数据
  2）Using where; Using index：查找使用了索引，需要的数据都在索引列中能找到，所以不需要回表查询数据

查询性能优化

优化数据访问

是否向数据库请求了大量不需要的数据。
MySql层面是否在分析大量超过需要数据的行。
- 可以使用覆盖索引
- 可以改变库表结构，使用汇总表

重构查询

切分查询：删除旧数据就是一个很好的例子，将delete分解成多个较小的查询，提高性能。
分解关联查询：不关联表，分开查询，让结果在应用程序中进行关联。

查询执行基础图

查询执行路径

优化特定类型查询

优化count()：条件反转，扫描少的数据。利用myisam的count(*)非常快的特性。
优化关联查询：两个表关联，只需要在第二个表的相应列上创建索引即可。
优化子查询：尽可能使用关联查询代替。
优化group by 和 distinct：mysql优化器会在内部相互转化这两类查询，最有效的方法是使用索引优化。
优化limit分页：在系统中需要进行分页操作的时候，通常会使用limit加上偏移量的方法，同时加上合适的group by，如果有对应的索引，效果将会很好。
优化sql_calc_found_rows：分页时另一个常用技巧是在limit语句中加上sql_calc_found_rows，就可以获得去掉limit后满足条件的行数作为分页的总数。
优化union：mysql通常使用创建并填充临时表的方式执行union，因此很多优化没有得到效果，要手动把子句下推到union的各个子查询中；尽量用union all否则会有唯一性检查。
使用用户自定义变量也是一个容易被忽视的方法。
防止sum返回null的npe问题
select ifnull(sum(price_adjust_percent),0) from price_change
使用ISNULL()来判断是否为NULL值，简洁易懂，并且ISNULL(column) 执行效率更快一些，NULL 与任何值的直接比较都为 NULL。
1. NULL<>NULL 的返回结果是 NULL，而不是 false。
2. NULL=NULL 的返回结果是 NULL，而不是 true。
3. NULL<>1 的返回结果是 NULL，而不是 true。
  SELECT * from price_change where price_adjust_percent = NULL 这个完全不会返回任何数据
  SELECT * from price_change where price_adjust_percent is null and city_code is not null
  改为
  SELECT * from price_change where isnull(price_adjust_percent) and city_code is not null
数据订正(特别是删除或修改记录操作)时，要先select，避免出现误删除，确认无误才能执行更新语句

mysql高级特性

分区表：粗粒度的简易的索引策略。
视图：对于几个表的复杂查询，视图有时候会大大简化问题。
外键：外键限制会将约束放在mysql中，提高性能。高性能系统中外键会显得臃肿。
存储过程：mysql本身实现了存储过程，触发器，存储函数和事件。
绑定变量：对于大量重复类型的查询语句，绑定变量只会解析一次，因此提高性能。
插件：使用c/c++编写的插件可以扩展mysql性能。
字符集：字符集是字节到字符之间的映射，使用utf-8时可能会消耗更多空间。
全文索引：myisam支持全文索引。全文索引是用于检索字段中是否包含或不包含指定的关键字，有点像搜索引擎的功能，其内部的索引结构采用的是与搜索引擎相同的倒排索引结构，其原理是对字段中的文本进行分词，然后为每一个出现的单词记录一个索引项，这个索引项中保存了所有出现过该单词的记录的信息，也就是说在索引中找到这个单词后，就知道哪些记录的字段中包含这个单词了。因此适合用大文本字段的查找。
XA事物：在使用innodb作为存储引擎，并且开启binlog的情况下，MySQL同时维护了binlog日志与innodb的redo log为了保证这两个日志的一致性，MySQL使用了XA事务，由于只在单机上工作，所以被称为内部XA。而外部XA就是一般谈论的分布式事务了
查询缓存：缓存中有以前查询的结果直接取出即可。