mysql
2020-11-20 17:44:31 54 举报AI智能生成
msyql
作者其他创作
大纲/内容
mysql
存储引擎
MYISAM
数据文件和索引文件分开（非聚集）frm+MYD+MYI
不支持事务，不支持行级锁
innodb
表数据文件就是按B+tree组织的索引结构文件(聚集)，frm（表结构）+ibd
支持事务
包含了完整的数据记录
主键索引包含数据，非主键索引不包含数据，只包含主键值，（基于数据一致性与节省空间考虑）
支持事务（TRANSACTION）支持行级锁
优化
MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。
、order by满足两种情况会使用Using index。1) order by语句使用索引最左前列。2) 使用where子句与order by子句条件列组合满足索引最左前列。
3、尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。
、如果order by的条件不在索引列上，就会产生Using filesort。
单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；
双路排序：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段
能用覆盖索引尽量用覆盖索引
group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于groupby的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中的限定条件就不要去having限定了
分库分表
垂直拆分
一个数据库由很多表的构成，每个表对应着不同的业务，垂直切分是指按照业务将表进行分类，分布到不同的数据库上面，这样也就将数据或者说压力分担到不同的库上面
优点
1拆分后业务清晰，拆分规则明确
2. 系统之间整合或扩展容易。
3. 数据维护简单。
缺点
1. 部分业务表无法join，只能通过接口方式解决，提高了系统复杂度。
2. 受每种业务不同的限制存在单库性能瓶颈，不易数据扩展跟性能提高。
3. 事务处理复杂。
水平拆分
垂直拆分后遇到单机瓶颈，可以使用水平拆分。相对于垂直拆分的区别是：垂直拆分是把不同的表拆到不同的数据库中，而水平拆分是把同一个表拆到不同的数据库中。相对于垂直拆分，水平拆分不是将表的数据做分类，而是按照某个字段的某种规则来分散到多个库之中，每个表中包含一部分数据。简单来说，我们可以将数据的水平切分理解为是按照数据行的切分，就是将表中 的某些行切分到一个数据库，而另外的某些行又切分到其他的数据库中，主要有分表，分库两种模式
1. 不存在单库大数据，高并发的性能瓶颈。
2. 对应用透明，应用端改造较少。
3. 按照合理拆分规则拆分，join操作基本避免跨库。
4. 提高了系统的稳定性跟负载能力。
1. 拆分规则难以抽象。
2. 分片事务一致性难以解决。
3. 数据多次扩展难度跟维护量极大。
4. 跨库join性能较差。
数据结构
B+Tree
非叶子节点，不存储数据，只存数索引（冗余），存储更多的数据
叶子节点，包含所有索引字段
叶子节点用指针连接，提高区间访问性能
b-tree
叶节点，具有相同的深度，叶节点指针为空
所有索引不重复
节点中的数据索引从左到有一次递增
索引
原则
最左前缀原则
explain执行计划
列：id越大执行优先级越高，一样大谁在前，谁先执行
simple:：简单查询
primery:复杂查询中最外层的 select
subquery:包含在 select 中的子查询（不在 from 子句中）
derived:包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义）
type
性能：system > const > eq_ref > ref > range > index > ALL
possible_keys
可能用到的索引
key
实际用的哪些key
key_len
这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。
计算规则：
字符串
char(n)=n个字节
varchar(n)：2字节存储字符串长度，如果是utf-8，则长度 3n+ 2
数值
tinyint：1字节
smallint：2字节
int：4字节
bigint:8个字节
时间
date：3字节
timestamp：4字节
datetime：8字节
如果字段允许为 NULL，需要1字节记录是否为 NULL索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。
ref
这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）
rows
mysql预估要读取的条数
Extra列
Using index：使用覆盖索引
Using where：使用 where 语句来处理结果，查询的列未被索引覆盖
Using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范围；
Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。
Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。
Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段是
锁
性能
乐观
悲观
操作类型
读锁（悲观，共享），同一数据允许多个操作同时进行读，
写锁（悲观，排他），写操作没有完成之前，不允许其他写锁和读锁
颗粒度
表锁
锁整张表，开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
查看锁：show open tables;
解锁：unlock tables;
读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。
行锁
每次操作锁住一行数据。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。
并发事务处理带来的问题
更新丢失
当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题–最后的更新覆盖了由其他事务所做的更新
脏读
事务A读取到了事务B已经修改但尚未提交的数据，还在这个数据基础上做了操作。此时，如果B事务回滚，A读取的数据无效，不符合一致性要求
不可重读
事务A读取到了事务B已经提交的修改数据，不符合隔离性
幻读
事务A读取到了事务B提交的新增数据，不符合隔离性
事务隔离级别
读未提交
可能出现脏读
可能出现不可重复读
可能出现幻读
读已提交
不能出现脏读
可重复读
不能出现不可重复读
可串行化
都不能出现
尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁合理设计索引，尽量缩小锁的范围尽可能减少检索条件范围，避免间隙锁尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行尽可能低级别事务隔离
0 条评论
回复 删除
下一页
