mysql王者晋级之路

发表于 分类于 ， 本文字数： 6.2k

存储引擎

InnoDB、Mysql、Memory、blackhole等

1687768365725

1687768385011

现在数据库默认的版本存储引擎时InnoDB,并且Msql8.0宣布InnoDB存储数据字典，MylSAM彻底从MyQL数据库中剥离开，被废弃了。

建议把线上的MyISM的存储引擎表全部转化成InnoDB表存储。下面是两种之间的主要区别

1687768646223

可以看出InnoDB存储引擎的优势很明显。

##表

整型

int tinyint 使用最多

1687771979891

1687772000859

id 一般都选择int，基本不用bigint这种，因为int unsigned数值范围可以达到43亿

问题：int(4)和int(10)有区别吗？

Int(n) 括号里面的数字无论写成多少，都是占四个字节，最多能存10位数字。n不是代表能存多少位数，只是显示宽度。所以两者没有区别。但如果定义了zerofill，就有区别了，比如写入一个1，int(4)会写成0001，int(10)会写成0000000001.

1687772412789

1687772443967

浮点型

1687772496719

避免使用浮点型，因为他属于并不精确的类型，生产中不建议使用float和double。

在生产环境中，我们大多数使用decimal来存储金钱字段，但是数值运算过程中，还是会转成浮点数来运算，而且在运算过程中会出现四舍五入的情况，这样就造成了金额的不准确。

decimal(6,2) 6代表整数加小数部分的总长度，2代表小数点后的保留位数，插入的值的小数部分会自动补齐或者四舍五入。插入的数字的整数部分长度不能超过M-D位，否则不能成功插入，会报超出范围的错误。

对于交易类的平台，这种四舍五入的现象还是要避免的，可以使用int来存储金钱，int单位为分，这样就不存在四舍五入了，让数据更精确。

时间类型

1687773196369

也可以使用int类型来存储时间，可以通过两个函数转换来，unix_timestamp和from_unixtime

字符串类型

1687773385247

text和blob这种存大量文字或者存图片的大数据类型建议不要与业务表放在一起。主要业务表切忌出现这样类型的字段。

问题：Char和VarChar的区别？

Char类型用于定长字符串，并且范围为0-255.如果字符数没有达到定义的位数，会在后面用空格补齐全存入数据库，如果超过大小，会被截断。

Varchar是变长长度，长度范围为0-65535，存储时，不会在后面补空格；如果超过指定长度，也会截断。可以根据实际内容动态地改变存储值地长度，在不确定需要多少字符时，可以大大地节约磁盘空间，提高存储效率。会使用多余字节记录长度，<255一个字节，>255两个字节

1687773991188

问题：IPv4这样地字段，选择什么类型数据存储合适？

推荐使用int类型类存储ip字段，可int不是整数吗，怎么可以存字符串呢？这里使用到inet_aton和inet_ntoa两个函数

表碎片产生的原因

1687794828234

1687794992864

1687795034807

1687795060611

mysql 库表常用命令总结

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
use database   选择数据库
show databases 查看所有数据库
show tables  查看某库下所有表
crate database database_name  创建数据库
drop database database_name 删除数据库
create table table_name(字段列表) 创建表
drop table table_name 删除表
delete from table_name(where)或者truncate table table_name 只删除表数据
insert into table_name(字段列表) values (对应字段地值) 往表插入数据
update table_name set: 字段值=某值(where) 更新表中某行数据
select * from table_name (where) 查询表中数据
show create table table_name \G 查看建表语句
desc table_name 查看表结构
show table status 获取表基础信息
show index from table_name 查看当前表下索引地情况
show full processlist 查看数据库当前连接地情况

##索引

###二叉树结构

B+树是由 二叉树->平衡二叉树->b树 演化而来地。

1687775957789

平衡二叉树

二叉树随着节点地深度加大时，查询地均分复杂度就会上升，为了提供更快地查询速度，平衡树就出现了。必须满足左右两个子树地高度差地绝对值不超过1，且它地左右子树都是一颗平衡二叉树。它和二叉树地区别在于随时要保证插入后地整颗二叉树是平衡地。它会左旋或者右旋来使不平衡地树变成平横树。

1687776200360

b树结构

有些书说索引结构是Btree，其实不正确。mysql地索引结构是B+tree,下图为Btree的结构

1687776324267

所有的叶子节点都在同一层，叶子结点都出现在同一层，叶子节点不包含任何关键字的信息。

b+树

是btree的变体，定义与btree基本相同，但它所有关键字的信息都出现在叶子节点中，并且包含这些关键字记录的指针，叶子节点可以按照关键字的大小顺序链接。还有它所有的数据都保存在叶子节点中，这是区别于btree结构最主要的特点。

1687777607677

B+树索引是双向链表结构，而且用B+tree结构做索引要比Btree块，可以看出访问关键字的顺序是连续的，不用再访问上一个节点，而且叶子节点包含所有数据信息。

聚集索引和普通索引

MySQL数据库的B+tree索引其实可以分为两大类，一类叫聚集索引，一类叫非聚集索引，也就是普通索引。

聚集索引叶子节点存放表中所有行数据记录的信息，所以经常会说数据即索引，索引即数据。创建表时，要显示为表指定一个主键（聚集索引），如果不主动创建主键，那么InnoDB会选择第一个不包含null值的唯一索引作为主键。如果唯一索引都没有，InnoDB就会为该表默认生成一个6字节的rowid作为主键

普通索引在叶子节点并不包含所有行的数据记录，只是会在叶子节点存有自己本身的键值和主键的值。在检索数据时，通过普通索引叶子节点上的主键来获取到想要查找的行数据记录

1687777900715

普通索引的创建语法

1
2
3
alter table table_name add index (索引字段)
或者
create index index_name on table_name(索引字段)

通常使用show index from table_name 来查看表中有那些索引。还可用explain命令查看sql语句的执行计划，判断添加索引后，优化器是否生成了更高效的执行计划。

1687778242689

1687778257645

1687778298642

1687778351949

1687778370663

1687778691528

1687778805400

主键索引和唯一索引

主键索引其实就是唯一索引，每张表中有且仅有一个主键，可以由表中一个或多个字段组成。主键索引必须满足三个条件，主键值必须唯一：不能包含null值；一定要保证该值是自增属性。使用自增列做索引，可以保证写入数据的顺序也是自增的，这就在很大程度上提高了存取效率。

创建主键的语法

1
alter table table_name add primary key(column);

唯一索引是约束条件的一种，其实就是不允许有重复值，但是可以运行有null值。唯一索引可以有多个

创建唯一索引的语法

1
alter table table_name add unique(column)

覆盖索引

只需通过索引就可以返回查询所需要的数据，而不必在查到索引之后再去回表查询数据了。这样就减少了大量的i/0操作，查询速度也相当块。

比如，想要查询主键id字段，而且在查询条件中name字段是普通索引，之但普通索引中包含主键的值，相当于（name，id）那么这条语句就使用了覆盖索引，出现了using index。

**如果使用覆盖索引，一定要让select列出所需要的列。坚决不可以直接写出select ***

前缀索引

对应BLOB、TEXT。或者很长的VARCHAR类型的列，为它的前几个字符建立索引，这样的索引就叫做前缀索引，这样建立的索引更小，所以查询更快，但也有坏处，他不能在ordery by或group by中使用前缀索引，也不能把他们用作覆盖索引

1
alter table table_name add key(column_name(prefix_length));

联合索引

联合索引又叫复合索引，是在表中两个以上的列上创建的索引，利用索引中的附加表，可以缩小检索的段池范围，更快的搜索到数据。

1
create index idx_c1_c2 on t (c1,c2);  可以用到从索引和c1,c2索引

联合索引使用过程中，必须满足最左前缀原则。一般把选择性高的列放在前面。一条查询语句可以只使用索引的一部分，但必须从最左侧开始。

1687791962554

1687792013621

哈希索引

1687792152768

###索引总结

索引的优点

  • 提高数据检索效率
  • 提高聚合函数效率
  • 提高排序效率
  • 使用覆盖索引可以避免会标

索引创建的四不要

  • 选择性低的字段不要创建索引 （例如sex，status）
  • 很少查询的列不要创建索引
  • 大数据类型字段不要创建索引
  • 尽量避免使用null，应该指定列为not null（在mysql中，含有空值的列很难进行查询优化，他们会使得索引、索引的统计信息及比较运算更加复杂，可以使用空字符串代替空值）

使用不到索引的情况

  • 通过索引扫描的行记录超过全表30%，优化器就不会走索引，而变成全表索引。

  • 联合索引中，第一个查询条件不是最左索引列

  • 联合索引中，第一个索引列使用范围查询，指南用到部分索引，有ICP出现（范围查询是指< 、=、 <=、 between and）

  • 联合索引中，第一个查询条件不是最左前缀列

  • 模糊查询条件列最左以通配符%开始（可以考虑放到子查询里面）

  • 两个单列索引，一个用于检索，一个用于排序。这种情况下只能使用到一个索引。因为查询语句中最多使用一个索引，考虑建立联合索引

  • 查询字段上面有索引，但是使用了函数运算

##事务
事务其实就是一组DML（insert，delete，update）语句的集合。MySQL数据库InnoDB存储引擎支持事务，MyISAM不支持。而且MySQL的事务默认是自提交模式，如果想要开启事务，必须以begin命令开始，以commit或者rollback命令结束。

事务的特性

  1. 原子性
    事务中包含的所有操作要么都做，要么都不做，保证数据库是一致的。
  2. 一致性
    数据库中的数据在事务操作前和事务处理后都必须满足业务规则约束。
  3. 隔离性
    数据库允许多个并发事务同时对数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
  4. 持久性
    事务处理结束后，对数据的修改就是永久的，即使系统发生故障也不会丢失

事务语句

事务的开启语句是由begin 或者start transaction命令来开始的，或者把自提交特性关掉（set autocommit=0命令）事务结束语句通常使用commit或者rollback显示结束。commit代表提交事务，使得已对数据库的所有修改改为永久性。rollback代表回滚事务，撤销正在进行的所有未提交的修改。

1687850088508

1687850169056

truncate和delete的区别

truncate是DDL语句操作，delete是DML语句操作，他们的共同点都是清空表内的数据，但truncate在事务中不能被回滚，而且truncate会清空表的自增属性。

  1. truncate不能回滚，delete可以回滚
  2. truncate清空表的自增id属性，从1重新开始记录，而delete则不会。

1687850797419

1687850857646

1687850872578

事务的隔离级别

MySQL InnoDB存储引擎实现SQL标准的4种隔离级别，用来限定事务内外的哪些改变是可见的，哪些是不可见的。低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。查看当前数据库的隔离级别命令,默认级别是REPEATABLE-READ

1
2
show variable like '%tx_isolation%'
set global|session transaction isolation level

  1. 读未提交（read uncommitted），简称RU，在其中一个事务中，可以读取到其他事务未提交的数据变化。这种读取其他会话还没提交的事务，叫做脏读现象。在生产环境不建议使用。

  2. 读已提交（read committed），简称RC，在其中一个事务中，可以读取到其他事务已经提交的数据变化，这种读取也可以叫做不可重复读，允许幻读现象的发生，是Oracle数据库默认的事务隔离级别，

  3. 可重复读（repetable read），简称RR，它是MySQL默认的事务隔离级别，在其中一个事务中，直到事务结束前，都可以反复读取到事务刚开始时看到的数据，并不会发生变化，避免了脏读。不可重复读和幻读现象的发生。

  4. 串行（serizlizable）在每个读的数据行上都加表级共享锁，在每次写数据时都要加表级排他锁。这就会造成InnoDB的并发能力下降，大量的超时和锁竞争就会发生。不建议使用到生产环境中。

脏读、不可重复读、幻读和可重复读

脏读

脏读是在事务隔离级别读未提交（RU）中出现的现象。一个事务读取到了其他事务还没有提交的数据。

1687854477167

1687854510391

不可重复读与幻读

在其中一个事务中，读取到了其他事务针对旧数据的修改记录（常见操作就是update或者delete）

幻读是指其中一个事务，读取到了其他事务新增的数据，仿佛出现了幻影现象（常见的就是insert语句）。这种读的现象允许出现在读已提交的事务隔离级别中

1687854798875

可重复读

是MySQL数据库默认的事务隔离级别。它消除了脏读、不可重复读、幻读现象，很好的保证了事务的一致性。

1687855020232

1687855062341

锁

简单来说就是为了保证数据库数据的一致性，使共享资源在被并发访问时变得有序而设计的一种规则

InnoDB支持行锁，有时也会升级为表锁，MyISAM只支持表锁

  • 表锁的特点就是开销小、加锁快；锁粒度大，发生锁冲突的概率高。并发度相对低。
  • 行锁的特定就是开销大，加锁慢；会出现死锁；锁粒度小，发生锁冲突的概率低，并发度也相对较高。

InnoDB的锁类型

主要有读锁（共享锁），写锁（排他锁），意向锁和MDL锁。

读锁

读锁简称S锁，一个是为u会的了一个数据行的读锁，其他事务能获得该行对应的读锁，但不能获得写锁，即一个事务在读一个数据行时，其他事务也可以读，但不能对该数据行进行增删改查的操作。

两者select方式，一种就不加任何锁，直接返回结果，这就是一致性非锁定读。一种是通过select lock in share mode 在被读取的行记录的范围加一个读锁，让其他事务可以读，但是想要申请加写锁，那就会被阻塞。

###写锁

写锁简称X锁，一个事务获取了一个数据行的写锁，其他事务就不能再获取该行的其他锁，写锁优先级最高。写锁的应用就很简单了，一些DML语句的操作就会对行记录加写锁

比较特殊的就是select for update，它就会对读取的行记录加一个写锁，那么其他任何事务就不能对被锁定的行加上任何锁了，要不然会被阻塞。

避免死锁的方法

  • 如果不同程序会并发存取多个表，或者涉及多行记录时，尽量约定以相同的顺序访问表，可以大大降低死锁的机会

  • 业务中尽量采用小事务，避免大事务，要及时提交或者回滚事务，可减少死锁产生的概率

  • 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生的概率

  • 对于非常容易产生死锁的业务部分，可以尝试使用升级锁粒度，通过表锁来减少死锁产生的概率

##备份恢复

1687856499872

冷备及恢复

冷备：数据库处于关闭状态下的备份，好处是保证数据库的完整，备份过程简单并且恢复速度相对块一些

热备及恢复

数据库处于运行状态下的备份，不影响现有业务的正常运行。细分为逻辑文件备份和裸文件备份。

------ 本文结束感谢您的阅读 ------
请我一杯咖啡吧！
itingyu 微信打赏 微信打赏