MYSQL-笔记

• MYSQL-笔记
  • 简介
  • 数据
    • 数据类型&函数
    • 字符集&比较集
    • SQL语句
  • 结构
    • 索引
    • 锁
    • MVCC
    • explain
  • 连接池
    • 链接参数
    • 集群
    • 读写分离&分表
    • 日志
    • 储存分区
  • Interview
    • 倒排索引
    • 回表查询-索引覆盖
    • 最左匹配原则
    • like查询索引/while not
    • 索引下推
    • 索引数据结构
    • 自增主键
    • binlog-redolg-undolog
    • sql优化
    • 存储计算
    • interview
  • ps

简介

mysql是一个单进程+多线程的数据库(对比redis,单进程+单线程);

innodb,MyISAM对比

show engines;--查看全部engins

• MyISAM: 支持表锁,不支持事务和FK;
• InnoDB: 支持行锁,支持事务,支持FK; MVCC实现高并发

Innodb事务的4个等级:

默认 => REPEATABLE READ(RR)

• READ UNCOMMITTED; 脏读; 没有使用MVCC,能读到所有未提交数据
• READ COMMITTED; 不可重复读; 没有MVCC,所有提交数据都可读, (RC + MVCC)
• REPEATABLE READ; 没有并发问题; 使用了MVCC解决 => 解决不可重复读, 使用区间锁 => 解决幻读,(RR + MVCC + GapLock)
• SERIALIZABLE ; 没有并发问题; 不适合MVCC,完全排他

单机事务的属性ACID

单机事务 vs 分布式事务

数据

数据类型&函数

float|double|decimal

• float占4个字节; double占8个字节;
• DECIMAL(M,D): mysql vs tidb => 金额精度问题

text|blob

text存储长文本;blob可以存储bytes(小图片:头像)

• 建议垂直拆分表单独存储
• 建立索引: 1.col_hash=md5(txt), 2, 使用前缀

INET_ATON|INET_NTOA

存储IP问题:其实建议直接varchar就可以

• INET_ATON(str),inet_aton('127.0.0.1')
• INET_NTOA(number):number to address

DATETIME|DATE|TIMESTAMP

• dev.mysql.com/doc/refman/…

• DATETIME: MySQL以'YYYY-MM-DD HH:MM:SS'格式检索与显示DATETIME类型,支持的范围是'1000-01-01 00:00:00'到'9999-12-31 23:59:59'

• DATE,用于显示日期,MySQL以'YYYY-MM-DD'格式检索与显示DATE值,支持的范围是'1000-01-01'到'9999-12-31'

• TIME: DATE + TIME = DATETIME

• TIMESTAMP: 完整TIMESTAMP格式是14位;在创建表时指定一个任意的显示尺寸，但是定义列长为0或比14大均会被强制定义为列长14。列长在从1～13范围的奇数值尺寸均被强制为下一个更大的偶数

  • TIMESTAMP(0) -> TIMESTAMP(14)
  • TIMESTAMP(15)-> TIMESTAMP(14)
  • TIMESTAMP(1) -> TIMESTAMP(2)
  • TIMESTAMP(5) -> TIMESTAMP(6)

• 推荐Datetime,没有时区变化;TIMESTAMP存在时区变化

• 都可以控制精度到毫秒

-- 自动插入|更新
created=DEFAULT CURRENT_TIMESTAMP
updated=DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

字符集&比较集

show character set; -- 查看所有charset/collate

charset	description	collate	maxLen
utf8mb4	UTF-8 Unicode	utf8mb4unicodeci	4
utf8	UTF-8 Unicode	utf8_general_ci	3

utf8与utf8mb4异同?

推荐utf8mb4; mb4即most bytes 4。简单说utf8mb4是utf8的超集并完全兼容utf8，能够用四个字节存储更多的字符,能够存储emoji;utf8最大支持3个字节.

utf8mb4unicodeci与utf8mb4generalci选择

推荐utf8mb4_unicode_ci,准确性更高

SQL语句

• 查询时的索引字段不能出现在公式中,否则索引无效 => select * from user where id+1 = 20;
• like不以通配符开头,否则索引无效=> select * from article where title like 'mysql%';
• 复合索引,左边字段等值: => index(p1,p2); select * from where p1 ='2';最左匹配规则
• select * 尽量不要使用,系统需要先依次处理列名,避免回表查询

# 查看用户权限：user
select host,name,password from user;
create user myname@‘%’ IDENTIFIED  by ‘mypasswd’;
GRANT ALL ON *.* TO myname'@'%’; -- show grants for myname; 
# 删除表
drop table if exists tb_name;  
# 删除重复数据:一条SQL
DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE  X.EMP_NO = E.EMP_NO);
# 查询mysql的进程
select id,time,info,USER from information_schema.processlist where command != 'Sleep' order by time desc limit 21702;
# 查看table的大小; use information_schema;
select table_name, round(sum(DATA_LENGTH/1024/1024),2) as 'MB', DATA_LENGTH,MAX_DATA_LENGTH,INDEX_LENGTH,TABLE_ROWS from TABLES where TABLE_SCHEMA = 'payment' group by table_name;

结构

索引

Btree(是balance-tree不是binary-tree)..

索引的数据结构

三种类型: Hash、BTree、BTree+(default);

1. Hash:查询修改复杂度O(1),适合等值查询场景,空间利用率低,%50左右,但是不适合范围查找,(>,<,between-and),索引不能一次全部加载.
2. Btree:查询修改复杂度O(logn),空间利用率高,一次可加载全部索引到内存,支持分段查找
3. Btree+:所有非叶子节点数据页出现在叶子节点中(叶子节点包含所有数据)，叶子节点是双琏表,支持范围查找
4. SkipList:

树的相关的数据结构

二叉树(binary tree搜索树),平衡数,红黑树,B树,B+数,最大堆/最小堆

• 二叉树(binary tree,搜索树):数组的排序,查找问题
• 平衡树:极限的二叉树会变为有序的琏表(所有节点只有左/右子树),为了解决极限二叉树问题,引入平衡树(子树高度的最大差=1),降低数的高度,便于查找O(logn)
• 红黑树,是一种平衡树,java中的TreeSet,HashMap的rehash结构
• B树(balance-tree): 二叉树扩展为多路,就是B树,更加降低数的高度,一次加载全部的索引排序关系到内存,节省内存空间,linux文件系统的索引结构
• B+树,b树+双琏表,便于select的批量查找; 文件系统采用了B树, 数据库索引采用了B+树,文件系统一般不会范围查询
• 堆: top-k问题的最好解答，既能节省空间又不用整体排序。

mysql中Btree+实现的索引类型:聚集索引和辅助索引

• 聚集索引和普通索引底层的数据结构都是btree+
• 聚集索引:一个数据表按主键id(PK)构建Btree+,每个表只有一个聚集索引,数据行(表的row)以数据页存在叶子节点,一个数据页可以存储多个表行,数据页的行数据可以根据id二分查找.row => page => node
• 普通索引:根据指定索引构建Btree+,叶子节点包含{索引值,主键id(PK)},用于回表查询。
• 节点大小是数据页的整数倍(1),便于查询速度

mysql通过倒排索引 实现文本检索。sql语句使用match,against函数

DDL索引修改 缺点:操作影响性能,索引操作对表加锁,会停止DML语言的执行

• 迁移数据: 新建表tb_tmp->同步数据-> 修改tb_tmp -> rename tb_tmp to tb_dst
• rename 修改表DDL

锁

行锁算法

• Record-Lock
• Gap-Lock(锁定范围,防止更新期间其他事务插入)
• Next-Key-Lock(gap的边界)

InnoDB行锁时通过给索引上的索引项加锁来实现的，意味着只有通过索引条件检索条件数据，InnoDB才使用行锁，否则InnoDB将使用表锁。 锁加在索引上; 否则锁全表;

加锁的规则:

1. 锁加在索引上,非索引字端不加锁
2. 非索引字端锁全表
3. union临时表与原始表之间的存在关联;只有查询结束才释放锁;

MVCC

Multi-Version Concurrency Control(多版本并发控制)mysql处理并发方式; MVCC只工作在READ COMMITED,REPEATABLE READ级别

-- 查看innodb所有的信息
show engine innodb status;

1. DB_TRX_ID:一个6byte的标识,每处理一个事务,其值自动+1 如insert、update、delete操作时更新;
2. DB_ROLL_PTR: 大小是7byte,指向写到rollback segment（回滚段）的一条 undo log记录（update操作的话，记录update前的ROW值）
3. DB_ROW_ID: 大小是6byte(4,8),该值随新行插入单调增加,当主键为空/没有unique时,由innodb自动产生并做聚集索引=> 这个与MVCC无关

explain

show index from tb_name; --索引字端

• Seq_in_index: column_name 在 Key_name 中的顺序
• Packed:关键字如何压缩； null => 不压缩
• Sub_part：当前列是否被索引，100 => 前100字符进行索引
• Index_type: 索引的类型:BTREE => B+数
• Collation: 列以什么方式存储在索引中,可以是A/NULL。A:B+数索引,排序，NULL:Hash索引不排序
• Cardinality:表示索引的散列程度.最好值等于数据的行数;如果=1,说明索引没用,可以删除.

-- 可以优化(refrush)索引,local是本地优化不写入binlog,空闲时间操作,影响性能
analyze [local] table

explain sql

• id(priority_id): select标识id; id如果相同,可以认为是一组,从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行;如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行.
• select_type: 从最好到最差:system>const>eq_ref>ref>range>index>ALL;
  • system,const,查询的变量为const; where id = 1
  • eq_ref: 索引等值查询,返回一行数据,应该是unique;1:1
  • ref: 索引等值查询,返回多行数据,应该是普通索引;1:N
  • range: 索引范围查询: where id >1
  • index: 索引全表查询:
  • ALL: 不使用索引,全表查询
• table: 查询的table
• type:
• possible_keys:可能使用的key
• key:实际使用的key
• key_len:
• ref:显示索引的哪一列被使用了
• rows:查询的行数
• filtered: 过滤的百分比
• Extra:
  • using index
  • using where,
  • using tmporary(使用临时表),
  • using filesort(使用额外排序,在内存中进行)
  • range checked for each record(没用索引，全表查询)

连接池

链接参数

• max_connections 最大客户端连接数=cpu+1
• table_open_cache: 表文件句柄缓存(分区存储文件句柄的缓存)
• key_buffer_size: 索引缓存
• innodb_buffer_pool_size:Innodb,引擎缓存池的大小
• innodb_buffer_pool_instances;缓存池的格式

# 压测工具:mysqlslap
show variables like '%max_connections%';  -- 最大连接数
show status like '%Max_used_connections%';
show status like '%Threads%'; -- 查看当前最大并发

集群

• mysql自身实现,主从
• 中间件
• 异地多活:canal阿里技术

读写分离&分表

读写分离

• 代码编程:配置多数据源+AOP
• 中间件:美团

分表

• 垂直 -> 基于业务,筛选无效数据/过滤有价值的数据
• 水平分表的技术

1. [mysql-proxy&主从]downloads.mysql.com/archives/pr…
2. [DBproxy]github.com/Meituan-Dia…
3. [canal]github.com/alibaba/can…
4. [Amoeba for MySQL]

日志

• 慢查询日志
• profile

# 慢查询
# 查看慢查询
show variables like 'slov_query_log';
# 开启满查询
set GLOBAL slow_query_log = on;
# 打开慢查询
set slow_query_log＝ ON;
#慢查询日志分析,options => ?
shell> mysqldumpslow slow_query.log;

# profile
show variables like 'profiling';  # select @@profiling;
set profiling=on;  -- 查看,配置
show profiles;     -- 查看SQ的执行信息
+----------+------------+-------------------------------------------------------+
| Query_ID | Duration   | Query                                                 |
+----------+------------+-------------------------------------------------------+
|        1 | 0.00086150 | show variables like 'profiling'                       |
|        2 | 0.15027550 | insert into article values (null,'test profile',':)') |
+----------+------------+-------------------------------------------------------+

# 根据query_ID,查看详细信息
show profile for query Query_ID;
#显示哪些线程正在运行。您也可以使用mysqladmin processlist语句得到此信息。如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程（也就是，与您正在使用的MySQL账户相关的线程）。如果有线程在update或者insert 某个表，此时进程的status为updating 或者 sending data。
SHOW PROCESSLIST;
#返回全部连接
SHOW full PROCESSLIST;
Show status like "%lock%";
Show variables  like "%timeout%";

储存分区

属DBA层,mysql文件存储的分区设置

• 表结构对应存储文件; 数据量大,则查询效率下降
  • MyISAM: 存储文件 .MYI和.MYD
  • InnoDB: 存储文件 .idb 和 .frm
• 分区算法
  • hash(id); id为整型
  • key(id); id是str
  • range(creaeTime):条件分区
  • list(statue)

# 存储分区
create table article(
id int auto_increment PRIMARY KEY, 
title varchar(64),
content text )PARTITION by HASH(id) PARTITIONS 10; 
# 对表article的存储文件根据hash(id)取模分别存入10个文件; 分区的字段必须出现在主键中
create table tmp()charset=utf8 

# 条件分区算法
# 条件分区1;
PARTITION BY RANGE(created_time)
( 
PARTITION p201808 VALUES less than (1535731199),  -- select UNIX_TIMESTAMP('2018-8-31 23:59:59')
 PARTITION p201809 VALUES less than (1538323199), -- 2018-9-30 23:59:59 
 PARTITION p201810 VALUES less than (1541001599)  -- 2018-10-31 23:59:59 
);
# 条件分区2
PARTITION BY list(status)( 
   PARTITION writing values in(0,1),--未发布的放在一个分区
   PARTITION published values in (2)--已发布的放在一个分区
);

Interview

倒排索引

倒排索引是实现全文检索的基础数据结构

正序索引: 文档-关键字，上图以列的方式建立索引 倒序索引: 关键字-文档，以行的方式建立索引, 关键字的索引结构可以只用hash或B+树，MYSQL使用的是B+

回表查询-索引覆盖

回表查询

索引的底层实现是B+树; InnoDB有两大类索引:聚集索引(clustered index)普通索引(secondary index);

InnoDB聚集索引的叶子节点存储的是行记录，InnoDB必须要有，且只有一个聚集索引:

1. 如果表定义了PK，则PK就是聚集索引；
2. 如果表没有定义PK，则第一个NOT NULL unique列是聚集索引；
3. 否则，InnoDB会创建一个隐藏的row-id作为聚集索引

所以PK查询非常快，直接定位行记录

InnoDB普通索引的叶子节点存储主键值(PK),MyISAM的叶子节点直接存储记录指针。从普通索引无法直接定位行记录,通常情况下，需要扫码两遍索引树:先通过普通索引定位到主键值id；再通过聚集索引定位到行记录。

这就是所谓的回表查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低。

索引覆盖

官方explain的输出结果Extra字段为Using index时，能够触发索引覆盖。只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快;通俗点,select不需要通过回表查询，一次索引查询即可返回全部的数据

如何实现索引覆盖,哪些场景,可以利用索引覆盖来优化SQL？

• 把要select的字端保证能在一次索引中全部查到,不需要回表查询；
• 建议使用联合索引:最好使用联合索引，不要使用多个单索引.
  • 减少建立索引的开销
  • 联合索引效率高,可以实现索引覆盖;

最左匹配原则

联合索引的检索从最左边的key开始匹配; 因为联合索引从左到右进行排序; 所以, 建立联合索引时最左字端区分度尽量高,查询联合索引时最左key尽量是等值查询而不是范围/模糊查询.

# 对列col1、col2和col3建一个联合索引
KEY test_col1_col2_col3 on test(col1,col2,col3);
SELECT * FROM test WHERE clo2=“2” AND col1=“1”  AND clo3>“4”
# 索引index(col1,col2,col3)是按从左到右排序的,在where条件中中索引的key出现的顺序不重要; `从左到右出现的第一个非等值索引则停止使用索引查询`.

联合索引好处

• 减少开销联合索引(col1,col2,col3),相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引(实际是一个索引)。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销=>(主要跟索引排序相关..)
• 覆盖索引方便使用索引覆盖,覆盖索引是主要的提升性能的手段
• 联合索引比多个单索引效率高,索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W10%=100w条数据，然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出1000w10% 10% *10%=1w，效率提升可想而知

对于联合索引(col1,col2,col3)，查询语句SELECT * FROM test WHERE col2=2;是否能够触发索引?

不会触发索引,因为联合索引最左匹配原则:从左到右第一个非等值索引则停止使用索引

EXPLAIN SELECT * FROM test WHERE col2=2;
EXPLAIN SELECT * FROM test WHERE col1=1;

观察上述两个explain结果中的type字段。查询中分别是：

type: index 和 type: ref:

• ref:这种类型表示mysql会根据特定的算法快速查找到某个符合条件的索引。使用索引范围查询.
• index:这种类型表示mysql会对整个该索引进行扫描。使用索引全表查询.
• ref通过索引计算col1;index通过遍历计算col2,索引不再起作用
• 索引只能用于查找key等值匹配，遇到范围查询 (>、<、between、like左匹配)等就不能进一步匹配了，后续退化为线性查找.

对于联合索引 (a,b,c,d),查询条件 a=1 and b=2 and c>3 and d=4，则会在每个节点依次命中a、b、c，无法命中d。(c已经是范围查询了，d肯定是排不了序了) => 因为联合索引最左匹配

like查询索引/while not

• like 'x%', 使用index
• like '%x%',不使用index
• like '%x',不使用index
• not 索引不起作用,索引全遍历

=> 联合索引最左匹配;(等值)

索引下推

最左匹配和索引下推都是在联合索引的场景; MySQL5.6引入了索引下推优化(ICP)，可以在索引遍历过程中，对索引中包含的字段优先做判断，过滤掉不符合条件的记录，减少回表次数；

-- index(name,age)
 select * from tuser where name like 'x%' and age=10 and ismale=1;

不使用索引下推: 则先索引返回name like 'x%', 再回表查询遍历所有判断age & ismale； 使用索引下推: 则先索引返回name like 'x%' and age = 10,再回表查询遍历ismale,条件判断age=10在联合索引查询内部就执行。减少回表查询的次数.

索引数据结构

select * from user where username='BB'的查询过程,username是普通索引

• 通过普通索引username找到指定节点的数据页
• 遍历页找到主键ID
• 回表查询通过username索引找到聚集索引要查的ID
• 聚集索引通过ID定位到所在的节点(节点),找到节点所占有的数据页
• 用ID二分查找指定的记录的全部记录数据

索引-Hash

适合等值查询,不适合范围查询(<>,between-and),空间利用率低；有序的hash->LinkedHashMap,每次CU(CRUD)都会更新顺序,操作复杂度高。适合存储静态的查询数据,如历史列表,不适合频繁CU的场景

索引-二叉树

查询O(lgn),适合范围查询; 叶子节点少(二叉)不适合大数据;存在极限二叉树的缺陷(全是左子树，退化为琏表了)

索引-Btree

Btree索引系统文件，对于数据库没有Btree+优秀;Btree在叶子节点冗余了非叶子节点的数据，所有叶子节点构成双琏表结构.3叉,查询效率高,减少磁盘IO,适合范围查询

mysql中B+索引的结构是怎样的

B+树的节点里面的逻辑结构是数据页,一个节点可以是一页或页的倍数(具体看源码,因此存在了页分裂和页合并的问题);数据页可以存储多个row记录.mysql的存储逻辑单元是数据页.

1. 数据页是双琏表的形式存储
2. 数据页存储的记录,数据页中的记录是单链表

每个数据页都会为存储在它里边儿的记录生成一个页目录(record目录)，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录.

3. 等值查询的过程:

1. Btree+找到指定的节点, 2)在节点遍历找到指定的页 3)二分(或遍历)找到页中指定的记录; 以其他列(非主键)作为搜索条件:只能从最小记录开始依次遍历单链表中的每条记录。

页合并和页分裂

• node = page = row * n 主要发生在数据insert/delete的过程中;页分裂和页合并

自增主键

auto_increment (unsight int)最大值2^32-1(4,294,967,295=42亿); 达到最大值会时会报重复主键的错误。一般选用big int(8Byte)。

不设置主键时: 默认生成的row_id(6Byte),最大值=2^48-1; 达到最大值时，从0开始计数。

• UUID,无序不是递增，字符串查询慢，不具备业务含义
• mysql, bigint(2^64-1)
• snowflake,

binlog-redolg-undolog

• binglog,server层,sql语句,主从
• redolog,innodb层,物理日志,循环使用,cash-safe,2pc
• undolog,innodb层，支持mvcc

sql优化

1. 最左前缀匹配原则。这是非常重要、非常重要、非常重要（重要的事情说三遍）的原则，MySQL会一直向右匹配直到遇到范围查询 （>,<,BETWEEN,LIKE）就停止匹配。
2. 尽量选择区分度高的列作为索引，区分度的公式是 COUNT(DISTINCT col)/COUNT(*)。表示字段不重复的比率，比率越大我们扫描的记录数就越少。
3. 索引列不能参与计算，尽量保持列“干净”。
4. 尽可能的扩展索引，不要新建立索引。比如表中已经有了a的索引，现在要加（a,b）的索引，那么只需要修改原来的索引即可。
5. 单个多列联合索引`比多个单列索引的检索查询效果好

存储计算

InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为〖10〗^3）。 也就是说一个深度为3的B+Tree索引可以维护10^3 * 10^3 * 10^3 = 10亿 条记录。（这种计算方式存在误差，而且没有计算叶子节点，如果计算叶子节点其实是深度为4了 B+tree 是1K叉的平衡树,大约的数据量 = pow(1K,n);n是树的高度

interview

• 聚集索引,非聚集索引
• 索引结构,数据页的大小
• 回表,索引覆盖,最左匹配,sql优化
• count 1|* 对比 select *
• MVCC和事务隔离级别的关系blog.csdn.net/qq_38538733…
• 行锁的三种算法:record,gap,next-key
• 唯一索引和普通索引(change Buffer)的区别
• 页分裂/页合并
• sql的执行过程
• mysql里的S/X锁
  • lock in share mode，共享锁
  • for update, 排他

ps

• DBA博客 www.fordba.com/category/my…
• www.fordba.com/locks-set-b…
• MVCC和事务隔离级别的关系blog.csdn.net/qq_38538733…
• [加锁分析]www.fordba.com/locks-set-b…
• 索引覆盖