2019年java中高级java面试题（三）mysql

拼搏现实的明天。 2022-01-23 12:47 313阅读 0赞

### 1、 乐观锁与悲观锁的区别？ ###

悲观锁(Pessimistic Lock), 顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。

乐观锁(Optimistic Lock), 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write\_condition机制的其实都是提供的乐观锁

### 2 、事务的特性 ###

原子性:(Atomicity)

原子性:强调事务的不可分割.

一致性:(Consistency)

一致性:事务在执行的前后,数据的完整性保持一致.

隔离性:(Isolation)

隔离性:多个事务并发执行的时候,一个事务的执行不应该受到其他的事务的干扰.

持久性:(Durability)

持久性:事务一个结束了.数据就永久的保存到数据库.

### 3 、不考虑事务的隔离性引发的一系列安全问题 ###

**\* 脏读      :一个事务读到了另一个事务未提交的数据.**

**\* 不可重复读 :一个事务读到了另一个事务已经提交的update的数据,导致在一个事务中多次的查询结果不一致.**

**\* 虚读/幻读  :一个事务读到了另一个事务已经提交的insert的数据,导致在一个事务中多次查询的结果不一致.**

**\* 数据库中提供了事务的隔离级别用于解决三类读问题.**

**\* read uncommitted  :未提交读.脏读、不可重复读、虚读都是有可能发生.**

**\* read committed    :已提交读.避免脏读.但是不可重复读、虚读是有可能发生.**

**\* repeatable read   :可重复读.避免脏读、不可重复读.但是虚读是有可能发生.**

**\* serializable      :串行化的.避免脏读、不可重复读、虚读的发生.**

**\* 安全性:serializable > repeatable read > read committed > read uncommitted**

**\* 效率性:read uncommitted > read committed > repeatable read > serializable**

**\*\*\*\*\* MYSQL数据库默认隔离级别:repeatable read .Oracle数据库默认的隔离级别:read committed**

### 4、 mysql常用的数据库引擎     ###

![20190221091906149.png][]

**InnoDB**：支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。

InnoDB是提供了提交，回滚和崩溃恢复能力的事物安全存储引擎，支持行锁定和外键，是mysql的默认存储引擎

**MyISAM**：插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比较低，也可以使用。

MyISAM不支持事物 插入和查询的处理效率高，支持索引

**MEMORY**：所有的数据都在内存中，数据的处理速度快，但是安全性不高。如果需要很快的读写速度，对数据的安全性要求较低，可以选择MEMOEY。它对表的大小有要求，不能建立太大的表。所以，这类数据库只使用在相对较小的数据库表。

### 5、 jdbc中Statement与Preparestament的区别 ###

第一：

prepareStatement会先初始化SQL，先把这个SQL提交到数据库中进行预处理，多次使用可提高效率。

Statement不会初始化，没有预处理.

第二：

prepareStatement可以替换变量在SQL语句中可以包含?，可以用

ps=conn.prepareStatement("select* from Cust where ID=?");
     
    int sid=1001;
     
    ps.setInt(1, sid);
     
    rs = ps.executeQuery();

可以把?替换成变量。

而Statement只能用

int sid=1001;
     
    Statement stmt = conn.createStatement();
     
    ResultSet rs = stmt.executeQuery("select * from Cust where ID="+sid);

来实现。

第三：

prepareStatement会先初始化SQL，先把这个SQL提交到数据库中进行预处理，多次使用可提高效率。

Statement不会初始化，没有预处理，没次都是从0开始执行SQL

### 6、 sql优化 ###

**insert优化**

1、如果需要对同一个表插入很多行数据时，尽量使用多个值表的sql语句

insert into tb_test values(1,'张三'),(2,'TOM'),(3,'JERRY');

2、在事务中进行数据插入

start transaction
    insert into tb_test values(1,'张三');
    insert into tb_test values(2,'TOM');
    insert into tb_test values(3,'JERRY');
    commit;

**rewriteBatchedStatements=true**，mysql默认关闭了batch处理，通过此参数进行打开

**order by优化**

Using filesort

Using index

尽量减少额外排序通过索引直接返回有序数据，通过建立合适的索引减少Filesort的出现

mysql两种排序算法

*两次扫描算法：首先根据条件取出排序字段和指针信息，在排序区中排序，完成排序后，根据行指针回表读取记录*

*一次扫描算法：一次性取出满足条件的所有字段在排序区排序后直接输出结果*

可以提高sort\_buffer\_size和max\_length\_for\_sort\_data系统变量，增大排序区大小，调高排序效率

**group by优化**

group by会进行额外的排序操作

select age,count(*) from emp group by age order by null;

**查询优化**  
1、对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。  
2、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库.  
3、应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。  
4、应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num=10 or Name = 'admin'

可以这样查询：

select id from t where num = 10
     
    union all
     
    select id from t where Name = 'admin'

5、in 和 not in 也要慎用，否则会导致全表扫描

6、创建适合的索引

例如我们建立了一个这样的索引（area,age,salary），那么其实相当于创建了（area,age,salary）,(area,age),(area)三个索引

7、like语句优化

SELECT id FROM A WHERE name like '%abc%'  
 　　由于abc前面用了“%”，因此该查询必然走全表查询，除非必要，否则不要在关键词前加%，优化成如下

SELECT id FROM A WHERE name like 'abc%'

8、尽量用内连接替换外链接，用外链接替换子查询。

9、分页优化

在索引上完成排序操作，再根据索引关联其他内容

select * 
        from tb_item t,(select id from tb_item order by id limit 2000000,10) a
    where 
        t.id=a.id

### 7、分库分表 ###

### 8、**oracle数据库的分页** ###

select t1.*
     
      from (select rownum rw, k.*
     
    from ks_collect_pushed k
     
    where rownum < = 15) t1
     
     where rw > 5

### 9、mysql索引分类 ###

**1、普通索引**

普通索引：允许列中插入重复值和空值

**2、唯一索引**

唯一索引：索引列的值必须唯一，但允许由空值

**3、主键索引**

主键索引，索引列的值必须唯一，切不允许有空值

**4、联合索引**

联合索引又叫复合索引。对于复合索引:Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持**a** |** a,b**| **a,b,c** 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。

**5、全文索引**

全文索引类型FULLTEXT，可以在CHAR、VARCHAR、TEXT上创建，只有MYIDAM存储引擎支持

**6、空间索引**

空间索引是对空间数据类型的字段建立的索引，mysql中的空间数据类型有4种，分别是：**GEOMETRY、POINT、LINESTRING、PLYGON**。mysql使用**SPATIAL**创建索引，空间索引的列为**NOTNULL**

### 10、MySQL存储引擎MyISAM与InnoDB区别  ###

**1) 事务支持**  
MyISAM不支持事务，而InnoDB支持。

**2) 存储结构**  
MyISAM：每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。  
InnoDB：所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。

**3) 存储空间**  
MyISAM：可被压缩，存储空间较小。支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。  
InnoDB：需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

**4) 可移植性、备份及恢复**  
MyISAM：数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。  
InnoDB：免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了。

**5) 事务支持**  
MyISAM：强调的是性能，每次查询具有原子性,其执行数度比InnoDB类型更快，但是不提供事务支持。  
InnoDB：提供事务支持事务，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。

**6) AUTO\_INCREMENT**  
MyISAM：可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，他可以根据前面几列进行排序后递增。  
InnoDB：InnoDB中必须包含只有该字段的索引。引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列。

**7) 表锁差异**  
MyISAM：只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。  
InnoDB：支持事务和行级锁，是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。

MyISAM锁的粒度是表级，而InnoDB支持行级锁定。简单来说就是, InnoDB支持数据行锁定，而MyISAM不支持行锁定，只支持锁定整个表。即MyISAM同一个表上的读锁和写锁是互斥的，MyISAM并发读写时如果等待队列中既有读请求又有写请求，默认写请求的优先级高，即使读请求先到，所以MyISAM不适合于有大量查询和修改并存的情况，那样查询进程会长时间阻塞。因为MyISAM是锁表，所以某项读操作比较耗时会使其他写进程饿死。

**8) 全文索引**  
MyISAM：支持(FULLTEXT类型的)全文索引  
InnoDB：不支持(FULLTEXT类型的)全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。

**9) 表主键**  
MyISAM：允许没有任何索引和主键的表存在，索引都是保存行的地址。  
InnoDB：如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。InnoDB的主键范围更大，最大是MyISAM的2倍。

**10) 表的具体行数**  
MyISAM：保存有表的总行数，如果select count(\*) from table;会直接取出出该值。  
InnoDB：没有保存表的总行数(只能遍历)，如果使用select count(\*) from table；就会遍历整个表，消耗相当大，但是在加了wehre条件后，myisam和innodb处理的方式都一样。

**11) CURD操作**  
MyISAM：如果执行大量的SELECT，MyISAM是更好的选择。  
InnoDB：如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。DELETE 从性能上InnoDB更优，但DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除，在innodb上如果要清空保存有大量数据的表，最好使用truncate table这个命令。

**12) 外键**  
MyISAM：不支持  
InnoDB：支持

###    11、mysql 内存优化 ###

1、将尽量多的内存给mysql作缓存

2、MyISAM存储文件的读取依赖于操作系统自身IO缓存

### 12、Innodb内存优化 ###

Innodb用一块内存做io缓存池，该缓存池不仅缓存innodb索引块，也用来缓存innodb的数据块

**innodb\_buffer\_pool\_size**

索引块和数据块最大的内存

**innodb\_log\_buffer\_size**

决定了innodb重做日志缓存的大小

[20190221091906149.png]: /images/20220123/0f038789741d49f18b84e371db18af1b.png