Mysql
2022-02-17 19:23:14 20 举报AI智能生成
自己结合一些网上查询的资料整理的mysql脑图，仅做学习使用，不断完善中
作者其他创作
大纲/内容
2. 查询
sql
4. 事务
ACID
原子性Atomicity
一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性Consistency
在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性Isolation
数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）
读取未提交内容（Read Uncommitted）
在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。
读取提交内容（Read Committed）
这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别 也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。
可重复读（Repeatable Read）
这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。
可串行化（Serializable）
这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。
持久性Durability）
事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
语法
BEGIN
ROLLBACK
COMMIT
隔离等级
READ UNCOMMITTED - 读未提交，事务内可读到其他事务未提交的修改
READ COMMITTED - 读已提交，事务内可读到其他事务已提交的修改
REPEATABLE READ - 可重复读（默认级别），在一个事务内重复读数据会一直保持一致，在本事务结束后才能读到其他事务的修改
SERIALIZABLE - 串行化，事务不可以交替修改数据
事务隔离性问题
脏读：指一个线程中的事务读取到了另外一个线程中未提交的数据。
不可重复读（虚读）：指一个线程中的事务读取到了另外一个线程中提交的update的数据。
幻读：指一个线程中的事务读取到了另外一个线程中提交的insert的数据。
6. 优化
作用
增加吞吐量
减少CPU处理压力
加快数据读写
字段设计
常规
每张表使用自增id，update_time，created_time, remark
not null优先，null是特定数值，占用空间， 空字符串不占用空间， 且null计算逻辑较复杂
语义化字段名，适当注释，选用合适的字段类型
适当冗余字段，减少关联查询与重复查询
范式
第一范式
确保每列保持原子性
第二范式
确保表中的每列都和主键相关
第三范式
确保每列都和主键列直接相关,而不是间接相关
存储引擎
选择
索引
查询
查找慢查询
show processlist
分析查询
explain
语法
explain %query%
信息字段
id 标志符
select_type 查询的类型。
SIMPLE(简单SELECT，不使用UNION或子查询等)
PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)
UNION(UNION中的第二个或后面的SELECT语句)
DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)
SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)
DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)
DERIVED(派生表的SELECT, FROM子句的子查询)
UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)
table 输出结果集的表
type 表示表的连接类型
ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
index: Full Index Scan，index与ALL区别为index类型只遍历索引树
range:只检索给定范围的行，使用一个索引来选择行
ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system
NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。
key 实际使用的索引
rows 扫描行数
Extra 执行情况的描述和说明
细节
EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
EXPLAIN不考虑各种Cache
EXPLAIN不能显示MySQL在执行查询时所作的优化工作
部分统计信息是估算的，并非精确值
EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。
优化查询
走索引
细节
批量导入代替循环插入
不使用 select * 之类模糊读取
单数据读取加上 limit 1 减少表扫描
where 代替大数值offset
key-value保存查询结果，key为sql语句
避免逐条事务
查询缓存
解释
操作
开启
query_cache_type
设置缓存大小
query_cache_size
重置缓存
reset query cache
失效
所有相关查询结果的数据表更新均为导致缓存失效
数据分割
水平
多张表存储同一类型数据，根据顺序或者其他标识分别读取对应表
垂直
把同一类型数据的属性字段分到多张表，形成对应关系
数据分区
主键用完
有主键，单表报主键冲突
无主键
表中第一个唯一索引NOT NULL字段作为主键, 报错同有主键
InnDB 会自动生成一个全局的row_id。它到达最大值后会从 0 开始算，遇到 row_id 一样时，新数据覆盖旧数据
8. 版本关键特性
5.6
innodb后台线程独立出来
explain语句支持insert，update，delete，replace语句，并且支持JSON格式
5.7
安全性
JSON
默认启用 STRICT_TRANS_TABLES 模式。对 ONLY_FULL_GROUP_BY 模式实现了更复杂的特性支持，并且也被默认启用。
8.0
JSON扩展语法，新功能，改进排序和部分更新。使用JSON表函数，您可以使用JSON数据的SQL机制。
GIS地理支持。空间参考系统（SRS），以及SRS感知空间数据类型，空间索引和空间功能。
安全 OpenSSL改进，新的默认身份验证，SQL角色，分解超级特权，密码强度等等。
性能大幅提升， 比5.7 快2倍
1. 增删改
数据库
create database db_name
drop database db_name
注意数据库编码，utf8_mb4 后才支持emoji
数据表
create table tb_name
drop table tb_name
数据
insert into tb_name ( field1, field2,...fieldN )
values
( value1, value2,...valueN );
values
( value1, value2,...valueN );
update tb_name set ?=? where ?
delete from tb_name where ?
3. 索引
解释
从数据中提取标志性关键字，形成对应数据的映射关系
类型
主键索引 primary key
一般索引 key
组合索引
全文索引 fulltext key
唯一索引 unique key
语法
查询
show create table xxx
建立
key `key_name` (`field_name`)
primary key (`id`)
key `key_name` (`field_1`， `field_2`)
删除
alter table xxx drop key `key_name`
细节
where 条件顺序优先使用已建立索引字段
枚举值类使用索引可能比全表扫描低效，需要具体场景分析
避免使用select *， count(1)或count(列) 代替 count(*)
表的字段顺序固定长度的字段优先
组合索引代替多个单列索引（经常使用多个条件查询时）
使用连接（JOIN）来代替子查询(Sub-Queries)
尽量使用短索引
连表时注意条件类型需一致
存储类型
hash
类似键值对的形式, 可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率, 这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高
btree
BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型
b+tree
RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。
相对于BTREE，RTREE的优势在于范围查找
相对于BTREE，RTREE的优势在于范围查找
FULLTEXT
只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引, 解决 where like '%xxx%' 模糊查询问题
索引失效
索引左原则， like 'xxx%' 使用索引，'%xxx', '%xxx%'不使用
数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。
or 需要左右均建立索引才会使用索引查询
组合索引，不是使用第一列索引，索引失效。左原则
在索引列上使用 IS NULL 或 IS NOT NULL操作。索引是不索引空值的，所以这样的操作不能使用索引，可以用其他的办法处理，例如：数字类型，判断大于0，字符串类型设置一个默认值，判断是否等于默认值即可。
在索引字段上使用not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。
对索引字段进行计算操作、字段上使用函数。（索引为 emp(ename,empno,sal)）
5. 数据库引擎
Innodb
InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，上图也看到了，InnoDB是默认的MySQL引擎
MyISAM
基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事务
7. 集群
主从复制
读写分离
负载均衡
轮询
加权轮询
实际负载分配
主主半同步复制
双通道复制
binlog文件服务器
MHA+多节点集群
zookeeper+proxy
9.架构
参考博文
参考博文2
0 条评论
下一页
