java【mysql】面试题

一、为什么互联网公司一般选择mysql而不是Oracle？

　　免费、流行、够用。

二、数据库的三范式是什么？什么是反模式？

　　数据库范式是为解决关系数据库中数据冗余、更新异常、插入异常、删除异常问题而引入的。简单的理解，数据库范式可以避免数据冗余，减少数据库的空间，并且减轻维护数据完整性的麻烦。

　　第一范式：

　　强调的是数据库中每个表中的属性，具有原子性，不再进行分解。

　　比如：如果涉及到设计一张活动表，这个表中，包含了属性（活动id，活动名称，活动地址等属性）其中，活动名称符合原子性，但是活动地址还可以进行分割成活动国家，活动省，活动地区，活动市等等。活动地址这个属性就不满足第一范式。

　　第二范式：

　　强调的是数据库中的数据必须有唯一主键，并且这个表中的非主键字段，都必须完全依赖主键。不能存在部分依赖。

　　比如说：版本表（有四个字段：版本编码，版本名称，产品编码，产品名称），其中主键是（版本编码、产品编码），那么这个场景中，数据库设计并不符合第二范式，因为产品名称依赖于产品编码。存在部分依赖，所以，为了使其满足第二范式，可以改造成两个表（）产品表和编码表。

　　第三范式：

　　强调属性冗余性的约束，即非主键列必须直接依赖于主键。

　　比如：订单表：（订单编码、顾客编码、顾客名称），其中主键是（订单编码、顾客编码、顾客名称），其中主键是（订单编号），这个场景中，顾客编码、顾客名称都完全依赖于主键，因此符合第二范式，但是顾客名称依赖于顾客比那吗，从而间接依赖于主键，所以不满足第三范式，可以拆分成两个表：订单表（订单编码、顾客编码）和顾客表（顾客编码、顾客名称），拆分后的数据库设计，就可以完全满足第三范式的要求了。

注意事项：第二范式的侧重点是非主键列是否完全依赖于主键，还是依赖于主键的一部分。第三范式的侧重点是非主键列是直接依赖于主键，还是直接依赖于非主键列。

　　反模式：

　　虽然范式可以避免数据冗余，减少数据库的空间，减轻维护数据的完整性的麻烦。

　　然而，通过数据库范式化设计，将导致数据库业务涉及的表变多，并且可能需要将涉及的业务表进行多表连接查询，这样将导致性能变差，且不利于分库分表。因此，处于性能有限的考量，可能在数据库的结构中需要使用反模式的设计，即空间获取时间，采取数据冗余的方式避免表之间的关联查询。至于数据一致性问题，因为难以满足数据强一致性，一般情况下，使存储数据尽可能达到用户一致，保证系统经过一段较短的时间的自我恢复和修正，数据最终达到一致。

　　需要谨慎使用反模式设计数据库。一般情况下，尽可能使用范式化的数据库设计。因为范式化的数据设计能让产品更加灵活，并且能在数据库层保持数据完整性。

　　有的时候，提升性能最好的方法是在同一张表中保持冗余数据，如果能容许少量的脏数据，创建一张完全独立的汇总表或者缓存表是非常好的方法。举个例子，设计一张“下载次数表“来缓存下载次数信息，可使在海量数据的情况下，提高查询总数信息的速度。

　　另外一个比较典型的场景，处于拓展性考虑，可能会使用BLOB和TEXT类型的列存储JSON结构的数据，这样的好处在于可以在任何时候，将新的属性添加到这个字段中。而不需要更改表的结构，但是，这个设计的缺点也比较明显，就是需要获取整个字段内容进行解码来获取指定的属性，并且无法使用所以、排序、聚和等操作。因此，需要考虑更加复杂的使用场景，更加建议使用MongoDB这样的文档型数据库。

三、MySql的数据类型有那些？

　　数值、日期/时间和字符串类型。

MySql中varchar与char的区别？varchar（50）中的50代表什么含义？

　　1.Varchar与char的区别，char是一种固定长度的类型，varchar则是一种可变长度的类型。

　　2.varchar（50）中50的涵义最多可以存放50个字符，varchar（50）和varchar（200）存储hello所占的空间一样，但是后者在排序时会消耗更多的内存。因为order By clo采用的fixed_length计算col长度（memory引擎也是一样。）

所以，实际场景下，选择合适的varchar长度还是必要的。

int（11）中的11代表了什么含义？

int（11）中的11，不影响字段存储范围，只是影响展示效果。

金额（金钱）相关的数据，选择什么数据类型？

  • 方式一、使用int或者bigint类型，如果需要存储到分的维度，需要*100进行放大。
  • 方式二、使用decimal类型，避免精度丢失。如果使用java语言时，需要使用bigDecimal进行对应

四、一张表，里面有ID自增主键，当insert17条数据之后，删除了第15，16，17条数据，再把MySql重启，再insert一条，这条记录的id是多少？

  • 一般情况下，我们创建的表的类型是innoDB，如果新增一条记录（不重启Mysql情况下）这时的ID是18，但是如果重新启动的话，这条记录就是15，因为InnooDB表只把自增主键的最大值记录到内存中，所以重启数据库或者对表OPTIMIZE操作，都会使最大ID丢失。
  • 但是如果我们使用的表的类型是MYISAM，那么这条记录的ID就是18，因为MYISAM表会把自增主键的最大ID记录到数据文件里面，重启MYISAM后，自增主键的最大ID也不会丢失。

五、表中有大字段X（例如：text类型），且字段X不会经常更新，以读为主，请问您是选择拆成子表，还是继续放在一起？写出您的理由。

  • 拆带来的问题：连接消耗+存储拆分空间。
如果能容忍拆分带来的空间问题，拆的话，最好和经常要查询的表的主键再物理结构上放置在一起（分区）顺序IO，减少连接消耗，最后这是一个文本列再加上一个全文索引来尽量抵消连接消耗。
  • 不拆可能带来的问题：查询性能
如果能容忍不拆分带来的查询性能损失的话，上面的方案再某个极致条件下可能会出现问题，那么不拆就是最好的选择。

实际场景下，例如说商品表数据量比较大的情况下，会将商品描述单独存储到一个表中。即使用拆的方案。

六、MYSQL常用的存储引擎有哪些？

  • InnoDB
  • MyISAM
  • MRG_MYISAM
  • MEMORY
  • CSV
  • ARCHIVE
  • BLACKHOLE
  • PERFORMEANCE_SCHEMA
  • FEDERATED
  • 。。。

如何选择合适的存储引擎？

　　提供几个选择的标准，然后按照标准来进行选择合适的存储引擎，也可以根据常用引擎对比，来选择你使用的存储引擎，使用哪种引擎需要根据需求灵活选择，一个数据库中多个表可以使用不同的引擎满足各种性能与实际需求。使用合适的存储引擎，将会提高整个数据库的性能。

  • 1.是否需要支持事务。
  • 2.对索引和缓存的支持。
  • 3.是否需要使用热备
  • 4.崩溃恢复，能否接受崩溃
  • 5.存储的限制。
  • 6.是否需要外键支持。

　　目前，MySQL默认的存储引擎是InnoDB，并且也是最主流的选择。主要原因是：

  • 1.支持事务
  • 2.支持行级锁和表级锁，能支持更多的并发量。
  • 3.查询不加锁，完全不影响性能。
  • 4.支持崩溃后恢复。

在Mysql5.1以及之前的版本，默认的存储引擎是MYISAM，但是目前已经不再更新，且它有几个比较关键的缺点：

  • 不支持事务
  • 使用表级锁，如果数据量大，一个插入操作锁定表后，其他请求都将阻塞。

 

 

 

Innodb的四大特性？

  • 插入缓存，
  • 二次写
  • 自适应哈希索引
  • 预读

六、为什么select count（*） from table 在InnoDB比MyISAM慢？

对于 SELECT COUNT(*) FROM table 语句，在没有 WHERE 条件的情况下，InnoDB 比 MyISAM 可能会慢很多，尤其在大表的情况下。因为，InnoDB 是去实时统计结果，会全表扫描；而 MyISAM 内部维持了一个计数器，预存了结果，所以直接返回即可。
 
各种不同 MySQL 版本的 Innodb 的改进？
MySQL5.6 下 Innodb 引擎的主要改进：
  • 1. online DDL
  • 2. memcached NoSQL 接口
  • 3. transportable tablespace（ alter table discard/import tablespace） 
  • 4. MySQL 正常关闭时，可以 dump 出 buffffer pool 的（ space， page_no），重启时 reload，加快预热速度
  • 5. 索引和表的统计信息持久化到 mysql.innodb_table_stats 和 mysql.innodb_index_stats，可提供稳定的执行计划
  • 6. Compressed row format 支持压缩表
MySQL5.7 下 Innodb 引擎的主要改进：
  • 1、修改 varchar 字段长度有时可以使用
  • 2、Buffffer pool 支持在线改变大小
  • 3、Buffffer pool 支持导出部分比例
  • 4、支持新建 innodb tablespace，并可以在其中创建多张表
  • 5、磁盘临时表采用 innodb 存储，并且存储在 innodb temp tablespace 里面，以前是 MyISAM 存储
  • 6、透明表空间压缩功能

七、什么是索引？

　　MySQL中存储引擎使用类似的方式进行查询，先去索引中查询对应的值，然后根据匹配的索引找到对应的数据行。

索引有什么好处？

  • 1.提高数据的检索速度，降低数据库IO成本：使用索引的意义就是通过缩小表中需要查询的记录的数目从而加快搜索的速度。
  • 2.降低数据排序的成本，降低CPU消耗：索引之所以查的快，是因为先将数据排好序，若该字段正好需要排序，则正好降低了排序的成本。

索引有什么坏处？

  • 1.占用存储空间：索引实际上也是一张表，记录了主键和索引字段，一般以索引文件的形式存储在磁盘上。
  • 2.降低更新表的速度：表的数据发生了变化，对应的索引也需要一起变更，从而减低的更新速度，否则索引指向的物理数据可能不对，这也是索引失效的原因之一。

索引的使用场景？

  • 对非常小的表，大部分情况下全表扫描效率更高。
  • 对中大型表，索引非常有效。
  • 特大型的表，建立和使用索引的代价随着增长，可以使用分区技术来解决。

索引的类型？

索引，都是实现在存储引擎层的。主要有六种类型：

  • 1、普通索引：最基本的索引，没有任何约束
  • 2、唯一索引：与普通索引类似，但具有唯一性约束。
  • 3、主键索引：特殊的唯一索引，不允许有空值。
  • 4、复合索引：将多个列组合在一起创建索引，可以覆盖多个列
  • 5、外键索引：只有InnoDB类型的表才可以使用外键索引，保证数据的一致性，完整性和实现级联操作。
  • 6、全文索引：MySQL自带的全文索引只能用于InnoDB、MyISAM，并且只能对引文进行全文检索，一般使用全文索引引擎。

Mysql索引的“创建”原则？

  • 1、最适合的索引的列是出现在where子句中的列，或者连接子句中的列。而不是出现在Select关键字后的列。
  • 2、索引列的基数越大，索引效果越好。
  • 3、根据情况创建复合索引，复合索引可以提高查询效率。
  • 4、避免创建过多的索引，索引会额外占用磁盘空间，降低写操作效率。
  • 5、主键尽可能选择较短的数据类型，可以有效减少索引的磁盘占用提高查询效率。
  • 6、对字符串进行索引，应该定制一个前缀长度，可以节省大量的索引空间。

MySQL索引的“使用”注意事项？

  • 1.应尽量避免在where子句中使用！=或者<>操作符，否则将引擎放弃使用索引而进行全表扫描。优化器将无法通过索引来确定将要命中的行数，因此需要搜索该表的所有行。
  • 2.应尽量避免在where子句中使用or连接，否则将导致引擎放弃使用索引而进行全表扫描，如： SELECT id FROM t WHERE num = 10 OR num = 20 。
  • 3.应尽量避免在where子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。
  • 4.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。
  • 5.不要在where子句中的==左边进行函数、算数运算或者其他表达式运算，否则系统将可能无法正确使用索引。
  • 6.复合索引怎讯前缀原则。
  • 7.如果mysql评估使用索引比全表扫描更慢，会放弃使用索引。如果此时想要索引，可以在语句中添加强制索引。
  • 8.列类型是字符串类型，查询时一定要给值加引号，否则索引将失效。
  • 9.like查询，%不能在前，因为无法使用索引，如果需要模糊匹配，可以使用全文索引。

以下三条SQL如何创建索引，只建一条怎么建？

WHERE a = 1 AND b = 1 
WHERE b = 1 
WHERE b = 1 ORDER BY time DESC
  • 以顺序 b , a, time 建立复合索引， CREATE INDEX table1_b_a_time ON index_test01(b, a, time) 。
  • 对于第一条 SQL ，因为最新 MySQL 版本会优化 WHERE 子句后面的列顺序，以匹配复合索引顺序。

想知道一个查询用到了那个索引，如何查看？

　　EXPLAIN显示了MYSQL如何使用索引来处理SELECT语句以及连接表，可以帮助选择更好的索引和写出更优化的查询语句。

　　使用方法，在SELECT语句前加上EXPLAIN就可以了。

MYSQL索引的原理？

-----

　　MySQl有那些索引？

　　B-Tree索引

　　Hash索引

什么是B-Tree索引？

　　B-Tree是为磁盘等外存储设备设计的一种平衡查找树，因此在讲B-Tree之前先了解一卡磁盘的相关知识。

  • 系统从磁盘读取数据到内存时以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。
  • InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。Innodb存储引擎中默认每个页的大小为16kb，可通过参数innodb_page_size将页的大小设置为4K、8K、16K，在Mysql中可通过如下命令查看页的大小：
Mysql> show variables list 'innodb_page_size'
  • 而系统一个磁盘块的存储空间往往没有那么大，因此innodb每次申请磁盘空间时都会是若干个地址连续磁盘块来达到页的大小16kb。Innodb在把磁盘数据读入到磁盘时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘IO次数，提高查询效率。

　　B-Tree结构的数据可以让系统高效的找到数据所在的磁盘块，为了描述B-Tree，首先定一条记录为一个二元组【key，data】，key为记录的键值，对应表中的主键值，data为一行记录中除主键外的数据。对于不同的记录，key值互不相同。

　　一棵m阶的B-Tree有如下特性：

  • 1.每个节点最多有m个孩子。
    • 除了根节点和叶子节点外，其他每个节点至少有ceil（m/2）个孩子。
    • 若根节点不是叶子节点，则至少有2个孩子。
  • 2.所有叶子节点都在同一层，且不包含其他关键字信息。
  • 3.每个非叶子节点包含n个关键字信息（p0，p1，p2，pn）

B-Tree中的每个节点根据实际情况可以包含大量的关键字信息和分支，如下图所示为一个3阶的B-Tree：

 

 

  • 每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的key和三个指向子树根节点的point，point存储的是子节点所在拍磁盘块的地址，两个key划分成的三个范围域，对应三个point指向的子树的数据的范围域。
  • 以根节点为例。key为17和35，P1指针指向的子树的范围小雨17，P2指针指向的子树的数据范围为【17-35】，P3指针指向的子树的数据范围大于35.

模拟查找key为29的过程：

  • 1.根据根节点找到磁盘块1，读入内存，【磁盘的I/O次数为1】
  • 2.比较key29在区间（17，35），找到磁盘块1的指针P2
  • 3.根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第二次】
  • 4.比较key29在区间（26，30），找到磁盘块3的指针P2
  • 5.根据P2指针找到磁盘块8，读入内存，【磁盘I/O次数第三次】
  • 6.在磁盘块8中的key列表中找到key29。

分析上面过程，发现需要3次磁盘i/o操作，和3次内存查找操作。由于内存中的key是一个有序表结构，可以二分法查找提高效率。而3次磁盘i/o操作是影响整个B-Tree查找效率的决定因素。B-Tree相对于AVLTree缩减了节点个数，使每次磁盘I/O取到内存的数据都发挥了做用，从而提高了查询效率。

什么是B+Tree索引？

 　　B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。

　　从上一节的B-Tree结构图中可以看到，每个节点中不仅包含数据的key值，还有data值，而每一个页的存储空间都是有限的。如果data数据较大时将会导致每个节点（即每一个页）能存储的key的数量很小，当存储的数据量和大时，同样会导致B—Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的页子节点上，而非页子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

B+Tree相对于B-Tree有几点不同：

  • 非叶子节点只存储键值信息
  • 所有叶子节点之间都有一个链指针
  • 数据记录都存放在叶子节点中。

　　将上一节中的B-Tree优化，由于B+Tree的非叶子节点只存储键值信息，假设每一个磁盘块都能存储4个键值及指针信息，则变成B+Tree后其结构如下图所示：

 

 

 

  • 通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种就是从根节点开始，进行随机查找。

可能上面例子中只有22条数据记录，看不出B+Tree的优点，下面做一个推算：

  • Innodb存储引擎中页的大小为16kb，一般表的主键类型为Int（占用4个字节）或者BIGINT（占用8个字节），指针类型也一般为4个字节或者8个字节，也就是说每一个页（B+Tree中的一个节点）中国大概存储16kb/（8B+8B）=1k个键值（因为时估值，为方便计算，这里的k取值为1000个）也就是说一个深度为3的B+Tree索引可以维护1000*1000*1000=10亿条记录。
  • 实际情况中每一个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2-4层，MySql的Innodb存储引擎子设计时是将根节点常驻内存的，也就是说查找一个键值的行记录时，最多需要1-3次磁盘I/操作。

B-Tree有哪些索引类型？

　　在B+Tree中，根据叶子节点的内容，索引类型分为主键索引和非主键索引。

  • 主键索引的叶子节点存的数据是整行数据（即具体数据）。在InnoDB里，非主键索引也被称为聚集索引。
  • 非主键索引的叶子节点存的数据是整行数据的主键，键值是索引。在InnoDB里，非主键索引也被称为辅助索引。

　　辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚集索引键，即主键。当通过辅助索引来查询数据时，需要进行两步：

  • 首先，InnoDB存储引擎会遍历辅助索引找到主键。
  • 然后，再通过主键在聚集索引中找到完整的行记录数据。

另外，InnoDB通过主键聚簇数据，如果没有定义主键，会选择唯一的非空索引代替，如果没有这样的索引，会隐式定义一个主键作为聚簇索引。

辅助索引如果是相同的索引怎么进行存储？因为最终存储到B+Tree非子节点中时，他们对应的主键ID是不同的。所以妥妥的。

 

 

聚簇索引的注意点有哪些？

聚簇索引表最大限度提高了I/O密集型应用的性能，但它也有一下几个限制：

  • 1.插入速度严重依赖插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键。
  • 2.更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
  • 3.二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。
  • 4.主键ID建议使用整型，因为，每个主键索引的B+Tree节点的键值可以存储更多主键ID，每个非主键索引的B+Tree节点的数据可以存储更多主键ID。

什么是索引的最左匹配特性？

　　当B+Tree的数据项是复合的数据结构，比如索引（name，age，sex）的时候，B+Tree是按照从左到右的顺序来建立搜索树的。

  • 比如当（张三，20，F）这样的数据来检索的时候，B+Tree会优先比较name来确定下一步的搜索方向，如果name相同再依次比较age和sex，最后得倒检索的数据。
  • 但当（20，F）这样的没有name的数据来的时候，B+Tree就不知道下一步该查那个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。
  • 比如当（张三，F）这样的数据来检索时，B+Tree可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了。
  • 这个是非常重要的性质，即索引的最左匹配特性。

MyISAM索引实现？

　　MyISAM索引的实现，和InnoDB索引的实现是一样使用B+Tree，差别在于MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。

1）主键索引：

　　MYISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的数据记录的地址。下图MyISAM主键索引的原理图：

 

 

 

  • 这里设表一共有三列，假设我们以Col1为主键，上图是一个MYISAM表的主键索引（Primary Key）示意。可以看出MYISAM的索引文件仅仅保存数据记录的地址。

2）辅助索引：

　　在MyISAM中，主索引和辅助索引在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

 

 

 

  • 同样是一颗B+Tree，data域保存数据记录的地址，因此，MyISAM中检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的key存在，则取出其data域的值为地址，读取相应的数据记录。

MyISAM的索引方式也叫做非聚集的，只索引这么称呼是为了InnoDB的聚集索引区分。

MyISAM索引与InnoDB索引的区别？

  • InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。
  • InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。
  • InnoDB的主键索引的叶子节点存储着行数据地址，需要再寻找一个才能得到数据。
  • InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。

请说说Mysql的四种事务隔离级别？

　　事务就是对一系列的数据库操作（比如插入多条数据）进行统一的提交或者回滚操作，如果插入成功，那么一起成功，如果中间有一条出现异常，那么回滚之前的所有操作。

　　这样可以防止出现脏数据，防止数据库数据出现问题。

事务的特性指的是？

指的时ACID，如下图所示：

 

 

  • 1.原子性：Atomicity，一个事务（transaction）中的所有操作，或者全部完成，或者全部步完成，不会结束再中间某个环节。事务在执行过程中发生错误，会被回复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样，即，事务不可分割，不可约简。
  • 一致性Consistency：在事务开始之前和事务结束之后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设约束、触发器、级联回滚等。
  • 隔离性Isolation：数据库允许多个兵法事务同时对数据进行读写和修改能力，隔离性可以防止多个事务并发之行时由于交叉之行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Sericlizable）
  • 持久性Durablity：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

事务的并发问题？

实际场景下，事务并不是串行的，所以会带来如下三个问题：

  • 脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据就是脏数据。
  • 不可重复读：事务A多次读取同一数据，事务B在事务A多次读取的过程中，对数据做了更新并提交，导致事务A多次读取同一数据时，结果不一致。
  • 幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫做幻读。

不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增和删除。解决不可重复读的问题只需要锁住满足条件的行，解决幻读需要锁表。

Mysql事务隔离级别会产生的并发问题？

事务定义了四种事务隔离级别，不同数据库在实现时，产生的并发问题时不同的。

　　不同的隔离级别有不同的现象，并有不同的锁定/并发机制，隔离级别越高，数据库的并发性能就越差。

  • READ UNCOMMITTED（未提交读）：事务中的修改，即使没有提交，对其他事务也都是可见的。（会导致脏读）
  • READ COMMITTED（提交读）：事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。（会导致不可重复读）。
  • REPEATABLE READ（可重复读）：一个事务按相同的查询条件读取以前检索过的数据，其他事务插入了满足其查询条件的新数据，产生换行（会导致幻读）
  • SERIALIZABLE（可串行化）：强制事务串行化之行。
MySQL InnoDB 采用 MVCC 来支持高并发，实现结果如下表所示：

  • MySQL默认的事务隔离级别为可重复读（repeatable-read）
  • 有些资料说可重复读解决了幻读，实际是存在的，可以通过 SELECT xxx FROM t WHERE id = ? FOR UPDATE 的方式，获得到悲观锁，禁止其它事务操作对应的数据，从而解决幻读问题。

请说说MySQL的锁机制？

表锁时日常开发中的常见问题，当多个查询同一时刻进行数据修改时，就会产生并发控制的问题。MySQL的共享锁和排他锁，就是读锁和写锁。

  • 共享锁：不堵塞，多个用户可以同时读一个资源，互不干扰。
  • 排他锁：一个写锁会阻塞其他的读锁和写锁，这样可以只允许一个用户进行写入，防止其他用户读取正在写入的资源。

锁的粒度？

  • 表锁：系统开销最小，会锁定整个表，MyISAM使用表锁。
  • 行锁：最大程度的支持并发处理，但是也带来了最大的锁开销，InnoDB使用行锁。

什么是悲观锁？什么是乐观锁？

1）悲观锁

　　它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改保持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性），否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据。

在悲观锁的情况下，为了保证事务的隔离性，就需要一致性锁定读，读取数据时给加锁，其他事务无法修改这些数据。修改删除数据时也要加锁，其他事务无法读取这些数据。

2）乐观锁

　　相对于悲观锁而言，乐观锁机制才去了更加宽松的加锁机制。悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度上的独占性，但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。

　　而乐观锁在一定程度上解决了这个问题，乐观锁，大多是基于数据版本（Version）记录机制实现的，

何谓数据版本？

即为数据库表的版本解决方案中，一般是通过为数据库表增加一个“version”字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一，此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

什么是死锁？

　　多数情况下，可以认为如果一个资源被锁定，它总会在以后某个时间被释放。而死锁发生在多个进程访问同一数据库时，其中每个进程都无法继续下去，简单的说，进程A等待进程B释放了它的资源，B又等待A释放它的资源，这就形成了互相等待，形成了死锁。

虽然进程在运行时，可能发生死锁，但是死锁的发生也必须具备一定的条件，死锁的发生必须具备以下四个必要条件：

  • 互斥条件：指进程对所分配到的资源进行排他性使用，即在一段时间内资源只由一个进程占用，如果此时还有其他进程请求资源，则请求者只能等待，直至占有资源的进程用完释放。
  • 请求和保持条件：指进程已经保持了至少一个资源，但又提出了新的资源请求，而该资源已被其他进程占有，此时请求进程阻塞，但又对自己已获得的其他资源保持不放。
  • 不剥夺条件：指进程已获得的资源，在未使用完之前，不能被剥夺，只能在使用完时由自己释放。
  • 环路等待条件：指在发生死锁时，必然存在一个进程-资源的环形脸，即进程集合（p0,p1,p2,p3....pn）中p0正在等待一个p1占用的资源，p1正在等待p2占用的资源，...pn正在等待已被p0占用的资源。

下列方法有助于最大限度地降低死锁：

  • 设置获取锁的超时时间。（通过超时时间，至少保证最差最差的情况下，有个口子可以退出去）
  • 按同一顺序访问对象
  • 避免事务中的用户交互
  • 保持事务间断并在同一批处理中。
  • 使用低隔离级别
  • 使用绑定连接

Mysql中InnoDB引擎的行锁时通过加在什么上完成（或实现的）？为什么时这样子的？

InnoDB时基于索引来完成行锁的，例如Select * from table_with_index where id =1 for update.

for update可以根据条件来完成行锁锁定，并且id是有索引键的列，如果id不是索引键那么Innodb将完成表锁，并发将无从谈起。

关于熟悉MySQL的锁机制？

  • gap锁
  • next-key锁

Innodb的行锁的怎么实现的？

InnoDB的锁策略为next-key锁，即record lock+gap lock，时通过在index上加lock实现的。

  • 如果index 为unique index，则降级为record lock行锁
  • 如果时普通index，则为next-key lock
  • 如果没有index，则直接锁住全表，即表锁。

MyISAM的表锁时怎么实现的？

MyISAM直接使用表锁。

MySQL查询之行顺序？

SELECT
DISTINCT
FROM
JOIN
ON
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT

什么是MVCC？

多版本并发控制（MVCC），是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每一个修改保存一个版本，版本与事务时间戳相关联，读操作只读事务开始前的数据库的快照。这样在读操作不用阻塞写操作，写操作不用阻塞读操作的同时，避免了脏读和不可重复读。

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

　　当CPU飙升到500%的话，先用操作系统命令top命令观察是不是mysqlId占用导致的，如果不是，找出占用高的进程，并进行相关的处理。

如果此时是IO压力比较大的话，可以使用iostat命令，定位是那个进程占用了磁盘io

如果是mysqld造成的，使用show processlist命令，看着里面跑的Session情况，是不是又消耗资源的SQL在运行，找出消耗高的SQL，看着之行计划是否准确，index是否缺失，或者是数据量太大造成的，一般来说，肯定要kill掉线程，同时观察cpu使用率是否下降，等进行相应的调整，（比方说加索引，改sql，改内存参数等）之后，再重新跑这些sql。

也可以查看MySQL慢查询日志，看是否有慢SQL

　　也有可能是每个SQL消息资源并不多，但是突然间有大量的Session连接进来并导致CPU飙升，这种情况就需要根应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数

在Mysql服务器运行缓慢的情况下输入什么命令能缓解服务器压力？

1）检查系统的状态

　　通常操作系统的一些工具检查系统的状态，比如CPU，内存，磁盘的利用率，根据经验或者系统正常时的状态相比怼，有时系统表面上看起来空闲，这也可能不是一个正常状态，因为CPU可能正等待IO的完成，除此之外，还应该注意哪些占用系统资源（CPU、内存）的进程。

  • 使用sar来检查操作系统是否存在IO问题
  • 使用cmstat监控内存cpu资源。
  • 磁盘io问题，处理方式：做raid10提高性能。
  • 网络问题，telnet 一下mysql对外开放的端口，如果不通的话，看着防火墙是否正确设置了。另外，看着MySQL是不是开启了skip-networking的选项，如果开启请关闭。

2）检查Mysql参数

  • max_connect_errors
  • connect_timeout
  • skip-name-resolve
     
  • slave-net-timeout=seconds
     
  • master-connect-retry

3）检查MySQL相关状态值

  • 关注连接数
  • 关注下系统锁情况
  • 关注慢查询（slow query）日志

 

posted @ 2020-10-07 15:49  King-DA  阅读(801)  评论(1)  编辑  收藏  举报