JDBC 连接操作数据库

https://www.cnblogs.com/jyroy/p/9637149.html
https://www.cnblogs.com/hongten/archive/2011/03/29/1998311.html

1. 加载JDBC驱动程序

在连接数据库之前，首先要加载想要连接的数据库的驱动到 JVM，这通过 java.lang.Class 类的静态方法forName(String className) 实现。成功加载后，会将 Driver 类的实例注册到 DriverManager 类中。

  1. try{   
  2.     // 加载MySql的驱动类
  3.     // DriverManager.registerDriver(new com.mysql.jdbc.Driver());
  4.     Class.forName("com.mysql.jdbc.Driver");
  5. }catch(ClassNotFoundException e){   
  6.     System.out.println("找不到驱动程序类 ，加载驱动失败！");   
  7.     e.printStackTrace() ;   
  8. }

2. 建立数据库连接

  1. Connection conn = DriverManager.getConnection("jdbc:msql://localhost/test?user=root&password=root");

3. 创建Statement

要执行 SQL 语句，必须获得 java.sql.Statement 实例，Statement 实例分为以下 3 种类型：

  1. 执行静态 SQL 语句，通常通过 Statement 实例实现。
  2. 执行动态 SQL 语句，通常通过 PreparedStatement 实例实现。

  3. 执行数据库存储过程，通常通过 CallableStatement 实例实现。

    1. Statement stmt = conn.createStatement() ;   
    2. PreparedStatement pstmt = conn.prepareStatement(sql) ;   
    3. CallableStatement cstmt = conn.prepareCall("{CALL demoSp(? , ?)}") ;

    4. 执行 SQL 语句

    Statement 接口提供了三种执行 SQL 语句的方法：executeQuery、executeUpdate 和 execute：

  4. ResultSet executeQuery(String sqlString)：执行查询数据库的SQL语句，返回一个结果集（ResultSet）对象；

  5. int executeUpdate(String sqlString)：用于执行 INSERT、UPDATE 或 DELETE 语句以及 SQL DDL 语句，如：CREATE TABLE 和 DROP TABLE 等；

  6. execute(String sqlString)：用于执行返回多个结果集、多个更新计数或二者组合的语句；

    1. ResultSet rs = stmt.executeQuery("SELECT * FROM ...") ;   
    2. int rows = stmt.executeUpdate("INSERT INTO ...") ;   
    3. boolean flag = stmt.execute(String sql) ;

    5. 处理结果

    两种情况：

  7. 执行更新返回的是本次操作影响到的记录数。

  8. 执行查询返回的结果是一个 ResultSet 对象。

    1. ResultSet 包含符合 SQL 语句中条件的所有行，并且它通过一套 get 方法提供了对这些行中数据的访问；
    2. 使用结果集（ResultSet）对象的访问方法获取数据；
      1. while(rs.next()){   
      2. String name = rs.getString("name") ;   
      3. String pass = rs.getString(1) ; // 此方法比较高效   
      4. }   
      5. // 列是从左到右编号的，并且从列 1 开始

      6. 释放资源：关闭 JDBC 对象

      操作完成以后要把所有使用的 JDBC 对象全都关闭，以释放 JDBC 资源，关闭顺序和声明顺序相反：
      1. if(rs != null) {   // 关闭结果集   
      2. try {   
      3.   rs.close();   
      4. } catch(SQLException e) {   
      5.   e.printStackTrace();   
      6. }   
      7. }   
      8. if(stmt != null) {   // 关闭声明   
      9. try {   
      10.   stmt.close();   
      11. } catch(SQLException e){   
      12.   e.printStackTrace();   
      13. }   
      14. }   
      15. if(conn != null) {  // 关闭连接对象   
      16. try {   
      17.   conn.close();   
      18. } catch(SQLException e) {   
      19.   e.printStackTrace();   
      20. }   
      21. }

      事务

      事务的四大特性 ACID

  9. 原子性（Atomicity）

  10. 一致性（Consistency）
  11. 隔离性（Isolation）

  12. 持久性（Durability）

    并发事务带来的问题

  13. 脏读：一个事务读取到另一个事务尚未提交的数据；

  14. 丢失修改：一个事务修改了另一个事务的修改结果；
  15. 不可重复读：一个事务两次读取数据之间，另一个事务修改了数据，导致两次读取的数据不一致；
  16. 幻读：一个事务两次读取同样的数据之间，另一个事务新增或删除了一些数据，导致两次读取的数据数目不一致；

不可重复读和幻读的区别是 数据的不一致还是记录数的不一致。

事务的隔离级别

  1. 读取未提交：最低的隔离级别，允许读取未提交的数据；可能导致脏读，幻读、不可重复读。
  2. 读取已提交：允许读取并发事务已提交的数据，可以避免脏读；可能导致丢失修改、幻读、不可重复读。
  3. 可重复读：对同一字段的多次读取结果是一致的，除非数据是由当前事务修改，可以避免脏读和不可重复读；可能导致幻读。
  4. 可串行化：最高的隔离级别，所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

MySQL InnoDB 存储引擎的默认⽀持的隔离级别是 REPEATABLE-READ（可重读）。我们可以通过 SELECT @@tx_isolation; 命令来查看

索引

https://www.cnblogs.com/williamjie/p/11187470.html

什么是索引？

索引是存储引擎用于提高数据库表的访问速度的一种数据结构。

优点：

  1. 加快数据查找的速度
  2. 为用来排序或者是分组的字段添加索引，可以加快分组和排序的速度

  3. 加快表与表之间连接的速度

    缺点：

  4. 创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加

  5. 建立索引需要占用物理空间

  6. 会降低表的增删改的效率，因为每次对表记录进行增删改，需要进行动态维护索引，导致增删改时间变长

    作用：

    数据是存储在磁盘上的，查询数据时，如果没有索引，会加载所有的数据到内存，依次进行检索，读取磁盘次数较多。有了索引，就不需要加载所有数据，因为B+树的高度一般在2-4层，最多只需要读取2-4次磁盘，查询速度大大提升。

    索引的分类

  7. 主键索引；

  8. 唯一索引；
  9. 普通索引；
  10. 组合索引；

  11. 全文索引；

    什么情况下要建立索引

  12. 经常用于查询的字段

  13. 经常用于连接的字段建立索引，可以加快连接的速度

  14. 经常需要排序、分组的字段建立索引，因为索引已经排好序，可以加快排序查询速度

    什么情况下不建立索引

  15. 很少查询或重复值很多的字段；

  16. 表记录较少
  17. 需要经常增删改
  18. 参与列计算的列不适合建索引
  19. 区分度不高的字段不适合建立索引，如性别等

    mysql 索引是怎么实现的？

    索引是满足某种特定查找算法的数据结构，而这些数据结构会以某种方式指向数据，从而实现高效查找数据。

具体来说 MySQL 中的索引，不同的数据引擎实现有所不同，但目前主流的数据库引擎的索引都是 B+ 树实现的，B+ 树的搜索效率，可以到达二分法的性能，找到数据区域之后就找到了完整的数据结构了，所有索引的性能也是更好的。

聚簇索引

在 InnoDB 里，索引 B+ Tree 的叶子节点存储了整行数据的是主键索引，也被称之为聚簇索引。

而索引B+ Tree的叶子节点存储了主键的值的是非主键索引，也被称之为非聚簇索引

组合索引和最左前缀匹配原则

创建组合索引时，多个字段根据识别度由高到低排列，根据最左前缀匹配原则，检索数据是从组合索引的最左边字段开始匹配。如一个组合索引（key1, key2, key3），相当于创建了 (key1)，(key1, key2)，(key1, key2, key3) 三个索引，这就是最左前缀匹配原则。

索引的数据结构

索引的数据结构主要有B+树和哈希表，对应的索引分别为 B+树索引 和哈希索引。InnoDB 引擎的索引类型有B+树索引和哈希索引，默认的索引类型为 B+树索引。

B+树索引

B+ 树是基于 B 树和叶子节点顺序访问指针进行实现，它具有 B 树的平衡性，并且通过顺序访问指针来提高区间查询的性能。

B+ 树相对于 B 树的改进：

  1. 非叶子节点，只存储键值信息，这样极大增加了存放索引的数据量。
  2. 所有叶子节点之间都有一个链指针。对于区间查询时，不需要再从根节点开始，可直接定位到数据。
  3. 数据记录都存放在叶子节点中。根据二叉树的特点，这个是顺序访问指针，提升了区间访问的性能。

通过这样的设计，一张千万级的表最多只需要3次磁盘交互就可以找出数据。

哈希索引

哈希索引是基于哈希表实现的，对于每一行数据，存储引擎会对索引列进行哈希计算得到哈希码，并且哈希算法要尽量保证不同的列值计算出的哈希码值是不同的，将哈希码的值作为哈希表的 key 值，将指向数据行的指针作为哈希表的 value 值。这样查找一个数据的时间复杂度就是 O(1)，一般多用于精确查找。

B+树索引和哈希索引的区别

  1. 哈希索引不支持排序，因为哈希表是无序的。
  2. 哈希索引不支持范围查找。
  3. 哈希索引不支持模糊查询及多列索引的最左前缀匹配。

  4. 因为哈希表中会存在哈希冲突，所以哈希索引的性能是不稳定的，而 B+ 树索引的性能是相对稳定的，每次查询都是从根节点到叶子节点。

    为什么 B+ 树比 B 树更适合实现数据库索引？

  5. 由于B+树的数据都存储在叶子结点中，叶子结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，而在数据库中基于范围的查询是非常频繁的，所以通常B+树用于数据库索引。

  6. B+树的节点只存储索引key值，具体信息的地址存在于叶子节点的地址中。这就使以页为单位的索引中可以存放更多的节点。减少更多的I/O支出。

  7. B+树的查询效率更加稳定，任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

    索引什么时候会失效？

  8. 最左前缀匹配：对于组合索引，不是使用组合索引最左边的字段，则不会使用索引

(A，B，C) 如果只有 （A，C）也会生效，但是会根据A查询出一个范围

  1. 以 % 开头的 like 查询如 %abc，无法使用索引（覆盖索引会使用）；非%开头的like查询如abc%，相当于范围查询，会使用索引
  2. 查询条件中列类型是字符串，没有使用引号，可能会因为类型不同发生隐式转换，使索引失效

比如 int 类型的主键，查询时带了引号，就会失效

  1. 数据的字符集不一致，比如 utf-8 和 utf8mb4
  2. 尽量使用覆盖索引，即查询列在索引字段的范围内；
  3. 判断索引列是否不等于（!= <>）某个值，或 is null，is not null；
  4. 对索引列进行运算
  5. 查询条件使用 or 连接，也会导致索引失效

image.png

Mysql 性能优化

优化的一般思路

  1. 设计良好的数据结构；
  2. 选择合适的表字段数据类型、选择合适的索引、SQL语句；
  3. MySQL 主从读写分离；
  4. 按规律进行分表，减少单表中的数据量提高查询速度；
  5. 选择合适的缓存；
  6. 优化硬件

首先来讲的话，在设计程序表结构的时候应该考虑好数据结构的类型，选择一个良好的数据类型对后期的优化和运行的速度都会有很大的提升；比如加密后的 md5 可以试用 Char ，年龄可以使用 unsigned tinyint 范围在 0-255足够年龄的使用；

当数据库存储数据增大时导致查询性能下降时，我们可以打开慢查询日志,让整个程序跑一段时间,过滤出执行速度慢 sql;通过执行计划分析执行速度慢的原因，再通过添加索引或修改 sql 结构来提升性能；当数据量过大时，添加索引将对性能提升不大时可以采用分库分表的方式或增强硬件；

1. 数据库的三范式是什么？

  • 第一范式：强调的是列的原子性，即数据库表的每一列都是不可分割的原子数据项。
  • 第二范式：要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。

  • 第三范式：任何非主属性不依赖于其它非主属性。

    2. 一张自增表里面总共有 17 条数据，删除了最后 2 条数据，重启 mysql 数据库，又插入了一条数据，此时 id 是几？

  • 表类型如果是 MyISAM ，那 id 就是 18。

  • 表类型如果是 InnoDB，那 id 就是 15。

InnoDB 表只会把自增主键的最大 id 记录在内存中，所以重启之后会导致最大 id 丢失。

3. 如何获取当前数据库版本？

使用 select version() 获取当前 MySQL 数据库版本。

4. 说一下 ACID 是什么？

  • Atomicity（原子性）：一个事务（transaction）中的所有操作，或者全部完成，或者全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。即，事务不可分割、不可约简。
  • Consistency（一致性）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设约束、触发器、级联回滚等。
  • Isolation（隔离性）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
  • Durability（持久性）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

    5. char 和 varchar 的区别是什么？

    char(n) ：固定长度类型，比如订阅 char(10)，当你输入”abc”三个字符的时候，它们占的空间还是 10 个字节，其他 7 个是空字节。
    chat 优点：效率高；缺点：占用空间；适用场景：存储密码的 md5 值，固定长度的，使用 char 非常合适。
    varchar(n) ：可变长度，存储的值是每个值占用的字节再加上一个用来记录其长度的字节的长度。

所以，从空间上考虑 varcahr 比较合适；从效率上考虑 char 比较合适，二者使用需要权衡。

6. float 和 double 的区别是什么？

  • float 最多可以存储 8 位的十进制数，并在内存中占 4 字节。
  • double 最可可以存储 16 位的十进制数，并在内存中占 8 字节。

    7. mysql 的内连接、左连接、右连接有什么区别？

    内连接关键字：inner join；左连接：left join；右连接：right join。

内连接是把匹配的关联数据显示出来；左连接是左边的表全部显示出来，右边的表显示出符合条件的数据；右连接正好相反。

9. 怎么验证 mysql 的索引是否满足需求？

使用 explain 查看 SQL 是如何执行查询语句的，从而分析你的索引是否满足需求。

explain 语法：explain select * from table where type=1。

10. 说一下数据库的事务隔离？

MySQL 的事务隔离是在 MySQL. ini 配置文件里添加的，在文件的最后添加：transaction-isolation = REPEATABLE-READ

可用的配置值：READ-UNCOMMITTED、READ-COMMITTED、REPEATABLE-READ、SERIALIZABLE。

  • READ-UNCOMMITTED：未提交读，最低隔离级别、事务未提交前，就可被其他事务读取（会出现幻读、脏读、不可重复读）。
  • READ-COMMITTED：提交读，一个事务提交后才能被其他事务读取到（会造成幻读、不可重复读）。
  • REPEATABLE-READ：可重复读，默认级别，保证多次读取同一个数据时，其值都和事务开始时候的内容是一致，禁止读取到别的事务未提交的数据（会造成幻读）。
  • SERIALIZABLE：序列化，代价最高最可靠的隔离级别，该隔离级别能防止脏读、不可重复读、幻读。

脏读 ：表示一个事务能够读取另一个事务中还未提交的数据。比如，某个事务尝试插入记录 A，此时该事务还未提交，然后另一个事务尝试读取到了记录 A。

丢失修改：事务修改结果被另一个事务修改

不可重复读 ：是指在一个事务内，多次读同一数据。

幻读 ：指同一个事务内多次查询返回的结果集不一样。比如同一个事务 A 第一次查询时候有 n 条记录，但是第二次同等条件下查询却有 n+1 条记录，这就好像产生了幻觉。发生幻读的原因也是另外一个事务新增或者删除或者修改了第一个事务结果集里面的数据，同一个记录的数据内容被修改了，所有数据行的记录就变多或者变少了。

11. 说一下 mysql 常用的引擎？

InnoDB 引擎：InnoDB 引擎提供了对数据库 acid 事务的支持，并且还提供了行级锁和外键的约束，它的设计的目标就是处理大数据容量的数据库系统。MySQL 运行的时候，InnoDB 会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎是不支持全文搜索，同时启动也比较的慢，它是不会保存表的行数的，所以当进行 select count(*) from table 指令的时候，需要进行扫描全表。由于锁的粒度小，写操作是不会锁定全表的,所以在并发度较高的场景下使用会提升效率的。

MyIASM 引擎：MySQL 的默认引擎，但不提供事务的支持，也不支持行级锁和外键。因此当执行插入和更新语句时，即执行写操作的时候需要锁定这个表，所以会导致效率会降低。不过和 InnoDB 不同的是，MyIASM 引擎是保存了表的行数，于是当进行 select count(*) from table 语句时，可以直接的读取已经保存的值而不需要进行扫描全表。所以，如果表的读操作远远多于写操作时，并且不需要事务的支持的，可以将 MyIASM 作为数据库引擎的首选。

12. 说一下 mysql 的行锁和表锁？

MyISAM 只支持表锁，InnoDB 支持表锁和行锁，默认为行锁。

  • 表级锁：开销小，加锁快，不会出现死锁。锁定粒度大，发生锁冲突的概率最高，并发量最低。

  • 行级锁：开销大，加锁慢，会出现死锁。锁力度小，发生锁冲突的概率小，并发度最高。

    13. 说一下乐观锁和悲观锁？

  • 乐观锁：每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。

  • 悲观锁：每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻止，直到这个锁被释放。

数据库的乐观锁需要自己实现，在表里面添加一个 version 字段，每次修改成功值加 1，这样每次修改的时候先对比一下，自己拥有的 version 和数据库现在的 version 是否一致，如果不一致就不修改，这样就实现了乐观锁。

14. mysql 问题排查都有哪些手段？

  • 使用 show processlist 命令查看当前所有连接信息。
  • 使用 explain 命令查询 SQL 语句执行计划。

  • 开启慢查询日志，查看慢查询的 SQL。

    15. 如何做 mysql 的性能优化？

  • 为搜索字段创建索引。

  • 避免使用 select *，列出需要查询的字段。
  • 垂直分割分表。
  • 选择正确的存储引擎。