Java面试题———MySql篇③

目录

1.查询语句执行流程

2.索引的数据结构是什么

3.数据库中的锁有哪些

4.MySQL日志类型

5.MySQL主从复制的流程

6.谈谈你对sql的优化的经验

---

1.查询语句执行流程

一条查询语句到达MySQL数据库之后，数据库中的各个组件会按照顺序执行自己的任务

1. 首先是连接器，他会负责建立连接、检查权限等操作

2. 连接成功之后，会查询缓存，如果缓存中有结果会直接返回；如果缓存中没有结果，会将sql交给分析器处理

3. 分析器负责检查sql的词法、语法，如果没有问题，再将sql交给优化器处理

4. 优化器会决定用哪个索引，决定表的连接顺序等，然后将优化之后的sql交给执行器

5. 执行器根据存储引擎类型，调用存储引擎接口

6. 存储引擎负责最后数据的读写

2.索引的数据结构是什么

在MySQL中索引使用的数据结构是B+Tree，B+树是基于B树的变种，它具有B树的平衡性，而且树的高度更低

• B+树非叶子节点不存在数据只存索引，因此其内部节点相对B树更小，树的高度更小，查询产生的I/O更少

• B+树查询效率更高，B+树使用双向链表串连所有叶子节点，区间查询效率更高

• B+树查询效率更稳定，B+树每次都必须查询到叶子节点才能找到数据，而B树查询的数据可能不在叶子节点，也可能在，这样就会造成查询的效率的不稳定

3.数据库中的锁有哪些

MySQL中的锁从不同维度可以分为不同的种类

1. 从锁的粒度上可以分为表锁和行锁

   表锁指的是会锁定修改数据所在的整个表，开销小，加锁快，锁定粒度大，发生锁冲突概率高

   行锁指的是会锁定修改数据所在的行记录，开销大，加锁慢，锁定粒度小，发生锁冲突概率低

2. 从锁的排他性上分为共享锁和排他锁

   共享锁指的是当一个事务针对同一份数据加上共享锁之后，另一个事务也可以再往上加一把共享锁，也可以读数据，但是不能改

   对索引列加共享锁，锁定的是一行数据；对非索引列加共享锁，锁定的是整表数据

   排他锁指的的是当一个事务针对同一份数据加上排他锁之后，另一个事务只能读数据，不能改，也不能再上其它任务锁

3. 还有两种概念上的锁是悲观锁和乐观锁

   悲观锁是指一个事务在修改数据的时候，总是认为别人也会修改此数据，所以强制要使用锁来保证数据安全

   乐观锁是指一个事务在修改数据的时候，总是认为别人不会修改此数据，因为不加任何锁

   这种情况下万一在当前事务修改的时候，数据被其它事务也修改了，机会出现问题，此时常用的方案是：

   给数据表中添加一个version列，每次更新后都将这个列的值加1，读取数据时，将版本号读取出来

   在执行更新的时候，会先比较版本号，如果相同则执行更新，如果不相同，说明此条数据已经发生了变化，就放弃更新或重试

4.MySQL日志类型

MySQL的很多功能都是依靠日志来实现的，比如事务回滚，数据备份，主从复制等等，常见的日志有下面几个

1. binlog归档日志

   负责记录对数据库的写操作，一般用在主从复制过程中记录日志，从库拷贝此日志做重放实现数据同步

2. redolog重做日志

   用于确保事务的持久性。防止在发生故障的时间点，尚有脏页未写入磁盘

   在重启 mysql 服务的时候，根据 redo log 进行重做，从而达到事务的持久性这一特性

3. undo log 回滚日志

   保存了事务发生之前的数据的一个版本，可以用于回滚

5.MySQL主从复制的流程

主从复制用于MySQL主从集群的主节点向从节点同步数据，主要是依靠MySQL的binLog实现的，大体流程分为三步：

1. Master 主库在事务提交时，会把数据变更记录在二进制日志文件 BinLog中

2. 从库读取主库的二进制日志文件 Binlog ，写入到从库的中继日志 RelayLog

3. slave重做中继日志中的事件，将改变反映它自己的数据

6.谈谈你对sql的优化的经验

我在企业中优化Sql大体分为三步：

1. 查找问题sql，主要手段是开启mysql的慢查询日志，它会将执行时间较长的sql记录记录下来

2. 找到sql之后，我会分析出现问题的原因，原因很多，主要字段类型选择错误、sql语句效率低、索引失效等等

3. 根据问题不同，我会再去定具体的解决方案

简单给您说几个常见的把

1. 确定选择的引擎是否合适

   myisam适合于查询为主，增删较少，无事务要求的数据表

   Innodb适用于有事务处理，或者包括很多的更新和删除的数据表

2. 表设计是否合理

   单表不要有太多字段，建议在20以内

   合理的加入冗余字段可以提高查询速度

3. 确定字段的数据类型是否合适

   数值型字段的比较比字符串的比较效率高得多，字段类型尽量使用最小、最简单的数据类型

   设置合适的字符串类型（char和varchar）char定长效率高，varchar可变长度，效率稍低，varchar的长度只分配真正需要的空间

   尽量使用TIMESTAMP而非DATETIME，尽量设计所有字段都得有默认值，尽量避免null

4. 确定sql的书写是否有的题

   SELECT语句务必指明字段名称，避免直接使用select *

   SQL语句中IN包含的值不应过多

   可以用内连接，就尽量不要使用外连接

   使用连接连接查询来代替子查询

   适用联合(UNION)来代替手动创建的临时表

5. 表数据比较多的时候是否添加了合适的索引

   表的主键、外键必须有索引

   经常出现在where子句中的字段，特别是大表的字段，应该建立索引

   经常用于排序、分组的字段，应当建立索引

   加上索引之后，还应该使用Explain来确认索引是否生效

6. 如果上面的几项都没有问题，那可能就是因为服务器性能或者数据量过大导致的查询慢，此时可以考虑读写分离

   也就是我们搭建一个MySQL的主从集群，让1个主节点负责写入数据，多个从节点负责查询数据，已分摊查询压力