MySQL知识点整理
2020-04-08 19:25:03 35 举报AI智能生成
MySQL知识点个人整理
作者其他创作
大纲/内容
DB、DBS和DBMS的区别是什么
DBMS
DB
DBS
注意
设计数据表的原则
“三少一多”原则
1.数据表的个数越少越好
2.数据表中的字段个数越少越好
3.数据表中联合主键的字段个数越少越好
4.使用主键和外键越多越好
常见约束
约束：主键、外键、唯一性、NOT NULL、 DEFAULT、CHECK约束等。
范式设计 : 3NF
数据库的设计范式都有哪些？
目前关系型数据库一共有6种范式
从1NF到3NF
1NF指的是数据库表中的任何属性都是原子性的，不可再分.
2NF指的数据表里的非主属性都要和这个数据表的候选键有完全依赖关系。
3NF在满足2NF的同时，对任何非主属性都不传递依赖于候选键。
功能分类
DDL
数据定义语言
修改表结构
添加字段
ALTER TABLE player ADD (age int(11));
修改字段名，将age字段改成player_age
ALTER TABLE player RENAME COLUMN age to player_age
修改字段的数据类型
ALTER TABLE player MODIFY (player_age float(3,1));
删除字段
ALTER TABLE student DROP COLUMN player_age;
DML
DCL
DQL
视图
创建视图:CREATE VIEW
存储过程
如何实现
事务
特性:ACID
原子性（Atomicity）
一致性（Consistency）
就是隔离性（Isolation）
持久性（Durability）
控制语句
START TRANSACTION或者 BEGIN
作用是显式开启一个事务
COMMIT
提交事务。当提交事务后，对数据库的修改是永久性的。
ROLLBACK
意为回滚事务。
SAVEPOINT
在事务中创建保存点，方便后续针对保存点进行回滚。一个事务中可以存在多个保存点。
RELEASE SAVEPOINT
删除某个保存点。
SET TRANSACTION
设置事务的隔离级别
自动提交
mysql> set autocommit =0; //关闭⾃动提交
并发处理
三种异常
脏读（Dirty Read）
读到了其他事务还没有提交的数据
不可重复读（Nnrepeatable Read）
对某数据进行读取，发现两次读取的结果不同，也就是说没有读到相同的内容。这是因为 有其他事务对这个数据同时进行了修改或删除
幻读（Phantom Read）
事务A根据条件查询得到了N条数据，但此时事务B更改或者增加了M条符合事务A查询条件的数 据，这样当事务A再次进行查询的时候发现会有N+M条数据，产生了幻读
隔离级别
索引
逻辑分类
MySQL
普通索引、唯一索引和主键索引
MySQL自带的全文 索引只支持英文。
全文索引
ES(ElasticSearch)和Solr
物理分类
聚集索引
非聚集索引
区别
聚集索引的叶子节点存储的就是我们的数据记录，非聚集索引的叶子节点存储的是数据位置。非聚集索 引不会影响数据表的物理存储顺序。
一个表只能有一个聚集索引，因为只能有一种排序存储的方式，但可以有多个非聚集索引，也就是多个 索引目录提供数据检索。
使用聚集索引的时候，数据的查询效率高，但如果对数据进行插入，删除，更新等操作，效率会比非聚 集索引低。
其他分类
单一索引
联合索引
最左匹配原则
索引的原理
B+tree
为什么
二叉书O(n)
平衡⼆叉搜索树（AVL树）O(log2n)
什么情况使用索引?
1.字段的数值有唯⼀性的限制，⽐如⽤⼾名
2.频繁作为WHERE查询条件的字段，尤其在数据表⼤的情况下
3.需要经常GROUP BY和ORDER BY的列
4.UPDATE、DELETE的WHERE条件列，⼀般也需要创建索引
5.DISTINCT字段需要创建索引
什么时候不需要创建索引
WHERE条件（包括GROUP BY、ORDER BY）⾥⽤不到的字段
第⼆种情况是，表记录太少
第三种情况是，字段中如果有⼤量重复数据，也不⽤创建索引，⽐如性别字段。
最后⼀种情况是，频繁更新的字段不⼀定要创建索引。因为更新数据的时候，也需要更新索引，如果索引太 多，在更新索引的时候也会造成负担，从⽽影响效率。
什么情况下索引失效
1.如果索引进⾏了表达式计算，则会失效
2.如果对索引使⽤函数，也会造成失效
3.在WHERE⼦句中，如果在OR前的条件列进⾏了索引，⽽在OR后的条件列没有进⾏索引，那么索引会失效。
4.当我们使⽤LIKE进⾏模糊查询的时候，后⾯不能是%
5.索引列为NULL进⾏判断的时候也会失效。
6.我们在使⽤联合索引的时候要注注意最左原则
为什么没有理想的索引？
窄索引
包含索引列数为1或2
会通过主键查找相应数据,称为回表
宽索引
包含的索引列数⼤于2
可以防止回表
过滤因子
where后的条件
针对SQL查询的理想索引设计：三星索引
1. 在WHERE条件语句中，找到所有等值谓词中的条件列，将它们作为索引⽚中的开始列；
2. 将 GROUP BY和ORDER BY中的列加⼊到索引中；
3. 将SELECT字段中剩余的列加⼊到索引中。
为什么很难存在理想的的索引设计
1. 如果数据量很⼤,过多索引所需要的磁盘空间可能会成为⼀个 问题，对缓冲池所需空间的压⼒也会增加。
2. 增加了索引维护的成本。
你能看到针对⼀条SQL查询来说，三星索引是个理想的⽅式，但实际运⾏起来我们要考虑更多维护的成本， 在索引效率和索引维护之间进⾏权衡。
设置合理索引
⼀张表的索引个数不宜过多
在索引⽚中，我们也需要控制索引列的数量
单列索引和复合索引的⻓度也需要控制
没有理想的索引, 只有适合的索引设计. 需要在索引效率和维护成本中进行平衡
缓冲池
数据库缓冲池
如何读取数据
执⾏SQL语句的时候更新了缓存池中的数据，那么这些数据会⻢上同步到磁盘上吗？
做checkpoint的机制将数据回写到磁盘
脏⻚（dirty page）
指的是缓冲池中被修 改过的⻚，与磁盘上的数据⻚不⼀致。
查看缓冲池的⼤⼩
show variables like 'innodb_buffer_pool_size'
设置缓冲池大小 128MB
set global innodb_buffer_pool_size = 134217729
查看缓冲池个数
mysql > show variables like 'innodb_buffer_pool_instances'
开启多个缓冲池，你⾸先需要 将innodb_buffer_pool_size参数设置为⼤于等于1GB
然后 再针对innodb_buffer_pool_instances参数进⾏修改
数据⻚加载的三种⽅式
1. 内存读取
如果该数据存在于内存中，基本上执⾏时间在1ms左右，效率还是很⾼的。
2.随机读取
如果数据没有在内存中，就需要在磁盘上对该⻚进⾏查找，整体时间预估在10ms左右
3. 顺序读取
通过last_query_cost统计SQL语句的查询成本
锁
按照锁粒度进⾏划分
分别为⾏锁、⻚锁和表锁
从数据库管理的⻆度对锁进⾏划分
共享锁
能被其他用户读取,但是不能修改
排它锁
其他事务⽆法对已锁 定的数据进⾏查询或修改
意向锁（Intent Lock）
从程序员的⻆度对进⾏划分
乐观锁
通过程序来实现
我们可以采⽤版本号机制或 者时间戳机制实现
悲观锁
上面那些数据库的锁
防止死锁
如果事务涉及多个表,操作比较复杂, 那么我们可以尽量一次锁定所有的资源, 而不是逐步来获取
如果事务需要更新数据表中大部分数据, 而且数据表又比较大, 这时可以采用锁升级的方式, 比如将行锁升级为表锁
不同事务并发读写多张数据表, 可以约定访问表的顺序, 采用相同的顺序可以降低死锁发生的概率 .
采用乐观锁
MVCC
什么是MVCC
MVCC是通过数据⾏的多个版本管理来实现数据库的并发控制
使用MVCC的好处
读写互相不阻塞
降低了死锁概率
解决了一致性读写问题
快照读
读取的是快照数据(历史版本)
当前读
读取最先的数据. 加锁的SELECT, 或者插入, 删除, 更新都会进行当前读
InnoDB中的MVCC
事务版本号
当我们每次开启一个事务, 都会从数据库中获得一个事务ID, 这个事务ID是自增长的, 所以通过ID大小可以判断出来事务的时间顺序
行记录的隐藏列
db_row_id, 隐藏的行ID
db_trx_id, 操作这个数据的事务ID
db_roll_ptr, 回滚指针
Undo Log
InnoDB将我们的行记录快照保存在了Undo Log里
Read View, 帮助我们解决了行的可见性问题
Read View是如何工作的
Read View的结构
1. trx_ids，系统当前正在活跃的事务ID集合。
2. low_limit_id，活跃的事务中最⼤的事务ID。
3. up_limit_id，活跃的事务中最⼩的事务ID。
4. creator_trx_id，创建这个Read View的事务ID。
Read View的原则
trx_id < 活跃的最⼩事务ID（up_limit_id）
这个⾏记录在这些活跃的事务创建之前就已经提 交了，那么这个⾏记录对该事务是可⻅的
trx_id > 活跃的最⼤事务ID（low_limit_id）
该⾏记录在这些活跃的事务创建之后才创建，那 么这个⾏记录对当前事务不可⻅
up_limit_id < trx_id < low_limit_id
查询数据的步骤
1. ⾸先获取事务⾃⼰的版本号，也就是事务ID；
2. 获取Read View；
3. 查询得到的数据，然后与Read View中的事务版本号进⾏⽐较；
4. 如果不符合ReadView规则，就需要从Undo Log中获取历史快照；
5. 最后返回符合规则的数据
读已提交时 , 一个事务中的每一次SELECT查询都会获取一次Read View
可重复读时 , 一个事务只在第一次SELECT时会获取Read View , 后面所有的SELECT 都会复用这个Read View
InnoDB是如何解决幻读的
在可重复读的情况下，InnoDB可以通过Next-Key锁+MVCC来解决幻读问题。
在读已提交的情况下，即使采⽤了MVCC⽅式也会出现幻读。
InnoDB 三种⾏锁的⽅式
1. 记录锁：针对单个⾏记录添加锁。
2. 间隙锁（Gap Locking）：可以帮我们锁住⼀个范围（索引之间的空隙），但不包括记录本⾝。采⽤间隙
锁的⽅式可以防⽌幻读情况的产⽣。
锁的⽅式可以防⽌幻读情况的产⽣。
3. Next-Key锁：帮我们锁住⼀个范围，同时锁定记录本⾝，相当于间隙锁+记录锁，可以解决幻读的问题。
查询优化器
SQL语句执行
SQL和NoSQL
SQL
MySQL
如何执行
架构
C/S架构:Client,Server
Client
应用程序
Server
mysqld
三层结构
连接层
SQL层
SQL层结构
查询缓存
解析器
优化器
执行器
到SQL语句在MySQL中的流程是：SQL语句→缓存查询→解析器→优化器→执行器
存储引擎层
存储引擎层结构
磁盘
内存
网络
插件式存储引擎
InnoDB
MyISAM
Memory
NDB
Archive
命令
profile
select version()查看MySQL的版本
函数
聚合函数
COUNT
MAX
MIN
SUM
AVG
性能优化
如何定位
用户的反馈
日志分析
服务器资源使用的监控:通过监控服务器的CPU、内存、I/O等使用情况，可以实时了解服务器的性能使用，与历史情况进行对比。
数据库内部情况监控
调优有什么维度
第一步，选择适合的DBMS
常用的有Oracle，SQL Server和MySQL等
NoSQL阵营包括键值型数据库、文档型数据库、搜索引擎、列式存储和图形数据库。
第二步，优化表设计
MySQL
可以根据不同表的使用需求，选择不同的存储引 擎。
表结构要尽量遵循第三范式的原则
合理使用反范式进行优化
如果分析查询应用比较多，尤其是需要进行多表联查的时候，可以采用反范式进行优化。
表字段的数据类型选择
第三步，优化逻辑查询
合理使用EXISTS子查询和IN子查询
WHERE子句中会尽量避免对字段进行函数运算，它们会让字段的索引失效。
第四步，优化物理查询
合理创建索引
如果数据重复度高，就不需要创建索引。
比如性别这个字段
别对索引字段进行了表达式的计算
会 造成这个字段的索引失效。
要注意联合索引对索引使用的影响。
索引不是越多越好，因为每个索引都需要存储空间，索引多也就意 味着需要更多的存储空间。
第五步，使用Redis或Memcached作为缓存
第六步，库级优化
主从架构 : 读写分离
master写
slave读
分库分表
分区表
性能分析工具定位
优化步骤
观察服务器状态
是否存在周期性波动
是
加缓存, 更改缓存失效策略
是否解决
是 , 结束
否, 仍有不规则延迟卡顿
进入慢查询
否
仍有不规则延迟卡顿
进入慢查询
开启慢查询
SQL执行时间长
优化
1.索引设计优化
2.JOIN表过多, 需要优化
3. 数据表设计优化
是否解决
是
否, SQL查询是否达到瓶颈
SQL等待时间长
调优服务器参数
是否解决
是
否, SQL查询是否达到瓶颈
SQL查询是否达到瓶颈
是
读写分离(主从架构)
分库分表(垂直分库, 垂直分表, 水平分表)
否, 重新检查
慢查询定位
查询是否开启
查询时间阈值
使用mysdqldumpslow工具分析
EXPLAIN查看执行计划
可以得到什么
SQL
type字段
效率
show profile查看执行时间
查看是否开启
当前会话有哪些
查看某Query_ID开销
命令将被弃用, 我们可以从information_schema中的profiling数据表进行查看
收藏
0 条评论
下一页
