>[info] 订单、库存两个表 如何保证数据的一致性？ 在一个电子商务系统中，正常的应该是订单生成成功后，相应的库存进行减少必须要保证两者的一致性，但有时候因 为某些原因，比如程序逻辑问题，并发等问题，导致下单成功而库存没有减少的情况。这种情况我们是不允许发生 的，MySQL的中的事务刚好可以解决这一问题，首先得选择数据库的存储引擎为InnoDB的，事务规定了只有下订单 完成了，并且相应的库存减少了才允许提交事务，否则就事务回滚，确保数据一致性。 >[info] 外键的作用 保持数据一致性，完整性，主要目的是控制存储在外键表中的数据 >[info] 什么是读锁，什么是写锁？ * **读锁：** 也叫共享锁、S锁，若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S 锁，而不能加X锁，直到T释放A上的S 锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修 改。 * **写锁：** 又称排他锁、X锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加 任何锁，直到T释放A上的锁。这保证了其他事务在T释放A上的锁之前不能再读取和修改A。 >[info] 数据库中，什么是锁？ 数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数 据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。 ***** **MyISAM和InnoDB存储引擎使用的锁：** * MyISAM采用表级锁(table-level locking)。 * InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁 ***** **表级锁和行级锁对比**： * **表级锁：** Mysql中锁定 粒度最大 的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁 快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和 InnoDB引擎都支持表 级锁。 **行级锁：** Mysql中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲 突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。 ***** **InnoDB存储引擎的锁的算法有三种：** * Record lock：单个行记录上的锁 * Gap lock：间隙锁，锁定一个范围，不包括记录本身 * Next-key lock：record+gap 锁定一个范围，包含记录本身 >[info] mysql事务隔离级别，说下你对事务的隔离性的理解？ **** **为了达到上述事务特性，数据库定义了几种不同的事务隔离级别：** * **READ_UNCOMMITTED（RU 未授权读取）**: 最低的隔离级别，允许读取尚未提交的数据变更，**可能会导致脏读、 幻读或不可重复读** * **READ_COMMITTED（RC 授权读取）**: 允许读取并发事务已经提交的数据，**可以阻止脏读，但是幻读或不可重复读 仍有可能发生** * **REPEATABLE_READ（RR 可重复读）**: 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修 改，**可以阻止脏读和不可重复读，但幻读仍有可能发生。** * **SERIALIZABLE（串行）**: 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之 间就完全不可能产生干扰，也就是说，**该级别可以防止脏读、不可重复读以及幻读。** 但是这将严重影响程序的 性能。通常情况下也不会用到该级别。 ***** **这里需要注意的是：** Mysql 默认采用的 REPEATABLE\_READ隔离级别 Oracle 默认采用的 READ\_COMMITTED 隔离级别. ***** 事务隔离机制的实现基于锁机制和并发调度。其中并发调度使用的是MVVC（多版本并发控制），通过保存修改 的旧版本信息来支持并发一致性读和回滚等特性。 >[info] 什么是事务(ACID)？及其特性？ **事务特性：（ACID） :** 1. **原子性（Atomicity）：** 即不可分割性，事务要么全部被执行，要么就全部不被执行。 2. **一致性（Consistency）：** 事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣 了钱，B却没收到。 3. **隔离性（Isolation）：** 同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正 在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。 4. **持久性（Durability）：** 事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。 >[info] 请问 varchar 和 char有什么区别？ char是固定长度的字符类型，分配多少空间，就占用多长空间。Varchar是可变长度的字符类型，内容有多大就占用 多大的空间，能有效节省空间。由于varchar类型是可变的，所以在数据长度改变的时，服务器要进行额外的操作， 所以效率比char类型低。 >[info] Mysql的存储引擎,myisam和innodb的区别 1. InnoDB支持事务，MyISAM不支持 对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在 begin和commit之间，组成一个事务； 2. . InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败； 3. . InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。 但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此主键不应该过大，因为主键太 大，其他索引也都会很大。 而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。 4. InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了 整个表的行数，执行上述语句时只需要读出该变量即可，速度很快； 5. Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高； >[info] 组合索引 (a,b,c)，支持哪些基于索引的查找 **最左原则:** a = 省 b = 市 c = 区 对于联合索引:Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。 ***** 例如索引是key index (a,b,c). 可以支持a 、 a,b 、ac、 a,b,c 4种组合进行查找，但不支持 b,c进行查找 .当最左侧字段 是常量引用时，索引就十分有效。 >[info] 什么情况下应不建或少建索引 (sex 字段) 1. 表记录太少 2. 经常插入、删除、修改的表 3. 数据重复且分布平均的表字段，假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率 大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。 4. 经常和主字段一块查询但主字段索引值比较多的表字段 >[info] 什么是表分区？ 表分区，是指根据一定规则，将数据库中的一张表分解成多个更小的，容易管理的部分。从逻辑上看，只有一张表， 但是底层却是由多个物理分区组成。 **分表：** 指的是通过一定规则，将一张表分解成多张不同的表。比如将用户订单记录根据时间成多个表。 **分表与分区的区别在于：** 分区从逻辑上来讲只有一张表，而分表则是将一张表分解成多张表。 >[info] MySQL支持的分区类型有哪些？ 1. RANGE分区： 这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区 2. LIST分区： 这种模式允许系统通过预定义的列表的值来对数据进行分割。按照List中的值分区，与RANGE的区 别是，range分区的区间范围值是连续的。 3. HASH分区 ：这中模式允许通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应 的数据区域进行分区。例如可以建立一个对表主键进行分区的表。 4. KEY分区 ：上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。 >[info] 关于MVCC的了解 MySQL InnoDB存储引擎，实现的是基于多版本的并发控制协议——MVCC (Multi-Version Concurrency Control) **注：** 与MVCC相对的，是基于锁的并发控制，Lock-Based Concurrency Control **MVCC最大的好处：** 读不加锁，读写不冲突。在读多写少的OLTP应用中，读写不冲突是非常重要的，极大的增加了 系统的并发性能，现阶段几乎所有的RDBMS，都支持了MVCC。 >[info] MySQL优化 1. 业务优化，数据库设计 2. 数据库索引 3. 分表分库(水平分割,垂直分割) 4. 读写分离 5. 存储过程(模块化编程,可以提 高速度) 6. 对MySQL配置优化(配置最大并发数my.ini,调整缓存大小) 7. SQL调优 8. 选择正确的存储引擎 9. 定时清除 不需要的数据,定时进行碎片整理 10. 热点数据采用Nosql等替代品 11. 模糊查询采用es等替代品 12. mysql配置优化 13. 服务器优化（操作系统和硬件） >[info] 大表优化，以及谈谈你对mysql分库分表的见解 当MySQL单表记录数过大时，数据库的CRUD性能会明显下降，一些常见的优化措施如下： 1. **限定数据的范围：** 务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内。 2. **读/写分离：** 经典的数据库拆分方案，主库负责写，从库负责读； 3. **缓存：** 使用MySQL的缓存，另外对重量级、更新少的数据可以考虑使用应用级别的缓存； 4. **垂直分区:** 根据数据库里面数据表的相关性进行拆分。 例如，用户表中既有用户的登录信息又有用户的基本信息，可以将 用户表拆分成两个单独的表，甚至放到单独的库做分库。 简单来说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。 如下图所示，这样来说大家应该 就更容易理解了。 **垂直拆分的优点：** 可以使得行数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表 的结构，易于维护。 **垂直拆分的缺点：** 主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此 外，垂直分区会让事务变得更加复杂；  5. **水平分区:** **保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目 的。 水平拆分可以支撑非常大的数据量。** 水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存 放。举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。 水品拆分可以支持非常大的数据量。需要注意的一点是:分表仅仅是解决了单一表数据过大的问题，但由于表的数据 还是在同一台机器上，其实对于提升MySQL 并发能力没有什么意义，所以 **水品拆分最好分库 。** 水平拆分能够 **支持非常大的数据量存储**，**应用端改造也少**，但 **分片事务难以解决** ，跨界点Join 性能较差，逻辑复杂  >[info] 【索引失效】在哪些情况下会发生针对该列创建了索引但是在查询的时候并没有使用呢? 1. **没有遵循索引最左原则。** 建立几个复合索引字段，最好就用上几个字段。 ***** 2. **最佳左前缀法则** 如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始，可以跳过但是尽量不跳过索引中 间的列。 ***** 3. 不再索引列上做任何操作(计算、函数、（自动or手动）类型转换)，会导致索引失效而转向全表扫描 ***** 4. 存储引擎不能使用索引中范围条件右边的列。（范围之后全失效） 若中间索引列用到了范围（>、<、like等），则后面的索引全失效 ***** 5. Mysql在使用不等于(!=、<>)或like的左模糊的时候无法试用索引会导致全表扫描 ***** 6. IS NULL和IS NOT NULL也无法使用索引 ***** 7. 字符串不加单引号索引失效，因为这里有一个隐式的类型转换操作，更严重会导致行锁变表锁，降低SQL效率 以上情况,MySQL无法使用索引. >[info] mysql的水平拆分和垂直拆分 1. **水平分割：** 例：QQ的登录表。假设QQ的用户有100亿，如果只有一张表，每个用户登录的时候数据库都要从这100亿中查找， 会很慢很慢。如果将这一张表分成100份，每张表有1亿条，就小了很多，比如qq0,qq1,qq1...qq99表。 ***** 用户登录的时候，可以将用户的id%100，那么会得到0-99的数，查询表的时候，将表名qq跟取模的数连接起来，就 构建了表名。比如123456789用户，取模的89，那么就到qq89表查询，查询的时间将会大大缩短。 ***** 2. **垂直分割:** 垂直分割指的是：表的记录并不多，但是字段却很长，表占用空间很大，检索表的时候需要执行大量的IO，严重降低 了性能。这时需要把大的字段拆分到另一个表，并且该表与原表是一对一的关系。 ***** 例如学生答题表tt：有如下字段： Id name 分数 题目 回答 其中题目和回答是比较大的字段，id name 分数比较小。 ***** 如果我们只想查询id为8的学生的分数：select 分数 from tt where id = 8;虽然知识查询分数，但是题目和回答这两个 大字段也是要被扫描的，很消耗性能。但是我们只关心分数，并不想查询题目和回答。这就可以使用垂直分割。我们 可以把题目单独放到一张表中，通过id与tt表建立一对一的关系，同样将回答单独放到一张表中。这样我们插叙tt中 的分数的时候就不会扫描题目和回答了。 >[info] 你用什么软件实现数据库高可用？ MHA >[info] mysql执行一条sql语句的完整过程，sql语句在mysql中的执行过程 MySQL 主要分为 Server 层和引擎层，Server 层主要包括连接器、查询缓存、分析器、优化器、执行器，同时还有 一个日志模块（binlog），这个日志模块所有执行引擎都可以共用,redolog 只有 InnoDB 有。 引擎层是插件式的， 目前主要包括，MyISAM,InnoDB,Memory 等。 SQL 等执行过程分为两类，一类对于查询等过程如下：**权限校验 —》查询缓存—》分析器—》优化器—》权限校验—》执行器—》引擎** 对于更新等语句执行流程如下：**分析器----》 权限校验----》执行器—》引擎—redo log prepare—》binlog—》redo log commit** >[info] 说说mysql主从同步怎么做的？ **原理:** * master提交完事务后，写入binlog * slave连接到master，获取binlog * master创建dump线程，推送binglog到slave * slave启动一个IO线程读取同步过来的master的binlog，记录到relay log中继日志中 * slave再开启一个sql线程读取relay log事件并在slave执行，完成同步 * slave记录自己的binglog >[info] 知道什么是间隙锁吗？(锁了1页数据) 间隙锁是(RR)可重复读级别下才会有的锁，间隙锁可以解决幻读的问题。我们还是以user举例，假设现在user表有几条记录: ``` 当我们执行： begin; select * from user where age=20 for update; begin; insert into user(age) values(10); #成功 insert into user(age) values(11); #失败 insert into user(age) values(20); #失败 insert into user(age) values(21); #失败 insert into user(age) values(30); #失败 只有10可以插入成功，那么因为表的间隙mysql自动帮我们生成了区间(左开右闭) (negative infinity，10],(10,20],(20,30],(30,positive infinity) 由于20存在记录，所以(10,20]，(20,30]区间都被锁定了无法插入、删除。 如果查询21呢？就会根据21定位到(20,30)的区间(都是开区间)。 ``` >[info] 什么是覆盖索引和回表？ **MySQL innodb的主键索引是簇集索引，也就是索引的叶子节点存的是整个单条记录的所有字段值，不是主键索引的 就是非簇集索引，非簇集索引的叶子节点存的是主键字段的值。回表是什么意思？就是你执行一条sql语句，需要从 两个b+索引中去取数据。举个例子：** ***** ``` 表tbl有a,b,c三个字段，其中a是主键，b上建了索引，然后编写sql语句 SELECT * FROM tbl WHERE a=1 这样不会产生回表，因为所有的数据在a的索引树中均能找到 SELECT * FROM tbl WHERE b=1 ``` 这样就会产生回表，因为where条件是b字段，那么会去b的索引树里查找数据，但b的索引里面只有a,b两个字段的 值，没有c，那么这个查询为了取到c字段，就要取出主键a的值，然后去a的索引树去找c字段的数据。查了两个索引 树，这就叫回表。 ***** 索引覆盖就是查这个索引能查到你所需要的所有数据，不需要去另外的数据结构去查。其实就是不用回表。 ***** 怎么避免？不是必须的字段就不要出现在SELECT里面。或者b,c建联合索引。但具体情况要具体分析，索引字段多 了，存储和插入数据时的消耗会更大。这是个平衡问题。 >[info] 如何预防死锁？ * 尽量使用主键更新数据，防止使用非聚簇索引回表时和使用聚簇索引冲突造成死锁。 * 在允许幻读和不可重复度的情况下，尽量使用RC的隔离级别，避免gap lock造成的死锁。 * 避免长事务，将事务拆解 * 设置锁超时等待`innodb_lock_wait_timeout` >[info] 如何预防间隙锁？ * 通过修改数据库的参数innodb_locaks_unsafe_for_binlog来取消间隙锁从而达到避免 * 删除之前,先查询是否存在. 如果并发不大,内部系统,可以不考虑 * 不做物理删除, 而做逻辑删除 或者 就 修改. >[info] 超大分页怎么处理? **超大的分页一般从两个方向上来解决:** * 数据库层面分页,每次获取一定的数据. * 从需求的角度减少这种请求….主要是不做类似的需求(直接跳转到几百万页之后的具体某一页.只允许逐页查看或 者按照给定的路线走,这样可预测,可缓存)以及防止ID泄漏且连续被人恶意攻击.  >[info] 对于关系型数据库而言，索引是相当重要的概念，请回答有关索引的几个问题： 1. 索引的目的是什么？ 快速访问数据表中的特定信息，提高检索速度 创建唯一性索引，保证数据库表中每一行数据 的唯一性。 加速表和表之间的连接,使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间. ***** 2. 索引对数据库系统的负面影响是什么？ 创建索引和维护索引需要耗费时间，这个时间随着数据量的增 加而增加；索引需要占用物理空间，不光是表需要占用数据空间，每个索引也需要占用物理空间；当对表进行增、 删、改、的时候索引也要动态维护，这样就降低了数据的维护速度。 ***** 3. 为数据表建立索引的原则有哪些？ 在最频繁使用的、用以缩小查询范围的字段上建立索引。 ***** 4. 什么情况下不宜建立索引？ 对于查询中很少涉及的列或者重复值比较多的列，不宜建立索引。 对于一些特殊的数据类型，不宜建立索引，比如文本字段（text）等 >[info] 什么是存储过程？用什么来调用？ 存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需创建一次，以后在该程序中就可以调 用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。可以用一个命令对象来调用存储 过程。 >[info] 主键、外键和索引的区别？ **主键、外键和索引的区别:** **定义：** * 主键–唯一标识一条记录，不能有重复的，不允许为空 * 外键–表的外键是另一表的主键, 外键可以有重复的, 可以是空值 * 索引–该字段没有重复值，但可以有一个空值 ***** **作用：** * 主键–用来保证数据完整性 * 外键–用来和其他表建立联系用的 * 索引–是提高查询排序的速度 ***** **个数：** * 主键–主键只能有一个 * 外键–一个表可以有多个外键 * 索引–一个表可以有多个唯一索引 >[info] 谈谈对mysql视图的理解 **什么是基本表？什么是视图？** 基本表是本身独立存在的表，在 SQL 中一个关系就对应一个表。 视图是从一个或几个基本表导出的表。视图本 身不独立存储在数据库中，是一个虚拟表. ***** **试述视图的优点？** (1) 视图能够简化用户的操作 (2) 视图使用户能以多种角度看待同一数据； (3) 视图为数据库提供了一定程度的逻 辑独立性； (4) 视图能够对机密数据提供安全保护。