数据库
阅读 3
2022-12-27
1、查询慢sql命令
查看慢查询存放日志文件，命令： show variables like 'slow_query_log_file';
2、explain 在查询语句前加explain 会显示执行计划分析结果
type:表示MySQL在表中找到所需行的方式，或者叫访问类型。常见访问类型如下，从上到下，性能由差到最好：
| Type | 意义 |
|---|---|
| ALL | 全表扫描 |
| index | 索引全扫描 |
| range | 索引范围扫描 |
| ref | 非唯一索引扫描 |
| eq_ref | 唯一索引扫描 |
| const,system | 单表最多有一个匹配行 |
| NULL | 不用扫描表或索引 |
key 有没有命中索引
Key_len 使用索引的字节数
Rows 检索了多少行
Key_len 使用索引的字节数
Rows 检索了多少行
3、数据库优化
| 1.查询最小范围属性 |
| 2.用join on 代替子查询(因为子查询mysql会在内存里创建临时表) |
| 3.保证事务的最小颗粒度，代码中在事务开启前进行数据的转换和查询相关功能尤其是rpc调用。事务中的代码最好只有inset update delete代码。 |
| 4. 使用索引 |
| 不命中索引的情况 |
| 1) 使用or如果两个字段不是都是索引的情况 就不会命中 |
| 2) Like 以%开头 |
| 3) 索引列使用函数 |
| 4) 两个列比较 |
| 5) Not in 和not exist |
4、死锁原因
1.数据库在获取锁后很久没释放一直卡顿在其他场景。
2.两个线程执行两张表顺序相反并发时导致资源互相竞争。
3.唯一索引出现死锁
4.间隙锁出现死锁
2.两个线程执行两张表顺序相反并发时导致资源互相竞争。
3.唯一索引出现死锁
4.间隙锁出现死锁
5、数据库唯一索引有可能会出现死锁的情况。
唯一索引为什么会出现死锁？
如果两个线程并发去insert并修改一张表 ，例如：
session1 执行update tb_user set level = 1 where username = "A"; 进入等待状态
其中字段username是唯一索引 都插入A 会添加共享锁。然后都去update 会添加排它锁，这样会添加失败。
因为事务对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁
共享锁（S锁）：共享 (S) 用于不更改或不更新数据的操作（只读操作），如 SELECT 语句。
因为事务对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁
共享锁（S锁）：共享 (S) 用于不更改或不更新数据的操作（只读操作），如 SELECT 语句。
如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。
排他锁（X锁）：用于数据修改操作，例如 INSERT、UPDATE 或 DELETE。确保不会同时同一资源进行多重更新。
如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。
如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。
共享锁：对某一资源加共享锁，自身可以读该资源，其他人也可以读该资源（也可以再继续加共享锁，即 共享锁可多个共存），但无法修改。要想修改就必须等所有共享锁都释放完之后。语法为：
select * from table lock in share mode
排他锁：对某一资源加排他锁，自身可以进行增删改查，其他人无法进行任何操作。语法为：
select * from table for update
select * from table lock in share mode
排他锁：对某一资源加排他锁，自身可以进行增删改查，其他人无法进行任何操作。语法为：
select * from table for update
6、Mysql 事务隔离级别
1.read uncommitted
可以看到未提交的数据（脏读），举个例子：别人说的话你都相信了，但是可能他只是说说，并不实际做。
2.read committed
读取提交的数据。但是，可能多次读取的数据结果不一致（不可重复读，幻读）。用读写的观点就是：读取的行数据，可以写。
3.repeatable read(MySQL默认隔离级别)
可以重复读取，但有幻读。读写观点：读取的数据行不可写，但是可以往表中新增数据。在MySQL中，其他事务新增的数据，看不到，不会产生幻读。采用多版本并发控制（MVCC）机制解决幻读问题。
4.serializable
可读，不可写。像java中的锁，写数据必须等待另一个事务结束。
可以看到未提交的数据（脏读），举个例子：别人说的话你都相信了，但是可能他只是说说，并不实际做。
2.read committed
读取提交的数据。但是，可能多次读取的数据结果不一致（不可重复读，幻读）。用读写的观点就是：读取的行数据，可以写。
3.repeatable read(MySQL默认隔离级别)
可以重复读取，但有幻读。读写观点：读取的数据行不可写，但是可以往表中新增数据。在MySQL中，其他事务新增的数据，看不到，不会产生幻读。采用多版本并发控制（MVCC）机制解决幻读问题。
4.serializable
可读，不可写。像java中的锁，写数据必须等待另一个事务结束。
7、事务四种特性
1.原子性：事务包含的所有操作要么全部成功，要么全部失败回滚;成功必须要完全应用到数据库，失败则不能对数据库产生影响；
2.一致性：事务执行前和执行后必须处于一致性状态，
例：用户A和用户B的前加起来一共是1000; 无论AB用户之间是如何相互转换的，事务结束后两个用户的钱加起来还是1000,这就是事务的一致性。
3.隔离性：当多个用户并发访问数据库时，数据库为每一个用户开启的事务，不被其他事务的操作所干扰，多个并发事务之间要相互隔离；
4.持久性：一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便在数据库系统遇到故障的情况下也不会丢失事物的操作。
2.一致性：事务执行前和执行后必须处于一致性状态，
例：用户A和用户B的前加起来一共是1000; 无论AB用户之间是如何相互转换的，事务结束后两个用户的钱加起来还是1000,这就是事务的一致性。
3.隔离性：当多个用户并发访问数据库时，数据库为每一个用户开启的事务，不被其他事务的操作所干扰，多个并发事务之间要相互隔离；
4.持久性：一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便在数据库系统遇到故障的情况下也不会丢失事物的操作。
8、MYSQL怎么保障 ACID
原子性A 通过undo log名为回滚日志
持久性D Innodb的redo log
隔离行I 利用MVCC和锁
一致性C 是目的 原子性持久性隔离性是手段 通过原子性、持久性、隔离性来保障。
持久性D Innodb的redo log
隔离行I 利用MVCC和锁
一致性C 是目的 原子性持久性隔离性是手段 通过原子性、持久性、隔离性来保障。
9、常用的分库分表的中间件及不同的分库分表中间件都有什么优点和缺点？
分库分表常见的中间件有：cobar、TDDL、atlas、sharding-jdbc和mycat等。
9.1 cobar
cobar是阿里的b2b团队开发和开源的，属于proxy层方案，介于应用服务器和数据库服务器之间。应用程序通过JDBC驱动访问cobar集群，cobar根据SQL和分库规则对SQL做分解，然后分发到MySQL集群不同的数据库实例上执行。cobar并不支持读写分离、存储过程、跨库join和分页等操作。早些年还可以用，但是最近几年都没更新了，基本没啥人用，算是淘汰了。
9.2 TDDL
TDDL是淘宝团队开发的，属于client层方案。支持基本的crud语法和读写分离，但是并不支持join、多表查询等语法。目前使用的也不多，因为使用还需要依赖淘宝的diamond配置管理系统。
9.3 atlas
atlas是360开源的，属于proxy层方案。以前是有一些公司再用的，但是社区最新的维护都在5年前了，现在用的公司也基本没有了。
9.4 sharding-jdbc
sharding-jdbc是当当开源的，属于client层方案。这个中间件对SQL语法的支持比较多，没有太多限制。2.0版本也开始支持分库分表、读写分离、分布式id生成、柔性事务（最大努力送达型事务、TCC事务）。目前社区也还一直在开发和维护，算是比较活跃，是一个现在也可以选择的方案。
9.5 mycat
mycat是基于cobar改造的，属于proxy层方案。其支持的功能十分完善，是目前非常火的一个数据库中间件。社区很活跃，不断在更新。相比于sharding-jdbc来说，年轻一些，经历的锤炼也少一些。
综上所述，现在建议考量使用的就是sharding-jdbc和mycat。
sharding-jdbc这种client层的优点在于不用部署，因此运维成本也就比较低。同时因为不需要代理层的二次转发请求，性能很高。但是如果遇到升级的话，需要各个系统都重新升级版本再发布，因为各个系统都需要耦合sharding-jdbc的依赖。
mycat这种proxy方案的缺点在于需要部署，因此运维成本也就比较高。但是优点在于其对于各个项目是透明（解耦）的，如果要升级的话只需要在中间件处理就行了。
sharding-jdbc这种client层的优点在于不用部署，因此运维成本也就比较低。同时因为不需要代理层的二次转发请求，性能很高。但是如果遇到升级的话，需要各个系统都重新升级版本再发布，因为各个系统都需要耦合sharding-jdbc的依赖。
mycat这种proxy方案的缺点在于需要部署，因此运维成本也就比较高。但是优点在于其对于各个项目是透明（解耦）的，如果要升级的话只需要在中间件处理就行了。
10、索引数据结构
索引采用B tree的数据结构，每个节点通过二分法查找，然后通过指针找下一节点。
B-tree和B tree区别
B-tree每个节点都会存储节点数据，而B tree只有在叶子节点存储。
B tree的好处是什么，由于非叶子节点没有存储数据，这样的话每个存储块会存储更多的节点，检索更快。
B-tree每个节点都会存储节点数据，而B tree只有在叶子节点存储。
B tree的好处是什么，由于非叶子节点没有存储数据，这样的话每个存储块会存储更多的节点，检索更快。
11、Mysql中myisam和innodb的区别
11.1 存储结构不同
MyISAM：每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。 .frm文件存储表定义。数据文件的扩展名为.MYD(MYD)。索引文件的扩展名是.MYI(MYIndex)。
InnoDB:所在的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。
InnoDB:所在的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。
11.2 存储空间 不同
MyISAM:可被压缩，存储空间较小。支持三种不同的存储格式：静态表（默认，但是注意数据末尾不能有空格，会被去掉）、动态表、压缩表。
InnoDB:需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。
InnoDB:需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。
11.3 事物支持
MyISAM:强调的是性能，每次查询具有原子性，其执行速度比Innodb类型更快，但是不提供事物支持。
InnoDB:提供事务支持，外部键等高级数据库功能。具有事务（commit）、回滚（rollback）和崩溃修复能力（crach recovery capabilities）的事务安全（transaction-safe ACID compliant）型表。
InnoDB:提供事务支持，外部键等高级数据库功能。具有事务（commit）、回滚（rollback）和崩溃修复能力（crach recovery capabilities）的事务安全（transaction-safe ACID compliant）型表。
11.4 CURD操作
MyISAM: 如果执行大量的select, MyISAM是更好的选择。（因为没有支持行级锁），在增删的时候需要锁定整个表格，效率会低一些。相关的是innoDB支持行级锁，删除插入的时候只需要锁定该行就行，效率较高。
InnoDB:如果你的数据执行大量的insert或update，出于性能方面的考虑，应该使用InnoDB表。Delete从性能上Innodb更优，但delete from table时，InnoDB不会重新建立表，而是一行一行的删除，在innodb上如果要清空保存有大量数据的表，最好使用truncate table这个命令。
InnoDB:如果你的数据执行大量的insert或update，出于性能方面的考虑，应该使用InnoDB表。Delete从性能上Innodb更优，但delete from table时，InnoDB不会重新建立表，而是一行一行的删除，在innodb上如果要清空保存有大量数据的表，最好使用truncate table这个命令。
11.5 外键
MyISAM: 不支持。
InoDB:支持
InoDB:支持
12、innodb_flush_log_at_trx_commit 和 sync_binlog
innodb_flush_log_at_trx_commit
提交事务的时候将 redo 日志写入磁盘中，所谓的 redo 日志，就是记录下来你对数据做了什么修改，比如对 “id=10 这行记录修改了 name 字段的值为 xxx”，这就是一个日志
当设置为0，该模式速度最快，但不太安全，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。
当设置为1，该模式是最安全的，但也是最慢的一种方式。在mysqld 服务崩溃或者服务器主机crash的情况下，binary log 只有可能丢失最多一个语句或者一个事务。。
当设置为2，该模式速度较快，也比0安全，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。
当设置为0，该模式速度最快，但不太安全，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。
当设置为1，该模式是最安全的，但也是最慢的一种方式。在mysqld 服务崩溃或者服务器主机crash的情况下，binary log 只有可能丢失最多一个语句或者一个事务。。
当设置为2，该模式速度较快，也比0安全，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。
sync_binlog
该参数控制着二进制日志写入磁盘的过程。
该参数的有效值为0 、1、N：
0：默认值。事务提交后，将二进制日志从缓冲写入磁盘，但是不进行刷新操作（fsync()），此时只是写入了操作系统缓冲，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘,若操作系统宕机则会丢失部分二进制日志。
1：事务提交后，将二进制文件写入磁盘并立即执行刷新操作，相当于是同步写入磁盘，不经过操作系统的缓存。
N：每写N次操作系统缓冲就执行一次刷新操作。
将这个参数设为1以上的数值会提高数据库的性能，但同时会伴随数据丢失的风险。
二进制日志文件涉及到数据的恢复，以及想在主从之间获得最大的一致性，那么应该将该参数设置为1，但同时也会造成一定的性能损耗。
该参数的有效值为0 、1、N：
0：默认值。事务提交后，将二进制日志从缓冲写入磁盘，但是不进行刷新操作（fsync()），此时只是写入了操作系统缓冲，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘,若操作系统宕机则会丢失部分二进制日志。
1：事务提交后，将二进制文件写入磁盘并立即执行刷新操作，相当于是同步写入磁盘，不经过操作系统的缓存。
N：每写N次操作系统缓冲就执行一次刷新操作。
将这个参数设为1以上的数值会提高数据库的性能，但同时会伴随数据丢失的风险。
二进制日志文件涉及到数据的恢复，以及想在主从之间获得最大的一致性，那么应该将该参数设置为1，但同时也会造成一定的性能损耗。
评论 (0)