执行一条select语句,期间发生什么?
MySQL架构主要分为两层:Server层和存储引擎
Server 层负责建立连接、分析和执行 SQL。MySQL 大多数的核心功能模块都在这实现,主要包括连接器,查询缓存、解析器、预处理器、优化器、执行器等。另外,所有的内置函数(如日期、时间等)和所有跨存储引擎的功能(如存储过程、触发器、视图等。)都在 Server 层实现。
存储引擎层负责数据的存储和提取。支持 InnoDB、MyISAM、Memory 等多个存储引擎,不同的存储引擎共用一个 Server 层。现在最常用的存储引擎是 InnoDB,从 MySQL 5.5 版本开始, InnoDB 成为了 MySQL 的默认存储引擎。我们常说的索引数据结构,就是由存储引擎层实现的,不同的存储引擎支持的索引类型也不相同,比如 InnoDB 支持索引类型是 B+树 ,且是默认使用,也就是说在数据表中创建的主键索引和二级索引默认使用的是 B+ 树索引。
具体过程:
- 连接器,先连接到服务,连接过程基于TCP连接,连接完成后开始验证用户名密码,如果不正确提示"Access denied for user",如果正确就会获取用户权限,保存起来,之后都会基于连接开始时读到的权限进行逻辑判断。可以用show processlist 查看有多少客户端连接,如果过多则会拒绝接下来的请求;
- 查询缓存,缓存以key-value形式存储,key为sql查询语句,value为查询结果,对于更新比较频繁的表,查询缓存的命中率很低的,因为只要一个表有更新操作,那么这个表的查询缓存就会被清空。如果刚缓存了一个查询结果很大的数据,还没被使用的时候,刚好这个表有更新操作,查询缓冲就被清空了,所以意义不大,8.0以后不会执行缓存阶段;
- 解析SQL,经历词法分析与语法分析,根据规则判断是否可以执行,不可以解析器就会报错;
- 执行SQL,包含三个阶段
- prepare 阶段,也就是预处理阶段;检查表或者字段是否存在,把select * 符号扩展为表上的所有列
- 优化阶段,优化查询语句,选择查询成本最小的执行
- 执行阶段,根据SQL语句进行查询,从存储引擎读取记录,返回给客户端。
什么是索引下推,索引覆盖,全表扫描,主键索引查询?
CHAR和VARCHAR区别
- CHAR是定长存储,VARCHAR是可变长,前者会根据声明的字符串长度分配空间,并使用空格对字符串右边进行尾部填充,所以所以在检索 CHAR 类型数据时尾部空格会被删除,如保存的是字符串 'char ',但最后查询到的是 'char'。因为长度固定,所以查询效率较高。
- 存储方式上前者英文字符占一个字节,汉字使用两个字节,后者对每个字符都用两个字节。
对于经常变更的数据来说,CHAR 比 VARCHAR更好,因为 CHAR 不容易产生碎片。
对于非常短的列或固定长度的数据(如 MD5),CHAR 比 VARCHAR 在存储空间上更有效率。
使用时要注意只分配需要的空间,更长的列排序时会消耗更多内存。
索引
索引是帮助MySQL高效获取数据的排好序的数据结构
从物理结构上来说主要是
聚簇索引:数据与索引放在一起,找到索引即找到数据了;
非聚簇索引:将数据存储于索引分开结构,索引结构的叶子节点指向了数据的对应行,myisam通过key_buffer把索引先缓存到内存中,当需要访问数据时(通过索引访问数据),在内存中直接搜索索引,
优点:
- 保证数据库表每行数据唯一性
- 加快访问速度,提高性能
- 加速表与表之间的连接
缺点:
- 创建和维护索引需要耗费时间,随着数据增加而增加,降低了数据的维护速度
- 占用物理空间,除了表占用空间,每一个索引还要占用
设计原则:
- 唯一性:更快找到所需要的数据
- 用经常作为查询条件的字段进行建立索引
- 限制索引数,每个索引都占用磁盘空间,索引越多,占用空间越多
- 小表不建议
- 尽量使用数据量少的索引,如果索引值很菜,尽量使用前缀索引
数据结构:常用的是Hash索引和B+树索引
- Hash 索引底层就是 Hash 表,进行查询时调用 Hash 函数获取到相应的键值(对应地址),然后回表查询获得实际数据.
- B+ 树索引底层实现原理是多路平衡查找树,对于每一次的查询都是从根节点出发,查询到叶子节点方可以获得所查键值,最后查询判断是否需要回表查询.
数据库事务
概念:
指数据库最小操作单元,操作中可以包含很多子操作,但是要一起向系统提交,要不都执行,要么都不执行,事务是一组不可以分割的操作集合。
ACID
- atomicity 原子性,只做一个步骤,过程保证,全部完成,不可以分割,发生错误会回滚;
- consistency 一致性 保证结果一致;
- isolation 隔离性 并发事务互不干扰;
- durability 持久性 事务一旦提交,接下来操作不对其执行结果有影响;
并发事务会有哪些问题
多个事务并发执行会产生一定的互相争夺资源的问题
- 脏读:一个事务读取了另一个事务未提交的数据,指一个事务在访问数据并修改,但是没有把修改的数据提交,这时候另一个数据来了读到了未提交的事务,这时就是“脏数据”,为脏读,因为还没提交不确定数据是否会有问题。(针对某条数据)
- 修改丢失:两个事务同时对一条数据进行修改,因为同时进行,最后保留了一个结果,第一次修改的结果丢失了(针对某条数据);
- 不可重复读:一个事务在重复读一条数据,同时来了一个事务对此条数据进行修改,可能造成第一个事务读取结果不一致(针对某条数据);
- 幻读:指同一个事务多次查询返回结果集不同,多了或者少量条数,比如开始10条后来变成了15条,主要针对不是每条数据而是表中总数据。
针对上面问题,数据库加锁进行操作,下面介绍事务的隔离级别。
事务的隔离级别
事务隔离级别 | 脏读 | 不可重复读(被修改) | 幻读(删减) |
读未提交(read-uncommitted) | 是 | 是 | 是 |
读已提交(read-committed) | 否 | 是 | 是 |
可重复读(repeatable-read) | 否 | 否 | 是 |
串行化(serializable) | 是 | 是 | 是 |
MySQL默认级别为可重复读。