Mysql

2021-08-20 15:29:47   13  举报





AI智能生成

MySQL是一种开源的关系数据库管理系统，它使用结构化查询语言（SQL）来管理数据。它是最流行的关系数据库管理系统之一，在WEB应用方面，MySQL是最好的RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一。 MySQL所使用的SQL语言是访问数据库的最常用标准化语言。MySQL软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发选择MySQL作为网站数据库。

mysql查询优化

作者其他创作

大纲/内容

Innodb数据结构

数据页与数据行

https://www.cnblogs.com/pjjlt/p/10940268.html

最左匹配原则

https://www.cnblogs.com/lanqi/p/10282279.html

MySQL的锁

当数据库有并发事物的时候，可能会产生数据的不一致，这时候需要一些机制来保证访问的次序，锁机制就是这样的一个机制

MVCC

MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读

数据事务的实现原理

我们这里以 MySQL 的 InnoDB 引擎为例来简单说一下。

MySQL InnoDB 引擎使用 redo log(重做日志) 保证事务的持久性，使用 undo log(回滚日志) 来保证事务的原子性。

MySQL InnoDB 引擎通过锁机制、MVCC 等手段来保证事务的隔离性（默认支持的隔离级别是 REPEATABLE-READ ）。

保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。

什么是索引

索引是一种数据结构，可以帮助我们快速的查找数据

索引改变数据存储结构

们平时建表的时候都会为表加上主键，在某些关系数据库中，如果建表时不指定主键，数据库会拒绝建表的语句执行。事实上，一个加了主键的表，并不能被称之为「表」。一个没加主键的表，它的数据无序的放置在磁盘存储器上，一行一行的排列的很整齐，跟我认知中的「表」很接近。如果给表上了主键，那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构，也就是上面说的「平衡树」结构，换句话说，就是整个表就变成了一个索引。没错，再说一遍，整个表变成了一个索引，也就是所谓的「聚集索引」。这就是为什么一个表只能有一个主键，一个表只能有一个「聚集索引」，因为主键的作用就是把「表」的数据格式转换成「索引（平衡树）」的格式放置。https://blog.csdn.net/sxb0103/article/details/91488713

索引是个什么样的数据结构呢

索引的数据结构和具体存储引擎实现有关，在mysql中使用较多的的索引有Hash索引，B+树索引等，而我们经常使用的InnoDB存储引擎的默认索引为B+树索引

Hash索引和B+树索引索引有什么区别

Hash索引和B+树索引的底层实现原理

Hash索引底层就是Hash表，进行查找时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获取得到实际的数据；B+树索引底层实现是多路平衡二叉树，对于每一次查询都是从根节点出发，查到叶子节点方可获得所查键值，然后根据查询判断是否需要回表查询数据。

不同點

Hash索引进行等值查询跟快，但是没法进行范围查询，因为在Hash索引过程中经过hash函数建立索引之后，索引的顺序与原顺序无法保持一致，不支持范围查询，而B+树的所有节点皆遵循左节点小于父节点，右节点大于父节点的规则，天然支持范围查询

Hash索引不支持使用索引进行排序

Hash索引不支持模糊查询以及多列索引的最左前缀匹配，原理也是因为Hash函数的不可预测AAA和AAAB有相关系

hash索引虽然在等值查询上较快,但是不稳定.性能不可预测,当某个键值存在大量重复的时候,发生hash碰撞,此时效率可能极差.而B+树的查询效率比较稳定,对于所有的查询都是从根节点到叶子节点,且树的高度较低.

什么是B+树

B+树是通过二叉查找树，再由平衡二叉树，B树演化而来

B+树在满足聚簇索引和覆盖索引的时候不需要回表查询，什么是聚簇索引？

在B+树的索引中，叶子节点可能存储了当前的key值，也可能存储了当前的key值以及整行的数据，这就是聚簇索引和非聚簇索引；
在InnoDB中，只有主键索引是聚簇索引。聚簇索引（Clustered indexes）是指索引和数据是在同一个结构里

常用索引

普通索引INDEX:加速查找

唯一索引:主键索引PRIMARY KEY:加速查找+约束（不为空、不能重复）

联合索引:PRIMARY KEY(id,name):联合主键索引
UNIQUE(id,name):联合唯一索引
INDEX(id,name):联合普通索引

非聚簇索引一定会回表查询吗?

不一定,这涉及到查询语句所要求的字段是否全部命中了索引,如果全部命中了索引,那么就不必再进行回表查询.
举个简单的例子,假设我们在员工表的年龄上建立了索引,那么当进行select age from employee where age < 20的查询时,在索引的叶子节点上,已经包含了age信息,不会再次进行回表查询.

在建立索引的时候,都有哪些需要考虑的因素呢

首先要考虑到字段使用的频率，经常作为条件查询的字段比较适合创建索引

创建的索引有没有被使用到?或者说怎么才可以知道这条语句运行很慢的原因

MySQL提供了explain命令来查看语句的执行计划,MySQL在执行某个语句之前,会将该语句过一遍查询优化器,之后会拿到对语句的分析,也就是执行计划,其中包含了许多信息. 可以通过其中和索引有关的信息来分析是否命中了索引,例如possilbe_key,key,key_len等字段,分别说明了此语句可能会使用的索引,实际使用的索引以及使用的索引长度.

索引失效的情况有哪些

1、使用不等于查询
2、列参与了数学运算或者函数
3、在字符串like的时候左边的通配符使用类似'%aaa'
4、当使用联合索引,前面一个条件为范围查询,后面的即使符合最左前缀原则,也无法使用索引

ACID(数据库事务)

A=Atomicity

原子性,就是上面说的,要么全部成功,要么全部失败.不可能只执行一部分操作.

C=Consistency

一致性,系统(数据库)总是从一个一致性的状态转移到另一个一致性的状态,不会存在中间状态.

I=Isolation

隔离性: 通常来说:一个事务在完全提交之前,对其他事务是不可见的.注意前面的通常来说加了红色,意味着有例外情况.

D=Durability

持久性,一旦事务提交,那么就永远是这样子了,哪怕系统崩溃也不会影响到这个事务的结果.

同时有多个事务在进行会怎么样呢

脏读

A事物读取到了B事物未提交的内容，而B事物后面进行了回滚

幻读

A事务读取了一个范围的内容,而同时B事务在此期间插入了一条数据.造成"幻觉"

不可重复读

当设置A事务只能读取B事务已经提交的部分,会造成在A事务内的两次查询,结果竟然不一样,因为在此期间B事务进行了提交操作.

修改丢失

丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。例如：事务 1 读取某表中的数据 A=20，事务 2 也读取 A=20，事务 1 修改 A=A-1，事务 2 也修改 A=A-1，最终结果 A=19，事务 1 的修改被丢失。

什么是事务

事务是逻辑上的一组操作，要么都执行，要么都不执行。

怎么解决这些问题呢?MySQL的事务隔离级别了解吗

未提交读

这就是上面所说的例外情况了,这个隔离级别下,其他事务可以看到本事务没有提交的部分修改.因此会造成脏读的问题(读取到了其他事务未提交的部分,而之后该事务进行了回滚).

已提交读

其他事务只能读取到本事务已经提交的部分.这个隔离级别有不可重复读的问题,在同一个事务内的两次读取,拿到的结果竟然不一样,因为另外一个事务对数据进行了修改.

可重复读

可重复读隔离级别解决了上面不可重复读的问题(看名字也知道),但是仍然有一个新问题,就是幻读,当你读取id> 10 的数据行时,对涉及到的所有行加上了读锁,此时例外一个事务新插入了一条id=11的数据,因为是新插入的,所以不会触发上面的锁的排斥,那么进行本事务进行下一次的查询时会发现有一条id=11的数据,而上次的查询操作并没有获取到,再进行插入就会有主键冲突的问题.

可串行化

这是最高的隔离级别,可以解决上面提到的所有问题,因为他强制将所以的操作串行执行,这会导致并发性能极速下降,因此也不是很常用.

Innodb使用的是哪种隔离级别呢

可重复读