一、什么是存储引擎
存储引擎是MySQL数据库中的一个【组件】,【负责执行实际的数据I/O操作】,工作在文件系统之上,数据库的数据会先传到存储引擎,在按照存储引擎的格式,保存到文件系统。
常用的存储引擎:InnoDB 、MyISAM
MySQL 整个查询执行过程,即MySQL的工作原理?
1、MySQL服务器接收到来自客户端的数据请求;2、数据库会先查询缓存记录,如果命中缓存,直接返回存储在缓存中的查询结果,如果没有命中,则进行下一步操作;
3、数据库会先由解析器来解析SQL语句的,然后进行预处理,接着用优化器生成最优的执行计划;
4、数据库根据最优的执行计划,调用存储引擎的API接口来执行查询结果,并将结果返回给客户端,同时缓存查询结果;
二、MyISAM 与 InnoDB 的区别
类型 | MyISAM | InnoDB |
事务 | 不支持事务 | 支持事务 |
外键约束 | 不支持外键约束 | 支持外键约束 |
锁定方式 | 只支持表级锁定 | 支持行级锁定,全表扫描时会表级锁定 |
MVCC | 不支持MVCC | 支持MVCC(多版本并发控制) |
读写性能 | 只支持单独的查询与插入,读写阻塞 | 读写和事务并发能力较好 |
全文索引 | 支持全文索引 | 支持全文索引(5.5版本以后支持) |
硬件 | 硬件资源占用较小 | 缓存能力较好,可以减少磁盘IO的压力 |
存储文件 | 数据文件和索引文件分开存储,存储为三个文件(.frm的表结构文件,.MYD的数据文件,.MYI的索引文件) | 数据文件也是索引文件,存储成两个文件(.frm的表结构文件,.ibd的数据和索引文件) |
使用场景 | 使用不需要事务支持,单独的查询和插入的业务场景 | 使用于需要事务支持,一致性要求比较高,数据会频繁更新,读写并发高的业务场景; |
三、如何查看当前表的存储引擎
1.查看当前的存储引擎
show create table 表名;
show table status [from 库名] where name='表名'\G
2.查看数据库支持哪些存储引擎
show engines;
四、如何设置存储引擎?
1、当前表已经存在,想要修改其存储引擎
alter table 表名 engine=innodb/myisam;
2、想要修改表的默认储存引擎
vim /etc/my.cnf
[mysqld]
default-storage-engine=innodb/myisam
systemctl restart mysqld
mysql -uroot -p密码
create table 表名(字段 数据类型,...);
show create table 表名;
3、想要在创建表的时候,直接指定存储引擎
create table 表名 (字段 数据类型,...) engine=innodb/myisam;
五、什么是死锁?以及怎么解决/预防?
死锁是指两个或多个事务在同一个资源上相互占用,并请求对方的锁定资源,从而导致恶性循环的现象,业务阻塞。
- 设置事务超时等待时间,innodb_lock_wait_timeout
- 开启死锁检测,innodb_deadlock_detect
- 使用更合理的业务逻辑,尽量按照顺序去处理表的操作,避免同时锁定两个资源;
- 保持事务剪短,减少对资源的占用时间和占用范围;
- 为表添加合理的索引,减少表锁发生的概率;
首先如果是由索引的情况下修改或删除数据使用where限定条件字段为索引字段,只会进行行级锁定。如果是where指定的条件的字段并没有索引,那么就会进行表级锁定。
- 如果业务允许的情况下,减低隔离级别,比如采用RC提交读隔离级别;可以避免掉很多因为间隙锁造成的死锁。
尽量避免脏读所以不使用RU,如果业务允许的恶化,可以降低隔离级别为RC,比如MySQL的默认隔离级别为RR,可以将其改为RC,支持较少的锁定
- 建议开发人员尽量采用乐观锁机制;
七、InnoDB如何避免全表扫描?
首先要知道全表扫描时非常耗时的,占用cpu资源,所以需要避免全表扫描
怎么看是否会不会全表扫描?
不是创建了索引就一定会在查询的时候用到索引的
使用explain加上select查询语句进行分析,如果是type字段的值为NULL那就说明没有用索引,会全表扫描。
如何避免全表扫描?
1、需要避免where的条件有null的判断;
2、避免where的条件是!=和<> 这都是在判断是否空;
3、避免where后面的条件中的字段存在运算,比如
select * from 表名 where age/2=15;
这种就是字段存在运算
需要修改为
select * from 表名 where age=15 * 2;
4、避免where后面的条件中 字段使用了函数,这一点跟第3点很像
5、避免在使用like模糊查询的时候,引号内的关键字为%开头