对mysql的总结与反思_深入领会MySQL，一篇简短的总结

MySQL的基本语法

这里作为MySQL部门模块的深入领会，大部门都是理论方面的条记，不会写详细用法。

详细用法会纪录在下面这个随笔分类下，不外暂时还没更新完，等过段时间会更新下事务、存储历程、索引等用法，虽然都很简朴，就当做个完整的条记。

一个关系型数据库的基本模块

以下模块也纷歧定是各大数据库现实的模块，然则都差不多，只是也许领会一下数据库的架构。

除了硬件，也就是存储部门，是由磁盘组成，在软件部门主要分为一下几个模块：

存储治理：用于治理数据格式，把物理数据通过逻辑的形式组织显示出来，即数据现实都是存在物理的磁盘当中，需要在软件层面做一个逻辑上的组织治理。

缓存机制：影响数据库性能的一大问题就是IO，以是会将取出来的数据放入内存当中，使用时直接从内存返回。纵然现在异常快的固态硬盘也远远比不上内存的速率。

SQL剖析：将SQL剖析成机械可读的语言。

日志治理：纪录用户对数据库的操作。

权限划分：字面意思，异常常见的一个功效，将差别的用户分为差别的角色，操作权限也差别。

容灾机制：这个部门较为庞大，也许作用就是当数据库发生异常灾难时该怎么恢复。

索引治理：优化数据库查询效率。

锁治理：使数据库支持并发操作。

Mysql索引的实现，B+树

索引是优化数据库查询效率，通俗的查询是全表查询，当数据量过大时会严重影响性能。

而索引就像一本词典的目录，在数据量较大时会增添查询效率，然则若是频仍的更新或删除数据，同时也需要去维护索引，反而会降低性能，以是索引不宜太多。

索引现实上也是一个文件，既然需要高效的查找固然也需要一个好的数据结构，关于索引的实现，有B树、二叉查找树等，这里只讲MySQL的B+树。

B+树的特点和插入删除历程想过许多文字描述，然则总有点说不清。推荐看看这篇博客，历程图文显示的很清晰。

为什么会选择B+树？

B+树的一个特点就是其叶子结点均有一个链指针指向下一个叶子结点，再加上其是有序的，以是我们举行局限查询时，好比查询>10的数据，只需要先找到10，再直接通过叶子结点的指针就能找到其余数据。

而其他结构还需从根节点出发接着找。

团结索引最左匹配原则

团结索引，有的叫组合索引、有的叫复合索引，叫法无所谓，也许是谁人意思就行。

1.一张内外有字段A、B，当我们需要查询where A=‘xxx’ and B=’xxx’，在这种场景下我们就可以使用团结索引。

而最左匹配原则就是，当建立索引时，语句如下

alter table TABLENAME add index index_name(A,B)

其中A在左边，那么若是我们只查询A时，会用到这个团结索引，而只查询B时，不会用到这个团结索引。

2.另有种情形，在团结索引中，mysql会从左往右匹配，直到遇到>、

好比团结索引中有四个字段A,B,C,D。where A=1 and B=2 and C>3 and D=4。其中ABC会用到索引，而D不会。若是在界说索引时交流C，D的位置,ABCD就都会使用索引

alter table TABLENAME add index index_name(A,B,C,D)-->alter table TABLENAME add index index_name(A,B,D,C)

以是最左匹配原则是依据界说索引时的顺序，查询时顺序若何不影响(由于mysql查询优化器会帮我们优化查询顺序)。

最左匹配原则的原理

索引的底层是B+树，团结索引也一样。然则团结索引特殊的就是有多个值，而构建B+树只需一个值，mysql选择最左的那一个字段当值。

上图是一个团结索引下的B+树，如果字段划分对应(A,B)，可以发现A的值是有序的(1，1，2，2，3，3)，B是无序的(1,2,1,4,1,2)，以是当我们直接查找B=2时是无法通过索引找的。

由于这个B+树是按A的值形成的，B的值完全不相符B+树特征，以是无法单独找到B。

那为先找到A后，就能找到B了？

人人仔细观察这个树，在A相等的区间内，B是有序的。

另有就是，为什么遇到局限查询就住手了。局限查询是针对全表的，而非最左的字段只是区间内有序。

MySQL两种引擎：MyISAM和InnoDB

简短地说

Myisam：是非群集索引，不支持事务，只支持表级锁。

InnoDB：是群集索引，支持事务，默认是行级锁，支持表级锁。

下面就这三个方面逐一说明。

群集索引

界说：数据行的物理顺序与列值(一样平常是主键的那一列)的逻辑顺序相同，一个表中只能拥有一个群集索引。即索引键值的逻辑顺序决议了表中响应行的物理顺序。

网上有个很好的例子，就是把群集索引比作一本字典的拼音目录。而数据就是字典内里的字。拼音目录是凭据一定顺序排列的，那么字典后面的字也一定是凭据拼音的顺序排列的。

当我们在拼音B处插入一个新汉字，那么B后面所有的汉字要向后移动，不可能是加在字典最后面的，由于它得按拼音顺序排列。

以是群集索引适用于：局限查询，好比,=,between等,另有分组group by，由于B+树是有序的，以是分组的效率也更高。

不适用于：频仍更改的列。

非群集索引

界说：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序差别，一个表中可以拥有多个非群集索引。

即索引在一个地方，数据在一个地方，索引带有指向数据的指针。这在物理上也体现出来了，在数据库目录下，索引存在.MYI文件下，数据存在.MYD文件下。上面的群集索引，数据和索引在统一个文件下.ibd。

而数据的顺序和索引纷歧样，以是群集索引适用于：频仍修改索引列。

表级锁和行级锁

两种锁即字面意思。表级锁，mysql中最大粒度获得锁，锁住整张数据表。行级锁，mysql中最小粒度的锁，只锁住操作的那一行，本表中其他行不锁。

而针对差别的操作，锁的详细种别又有所差别，为了不混淆，人人可以把表级锁和行级锁看成锁的局限。而下面说到的锁看成详细的分类。

当Myisam查询时：会在整张表的局限上加上读锁，又叫共享锁。当加上读锁，其他sql的想增删改时就会被壅闭，必须守候查询完毕。又叫共享锁的原因是，可以同时在统一张表内做查询。

当Myisam增删改时：会在整张表的局限上加上写锁，又叫排他锁。当加上写锁，其他sql不论是增删改照样查询都会被壅闭。又叫排他锁的原因是，纵然我修改的是1-10行数据，你查询第11行数据也会被壅闭。

上面说的是MyISAM引擎的情形，InnoDB在读锁、写锁上的逻辑也是一样的，只是锁局限变成了行。

人人在mysql上实验InnoDB的锁时：要注意，InnoDB对select举行了优化，并未对select语句加上读锁，也就是非壅闭select，人人可以在select语句后面加上lock in share mode手动加上读锁。

详细怎么实验：1.往表中插入几百万条的数据，增删改查时在局限内举行，这样就可以模拟壅闭环境了。2.InnoDB支持事务，不外其是自动提交的，还得用set autocommit=0作废自动提交。这样在commit之前就是壅闭状态。

事务

简朴的说，就是使多步操作具有原子性。即在事务内执行多条SQL语句，要么所有乐成，要么所有不乐成，不会存在部门执行乐成，而导致数据纷歧致的情形。

事务具有四大特征：

原子性，上面说到的。

一致性，事务前后数据的完整性必须保持一致。

隔离性，多个用户事务并发举行时，不能相互滋扰。

持久性，一旦事务提供，其修改的效果是永远存在的。

重点说说隔离性。若是事务间没有隔离会发生什么情形呢。

如果有一个场景：事务A获取到一个数据为900，此时另外一个事务B在事务A提交之前就修改了数据到800，并提醒乐成。

而事务A的操作数据依旧是查询时的900，并举行+100，变成了1000。显然效果是纰谬的，这就是更新丢失问题。

这个问题是不是很像多线程并发操作，然则没有锁时就会发生的问题。现实上，事务的隔离就是用锁来实现的。

领会隔离级别之前需要知道几个观点：

脏读：事务A读取到了事务B还未提交的数据。

不可重复读：事务A多次查询数据时，事务B对该数据做了修改并提交，此时事务A发现多次查询前后效果纷歧样。

在我们看来好像没什么问题，一个事务修改了数据，一个事务查询到了修改后的效果。但有个问题就是，事务A是多次查询，若是他没有多次查询，直接在第一次查询的效果上操作，那么是不是就会出现问题。

幻读:事务A查询了一段数据集，事务B修改了事务A的数据集局限内的某些数据，导致查询效果和现实效果纷歧致。

领会了这三个观点后就可以很好的明白几个隔离级别：

隔离级别

脏读

不可重复读

幻读

未提交读(Read uncommitted)

可能

可能

可能

已提交读(Reda committed)

不可能

可能

可能

可重复读(Repeatable read)

不可能

不可能

可能

可串行化(Serializable)

不可能

不可能

不可能

隔离级别越高，安全性越高，性能也越低，以是要凭据现实营业设置差别的隔离级别。

举个例子：若是设置的是可串行化隔离级别，事务A对TableA的1-10行举行操作，事务B纵然对TableA的第11行举行操作也会被壅闭。对于这种营业没必要设置为可串行化隔离级别。

原文链接：https://www.cnblogs.com/lbhym/p/12334582.html

本站声明:网站内容来源于网络,若有侵权,请联系我们,我们将及时处理。