mysql知识点简单小记

连接器

连接器负责跟客户端建立连接、获取权限、维持和管理连接

mysql -h$ip -P$port -u$user -p

输完命令之后，你就需要在交互对话里面输入密码。密码也可以直接跟在 -p 后面(不要加空格)写在命令行中

mysql> show processlist;

其中的 Command 列显示为“Sleep”的这一行，就表示现在系统里面有一个空闲连接,

客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的，默认值是 8 (28800秒)小时

show variables like 'wait_%'

分析器

mysql> select * from T where ID=10；

分析器包含词法分析,语法分析:

所谓词法分析将关键字识别成一个个token,

词法分析是识别一个个的单词，而语法分析就是在词法分析的基础上识别出程序的语法结构,也就是AST抽象语法树

例如:MySQL 从你输入的"select"这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别成“表名 T”，把字符串“ID”识别成“列 ID”

可以感受语法树链接

优化器

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序

执行器

调用 引擎接口读写数据,处理获取的数据

InnoDB 的索引模型

InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的

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

插入几条值,看下索引结构:

主键索引的叶子节点存的是整行数据,

B+tree的数据都存储在叶子节点,

非叶子节点放索引

每个数据页默认是16KB,把数据放在叶子节点,就可以存储更多的索引值,减少层级,加快查询速度

非主键索引的叶子节点内容是主键的值

每次新建一个索引都是建立一颗B+树

这里可以查看了解B+tree添加结构

回表查询:

理解了数据结构就很容易知道啥叫回表,查询普通索引,普通****索引上存储的知识主键id,所以需要其他数据必须通过获取的主键id,到主索引去获取数据

索引覆盖:

覆盖索引是指，索引上的信息足够满足查询请求，不需要再回到主键索引上去取数据。

select id,k from ltq where k in (3,5);

通过普通索引已经可以满足要求,普通索引已经包含id,k 索引就不用回表了,特别适合联合索引

索引下推

CREATE TABLE `tuser` (
  `id` int(11) NOT NULL,
  `id_card` varchar(32) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `ismale` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_card` (`id_card`),
  KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB

联合索引（name, age)

mysql> select * from tuser where name like '张%' and age=10 and ismale=1;

查询到ID3时,不用回表查对应数据,可以直接通过age字段过滤不满足条件的行,减少回表次数

MySQL 常见锁

全局锁

MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock

整个库处于只读状态,数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句,都会阻塞;

使用查看连接状态 show processlist,可以看到,连接阻塞了

使用查看连接状态 show processlist;

show processlist;

可以看到连接处在等待锁的状态,waiting fo global read lock;

解锁:unlock tables;

表级锁

一种是表锁，一种是元数据锁（meta data lock，MDL);

表锁的语法是 lock tables … read/write;

在某个线程 A 中执行 lock tables friend read, is_like write;

其他线程写 friend、读写 is_like 的语句都会被阻塞。

线程 A 只能执行读 friend、读写is_like的操作

另一类表级的锁是 MDL**（metadata lock)**

当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁

• 读锁之间不互斥，可以有多个线程同时对一张表增删改查

• 读写锁之间、写锁之间是互斥的

session A 先启动，会对表 t 加一个 MDL 读锁

session B 需要的也是 MDL 读锁(读锁不互斥)

session C 添加age列,这个时候session A持有读锁还没提交,所以没释放锁

session C 会被 blocked(读写互斥),

读锁不释放,后面sessionD的查询语句也会被阻塞

下面是操作结果图

行锁

MySQL 的行锁是在引擎层由各个引擎自己实现的,MyISAM 引擎就不支持行锁

行锁就是针对数据表中行记录的锁

begin;

开启事务;

执行 update 语句给对应行加上行锁(持有行锁)

当其他线程连接对同一行更新时,就被阻塞,等待行锁

行锁必须等待事务提交后才释放

行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放

如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放

这样可以最大程度的提高并发度

当你在一个大事务中,要尽可能的把更新语句往后放,这样就可以晚开启行锁,事务提交,尽快释放行锁

order by

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB;

select city,name,age from t where city='杭州' order by name limit 1000  ;

全字段排序

Extra 这个字段中的“Using filesort”需要排序，MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer,sort_buffer是在server层

1. sort_buffer是在server层,执行器查看表定义，发现name、city、age字段的长度之和小于max_length_for_sort_data

2. 初始化 sort_buffer，确定放入 name、city、age 这三个字段；

3. 从索引 city 找到第一个满足 city='杭州’条件的主键 id，也就是图中的 ID_X；

4. 到主键 id 索引取出整行，取 name、city、age 三个字段的值，存入 sort_buffer 中；

5. 从索引 city 取下一个记录的主键 id；重复步骤 3、4 直到 city 的值不满足查询条件为止，对应的主键 id 也就是图中的 ID_Y；

6. 对 sort_buffer 中的数据按照字段 name 做快速排序；按照排序结果取前 1000 行返回给客户端。

示意图:

rowid 排序

如果排序的行数据的长度超过 max_length_for_sort_data; 

show variables like '%max_length_for_sort_data%'

修改

SET max_length_for_sort_data = 16;

1. 执行器查看表定义，发现name、city、age字段的长度之和超过max_length_for_sort_data，所以初始化sort_buffer的时候只放入id和name字段。

2. 执行器调用存储引擎的读数据接口，从索引 city 找到第一个满足 city='杭州’条件的主键 id，也就是图中的 ID_X；

3. 到主键 id 索引取出整行，取 name、id 这两个字段，存入 sort_buffer 中；

4. 从索引 city 取下一个记录的主键 id；

5. 重复步骤 3、4 直到不满足 city='杭州’条件为止，也就是图中的 ID_Y；

6. 对 sort_buffer 中的数据按照字段 name 进行排序；

7. 遍历排序结果，取前 1000 行，并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端。

排序过程:

1. 结果集”是一个逻辑概念，

   实际上 MySQL 服务端从排序后的 sort_buffer 中依次取出 id，

   然后到原表查到 city、name 和 age 这三个字段的结果，服务端再耗费内存存储结果，是直接返回给客户端的

MySQL 之所以需要生成临时表，并且在临时表上做排序操作，其原因是原来的数据都是无序的

alter table t add index city_user(city, name);

1. ​加了复合索引之后,数据就是有序的,会先按第一个字段排序,再按第二个字段排序

   1. 从索引 (city,name) 找到第一个满足 city='杭州’条件的主键 id；

   2. 到主键 id 索引取出整行，取 name、city、age 三个字段的值，作为结果集的一部分直接返回；

   3. 从索引 (city,name) 取下一个记录主键 id； 重复步骤 2、3，直到查到第 1000 条记录，或者是不满足 city='杭州’条件时循环结束。

2. 

未加复合索引的,使用了排序

添加(city,name)复合索引后,直接从索引中获取,不用使用排序:

随机查询

随机排序取前 3 个

mysql> CREATE TABLE `words` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `word` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

mysql> select word from words order by rand() limit 3;

可以看到 Extra 字段显示 Using temporary，表示的是需要使用临时表；Using filesort，表示的是需要执行排序操作。

1. 需要使用memory引擎创建临时表 。表里有两个字段，第一个字段是 double 类型，放随机生成数字,第二个字段是 varchar(64) 类型,放word值
2. 从 words 表中，按主键顺序取出所有的 word 值。对于每一个 word 值，调用 rand() 函数生成一个大于 0 小于 1 的随机小数，并把这个随机小数和 word 分别存入临时表
3. 初始化 sort_buffer。sort_buffer 中有两个字段，一个是 double 类型，另一个是整型。从内存临时表中一行一行地取出随机值和主键
4. 分别存入 sort_buffer 中的两个字段里。在 sort_buffer 中根据 随机值 进行排序。排序完成后，取出前三个结果的位置信息，依次到内存临时表中取出 word 值，返回给客户端

随机查询好的方案:

• ​下面是比较好的方案

  随机查询好的方案:

  • 先查询满足的所有行count(*) 记为count；

  • 随机生成三个值 a,b,c；

  • 再执行三个 limit Y, 1 语句得到三行数据。

  mysql> select count() into C from t; Integer a = Math.floor(count rand()); Integer b = Math.floor(count* rand()); Integer c = Math.floor(count* rand()); select * from t limit a; 1；select * from t limit b，1； select * from t limit c，1；

MySQL 中有两个 kill 命令：

• 一个是 kill query + 线程 id；
• 一个是 kill connection + 线程 id;

当你发现有连接死锁,或者大事务占用内存

​show processlist;

查看连接状态,通过id关闭连接