左远辉 发表于 2023-4-3 21:36:10

mysql总结

MyiSAM和innodb

MyiSAM:非聚集索引、B+树、叶子结点保存data地址;
innodb:聚集索引、B+树、聚集索引中叶子结点保存完整data,innodb非聚集索引需要两遍索引,innoDB要求表必须有主键;
innodb为什么要用自增id作为主键:

自增主键:顺序添加,页写满开辟新的页;
非自增主键(学号等):主键值随机,有碎片、不够紧凑的索引结构;
分库与分表设计、分片:

水平分表;
垂直分表:不常用的加入另一张表、大文本字段单独拆分到另一张表、不经常修改的字段放入另一张表;
聚集索引与非聚集索引:

聚集索引:聚集索引查找完整数据;
非聚集索引:查找对应的主键值,然后根据主键值查找聚集索引,查找到完整数据
事务四大特性(ACID):

原子性:一个事务中的所有操作,要么全部完成,要么全部不完成,在执行过程中发生错误,会发生回滚(rollback);
一致性:事务开始前和结束后,数据库的完整性没有被破坏;
隔离性:多个事务执行,防止多个事务之间由于交叉执行而导致数据不一致。读未提交、读提交、可重复度、串行化。
持久性:事务提交后,对数据库的修改是永久的。
事务的并发?事务隔离级别,每个级别会引发什么问题,MySQL默认是哪个级别?

脏读:一个事务处理过程中读到了另一个事务未提交的数据;
不可重复读:一个事务多次读取一个数据,获得不同的数据结果;
幻读:一个事务读的过程中,另一个事务删除或者增加一条数据,影响这条事务的读的结果。
Mysql级别:可重复读。
事务隔离级别:

读未提交:读取未提交的数据,脏读;
不可重复读:事务A多次读取同一数据,事务B在该过程中对数据进行修改并提交,导致A多次读取数据不一致;
可重复读:同一事务里,多次读操作结果一致,但是存在幻读;
串行化:事务并发,一个个按顺序执行。
MySQL常见的存储引擎InnoDB、MyISAM的区别?

MyISAM:事务×,锁级别:表级锁,存储表总行数,非聚集索引;
(适用于插入不频繁,查询频繁)
InnoDB:事务✓,锁级别:行级锁和外键约束,不存储表总行数,聚集索引;
(可靠性要求比较高,或要求事务,表更新和查询都频繁)
数据库三范式,根据某个场景设计数据表?优缺点


[*]所有字段值都是不可分解的原子值。
[*]在一个数据库表中,一个表中只能保存一种数据,不可以把多种数据保存在同一张数据库表中。
[*]数据表中的每一列数据都和主键直接相关,而不能间接相关。
第一范式(确保每列保持原子性):

表中字段值不可再分,提高数据库性能;
第二范式(确保表中的每列都和主键相关):

第二范式需要确保数据库表中的每一列都和主键相关,而不能只与主键的某一部分相关(主要针对联合主键而言)。比如要设计一个订单信息表,因为订单中可能会有多种商品,所以要将订单编号和商品编号作为数据库表的联合主键。
第三范式(确保每列都和主键列直接相关,而不是间接相关):

比如在设计一个订单数据表的时候,可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息(比如姓名、所属公司等)的字段。
优点:可以尽量得减少数据冗余 缺点:对于查询需要多个表进行关联,更难进行索引优化 反范式化: 优点:可以减少表得关联 缺点:数据冗余以及数据异常
Explain关键字:

table:当前语句访问的表;
id:id相同,顺序执行,id越大,执行优先度越高;
select_type:小查询语句扮演的角色,比如SIMPLE、PRIMARY、UNION等。
☆type:表示MySQL在表中找到所需行的方式,又称”访问类型“。
常见类型:NULL,system,const,eq_ref,ref,range,index,ALL。
(性能由好到差)
ALL:遍历全表;
EXPLAIN SELECT * FROM s1;index:只遍历索引树;
EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';range:检索给定范围的行;
EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c');ref:表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值;
eq_ref:类似ref,区别:唯一索引;
const、system:查询优化时,转换为常量;system:只查询一行;
# const
EXPLAIN SELECT * FROM s1 WHERE id = 10005;
# system
INSERT INTO t VALUES(1);NULL:不需要访问表。
☆rows:预估的需要读取的记录条数,值越小越好。
☆key_len :
实际使用到的索引长度 (即:字节数)
帮你检查是否充分的利用了索引,值越大越好,主要针对于联合索引,有一定的参考意义。
MVCC多版本并发控制(Multiversion Concurrency Control):

InnoDB中实现MVCC机制;
快照读与当前读:
快照读:不加锁的简单的 SELECT 都属于快照读;
当前读:读取的记录进行加锁;
MVCC:包括:隐藏字段、Undo Log和Read View
隐藏字段:当前事务id,undo指针(指向历史版本的当前记录);
Undo Log:undo日志,通过undo指针串联历史版本;
ReadView:在查询时创建,
包含:
创建这个Read View的事务ID;
当前活跃的事务id列表;
活跃事务最小事务ID;
系统事务最大ID(并不一定是活跃的);
Read View规则:判断当前查询事务与Read View中最小事务的关系,
如果小于最小活跃ID,那么说明当前读取的行已提交;
如果大于最大ID,说明当前的行是由一个活跃的事务还未提交,正在处理,需要按照Undo Log往下找,找到不在活跃事务id列表中的最新的提交的数据。
简而言之:每一行存储历史信息,查找时获取当前活跃id,找到历史信息中最新的,并且不在活跃id的事务。
处理读已提交:
每次select时创建一个新的Read View;
处理可重复读:
每次同样的select只在最初的select创建一个Read View;
索引优化:

优化的角度:

[*]索引失效、没有充分利用到索引——建立索引
[*]关联查询太多JOIN(设计缺陷或不得已的需求)——SQL优化
[*]服务器调优及各个参数设置(缓冲、线程数等)——调整my.cnf
[*]数据过多——分库分表
索引失效情况:

最左前缀:MySQL可以为多个字段创建索引,一个索引可以包含16个字段。对于多列索引,过滤条件要使用索引必须按照索引建立时的顺序,依次满足,一旦跳过某个字段,索引后面的字段都无法被使用。如果查询条件中没有用这些字段中第一个字段时,多列(或联合)索引不会被使用。
递增主键:减少页分裂,不用随机字段作为主键。
计算、函数、类型转换(自动或手动)导致索引失效:
函数失效:
# 使用like
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';
# 使用函数
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';
# 创建索引之后
CREATE INDEX idx_name ON student(NAME);函数将导致索引失效;
计算失效:
# 创建索引
CREATE INDEX idx_sno ON student(stuno);
# 查询时使用计算
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;导致索引失效;
类型转换失效:
# 未使用到索引
# name的类型为字符串,下面发生了类型转换,导致失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123;

# 使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name='123';范围条件右边的列索引失效:
SELECT SQL_NO_CACHE * FROM student
WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ;idx_age_classId_name索引将会失效,因为ID>20作为范围条件,导致右侧的name失效。
除非如下修改,将ID>20放到最后:
create index idx_age_name_classId on student(age,name,classId);
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name = 'abc' AND student.classId>20;不等于(!= 或者)索引失效;
is null可以使用索引,is not null无法使用索引;
like以通配符%开头索引失效;
OR 前后存在非索引的列,索引失效:
OR前后的两个条件中的列都是索引时,查询中才使用索引。
查询优化:

① 尽可能的使用联合索引而不是索引的组合;
②创建索引尽量让辅助索引进行索引覆盖 而不是回表;
③在可以使用主键id的表中,尽量使用自增主键id,这样可以避免页分裂;
④查询的时候尽量不要使用select * ,这样可以避免大量的回表;
⑤尽量少使用子查询,能使用外连接就使用外连接,这样可以避免产生笛卡尔集;
⑥能使用短索引就使用短索引,这样可以在非叶子节点存储更多的索引列降低树的层高,并且减少空间的开销;

来源:https://www.cnblogs.com/maxzhangxiaotao/p/17222892.html
免责声明:由于采集信息均来自互联网,如果侵犯了您的权益,请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容,谢谢合作!
页: [1]
查看完整版本: mysql总结