MySQL高级
2022-12-12 17:05:28 1 举报AI智能生成
MySQL高级知识点，思维导图
作者其他创作
大纲/内容
存储引擎
MyISAM(聚簇集索引)
是一种非事务性的引擎，提供高速存储和检索，以及全文搜索能力，适合数据仓库等查询频繁的应用
不支持事务;支持表级锁，并发性差；主机宕机后，MyISAM表易损坏，灾难恢复性不佳；
可以配合锁，实现操作系统下的复制备份、迁移
可以配合锁，实现操作系统下的复制备份、迁移
只缓存索引，数据的缓存是利用操作系统缓冲区来实现的;可能引发过多的系统调用且效率不佳；
数据紧凑存储，因此可获得更小的索引和更快的全表扫描性能
数据紧凑存储，因此可获得更小的索引和更快的全表扫描性能
innoDB(聚簇索引)
支持事务的引擎。给MySQL提供了具有提交，回滚和崩溃恢复能力的事务安全（ACID兼容）存储引擎
支持表级锁和行级锁，支持外键关联，支持热备份
当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙(GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制不是所谓的间隙锁（Next-Key锁）。InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求；另一方面，是为了满足其恢复和复制的需要。
B+tree 数据结构
索引
分类
物理存储
聚簇索引（一级索引）
叶节点将数据跟索引结构放到一块，因此一个表仅有一个聚簇索引
特点
索引页+数据页组成的B+树
同层级的索引页，互相之间基于指针组成双向链表。
最下层的索引页，有指针引用数据页。即在大的B+树索引数据结构里，叶子节点是数据页本身。
当数据页开始分裂时，会维护上层索引数据结构，在上层索引页里维护索引条目，不同数据页和最小键值。
当数据页越来越多，一个索引页放不下，就会拉出新的索引页，同时加一个上层索引页，上层索引页存放的条目时下层索引页页号和最下主键值。
数据量越大，索引页层级越多，一般索引页可以放很多索引条目，所以通常而言，即使是亿级大表，基本上大表建的索引层级也就三四层。
非聚簇索引（二级索引）
由于每个辅助索引都包含主键索引，因此，为了减小辅助索引所占空间，我们通常希望 InnoDB 表中的主键索引尽量定义得小一些，并且希望InnoDB的主键是自增长的，因为如果主键并非自增长，插入时，由于写入时乱序的，会使得插入效率变低。
特点
独立于聚簇索引之外的另一个B+树结构
从小到大排序，下一个数据页索引字段值大于上一个数据页索引字段值
叶子节点仅仅存放主键和索引字段的值，而不保存数据
回表
在索引页，根据索引字段找到数据后，还需要通过主键信息在聚簇索引里从根节点开始查找，一路找到叶子节点的数据页，才能定位到主键对应的完整数据行。
InnoDB采用的方式是在叶子页中保存主键值，通过这个主键值来回表查询到一条完整记录，因此按辅助索引检索实际上进行了二次查询，
效率肯定是没有按照主键检索高的
效率肯定是没有按照主键检索高的
字段特性
主键索引
特点
B+树结构
索引页里保存了下级索引页的页号和最小主键值。通过二分查找可以找到下级索引页。如果最下层的索引页的页号太多，可以再次分裂，再加一层索引页。
针对主键的索引其实就是主键目录，把每个数据页的页号，还有数据页最小的键值放在一起，组成一个索引目录，即索引页。
从小到大排序
建立在主键上的索引被称为主键索引，一张数据表只能有一个主键索引，索引列值不允许有空值，通常在创建表时一起创建
innodb主键使用自增bigint效率比uuid高
1.方便比较大小
2.不会破坏B+TREE结构
1.方便比较大小
2.不会破坏B+TREE结构
唯一索引
建立在UNIQUE字段上的索引被称为唯一索引，一张表可以有多个唯一索引，索引列值允许为空，列值中出现多个空值不会发生重复冲突
普通索引
建立在普通字段上的索引被称为普通索引
前缀索引
对字符类型字段的前几个字符或对二进制类型字段的前几个bytes建立的索引，而不是在整个字段上建索引
字段个数
单列索引
建立在单个列上的索引被称为单列索引
组合索引（联合索引）
建立在多个列上的索引被称为联合索引，又叫复合索引、组合索引
特点
原理同二级索引，只不过排序的顺序是从靠左边的索引字段开始排序。
查找过程中遇到范围查找的时候，后面的索引字段无法排序。因此设计索引的时候，要把需要做范围查询的索引字段放在最右边。
匹配规则
等值匹配规则
最左侧匹配规则
从索引最左侧值开始匹配，不能跳过左边索引字段直接匹配右边索引字段。
最左前缀匹配规则
可以用like 'xxx%'的方式匹配
范围查找规则
可以用select * from table where x1 > xx and x1 < yy这种方式匹配
等值匹配+范围匹配规则
排序/分组走索引提升查询速度
order by 后面字段顺序和联合索引从最左侧开始字段一致。
group by 后面字段顺序和联合索引从最左侧开始字段一致。
索引是否越多越好
索引太多，也许查询速度可以提高，但增删改速度比较差。占用磁盘空间也越多，所以索引并不是越多越好。
覆盖索引
select 后面跟的字段，即查询的字段，在索引树中，不需要回表去聚簇索引中找其他字段了。
索引设计小技巧
- 针对where条件，order by条件，group by条件，join条件on连接字段，去设计索引
- 尽量使用基数较大的字段（也可以称为散列性高），就是值比较多的字段建立索引，那样才能发挥出B+树快速二分查找的优势来
- 对那些字段的类型比较小的列来设计索引，这样占用的磁盘空间小，搜索性能好
- 如果某个较大的字段要建立索引，又不想索引树占太多磁盘空间，可以考虑用这个字段的前20个字符前缀建立索引
- 避免在索引字段里套函数，进行计算
- 索引不要设计太多，建议两三个联合索引就应该覆盖掉某个表的全部查询
- 主键一定要是自增的，别用UUID之类的
- 设计低基数字段索引时，可以加个复杂辅助字段。
索引失效原因
索引列上使用函数（replace/substr/concat/sum/count/avg）表达式
字符串不加引号，出现隐式转换
like条件中前面带%
使用not in或 not exits
事务
ACID属性
原子性(Atomicity)
事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行
一致性(Consistent)
在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改,
以保持数据的完整性;事务结束时,所有的内部数据结构(如B树索引或双向链表)也都必须是正确的
以保持数据的完整性;事务结束时,所有的内部数据结构(如B树索引或双向链表)也都必须是正确的
隔离性(Isolation)
数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行。
这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然
这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然
持久性(Durable)
事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持
并发事务处理带来的问题
更新丢失（Lost Update）
两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，
就会发生丢失更新问题–最后的更新覆盖了由其他事务所做的更新
就会发生丢失更新问题–最后的更新覆盖了由其他事务所做的更新
不可重读（Non-Repeatable Reads）
一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，
却发现其读出的数据已经发生了改变、
或某些记录已经被删除了！这种现象就叫做“不可重复读”。
一句话：事务A读取到了事务B已经提交的修改数据，不符合隔离性
却发现其读出的数据已经发生了改变、
或某些记录已经被删除了！这种现象就叫做“不可重复读”。
一句话：事务A读取到了事务B已经提交的修改数据，不符合隔离性
脏读（Dirty Reads）
事务A读取到了事务B已经修改但尚未提交的数据，还在这个数据基础上做了操作。
此时，如果B事务回滚，A读取的数据无效，不符合一致性要求
此时，如果B事务回滚，A读取的数据无效，不符合一致性要求
幻读（Phantom Reads）
一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，
这种现象就称为“幻读”
这种现象就称为“幻读”
事务隔离级别
SQL标准事务4种隔离级别
读未提交 RU (Read uncommitted)
脏读/不可重复读/幻读都可能
读已提交 RC (Read uncommitted)
脏读不可能，可能会发生不可重复读和幻读问题。
可重复读 RR (Repeatable read)
不可重复读/脏读不可能，可能会出现幻读的问题。
可重复读的隔离级别下使用了MVCC机制，select操作不会更新版本号，是快照读（历史版本）
insert、update和delete会更新版本号，是当前读（当前版本）
insert、update和delete会更新版本号，是当前读（当前版本）
undo log多版本链条（快照）
ReadView
RC级别下事务的每次重新生成ReadView
RC级别下事务的每次重新生成ReadView
要避免幻读可以用间隙锁在Session _1下面执行update acc ount se t name ='zhuge'
where i d> 10and id<= 20;，则其他Session没法插入这个范围内的数据
where i d> 10and id<= 20;，则其他Session没法插入这个范围内的数据
可串行化(Serializable)
脏读/不可重复读/幻读都不可能
mysql中事务隔离级别为serializable时会锁表，因此不会出现幻读的情况，
这种隔离级别并发性极低，开发中很少会用到
这种隔离级别并发性极低，开发中很少会用到
查看事务隔离级别
show variables like 'transaction_isolation';
select @@transaction_isolation;
默认的事务隔离级别
可重复读 RR (Repeatable read)
- 共享锁与共享锁不互斥
- 独占锁、独占锁、共享锁相互互斥
- 通常使用Redis或zookeeper替代显式加锁
分表
横向：表字段相同，数据量太大
union会去重，union all不会
纵向：一个表存基本信息，另外一个表存详情
join
SQL执行组件
客户端程序
客户端程序 : 包括一些mysql工具如：native 或者语言工具如：php 、go 、python
连接池
提供多个用户客户端和服务端交互的线程
SQL接口
接收sql命令，返回查询结果
解析器
进行sql语法的解析、语意解析、生成语法树
优化器
mysql核心组件，对sql命令进行优化
缓存
以key -> value方式缓存查询结果 (如果查询sql指令有缓存直接在SQL接口部分返回缓存结果)
存储引擎
与底层文件惊醒交互，查询数据文件系统、日志文件等
执行与优化
步骤
1.开启慢查询日志,设置阈值,比如超过5秒钟的就是慢SQL,并将它抓取出来;
2.EXPLAIN+慢SQL分析;
3.SHOW profile,查询SQL在MySQL服务器里面的执行细节和生命周期情况
4.具体优化
explain
语法
explain select * from xxl_job_log l where l.job_id in (select id from xxl_job_info)
字段说明
id
id相同,执行顺序由上至下;
id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行;
id相同不同,都存在
id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行;
id相同不同,都存在
表示查询中执行select子句或操作表的顺序
select_type
查询的类型
SIMPLE：简单的select查询，查询中不包括子查询或者UNION
PRIMARY：查询中若包括任何复杂的子部分，最外层查询则被标记为PRIMARY
SUBQUERY：在select或where列表中包含了子查询
DERIVED: 在FROM列表中,包含的子查询被标记为DERIVED(衍生),MySQL会递归执行这些子查询,
把结果放在临时表里
把结果放在临时表里
UNION：若第二个SELECT出现在UNION之后,则被标记为UNION;若UNION包含在FROM子句的子查询中,
外侧SELECT将被标记为 DERIVE
外侧SELECT将被标记为 DERIVE
UNION RESULT：从UNION表获取结果的SELECT
MATERIALIZED
物化表
table
查询的表
type
检索方式
在表中找到所需行的方式 ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）
ALL
全部扫描，效率低
根据双向链表依次把磁盘上的数据页加载到缓存页里去。然后在缓存页内部查找那条数据
index
遍历二级索引就能拿到想要的数据，而不需要回表查询
range
只检索给定范围的行，使用一个索引来选择行
explain select * from employee where rec_id < 3其中rec_id为主键
ref
通过普通二级非唯一索引（如果是联合索引，须从索引最左侧开始连续多个列都是等值比较才属于ref）+聚簇索引回表快速查询数据的过程
普通索引，查询条件里面包含is null或者is not null
eq_ref
在连接查询时，被驱动表如果基于主键进行等值匹配
const
通过聚簇索引或者唯一二级索引（即unique key）+聚簇索引回表快速查询数据的过程
性能超高，速度很快，常量级的
system
当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下
explain extended select * from (select * from film where id =1) tmp 临时表中只有一条记录
NULL： MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成explain select min(id) from film id是主键
possible_keys
指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null），可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mys ql认为索引对此查询帮助不大，选择了全表查询
key
显示MySQL实际决定使用的键（索引)
key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，
并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）不损失精确性的情况下，长度越短越好
并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）不损失精确性的情况下，长度越短越好
索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。
计算规则如下
字符串
char(n)：n字节长度
varchar(n)：2字节存储字符串长度，如果是utf-8，则长度3n + 2
char(n)：n字节长度
varchar(n)：2字节存储字符串长度，如果是utf-8，则长度3n + 2
数值类型
tinyint：1字节
smallint：2字节
int：4字节
bigint：8字节
tinyint：1字节
smallint：2字节
int：4字节
bigint：8字节
时间类型
date：3字节timestamp：4字节
datetime：8字节如果字段允许为 NULL，需要1字节记录是否为 NULL
date：3字节timestamp：4字节
datetime：8字节如果字段允许为 NULL，需要1字节记录是否为 NULL
ref
使用某个字段的索引进行等值匹配搜索的时候，跟索引列进行等值匹配的那个目标值的一些信息
rows
预估通过索引或者别的方式访问这个表的时候，大概会读取到的数据量。
filtered
经过搜索条件过来之后剩余数据的百分比
一个半分比的值，rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数（前一个表指 explain 中的id值比当前表id值小的表）
Extra
Using where
不用读取表中所有信息，仅通过索引就可以获取所需数据，
这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤
这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤
Using temporary
表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by;distinct
Using filesort
基于磁盘文件排序，性能很差。如果我们用order by, group by, union, distinct之类的语法，如果没法直接利用索引进行，那么会基于临时表完成，会有大量的磁盘操作，性能非常低。
Using join buffer
改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。
如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能
如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能
Impossible where
这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）
Select tables optimized away
这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行
No tables used
Query语句中使用from dual 或不含任何from子句
缺点：
• EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
• EXPLAIN不考虑各种Cache
• EXPLAIN不能显示MySQL在执行查询时所作的优化工作
• 部分统计信息是估算的，并非精确值
• EXPLAIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划
• EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
• EXPLAIN不考虑各种Cache
• EXPLAIN不能显示MySQL在执行查询时所作的优化工作
• 部分统计信息是估算的，并非精确值
• EXPLAIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划
SHOW WARNINGS 在explain执行后执行，查看翻译后的sql
索引优化规则
使用多个索引的硬性要求
如果有联合索引，必须把联合索引里面每个字段都放SQL里，而且必须是等值匹配。
通过主键+其他二级索引等值匹配，也有可能做一个多索引查询和交集。
最佳左前缀法则
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列
不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei'; 使用索引
EXPLAIN SELECT * FROM employees WHERE left(name,3) = 'LiLei'; 未使用索引
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei'; 使用索引
EXPLAIN SELECT * FROM employees WHERE left(name,3) = 'LiLei'; 未使用索引
存储引擎不能使用索引中范围条件右边的列
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager'; 使用索引
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='manager'; 未使用索引
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager'; 使用索引
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='manager'; 未使用索引
尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少select *语句
EXPLAIN SELECT name,age FROM employees WHERE name= 'LiLei' AND age = 23 AND position ='manager'; 只查询索引不用查询具体的数据，效率更高
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 23 AND position ='manager';
EXPLAIN SELECT name,age FROM employees WHERE name= 'LiLei' AND age = 23 AND position ='manager'; 只查询索引不用查询具体的数据，效率更高
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 23 AND position ='manager';
使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描
is null,is not null 也无法使用索引
like以通配符开头（'$abc...'）mysql索引失效会变成全表扫描操作
字符串不加单引号索引失效（数据类型不要转换）
EXPLAIN SELECT * FROM employees WHERE name = '1000';
EXPLAIN SELECT * FROM employees WHERE name = 1000;
EXPLAIN SELECT * FROM employees WHERE name = '1000';
EXPLAIN SELECT * FROM employees WHERE name = 1000;
or 只有两边都有索引才走索引，如果都没有或者只有一个是不走索引的
in操作能避免则避免，若实在避免不了，需要仔细评估in后边的集合元素数量，控制在1000个之内
union all 不去重复，union去重复，union使用了临时表，应尽量避免使用临时表
order by如果根据多个值进行排序，顺序和排序方式 与 多个值建的组合索引一致，否则不走索引
多表关联查询/结果集聚合，根据一个表查一波数据，这个表叫驱动表，再根据这个表去另外一个表查一波数据进行关联，这个表叫被驱动表。应该小表驱动大表
优化方式
优化数据库表结构的设计
字段的数据类型
不同的数据类型的存储和检索方式不同，对应的性能也不同，所以说要合理的选用字段的数据类型。比如人的年龄用无符号的unsigned tinyint即可，没必要用integer
数据类型的长度
数据库最终要写到磁盘上，所以字段的长度也会影响着磁盘的I/O操作，如果字段的长度很大，那么读取数据也需要更多的I/O, 所以合理的字段长度也能提升数据库的性能。比如用户的手机号11位长度，没必要用255个长度
表的存储引擎
分库分表
数据库参数配置优化
主从复制，读写分离
数据库编码: 采用utf8mb4而不使用utf8
字段名
MySQL 在 Windows 下不区分大小写，但在 Linux 下默认是区分大小写。因此，数据库名、 表名、字段名，都不允许出现任何大写字母，避免节外生枝。
一般所有表都要有id, id必为主键，类型为bigint unsigned,单表时自增、步长为1; 有些特殊场景下(如在高并发的情况下该字段的自增可能对效率有比价大的影响)id是通过程序计算出来的一个唯一值而不是通过数据库自增长来实现的。
一般情况下主键id和业务没关系的，例如订单号不是主键id，一般是订单表中的其他字段，一般订单号order_code为字符类型
一般情况下每张表都有着四个字段create_id,create_time,update_id,update_time, 其中create_id表示创建者id，create_time表示创建时间，update_id表示更新者id，update_time表示更是时间，这四个字段的作用是为了能够追踪数据的来源和修改
表达是与否概念的字段，必须使用 is_xxx 的方式命名，数据类型是 unsigned tinyint (1 表示是，0 表示否), 任何字段如果为非负数，必须是unsigned。表达逻辑删除的字段名 is_deleted，1 表示删除，0 表示未删除
如果某个值能通过其他字段能计算出来就不需要用个字段来存储，减少存储的数据（但为了提高查询效率，可以适当的数据冗余，注意是适当
）
强烈建议不使用外键, 数据的完整性靠程序来保证
单条记录大小禁止超过8k， 一方面字段不要太多，有的都能上百，甚至几百个，另一方面字段的内容不易过大像文章内容等这种超长内容的需要单独存到另一张表
一般所有表都要有id, id必为主键，类型为bigint unsigned,单表时自增、步长为1; 有些特殊场景下(如在高并发的情况下该字段的自增可能对效率有比价大的影响)id是通过程序计算出来的一个唯一值而不是通过数据库自增长来实现的。
一般情况下主键id和业务没关系的，例如订单号不是主键id，一般是订单表中的其他字段，一般订单号order_code为字符类型
一般情况下每张表都有着四个字段create_id,create_time,update_id,update_time, 其中create_id表示创建者id，create_time表示创建时间，update_id表示更新者id，update_time表示更是时间，这四个字段的作用是为了能够追踪数据的来源和修改
表达是与否概念的字段，必须使用 is_xxx 的方式命名，数据类型是 unsigned tinyint (1 表示是，0 表示否), 任何字段如果为非负数，必须是unsigned。表达逻辑删除的字段名 is_deleted，1 表示删除，0 表示未删除
如果某个值能通过其他字段能计算出来就不需要用个字段来存储，减少存储的数据（但为了提高查询效率，可以适当的数据冗余，注意是适当
）
强烈建议不使用外键, 数据的完整性靠程序来保证
单条记录大小禁止超过8k， 一方面字段不要太多，有的都能上百，甚至几百个，另一方面字段的内容不易过大像文章内容等这种超长内容的需要单独存到另一张表
字段类型
字符类型
不同存储引擎对char和varchar的使用原则不同，myisam:建议使用国定长度的数据列代替可变长度。
innodb：建议使用varchar，大部分表都是使用innodb，所以varchar的使用频率更高
innodb：建议使用varchar，大部分表都是使用innodb，所以varchar的使用频率更高
数值类型
金额类型的字段尽量使用long用单位分表示，尽量不要使用bigdecimal，严禁使用float和double因为计算时会丢失经度
如果需要使用小数严禁使用float，double，使用定点数decimal，decimal实际上是以字符串的形式存储的，所以更加精确，java中与之对应的数据类型为BigDecimal
如果值为非负数，一定要使用unsigned，无符号不仅能防止负数非法数据的保存，而且还能增大存储的范围
如果需要使用小数严禁使用float，double，使用定点数decimal，decimal实际上是以字符串的形式存储的，所以更加精确，java中与之对应的数据类型为BigDecimal
如果值为非负数，一定要使用unsigned，无符号不仅能防止负数非法数据的保存，而且还能增大存储的范围
日期类型
根据实际需要选择能够满足应用的最小存储日期类型。
如果应用只需要记录年份，那么仅用一个字节的year类型。
如果记录年月日用date类型, date占用4个字节，存储范围10000-01-01到9999-12-31
如果记录时间时分秒使用它time类型
如果记录年月日并且记录的年份比较久远选择datetime，而不要使用timestamp,因为timestamp表示的日期范围要比datetime短很多
如果记录的日期需要让不同时区的用户使用，那么最好使用timestamp, 因为日期类型值只有它能够和实际时区相对应
datetime默认存储年月日时分秒不存储毫秒fraction，如果需要存储毫秒需要定义它的宽度datetime(6)
timestamp与datetime
两者都可用来表示YYYY-MM-DD HH:MM:SS[.fraction]类型的日期。
都可以使用自动更新CURRENT_TIMESTAMP
对于TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。而对于DATETIME，不做任何改变，基本上是原样输入和输出。
timestamp占用4个字节：timestamp所能存储的时间范围为：’1970-01-01 00:00:01.000000’ 到 ‘2038-01-19 03:14:07.999999’
datetime占用8个字节 ：datetime所能存储的时间范围为：’1000-01-01 00:00:00.000000’ 到 ‘9999-12-31 23:59:59.999999’
总结：TIMESTAMP和DATETIME除了存储范围和存储方式不一样，没有太大区别。如果需要使用到时区就必须使用timestamp，如果不使用时区就使用datetime因为datetime存储的时间范围更大
注意：
禁止使用字符串存储日期，一般来说日期类型比字符串类型占用的空间小，日期时间类型在进行查找过滤是可以利用日期进行对比，这比字符串对比高效多了，日期时间类型有丰富的处理函数，可以方便的对日期类型进行日期的计算
也尽量不要使用int来存储时间戳
如果应用只需要记录年份，那么仅用一个字节的year类型。
如果记录年月日用date类型, date占用4个字节，存储范围10000-01-01到9999-12-31
如果记录时间时分秒使用它time类型
如果记录年月日并且记录的年份比较久远选择datetime，而不要使用timestamp,因为timestamp表示的日期范围要比datetime短很多
如果记录的日期需要让不同时区的用户使用，那么最好使用timestamp, 因为日期类型值只有它能够和实际时区相对应
datetime默认存储年月日时分秒不存储毫秒fraction，如果需要存储毫秒需要定义它的宽度datetime(6)
timestamp与datetime
两者都可用来表示YYYY-MM-DD HH:MM:SS[.fraction]类型的日期。
都可以使用自动更新CURRENT_TIMESTAMP
对于TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。而对于DATETIME，不做任何改变，基本上是原样输入和输出。
timestamp占用4个字节：timestamp所能存储的时间范围为：’1970-01-01 00:00:01.000000’ 到 ‘2038-01-19 03:14:07.999999’
datetime占用8个字节 ：datetime所能存储的时间范围为：’1000-01-01 00:00:00.000000’ 到 ‘9999-12-31 23:59:59.999999’
总结：TIMESTAMP和DATETIME除了存储范围和存储方式不一样，没有太大区别。如果需要使用到时区就必须使用timestamp，如果不使用时区就使用datetime因为datetime存储的时间范围更大
注意：
禁止使用字符串存储日期，一般来说日期类型比字符串类型占用的空间小，日期时间类型在进行查找过滤是可以利用日期进行对比，这比字符串对比高效多了，日期时间类型有丰富的处理函数，可以方便的对日期类型进行日期的计算
也尽量不要使用int来存储时间戳
是否为null
MySQL字段属性应该尽量设置为NOT NULL，除非你有一个很特别的原因去使用 NULL 值，你应该总是让你的字段保持 NOT NULL 。
在MySql中NULL其实是占用空间的，“可空列需要更多的存储空间”：需要一个额外字节作为判断是否为NULL的标志位“需要mysql内部进行特殊处理”， 而空值”“是不占用空间的。
含有空值的列很难进行查询优化，而且对表索引时不会存储NULL值的，所以如果索引的字段可以为NULL，索引的效率会下降很多。因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替null。
联表查询的时候，例如SELECT user.username, info.introduction FROM tbl_user user LEFT JOIN tbl_userinfo info ON user.id = info.user_id; 如果tbl_userinfo.introduction设置的可以为null, 假如这条sql查询出了对应的记录，但是username有值，introduction没有值，那么就不是很清楚这个introduction是没有关联到对应的记录，还是关联上了而这个值为null，null意思表示不明确，有歧义
注意：NULL在数据库里是非常特殊的，任何数跟NULL进行运算都是NULL, 判断值是否等于NULL，不能简单用=，而要用IS NULL关键字。使用 ISNULL()来判断是否为 NULL 值，NULL 与任何值的直接比较都为 NULL。
1) NULL<>NULL的返回结果是NULL，而不是false。
2) NULL=NULL的返回结果是NULL，而不是true。
3) NULL<>1的返回结果是NULL，而不是true。
在MySql中NULL其实是占用空间的，“可空列需要更多的存储空间”：需要一个额外字节作为判断是否为NULL的标志位“需要mysql内部进行特殊处理”， 而空值”“是不占用空间的。
含有空值的列很难进行查询优化，而且对表索引时不会存储NULL值的，所以如果索引的字段可以为NULL，索引的效率会下降很多。因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替null。
联表查询的时候，例如SELECT user.username, info.introduction FROM tbl_user user LEFT JOIN tbl_userinfo info ON user.id = info.user_id; 如果tbl_userinfo.introduction设置的可以为null, 假如这条sql查询出了对应的记录，但是username有值，introduction没有值，那么就不是很清楚这个introduction是没有关联到对应的记录，还是关联上了而这个值为null，null意思表示不明确，有歧义
注意：NULL在数据库里是非常特殊的，任何数跟NULL进行运算都是NULL, 判断值是否等于NULL，不能简单用=，而要用IS NULL关键字。使用 ISNULL()来判断是否为 NULL 值，NULL 与任何值的直接比较都为 NULL。
1) NULL<>NULL的返回结果是NULL，而不是false。
2) NULL=NULL的返回结果是NULL，而不是true。
3) NULL<>1的返回结果是NULL，而不是true。
实例
EXPLAIN SELECT * FROM tbl_user LIMIT 100000,2;
EXPLAIN SELECT * FROM tbl_user u INNER JOIN (SELECT id FROM tbl_user ORDER BY id ASC LIMIT 10000,2) temp ON u.id = temp.id;
id为主键，性能高于第一条全表扫描
EXPLAIN SELECT * FROM tbl_user u INNER JOIN (SELECT id FROM tbl_user ORDER BY id ASC LIMIT 10000,2) temp ON u.id = temp.id;
id为主键，性能高于第一条全表扫描
where中如果有多个过滤条件，在没有索引的情况下将过滤多的写在前面，过滤少的写在后面
禁止使用select *，需要什么字段就去取哪些字段
防止回表
不要使用count(列名)或 count(常量)来替代 count()，count()是SQL92定义的标准统计行数的语法，跟数据库无关，跟 NULL和非NULL无关。 说明:count(*)会统计值为NULL 的行，而count(列名)不会统计此列为NULL值的行
禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。避免使用存储过程、触发器
除了 IO 瓶颈之外，SQL优化中需要考虑的就是 CPU 运算量的优化了。order by, group by,distinct … 都是消耗 CPU 的大户（这些操作基本上都是 CPU 处理内存中的数据比较运算）。当我们的 IO 优化做到一定阶段之后，降低 CPU 计算也就成为了我们 SQL 优化的重要目标
锁
分类
乐观锁和悲观锁
乐观锁
根据版本号控制
悲观锁
锁定表或者行，让其他数据操作等待
读锁(共享锁)
针对同一份数据，多个读操作可以同时进行而不会互相影响
不能进行写操作
写锁(排他锁)
当前写操作没有完成前，它会阻断其他写锁和读锁
表锁和行锁
表锁
表锁偏向MyISAM存储引擎，开销小，加锁快，无死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低
当前session对该表的增删改查都没有问题，其他session对该表的所有操作被阻塞
当前session对该表的增删改查都没有问题，其他session对该表的所有操作被阻塞
互斥性
锁机制 独占锁 意向独占锁 共享锁 意向共享锁
独占锁 互斥 不互斥 互斥 互斥
意向独占锁 互斥 不互斥 互斥 不互斥
共享锁 互斥 互斥 不互斥 不互斥
意向共享锁 互斥 不互斥 不互斥 不互斥
独占锁 互斥 不互斥 互斥 互斥
意向独占锁 互斥 不互斥 互斥 不互斥
共享锁 互斥 互斥 不互斥 不互斥
意向共享锁 互斥 不互斥 不互斥 不互斥
类型
独占锁
lock tables tableName write
lock tables tableName write
意向独占锁（事务自动）
共享锁
lock tables tableName read
lock tables tableName read
意向共享锁（事务自动）
行锁
行锁偏向InnoDB存储引擎，开销大，加锁慢，会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发度也最高。InnoDB与MYISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁
快照读
snapshot read
定义：读取记录的可见版本（可能是历史版本），不加锁
表现
普通 select
当前读
current read
定义：读取记录最新版本，会加锁保证不并发修改
表现
特殊查询 select .. where ？ for update 加独占锁 X
select .. where ？ lock in share mode 加共享锁 S
insert，update，delete等 DML操作 加x锁
形态
更新一行数据必须把他所在的数据页从磁盘文件读取到缓存中才能更新，所以此时这行数据和关联的锁数据结构，都是在内存里面的。
特点
针对当前读，innoDB交互是一条一条的进行，加锁也是一条一条
过程
发送一个update语句
使用当前读取出第一条并加锁
mysql 收到会再发一个update请求，更新这条记录
完成后再读下一条重复上述过程
两阶段锁实现
2PL就是将加锁/解锁分为两个完全不相交的阶段。加锁阶段：只加锁，不放锁。解锁阶段：只放锁，不加锁
通过索引+mvcc 实现
互斥性
锁类型 独占锁 共享锁
独占锁 互斥 互斥
共享锁 互斥 不互斥
独占锁 互斥 互斥
共享锁 互斥 不互斥
元数据锁（Metadata Locks）
执行DDL时，会阻塞增删改操作，执行增删改操作时，会阻塞DDL操作。
行锁分析
show status like'innodb_row_lock%';
Innodb_row_ lock_current_wait
当前正在等待锁定的数量
Innodb_row_ lock_time
从系统启动到现在锁定总时间长度
Innodb_row_ lock_time_avg
每次等待所花平均时间
Innodb_row_ lock_time_max
从系统启动到现在等待最长的一次所花时间
Innodb_row_ lock_waits
系统启动后到现在总共等待的次数
死锁
Session _1执行：select *from account where i d= 1 for update;
Session _2执行：select *from account where i d= 2 for update;
Session _1执行：select *from account where i d= 2 for update;
Session _2执行：select *from account where i d= 1 for update;
查看近期死锁日志信息：show engine inno db statu s\G;
Session _2执行：select *from account where i d= 2 for update;
Session _1执行：select *from account where i d= 2 for update;
Session _2执行：select *from account where i d= 1 for update;
查看近期死锁日志信息：show engine inno db statu s\G;
mvcc
为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读;
为了实现读-写冲突不加锁，而这个读指的就是快照读, 而非当前读，当前读实际上是一种加锁的操作，是悲观锁的实现
为了实现读-写冲突不加锁，而这个读指的就是快照读, 而非当前读，当前读实际上是一种加锁的操作，是悲观锁的实现
一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照
为数据库解决
在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能
同时还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决更新丢失问题
两个组合
MVCC + 悲观锁:MVCC解决读写冲突，悲观锁解决写写冲突
MVCC + 乐观锁:MVCC解决读写冲突，乐观锁解决写写冲突
原理主要是依赖记录中的 3个隐式字段，undo日志 ，Read View 来实现的
隐含字段
DB_TRX_ID 6字节 ;事务id，随事务增加
DB_ROLL_PTR 7字节 回滚指针
DB_ROW_ID 隐藏的id，随记录自然增长
undo log
insert undo log:代表事务在insert新记录时产生的undo log, 只在事务回滚时需要，并且在事务提交后可以被立即丢弃
update undo log:事务在进行update或delete时产生的undo log; 不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快照读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除
过程
select
1、当前session 事务版本号>=DB_TRX_ID
2、delete_id is null or 当前事务号<DB_TRX_ID
insert
新插入的行，DB_TRX_ID=当前事务号
update
更新行的时候，InnoDB会把原来行复制一份，DB_TRX_ID=当前事务号
delete
设置删除版本号为当前事务id (DB_TRX_ID=当前事务号??) delete_id = 当前事务号
相当于标记为删除，而不是物理的删除。真是的删除是在InnoDB的purge线程去做的。
commit
修改事务状态为commit,在 redo log 中写入 commit 记录
rollback
根据当前回滚指针从undo log中找出事务修改前的版本，并恢复
ReadView机制
判断规则
从数据的最早版本开始判断（undo log）
数据版本trx_id = creator_trx_id， 当前事务修改，可以访问。
数据版本trx_id < min_trx_id，这个版本在生成readview已经提交，可以访问。
数据版本trx_id > max_trx_id，这个版本是生成read_view之后才开启的事务建立的，不能访问。
数据版本trx_id在min_trx_id和max_trx_id之间，看是否在m_ids中，不过在，不可以访问，不在则可以访问，
如果当前版本不可见，如果不在，可以。
机制
开始一个查询的时候，便会创建一个ReadView视图，可以保证你读到你事务开启前，别的提交事务更新的值，还有自己事务更新的值。别的事务正在运行，然后你的事务开启之后，别的事务更新了的值(当每个事务开启时，都会被分配一个ID, 这个ID是递增的，所以最新的事务，ID值越大)，你是读不到的。或者是你事务开启了之后，比你晚开启的事务更新了的值，你也是读不到的。就解决了幻读和不可重复读的问题。整个过程是通过ReadView+undo log日志链条的机制实现的。
主从同步
主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。MySQL 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表
原理
MySql主库在事务提交时会把数据变更作为事件记录在二进制日志Binlog中
主库推送二进制日志文件Binlog中的事件到从库的中继日志Relay Log中，之后从库根据中继日志重做数据变更操作，通过逻辑复制来达到主库和从库的数据一致性
MySql通过三个线程来完成主从库间的数据复制，其中Binlog Dump线程跑在主库上，I/O线程和SQL线程跑着从库上
当在从库上启动复制时，首先创建I/O线程连接主库，主库随后创建Binlog Dump线程读取数据库事件并发送给I/O线程，I/O线程获取到事件数据后更新到从库的中继日志Relay Log中去，之后从库上的SQL线程读取中继日志Relay Log中更新的数据库事件并应用
MySql主库在事务提交时会把数据变更作为事件记录在二进制日志Binlog中
基于GTID复制实现的工作原理
主节点更新数据时，会在事务前产生GTID，一起记录到binlog日志中
从节点的I/O线程将变更的bin log，写入到本地的relay log中
SQL线程从relay log中获取GTID，然后对比本地binlog是否有记录（所以MySQL从节点必须要开启binary log）
如果有记录，说明该GTID的事务已经执行，从节点会忽略
如果没有记录，从节点就会从relay log中执行该GTID的事务，并记录到bin log
在解析过程中会判断是否有主键，如果没有就用二级索引，如果有就用全部扫描
用途
读写分离
数据实时备份，当系统中某个节点发生故障时，可以方便的故障切换
高可用HA
架构扩展
主从形式
一主一从
一主多从
多主一从
双主复制
互做主从复制，每个master既是master，又是另外一台服务器的slave。这样任何一方所做的变更，都会通过复制应用到另外一方的数据库中
级联复制
部分slave的数据同步不连接主节点，而是连接从节点
主节点有太多的从节点，就会损耗一部分性能用于replication
主从复制模式
异步模式(MYSQL的默认设置)
主节点不会主动push bin log到从节点，这样有可能导致failover的情况下，也许从节点没有即时地将最新的bin log同步到本地
半同步模式
主节点只需要接收到其中一台从节点的返回信息，就会commit;否则需要等待直到超时时间然后切换成异步模式再提交；
这样做的目的可以使主从数据库的数据延迟缩小，可以提高数据安全性，确保了事务提交后，binlog至少传输到了一个从节点上，
不能保证从节点将此事务更新到db中。性能上会有一定的降低，响应时间会变长
这样做的目的可以使主从数据库的数据延迟缩小，可以提高数据安全性，确保了事务提交后，binlog至少传输到了一个从节点上，
不能保证从节点将此事务更新到db中。性能上会有一定的降低，响应时间会变长
全同步模式
主节点和从节点全部执行了commit并确认才会向客户端返回成功
复制类型
基于二进制日志点的复制
三种方式
基于SQL语句的复制（statement-based replication，SBR
基于行的复制（row-based replication，RBR)
混合模式复制（mixed-based replication,MBR)
基于GTID的复制（MySQL>=5.7推荐使用）
0 条评论
下一页
