1、关系型和非关系型数据库的区别你了解多少？

非关系型数据库（NOSQL）和关系型数据库（SQL）区别详解
image.png
关系型数据库（SQL）
定义：
关系型数据库指的是使用关系模型（二维表格模型）来组织数据的数据库。
关系模型可以简单理解为二维表格模型，而一个关系型数据库就是由二维表及其之间的关系组成的一个数据组织。
常见的有Oracle 、 MySql、SQL Server、SQLite等。
优势：

  1. 它采用了关系模型（二维表结构）来组织数据，贴近正常开发逻辑，容易理解。
  2. 支持通用的SQL（结构化查询语言）语句；
  3. 丰富的完整性大大减少了数据冗余和数据不一致的问题。并且全部由表结构组成，文件格式一致；
  4. 可以用SQL句子多个表之间做非常繁杂的查询；
  5. 关系型数据库提供对事务的支持，能保证系统中事务的正确执行，同时提供事务的恢复、回滚、并发控制和死锁问题的解决。
  6. 数据存储在磁盘中，安全可靠。

不足：

  1. 高并发读写能力差：网站类用户的并发性访问非常高，而一台数据库的最大连接数有限，且硬盘 I/O 有限，不能满足很多人同时连接。
  2. 海量数据情况下读写效率低：对大数据量的表进行读写操作时，需要等待较长的时间等待响应。
  3. 可扩展性不足：不像web server和app server那样简单的添加硬件和服务节点来拓展性能和负荷工作能力。
  4. 数据模型灵活度低：关系型数据库的数据模型定义严格，无法快速容纳新的数据类型（需要提前知道需要存储什么样类型的数据）。

非关系型数据库（NOSQL）
非关系型数据库又被称为 NoSQL（Not Only SQL )，意为不仅仅是 SQL。通常指数据以对象的形式存储在数据库中，而对象之间的关系通过每个对象自身的属性来决定，常用于存储非结构化的数据。
常见的NOSQL数据库：

  1. 键值数据库：Redis、Memcached、Riak
  2. 列族数据库：Bigtable、HBase、Cassandra
  3. 文档数据库：MongoDB、CouchDB、MarkLogic
  4. 图形数据库：Neo4j、InfoGrid

优势

  1. 非关系型数据库存储数据的格式可以是 key-value 形式、文档形式、图片形式等。使用灵活，应用场景广泛，而关系型数据库则只支持基础类型。
  2. 速度快，效率高。 NoSQL 可以使用硬盘或者随机存储器作为载体，而关系型数据库只能使用硬盘。 不需要经过SQL层的解析。
  3. 海量数据的维护和处理非常轻松，成本低。
  4. 非关系型数据库具有扩展简单、高并发、高稳定性、成本低廉的优势。
  5. 可以实现数据的分布式处理

不足

  1. 非关系型数据库暂时不提供 SQL 支持，学习和使用成本较高。
  2. 非关系数据库没有事务处理，无法保证数据的完整性和安全性。适合处理海量数据，但是不一定安全。
  3. 功能没有关系型数据库完善。
  4. 复杂表关联查询不容易实现。

适用场景

  • 日志系统
  • 地理位置存储
  • 数据量巨大

  • 高可用

    2、MySQL中CHAR和VARCHAR的区别有哪些？

  • char的长度是不可变的，用空格填充到指定长度大小，而varchar的长度是可变的。

  • char的存取速度还是要比varchar要快得多
  • char的存储方式是：对英文字符（ASCII）占用1个字节，对一个汉字占用两个字节。varchar的存储方式是：对每个英文字符占用2个字节，汉字也占用2个字节。

char 优点：效率高；缺点：占用空间；
适用场景：存储密码的 md5 值，固定长度的，使用 char 非常合适。
从空间上考虑 varcahr 比较合适；从效率上考虑 char 比较合适，二者使用需要权衡。

3、varchar(10) 和 varchar(20) 的区别？

内存使用字符类型中定义的长度。
varchar(10) 中 10 的涵义最多存放 10 个字符，varchar(10) 和 varchar(20) 存储 hello 所占空间一样，但后者在排序时会消耗更多内存，因为 order by col 采用 fixed_length 计算 col 长度。

4、说一下MySQL是如何执行一条SQL的？具体步骤有哪些？

Server层按顺序执行sql的步骤为：
1. 客户端请求->
2. 连接器（验证用户身份，给予权限） ->
3. 查询缓存（存在缓存则直接返回，不存在则执行后续操作）->
4. 分析器（对SQL进行词法分析和语法分析操作） ->
5. 优化器（主要对执行的sql优化选择最优的执行方案方法） ->
6. 执行器（执行时会先看用户是否有执行权限，有才去使用这个引擎提供的接口）->
7. 去引擎层获取数据返回（如果开启查询缓存则会缓存查询结果）
简单概括：

  • 连接器：管理连接、权限验证；
  • 查询缓存：命中缓存则直接返回结果；
  • 分析器：对SQL进行词法分析、语法分析；（判断查询的SQL字段是否存在也是在这步）
  • 优化器：执行计划生成、选择索引；
  • 执行器：操作引擎、返回结果；
  • 存储引擎：存储数据、提供读写接口。

image.png

5、你了解MySQL的内部构造吗？一般可以分为哪两个部分？

可以分为服务层和存储引擎层两部分，其中：
服务层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖MySQL的大多数核心服务功能，以及
所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如
存储过程、触发器、视图等。
存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、Memory等多个
存储引擎。现在最常用的存储引擎是InnoDB，它从MySQL 5.5.5版本开始成为了默认的存储引擎。
连接器
负责跟客户端建立连接、获取权限、维持和管理连接。

  1. mysql -h$ip -P$port -u$user -p

注意：

  • 修改不会影响已经存在连接的权限
  • show processlist
  • 自动断开，8h
  • 数据库里面，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。
  • 建立连接的过程通常是比较复杂的，建议尽量减少建立连接的动作，尽量使用长连接。全部使用长连接后，MySQL占用内存涨得特别快，这是因为

MySQL在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是MySQL异常重启了。如何解决？

  1. 定期断开长连接。
  2. MySQL 5.7以上，mysql_reset_connection来重新初始化连接资源。

查询缓存

  • key-value对：key是查询的语句，value是查询的结果。
  • 不在，执行后会被存入查询缓存
  • 不建议使用，弊大于利，查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。更新频繁时效率低。

  • “按需使用”，不使用查询缓存，将参数query_cache_type设置成DEMAND，确定使用，用SQL_CACHE显式指定。

    mysql> select SQL_CACHE * from T where ID=10；

  • MySQL 8.0版本没有此功能了。

分析器

  • 词法分析-语法分析，”词法分析”：识别出SQL语句中的字符串分别是什么，代表什么。语法分析器会根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法。
  • Mysql知道了你要做什么

优化器

  • 优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。选择一个执行效率高的。
  • 知道了该怎么做

执行器

  • 判断有没有执行查询的权限。如果命中查询缓存，会在查询缓存放回结果的时候，做权限验证。查询也会在优化器之前调用precheck验证权限。
  • 打开表，使用对应引擎提供的接口。
  • 去某行-判断-跳过/存在结果集中-循环-返回结果集。

rows_examined字段与引擎扫描行数不一定相等，可扫描多行。

6、一条SQL更新语句是如何执行的?

查询语句的那一套流程，更新语句也是同样会走一遍。
不一样的是，还涉及两个重要的日志模块为 redo log（重做日志）和 binlog（归档日志）。
redo log

  • 物理日志
  • WAL技术（Write-Ahead Logging），它的关键点就是先写日志，再写磁盘。
  • InnoDB的redo log是固定大小的，从头开始写，写到末尾就又回到开头循环写。
  • 有了redo log，InnoDB就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。
  • “两阶段提交”：保证redo log和binlog逻辑上的一致性。
  • innodb_flush_log_at_trx_commit这个参数设置成1时，表示每次事务的redo log都直接持久化到磁盘。

binlog

  • 逻辑日志
  • redo log是InnoDB引擎特有的日志，而Server层也有自己的日志，称为binlog（归档日志）。
  • sync_binlog这个参数设置成1时，表示每次事务的binlog都持久化到磁盘。

为什么会有两份日志呢？
因为最开始MySQL里并没有InnoDB引擎。MySQL自带的引擎是MyISAM，但是MyISAM没有crash-safe的能力，binlog日志只能用于归档。而InnoDB是另一个公司以插件形式引入MySQL的，既然只依靠binlog是没有crash-safe能力的，所以InnoDB使用另外一套日志系统——也就是redo log来实现crash-safe能力。
两者区别

  1. redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使用。
  2. redo log是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”。
  3. redo log是循环写的，空间固定会用完；binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

图中浅色框表示是在InnoDB内部执行的，深色框表示是在执行器中执行的。
image.png

问题：一天一备跟一周一备的对比。
好处是“最长恢复时间”更短。
在一天一备的模式里，最坏情况下需要应用一天的binlog。比如，你每天0点做一次全量备份，而要恢复出一个到昨天晚上23点的备份。
一周一备最坏情况就要应用一周的binlog了。
系统的对应指标就是RTO（恢复目标时间）。
当然这个是有成本的，因为更频繁全量备份需要消耗更多存储空间，所以这个RTO是成本换来的，就需要你根据业务重要性来评估了。

7、说一说Drop、Delete与Truncate的共同点和区别

image.png
Drop、Delete、Truncate都表示删除，但是三者有一些差别：
Delete用来删除表的全部或者一部分数据行，执行delete之后，用户需要提交(commmit)或者回滚(rollback)来执行删除或者撤销删除，会触发这个表上所有的delete触发器。 可以加where字句。
Truncate删除表中的所有数据，这个操作不能回滚，也不会触发这个表上的触发器，TRUNCATE比delete更快，占用的空间更小。
Drop命令从数据库中删除表，所有的数据行，索引和权限也会被删除，所有的DML触发器也不会被触发，这个命令也不能回滚。
因此，在不再需要一张表的时候，用Drop；在想删除部分数据行时候，用Delete；在保留表而删除所有数据的时候用Truncate。
具体解析
1. DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。
2. 表和索引所占空间。当表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小，而DELETE操作不会减少表或索引所占用的空间。drop语句将表所占用的空间全释放掉。
3. 一般而言，drop > truncate > delete
4. 应用范围。TRUNCATE 只能对TABLE；DELETE可以是table和view
5. TRUNCATE 和DELETE只删除数据，而DROP则删除整个表（结构和数据）。
6. truncate与不带where的delete ：只删除数据，而不删除表的结构（定义）drop语句将删除表的结构被依赖的约束（constrain),触发器（trigger)索引（index);依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。
7. delete语句为DML（Data Manipulation Language),这个操作会被放到 rollback segment中,事务提交后才生效。如果有相应的 tigger,执行的时候将被触发。
8. truncate、drop是DDL（Data Define Language),操作立即生效，原数据不放到 rollback segment中，不能回滚
9. 在没有备份情况下，谨慎使用 drop 与 truncate。要删除部分数据行采用delete且注意结合where来约束影响范围。回滚段要足够大。要删除表用drop;若想保留表而将表中数据删除，如果与事务无关，用
truncate即可实现。如果和事务有关，或老是想触发trigger,还是用delete。
10. Truncate table 表名 速度快,而且效率高,因为: truncate table 在功能上与不带 WHERE 子句的 DELETE语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。
11. TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。
12. 对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。

8、SQL中的NOW()和CURRENT_DATE()两个函数有什么区别？

NOW（）命令用于显示当前年份，月份，日期，小时，分钟和秒。
CURRENT_DATE（）仅显示当前年份，月份和日期。

9、SQL 与 MySQL 有什么区别？

SQL 和 MySQL 是 DBMS 中最令人困惑的两个术语，二者之间存在本质上的区别。

  • SQL 是一种 结构化查询语言，用于在数据库上执行各种操作，但 MySQL 是一个 关系数据库管理系统（RDBMS），使用 SQL 执行所有数据库操作。
  • SQL 用于访问，更新和操作数据库中的数据，用户使用时需要学习该语言，然后编写查询，而 MySQL 是一个软件，会为用户提供一个界面，只需单击一些按钮即可用于执行各种数据库操作。

  • 由于 MySQL 是一个软件，所以它会定期获得各种更新，但在 SQL 中，命令总是相同的。

    10、UNION 与 UNION ALL 的区别

    UNION 用于把来自多个 SELECT 语句的结果组合到一个结果集合中，MySQL 会把结果集中 重复的记录删掉，而使用 UNION ALL，MySQL 会把所有的记录返回，且效率高于 UNION 。

    11、数据库中的主键、超键、候选键、外键是什么？

    超 键：在关系中，能唯一标识元组的属性集称为关系模式的超键。一个属性可以作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。
    候选键：是最小超键，即没有冗余元素的超键。
    主 键：数据库表中对储存数据对象予以 唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（NULL）。
    外 键：在一个表中存在的另一个表的主键称此表的外键，外键可以有重复的, 可以是空值。外键是用来和其他表建立联系用的。如果关系模式R中属性K是其它模式的主键，那么k在模式R中称为外键。
    主键为候选键的子集，候选键为超键的子集，而外键的确定是相对于主键的。

    12、SQL语法中内连接、自连接、外连接（左、右、全）、交叉连接的区别分别是什么？

    内连接：只有两个元素表相匹配的才能在结果集中显示。
    外连接：
    左外连接: 左边为驱动表，驱动表的数据全部显示，匹配表的不匹配的不会显示。
    右外连接:右边为驱动表，驱动表的数据全部显示，匹配表的不匹配的不会显示。
    全外连接：连接的表中不匹配的数据全部会显示出来。
    交叉连接： 笛卡尔效应，显示的结果是链接表数的乘积。

    13、数据库union join的区别

    1) join 是两张表做交连后⾥⾯条件相同的部分记录产⽣⼀个记录集，union是产⽣的两个记录集(字段要⼀样的)并在⼀起，成为⼀个新的记录集 。
    2) union在数据库运算中会过滤掉᯿复数据，并且合并之后的是根据⾏合并的，即：如果a表和b表中的数据各有五⾏，且有两⾏是重复数据，合并之后为8⾏。运⽤场景：适合于需要进⾏统计的运算
    3) union all是进⾏全部合并运算的，即：如果a表和b表中的数据各有五⾏，且有两⾏是᯿复数据，合并之后为10⾏。
    4) join是进⾏表关联运算的，两个表要有⼀定的关系。即：如果a表和b表中的数据各有五⾏，且有两⾏是᯿复数据，根据某⼀列值进⾏笛卡尔运算和条件过滤，假如a表有2列，b表有2列，join之后是4列。

    14、数据库的三大范式是什么？

    数据库设计的三大范式
    范式是符合某一种级别的关系模式的集合。
    1）第一范式
    第一范式（1NF）用来确保每列的原子性，要求每列（或者每个属性值）都是不可再分的最小数据单元（也称为最小的原子单元）
    2）第二范式
    第二范式（2NF）在第一范式的基础上更进一层，要求表中的每列都和主键相关，即要求实体的唯一性。如果一个表满足第一范式，并且除了主键以外的其他列全部都依赖于该主键，那么该表满足第二范式。
    3）第三范式
    第三范式（3NF）在第二范式的基础上更进一层，第三范式是确保每列都和主键列直接相关，而不是间接相关，没有传递依赖，即限制列的冗余性。如果一个关系满足第二范式，并且除了主键以外的其他列都依赖于主键列，列和列之间不存在相互依赖关系，则满足第三范式。
    反范式化
    不满足范式的数据库设计，就是反范式化。
    1）范式化优点如下：

  • 减少数据冗余

  • 范式化后的表中只有很少的重复数据，更新时只需要更新较少的数据，所以范式化的更新操作比反范式化更快
  • 范式化的表通常比反范式化更小
    缺点如下：
  • 范式化的表在查询时经常需要很多的关联，这回导致性能降低
  • 增加了索引优化的难度

2）反范式化优点如下：

  • 可以减少表的关联
  • 可以更好的进行索引优化
    缺点如下：
  • 数据表存在数据冗余及数据维护异常

  • 对数据的修改需要更多的成本

    15、为什么要分库分表？

    目的在于，减小数据库的单库单表负担，提高查询性能，缩短查询时间。
    数据库中的数据量不一定是可控的，随着时间和业务的发展，库中的表会越来越多，表中的数据量也会越来越大，相应地数据操作，例如 增删改查的开销 也会越来越大；另外，若不进行分布式部署，而一台服务器的 资源（CPU、磁盘、内存、IO 等）是有限的，最终数据库所能承载的数据量、数据处理能力都将遭遇瓶颈。
    所以，从 性能 和 可用性 角度考虑，会进行数据库拆分处理，具体地说，把原本存储于一个库的数据分块存储到多个库上，把原本存储于一个表的数据分块存储到多个表上，即 分库分表。
    存在的问题

  • 事务问题：分库分表后，就成了分布式事务。如果依赖数据库本身的分布式事务管理功能去执行事务，将付出高昂的性能代价； 如果由应用程序去协助控制，形成程序逻辑上的事务，又会造成编程方面的负担。

  • 跨库跨表的 JOIN 问题：在执行了分库分表之后，难以避免会将原本逻辑关联性很强的数据划分到不同的表、不同的库上，这时，表的关联操作将受到限制，我们无法 JOIN 位于不同分库的表，也无法 JOIN 分表粒度不同的表，结果原本一次查询能够完成的业务，可能需要多次查询才能完成。
  • 额外的数据管理负担和数据运算压力：额外的数据管理负担，最为常见的是数据的 定位问题 和数据的 增删改查 的重复执行问题，这些都可以通过应用程序来解决，但必然会引起额外的逻辑运算。

分布式困境与应对

  • 数据迁移与扩容问题——一般做法是通过程序先读出数据，然后按照指定的分表策略再将数据写入到各个分表中。
  • 分页与排序问题——需要在不同的分表中将数据进行排序并返回，并将不同分表返回的结果集进行汇总和再次排序，最后再返回给用户

分表策略
可以归纳为垂直拆分和水平拆分：

  • 水平切分

水平切分是将同一个表中的记录拆分到多个结构相同的表中。当一个表的数据不断增多时，水平切分是必然的选择，它可以将数据分布到集群的不同节点上，从而缓存单个数据库的压力。

  • 垂直切分

垂直切分是将一张表按列切分成多个表，通常是按照列的关系密集程度进行切分，也可以利用垂直切分将经常被使用的列和不经常被使用的列切分到不同的表中。例如将原来的电商数据库垂直切分成商品数据库、用户数据库等。
库内分表，仅仅是解决了单表数据过大的问题，但并没有把单表的数据分散到不同的物理机上，因此并不能减轻 MySQL 服务器的压力，仍然存在同一个物理机上的资源竞争和瓶颈，包括 CPU、内存、磁盘IO、网络带宽等。

16、谈谈你对数据库读写分离的理解？如何实现？

读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器。主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作。
读写分离能提高性能的原因在于：

  1. 主从服务器负责各自的读和写，极大程度缓解了锁的争用；
  2. 从服务器可以使用 MyISAM，提升查询性能以及节约系统开销；
  3. 增加冗余，提高可用性。

实现方案
MySQL 读写分离的实现方式主要基于 主从复制，通过 路由的方式 使应用对数据库的写请求只在 Master 上进行，读请求在 Slave 上进行。
具体地，有以下四种实现方案：
方案一：基于 MySQL proxy 代理
在应用和数据库之间增加 代理层，代理层接收应用对数据库的请求，根据不同请求类型（即是读 read 还是写 write）转发到不同的实例，在实现读写分离的同时可以实现负载均衡。MySQL 的代理最常见的是 mysql-proxy、cobar、mycat、Atlas 等。
方案二：基于应用内路由
基于应用内路由的方式即为在应用程序中实现，针对不同的请求类型去不同的实例执行 SQL。
具体实现可基于 spring 的 aop：用 aop 来拦截 spring 项目的 dao 层方法，根据方法名称就可以判断要执行的类型，进而动态切换主从数据源。
方案三：基于 MySQL-Connector-Java 的 JDBC 驱动方式
Java 程序通过在连接 MySQL 的 JDBC 中配置主库与从库等地址，JDBC 会自动将读请求发送给从库，将写请求发送给主库，此外， MySQL 的 JDBC 驱动还能够实现多个从库的负载均衡。
方案四：基于 sharding-jdbc 的方式
sharding-sphere 是强大的读写分离、分表分库中间件，sharding-jdbc 是 sharding-sphere 的核心模块。

17、主从复制涉及了哪些线程？

主要涉及三个线程：binlog 线程、I/O 线程和 SQL 线程。

  1. binlog 线程 ：负责将主服务器上的数据更改写入二进制日志（Binary log）中。
  2. I/O 线程 ：负责从主服务器上读取二进制日志，并写入从服务器的重放日志（Relay log）中。

  3. SQL 线程 ：负责读取重放日志并重放其中的 SQL 语句。
    image.png

    18、主从同步的延迟原因及解决办法？

    主从同步的延迟的原因：
    假如一个服务器开放 Ｎ 个连接给客户端，这样有会有大并发的更新操作, 但是从服务器的里面读取 binlog 的线程仅有一个， 当某个 SQL 在从服务器上执行的时间稍长或者由于某个 SQL 要进行锁表就会导致主服务器的 SQL 大量积压，未被同步到从服务器里。这就导致了主从不一致， 也就是主从延迟。
    主从同步延迟的解决办法：
    实际上主从同步延迟根本没有什么一招制敌的办法， 因为所有的 SQL 必须都要在从服务器里面执行一遍，但是主服务器如果不断的有更新操作源源不断的写入，那么一旦有延迟产生，那么延迟加重的可能性就会原来越大。当然我们可以做一些缓解的措施。

  4. 我们知道因为主服务器要负责更新操作， 它对安全性的要求比从服务器高，所有有些设置可以修改，比如sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之类的设置，而 slave 则不需要这么高的数据安全，完全可以将 sync_binlog 设置为 0 或者关闭 binlog、innodb_flushlog、innodb_flush_log_at_trx_commit 也 可以设置为 0 来提高 SQL 的执行效率。

  5. 增加从服务器，这个目的还是分散读的压力， 从而降低服务器负载。

    19、MySQL优化了解吗？说一下从哪些方面可以做到性能优化？

  • 为搜索字段创建索引
  • 避免使用 Select *，列出需要查询的字段
  • 垂直分割分表
  • 选择正确的存储引擎

    20、查询性能的优化方法？

    减少请求的数据量
  1. 只返回必要的列：最好不要使用 SELECT * 语句。
  2. 只返回必要的行：使用 LIMIT 语句来限制返回的数据。
  3. 缓存重复查询的数据：使用缓存可以避免在数据库中进行查询，特别在要查询的数据经常被重复查询时，缓存带来的查询性能提升将会是非常明显的。

减少服务器端扫描的行数
最有效的方式是使用索引来覆盖查询。

21、MySQL 问题排查都有哪些手段？

  1. 使用 show processlist 命令查看当前所有连接信息；
  2. 使用 Explain 命令查询 SQL 语句执行计划；
  3. 开启慢查询日志，查看慢查询的 SQL。

    22、MySQL 数据库 CPU 飙升到 500% 的话他怎么处理？

    当 CPU 飙升到 500% 时，先用操作系统命令 top 命令观察是不是 mysqld 占用导致的，如果不是，找出占用高的进程，并进行相关处理。
    如果是 mysqld 造成的，通过 SHOW PROCESSLIST 查看正在运行的线程，是不是有消耗资源的 SQL 在运行，找出其中消耗高的 SQL，看看执行计划是否准确， index 是否缺失，或者是数据量太大造成。
    然后 kill 掉这些线程（同时观察 CPU 使用率是否下降），等进行相应的调整（比如说加索引、改 SQL、改内存参数）之后，再重新跑这些 SQL。
    若每个 SQL 消耗资源都不多，只是同一时间大量的 session 连进来导致 CPU 飙升，这种情况就需要分析为何连接数会激增，再做出相应的调整，比如说限制连接数等

    23、MySQL常见的存储引擎InnoDB、MyISAM的区别？适用场景分别是？

    InnoDB
  • 是 MySQL 默认的事务型存储引擎，只有在需要它不支持的特性时，才考虑使用其它存储引擎。
  • 实现了四个标准的隔离级别，默认级别是可重复读(REPEATABLE READ)。在可重复读隔离级别下，通过多版本并发控制(MVCC)+ 间隙锁(Next-Key Locking)防止幻影读。
  • 主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升。
  • 内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。
  • 支持真正的在线热备份。其它存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。

MyISAM

  • 设计简单，数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，仍可以使用
  • 提供了大量的特性，包括压缩表、空间数据索引等。
  • 不支持事务。
  • 不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作时，也可以往表中插入新的记录，这被称为并发插入(CONCURRENT INSERT)。

总结

  1. 事务：MyISAM不支持事务，InnoDB支持事务；
  2. 全文索引：MyISAM 支持全文索引，InnoDB 5.6 之前不支持全文索引；
  3. 关于 count()：MyISAM会直接存储总行数，InnoDB 则不会，需要按行扫描。意思就是对于 select count() from table; 如果数据量大，MyISAM 会瞬间返回，而 InnoDB 则会一行行扫描；
  4. 外键：MyISAM 不支持外键，InnoDB 支持外键；
  5. 锁级别：MyISAM 只支持表锁，InnoDB 可以支持行锁及外键约束。
  6. MyISAM采用非聚集索引，B+树叶子存储指向数据文件的指针。InnoDB主键索引采用聚集索引，B+树叶子存储数据
  7. 备份：InnoDB 支持在线热备份。
  8. 崩溃恢复: MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。
  9. 其它特性: MyISAM 支持压缩表和空间数据索引。

适用场景：
MyISAM适合：
插入不频繁，查询非常频繁，如果执行大量的SELECT，MyISAM是更好的选择，没有事务。
InnoDB适合：
可靠性要求比较高，或者要求事务； 表更新和查询都相当的频繁， 大量的INSERT或 UPDATE。
特点：
⼤尺⼨的数据集趋向于选择InnoDB引擎，因为它⽀持事务处理和故障恢复。数据库的⼤⼩决定了故障恢复的时间⻓短，InnoDB可以利⽤事务⽇志进⾏数据恢复，这会⽐较快。主键查询在InnoDB引擎下也会相当快，不过需要注意的是如果主键太⻓也会导致性能问题。⼤批的INSERT语句(在每个INSERT语句中写⼊多⾏，批量插⼊)在MyISAM下会快⼀些，但是UPDATE语句在InnoDB下则会更快⼀些，尤其是在并发量⼤的时候。

24、你知道哪些数据库结构优化的手段？

  • 范式优化： 比如消除冗余（节省空间。）
  • 反范式优化：比如适当加冗余等（减少join）
  • 限定数据的范围： 务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内。
  • 读/写分离： 经典的数据库拆分方案，主库负责写，从库负责读；

  • 拆分表：分区将数据在物理上分隔开，不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样，当对这个表进行查询时，只需要在表分区中进行扫描，而不必进行全表扫描，明显缩短了查询时间，另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘I/O，一个精心设置的分区可将数据传输对磁盘I/O竞争均匀地分散开。对数据量大的时时表可采取此方法。可按月自动建表分区.

    25、数据库高并发是我们经常会遇到的，你有什么好的解决方案吗？

  • 在web服务框架中加入缓存。在服务器与数据库层之间加入缓存层，将高频访问的数据存入缓存中减少数据库的读取负担。

  • 增加数据库索引，进而提高查询速度。（不过索引太多会导致速度变慢，并且数据库的写入会导致索引的更新，也会导致速度变慢）
  • 主从读写分离，让主服务器负责写，从服务器负责读。
  • 将数据库进行拆分，使得数据库的表尽可能小，提高查询的速度。

  • 使用分布式架构，分散计算压力。

    26、一道场景题：假如你所在的公司选择MySQL数据库作数据存储，一天五万条以上的增量，预计运维三年，你有哪些优化手段？

  • 设计良好的数据库结构，允许部分数据冗余，尽量避免join查询，提高效率。

  • 选择合适的表字段数据类型和存储引擎，适当的添加索引。
  • MySQL库主从读写分离。
  • 找规律分表，减少单表中的数据量提高查询速度。
  • 添加缓存机制，比如Memcached，Apc等。
  • 不经常改动的页面，生成静态页面。
  • 书写高效率的SQL。比如 SELECT * FROM TABEL 改为 SELECT field_1, field_2, field_3 FROM TABLE。

    27、数据库连接池的作⽤

    1) 在内部对象池中，维护⼀定数量的数据库连接，并对外暴露数据库连接的获取和返回⽅法，如外部使⽤者可通过getConnection⽅法获取数据库连接，使⽤完毕后再通过releaseConnection⽅法将连接返回，注意此时的连接并没有关闭，⽽是由连接池管理器回收，并为下⼀次使⽤做好准备。
    2) 资源重⽤，由于数据库连接得到重⽤，避免了频繁创建、释放连接引起的⼤量性能开销。在减少系统消耗的基础上，增进了系统环境的平稳性（减少内存碎⽚以级数据库临时进程、线程的数量）
    3) 更快的系统响应速度，数据库连接池在初始化过程中，往往已经创建了若⼲数据库连接置于池内备⽤。此时连接池的初始化操作均已完成。对于业务请求处理⽽⾔，直接利⽤现有可⽤连接，避免了数据库连接初始化和释放过程的时间开销，从⽽缩减了系统整体响应时间。
    4) 新的资源分配⼿段，对于多应⽤共享同⼀数据库的系统⽽⾔，可在应⽤层通过数据库连接的配置，实现数据库连接技术。
    5) 统⼀的连接管理，避免数据库连接泄露，较较为完备的数据库连接池实现中，可根据预先的连接占⽤超时设定，强制收回被占⽤的连接，从⽽避免了常规数据库连接操作中可能出现的资源泄露。