MySQL学习整理-码农进阶之路(一)

最新推荐文章于 2024-04-19 22:16:27 发布

长弓成zerozero

最新推荐文章于 2024-04-19 22:16:27 发布

阅读量209

点赞数 2

分类专栏：数据库文章标签： mysql 数据库

本文链接：https://blog.csdn.net/changgongcheng_yq/article/details/120649477

版权

数据库专栏收录该内容

22 篇文章 0 订阅

订阅专栏

mysql在开源社区孕育至今已经是比较成熟的产品，尤其5.6、5.7版本做了大量的优化，相当于java的6-8，技术的迭代意味着观念需要转变，以前一些不能写的sql在新版本的mysql服务器上也许就有很好的表现。

互联网的底层逻辑是数据和业务，程序的底层逻辑是数据结构和算法，数据库就是互联网大厦的地基，mysql作为我们饭碗的重要组成部分不能不引起重视，了解他的设计思想也对我们日后的编码设计有促进作用。

一：基础概念

1.1 事务ACID

原子性Atomicity：要么都成功、要么都失败

一致性Consistency ：数据逻辑的完整性没有被破坏

隔离性Isolation ：事务之间的修改互不影响，相当于事务锁的粒度，有4个隔离级别

持久性Durability：事务的修改要能持久化到硬盘，一般数据库的持久化实现都是WAL机制(write ahead log)，日志比数据更重要。

1.2 事务隔离级别

READ UNCOMMITTED：未提交读 [脏读/不可重复读/幻读]

READ COMMITTED：提交读[不可重复读/幻读]

REPEATABLE READ：可重复读[幻读(InnoDB引擎在该级别使用MVCC解决了幻读)]

SERIALIZABLE：强制事务串行执行，会在读取的每一行数据上都加锁，导致大量的超时和锁争用

1.3 范式与反范式

范式设计是为了消除冗余，重复的数据更少因此表更小更节约磁盘和内存，dml操作通常更快。

反范式是使用合理的冗余避免表关联，提供更高效的查询效率，可以使用覆盖索引，避免随机IO。

范式总共有六个，此处只列常见的三个：

第一范式：确保列原子性，属性拆分

第二范式：主键完全依赖

第三范式：消除非主键列依赖

1.4 快照读[一致性读]和当前读[锁定读]

快照读：普通select语句

当前读：insert/update/delete/select xxx for update/select xxx lock in share mode

1.5 锁

读锁[共享锁/S锁]、写锁[排他锁/独占锁/X锁]。当前读时InnoDB在二级索引上使用s锁，但访问主键需要x锁

在RR级别下，InnoDB使用MVCC解决了快照读的不可重复读和幻读，使用锁解决了当前读的不可重复读和幻读。

表锁：server层实现，如ALTER TABLE语句就会使用表锁。

行锁：也叫记录锁[record lock]，存储引擎层实现如InnoDB XtraDB

隐式锁和隐式提交：隐式锁是指在事务执行过程中，InnoDB根据隔离级别按需锁定，并在同一时刻释放[commit/rollback]，其中DDL DML语句会触发隐式提交释放隐式锁，即执行该类型语句时会自动提交之前的事务。

显式锁：select xxx for update/select xxx lock in share mode。该锁只对实现了行级锁的引擎有效。

显式锁会导致覆盖索引失效，因为锁定行需要获取行的版本信息[trx_id]存储在主键中，而覆盖索引就是不要回表访问主键。

锁的算法和LBCC[lock-based concurreny control 基于锁的并发控制]：

记录锁/行锁[record lock]，通过索引实现对行的锁定。

间隙锁[gap lock]，对范围记录的锁定，若新插入的记录按B+树排序规则需落入锁定区间内，则需等待锁释放。

临键锁[next-key locking]：InnoDB的行锁实现，使用了record lock 和 gap lock。

意向锁：相当于锁的计数器，来显示表有没有S/X锁的，由InnoDB自行管理。

死锁：

表级锁不会死锁，死锁发生在行锁和间隙锁，通常来说发生在两个事务多条语句之间，两个事务commit前互相锁定对方后续请求的数据行。

InnoDB的有死锁检测机制，当发生死锁时会将持有最少行级x锁的事务回滚(事务代价较小的session)。

老版本的mysql没有死锁检测，死锁会使得连接处于等待状态直到50s锁超时，这样会长时间占用数据库连接导致连接池耗尽，服务不可用。

如下语句，开两个事务依次交替执行两条语句就会发生死锁。

begin;
update tb1 set a = 1 where id = 1; //s1
update tb2 set b = 1 where id = 1; //s2
commit;

show global variables like '%innodb_deadlock_detect%';        //innoDB死锁检测开关默认ON

show global variables like '%innodb_lock_wait_timeout%';        //死锁等待超时时间默认50s

set global innodb_deadlock_detect = off;        //可关闭死锁检测

死锁检测关闭，发生死锁时，两个session连接都会处于等待中直到锁超时，然后提示：
Lock wait timeout exceeded; try restarting transaction
死锁检测开启，发生死锁时，会马上回滚其中一个事务，然后提示：
Deadlock found when trying to get lock; try restarting transaction

解决mysql死锁问题的方式通多线程死锁一样，就是让不同session对资源的加锁有次序，但生产环境发生死锁往往是不同服务不同进程不同线程对某公共表的修改，解决方式只能根据实际情况从业务上规避了，比如上层业务合并重新划分领域、表重新设计使用区分度更高的索引、使用分布式锁等。

1.6 连接查询

驱动表：联表查询的主表，一次查询获取该表的数据，相当于顺序IO

被驱动表：联表查询从表，根据驱动表的扇出[记录次数]查询从表记录，每次相当于随机IO

内连接：[inner] join 驱动表在被驱动表中找不到对应记录，则这条记录不会加入到结果集[相当于取符合条件的交集，因此驱动表和被驱动表顺序是可以交换的]

外连接：left/right [outer] join 驱动表在被驱动表中找不到对应的记录，驱动表的记录也会加入到结果集，查询结果中该被驱动表的记录为null

1.7 排序

排序是mysql性能优化的一个重要方面，数据在B+树中是有序的，如果不需要额外排序查询性能会很好。

排序空间消耗：InnoDB排序时会对每一个字段分配一个足够长的空间，比如VARCHAR(1000) utf8mb4，一个字段需要1000*4+2=4002个字节的空间，尽管列实际可能只存了一个字符。因此mysql用来排序所开辟的临时空间可能远远比我们想象的要大的多。Extra:using filesort

VARCHAR(m)：varchar最大能存65535(2[长度]+1[NULL])，若字符集类型为utf-8(一个字符3字节)，m最大大概为(65535-3)/3=21844。

varchar存储变长字符串，在磁盘上仅使用必要的空间，[使用额外1/2个字节记录长度，最大长度<=255为1否则为2]，排序时会使用m长度字符集类型的最大空间，因此varchar的长度应合理定义。

两次传输排序：1.读取主键[行指针]和排序列字段进行排序，2.排序好后根据主键查询所有数据行

单次传输排序：查询所有列，对查询结果排序。Mysql4.1之后使用该方式，对IO密集型应用友好，避免了二次传输的随机IO[类似回表]，缺点是如果单次查询数据量过大，会占用大量空间。[是内存技术进步后，用内存空间换时间的的策略]

关联查询排序：如果排序的列都来自第一个表，会在查第一个表的时候完成排序，再关联查询后面的表[Extra：using filesort]。否则会先将关联的结果放到临时表中，等关联查询结束后进行排序。[Extra: using temporaty; using filesort]

1.8 索引

索引是帮助MySQL高效获取数据的数据结构，一个索引就是一个B+树，本质上还是空间换时间的策略，对于数据量级非常小的表，简单的全表扫描更高效。索引的列必须是独立的列，即查询时索引列不能是表达式的一部分，也不能是函数的参数。

聚簇索引：InnoDB是基于聚簇索引建立的，表数据就存放在聚簇索引的叶子节点中。

非聚簇索引：也称二级索引，叶子节点存放索引列的值和主键值，一般根据索引列值升序排序。其大小远远小于聚簇索引，相当于一个表的热点数据表，可以帮助减少IO。

联合索引：也称多列索引如index(a,b,c)，首先保证a有序，其次b在a有序的基础上局部有序，c在b的基础上局部有序。

最左前缀：使用索引查询的前提是数据的有序，根据查询条件可以确定扫描区间。如果数据无序则只能全索引表扫描逐个比对，此时无法使用的索引列即“索引失效”，依据上文联合索引的排序规则，b列离开了a列总体上是无序的，无法顺序扫描。

因此一个index(a,b,c)的索引,仅使用(b,c)条件是无法使用当前索引的，使用(a,c)条件可以使用索引但c列不生效，具体表现为：根据a字段确定扫描区间，查询出数据后再使用ICP技术逐个比对c字段，最后把查询结果返回server层。

覆盖索引：一种查询方式即查询列包含在索引列中，仅通过索引就能获取要查询的结果[索引列的叶子节点存放当前列的值和主键id值]。比如index(a,b) select b from tb where a>xx;

回表查询：同覆盖索引，即能使用二级索引但要查询的列不包含在索引列中，需要根据二级索引的主键id去聚簇索引获取数据。比如index(a,b) select a,b,c from tb where a=x and b=xx;

哈希索引：InnoDB内部创建并使用，用户不能干预

全文索引：mysql5.6之后支持，算法机制是倒排索引，业界比较成功的是ES

索引使用原则：索引列尽可能not null[null对于mysql来说也是一个值且定义不明确]，列数据类型尽可能小[varchar(2000)/text/blob这种就不建议]，应选用选择性[离散性/散列度]高的列当索引。比如一个列为sex[男/女]，那么索引扫描数据基本是一半，起不到索引过滤性的作用。
根据《高性能MySQL》一书中提到的技巧，sex类型的字段使用索引的话可以用在联合索引中，当不需要使用该列做筛选时使用in()包含全部的类型，比如index(sex,a,b) select xx from tb where sex in(0,1) and a= xx...

散列度计算：select count(distinct col)/count(*) from tb;

三星索引：

一星：索引将相关的记录放一起[缩小扫描区间]

二星：索引中的数据顺序和查找的排列顺序一致[避免排序]

三星：索引的列包含了查询需要的全部列[宽索引，覆盖索引，优先级最高]

1.9 Mysql其他分支

Drizzle：mysql分支fork出来的，和mysql高度不兼容，设计目标是解决可用性问题，开发语言从C换成C++

MariaDB：原mysql开发大佬独立出来开发的数据库，mysql的扩展集。

Percona Server：数据库领域的性能优化专家，是淘宝使用的mysql版本，《高性能MySql》一书作者所在的Percona公司的数据库，在功能和性能上有显著的提升，很多mysql上的技术功能都是先在这里出现，验证后再移植过去的。
提升了高负载下InnoDB的性能，为DBA提供了非常有用的性能诊断工具，透明化管理mysql。

Postgre Sql：替代产品，高稳定性，数据类型丰富，[加利福利亚大学伯克利分校]，过分学术化不简单易用

SQLite：世界上部署最广泛的数据引擎，物联网