互联网科技Java编程it互联网

MySQL面试知识整理，Java程序员升职涨薪必备，附赠架构师成

2019-10-30  本文已影响0人  Java高级架构狮

一、数据库引擎

MySQL InnoDB MyISAM
事物支持 是 否
外键支持 是 否
锁 行级 表级
全文索引 否 是【只支持英文】
崩溃安全恢复支持 是 否

选择：MyISAM相对简单，所以在效率上要优于InnoDB。如果系统插入和查询操作多，不需要事务和外键，选择MyISAM，如果需要频繁的更新、删除操作，或者需要事务、外键、行级锁的时候，选择InnoDB。

二、事务

1、事务特性

2、事物的并发问题

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

3、事物的隔离级别

事务隔离级别 读数据一致性 脏读 不可重复读 幻读
读未提交（read-uncommitted） 最低级别 是 是 是
读已提交（read-committed） 语句级 否 是 是
可重复读（repeatable-read） 事务级 否 否 是
串行化（serializable） 最高级别，事务级 否 否 否

4、总结

三、锁

​1、行锁

①. 优势：

②. 劣势：

③. 加锁方式：

自动加锁。

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及的数据集加排他锁；对于普通SELECT语句，InnoDB不会加任何锁；当然我们也可以显示的加锁： 加共享锁：select * from tableName where ... lock in share mode 加排他锁：select * from tableName where ... for update

④. 间隙锁【Next-Key锁】：

当我们用范围条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做"间隙(GAP)"。InnoDB也会对这个"间隙"加锁，这种锁机制就是所谓的间隙锁(Next-Key锁)。

危害（坑）：若执行的条件范围过大，则InnoDB会将整个范围内所有的索引键值全部锁定，很容易对性能造成影响。

Transaction-A
mysql> update innodb_lock set k=66 where id >=6;
Query OK, 1 row affected (0.63 sec)
mysql> commit;

Transaction-B
mysql> insert into innodb_lock (id,k,v) values(7,'7','7000');
Query OK, 1 row affected (18.99 sec)

⑤. 排他锁：

排他锁，也称写锁，独占锁，当前写操作没有完成前，它会阻断其他写锁和读锁。

# Transaction_A
mysql> set autocommit=0;
mysql> select * from innodb_lock where id=4 for update;
+----+------+------+
| id | k    | v    |
+----+------+------+
|  4 | 4    | 4000 |
+----+------+------+
1 row in set (0.00 sec)

mysql> update innodb_lock set v='4001' where id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.04 sec)
# Transaction_B
mysql> select * from innodb_lock where id=4 for update;
+----+------+------+
| id | k    | v    |
+----+------+------+
|  4 | 4    | 4001 |
+----+------+------+
1 row in set (9.53 sec)

⑥. 共享锁：

共享锁，也称读锁，多用于判断数据是否存在，多个读操作可以同时进行而不会互相影响。如果事务对读锁进行修改操作，很可能会造成死锁。如下图所示。

# Transaction_A
mysql> set autocommit=0;
mysql> select * from innodb_lock where id=4 lock in share mode;
+----+------+------+
| id | k    | v    |
+----+------+------+
|  4 | 4    | 4001 |
+----+------+------+
1 row in set (0.00 sec)

mysql> update innodb_lock set v='4002' where id=4;
Query OK, 1 row affected (31.29 sec)
Rows matched: 1  Changed: 1  Warnings: 0
# Transaction_B
mysql> set autocommit=0;
mysql> select * from innodb_lock where id=4 lock in share mode;
+----+------+------+
| id | k    | v    |
+----+------+------+
|  4 | 4    | 4001 |
+----+------+------+
1 row in set (0.00 sec)

mysql> update innodb_lock set v='4002' where id=4;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

⑦. 分析行锁定：

通过检查InnoDB_row_lock 状态变量分析系统上的行锁的争夺情况，命令：

show status like 'innodb_row_lock%'

mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 0     |
| Innodb_row_lock_time_avg      | 0     |
| Innodb_row_lock_time_max      | 0     |
| Innodb_row_lock_waits         | 0     |
+-------------------------------+-------+

innodb_row_lock_current_waits: 当前正在等待锁定的数量 innodb_row_lock_time: 从系统启动到现在锁定总时间长度；【非常重要的参数】 innodb_row_lock_time_avg: 每次等待所花平均时间；【非常重要的参数】 innodb_row_lock_time_max: 从系统启动到现在等待最长的一次所花的时间； innodb_row_lock_waits: 系统启动后到现在总共等待的次数；非常重要的参数，直接决定优化的方向和策略。

⑧. 行锁优化：

2、表锁

①. 优势：

②. 劣势：

③. 加锁方式：

自动加锁。

查询操作（SELECT），会自动给涉及的所有表加读锁，更新操作（UPDATE、DELETE、INSERT），会自动给涉及的表加写锁。也可以显示加锁：

共享读锁：lock table tableName read; ​ 独占写锁：lock table tableName write; ​ 批量解锁：unlock tables;

④. 共享读锁：

对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读操作，但会阻塞对同一表的写操作。只有当读锁释放后，才能执行其他进程的写操作。

Transaction-A
mysql> lock table myisam_lock read;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from myisam_lock;
9 rows in set (0.00 sec)

mysql> select * from innodb_lock;
ERROR 1100 (HY000): Table 'innodb_lock' was not locked with LOCK TABLES

mysql> update myisam_lock set v='1001' where k='1';
ERROR 1099 (HY000): Table 'myisam_lock' was locked with a READ lock and can't be updated

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
Transaction-B
mysql> select * from myisam_lock;
9 rows in set (0.00 sec)

mysql> select * from innodb_lock;
8 rows in set (0.01 sec)

mysql> update myisam_lock set v='1001' where k='1';
Query OK, 1 row affected (18.67 sec)

⑤. 独占写锁：

对MyISAM表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其他进程的读写操作。

Transaction-A
mysql> set autocommit=0;
Query OK, 0 rows affected (0.05 sec)

mysql> lock table myisam_lock write;
Query OK, 0 rows affected (0.03 sec)

mysql> update myisam_lock set v='2001' where k='2';
Query OK, 1 row affected (0.00 sec)

mysql> select * from myisam_lock;
9 rows in set (0.00 sec)

mysql> update innodb_lock set v='1001' where k='1';
ERROR 1100 (HY000): Table 'innodb_lock' was not locked with LOCK TABLES

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
Transaction-B
mysql> select * from myisam_lock;
9 rows in set (42.83 sec)

小结：表锁，读锁会阻塞写，不会阻塞读。而写锁则会把读、写都阻塞。

⑥. 查看加锁情况:

show open tables; 1表示加锁，0表示未加锁。

mysql> show open tables where in_use > 0;
+----------+-------------+--------+-------------+
| Database | Table       | In_use | Name_locked |
+----------+-------------+--------+-------------+
| lock     | myisam_lock |      1 |           0 |
+----------+-------------+--------+-------------+

⑦. 分析表锁定：

通过检查table_locks_waited 和 table_locks_immediate 状态变量分析系统上的表锁定，命令：

show status like 'table_locks%';

mysql> show status like 'table_locks%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 104   |
| Table_locks_waited         | 0     |
+----------------------------+-------+

此外，MyISAM的读写锁调度是写优先，这也是MyISAM不适合做写为主的存储引擎。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永久阻塞。

3、什么情况下用表锁？

InnoDB默认采用行锁，在未使用索引字段查询时升级为表锁。MySQL这样设计并不是给你挖坑。它有自己的设计目的。 ​ 即便你在条件中使用了索引字段，MySQL会根据自身的执行计划，考虑是否使用索引(所以explain命令中会有possible_key 和 key)。如果MySQL认为全表扫描效率更高，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。

4、总结

四、索引

1、原理

我们拿出一本新华字典，它的目录实际上就是一种索引：非聚集索引。我们可以通过目录迅速定位我们要查的字。而字典的内容部分一般都是按照拼音排序的，这实际上又是一种索引：聚集索引。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

2、底层实现【数据结构】

①、mysql主要使用B+树来构建索引，为什么不用二叉树和红黑树？

在InnoDB里，每个页默认16KB，假设索引的是8B的long型数据，每个key后有个页号4B，还有6B的其他数据（参考《MySQL技术内幕：InnoDB存储引擎》P193的页面数据），那么每个页的扇出系数为16KB/(8B+4B+6B)≈1000，即每个页可以索引1000个key。在高度h=3时，s=1000^3=10亿！！也就是说，InnoDB通过三次索引页的I/O，即可索引10亿的key。通常来说，索引树的高度在2~4。

②. B+Tree与B-Tree的区别

M阶B-Tree

定义：

特性：

M阶B+Tree

定义：

特性：

③. B+Tree与B-Tree的区别

④. 为什么mysql的索引使用B+树而不是B树呢？

3、索引优点

4、索引缺点

五、mysql性能分析

1、MySQL 自身瓶颈

MySQL自身常见的性能问题有磁盘空间不足，磁盘I/O太大，服务器硬件性能低。

2、explain 分析sql语句

使用explain关键字可以模拟优化器执行sql查询语句，从而得知MySQL 是如何处理sql语句。

①. id
select 查询的序列号，包含一组可以重复的数字，表示查询中sql语句的执行顺序。一般有三种情况： 第一种：id全部相同，sql的执行顺序是由上至下； 第二种：id全部不同，sql的执行顺序是根据id大的优先执行（如果是子查询，id的序号会递增）； 第三种：id既存在相同，又存在不同的。先根据id大的优先执行，再根据相同id从上至下的执行。

②. select_type

select 查询的类型，主要是用于区别普通查询，联合查询，嵌套的复杂查询：

subquery和union 还可以被标记为dependent和uncacheable。 dependent意味着select依赖于外层查询中发现的数据。 uncacheable意味着select中的某些特性阻止结果被缓存于一个item_cache中。

③. table

查询结果来自于哪个表。

④. partitions

表所使用的分区，如果要统计十年公司订单的金额，可以把数据分为十个区，每一年代表一个区。这样可以大大的提高查询效率。

⑤. type

这是一个非常重要的参数，连接类型，常见的有：all , index , range , ref , eq_ref , const , system , null 八个级别。

性能从最优到最差的排序：null > system > const > eq_ref > ref > range > index > all

对java程序员来说，若保证查询至少达到range级别或者最好能达到ref则算是一个优秀而又负责的程序员。

explain select * from t1 where name='yayun';

explain select t1.name from t1, t2 where t1.id=t2.id;

explain select * from t1 where id = 1

explain select * from t1 where id = (select min(id) from t2);

⑥. possible_keys

显示查询语句可能用到的索引(即查询涉及字段中存在索引的字段，可能为一个、多个或为null)，不一定被查询实际使用，仅供参考使用。

⑦. key

显示查询语句实际使用的索引字段。若为null，则表示没有使用索引。

⑧. key_len

显示索引中使用的字节数，可通过key_len计算查询中使用的索引长度。

​ 在不损失精确性的情况下索引长度越短越好。key_len 显示的值为索引字段的最可能长度，并非实际使用长度，即key_len是根据表定义计算而得，并不是通过表内检索出的。

⑨. ref

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。即显示使用哪个列或常数与key一起从表中选择行。

**⑩. rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，值越大越不好。

即根据查询语句及索引选用情况，大致估算出要得到查询结果，所需要在表中读取的行数。

⑪. filtered

一个百分比的值，和rows 列的值一起使用，可以估计出查询执行计划(QEP)中的前一个表的结果集，从而确定join操作的循环次数。小表驱动大表，减轻连接的次数。

⑫. extra

包含不适合在其他列中显示但又十分重要的额外信息。

3、explain总结

通过explain的参数介绍，我们可以得知:

六、数据库性能优化

1、优化思路

可从以下几个方面对数据库性能进行优化：

2、数据库与索引设计

①. 数据库设计

②. 索引设计

③. 总结

就一句话：使用合适的数据类型，选择合适的索引：

3、sql优化

4、分区、分库、分表

①. 分区

把一张表的数据分成N个区块，在逻辑上看最终只是一张表，但底层是由N个物理区块组成的，通过将不同数据按一定规则放到不同的区块中提升表的查询效率。

②. 分表

③. 分库

面对高并发的读写访问，当数据库无法承载写操作压力时，不管如何扩展slave服务器，此时都没有意义了。因此需对数据库进行拆分，从而提高数据库写入能力，这就是分库。

④. 问题

七、缓存

对于很多的数据库系统都能够缓存执行计划，对于完全相同的sql, 可以使用已经已经存在的执行计划，从而跳过解析和生成执行计划的过程。MYSQL提供了更为高级的查询结果缓存功能，对于完全相同的SQL (字符串完全相同且大小写敏感) 可以执行返回查询结果。

MySQL缓存机制简单的说就是缓存sql文本及查询结果，如果运行相同的sql，服务器直接从缓存中取到结果，而不需要再去解析和执行sql。如果表更改了，那么使用这个表的所有缓冲查询将不再有效，查询缓存值的相关条目被清空。更改指的是表中任何数据或是结构的改变，包括INSERT、UPDATE、 DELETE、TRUNCATE（截断）、ALTER TABLE、DROP TABLE或DROP DATABASE等，也包括那些映射到改变了的表的使用MERGE表的查询。显然，这对于频繁更新的表，查询缓存是不适合的，而对于一些不常改变数据且有 大量相同sql查询的表，查询缓存会节约很大的性能。

八、面试真题

问：有个表特别大，字段是姓名、年龄、班级，如果调用select * from table where name = xxx and age = xxx该如何通过建立索引的方式优化查询速度？

答：由于mysql查询每次只能使用一个索引，如果在name、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(name, age)的复合索引，那么其实相当于创建了(name)、(name, age)两个索引，这被称为最佳左前缀特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。其次还要考虑该列的数据离散程度，如果有很多不同的值的话建议放在左边，name的离散程度也大于age。

问：max(xxx)如何用索引优化？

答：在xxx列上建立索引，因为索引是B+树顺序排列的，锁在下次查询的时候就会使用索引来查询到最大的值是哪个。

问：如何对分页进行优化？

答：SELECT * FROM big_table order by xx LIMIT 1000000,20，这条语句会查询出1000020条的所有数据然后丢弃掉前1000000条，为了避免全表扫描的操作，在order by的列上加索引就能通过扫描索引来查询。但是这条语句会查询还是会扫描1000020条，还能改进成select id from big_table where id >= 1000000 order by xx LIMIT 0,20，用ID作为过滤条件将不需要查询的数据直接去除。

九、读者福利

针对Java程序员，笔者最近整理了一份完整的一线互联网大厂面试真题，包含了Kafka、Mysql、Tomcat、Docker、Spring、MyBatis、Nginx、Netty、Dubbo、Redis、Netty、Spring cloud、分布式、高并发、性能调优、微服务等架构技术。

需要的朋友请点击下方传送门免费领取

传送门

以下是部分面试题截图

十、架构师成长路线图

上一篇 下一篇

猜你喜欢

热点阅读