2. JDBC

1. 概述

1.1 数据持久化

• 把数据保存到可掉式存储设备中以供之后使用

• 持久化是实现过程大多数通过各种关系数据库来完成

• 包括从关系数据库中读取数据和写入到数据库中

1.2 JDBC介绍

• JDBC(Java Database Connectivity)
• 它是一个独立于特定数据库管理系统、通用的SQL数据库存取和操作公共接口

1.3 JDBC步骤

2. Mysql连接

2.1 Driver接口

• 加载和注册JDBC驱动

1. 导入依赖
在程序的pom.xml文件中的<dependencies> </dependencies>加入以下内容
<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<version>8.0.31</version>
</dependency>

2.加载和注册驱动 --使用反射机制
Class.forName(“com.mysql.jdbc.Driver”);  -- 加载
注册驱动会自动启动

2.2 URL

• JDBC URL用于表示一个被注册驱动程序，驱动程序管理器通过这个URL选择正确的驱动程序，从而建立到数据库的连接
• URL通常由三部分组成，各部分之间用冒号分隔即：jdbc:子协议:子名称
  • 协议：JDBC URL中的协议总是jdbc
  • 子协议：子协议用于标识一个数据库驱动程序
  • 子名称：一种标识数据库的方法。目的：定位数据库。包含主机名，端口号，数据库名

举例说明
  jdbc: mysql: //localhost:3306/test
    ^     ^            ^
    |     |            |
   协议  子协议        子名称

2.3 用户名和密码

• user和password可以用“属性名=属性值”方式告诉数据库

• 可以调用DriverManager类的getConnection()方法建立到数据库的连接

2.4 连接代码

@Test
public  void testConnection5() throws Exception {
    //1.加载配置文件
    InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
    Properties pros = new Properties();
    pros.load(is); 
    //2.读取配置信息
    String user = pros.getProperty("user");
    String password = pros.getProperty("password");
    String url = pros.getProperty("url");
    String driverClass = pros.getProperty("driverClass");
   
    //3.加载驱动
    Class.forName(driverClass);
    //4.获取连接
    Connection conn = DriverManager.getConnection(url,user,password);
    System.out.println(conn);
}

• 其中配置文件在工程的src目录下的resources文件夹下 的【jdbc.properties】文件

user=root
password=123456
url=jdbc:mysql://localhost:3306/test 连接本地数据库的test库
driverClass=com.mysql.jdbc.Driver

3. PreparedStatement

3.1 简介

• 向数据库服务器发送命令，并接收数据库的结果。因此该连接是一个Socket连接。
• 在 java.sql 包中有 3 个接口分别定义了对数据库的调用的不同方式
• • Statement：用于执行静态 SQL 语句并返回它所生成结果的对象。
  • PrepatedStatement ：SQL 语句被预编译并存储在此对象中，可以使用此对象多次高效地执行该语句。推荐使用
  • CallableStatement ：用于执行 SQL 存储过程。
• PreparedStatement介绍
  • 使用Connection的preparedStatement(String sql)方式获取PreparedStatement对象
  • 它未Statement的子接口，表示一条预编译过的SQL语句
  • 该对象的 SQL 语句中的参数用问号(?)来表示，用它的SetXX()方法来设置这些参数，下标从1开始
• PreparedStatement 的优点
• 能最大可能提高性能
• 可以防止SQL注入
• Statement对象需要字符串拼接，语句繁琐。

3.2 案例展示

案例 登录操作
public class PrepareStatementTest {
    public static void main(String[] args) throws Exception {
        Scanner sc = new Scanner(System.in);
        System.out.println("输入用户名");
        String username = sc.nextLine();  //等价于sc.next()
        System.out.println("输入密码");
        String password = sc.nextLine();

        Class.forName("com.mysql.cj.jdbc.Driver");  //加载驱动  
        
       //Connection
        Connection conn = null;                                        //URL    账号     密码
        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
       
        String sql = "SELECT * FROM user_table WHERE USER = ? AND PASSWORD = ?";  //sql语句
        //PrepareStatement 执行sql语句
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, username);    //将sql语句中的？替代成要传入的值
        ps.setString(2, password);
       
        //ResultSet
        ResultSet rs = ps.executeQuery();  // 接受sql语句执行的结果
       
        if (rs.next()) {
            System.out.println("登录成功");
        } else {
            System.out.println("登录失败");
        }
        //必须关闭连接
        rs.close();
        ps.close();
        conn.close();
    }
}

4. CRUD和改良

4.1 程序的分层

● Controller：控制层
● Service：业务逻辑层（调用dao）
● DAO（data access object）：数据访问层（jdbc） 主要代码在这里写
● po：实体类，领域模型(persistence object）

4.2 类型对照

• 创建表对应的实体类，表->类，字段->属性
• 对应关系如下给

Java类型	sql类型
boolean	BIT
byte	TINYINT
short	SMALLINT
int	INTEGER
long	BIGINT
string	CHAR,VARCHAR
byte array	BINARY,VAR BINARY
java.sql.Date	DATE
java.sql.Time	TIME
java.sql.Timestamp	TIMESTAMP

//注意事项：
1. 类中的整数类型变量使用 Integer 包装类，不要使用int 因为int类型变量不能为空
2. 尽量做到属性名和字段名一致

4.3 Dao层代码

• 以User为例进行演示

1.配置文件-->jdbc.properties//在源代码的resources下
   //作用：资源信息；
user=root
password=123456
url=jdbc:mysql://localhost:3306/test
driverClass=com.mysql.cj.jdbc.Driver

2.自定义工具类
   //作用：打开连接，关闭资源
public class JdbcUtil {
    static String driver ;
    static String url;
    static String user;
    static String password;
    static Properties pro;

    static{//静态获取一次配置文件中的内容
        InputStream in = JdbcUtil.class.getResourceAsStream("/jdbc.properties");
        pro = new Properties();
        try {
            pro.load(in);//加载
        } catch (IOException e) {
            e.printStackTrace();
        }
        driver = pro.getProperty("driverClass");
        url = pro.getProperty("url");
        user =pro.getProperty("user");
        password = pro.getProperty("password");
    }

       //资源连接
       public static Connection getConnection()throws Exception  {
//        Class.forName(driver);
//        return DriverManager.getConnection(url, user, password);

        //使用连接池
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
        pro.load(is);
        DataSource source = DruidDataSourceFactory.createDataSource(pro);
        return source.getConnection();
    }
    //关闭资源操作
    public static void closeResource(Connection conn, PreparedStatement ps, ResultSet rs){
        try {
            if (rs != null && !rs.isClosed()) {
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (ps != null && !ps.isClosed()) {
                ps.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (conn != null && !conn.isClosed()) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

3.BaseDao基类，User的父类
public class BaseDao<T> {
    Class<T> clazz;
    {
        //得到当前对象的父类声明的返回类型即T的准取值
        // - this = UserDao extends BaseDao<User>
        // - this = OrderDao extends BaseDao<Order>
        Type genericSuperclass = this.getClass().getGenericSuperclass();//获取泛型型类型 T
        ParameterizedType paramType = (ParameterizedType) genericSuperclass;//将泛型型转为参数型类型
        Type[] typeArguments = paramType.getActualTypeArguments();
       //获取参数型类型的每一个类型数组，（因为泛型中可以传入多个参数）
       
        clazz = (Class<T>) typeArguments[0];//得到数组的第一个类型即：T的类型
    }
    //查询操作
    public List<T> seekOperator(String sql, Object... params) throws Exception {
       //使用可变参数好处-->参数个数可以为零
        List<T> list = new ArrayList<>(10);
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtil.getConnection();//
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i + 1, params[i]);
            }
            rs = ps.executeQuery();

            //查询结果的元数据（列名，列类型，列数量...）
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();//得到结果中列的数量
            //获取类型对象
            T instance = null;

            while (rs.next()) {//查看是否还有数据
                instance = clazz.newInstance();
                for (int i = 0; i < columnCount; i++) {//对一个记录进行赋值操作

                    Object value = rs.getObject(i + 1);//得到列的数值

                    //得到属性名并赋值，通过反射得到 前提：列名 = 类的属性名
                    String columnLabel = metaData.getColumnLabel(i + 1);//得到列的别名
                    Field field = clazz.getDeclaredField(columnLabel);//该field为类的属性
                    field.setAccessible(true);//关闭安全检测
                    field.set(instance, value);//赋值操作
                }
                list.add(instance);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.closeResource(conn, ps, rs);
        }
        return list;
    }
   
   /*内容补充
   ResultSetMetaData用于获取关于 ResultSet 对象中列的类型和属性信息的对象
   ● ResultSetMetaData meta = rs.getMetaData();
 	  ○ getColumnName(int column)：获取指定列的名称
	  ○ getColumnLabel(int column)：获取指定列的别名
 	  ○ getColumnCount()：返回当前 ResultSet 对象中的列数。  
   */
   

    //查询一个操作
    public T seekOnlyOperator(String sql, Object... params) throws Exception {
        T instance = null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtil.getConnection();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i + 1, params[i]);
            }
            rs = ps.executeQuery();

            //查询结果的元数据（列名，列类型，列数量...）
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();//得到结果中列的数量

            if (rs.next()) {//查看是否还有数据
                instance = clazz.newInstance();
                for (int i = 0; i < columnCount; i++) {//对一个记录进行赋值操作
                    Object value = rs.getObject(i + 1);//得到列的数值

                    //得到属性名并赋值，通过反射得到 前提：列名 = 类的属性名
                    String columnLabel = metaData.getColumnLabel(i + 1);//得到列的别名
                    Field field = clazz.getDeclaredField(columnLabel);//该field为类的属性
                    field.setAccessible(true);//关闭安全检测
                    field.set(instance, value);//赋值操作
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.closeResource(conn, ps, rs);
        }
        return instance;
    }

    //增删改操作
    public int update(String sql, Object... params) throws Exception {
        int rows = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtil.getConnection();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i + 1, params[i]);
            }

            rows = ps.executeUpdate();//执行修改，返回语句影响的行数
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.closeResource(conn, ps, rs);
        }
        return rows;
    }
}

 4.User本类，进行增删改查都是调用父类的代码，--提高了代码的维护性
    //查询所有的记录
    public List<User> check() throws Exception {
        String sql = "SELECT `id`,`name`,`password`,`address`,`phone` from `user`";
        return super.seekOperator(sql);
    }
    //查询一个记录
    public User checkOnly(Integer id) throws Exception {
        String sql = "SELECT `id` ,`name`,`password`,`address`,`phone` from `user` where id = ? ";
        return super.seekOnlyOperator(sql, id);
    }
    //插入操作
    public int insertOperator(User user) throws Exception {
        String sql = "insert into user(`name`, `password`, `address`, `phone`) values(?,?,?,?)";
        return super.update(sql, user.getName(), user.getPassword(), user.getAddress(), user.getPhone());
    }
    //删除操作
    public int deleteOperator(int id) throws Exception {
        String sql = "delete from user where id = ?";
        return super.update(sql, id);
    }
    //修改操作
    public int updateOperator(User user) throws Exception {
        String sql = "update `user` set `name`= ?, `password`=?,`address`=?,`phone`=? WHERE `id` = ?";
        return super.update(sql, user.getName(), user.getPassword(), user.getAddress(),
                user.getPhone(), user.getId());
    }
}

4.4 Po层代码

• 总的来说该层是设置表的字段

public class User {
    private Integer id;
    private String name;
    private String password;
    private String address;
    private String phone;
    public User() {
    }
    public User(Integer id, String name, String password, String address, String phone) {
        this.id = id;
        this.name = name;
        this.password = password;
        this.address = address;
        this.phone = phone;
    }
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getPassword() {
        return password;
    }
    public void setPassword(String password) {
        this.password = password;
    }
    public String getAddress() {
        return address;
    }
    public void setAddress(String address) {
        this.address = address;
    }
    public String getPhone() {
        return phone;
    }
    public void setPhone(String phone) {
        this.phone = phone;
    }
    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", password='" + password + '\'' +
                ", address='" + address + '\'' +
                ", phone='" + phone + '\'' +
                '}';
    }
}

4.5 Test层代码

• 对上述代码进行测试
• 快捷键 Ctrl + Shift + T

public class UserDaoTest {
    UserDao userDao = new UserDao();

    @Test
    public void check() throws Exception {//测试显示所有记录
        userDao.check().forEach(user -> {
            System.out.println(user);
        });
    }

    @Test
    public void checkOnly() throws Exception {//测试查找一个记录
        System.out.println(userDao.checkOnly(5));
    }

    @Test
    public void insertOperator() throws Exception {//测试插入记录
        User user = new User(null,"周星驰", "123", "香港", "110");
        userDao.insertOperator(user);
    }

    @Test
    public void deleteOperator() throws Exception {//测试删除记录
        userDao.deleteOperator(10);
    }

    @Test
    public void updateOperator() throws Exception {//测试更新记录
        User user = new User(11, "张学友", "123456", "中国", "32564565463");
        userDao.updateOperator(user);
    }
}

5. BLOB类型

• MySQL中BLOB是一个可以存储大量二进制的类型
• MySQL的四种BLOB类型(除了在存储的最大信息量上不同外，他们是等同的)
  • TinyBlob 最大255
  • Blob 最大 65K
  • MediumBlob 最大16M
  • LongBlob 最大4G

5.1 查看Blob

public class getImageBlob extends BaseDao {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        InputStream in = null;//读取流
        FileOutputStream out = null;//写入到指定文件中
        try {
            conn = JdbcUtil.getConnection();
            String sql = "select photo from customers where id = 16";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();

            if (rs.next()) {
                Blob blob = rs.getBlob(1);//获取结果中的第一个参数
                in = blob.getBinaryStream();//读取该类型的字节流
                out = new FileOutputStream("a.jpg");//写入到a.jpg文件中
                int length = -1;
                byte[] buff = new byte[1024];
                while ((length = in.read(buff))!=-1){
                    out.write(buff, 0, length);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {   //必要的关闭
            JdbcUtil.closeResource(conn, ps, rs);
            try {
                in.close();
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
            try {
                out.close();
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
        }
    }
}

5.2 写入Blob

public class insertBlob {
    public static void main(String[] args) {

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        FileInputStream in = null;
        try {
            conn = JdbcUtil.getConnection();
            String sql = "insert into customers(name,email,birth, photo) values(?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1,"mickey");
            ps.setString(2,"mickey@163.com");
            ps.setDate(3,new Date(new java.util.Date().getTime()));
            in = new FileInputStream("SQLJOIN.png");
            ps.setBlob(4, in);//直接将inputStream流写入到数据库
            ps.executeUpdate();//执行
        } catch (Exception e) {
            e.printStackTrace();
        } finally {//必要的关闭操作
            JdbcUtil.closeResource(conn, ps, rs);
            try {
                in.close();
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
        }
    }
}

5.3 更新Blob

public class UpdateBlob {
    public static void main(String[] args) {
        String sql = "update customers set photo = ? where id=?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        FileInputStream in = null;
        try {
            conn = JdbcUtil.getConnection();
            ps = conn.prepareStatement(sql);

            ps.setInt(2, 20);
            in = new FileInputStream("yeguang.png");//该文件替代先前的文件
            ps.setBlob(1, in);//直接将inputStream输入数据库中

            ps.executeUpdate(); //执行修改,返回语句影响的行数
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.closeResource(conn, ps, rs);
            try {
                in.close();
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
        }
    }
}

6. 批量插入

6.1 简介

• 当需要成批插入或者更新记录时，可以使用批量更新机制，该机制允许多条语句一次性提交给数据库批量处理。效率更高

• JDBC的批量处理语句包括下面三个方法：

  • addBatch(String)：添加多条SQL语句到Batch库中

  • executeBatch()：执行Batch库中的sql语句

  • clearBatch(): 清空Batch中的数据

• 批量处理的使用场景

  • 多条SQL语句的批量处理；

  • 一个SQL语句的批量传参；

6.2 案例展示

1.修改配置:
	url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&rewriteBatchedStatements=true
	注意：此处不能使用连接池。
2. 代码比较
public class test1 {
    @Test  //未使用批量插入操作，速度慢
    public void test1() throws Exception {
        long start = System.currentTimeMillis();//开始时间

        Connection conn = JdbcUtil.getConnection();
        conn.setAutoCommit(false);//打开事务，每次执行sql语句都要进行提交操作

        String sql = "insert into goods(name) values(?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        for (int i = 1; i <= 200000; i++) {//使用for循环对执行200000次提交操作
            ps.setString(1, "name_" + i);
            ps.executeUpdate();
        }
        conn.commit();  //提交操作
        long end = System.currentTimeMillis();
        System.out.println("花费的时间为：" + (end - start));//182340
        JdbcUtil.closeResource(conn, ps, null);
    }

        @Test  //使用批量插入操作，速度块
    public void test2() throws Exception {
        long start = System.currentTimeMillis();
        Connection conn = JdbcUtil.getConnection();
       
        conn.setAutoCommit(false);
        String sql = "insert into goods(name)values (?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        for (int i = 1; i <= 200000; i++) {
            ps.setString(1,"name" + i);
            ps.addBatch();//添加整条语句到batch中，填充一条存入一条
            if (i % 10000 == 0) {
                ps.executeBatch();//执行Batch中的SQL语句
                ps.clearBatch();//清空语句
            }
        }
        conn.commit();
        long end = System.currentTimeMillis();
        System.out.println("花费的时间为：" + (end - start));//5751
        JdbcUtil.closeResource(conn, ps, null);
    }
}

7. 连接池

7.1 必要性

在使用开发啊基于数据库的web程序时，传统的模式基本是按以下步骤： 
   1.建立数据库库连接，
   2.进行sql操作，
   3.断开数据库连接   

• 存在的问题
  • 每次向数据库建立连接的时候都要将 Connection 加载到内存中，再验证用户名和密码(得花费0.05s～1s的时间)，执行完成后再断开连接。使用这种方式将会消耗大量的资源和时间。数据库的连接资源并没有得到很好的重复利用。
  • **对于每一次数据库连接，使用完后都得断开。**否则，如果程序出现异常而未能关闭，将会导致数据库系统中的内存泄漏，最终将导致重启数据库。
  • 这种开发不能控制被创建的连接对象数，系统资源会被毫无顾及的分配出去，如连接过多，也可能导致内存泄漏，服务器崩溃。

7.2 简介

• 为解决传统开发中的数据库连接问题，可以采用数据库连接池技术。
• 数据库连接池的基本思想：就是为数据库连接建立一个“缓冲池”。预先在缓冲池中放入一定数量的连接，当需要建立数据库连接时，只需从“缓冲池”中取出一个，使用完毕之后再放回去。
• 数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是重新建立一个。
• 数据库连接池在初始化时将创建一定数量的数据库连接放到连接池中，这些数据库连接的数量是由最小数据库连接数来设定的。无论这些数据库连接是否被使用，连接池都将一直保证至少拥有这么多的连接数量。连接池的最大数据库连接数量限定了这个连接池能占有的最大连接数，当应用程序向连接池请求的连接数超过最大连接数量时，这些请求将被加入到等待队列中。

7.3 原理和优点

• 工作原理

• 连接池的优点
  • 数据库连接池技术的优点1. 资源重用由于数据库连接得以重用，避免了频繁创建，释放连接引起的大量性能开销。
  • 2. 更快的系统反应速度数据库连接池在初始化过程中，往往已经创建了若干数据库连接置于连接池中备用。此时连接的初始化工作均已完成。对于业务请求处理而言，直接利用现有可用连接，避免了数据库连接初始化和释放过程的时间开销，从而减少了系统的响应时间
  • 3. 新的资源分配手段对于多应用共享同一数据库的系统而言，可在应用层通过数据库连接池的配置，实现某一应用最大可用数据库连接数的限制，避免某一应用独占所有的数据库资源
  • 4. 统一的连接管理，避免数据库连接泄漏在较为完善的数据库连接池实现中，可根据预先的占用超时设定，强制回收被占用连接，从而避免了常规数据库连接操作中可能出现的资源泄露

7.4 多种连接池

• DBCP是Apache提供的数据库连接池。tomcat 服务器自带dbcp数据库连接池。速度相对c3p0较快，但因自身存在BUG，Hibernate3已不再提供支持。

• C3P0 是一个开源组织提供的一个数据库连接池，**速度相对较慢，稳定性还可以。**hibernate官方推荐使用

• Druid 是阿里提供的数据库连接池，据说是集DBCP 、C3P0 、Proxool 优点于一身的数据库连接池，但是速度不确定是否有BoneCP快

• DataSource 通常被称为数据源，它包含连接池和连接池管理两个部分，习惯上也经常把 DataSource 称为连接池。DataSource用来取代DriverManager来获取Connection，获取速度快，同时可以大幅度提高数据库访问速度。

• 特别注意：

  • 数据源和数据库连接不同，数据源无需创建多个，它是产生数据库连接的工厂，因此整个应用只需要一个数据源即可
  • 当数据库访问结束后，程序还是像以前一样关闭数据库连接conn.close()，但是conn.close()并没有关闭数据库的物理连接，它仅仅把数据库连接释放归还了数据库连接池

• C3P0数据库连接池

// 1.导入依赖--pom.xml
<dependency>
   <groupId>com.mchange</groupId>
   <artifactId>c3p0</artifactId>
   <version>0.9.2.1</version>
</dependency>
   
//2. resource下配置文件[c3p0-config.xml] 
<?xml version="1.0" encoding="UTF-8"?>
 <c3p0-config>
     <named-config name="helloc3p0">
         <!-- 获取连接的4个基本信息 -->
         <property name="user">root</property>
         <property name="password">abc123</property>
         <property name="jdbcUrl">jdbc:mysql:///test</property>
         <property name="driverClass">com.mysql.jdbc.Driver</property>
         
         <!-- 涉及到数据库连接池的管理的相关属性的设置 -->
         <!-- 若数据库中连接数不足时, 一次向数据库服务器申请多少个连接 -->
         <property name="acquireIncrement">5</property>
         <!-- 初始化数据库连接池时连接的数量 -->
         <property name="initialPoolSize">5</property>
         <!-- 数据库连接池中的最小的数据库连接数 -->
         <property name="minPoolSize">5</property>
         <!-- 数据库连接池中的最大的数据库连接数 -->
         <property name="maxPoolSize">10</property>
         <!-- C3P0 数据库连接池可以维护的 Statement 的个数 -->
         <property name="maxStatements">20</property>
         <!-- 每个连接同时可以使用的 Statement 对象的个数 -->
         <property name="maxStatementsPerConnection">5</property> 
     </named-config>
 </c3p0-config>  

            
// 3. 示例代码
public class c3p0 {
    //使用C3P0数据库连接池的方式，

    // 方式一：使用代码获取数据库的连接    
    @Test// 不推荐
    public void test1() throws Exception{
        ComboPooledDataSource cpds = new ComboPooledDataSource();
        cpds.setDriverClass("com.mysql.jdbc.Driver");
        cpds.setJdbcUrl("jdbc:mysql://localhost:3306/test");
        cpds.setUser("root");
        cpds.setPassword("123456");

        //  cpds.setMaxPoolSize(100);
        Connection conn = cpds.getConnection();
        System.out.println(conn);
    }
    //方式二：
    //使用配置文件【c3p0-config.xml】
    @Test
    public void test2() throws SQLException {
        DataSource cpds = new ComboPooledDataSource("hellc3p0");
        Connection conn = cpds.getConnection();
        System.out.println(conn);
    }
    /**
     * 使用时只需要 与数据库的连接方式进行修改即可，
     * 注意： conn.close()操作是将连接还给池子，并不是关闭连接
     */
}          

• DBCP数据库连接
  • Tomcat 的连接池正是采用该连接池来实现的

配置属性名   默认值   描述
---------------------------
initialSize	0	   连接池启动时初始化连接数量
maxActive	8	   连接池中可同时连接的最大的连接数
maxIdle	   8  	连接池中最大的空闲的连接数，超过的空闲连接将被释放，如果设置为负数表示不限制
minIdle	   0	   最小的空闲连接数，低于该值会创建新的。该参数越接近maxIdle性能越好，因为创建和销毁都需要消耗资源；
maxWait	 无限制	 最大等待时间，当没有连接时，任务程序进入等待，超过该时间限制会抛出异常，如果设置-1表示无限等待
poolPreparedStatements	       false  开启池的Statement是否prepared
maxOpenPreparedStatements	    无限制  开启池的prepared 后的同时最大连接数
minEvictableIdleTimeMillis		  null  连接池中连接，  在时间段内一直空闲， 被逐出连接池的时间
removeAbandonedTimeout	        300   超过时间限制，回收没有用(废弃)的连接
removeAbandoned	             false  超过removeAbandonedTimeout时间后，是否进 行没用连接（废弃）的回收   

//1. 导入依赖--pom.xml
<dependency>
    <groupId>org.apache.tomcat</groupId>
    <artifactId>dbcp</artifactId>
    <version>6.0.29</version>
</dependency>

//2. 配置文件resources下的文件【dbcp.properties】  
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///test
username=root
password=123456
initialSize=10  //初始连接数量 
   
//3. 示例代码
public class Dbcp {
   
    //方式一 通过代码获取
    //不推荐
    @Test
    public void test1() throws SQLException {
        //创建了DBCP的数据库连接池
        BasicDataSource source = new BasicDataSource();

        //设置基本信息
        source.setDriverClassName("com.mysql.jdbc.Driver");
        source.setUrl("jdbc:mysql:///test");
        source.setUsername("root");
        source.setPassword("123456");

        //还可以设置其他涉及数据库连接池管理的相关属性：
        source.setInitialSize(10);
        source.setMaxActive(10);
        //。。。
        Connection conn = source.getConnection();
        System.out.println(conn);
    }

    //方式二：同样使用配置文件
    // 推荐
    @Test
    public void testGetConnection1() throws Exception{
        Properties pros = new Properties();

        //方式1：
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("dbcp.properties");
        //方式2：
//		FileInputStream is = new FileInputStream(new File("src/dbcp.properties"));
        pros.load(is);
        DataSource source = BasicDataSourceFactory.createDataSource(pros);

        Connection conn = source.getConnection();
        System.out.println(conn);
    }
}

• Druid(德鲁伊)数据库连接
  • Druid是阿里巴巴开源平台上一个数据库连接池实现，它结合了C3P0、DBCP、Proxool等DB池的优点，同时加入了日志监控，可以很好的监控DB池连接和SQL的执行情况，可以说是针对监控而生的DB连接池，可以说是目前最好的连接池之一.
  • 配置参数

配置参数名         默认值            描述
name		                配置这个属性的意义在于，如果存在多个数据源，监控的时候可以通过名字来区分开来。 
                         如果没有配置，将会生成一个名字，格式是：”DataSource-” + System.identityHashCode(this)
url		                连接数据库的url，不同数据库不一样。例如：mysql : jdbc:mysql://10.20.153.104:3306/
                              druid2 oracle :jdbc:oracle:thin:@10.20.149.85:1521:ocnauto
username		             连接数据库的用户名
password		             连接数据库的密码。如果你不希望密码直接写在配置文件中，可以使用ConfigFilter。
driverClassName		    根据url自动识别 这一项可配可不配，如果不配置
                              druid会根据url自动识别dbType，然后选择相应的driverClassName(建议配置下)
initialSize	       0	    初始化时建立物理连接的个数。初始化发生在显示调用init方法，或者第一次getConnection时
maxActive	       8	    最大连接池数量
maxIdle	          8   	 已经不再使用，配置了也没效果
minIdle		             最小连接池数量
maxWait		             获取连接时最大等待时间，单位毫秒。配置了maxWait之后，缺省启用公平锁，并发效率会
                              有所下降，如果需要可以通过配置useUnfairLock属性为true使用非公平锁。
                                 
poolPreparedStatements	   false	  是否缓存preparedStatement，也就是PSCache。PSCache对支持游标的数据库性能提
                                 		升巨大，比如说oracle。在mysql下建议关闭。
maxOpenPreparedStatements	-1	     要启用PSCache，必须配置大于0，当大于0时，poolPreparedStatements自动触发修改
                                 		为true。在Druid中，不会存在Oracle下PSCache占用内存过多的问题，可以把这个
                                 		数值配置大一些，比如说100用来检测连接是否有效的sql，要求是一个查询语句。如果
                                 		validationQuery为null，testOnBorrow、testOnReturn、testWhileIdle都
                                 		不会其作用。
testOnBorrow	            true	  申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。
testOnReturn	            false	  归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能
testWhileIdle	            false	  建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于
                                 		timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。
timeBetweenEvictionRunsMillis		  有两个含义： 1)Destroy线程会检测连接的间隔时间2)testWhileIdle的判断依据，
                                 		详细看testWhileIdle属性的说明
numTestsPerEvictionRun		        不再使用，一个DruidDataSource只支持一个EvictionRun
minEvictableIdleTimeMillis		
connectionInitSqls		           物理连接初始化的时候执行的sql
exceptionSorter		              根据dbType自动识别 当数据库抛出一些不可恢复的异常时，抛弃连接
filters		                       属性类型是字符串，通过别名的方式配置扩展插件，常用的插件有： 监控统计用
                                 		的filter:stat日志用的filter:log4j防御sql注入的filter:wall
proxyFilters		                 类型是List，如果同时配置了filters和proxyFilters，是组合关系，并非替换关系

//导入依赖-- pom.xml
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.8</version>
</dependency>
   
//2. 配置文件resources下的文件【druid.properties】
url=jdbc:mysql:///test
username=root
password=123456
driverClassName=com.mysql.jdbc.Driver
initialSize=10
maxActive=10  
   
   
//3. 示例文件
public class Druid {
    //性能最好，最推荐使用
    @Test
    public void test1() throws Exception {
        Properties pros = new Properties();

       //读取文件
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
        pros.load(is);//加载文件

        DataSource source = DruidDataSourceFactory.createDataSource(pros);
        Connection conn = source.getConnection();//获取链接
        System.out.println(conn);
    }
}  

8. 数据库事务

8.1 简介

• 事务：一组逻辑操作单元，使数据从一种状态变换到另一种状态
• 事务处理：保证所有事务都为一个工作单元来执行，即使出现了故障，都不会改变这种执行方式。当一个事务执行多个操作时：
  • 要么所有事务都被提交（commit）了，那么这些修改就永久保留下来;
  • 要么数据库管理系统将放弃所有的修改，整个事务回滚（rollback）到最初状态。
• 为确保数据库中数据的一致性，数据的操纵应当是离散的成组的逻辑单元：当它全部完成时，数据的一致性可以保持，而当这个单元中的一部分操作失败，整个事务应全部视为错误，所有从起始点以后的操作应全部回退到开始状态。

8.2 JDBC事务处理

• 数据一旦提交，就不可回滚。
• 数据在什么时候提交；当一个连接对象被创建时，默认情况下是自动提交事务，每执行一个sql语句，如果执行成功自动提交，发生异常则回滚；关闭数据库时数据会自动提交，同一个事务的多个操作必须在同一个连接下。
• JDBC程序中为了让多个sql语句作为一个事务执行
  • 调用Connection对象的setAutoCommit(false):以取消自动提交事务
  • 在所有的sql语句都成功执行后，调用commit()；方法提交事务
  • 在出现异常时，调用rollback(true)。尤其是在使用数据库连接池技术时，执行close()方法前，建议恢复自动提交状态。

// 案例  用户AA向用户BB转账
public class Test1 {
    //案例 AA向BB转账1000元
    @Test
    public void test1() {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JdbcUtil.getConnection();
            // 2.开启事务 关闭自动提交
            conn.setAutoCommit(false);
            // 3.进行数据库操作
            String sql1 = "update user_table set balance = balance - 1000 where user = ?";
            ps = conn.prepareStatement(sql1);
            ps.setString(1, "AA");
            ps.executeUpdate();

            // 模拟网络异常
            int i = 10 / 0;

            String sql2 = "update user_table set balance = balance + 1000 where user = ?";
            ps = conn.prepareStatement(sql2);
            ps.setString(1, "BB");
            ps.executeUpdate();

            // 4.若没有异常，则提交事务
            conn.commit();
        } catch (Exception e) {
//            e.printStackTrace();
            System.out.println("错误原因：" + e.getMessage());
            try {
                conn.rollback();// 5.若有异常，则回滚事务
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } finally {
            try {
                ps.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            try {
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
}

8.3 事务的ACID属性

1. 原子性(Atomicity)：原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

2. 一致性（Consistency）事务必须使数据库从一个一致性状态变换到另外一个一致性状态。

3. 隔离性(Isolation)：事务的隔离性是指一个事务的执行不能被其它事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

4. 持久性（Durability）持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

8.4 隔离级别

• 数据库的并发问题

  • 对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采取必要的隔离机制，就会导致各种并发问题；
  • 问题一脏读：对于两个事务 T1, T2； T1 读取了已经被 T2 更新但还没有被提交的字段。之后, 若 T2 回滚, T1读取的内容就是临时且无效的。
  • 问题二不可重复读：对于两个事务T1, T2； T1 读取了一个字段, 然后 T2 更新了该字段。之后, T1再次读取同一个字段, 值就不同了。
  • 问题三幻读：对于两个事务T1, T2；T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行。之后, 如果 T1 再次读取同一个表, 就会多出几行。

• 数据库事务的隔离性：数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题。

• 事务与事务隔离的程度称为隔离级别。数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱。

• 四种隔离级别（READ UNCOMMITED，READ COMMITED，REPEATABLE READ，SEPIALIZABLE）

隔离等级	描述
读未提交	允许事务读取未被其他事物提交的变更.脏读,不可重复读和幻读的问题都会出现
读已提交	只允许读已经被其它事务提交的数据.可以避免脏读,但免不了不可重复读和幻读（Oracle）
可重复读	确保事务可以多次读取相同的值.在事务持续期间,禁止其他事务的更新.可以避免脏读和不可重复读,但免不了幻读
串行化	所有问题都可以避免,但性能十分低下

• mysql 中设置隔离级别
  • 每启动一个 mysql 程序, 就会获得一个单独的数据库连接. 每个数据库连接都有一个全局变量 @@tx_isolation, 表示当前的事务隔离级别。
  • 查看当前的隔离级别select @@transaction_isolation;
  • 设置当前mysql连接的隔离级别set session transaction isolation level read committed; 读已提交
  • mysql 默认是在可重复读

8.5 隔离实验

1. 读未提交
   
2. 读已提交

• 事务2未提交时
  

• 事务2提交后

3. 可重复读

• 事务1未提交时
  

• 事务1提交后
  

4. 可重复读中产生幻读
   

9. MVCC

9.1 简介

• MVCC（Multi-Version Concurrency Control）多版本并发控制，是⽤来在数据库中控制并发的⽅法，主要是解决：读时写的问题。

• MVCC就是⼀种写时复制（cow）的思想的应⽤

• MVCC只在读取已提交（Read Committed）和可重复读（Repeatable Read）两个事务级别下有效

• 通过Undo⽇志中的版本链和ReadView⼀致性视图来实现的。

• MVCC是在多个事务同时存在时SELECT语句查寻到具体是版本链上的哪个版本，然后在找到的版本上返回其中所记录的数据的过程。

• 在MySQL中默认添加三个隐藏字段

  • DB_ROW_ID: 行id （该列和MVCC关系不大）
  • DB_TRX_ID：事务id 记录的是当前事务在左inserthe update delete语句时的事务ID
  • DB_ROLL_PTR: 回滚指针，通过它可以将不同的版本串联起来，形成版本链，相当于链表的next指针

9.2 ReadView

• ReadView⼀致性视图主要是由两部分组成：所有未提交事务的ID数组和已经提交的最⼤事务ID组成。如：[100,200],300

  • 事务100和200是当前未提交的事务，事务300是提交的最⼤事务。

• 当执⾏SELECT语句的时候会创建ReadView，但是在读已提交和可重复读两种事务级别下，⽣成一致性视图的策略是不⼀样的，

  • 读已提交级别：每执⾏⼀次SELECT语句就会重新⽣成⼀份ReadView，
  • 可重复读级别：只会在第⼀次SELECT语句执⾏的时候会⽣成⼀份，后续SELECT语句会沿⽤之前⽣成的ReadView。

9.3 版本链

• 所有版本的数据都只会存一份，然后通过回滚指针连接起来，之后就是通过一定的规则找到具体是哪一个版本的数据就行了，
• 假如现在有一张account表，其中有id和name两个字段。版本链的示意图如下：
  

​

• 对比规则：首先从版本链中拿出最上面的事务ID开始逐个往下进行比对：

min_id指向ReadView中未提交事务数组中的最小事务ID
max_id指向ReadView中已提交的最大事务ID
   - 如果落在绿色区域：说明这个版本在select之前就已经提交了，
   - 如果落在红色区域：说明这个版本是由将来启动的事务来生成的，当前还未开始
   - 如果落在黄色区域：这时就需要进行判断了：
       - 如果这个版本的事务ID在ReadView的未提交事务数组中，这个版本是未提交事务，不可见
       - 如果这个版本的事务ID在ReadView的提交事务数组中，这个版本是已提交事务。那么就是可见。

• 如果发现当前版本是不可见的，那么就继续从版本链中通过回滚指针拿去下一个版本来进行上述的判断。

9.4 演示过程

• 下面通过一个实例来具体演示MVCC的执行过程(前提：可重复读),当前account表中已经有了一条初始数据(id=1,name= monkey);

序号	Transaction100	Transaction200	Transaction300	无事务ID	无事务ID
1	begin:	begin:	begin:	begin:	begin:
2	update test set a=‘1’;where id=1;
3		update test set a= ‘2’ where id = 2;
4			update account set name = ‘monkey301’ where id = 1;
5			commit;
6				select name from account where id = 1;
7	update account set name = ‘monkey101’ where id = 1;
8	update account set name = ‘monkey102’ where id = 1;
9				select nam from account where id = 1;
10	commit;	update account set name = ‘monkey201’ where id = 1;
11		update account set name = ‘monkey202’ where id = 1;
12				select name from account where id = 1;	select name from account where id=1;
13		commit;

1. 从左往右分别是五个事务，从上到下为时刻点。在第2和3时刻点中事务100和事务200（正常的事务ID是以+1的方式创建的）分别执行了update语句。这两条语句并无实际的意义。只是为了生成事务ID（现在只研究account表）事务ID是执行一条更新操作(增删改)的语句后才会生成，执行查询语句不悔产生事务ID。
2. 初始状态时 版本链和ReadView ；ReadView只在修改表时生成。此时版本链中只有一条初始数据

• 第1时刻点 五个事务分别开启了事务，但还没有生成事务ID
• 第2时刻点 第一个事务执行了一条update语句，生成了事务ID 100；
• 第3时刻点 第二个事务执行了一条update语句，生成了事务ID 200；
• 第4时刻点 第三个事务执行了一条update语句，修改了account的记录，同时生成事务ID 300；
• 第5时刻点 事务300执行了commit操作
• 第6时刻点 第四个事务执行了一条select语句，此时版本链和ReadView如下：

因为在第5时刻点，事务300已经commit了，所以ReadView的未提交事务数组中不包含它
此时拿版本链的第一个版本的事务ID为300进行对比，发现是落在黄色区域，事务300没有在未提交事务数组中，所以是可见的
即此时在第6时刻点，第四个事务所查询的结果为monkey301。

• 第7时刻点 事务100执行了一条update语句，修改了account的记录
• 第8时刻点 事务100执⾏了⼀条UPDATE语句，修改了account的记录
• 第9时刻点 第四个事务执行了一条select语句，此时版本链和ReadView如下(省略初始版本)：

此时实在可重复读的事务级别下，所有此时的ReadView还是使用了第6时刻点的Readview
//在读已提交事务等级下会重新生成一份新的ReadView
此时未提交事务ID由[100,200],已提交事务ID为300
拿版本链的第一个版本的事务ID为100进行对比，发现是落在黄色区域，并且在未提交事务数组中， 所以是不可见的
此时通过回滚指针拿去下一个版本，发现事务ID还是100，不可见，
此时又拿取下一个版本，事务ID为300，300是落在黄色区域并没有在未提交事务数组中，因此是可见的。
即此时在第9时刻点，第四个事务所查询的结果仍为monkey301。

• 第10时刻点 事务100执行了commit，同时事务200执行了一条update语句，修改了account表的记录
• 第11时刻点 事务200执行了一条update语句，修改了account表的记录
• 第12时刻点 第四个事务执行了一条select语句，此时版本链和ReadView如下(省略初始版本)：

在可重复读的事务级别下，ReadView依旧使用第6时刻点的ReadView，
//在读已提交事务等级下会重新生成一份新的ReadView
此时未提交事务数组依旧是[100,200] 已提交事务ID 300；
拿版本链的第一个版本的事务ID为200进行对比，发现是落在黄色区域并且是在未提交事务数组中， 所以是不可见的
通过回滚指针拿去下一个版本，发现事务ID还是200，不可见，又取下一版本是100不可见，以此类推。
此时又拿取下一个版本，事务ID为300，300是落在黄色区域并没有在未提交事务数组中，因此是可见的。
即此时在第12点，第四个事务所查询的结果仍为monkey301。 
   
与此同时 第五个事务执行了一条select语句。因为第个五事务是第一次执行，所以会生成新的ReadView，如下

此时 事务100和事务300都已提交，所以未提交事务数组中为[200],而已提交的最大事务ID为300，所以ReadView为[200],300;
//现在依旧是在可重读读事务登记下，规则依旧相同
拿取版本链的第一个事务ID为200进行比较，发现落在黄色区域并出现在未提交事务数组中，不可见
通过回滚指针拿去下一个版本，事务ID为200，不可见，又取下一个版本，事务ID为100，发现它落在绿色区域，所以是可见的。
即此时在第12时刻点的第五个事务查询结果为monkey102；

• 第13时刻点 事务200执行了commit操作，整段分析过程结束。

10. Mysql索引

10.1 索引的分类

存储形式	数据约束	索引列的数量	innoDB索引
聚簇索引	主键索引	单列索引	主键索引
非聚簇索引	唯一索引	组合索引	唯一索引
	非唯一索引		普通索引

• 聚簇索引：索引组织表中数据也是索引的一部分;主键索引也被称为聚簇索引

• 非聚簇索引：索引文件和数据文件是分离的，查找数据需要进行回表

10.2 索引的数据结构

• 二叉查找树

• btree(b-tree)

btree缺点：
我们从图上可以看到，每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节
点(即一个页)能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率

• b+tree
  • 每次读取磁盘块都要进行一次 磁盘IO

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一
层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

• 聚簇索引
  

• 非聚簇索引

首先得到name=’list‘的id 为8，再通过聚簇索引树搜索一次。这个过程称为回表。
也就是说，非聚簇索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

• 如何做索引维护

B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护。以上面这个图为例，

如果插入新的行ID值为700，则只需要在R5的记录后面插入一个新记录。
如果新插入的ID值为400，就麻烦了,需要逻辑上挪动后面的数据,空出位置。
而更糟的情况是，如果 R5 所在的数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。
	这个过程称为页分裂。在这种情况下，性能自然会受影响。除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分
   到两个页中，整体空间利用率降低大约50%。
有分裂就有合并。当相邻两个页由于删除了数据,利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。

页分裂

• 自增主键的作用

你可能在一些建表规范里面见到过类似的描述，要求建表语句里一定要有自增主键。
当然事无绝对，我们来分析一下哪些场景下应该使用自增主键，而哪些场景下不应该。
自增主键是指自增列上定义的主键，在建表语句中一般是这么定义的： NOT NULLPRIMARY KEY AUTO_INCREMENT。
   
1）从性能上的考虑
插入新记录的时候可以不指定ID的值，系统会获取当前ID最大值加1作为下一条记录的ID值。也就是说，自增主键的插入数据模式，正符合了
   我们前面提到的递增插入的场景。每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。而有业务
   逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高。
2）从存储空间角度
我们可以从存储空间的角度来看。假设你的表中确实有一个唯一字段，比如字符串类型的身份证号，那应该用身份证号做主键，还是用自增字段
   做主键呢？由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键，那么每个二级索引的叶子节点占用约 20 个字节，
   而如果用整型做主键，则只要 4 个字节，如果是长整型（bigint）则是 8 个字节。显然，主键长度越小，普通索引的叶子节点就越小，
   普通索引占用的空间也就越小。
所以，从性能和存储空间方面考量，自增主键往往是更合理的选择。

• 回表查询

  • 回表查询是指在使⽤⾮聚簇索引进⾏查询时，当需要获取的数据列不在索引中时，MySQL 需要通过索引的指针回到主索引获取缺失的数据列。

  • 在回表查询中，⾸先根据⾮聚簇索引定位到符合条件的索引记录，然后通过索引中的指针获取主索引中的相应数据列。因此相对于聚簇索引，回表查询增加了开销和响应时间。

  • 回表查询可能会对性能产⽣影响，尤其是大数据和高并发。因此为了减少回表查询，可以考虑用覆盖索引进行优化，

• 覆盖索引

  • 覆盖索引是指在查询过程中，索引包含了查询所需的所有数据列，⽆需回表查询主索引。从⽽提⾼查询性能和效率。

• 相对于回表查询，覆盖索引则避免了查询的开销，因为索引本身就包含了查询所需的所有数据列。

覆盖索引优点：
1.提⾼查询性能：由于覆盖索引能够直接提供查询所需的数据，减少了磁盘的随机访问和回表查询，从⽽加快了查询的执⾏速度。
2.减少磁盘 I/O：回表查询需要进⾏额外的磁盘IO操作，⽽覆盖索引不需要，降低系统的磁盘负载。
3.减少内存消耗：覆盖索引可以减少需要加载到内存中的数据量，节省了内存的使⽤，提⾼了查询的效率

常见覆盖索引
1.要创建覆盖索引，需要选择适当的索引列，需要考虑各种因素。
2.需要注意的是，创建过多的覆盖索引可能会增加索引的维护成本和存储空间占⽤。
3.总之，覆盖索引是⼀种优化⼿段，避免了回表查询，提⾼了查询的效率，但需要权衡索引的设计和维护成本

11. 查询优化

11.1 优化器简介

• 查询优化器是MySQL中的⼀个关键组件，它负责分析查询语句并⽣成最优的查询执行计划。

• 查询优化器根据查询的多种因素，评估不同的执⾏计划，并选择代价最低的执⾏计划来执⾏查询。

• MySQL的expiain 命令可以对 SELECT 语句的执⾏计划进⾏分析, 并输出 SELECT 执⾏的详细信息, 以供⼈员进行优化.

• explain命令来查看该SQL语句有没有使⽤上了索引,有没有做全表扫描。

11.2 explain参数

• id select查询的标识符，每个select都会自动分配一个唯一的标识符
• select_type select查询的类型 simple普通查询 primary带union或子查询
• table 查询那个表 使用别名显示别名，表示临时表
• partitions 匹配那个分区
• type 访问类型
• possible_keys 可能选用的索引
• key 确切用到的索引
• ref 那个字段与key一起被使用
• rows 扫描了多少行
• filtered 过滤的数据的百分比
• extra 额外信息 Using index不需要回表 Using where过滤条件无索引

11.3 用户搜索

覆盖索引优化

1. 不使用索引查询，电商后台管理系统通常需要根据用户名称、手机号。地址搜索相关用户信息、常见的查询sql语句如下：select * from my_customer where phone like '%157%'

• 使用explain的输出结果，可以了解查询的执行计划、访问方式和可能存在的问题。可以根据字段信息优化语句来提高性能explain select * from my_customer where phone like '%157%'
• 结果为：type类型为ALL，全盘扫描99w行。表示全盘扫描会导致查询效率低，耗时长。

2. 使用索引查询，应该考虑为查询字段加上索引，如phone字段

//CREATE index 索引名 using btree ON 库名.表名(字段名);
CREATE INDEX my_customer_phone_IDX USING BTREE ON store.my_customer(phone); 
注意：模糊匹配查询使用 % 在开头会导致索引失效，改为以%结尾的模糊匹配
explain select * from `my_customer` where phone like '157%';   

• 可看出sql执行过程中使用到了索引，扫描了10w行。所以在实际开发中应该避免使⽤ * ,只选择需要的字段，可以提升查询效率。

3. 当需要⽤户⼿机号查询id和姓名, sql语句为select id, name from my_customer where phone like '157%';这时肯定需要用到回表查询，接下来进行优化。

drop index 索引名 on 数据库名.表名  #删除索引
DROP INDEX my_customer_phone_IDX on store.my_customer;
#创建新的索引
CREATE INDEX my_customer_phone_IDX USING BTREE ON store.my_customer(phone,name);
# 查看执行计划 
explain select * from `my_customer` where phone like '157%';
explain select id, name from my_customer where phone like '157%'; 

• 从下面的表格中可以看到Extra字段包好Using index,表明使用了索引覆盖，并没有进行回表查询。同理select count(name) from my_customer where phone like '157%';覆盖索引也会生效。

4. 创建索引create index idx on table(A,B,C)，这种就是联合索引，联合索引的所有索引项都会出现在索引上。如果第⼀个索引项相同的话才会去看第⼆个：因此必须要有第一个索引，没有头索引那个该联合索引就不会生效。这就是最左匹配法则,举例说明

创建组合索引组合索引（quantity，total_price，order_status）#三个索引列为组合索引
select * from user where total_price=770 AND order_status=3 #跳过了age这列，索引失效。


#有效的组合(必须要有头索引)
explain select * from my_order where quantity = 10 and total_price=770;
explain select * from my_order where quantity = 10 and order_status=3;
#无效的索引组合
explain select * from my_order where total_price=770 and order_status=3;
explain select * from my_order where order_status=3;

11.4 订单查询

连接查询优化

1. 我们通常会先给商品表加个以品牌字段作为索引：
   • CREATE INDEX my_product_brand_IDX USING BTREE ON store.my_product (brand);

• select * from my_order mo where product_id in (select id from my_product mp where brand = 'Apple');sql查询耗时6000ms查看执行计划（子查询）

• 可以看到订单表的查询使用了全盘扫描。我们可以个订单添加索引字段

• CREATE INDEX my_order_product_id_IDX USING BTREE ON store.my_order(product_id);查看执行计划
  

• 这⾥，我们可以看到两条计划都⽤到了 prodcut_id 字段索引，加快了查询效率。

2. 通常情况下，我们不使用子查询而是使⽤join⽅法替代的⼦查询，这样更加简洁和⾼效。
3. 使用内连接的方式修改上面子查询的代码

SELECT mo.id as orderId, mo.customer_id as customerId, mp.name as productName, 
	mo.order_status as orderStatus FROM my_order mo JOIN my_product mp 
	ON mo.product_id = mp.id WHERE mp.brand = 'Apple';
#虽然多表连接查询（多表 JOIN）是常⻅的查询⽅式之⼀，但是⼀旦join涉及到的数据量很⼤效率就很难保证，
#这种情况下强烈推荐分别根据索引单表取数据，然后在应⽤层⾥⾯做join，merge数据。	

11.5 分页查询

自连接优化

#典型的分页查询方式为
SELECT mo.id as orderId, mo.customer_id as customerId, mo.order_status as orderStatus 
	FROM my_order mo where mo.order_status = 1 
	order by mo.id asc limit 1000000, 10#查询1000000行数据后的10条数据。

• 运行方式：先遍历了前1000000个，然后取了第1000000到1000010个，舍弃了前1000000个, 可想而知性能有多差；查看他的执行计划。

• 我们可以使用索引进行优化；分⻚查询到第1000000条数据，订单ID为9397780，那么下个分⻚的所有订单ID都是⼤于9397780

SELECT mo.id as orderId, mo.customer_id as customerId, mo.order_status as orderStatus 
	FROM my_order mo join (select id from my_order where id >9397780 and order_status = 1 limit 10) 
	mo2 on mo.id = mo2.id order by mo.id asc
#该代码思路：1.先找到id>9397780并且order_status = 1的10个数据的id表   2.将订单mo和这10个数据进行自联，条件是id相等再排序。

• ⾸先⼦查询根据主键索引，获取最多10条订单ID, 然后再根据这10条id 获取数据详情。不需要再查询上百万条数据后排序取所需⼏⾏数据。

11.6 订单统计

并发计算优化

1. 电商平台经常需要从多个维度统计订单数据，记录等等数据。假设需要查询不同商品的订单数和订单总额。sql语句如下

select mo.product_id , count(*) as num , sum(mo.total_price) from my_order mo group by mo.product_id

2. 对于分组查询进行优化的思路

1.使用合适的索引     2.缓存结果集
3.预聚合数据        4.合理设置分组字段
5.考虑并行计算

• 我们在前面已经对product_id 字段加了索引，所以可以根据并⾏计算和在应⽤层聚合数据。

代码如下：
select mo.product_id , count(*) as num , sum(mo.total_price) 
	from my_order mo where mo.product_id between 1000 and 2000 
	group by mo.product_id;
#这⾥仅对商品ID在（1000,2000）范围内的订单进⾏统计，可以分多次查询不同的数据。