mysql常用面试题集锦

1. 如何优化查询

  • 影响数据库查询的因素:服务器硬件,磁盘io,网卡流量,sql查询速度
  • 风险分析
    • QPS:每秒查询率,是一台服务器每秒能够相应的查询次数,是对一个特定的查询服务器在规定时间内所处理流量多少的衡量标准。
    • TPS: 也就是事务数/秒。它是软件测试结果的测量单位。客户机在发送请求时开始计时,收到服务器响应后结束计时,以此来计算使用的时间和完成的事务个数。

Tips:最好不要在主库上数据库备份,大型活动前取消这样的计划。
1、效率低下的 sql:超高的 QPSTPS
2、大量的并发:数据连接数被占满( max_connection默认 100,一般把连接数设置得大一些)。
并发量:同一时刻数据库服务器处理的请求数量
3、超高的 CPU使用率:CPU资源耗尽出现宕机。
4、磁盘 IO:磁盘 IO性能突然下降、大量消耗磁盘性能的计划任务。解决:更快磁盘设备、调整计划任 务、做好磁盘维护。

  • 网卡流量:如何避免无法连接数据库的情况
    1. 减少从服务器的数量(从服务器会从主服务器复制日志)
    2. 进行分级缓存(避免前端大量缓存失效)
    3. 避免使用 select 进行查询
    4. 分离业务网络和服务器网络
  • 大表带来的问题(重要)
    • 大表特点:记录行数巨大单表超千万;表数据文件巨大,超过10个G
    • 大表的危害:

a. 很难在短时间内过滤出需要的数据

  1. - 查询字区分度低 -> 要在大数据量的表中筛选出来其中一部分数据会产生大量的磁盘 io -> 降低磁盘效率

b. 对 DDL影响:建立索引需要很长时间

  - `MySQL-v<5.5` 建立索引会锁表 `MySQL-v>=5.5` 建立索引会造成主从延迟( `mysql`建立索引,先在组上执行,再在库上执行)

c. 修改表结构需要长时间的锁表:会造成长时间的主从延迟(‘480秒延迟’)

  • 大事务带来的问题(重要)
  • 什么影响了Mysql的性能(非常重要)
  1. 服务器硬件。
  2. 服务器系统(系统参数优化)。
  3. 存储引擎MyISAM:不支持事务,表级锁。InnoDB: 支持事务,支持行级锁,事务 ACID
  4. 数据库参数配置。
  5. 数据库结构设计和SQL语句。(重点优化)
  • 强烈建立对Innodb 使用独立表空间,优化什么的更方便,可控。
  • 性能优化顺序
    • 库结构设计和SQL语句
    • 数据库存储引擎的选择和参数配置
    • 系统选择及优化
    • 硬件升级

      2. mysql的事务

  1. 什么是事务

事务是数据库系统区别于其他一切文件系统的重要特性之一
事务是一组具有原子性的SQL语句,或是一个独立的工作单元
事务要求符合:原子性、一致性、隔离性、持久性

  1. 事务的 ACID属性

1、原子性( atomicity):全部成功,全部回滚失败。银行存取款。
2、一致性(consistent):银行转账的总金额不变。
3、隔离性(isolation):
4、持久性( DURABILITY):从数据库的角度的持久性,磁盘损坏就不行了

  • 未提交读
  • 已提交读
  • 可重复读
  • 可串行化

以上,隔离性由低到高,并发性由高到低
redolog机制保证事务更新的一致性持久性

3. 隔离级别

未提交读( READ UNCOMMITED) 脏读,两个事务之间互相可见;
已提交读( READ COMMITED)符合隔离性的基本概念,一个事务进行时,其它已提交的事物对于该事务是可见的,即可以获取其它事务提交的数据。
可重复读( REPEATABLE READ) InnoDB的默认隔离等级。事务进行时,其它所有事务对其不可见,即多次执行读,得到的结果是一样的!
可串行化( SERIALIZABLE) 在读取的每一行数据上都加锁,会造成大量的锁超时和锁征用,严格数据一致性且没有并发是可使用。
查看系统的事务隔离级别: show variables like'%iso%';
开启一个新事务: begin;
提交一个事务: commit;
修改事务的隔离级别: setsession tx_isolation='read-committed';

4. mvcc redolog binlog

MVCC是为了解决什么问题?

锁机制可以控制并发操作,但是其系统开销较大,而MVCC可以在大多数情况下代替行级锁,使用MVCC,能降低其系统开销.
MVCC实现
通俗的讲就是MVCC通过对数据进行多版本保存,根据比较版本号来控制数据是否展示,从而达到读取数据时无需加锁就可以实现事务的隔离性。
MVCC是通过保存数据在某个时间点的快照来实现的. 不同存储引擎的MVCC. 不同存储引擎的MVCC实现是不同的,典型的有乐观并发控制和悲观并发控制.
InnoDB的MVCC,是通过在每行记录后面保存两个隐藏的列来实现的,这两个列,分别保存了这个行的创建时间,一个保存的是行的删除时间。这里存储的并不是实际的时间值,而是系统版本号(可以理解为事务的ID),没开始一个新的事务,系统版本号就会自动递增,事务开始时刻的系统版本号会作为事务的ID.
InnoDB只会查找版本早于当前事务版本的数据行(也就是,行的系统版本号小于或等于事务的系统版本号),这样可以确保事务读取的行,要么是在事务开始前已经存在的,要么是事务自身插入或者修改过的.

  1. MVCC手段只适用于Msyql隔离级别中的读已提交(Read committed)和可重复读(Repeatable Read).
  2. Read uncimmitted由于存在脏读,即能读到未提交事务的数据行,所以不适用MVCC.

原因是MVCC的创建版本和删除版本只要在事务提交后才会产生。
客观上,我们认为他就是乐观锁的一整实现方式,就是每行都有版本号,保存时根据版本号决定是否成功。

5. 产生死锁的必要条件:

  • 互斥条件:资源排他性使用,资源只能被一个进程占用。如果此时还有其它进程请求该资源,则请求进程只能等待,直至占有该资源的进程用完释放。
  • 请求和保持条件:请求资源未果,进程虽阻塞但保持占有资源不放。
  • 环路等待:进程—-资源形成环形链{P,P,P2,…P},即P0正在等待一个P1占用的资源,P1正在等待P2占用的资源,……,Pn正在等待P0占用的资源。
  • 不剥夺条件:进程已获资源未使用完之前不能被剥夺。

6. MySQL建立索引遵循原则的注意点


  • 选择唯一性索引

唯一性索引的数据是唯一的,可以更快的通过该索引查询某条数据。

  • 为经常需要排序,分组和联合操作的字段建立索引

order by,group by的字段在排序操作时很是耗时,可以对其加索引。

  • 为经常作为查询条件的字段建立索引
  • 限制索引的数量

过多的索引很是消耗数据库的性能,多使用组合索引。

  • 尽量使用数据量少的索引。

如果索引的值很长,查询的速度会受到影响。

  • 删除不再使用或很少使用的索引

减少对索引的维护

  • 最左前缀原则
  • 选择区分度高的列作为索引
  • 索引列不能参与运算,否则索引失效
  • 尽量扩展索引,不要新增索引

例如,现在有个索引a,但是想新建一个组合索引(a,b),那么就对普通索引a,进行修改为组合索引,不要新建组合索引

7. InnoDB存储引擎的特性

特性一:事务性存储引擎及两个特殊日志类型:Redo Log 和 Undo Log

1、 Innodb 是一种事务性存储引擎
2、完全支持事务的 ACID特性。3、支持事务所需要的两个特殊日志类型:RedoLogUndoLog
Redo Log:实现事务的持久性(已提交的事务)。Undo Log:未提交的事务,独立于表空间,需要随机访问,可以存储在高性能io设备上。
Undo日志记录某数据被修改前的值,可以用来在事务失败时进行 rollbackRedo日志记录某数据块被修改后的值,可以用来恢复未写入 data file的已成功事务更新的数据。

特性二:支持行级锁

1、InnoDB支持行级锁。
2、行级锁可以最大程度地支持并发。
3、行级锁是由存储引擎层实现的。

8. 什么是锁

锁的主要作用是管理共享资源的并发访问
锁用于实现事务的隔离性

锁类型

共享锁(也称读锁)
独占锁(也称写锁)

锁的粒度

MySQL的事务支持不是绑定在MySQL服务器本身 而是与存储引擎相关

  • 表级锁
  • 行级锁

table_name加表级锁命令:locktable table_name write; 写锁会阻塞其它用户对该表的‘读写’操作,直到写锁被释放:unlock tables
1、锁的开销越大,粒度越小,并发度越高。2、表级锁通常是在服务器层实现的。3、行级锁是存储引擎层实现的。innodb的锁机制,服务器层是不知道的

阻塞和死锁

1、阻塞是由于资源不足引起的排队等待现象。
2、死锁是由于两个对象在拥有一份资源的情况下申请另一份资源,而另一份资源恰好又是这两对象正持有的,导致两对象无法完成操作,且所持资源无法释放。