mysql笔记

最新推荐文章于 2024-10-10 10:58:41 发布

weixin_34311757

最新推荐文章于 2024-10-10 10:58:41 发布

阅读量76

点赞数

文章标签：数据库

原文链接：https://juejin.im/post/5c599a4a518825627c38a0ef

版权

客户端访问： ---> serve层（连接器+分析器+优化器+执行器） + 搜索引擎

WAL技术 write-Ahead Logging

redo log（重做日志）和 binlog（归档日志）

innoDB的redo log的大小是固定的。可设置。

日志区别：

1、redo log是innodb引擎持有的；binlog是mysql的sever层实现的，所有引擎都可以使用。

2、redo log是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的是这个语句的原始逻辑，如“字段a+1”

3、redo log是循环写的，空间会用完；binlog可以追加写--新建不覆盖。

//数据库 “两阶段提交”（属于事务）

1先在内存找

2执行器拿到引擎给的数据，执行操作，得到一行新数据，再调用引擎写入这行新数据。

3引擎将新数据更新到内存中，同时将这个更新操作记录到redo log里面，此时redo log处于prepare状态。然后告诉执行器执行完成了，随时可以提交事务。

4执行器生成这个操作的binlog，并把binlog写入磁盘。

5执行器调用引擎的提交事务接口，引擎把刚写入的redo log改成提交commit状态，更新完成。

eg：当你需要扩容时，也就是需要多建数据库，来增加系统的读能力的时候，现在常见的做法也是用全量备份 + 应用binlog 来实现的。

redo log用于保证crash-safe能力。 innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘。

sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。

事务隔离级别。 mysql默认是 READ_COMMITTED

show variables like 'transaction_isolation'

读未提交：别人改数据的事务尚未提交，我在我的事务中也能读到。

读已提交：别人改数据的事务已经提交，我在我的事务中才能读到。

可重复读：别人改数据的事务已经提交，我在我已开启的事务中也不会去再读。

串行：我的事务尚未提交，别人就别想改数据。

这4种隔离级别，并行性能依次降低，安全性依次提高

事务的启动方式

set autocommit=0，这个命令会将这个线程的自动提交关掉，之后的操作全在长连接里面。

所以，建议：

使用set autocommit=1, 通过显式语句的方式来启动事务。用begin显示开启，执行commit命令提交。

如果执行commit work and chain，则是提交事务并自动启动下一个事务。

---搜索引擎---：负责存储相关的具体事宜

MYISAM 稀疏索引 ---> 数据跟索引是分开的。

eg：shop_info_small.frm //创建表文件信息

shop_info_small.MYD //表数据

shop_info_small.MYI

INNODB 密集索引

eg: person_info_large.frm //创建表文件信息

person_info_large.ibd //数据信息和索引

调优sql

一、使用explain工具，进行分析sql，性能调优

explain select name from person_info_large order by name desc;

//显示出在表person_info_large 执行查询语句时，性能的详细信息

//主要靠：type和extra的信息

二、增加索引,尽量让sql走索引

alter table person_info_large add index idx_name(name);

//执行一条插入语句，往表person_info_large中，以name为索引，插入索引idx_name；

explain select count(id) from person_info_large force index(primary)

-------索引问题

联合索引。eg: area和title

explain select * from person_info_large where area="EJZszkSKGtQuYoiLRXsQ" and title="ABdfGFSjorHRPwnNrJsp"

1、索引引出的最左前缀匹配原则，mysql会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配。如a=3 and b=4 and c>5 and d=6 如果建立（a、b、c、d）顺序的索引，d是用不到索引的，如果建立（a、b、d、c）的索引，都可以用到，abd的顺序可以任意调整。

2、=和in可以乱序，比如a=1 and b=2 and c=3建立（a、b、c）索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

MyISAM默认用的是表级锁，不支持行级锁。

InnoDB默认用的是行级锁，也支持表级锁。

排它锁语句：

select * from person_info_myisam where id between 1 and 2000000 for update;

共享锁语句：

select * from person_info_large where id=3 lock in share mode;

commit;

innodb在sql没用到索引的时候，用的是表级锁

------数据文件分离，索引保存的是数据文件的指针，主键索引和辅助索引是独立的。因此，在少量增、删、改的性能中，MYISAM是优于INNODB；

myisam适用场景

1、频繁执行全表count语句