java（数据存储）面试要点3

• MySQL 索引使用的注意事项

1. 不要在列上使用函数和进行运算
2. 尽量避免使用 != 或 not in或 <> 等否定操作符
3. 尽量避免使用 or 来连接条件
4. 多个单列索引并不是最佳选择
5. 复合索引的最左前缀原则
6. 覆盖索引的好处
7. 范围查询对多列查询的影响
8. 索引不会包含有NULL值的列
9. 隐式转换的影响
10. like 语句的索引失效问题

• 说说反模式设计

　　　　即空间换取时间，采取数据冗余的方式避免表之间的关联查询。

• 说说分库与分表设计

　　　　实际上，垂直拆分后的表依然存在单表数据量过大的问题，需要进行水平拆分。因此，实际情况中，水平拆分往往会和垂直拆分结合使用。假设，随着用户数的不断增加，用户表单表存在上千万的数据，这时可以把一张用户表的数据拆成多张用户表来存放。

• 分库与分表带来的分布式困境与应对之策

数据迁移与扩容问题
表关联问题
分页与排序问题
分布式事务问题
分布式全局唯一ID

分库与分表主要用于应对当前互联网常见的两个场景：海量数据和高并发。然而，分库与分表是一把双刃剑，虽然很好的应对海量数据和高并发对数据库的冲击和压力，但是却提高的系统的复杂度和维护成本。

因此，我的建议：需要结合实际需求，不宜过度设计，在项目一开始不采用分库与分表设计，而是随着业务的增长，在无法继续优化的情况下，再考虑分库与分表提高系统的性能。

• 说说 SQL 优化之道

单列索引：单列索引是最基本的索引，它没有任何限制。

复合索引：复合索引是在多个字段上创建的索引。复合索引遵守“最左前缀”原则，即在查询条件中使用了复合索引的第一个字段，索引才会被使用。因此，在复合索引中索引列的顺序至关重要。

唯一索引：唯一索引和单列索引类似，主要的区别在于，唯一索引限制列的值必须唯一，但允许有空值。对于多个字段，唯一索引规定列值的组合必须唯一。

主键索引：主键索引是一种特殊的唯一索引，不允许有空值。此外， CREATE INDEX 不能创建主键索引，需要使用 ALTER TABLE 代替，例如：

alter table tbl_name add primary key(index_col_name);
强制索引
有时，因为使用 MySQL 的优化器机制，原本应该使用索引的优化器，反而选择执行全表扫描或者执行的不是预期的索引。此时，可以通过强制索引的方式引导优化器采取正确的执行计划。

使用强制索引，SQL 语句只使用建立在 index_col_name 上的索引，而不使用其它的索引。

select * from tbl_name force index (index_col_name) …
切记，不要滥用强制索引，因为 MySQL 的优化器会同时评估 I/O 和 CPU 的成本，一般情况下，可以自动分析选择最合适的索引。

如果优化器成本评估错误，因而没有选择最佳方案，最好的方法应该是将合适的索引修改得更好。

如果某个 SQL 语句使用强制索引，需要在系统迭代开发过程中时时维护强制索引，一方面，需要保证使用的强制索引最优，另外一面，需要保证所使用的强制索引不能被误删，不然将导致 SQL 报错。

因此，如果某个 SQL 语句必须要使用强制索引，建议在团队内部开展严格地评审后才可以使用。

全文索引
在一般情况下，模糊查询都是通过 like 的方式进行查询。但是，对于海量数据，这并不是一个好办法，在 like “value%” 可以使用索引，但是对于 like “%value%” 这样的方式，执行全表查询，这在数据量小的表，不存在性能问题，但是对于海量数据，全表扫描是非常可怕的事情,所以 like 进行模糊匹配性能很差。

这种情况下，需要考虑使用全文搜索的方式进行优化。全文搜索在 MySQL 中是一个 FULLTEXT 类型索引。 FULLTEXT 索引在 MySQL 5.6 版本之后支持 InnoDB，而之前的版本只支持 MyISAM 表。

 

为中文内容表提供一个全文索引表，存储全文索引分词信息，两张表根据中文内容表的 ID 进行关联。
将内容进行分词后，用 base64 编码，保存在全文索引表中。
关键的一步，如何分词，分词的命中率问题。很简单，自定义分词库，写一个分词算法将所有的组合进行分词，在内容不多的情况下非常有用。举个例子，“梁桂钊”，可以进行自定义分词：[梁、桂、钊、梁桂、桂钊、梁桂钊]。
事实上，MySQL 全文搜索只是一个临时方案，对于全文搜索场景，更专业的做法是使用全文搜索引擎，例如 ElasticSearch 或 Solr。

 

• MySQL 遇到的死锁问题

在数据库中有两种基本的锁类型：排它锁（Exclusive Locks，即X锁）和共享锁（Share Locks，即S锁）。当数据对象被加上排它锁时，其他的事务不能对它读取和修改。加了共享锁的数据对象可以被其他事务读取，但不能修改。数据库利用这两 种基本的锁类型来对数据库的事务进行并发控制。

死锁的第一种情况

一个用户A 访问表A(锁住了表A),然后又访问表B；另一个用户B 访问表B(锁住了表B)，然后企图访问表A；这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B才能继续，同样用户B要等用户A释放表A才能继续，这就死锁就产生了。

解决方法：

这种死锁比较常见，是由于程序的BUG产生的，除了调整的程序的逻辑没有其它的办法。仔细分析程序的逻辑，对于数据库的多表操作时，尽量按照相同的顺序进 行处理，尽量避免同时锁定两个资源，如操作A和B两张表时，总是按先A后B的顺序处理， 必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源。

死锁的第二种情况

用户A查询一条纪录，然后修改该条纪录；这时用户B修改该条纪录，这时用户A的事务里锁的性质由查询的共享锁企图上升到独占锁，而用户B里的独占锁由于A 有共享锁存在所以必须等A释放掉共享锁，而A由于B的独占锁而无法上升的独占锁也就不可能释放共享锁，于是出现了死锁。这种死锁比较隐蔽，但在稍大点的项 目中经常发生。如在某项目中，页面上的按钮点击后，没有使按钮立刻失效，使得用户会多次快速点击同一按钮，这样同一段代码对数据库同一条记录进行多次操 作，很容易就出现这种死锁的情况。

解决方法：

1、对于按钮等控件，点击后使其立刻失效，不让用户重复点击，避免对同时对同一条记录操作。
2、使用乐观锁进行控制。乐观锁大多是基于数据版本（Version）记录机制实现。即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是 通过为数据库表增加一个“version”字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数 据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。乐观锁机制避免了长事务中的数据 库加锁开销（用户A和用户B操作过程中，都没有对数据库数据加锁），大大提升了大并发量下的系统整体性能表现。Hibernate 在其数据访问引擎中内置了乐观锁实现。需要注意的是，由于乐观锁机制是在我们的系统中实现，来自外部系统的用户更新操作不受我们系统的控制，因此可能会造 成脏数据被更新到数据库中。
3、使用悲观锁进行控制。悲观锁大多数情况下依靠数据库的锁机制实现，如Oracle的Select … for update语句，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。如一个金融系统， 当某个操作员读取用户的数据，并在读出的用户数据的基础上进行修改时（如更改用户账户余额），如果采用悲观锁机制，也就意味着整个操作过程中（从操作员读 出数据、开始修改直至提交修改结果的全过程，甚至还包括操作员中途去煮咖啡的时间），数据库记录始终处于加锁状态，可以想见，如果面对成百上千个并发，这 样的情况将导致灾难性的后果。所以，采用悲观锁进行控制时一定要考虑清楚。

死锁的第三种情况

如果在事务中执行了一条不满足条件的update语句，则执行全表扫描，把行级锁上升为表级锁，多个这样的事务执行后，就很容易产生死锁和阻塞。类似的情 况还有当表中的数据量非常庞大而索引建的过少或不合适的时候，使得经常发生全表扫描，最终应用系统会越来越慢，最终发生阻塞或死锁。

解决方法：

SQL语句中不要使用太复杂的关联多表的查询；使用“执行计划”对SQL语句进行分析，对于有全表扫描的SQL语句，建立相应的索引进行优化。
5．小结
总体上来说，产生内存溢出与锁表都是由于代码写的不好造成的，因此提高代码的质量是最根本的解决办法。有的人认为先把功能实现，有BUG时再在测试阶段进 行修正，这种想法是错误的。正如一件产品的质量是在生产制造的过程中决定的，而不是质量检测时决定的，软件的质量在设计与编码阶段就已经决定了，测试只是 对软件质量的一个验证，因为测试不可能找出软件中所有的BUG。

• 存储引擎的 InnoDB 与 MyISAM

MyISAM 不支持行级锁，换句话说，MyISAM 会对整张表加锁，而不是针对行。同时，MyISAM 不支持事务和外键。MyISAM 可被压缩，存储空间较小，而且 MyISAM 在筛选大量数据时非常快。

InnoDB 是事务型引擎，当事务异常提交时，会被回滚。同时，InnoDB 支持行锁。此外，InnoDB 需要更多存储空间，会在内存中建立其专用的缓冲池用于高速缓冲数据和索引。InnoDB 支持自动奔溃恢复特性。

• 数据库索引的原理

　　　　https://www.cnblogs.com/aspwebchh/p/6652855.html

• 为什么要用 B-tree

　　　　https://blog.csdn.net/bigtree_3721/article/details/73151472

　　　　重要：https://www.kancloud.cn/kancloud/theory-of-mysql-index/41855

• InnoDB索引和MyISAM索引的区别：

      一是主索引的区别，InnoDB的数据文件本身就是索引文件。而MyISAM的索引和数据是分开的。

      二是辅助索引的区别：InnoDB的辅助索引data域存储相应记录主键的值而不是地址。而MyISAM的辅助索引和主索引没有多大区别。

• 聚集索引与非聚集索引的区别

　　　　https://www.cnblogs.com/s-b-b/p/8334593.html

• limit 20000 加载很慢怎么解决

http://uule.iteye.com/blog/2422189

当一个数据库表过于庞大，LIMIT offset, length中的offset值过大，则SQL查询语句会非常缓慢，你需增加order by，并且order by字段需要建立索引。

   如果使用子查询去优化LIMIT的话，则子查询必须是连续的，某种意义来讲，子查询不应该有where条件，where会过滤数据，使数据失去连续性。

   如果你查询的记录比较大，并且数据传输量比较大，比如包含了text类型的field，则可以通过建立子查询。

建立复合索引。

• 选择合适的分布式主键方案
• 选择合适的数据存储方案
• ObjectId 规则
• 聊聊 MongoDB 使用场景
• 倒排索引
• 聊聊 ElasticSearch 使用场景