尚硅谷Java技术之深圳高频面试题：第二章 MySQL相关

尚硅谷Java技术之深圳高频面试题

版本：V1.0

尚硅谷Java技术中心

第二章 MySQL相关

1. MySQL索引原理

MySQL的Innodb和MyISAM引擎索引都是通过B+树来实现的。

具体细节可参考：https://cloud.tencent.com/developer/article/1125452

2. 索引失效的场景

1.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引

2. 对于多列索引，不是使用的第一部分，则不会使用索引

3. like查询以%开头

4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

5. 如果mysql估计使用全表扫描要比使用索引快,则不使用索引

6. 组合索引要遵循最左匹配原则

3. SQL优化

（1）尽量选择较小的列

（2）将where中用的比较频繁的字段建立索引

（3）select子句中避免使用’*’

（4）避免在索引列上使用计算、not in 和<>等操作

（5）当只需要一行数据的时候使用limit 1

（6）保证单表数据不超过200W，适时分割表。针对查询较慢的语句，可以使用explain
来分析该语句具体的执行情况。

（7）避免改变索引列的类型。

（8）选择最有效的表名顺序，from字句中写在最后的表是基础表，将被最先处理，在from子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。

（9）避免在索引列上面进行计算。

（10）尽量缩小子查询的结果

4. 数据库分表操作

可以说使用Mycat或者ShardingSphere等中间件来做，具体怎么做就要结合具体的场景进行分析了。

可以参考：https://database.51cto.com/art/201809/583857.htm

5. 如何进行表设计

1. 数据库设计的实用原则是：

1) 在数据冗余和处理速度之间找到合适的平衡点。原则是相对的，不是绝对的。

2)做表设计，读懂需求就对了。先不要管性能，先实现需求。表设计好了，写SQL的时候再考虑该合并，合并，该拆分，拆分。另外最关键的就是搞清楚一对一还是一对多。

2. 表设计范式：

1)保证每列的原子性，不可分解，意思表达要清楚，不能含糊，高度概括字段的含义，能用一个字段表达清楚的绝不使用第二个字段，可以用两个字段表达清楚的绝不使用一个字段

2) 表及其字段之间的关系,
应尽量满足第三范式。但是，满足第三范式的数据库设计，往往不是最好的设计。为了提高数据库的运行效率，常常需要降低范式标准：适当增加冗余，达到以空间换时间的目的。

例如：表内有商品单价和数量字段，我们设计的时候加上了一个金额的字段，这虽然违背了数据库设计的第三范式，但以查询统计，这能大大提高查询的速度，这就是空间换时间的做法。

3、表关系设计：

1)最好做好静态表和动态表的分离。这里解释一下静态表和动态表的含义，静态表：存储着一些固定不变的资源，比如城市/地区名/国家。动态表：一些频繁修改的表

2)不要有null值，有null值的话，数据库在进行索引的时候查询的时间更久，从而浪费更多的时间！建议可以为null的值转换成not
null default

3)2张表的多对多的表关系，最好设计成3张表，即增加一张中间表，之前的两张表和中间表的关系是一对多的关系。

4)建表的时候，字段长度尽量要比实际业务的字段大3-5个字段左右（考虑到合理性和伸缩性），最好是2的n次方幂值。不能建比实际业务太大的字段长度，这是因为如果字段长度过大，在进行查询的时候索引在B-Tree树上遍历会越耗费时间，从而查询的时间会越久；但是绝对不能建小，否则mysql数据会报错，程序会抛出异常；

5)对于频繁修改的字段（一般是指状态类字段）最好用独立的数字或者单个字母去表示，不用使用汉字或者英文

6)数据库不要存储任何资源文件，比如照片/视频/网站等，可以用文件路径/外链用来代替，这样可以在程序中通过路径，链接等来进行索引

7)关系映射：多对一或者一对多的关系,关联一张表最好通过id去建立关系，而不是去做重复数据，这样做最大的好处就是中间的关系表比较清楚明白。

8)通过单一字段表示该行记录是否可用，通过一个单一字段去控制表是否可用，比如通常起名为isVaild，预制的含义为0为有效，1为无效，这样便于以后我们去剔除数据或者重整数据，使其成为boolean性质的数据
更加便于我们去操控。

9)预留备用字段：在设计一张表的时候应该预制2到3个空白字段，用于以后的扩展，因为你也不是确定这张表以后不会扩展。

4、主键的使用：

1)主键不要与业务逻辑有所关联，最好是毫无意义的一串独立不重复的数字，常见的比如UUID或者将主键设置为Auto_increment;

2) 主键：主键可以是一无物理意义的数字串,
由程序自动加1来实现。也可以是有物理意义的字段名或字段名的组合。不过前者比后者好。当PK是字段名的组合时，建议字段的个数不要太多，多了不但索引占用空间大，而且速度也慢。

3)一个表中组合主键的字段个数越少越好。因为主键的作用，一是建主键索引，二是做为子表的外键，所以组合主键的字段个数少了，不仅节省了运行时间，而且节省了索引存储空间；

5、提高数据库运行效率的办法

在系统硬件和系统软件条件确定的情况下，提高数据库系统的运行效率的办法是：

1) 在数据库物理设计时，降低范式，增加冗余，少用触发器, 多用存储过程。

2)当计算非常复杂、而且记录条数非常巨大时（例如一千万条），复杂计算要先在数据库外面，以文件系统方式用C++语言计算处理完成之后，最后才入库追加到表中去。这是电信计费系统设计的经验。

3)发现某个表的记录太多，例如超过一千万条，则要对该表进行水平分割。水平分割的做法是，以该表主键PK的某个值为界线，将该表的记录水平分割为两个表。若发现某个表的字段太多，例如超过八十个，则垂直分割该表，将原来的一个表分解为两个表。

4) 对数据库管理系统DBMS进行系统优化，即优化各种系统参数，如缓冲区个数。

5) 在使用面向数据的SQL语言进行程序设计时，尽量采取优化算法。

6)如果进行更新表的数据量较大，例如，更新的字段的值，需要重新从子表查询，且是全表查询，

如：UPDATE TABLE_A A SET COLUMN_A = (SELECT COLUMN_A FROM TABLE_A_B B
WHERE A.COLUMN_B = B.COLUMN_B);

其中每更新一行的字段，都会把括号内的sql执行一次，对于数据量大的表，其IO流会非常大，更新时间会较长，建议使用plsql过程语言，或使用merge
into 语句进行更新。

merge into
语句使用的是多块读，优点就是可以批量提交，缺点就是不能并行更新。

总之，要提高数据库的运行效率，必须从数据库系统级优化、数据库设计级优化、程序实现级优化，这三个层次上同时下功夫。

相关文章：
第一章 Java基础
第二章 MySQL相关
第三章 框架相关
第四章 Redis相关
第五章 JVM相关
第六章 消息队列相关
第七章 项目相关