mysql面试简洁版

优化

mysql的优化可以从,硬件,系统配置,表结构,sql语句几个方面优化

硬件

硬盘选择:使用高性能的固态硬盘代替机械硬盘,提高数据读写速度

增加内存:增加数据库服务器的内存,以提高缓存效果,减少磁盘IO操作

cpu升级:升级到更快的多核cpu,提高数据库处理能力

系统配置

调整缓冲池:数据库会根据服务器自身的配置将一部分数据留于内存中,调整数据库管理系统的缓冲池大小,确保数据可以常驻内存

减少磁盘IO:

• 增加redo缓冲区的大小
• 调整日志缓冲区的策略(0是不会立即将事务日志写入磁盘，而是将日志缓冲在内存中，然后按一定的策略异步刷新到磁盘;        1是立即提交内存且立即刷盘;        2是将事务日志写入操作系统的缓冲区，但并不立即将其刷新到磁盘，而是在稍后的时间点通过异步方式进行刷新)

调整日志:优化日志级别,减少不必要的日志记录,提高性能

定期维护:定期执行数据库的备份,索引重建和统计信息更新等维护任务

表结构

• 设置中间表:在空闲时间将工作中常用到的统计结果提前统计存储到中间表中
• 适当增加冗余:适当增加冗余能够减少多表的关联
• 切分大表:对于字段过多的表将其拆分为常用,不常用,文本字段单独表分开存储在几张吧表中,用主键关联
• 字段设计:尽量将字段设计为数值/枚举型,他们较文本类型查询更快,存储空间更少

语句优化

• explain查看有问题的sql执行计划,重点查看索引使用情况
• in包含的值不应过多
• select语句指明字段的名称
• 只需要一条数据时,使用limit 1
• where后条件,排序分组字段设置索引
• or两边的字段都要加索引
• 尽量用union all 代替 union
• 不使用order by rand()
• 尽量用(not) exists代替(not) in
• 避免使用%前缀模糊查询
• 避免在where条件后进行表达式操作(where id + 1 > 1这种)
• 联合索引遵守最左前缀
• 数据库没有按预期索引查找,使用force index强制走指定索引
• 尽量只用inner join,避免left join

---

redo log和undo log

两者出现的前提:mysql为了提升性能并不会将每次的修改实时同步到磁盘中,而是会先存入boffer pool(缓冲池),由配置的策略来让相应线程做缓冲池与磁盘间的同步,在操作数据命令发出后,先将最初数据记录到undo log,将更新后数据记录到redo log来保证持久与原子性

redo log和undo log都是用来恢复数据的,两种记录的内容都是偏移量+具体数据,而非操作,redo log保障事务的持久性,undo log保障事务的原子性

当事务已提交,数据从磁盘调到缓存,修改完成,但此时数据库宕机,就可以使用redo log来恢复已经提交事务修改的数据至预期

当事务已提交,事务内语句出错需要回滚,或主动要求回滚,可以使用undo log来将最初的数据还原

---

mvcc

同一份数据保留多版本,进而实现并发控制,查询时通过read view和版本链找到对应版本数据,在高并发场景下,mvcc比行级锁开销更小

---

脏读

当一个事务正在访问数据,并对数据进行了修改但未提交到数据库,同时间另一个事务也访问这个数据并使用了这个修改的数据就叫脏读

---

hash索引缺点

• 不能避免读取行:哈希索引底层存储的是哈希值和指针,而非字段值
• 不能排序:hash索引是靠哈希值实现的而非字段值
• 不支持部分索引列匹配:哈希索引是用索引列全部内容来计算哈希值,用a,b建立hash索引,就不能单独用a
• 只能等值查找
• hash冲突:不同索引列值计算后是相同哈希值,出现hash冲突,对于同hash值的只能逐行比对

---

myism和innodb

1. 事务支持：

   • InnoDB：支持事务，具有ACID（原子性、一致性、隔离性、持久性）特性
   • MyISAM：不支持事务，不具备ACID特性。

2. 并发性：

   • InnoDB：支持高并发，具有更好的并发控制机制，允许多个事务同时进行，提供了更好的数据一致性和隔离性。
   • MyISAM：并发性较差，不支持行级锁，因此在高并发环境下可能会导致性能问题。

3. 表级锁定和行级锁定：

   • InnoDB：支持行级锁定，可以避免大部分锁冲突，允许多个事务在同一表上同时操作不同的行。
   • MyISAM：只支持表级锁定，因此在多个事务尝试同时修改同一表时可能会出现锁冲突，影响性能。

4. 外键支持：

   • InnoDB：支持外键约束，可以保证数据完整性，并执行级联操作（如级联删除和级联更新）。
   • MyISAM：不支持外键约束。

5. 崩溃恢复：

   • InnoDB：具有崩溃恢复机制，可以在数据库崩溃后较好地恢复数据。
   • MyISAM：不太稳定，容易在崩溃时损坏数据。

6. 全文搜索：

   • InnoDB：不支持全文搜索，需要使用全文搜索引擎如Elasticsearch等来实现全文搜索功能。
   • MyISAM：支持全文搜索，具有内置的全文搜索功能。

7. 空间数据类型：

   • InnoDB：支持GIS（地理信息系统）数据类型，可以用于处理地理空间数据。
   • MyISAM：不支持GIS数据类型。

8. 性能：

   • InnoDB：在事务处理和并发性能方面通常优于MyISAM，特别适合写入密集型应用。
   • MyISAM：在读取密集型应用中性能可能更好，但在写入密集型或要求事务支持的应用中性能较差。