MySQL -- order by rand
单词表目的:随机选择3个单词
12345678910111213141516171819CREATE TABLE `words` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `word` VARCHAR(64) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;DELIMITER ;;CREATE PROCEDURE wdata()BEGIN DECLARE i INT; SET i=0; WHILE i<10000 DO INSERT INTO words(word) VALUES (CONCAT(CHAR(97+(i DIV 1000)), CHAR(97+(i % 1000 DIV 100)), CHAR(97+(i % 100 DIV 10)), CHAR(97+(i % 10)))); SET i=i+1; END WHILE;END;;DELIMITER ;CALL wdata();
查询语句1SELECT word FROM words ORDER ...
MySQL -- order by
市民信息123456789CREATE TABLE `t` ( `id` INT(11) NOT NULL, `city` VARCHAR(16) NOT NULL, `name` VARCHAR(16) NOT NULL, `age` INT(11) NOT NULL, `addr` VARCHAR(128) DEFAULT NULL, PRIMARY KEY (`id`), KEY `city` (`city`)) ENGINE=InnoDB;
查询语句1SELECT city,name,age FROM t WHERE city='杭州' ORDER BY name LIMIT 1000;
存储过程12345678910111213DELIMITER ;;CREATE PROCEDURE idata()BEGIN DECLARE i INT; SET i=0; WHILE i<4000 DO INSERT INTO t VALUES (i,'杭州',concat('zhongmingmao ...
MySQL -- 无过滤条件的count
count(*)实现
MyISAM:将表的总行数存放在磁盘上,针对无过滤条件的查询可以直接返回
如果有过滤条件的count(*),MyISAM也不能很快返回
InnoDB:从存储引擎一行行地读出数据,然后累加计数
由于MVCC,在同一时刻,InnoDB应该返回多少行是不确定
样例假设表t有10000条记录
session A
session B
session C
BEGIN;
SELECT COUNT(*) FROM t;(返回10000)
INSERT INTO t;(插入一行)
BEGIN;
INSERT INTO t(插入一行);
SELECT COUNT(*) FROM t;(返回10000)
SELECT COUNT(*) FROM t;(返回10002)
SELECT COUNT(*) FROM T;(返回10001)
最后时刻三个会话同时查询t的总行数,拿到的结果却是不同的
InnoDB默认事务隔离级别是RR,通过MVCC实现
每个事务都需要判断每一行记录是否对自己可见
优化
InnoDB是索引组织表
聚簇索引树:叶 ...
MySQL -- 空间回收
InnoDB的物理存储
InnoDB表的组成:表结构(frm)+数据(ibd)
MySQL 8.0开始,允许将表结构定义(占用空间很小)放在系统数据表中
控制参数innodb_file_per_table
ON:每个InnoDB表数据存储在一个以**.ibd为后缀的文件中,推荐**
更容易管理,DROP TABLE会直接删除这个文件
OFF:InnoDB表数据存储在共享表空间
DROP TABLE,空间也是不会回收的
123456mysql> SHOW VARIABLES LIKE '%innodb_file_per_table%';+-----------------------+-------+| Variable_name | Value |+-----------------------+-------+| innodb_file_per_table | ON |+-----------------------+-------+
文件空洞空洞:可以被复用但没有被使用的空间,经过大量增删改的表,都会存在空洞
删除
如果删掉R4, ...
MySQL -- 字符串索引
场景建表12345CREATE TABLE SUser( id BIGINT UNSIGNED PRIMARY KEY, name VARCHAR(64), email VARCHAR(64)) ENGINE=InnoDB;
查询1SELECT id,name,email FROM SUser WHERE email='[email protected]';
创建索引12ALTER TABLE SUser ADD INDEX index1(email);ALTER TABLE SUser ADD INDEX index2(email(6));
index1
索引长度:整个字符串
从index1索引树找到第一个满足索引值为zhangssxyz@xxx.com的记录,取得主键为ID2
到聚簇索引上查找值为ID2的行,判断email的值是否正确(Server层行为),将该行记录加入结果 ...
MySQL -- flush
脏页 + 干净页
脏页:内存数据页与磁盘数据页内容不一致
干净页:内存数据页与磁盘数据页内容一致
flush:将内存中的脏页写入磁盘
flush – 刷脏页;purge – 清undolog;merge – 应用change buffer
flush过程
触发flushredolog写满
当InnoDB的redolog写满,系统会停止所有的更新操作,推进checkpoint
把checkpoint从CP推进到CP’,需要将两点之间的日志(绿色),所对应的所有脏页都flush到磁盘上
然后write pos到CP’之间(红色+绿色)可以再写入redolog
性能影响InnoDB应该尽量避免,此时所有更新都会被堵住,更新数(写性能)跌为0
内存不足
当需要新的内存页,而内存不够用时,就需要淘汰一些内存数据页(LRU)
如果淘汰的是脏页,就需要先将脏页flush到磁盘
该过程不会动redolog,因为redolog在重放的时候
如果一个数据页已经flush过,会识别出来并跳过
性能影响
这种情况是常态,InnoDB使用buffer pool管理内存
buffer pool中内存页有3种状态
没 ...
MySQL -- 索引选择
优化器
优化器的重要职责:_选择索引_
目的是寻找最优的执行方案
大多数时候,优化器都能找到正确的索引
在数据库里面,决定执行代价的因素
扫描行数 – 本文关注点
是否使用临时表
是否排序
MySQL在真正开始执行语句之前,并不能精确地知道满足条件的记录有多少
只能根据统计信息(索引的区分度)来估算记录数
基数越大(不同的值越多),索引的区分度越好
统计信息中索引的基数是不准确的
12345678mysql> SHOW INDEX FROM t;+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Nu ...
MySQL -- 普通索引与唯一索引
场景
维护一个市民系统,有一个字段为身份证号
业务代码能保证不会写入两个重复的身份证号(如果业务无法保证,可以依赖数据库的唯一索引来进行约束)
常用SQL查询语句:SELECT name FROM CUser WHERE id_card = 'XXX'
建立索引
身份证号比较大,不建议设置为主键
从性能角度出发,选择普通索引还是唯一索引?
假设字段k上的值都不重复
查询过程
查询语句:SELECT id FROM T WHERE k=5
查询过程
通过B+树从树根开始,按层搜索到叶子节点,即上图中右下角的数据页
在数据页内部通过二分法来定位具体的记录
针对普通索引
查找满足条件的第一个记录(5,500),然后查找下一个记录,直到找到第一个不满足k=5的记录
针对唯一索引
由于索引定义了唯一性,查找到第一个满足条件的记录后,就会停止继续查找
性能差异
性能差异:微乎其微
InnoDB的数据是按照数据页为单位进行读写的,默认为16KB
当需要读取一条记录时,并不是将这个记录本身从磁盘读出来,而是以数据页为单位进行读取的
当找到k=5的记录时,它所在的数据页都已经在 ...
MySQL -- RR隔离与RC隔离
视图
虚拟表 – 本文不关心
在调用的时候执行查询语句并生成执行结果
SQL语句:CREATE VIEW
InnoDB在实现MVCC时用到的一致性读视图(consistent read view)
用于支持RC和RR隔离级别的实现
没有对应的物理结构
主要作用:在事务执行期间,事务能看到怎样的数据
快照
在RR隔离级别下,事务在启动的时候保存了一个快照,快照是基于整库的
在InnoDB,每个事务都有一个唯一的事务ID(transaction id)
在事务开始的时候向InnoDB的事务系统申请的,按申请的顺序严格递增
每行数据都有多个版本,每次事务更新数据的时候,都会生成一个新的数据版本
事务会把自己的transaction id赋值给这个数据版本的事务ID,记为row trx_id
每个数据版本都有对应的row trx_id
同时也要逻辑保留旧的数据版本,通过新的数据版本和undolog可以计算出旧的数据版本
多版本
虚线框是同一行记录的4个版本
当前最新版本为V4,k=22,是被transaction id为25的事务所更新的,因此它的row trx_id为25
...
MySQL -- 锁
全局锁
全局锁:对整个数据库实例加锁
加全局读锁:FLUSH TABLES WITH READ LOCK,阻塞其他线程的下列语句
数据更新语句(增删改)
数据定义语句(建表、修改表结构)
更新类事务的提交语句
主动解锁:UNLOCK TABLES
典型使用场景:全库逻辑备份
把整库每个表都SELECT出来,然后存成文本
缺点
如果在主库上执行逻辑备份,备份期间不能执行更新操作,导致业务停摆
如果在备库上执行逻辑备份,备份期间从库不能执行由主库同步过来的binlog,导致主从延时
备份加全局锁的必要性
保证全局视图是逻辑一致的
mysqldump
--single-transaction
导数据之前启动一个事务,确保拿到_一致性视图_
由于MVCC的支持,在这个过程中是可以正常更新数据的
需要存储引擎支持_RR的事务隔离级别_
MyISAM不支持事务,如果备份过程中有更新,总是能取到最新的数据,破坏了备份的一致性
因此MyISAM只能依赖于FLUSH TABLES WITH READ LOCK,不能使用--single-transaction
针对全库逻辑备份的场景,--singl ...