MySQL总结

MySQL总结

MySQL B+树

1.InnDB以页的方式进行存储,如果超过默认的16KB则生成下一页,也就是增加树的一个磁盘块,这个大小可以设置;

2.B树的叶子节点只有数据,而非叶子节点既有数据又有指向对应节点的指针;

3.B+树的叶子节点只有数据,而非叶子节点只有指针

为什么这样设计?

由于B树的非叶子节点还有数据,这就占用了每个磁盘块的内存,也就导致更多的非叶子节点,增加了树的高度,而B+树的非叶子节点只有指针就可以存储更多的指针,这个时候就减少了树的高度

索引

1.索引分为普通索引,唯一索引,主键索引

普通索引:没有限制,允许插入重复值和空值

唯一索引:索引必须是唯一的

主键索引:作为主键的索引

2.当你创建一个索引时,其实也就是创建了一个B+树,后续的查找也是通过B+树进行查找的,但是B+树的叶子节点只有id和此索引数据,后续查询就会出现回表查询的情况;

3.回表查询就是当查询的这个字段是索引字段时,而我们需要的不仅仅是该索引数据和Id时,最后还是会向主键索引再次查找一边;

怎么解决这个问题呢?

我们可以将这个索引字段和其他我们需要的字段做关联,B+树的数据中就会有我们需要的数据了,只需要查询一次就可以得到结果了;

4.覆盖索引:将我们需要的结果字段和索引字段做关联,这个时候通过索引查询的数据就只需要查询一遍了;

5.索引不是越多越好,毕竟索引占内存,而且在插入数据后B+树会变,写入数据会变慢.总结来说就是1.空间换时间 2.牺牲写操作,提示读操作

6.索引注意事项:1.避免更改索引数据 2.经常更新的表避免创建索引 3.几万级别下的表最好不要创建索引 4.避免在相同值多的列上创建索引

页、缓冲池和各种日志

1.InnoDB的数据页大小为16k

2.数据库中的缓存池会将访问的页数据放到缓存池中,以供下次读取,其中缓存池的大小可以设置,默认是128M

3.当我们修改了缓存页中的数据的时候,就会有脏页的情况,数据中专门的链串联所有的脏页"flush链",一秒刷新一次将脏页数据写到磁盘中

4.脏页刷新时机: 1.磁盘的redo log日志文件写满时 2.内存不足时 3.系统空闲时 4.MySQL正常关闭时

5.bin-log 主要用于数据备份 undo-log主要用于事务回滚 redo-log主要用于用户服务器宕机恢复

事务隔离级别

6.事务的隔离级别:读未提交,读已提交,可重复读,串行化

可重复读:当select 数据时没有提交事务再次select 数据不会改变结果,原理是在读取数据的时候会复制一份数据以作后续读取

串行化:当A读取此数据时,B对这行数据的任何操作都会被阻塞

数据库默认开启可重复读

select-for-update 和锁

7.select for uodate锁分为行锁和表锁,

行锁:其中操作的数据是带有索引的话会锁住该行,也就是行锁;

表锁:操作的数据没有索引,数据库会去检索全表,这个时候会执行表锁;

锁的根本是锁索引的B+树

慢查询和执行计划 typ 值

8.慢查询:当查询的时间超过一定的时间没有返回结果的时候,该SQL语句会被记录到slow_query_log日志中,不过慢查询日志是默认不开启的,需要启动时进行配置

9.当我们在执行的查询sql语句前加上explain,结果会显示执行的type值,通过type值可以判断sql语句的执行快慢,下面从快到慢

1.const

2.eq_ref

3.ref

4.fulltext

5.ref_or_null

6.index_merge

7.unique_subquery

8.index_subquery

9.range

10.index

11.all

SQL 语句优化

8.SQL语句优化策略

1.避免 select *

2.在where 中尽量使用等值判断

3.尽量避免全表扫描

4.用UNION来代替OR

5.like 语句避免前置百分号