mysql面试相关

1 存储引擎

 2 explain各个字段含义

 

 3 MyISAM和InnoDB

mysql的data目录下的myisam文件.frm表结构文件, .MYD数据文件, .MYI索引文件

 

 4 分库分表

常用的有mycat等

可分为水平拆分和垂直拆分， 垂直拆分是指将一个库中的表分散到多个库中，但是这个对性能影响不大

重要的是水平拆分, 水平拆分涉及到分片，首先会先根据语法树, 去解析路由引擎, 然后改写引擎，执行引擎，最后再将查询结果归并引擎

5 悲观锁和乐观锁区别

5.1 悲观锁:

mysql悲观锁在非主键、非索引时是表锁，在主键、索引时是行锁

Java程序中的Synchronized和ReentrantLock等实现的锁也均为悲观锁，对数据库数据更新时, 先上锁，除了查询操作以为的其它操作数据库的请求都会进行等待。

在数据库中，悲观锁的调用一般是在所要查询的语句后面加上 for update，

select * from db_stock where goods_id = 1 for update

缺点当高并发情况下，会降低系统的吞吐量

5.2 乐观锁

 5.2.1 CSA

 CSA(比较交换) : 不对表数据上锁，但是每次修改操作时都会进行比较

 CAS的全称是Compare-and-Swap，也就是比较并交换，它包含了三个参数：V，A，B，V表示要读写的内存位置，A表示旧的预期值，B表示新值

 具体逻辑是: 在操作时会跟据V的值和预期A值比较，如果相等则更新为B, 如果不等则一直循环重试

缺点: 一直循环重试比较耗资源;  还会出现ABA问题: 当另一个线程将值A第一次被修改为B,第二次又被修改为A后。会被误认为是没有进行修改，此时当前这个线程会去修改

如何解决ABA问题:  加标识位，例如自增字段，每次修改都去自增1, 或者增加时间戳字段，每次修改时都会去对比时间戳值是否相同。

适用场景: 并发不高的情况

5.2.2 版本号机制

对表增加一个版本号字段，每次操作时都会带上版本号，如果能匹配上则进行更新操作

select goods_id,stock_num,version from db_stock where goods_id = 1
update db_stock set stock_num = stock_num - 1,version = version + 1 where goods_id = 1 and version = #{version}

 6 慢查询优化

使用explain去检查索引和耗时

避免索引失效，例如 %xx% 没有走索引，

   count(1) 替代 count(*);

   sum, count, order by, group by以及 时间过滤等字段可以加索引

  有些数据可以根据 最大id和最小id 去做区间分批多线程查询；

  有些数据可以先通过定时任务进行统计到新表需要用的时候再从新表查询

  当然还有一些内存数据库，性能更好成本更高

 7 B树和B+树的区别

B树: 对节点排序，一个节点可以存储很多元素，这些元素也排序了

B+树: 拥有B树的特点， 叶子节点之前存有指针，叶子节点冗余了非叶子节点的数据，即叶子节点包含了所有数据，并且排好序了

 8 mysql索引

注意: 索引字段不是越多越好，查询较多，修改较少的字段，并且过滤数据量大的字段适合做索引，

时间字段，sum, count, order by group by等字段适合加索引

避免索引失效，如: like %xx%

 

普通索引，唯一索引，主键索引，组合索引，全文索引

唯一索引: 运行为null, 值加唯一约束

主键索引: 不允许值为null, 每张表只能有一个主键

组合索引: 多个字段组和成一个索引; 

全文索引: 当大量的文件数据需要进行检索时，可以使用全文索引，mysql5.6之前只有myisam支持全文索引，mysql5.6之后myisam和innodb都支持全文检索

9 

 10

 11

 12

 13

 14

 15

 16

 17

18 几种索引存储结构区别

二叉树:

 每个节点是key-value格式存储, key存储的是索引字段的值,value存储的是索引所在行的磁盘文件地址

 大的值放右边，小的放左边

  缺点: 图2中每次插入数据，数据值都比之前的大，那么都会放在树的右边，这样树的高度很高

图1(二叉树)

图2(二叉树)

 

红黑树(平衡二叉树): 

  特点: 也是大的放右边，小的放左边

  和二叉树比较的优点: 二叉树不会自动平衡，红黑树如果单边元素过多会自动平衡以减小高度(例如: 5, 6, 7 依次插入时, 红黑树会自动平衡一次选择6作为父节点，高度变为两层，而二叉树高度是三层)

  缺点: 如果数据量很大时，树的高度还是会很高

图3

B树

 会将一个节点拆分成多个小节点, 每个小节点是key-value格式存储, key存储的是索引字段的值,value存储的是索引所在行的磁盘文件地址

 图4

B+树

高版本Mysql会将非叶子节点行加载到内存当中,这样只需访问一次磁盘io也就是访问叶子节点行

优点: 非叶子节点不存储data, 因此可以放更多的索引，而B树会存储data, B树非叶子节点占用容量大，所以B树节点高度比B+树更高

Hash索引结构

 19 聚集索引和非聚集索引区别

myisam属于非聚集索引, 根据索引文件找出对应的叶子节点, 叶子节点存储的是索引字段所在行的地址, 数据文件和索引文件是分开的

innodb属于聚集索引, 叶子节点存储的是索引字段的所在行的一整行字段数据(即冗余了其它字段), 效率高

 20 联合索引

有个最左前缀原则: 多个字段组合的索引按照索引字段从左到右的优先级进行过滤. 先会按照name字段进行过滤，如果找到了对应的值并且没有重复则无需再进行过滤。

如果存在相同的name值则继续根据age字段.. 依次下去直到找到这个主键值，然后通过主键值进行查找返回这行数据。

所以sql中至少要带上第一个索引字段即name字段作为过滤条件，不然这个组合索引会失效

 21. mysql查询缓存

mysql执行查询之前先会从查询缓存里面找, 如果找不到再会去磁盘里面找

查询缓存里面的key是sql语句, value是具体的值

查看查询缓存信息

21 mysql的分析器, 优化器和执行器

 22 mysql执行流程

22 MySQL目前主要有以下几种索引类型：

1.普通索引

2.唯一索引

3.主键索引

4.组合索引

5.全文索引

23 mysql锁有哪些

 23 for update悲观锁, 锁的范围

当select...from...where...for update时，where条件能精确到行既为行锁，不能则为表锁。
例1：某张表id是主键（既：id唯一）, 此时select…from…where id=3 for update就是行锁，因为id=3在这张表永远只有一个。

例2：某张表的字段name（name不唯一），此时select…from…where name=张三 for update就是表锁，因为表中可能存在多个张三。

特殊情况：需要特别注意的是，当字段唯一，也要求where条件要能够找到相应的值才是锁行，否则也会锁表。
例如：某张表id是主键（既：id唯一）, 此时select…from…where id=99999999 for update， 然而你的数据id并没有99999999，这样就会锁表。

 24 mongodb和mysql区别

1. 数据存储方式

MySQL：基于关系模型的数据库，主要采用表的形式存储数据，需要定义表结构，通常采用SQL语言来查询和修改数据。

MongoDB：基于文档模型的数据库，主要采用文档的形式存储数据，不需要定义表结构，通常采用MongoDB指定的查询语言来查询和修改数据。

2. 数据库架构

MySQL：采用传统的客户端-服务器架构，通常需要一个独立的服务器，然后通过网络访问服务器上的数据库。

MongoDB：采用分布式架构，数据可以存储在多个服务器上，使得它更加灵活，并且可以处理大量的数据。

3. 扩展性

MySQL：相对来说比较难扩展，需要对数据结构和系统架构进行重大的修改。

MongoDB：相对来说比较容易扩展，有很好的水平和垂直扩展能力。

4. 性能

MySQL：性能通常较低，会有一些瓶颈问题。

MongoDB：性能通常很高，可以处理大量的并发请求。

5. 适用范围

MySQL：适用于传统的关系型数据库场景，适用于需要事务处理的应用。

MongoDB：适用于需要快速处理大量非结构化数据的应用，适用于大数据和互联网领域。

 

25 mysql一些系统表相关信息(锁, 线程, 事务等)

查看数据库状态

# 会显示加锁的信息等等show engine innodb status;  

查看正在执行的线程信息

show full processlist;

查看正在锁的表

show open tables where in_use > 0;show open tables;

查看锁的类型、状态

show status like '%lock%';

5.0后，增加了3个关于锁的表

MySQL5.7版本

INFORMATION_SCHEMA.innodb_trx 当前运行的所有事务INFORMATION_SCHEMA.innodb_locks 当前出现的锁INFORMATION_SCHEMA.innodb_lock_waits 锁等待的对应关系

MySQL8.0版本

8.0后，performance_schema.data_locks代替了INFORMATION_SCHEMA.innodb_locks ，performance_schema.data_lock_waits代替了INFORMATION_SCHEMA.innodb_lock_waits

查看正在锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; -- 5.7SELECT * FROM performance_schema.data_locks; -- 8.0

查看等待锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; -- 5.7SELECT * FROM performance_schema.data_lock_waits; -- 8.0

查看行锁情况

show status like 'InnoDB_row_lock%';

查看表锁情况

show status like 'table%';

开启锁监控

开启监控后，可以看到锁的具体信息以及加锁顺序等，更容易分析死锁。

方式一：创建监控表，监控某个数据库

开启：

CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB;

关闭：

DROP TABLE innodb_lock_monitor;

注意：在未拥有设置全局属性权限下也可以开启该功能。

方式二，开启全局监控

开启：

set GLOBAL innodb_status_output=ON;set GLOBAL innodb_status_output_locks=ON;

关闭：

set GLOBAL innodb_status_output=OFF;set GLOBAL innodb_status_output_locks=OFF;

查看数据库状态

show engine innodb status \G;

其他监控

标准监控(Standard InnoDB Monitor)：监视活动事务持有的表锁、行锁；事务锁等待；线程信号量等待；文件IO请求；buffer pool统计信息；InnoDB主线程purge和change buffer merge活动。

# 方式一CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;DROP TABLE innodb_monitor;# 方式二set GLOBAL innodb_status_output=ON;set GLOBAL innodb_status_output=OFF;

锁监控(InnoDB Lock Monitor)：提供额外的锁信息。

# 方式一CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB;DROP TABLE innodb_lock_monitor;# 方式二set GLOBAL innodb_status_output=ON;set GLOBAL innodb_status_output_locks=ON;set GLOBAL innodb_status_output=OFF;set GLOBAL innodb_status_output_locks=OFF;

表空间监控(InnoDB Tablespace Monitor)：显示共享表空间中的文件段以及表空间数据结构配置验证。

CREATE TABLE innodb_tablespace_monitor (a INT) ENGINE=INNODB;DROP TABLE innodb_tablespace_monitor;

表监控(InnoDB Table Monitor)：显示内部数据字典的内容。

CREATE TABLE innodb_tablespace_monitor (a INT) ENGINE=INNODB;DROP TABLE innodb_tablespace_monitor;

 

root@127.0.0.1 : information_schema 13:28:38> desc innodb_locks;
+————-+———————+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+————-+———————+——+—–+———+——-+
| lock_id |#锁ID
| lock_trx_id |#拥有锁的事务ID
| lock_mode |#锁模式
| lock_type |#锁类型
| lock_table |#被锁的表
| lock_index |#被锁的索引
| lock_space |#被锁的表空间号
| lock_page |#被锁的页号
| lock_rec |#被锁的记录号
| lock_data |#被锁的数据
+————-+———————+——+—–+———+——-+
10 rows in set (0.00 sec)
root@127.0.0.1 : information_schema 13:28:56> desc innodb_lock_waits;

+——————-+————-+——+—–+———+——-+

| Field | Type | Null | Key | Default | Extra |

+——————-+————-+——+—–+———+——-+

| requesting_trx_id |#请求锁的事务ID

| requested_lock_id |#请求锁的锁ID

| blocking_trx_id |#当前拥有锁的事务ID

| blocking_lock_id |#当前拥有锁的锁ID

+——————-+————-+——+—–+———+——-+

4 rows in set (0.00 sec)

root@127.0.0.1 : information_schema 13:29:05> desc innodb_trx ;

+—————————-+———————+——+—–+———————+——-+

| Field | Type | Null | Key | Default | Extra |

+—————————-+———————+——+—–+———————+——-+

| trx_id |#事务ID

| trx_state |#事务状态：

| trx_started |#事务开始时间；

| trx_requested_lock_id |#innodb_locks.lock_id

| trx_wait_started |#事务开始等待的时间

| trx_weight |#

| trx_mysql_thread_id |#事务线程ID

| trx_query |#具体SQL语句

| trx_operation_state |#事务当前操作状态

| trx_tables_in_use |#事务中有多少个表被使用

| trx_tables_locked |#事务拥有多少个锁

| trx_lock_structs |#

| trx_lock_memory_bytes |#事务锁住的内存大小（B）

| trx_rows_locked |#事务锁住的行数

| trx_rows_modified |#事务更改的行数

| trx_concurrency_tickets |#事务并发票数

| trx_isolation_level |#事务隔离级别

| trx_unique_checks |#是否唯一性检查

| trx_foreign_key_checks |#是否外键检查

| trx_last_foreign_key_error |#最后的外键错误

| trx_adaptive_hash_latched |#

| trx_adaptive_hash_timeout |#