mysql硬解析_MySQL笔记
MySQL使⽤原则：
⾃⼰扛住的⾃⼰扛，扛不住的使⽤中间件，⽐如：Redis；
最佳的单表存储量是千万级别，超过这个级别采取分表分布的⽅式来存储表。
MySQL体系架构
MySQL语句在数据库的执⾏过程：
经过parser进⾏词法解析和语法解析；
解析之后经过优化器⽣成执⾏计划，MySQL不缓存执⾏计划(也叫做硬解析)；
执⾏计划进⼊执⾏器执⾏，到存储引擎中数据；
返回数据。
MySQL包括核⼼层(mysqld)和存储引擎层，前者在内存和CPU中执⾏，后者在硬盘中，指的是不同的存储⽅法，最主要的两种是InnoDB 和MyISAM
MySQL⼀次读取数据为16K，每个16K叫做page(页)，
InnoDB
包括主键索引和辅助索引(⼜叫K值索引，⼆级索引)，主键索引的叶⼦节点存放的就是记录，辅助键索引叶⼦结点存放的是索引字段和主键，通过索引字段查对应的记录，根据该记录中的主键去主键索引对应的记录。
建表时，如果没有声明主键，InnoDB会通过第⼀个⾮空且唯⼀的字段建⽴主索引，没有的话，会建⽴隐藏列(6字节)。
索引是⼀颗B+树，所有的叶⼦节点是⼀个双向链表，在插⼊数据维护索引时，可能会出现页分裂的情况。
根据辅助索引查询数据时，如果需要查询除主键索引外的数据，那么需要回表(辅助索引到主键索引的过程)，但是如果只查主键，就不需要回表。
联合索引
多个字段联合建⽴索引。
最左前缀原则
当你定义了联合索引时，在执⾏查时，只有where字句出现的字段与联合索引出现的顺序⼀致，才能使⽤上索引。
⽀持事务。
表在存储引擎中被存放到两个⽂件，.idb⽂件存放的实际数据，.frm⽂件存放的是表的定义，由于系统中⽂件⼤⼩有限制，因此表存储的数据量有限制。
innodb buffer pool - 数据和索引都能缓存，MyISAM只能缓存索引。
MySQL查询数据时取表中16K的数据放到Innodb buffer pool做查询，满⾜查询条件的数据留下，继续扫描直到扫描了全表，同时，也存储了索引。
相关参数：
innodb_buffer_pool_size - innodb buffer pool在内存⾥的⼤⼩；
innodb_buffer_pool_instance - innodb buffer pool被分为⼏块，⼀般设置成16或者32。
innodb buffer pool使⽤LRU(Least Recent Used，最近最少使⽤)算法存储缓存在内存中的数据，默认页的⼤⼩是16K。
查询机制：
如果要查询到数据所在页不在Buffer Pool中，把该页从磁盘加载到Buffer Pool中的缓存页时，将该缓存页包装成节点塞到链表的头部；
如果该页在Buffer pool中，则直接把该页对应的LRU链表节点移动到链表的头部。sort命令排序
LRU链的前半部分称为热端，后半部分称为冷端。
缓存页直接放在热端带来的问题是，访问极少使⽤的表后，对应的页会放置在热端，造成原本频繁访问的数据被淘汰，后期再访问时存在⼤量磁盘IO，因此为了优化这⼀问题，在LRU中设置了midpoint，默认情况下新缓存页第⼀次插⼊到LRU列表的5/8处，再次查询时再放到LRU链表头部。
update机制
事务：对数据做修改，以commit或者rollback结束的过程。
假设当前有个⽤户对某条数据进⾏更新，执⾏如下语句：update emp set name=JACK where empno=7369;，随后⼜来⼀个⽤户读取同⼀条数据，select * form emp where empno=7369;，数据库默认不执⾏commit，其流程如下：
读线程io_read_thread从存储引擎的*.idb⽂件中到该⾏数据，并读取到innodb buffer pool中，并将修改前的数据写到UNDO中；
写线程io_write_thread对innodb buffer pool中的数据进⾏修改，此时，事务未进⾏commit时，该条数据在innodb buffer pool和存储引擎中的数据会不⼀致，在innodb buffer pool中的是否在事务中字段会存储是，UNDO字段中会存放该条数据前镜像的地址**；
后⼀个⽤户此时读数据时，读线程在innodb buffer pool中到该条数据后，根据事务字段判断该条数据处于事务未提交状态，然后根据UNDO字段到数据存储在ibdata1⽂件(存储被修改前的数据)中的地址，并去读取。
UNDO⽂件(保证⼀致性)的作⽤：
事务未提交时作⼀致性读取；
事务回滚后从UNDO中回更新前的数据。
数据库软件设计的原则
内存读优于磁盘读；
⽇志先⾏(先记再改)。
log buffer
log buffer在内存中，记录下对数据库的update、delete和insert操作，由log thread(只有1个)执⾏；
commit后，log buffer会写到磁盘中，存在ib_logfile0和ib_logfile1⽂件中，这两个⽂件称作redo log，当两个⽂件都写满之后，接下来数据库只能进⾏读，⽆法执⾏其他操作，这时的数据库状态叫做hung住。
写到磁盘的条件：1)达到1/2满；2)超过1M⼤⼩；3)commit(相关的参数：inndb_flush_log_at_trx_commit，这个参数=0时，表⽰log buffer每秒落⼀次盘；=1时表⽰每次commit就落盘，默认是1；)；满⾜上述三个条件中的任意⼀个log buffer都会写⼊到磁盘中；
解决系统因为redo⽇志⽆法覆盖造成hung住的解决⽅案：
调整redo⽇志⼤⼩；
调整redo⽇志⽂件数量。
MySQL启动时会进⾏实例恢复，即重做redo⽇志。
redo⽇志不能归档，即ib_logfile0和ib_logfile1⽂件写满后⽆法复制到其他⽂件中。
change buffer
在innodb buffer pool中，⽤来缓存⽤户在innodb buffer pool对数据进⾏修改且未提交的数据；
⽬的是减少落盘时磁头在不同盘块间的移动次数(即随机访问的次数)；
不适合马上写马上读的操作；
唯⼀索引不能使⽤change buffer。
表连接
驱动表：表连接时，需要全表扫描的表，让⾮驱动表⾛索引。栗⼦：select * from t1 join t2 on(t1.a=t2.1); //其中t1是驱动表
Index Nested-Loop Join(Index NLJ，嵌套循环连接)：⾮驱动表⾛索引，优化的⽅向；
Simple Nested-Loop Join(Simple NLJ)：⾮驱动表不⾛索引，select * from t1 straight_join t2 on(t1.a=t2.b);
Block Nested-Loop Join(BNLJ)：即当连接的表都不存在索引时，在作连接时，会将驱动表整块表或者分块放进join buffer中，由于驱动表存放在内存中，因此在作全表扫描时，能够加快表的连接；
Batched Key Access:Index Nested-Loop Join 和 MRR的结合；
表连接的⽅式使⽤选择：Index NLJ -> BNLJ -> Simple NLJ，除了Simple NLJ，其他的表连接⽅式都会⽤到join buffer;
辅助索引范围扫描的优化⽅式：Multi rang read(MRR)，存储在分配给⽤户的buffer中，即在对辅助索引进⾏范围查询时，每查到⼀个数据不直接进⾏回表，⽽是将所有满⾜条件的数据放到MRR buffer中，按照主键索引进⾏排序，然后再根据排序的结果返回到主键索引中进⾏查。
SQL语句 -- 考题
select length("开⼼⽣活") - 返回存储字符串的字节数；
select char_length("开⼼⽣活") - 返回字符串的字符数，与本地客户端的字节编码⽅式(character_set_client = utf8 or latin1)有关；
每个⽤户都会有⾃⼰的join、sort、multi range的buffer；
select concat和select concat_ws的区别：前者将所有字符串连接在⼀块，后者第⼀个参数是其它参数的分隔符，分隔符的位置放在要连接的两个字符串之间，如果分隔符为 NULL，则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值，但是CONCAT_WS()不会忽略任何空字符串；
user()和current_user()的区别：
获取当前的⽇期：CURDATE()，获取当前时间：CURTIME()，获取当前⽇期时间：NOW()。
where字句中!=、is null、is not null在MySQL5.7.21版本中不⾛索引；
utf8编码⽤3个字节存储字符。
常见字符串函数：
instr(string, substring): 返回substring第⼀次出现在string的起始位置，如果不存在substring返回0。
strcmp(string1,string2): 两个字符串相同时，返回0；string1在字典中⽐string2靠后，返回1；否则返回-1。
left(string, numstr): 返回字符串左边的numstr个字符；
right(string,numstr): 返回字符串右边的numstr个字符；
lpad(string, len, padstring): 如果string的字符数量⼤于len，那就从string截取len个字符返回，如果string的字符数量⼩于len，那就在string的前边添加padstring直到string有len个字符返回；
rpad(string, len, padstring): 与lpad作⽤相反，在右边补充字符。
substring(string, pos, length): 返回string中pos位置开始的len个字符，MySQL从1开始数。
substring_index:
trim
常见的DATE and TIME运算：
类型：DATE、TIME、DATETIME；
函数：NOW()、CURDATE()、CURTIME()；
DATE_ADD()、DATE_SUB()。
常见的Numeric函数：
ROUND(): 四舍五⼊；
TRUNCATE():
ceiling(x)：对x进⾏向上取整。
系统参数：
所有创建的⽤户都在mysql库中的user表中；
select user();: 获取当前真正连接数据库的⽤户；
select current_user();
创建⽤户时，需要指定⽤户名、密码和客户端IP地址create user user_name@ip_address identified by pwd;
聚合函数：
GROUP BY:
HAVING:
LIMIT:
GROUP_CONCAT()
Union 与 union all
temporary buffer: group by，union，distinct
唯⼀索引和普通索引的区别：只有普通索引能使⽤change buffer，普通索引适合于频繁写⼊，但不常常读取。
MySQL没有函数索引和索引跳跃扫描；
执⾏计划中通过type判断查询⾛不⾛索引。
key_len的算法：索引列类型if：1)int不能为空时=4；能为空时=5；2)varchar(20)，由于加上变长，key_len=22；3)datetime=8；4)date=3。
sync_binlog、innodb_flush_log_at_trx_commit：两个值都设为1时，称作双1模式。
⽤户访问数据库，如果需要sort，在内存中会⽣成⼀块私⼈的sort buffer，从innodb buffer pool中取数据，放到sort buffer中排序。
索引
索引是什么？
索引的出现是为了提⾼数据查询的效率，就像书的⽬录⼀样。
有哪些索引，适⽤于对哪⼀列进⾏索引？
常见的索引模型
哈希表：1)适合于单个数据进⾏精确查询，以及插⼊速度快；2)不适合范围查询，以及不适合做更新。
有序数组：根据索引对数据进⾏排序，1)范围查询速度快；2)插⼊和更新满，因为数组是连续存储的，因此在插⼊和更新时需要移动元素。适⽤于历史表。
⼆叉搜索树：由于分⽀数量为2，导致树较⾼，增加了查询数据时磁盘IO的次数，在数据库中不常使⽤。
N叉搜索树(MySQL使⽤，也叫B+树)：树根的数据块总是放在内存中，每⼀个索引在InnoDB中对应⼀棵B+树。
常见的索引
主键索引
唯⼀索引：由于索引定义了唯⼀性，查到第⼀个满⾜条件的记录后，就会停⽌继续检索。
普通索引：查到满⾜条件的第⼀个记录后，需继续查下⼀个记录，直到碰到第⼀个不满⾜条件的记录。
前缀索引：即当要建⽴索引的字段长度太长时，从左往右截取⼀定长度的字符串建⽴索引，但是需要满⾜截取下来的⼦串区分度较⼤，栗⼦：如果对订单号建⽴前缀索引时，由于订单前⾯⼏位通常以⽇期开头，此时区分度较⼩，可以将订单号反转。
索引设计原则
选择度越⼤，越适合建索引，选择度是指：对于⼀个表，随机抽出N⾏，在这N⾏中，对于索引列⽽⾔如果存在n个不同的值，我们称n/N为该列的选择度。MySQL通过收集统计信息来计算选择度，统计信
息存储在.frm⽂件中，⼿动收集统计信息语句：analyze table 表名;，查看统计信息语句：show index from 表名;；
执⾏计划的阅读
使⽤EXPLAIN命令查看SQL语句的执⾏计划，但是语句不会被执⾏；
执⾏计划的关键字段：
type: 值是range表⽰索引范围扫描(在sql语句的where字句表⽰范围查)，值是ALL表⽰全表扫描，值是ref表⽰等值索引扫描(等号查)；
possible_keys：所有查询时可能⽤到的索引；
key: 查询实际使⽤的索引；
key_len: 所使⽤索引的长度；
ref:
rows: 要查的⾏数，从统计信息⽽来。
filtered:
Extra: 值是Using index表⽰当前查询⽤到了索引覆盖优化。
复制表中的所有数据到新表中：create table t_new as select * from t;
where字句中如果是数学表达式、带函数的列、!、<>和not in(都表⽰不等于)、is null就⾛不了索引。
null和任何值做运算结果必然是null。
MySQL中的锁
undo保证事务的⼀致性，commit和rollback保证事务的原⼦性，锁保证隔离性，redo⽇志保证持久性。
全局锁
整个数据库只能读，不能写。Flush tables with read lock(FTWRL)，适⽤于数据库进⾏备份时使⽤全局锁。
表级锁
包括表锁和元数据锁。
表锁
lock table t read;: 给表t加读锁，此时表t只允许读，不允许写；
unlock tables;: 释放锁；
lock table t write;: 给表t加写锁，此时表t不允许读和写。
元数据锁(⽣产常⽤)
所有的SQL语句对表进⾏操作时，会对表加MDL锁(Metadata lock，元数据锁)，此时⽆法对表的结构进⾏修改(⽐如增加或者减少列)，因为修改表结构需要。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。