MySQL 相关学习整理

最新推荐文章于 2024-11-29 14:25:36 发布

spyker_wy

最新推荐文章于 2024-11-29 14:25:36 发布

阅读量134

点赞数

文章标签： mysql

本文链接：https://blog.youkuaiyun.com/weixin_43320440/article/details/115409056

版权

MySQL 基础整理

前言

整理一下最近在学习的MySQL 相关的一些基础知识以便未来复习

一、一条SQL的查询语句是如何执行的？

比如你有一个简单的表表里又一个ID字段在执行

mysql>select * from T where ID = 1;

当执行这条sql语句带返回查询结果其中的过程是怎么的呢？

首先MySQL可以分为两部分一部分是 Server层一部分是存储引擎层

server层包括包括连接器缓存解析器优化器执行器等
存储引擎层主要就是存储数据提供读写接口 MySQL引擎主要有InnoDB，Memory，MyISAM等不同的存储引擎的表数据存储方式都不同，支持的功能也不同。会按照功能进行选择，在没有指定选择引擎时，在MySQL5.5版本之后默认使用InnoDB引擎。

1. 连接器

第一步是先连接到这个数据库，连接数据库的时候就用到了连接器。连接器就是负责跟客户端建立连接，获取权限，维持和管理连接的。

2.查询缓存

建立1连接之后就可以正常的执行select语句了 MySQL拿到一个查询请求后，会先到查询1缓存中看一下是否是这个缓存，如果你的查询能在缓存中找到相应的key，则对应的value值会直接返回给客户端。
查询缓存不建议使用的原因是：
只要有对一个表的更新，这个表的所有查询缓存都会被清空。查询缓存的失效非常的频繁。在MySQL8.0版本之后查询缓存的功能就被删除掉了。

3.分析器

分析器主要是识别SQL语句让MySQL知道你现在在做什么。

首先分析器会先做词法分析。识别你输入的字符串分别是什么代表1什么以上面那个select查询为例：MySQL会从你输入的select关键字识别出来这是一个查询语句，把字符串T识别出表名 T 把字符串ID 识别成列Id
语法分析：根据词法分析的结果来通过语法分析器，判断输入的SQL是否满足MySQL的语法。如果你的语法不对，就会收到“You have an error in your SQL syntax”的错误提醒。

4.优化器

优化器主要处理两部分内容一个是决定使用哪个索引，一个是决定联表时各个表的连接顺序。

ps：具体如何选择索引会后续进行补充。

5.执行器

执行器简单的来说就是要开始真正的执行SQL语句了

判断操作人是否对所查的表T有权限。如果没有返回ERROR 1142 (42000): SELECT command denied to user ‘b’@‘localhost’ for table 'T’的错误提示。
如果有权限。会根据表的引擎定义调用相应引擎的接口。

二、一条SQL的更新语句是如何执行的？

上面说了一个查询语句的执行过程一个更新语句和查询语句的过程大体类似只是加入了 binlog 如果是InnoDB 还会加入redo log的操作具体如下：

mysql> create table T(ID int primary key, c int);

mysql> update T set c=c+1 where ID=2;

1.更新操作也会完成Server层里连接器缓存分析器优化器执行器这些部分

2.执行器阶段会追加一个日志的模块

MySQL自带的日志模块是binlog 所有存储引擎都可以进行使用，常用的InnoDB引擎还有一个redo log日志模块。

拿到需要查询的数据之后把c的值改为c+1
调用引擎API接口写入这一行数据
InnoDB会把数据保存在内存中，同时记录redo log 此时的redo log进入prepare状态。之后通知执行器，已经执行完可以进行提交。
执行器收到通知后会记录binlog 然后调用1引擎api接口提交redo log为提交状态。
完成更新操作。

具体日志的处理过程可参考
redolog binlog 详解

为什么InnoDB需要用两个日志模块。主要是因为InnoDB需要用redo log来支持事务的操作

redo log的prepare状态和提交状态主要是实现 redo log和binlog的数据的一致性

三、MySQL索引

索引是MySQL中很重要的部分索引可以提高数据查询的效率就像书的目录也一样索引的数据结构有hash 二叉树 N叉树 B+树现在最常用的InnoDB引擎用的就是B+树做的底层数据结构。在InooDB中表都是根据主键顺序以索引的形式存放的，这种存储方式的表叫做索引组织表。
所有数据都是存在InnoDB上的 每一个索引在InnoDB里面都对应一颗B+树。

下面主要介绍几种常用的索引类型。

1.主键索引(聚簇索引)和非主键索引(二级索引)

主键索引的叶子结点存储的是整行数据。非主键索引的叶子结点内容是主键的值。
主键索引和非主键索引查询过程的区别：

mysql> create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k))engine=InnoDB;

select* from T where ID=500,这是用主键索引进行的查询，只需要搜索ID这可B+树。
select* from T where K=1,这里用的就是非主键索引，需要先搜索K的B+索引树得到ID的值，之后再到ID的B+索引树中在搜索一遍。这个过程就称为回表。
也就是说非主键索引的查询会多扫描一颗索引树。

2.联合索引

联合索引，就如同其名。就是多个索引共同建立索引，但是建立联合索引的时候要考虑索引的先后顺序，因为索引又一个最左前缀原则，下面会针对最左前缀原则进行详细讲解。

3.覆盖索引

覆盖索引，顾名思义就是把主键索引覆盖进查询条件。覆盖索引的目的其实就是尽量减少一次回表的操作，减少多查询一棵树带来的额外开销。
举个例子：
select ID from T where k betwwen 3 and 5;这个查询因为只需要查ID的值，而Id的值就已经在B+索引树的叶子结点上了。所以就不需要回表操作。

覆盖索引一般都配合联合索引共同使用。例如：
当我们查询用户信息，可以直接在身份证号上添加索引，就可以满足相应的查询。那么还有必要做身份证号和姓名的联合索引吗。如果有一个高频请求，需要通过身份证号查询对应的姓名，这时候这个联合索引就有意义了，他可以在这个高频请求上用到覆盖索引，避免了回表的操作，减少语句的执行时间。

4.最左前缀原则

5.索引下推

6.InnoDB中索引的数据结构

为什么选择B+树做存储索引的数据结构

7.什么时候索引会无效

因为sql执行的时候优化器会选择最优的索引或者选择不使用索引所以有些情况会导致索引无效。还有一些特殊原因可能会导致索引无效化。下面列举了索引无效的情况：

当用or时 or会使索引失效除非or两边用的是一个索引条件例：
索引a，b
select* from a=1 or a=2; 索引有效
select* from a=1 or b=2; 索引失效
使用联合索引(a,b) select * from T where b=1 and a=1 这时候a的索引是有效的 b的索引是无效的
模糊查询 like 以%开头
优化器内分析用索引可能没有全表查询快的时候也会选择不用索引
如果列类型是字符串要使用引号不然索引失效
where语句中有<> != 时不会使用索引
where语句中使用not in 不会使用索引
应尽量避免对字段进行null值判断容易导致引擎会放弃索引使用全表扫描
参考：索引失效

8.为什么要用自增Id做主键索引

相信大家总会在各种资料中看见，建议使用自增Id来做主键索引，为什么要用自增id做主键索引呢，比如用户表用手机号或者生份证号做索引不可以吗

B+树为了维护索引的有序性，在插入新值的时候会做必要的维护。如果插入的数值不是现存记录中最大的，就会导致比这个数值大的数据进行挪动。举个例子：假如上面建的的表中ID有300 600 700三个数据。当你插入200这个数据的时候 300 600 700 这三个数据的位置都需要往后挪动一位。
如果你插入的这个数据所在位置的数据页已经满了，根据B+树的算法这时候你需要申请一个新的数据页，然后挪动部分数据过去。这个过程就称为也分裂。影响性能。除了性能外，页分裂操作还会影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体利用率降低大约百分之50.
当相邻的两个页由于删除数据导致利用率很低之后，会可能对数据页做合并操作，合并过程就基本是页分裂的逆过程。

自增主键就可以避免上面的问题的发生。每次插入操作都是追加操作，都不涉及挪动其他记录，也就不会触发叶子结点的分裂。
从存储空间上来看因为每个非主键索引的叶子结点都有主键的值。如果身份证号做主键，每个二级索引的叶子节点占用用20个字节。而如果整型做主键只需要4个字节，长整型也只需要8个字节。 显而易见，主键长度越小，二级索引的叶子结点就越小，二级索引占用的空间就越小。

当然也有场景可以用身份证号来做索引如下例子：
业务需要只有一个索引，该索引必须是唯一索引。
因为没有其他索引所以也就不用考虑叶子节点大小的问题。尽量使用主键查询，直接把这个索引设置为主键，可以避免查询回表导致搜索两颗树。