mysql 原理_mysql底层原理
发布时间：2022-09-30 12:53:14 所属栏目：MySql教程 来源：
导读： 一：MySql架构
1.一条sql语句如何执行的：mysql5.7查询缓存默认关闭，mysql8缓存已被移除。
存储引擎对比：
MySIAM：表级锁定，不支持事务，已读为主
InnoDB：支持事务，支持外
1.一条sql语句如何执行的：mysql5.7查询缓存默认关闭，mysql8缓存已被移除。
存储引擎对比：
MySIAM：表级锁定，不支持事务，已读为主
InnoDB：支持事务，支持外
|
一：MySql架构 1.一条sql语句如何执行的：mysql5.7查询缓存默认关闭，mysql8缓存已被移除。 存储引擎对比： MySIAM：表级锁定，不支持事务，已读为主 InnoDB：支持事务，支持外键，支持行级别和表级别的锁定，B+索引，效率高 Memory：内存存储。 Archive：用于存储和检索大量很少引用的历史、存档、安全审计信息，不支持事务。 mysql架构 局部性原理：读取磁盘的数据，它附近的数据也会被读取到内存，操作形同一般4k，mysql中读取每页的数据16k，Innodb设置了一个内存叫Buffer Pool，默认128M，读取数据时先去buffer中读取，写数据也是先写到buffer中，为了不产生脏页，innodb有专门的后台线程把buffer中的数据写到磁盘中，这个动作叫刷脏。为了防止数据库宕机造成数据不一致，Innodb会把修改操作写到Redo log(重做日志)中，事务的acid的持久化就是这个日志实现的，(为啥不直接写到磁盘，因为写磁盘的数据不是连续的，io效率低，日志是顺序的，快速。)redo log大小是固定的，一旦写满，就会触发buffer到磁盘的同步。和redo log 还有一个undo log 是重做日志，记录的修改之前的数据，用于回滚。 6.redo log 和undo log是innodb独有的，mysql的server层也有一个日志叫Binlog可以被所有存储引擎使用，binlog以事件的形式记录所有ddl和dml语句，比如给id=1的count字段加1，binlog记录的是操作不是数据值，属于逻辑日志。 Binlog用于1.主从复制2数据恢复。 7.mysql主从复制原理:从服务器读取主服务器的binlog，在执行一次。 7．一条更新语句的执行(省略了undo log) Redo两阶段是为了防止写入binlog失败。 二：MySql索引原理 为何需要索引 数据是以文件存储在磁盘，查询的时候不能挨个查找数据，有了索引直接可以找到该数据所在的位置 InnoDB索引类型：普通索引，唯一索引，主键索引，聚集索引 聚集索引：索引键值的逻辑顺序和表数据行的物理存储顺序一致，主键索引就是一种聚集索引。 3.InnoDB索引采用B+树 4.聚集索引的叶子节点存储的数据行。二级索引(非聚集索引)叶子节点存储的是索引字段和所在行的主键字段，当查询一个name的二级索引时，先找到二级索引中叶子节点name的主键id值，然后在主键索引中查找id的叶子结点，叶子结点存储的就是数据行的数据。 》如果定义了主键，主键就作为聚集索引， 》如果没有定义主键索引的话，InnoDB会选择第一个不包含NULL的唯一索引作为主键索引， 》如果这个唯一也没有，InnoDB会选择一个6字节的ROWID字段作为隐藏的聚集索引，会随着记录的写入而作为主键递增。 5.联合索引：多个字段作为一个索引，最左匹配， 6.覆盖索引：如果select的数据在二级索引的节点就能找到就不会再去主键索引中查找。 7.回表：查询二级索引，再根据二级索引的数据到主键索引去查询数据叫回表 8.在什么字段上建立索引 Wher 9.什么时候索引会失效 》索引列上使用函数(replace substr concat sum count avg)表达式、计算等 》字符串不加引号 where name=123 》like条件中前面带% 》NOT LIKE 查询 》！= not in在某些条件下可以使用索引 三：MySql事务和锁机制 事务四大特性;ACID 原子性：事务要么成功，要么失败 一致性：数据前后保持一致 隔离性：多个事务之间是相互隔离，互不影响的 持久性：只要事务操作成功，不会因为数据库宕机等外界因素恢复到原来的状态(redo log实现) 事务并发问题 脏读：事务A读到事务B还未提交的数据 不可重复读：事务A读到事务B已提交的数据，前后数据不一致。 幻读：事务A读取的数据是事务B插入了新行。 事务隔离级别： 未提交读：没解决任何问题 已提交读：解决了脏读 可重复读：解决了脏读和不可重复读 串行化：三个问题都解决了 InnoDB堆事务隔离级别的支持 InnoDB的两大实现方法 5.1 LBCC：读取数据的时候锁定数据，锁的并发控制 5.2 MVCC:修改数据的时候建立一个快照，多版本的并发控制 Mvcc实现的原理：我可以查到在我这个事务修改的数据或者我这个事务开始之前已经存在并提交的数据，在我这个事务之后新增修改的数据我是查不到的。 Innodb给每行记录增加三个字段: 创建版本号：插入或更新行的最后一个事务id 删除版本号：数据被删除或标志为旧数据的事务id Rowed：主键id 锁的分类 》共享锁：slect 。。lock in share mode 》排他锁：一个事务获取了一行数据的排它锁，其他事务就不能再获取这一行数据的共享锁或者排他锁，但不表示不能读数据，比如select * from a where name=‘2’ 这个select是不会加任何锁的，所以不会阻塞。Update delete 会自动加排它锁 》意向锁：给一行数据加上共享锁之前，数据库会自动给这张表加上意向共享锁，同样的，给一行数据加上排他锁之前，数据库会自动给这张表加上意向排他锁，这样其他事务就不用每行遍历查看数据是否有共享或者排它锁。提高效率。 行锁的原理：是给索引加锁。 》如果有二级索引的话，会通过二级索引查找聚集索引。锁住索引对应的记录。表如果没有索引，我们知道会创建一个隐藏的rowid作为聚集索引，查询的时候因为没创建索引，所以会全表查询mysql原理，这样就锁住了所有的隐藏rowid索引，所以外界看来就是整张表锁住了。 锁的算法。比如 id建立唯一索引 有 1 4 7 10 数据 》记录锁 ：当使用唯一索引(唯一索引和主键索引)使用等值查询，精确匹配到一条记录的时候，使用的就是记录锁，比如 where id=1 》间隙锁:查询的记录不存在，无论等值查询还是范围查询，使用的都是间隙锁，比如：where id>4 and id 》临键锁：当使用范围查询，不仅命中了记录，还包含了间隙锁，这使用的就是临键锁，相当于记录锁加上间隙锁，比如where id>5and id innodb各隔离级别的实现方式 四：MySql优化 服务器配置的优化 Mysql配置的优化，连接数的设置等。 架构的优化：使用redis缓存，集群，主从复制，分库分表 Sql的优化：慢查询日志分析，分析mysql运行状态，explain分析sql，看看查询顺序、查询type类型(system>const>eq_ref>ref>range>index>all 至少达到range范围索引)，可能用到的索引，扫描行数，extra信息，优化sql语句，比如使用索引， 存储引擎的选择，常规的innodb，查询多的myisam，临时数据多的memory，表结构的构建，字段的类型选择，比如性别用tinyint，varchar代替char，不要使用外键，触发器，视图等，不要使用数据库存图片。 代码的优化，降级限流，消息队列等等，尽量减轻数据库压力。 （编辑：百客网 - 域百科网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐