04_连接池 & 索引

连接池

连接池是创建和管理一个连接的缓冲池的技术，这些连接准备好被任何需要它们的线程使用。

连接池的优点

• 减少连接创建时间
• 简化编程模式
• 受控的资源使用

自己手动实现简易的连接池

主要实现功能：

• 对外提供连接
• 连接使用完成后，回收连接

eg：

{
    private static List<Connection> connections;


    private static String driverClassName;
    private static String url;
    private static String username;
    private static String password;

    static {
        connections = new ArrayList<>();

        Properties properties = new Properties();
        try {
            properties.load(new FileInputStream("db.properties"));
        } catch (IOException e) {
            throw new RuntimeException(e);
        }

        driverClassName = properties.getProperty("driverClass");
        url = properties.getProperty("url");
        username = properties.getProperty("username");
        password = properties.getProperty("password");

        try {
            Class.forName(driverClassName);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }

        for (int i = 0; i < 5; i++) {
            try {
                connections.add(DriverManager.getConnection(url, username, password));
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    /**
     * 获取连接的方法，如果连接池为空，则直接返回null。
     *
     * @return 连接池对象
     */
    public static Connection getConnection() {
        if (connections.isEmpty()) {
            return null;
			
			// 如果collection为空，再创建5个
			// 注：要把return null删掉
			for(int i = 0; i < 5; ++i){
				connections.add(DriverManager.getConnection(url, username, password));
			}
        }
        return connections.remove(connections.size() - 1);
    }


    // 归还连接给连接池
    public static void returnConnection(Connection connection) {
        connections.add(connection);
    }

}

简易的连接池实现的功能与未实现的功能：

• 实现的功能
  • 实现了从容器里获取连接的功能
  • 扩容
• 未实现的功能
  • 初始化容量、扩容的方法可以配置化.properties
  • 没有实现受限的资源使用

第三方提供的连接池

DataSource

是JDBC提供的一个连接池的接口，主要的方法就是getConnection()

DBCP

网址：https://commons.apache.org/proper/commons-dbcp/index.html

实现流程：

• 导包
  • 需要两个包：commons-dbcp、commons-pool
  • 去这个网址搜这两个包：https://mvnrepository.com/
  • 
• 设置配置文件

username=root
password=123456
url=jdbc:mysql://localhost:3306/test
driverClassName=com.mysql.jdbc.Driver
# 配置的参数。可以不用在url后面写。以分号分割
connectionProperties=characterEncoding=utf8;useSSL=false
initialSize=10

• 举例
  • 先将查询语句封装成一个类
  • 然后加入到主函数当中

public class DemoQurey {

    // 主要就是传入一个Connection，用它来执行一下SQL，检查好坏
    public static void query(Connection connection) throws SQLException {

        Statement statement = connection.createStatement();

        ResultSet resultSet = statement.executeQuery("select * from t_staff");

        ResultSetMetaData metaData = resultSet.getMetaData();

        int columnCount = metaData.getColumnCount();
        while (resultSet.next()) {
            List<String> list = new ArrayList<>();

            for (int i = 0; i < columnCount; i++) {
                String value = resultSet.getString(i);

                list.add(value);
            }

            System.out.println(list);
        }
    }
}

/**
 * org/apache/commons/pool/KeyedObjectPoolFactory
 * 出现这种异常是两种情况：
 * 1. 没导包
 * 2. 包冲突了
 */

public class Demo {
    public static void main(String[] args) throws SQLException {
        DataSource dataSource = null;

        try {
            Properties properties = new Properties();
            // 不建议对于.properties文件写绝对路径
            // 会去workspace下面找
            properties.load(new FileInputStream("jdbc.properties"));
            dataSource = BasicDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

        // dataSource 是一个数据库连接池接口
        Connection connection = dataSource.getConnection();

        // 执行一些查询
        DemoQurey.query(connection);
    }
}

为什么连接池没有给我们留returnConnection类似的方法？

• 如果返回了JDBC4Connection这种对象，直接close掉，那连接池就没有作用了，所以直接重写了Connection的close方法。
• 在子类的Connection中，直接把close方法重写成了往容器里面还数据

---

C3p0

dbcp断更后。出来的一个新的数据库连接池

实现流程：

• 导包
  • c3p0-0.9.5.5.jar
  • mchange-commons-java-0.2.19.jar
• 配置
  • 需要在src目录下，新建一个配置文件
  • 方法一：xml文件
    • 名字固定为c3p0-config.xml
  • 方法二：.properties文件
    • 名字固定为c3p0.properties

方法一：

<c3p0-config>
    <default-config>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property
                name="jdbcUrl">jdbc:mysql://localhost:3306/test?
                characterEncoding=utf8&amp;useSSL=false</property>
        <property name="user">root</property>
        <property name="password">123456</property>

        <property name="checkoutTimeout">30000</property>
        <property name="idleConnectionTestPeriod">30</property>
        <property name="initialPoolSize">10</property>
        <property name="maxIdleTime">30</property>
        <property name="maxPoolSize">100</property>
        <property name="minPoolSize">10</property>

    </default-config>
</c3p0-config>

方法二：

c3p0.driverClass=com.mysql.jdbc.Driver
c3p0.jdbcUrl=jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=false
c3p0.user=root
c3p0.password=123456

• 主函数

 static DataSource cpds;

    static {
      // 会自动去类路径下，会找指定的配置文件
      cpds = new ComboPooledDataSource();
    }
    

注意：c3p0的配置文件位置，都是写死在c3p0的代码里，所以配置文件的名字和位置都只能是固定的。

---

Druid

阿里开源的数据库连接池。

实现流程：

• 导包
  • druid-1.2.16.jar
• 配置文件.properties

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=false
username=root
password=123456

• 主函数

Properties properties = new Properties();
properties.load(new FileInputStream("druid.properties"));

DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
Connection connection = dataSource.getConnection();

---

HikariCP

Springboot的默认数据库连接池

实现流程：

• 导包
  • HikariCP-4.0.3.jar
  • slf4j-api-1.7.30.jar
• 配置文件.properties

jdbcUrl=jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=false
username=root
password=123456

• 主函数

Properties properties = new Properties();
properties.load(new FileInputStream("hikaricp.properties"));

HikariConfig config = new HikariConfig(properties);
HikariDataSource hikariDataSource = new HikariDataSource(config);
Connection connection = hikariDataSource.getConnection();

---

总结

主要流程：

1. 导包.jar 辅助的jar包
2. 写配置文件.properties、.xml
3. 加载配置（c3p0可以自动加载配置），创建DataSource出来
4. 直接使用 获取连接

---

索引

索引其实就是一种可以帮助我们提高查询速度的数据结构

索引的数据结构

eg：

查询单个值：
select * from user where id = 5;

查询范围值：
select * from user where id between 5 and 10;

插入数据
insert into user(name, age) values('zs',19);

• 数组
  • 都不高
• 链表
  • 都不高
• 有序数组
  • 查询单个值：速度快。采用二分法
  • 查询范围值：速度快。因为是有序的
  • 插入数据：速度慢。因为插入一条数据，需要挪动数据。
  • 有序数组索引只适用于静态存储引擎
• 二叉搜索树
  • 优点：
    • 查找单个值：速度快
    • 查找范围值：速度慢
    • 插入数据：速度快
  • 缺点：
    • 只有两个孩子。当数据量增大的时候，树的高度会升高，这个时候查询的次数就会变多。随着数据量的增大，会影响查询速度。
    • 二叉搜索树存储的信息比较少
• B树
  • 查询单个值：比数组和链表要方便很多，比二叉树高度降低了，查询的效率也变高了
  • 查询范围值：查询范围值需要在父子节点之前反复查找，其实不太方便
  • 插入数据：速度快
  • 
  • B树比起二叉搜索树的改进是多度，并且树的高度降低了，查找效率变高了
    • 结论：数据库在读取数据的时候，每一层会经过一次磁盘IO。假如数据的高度比较高，那么就需要经过多次的磁盘IO才能找到对应的数据，树的高度降低了之后，磁盘IO的次数会减少，那么这个时候查询速度增高。
• B+树

  • 查询单个值：比较方便

  • 查询范围值：比较方便（因为叶子节点之间维护了一个指针，指向下一个叶子节点）

  • 插入值：方便

  • 

  • B+树其实就是在B树的基础之上进行了优化：

    • 叶子节点之间维护一个指针，方便了范围查找
    • 所有的非叶子节点，都在叶子节点中冗余一份
      • B+树所有数据，都在叶子结点上有
    • 所以的非叶子节点，只存储key，不存储data，会降低树的高度，进一步提高查询的效率。
• Hash表
  • 查询单个值：很方便，对比B+树来说要方便一些
  • 查询范围值：很不方便，需要一个一个查。
  • 插入值：方便
  • 
  • Hash索引是MySQL内部使用的一种索引，没有开放给用户使用。

---

索引的实现

数据库的组成结构：

• 连接器
  • 主要负责连接的管理
• 分析器
  • 语法分析、词法分析SQL的编译
• 优化器
  • 索引的选择
  • 表连接打开的顺序
• 执行器
  • 负责语句的执行
• 存储引擎
  • 存储数据

在MySQL中，有很多种存储引擎：

• InnoDB（5.1之后默认的存储引擎），这个存储引擎其实一开始是以插件的形式存在的，在5.1之后，MySQL官方团队把InnoDB当成了默认的存储引擎
• MyISAM（5.1之前默认的存储引擎），这个存储引擎是由MySQL的官方团队开发的
• Memory（基本不用）

---

MyISAM

MyISAM的表都有三个文件：

• .frm

  • 表结构定义文件

• .MYD

  • 数据文件，其实也就是这个表中的数据都存储到这个文件中

• .MYI

  • 索引文件，这个表中的所有的索引树都是存储在这个文件中

eg：

create table t_myisam (
  ID int primary key,
  k int NOT NULL DEFAULT 0, 
  s varchar(16) NOT NULL DEFAULT '',
    -- index关键词 表明我想创建一个索引
    -- k 索引的名字
    -- (k) 索引列
  index k_name(k)
) engine=MyISAM;

• 对于MyISAM中的索引来说，数据和索引是分开存储的，这种索引叫做 非聚集索引。
• MyISAM的索引分为两种类型，一种叫做主键索引，一种叫做非主键索引

主键索引

主键索引是指MyISAM默认会根据主键这一列的值，去建立一个B+树，这个B+树就叫做主键索引树

上图为一个主键索引树

• 叶子结点上存储了key值，还存储了data值（索引列这一行数据对应的地址值）
• 这个data是一个地址值，指向.MYD里面的值，树放在.MYI里面

非主键索引

MyISAM中的非主键索引，是指我们可以把其他的非主键列声明为索引列，那么这样MyISAM就可以帮助我们根据这一列的值去建立一个索引树。意味着一个表可以有多个索引树。

上图为一个非主键索引树

• 叶子结点上存储了key值，还存储了data值（索引列这一行数据对应的地址值）
• 这个data是一个地址值，指向.MYD里面的值，树放在.MYI里面

总结：不管是主键索引还是非主键索引，索引的data只存储地址，并且这个地址指向.MYD文件

---

InnoDB

每一个InnoDB表都有两个文件：

• .frm

  表结构定义文件

• .ibd

  数据和索引文件：这个文件中存储了数据和索引。

eg：

create table t_innoDB (
  ID int primary key,
  k int NOT NULL DEFAULT 0, 
  s varchar(16) NOT NULL DEFAULT '',
  index k(k)
) engine=InnoDB;

• 在InnoDB的非主键索引中，索引只和主键存储在到了一起，实际上没有和数据存储在一起，其实也是非聚集索引。
• InnoDB的索引分为两种类型，一种叫做主键索引，一种叫做非主键索引

主键索引

• key：主键值

• data：主键这一行对应的其他列的数据

非主键索引

• key：索引列的值

• data：这一行数据对应的主键值

注：

• 对于InnoDB的表来说，必须得有一个主键。

• 对于InnoDB的表来说，如果用户在建表的时候，没有设置主键，那么InnoDB会维护一个隐藏的列来当做主键。

MyISAM和InnoDB之间的对比：

• MyISAM使用索引存储。主键索引和非主键索引，其实存储的都是地址。都需要去数据文件中找这个数据
• InnoDB使用索引存储。主键索引，直接存储的是数据。非主键索引，存储的是主键的值。

MyISAM和InnoDB的区别

1. 存储的文件不一样，MyISAM有三个文件（frm/MYD/MYI）、InnoDB只有两个文件(frm/ibd)
2. InnoDB支持事务、MyISAM不支持事务
   • 历史数据可以用MyISAM来存储是，因为历史数据不需要增删改，不需要事务
3. InnoDB支持外键，MyISAM不支持外键
4. InnoDB支持表锁和行锁，MyISAM只支持表锁
   • 表锁是指锁的对象是整张表
   • 行锁是指锁的对象是一行数据

---

InnoDB举例

eg：

mysql> create table T (
  ID int primary key,
  k int NOT NULL DEFAULT 0, 
  s varchar(16) NOT NULL DEFAULT '',
  index k(k)
) engine=Innodb;

insert into T values(100,1, 'aa'),(200,2,'bb'),
(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

如果执行的指令为select * from T where k = 3;
则执行步骤为：

1. 在k索引树（非主键索引树）上找到k=3的记录，取得主键ID=300
2. 再到ID索引树（主键索引树）上查找ID=300对应的行

回表：就是先在K索引树走一遍，找到主键后，再在ID索引树树上搜索的过程，称为回表。

避免回表的方式：

1. 如果有主键条件，应该用主键查
2. 修改SQL
3. 如果需要什么数据，就写什么数据，尽量不要*

覆盖索引：select ID from T where k = 3;

• 由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段

---

索引的语法

• 建立索引

drop table if exists student;
-- 建立索引
create table student(
	id int PRIMARY KEY,
	name varchar(20),
	age int(10),
	gender varchar(10),
	index name_index(name) using BTREE
     -- 在name上建立了一个索引，用的是B+树 
)ENGINE=InnoDB character set utf8mb4;

-- 上面语段建立了两个索引树，第一个是主键上面的索引树，第二个是name上面的索引树
-- 插入3条数据，怎么维护数据？
-- 创建索引树的过程 要会画
-- 查询的过程需要会画。  1.主键索引的查询；2.非主键索引的查询。

• 查询索引

-- 查询索引
show index from innodb_user;

• 删除索引

-- 删除索引
alter table student drop index name_index;

• 添加索引

-- 添加索引
alter table student add index age_index(age);

---

常考问题

1. 索引采用的是什么数据结构？为什么采用这种数据结构
   • B+树，可以把上述那些数据结构（数组、链表、二叉搜索树等等）都说一遍，从查询单个值速度、查询范围值速度和插入一个值速度三个方面分析优缺点。
2. 数据库为什么推荐使用自定义主键，并且在MySQL中使用推荐使用主键自增的策略？
   • 自定义主键：MySQL默认的使用的是InnoDB存储引擎，那么InnoDB存储引擎的数据和主键索引树是绑定在一起的，假如没有主键索引树，那么数据没有办法存储。假如没有给表指定主键的话，那么InnoDB会创建一个隐藏的列来当做主键，并建立主键索引树。假如使用了隐藏的列来当做的主键的话，那么我么查询的时候，就会浪费主键索引索引树带来的索引性能，所以推荐自己定义主键。
   • 自增的策略：因为自增的策略，在插入的时候，永远只会插入到索引树的右侧，那么这样就能保证树的结构不会发生比较大的改变，而结构改变是需要消耗时间的，所以这样就能保证插入的效率会比较稳定。
3. InnoDB和MyISAM有什么区别？什么情况下使用MyISAM？
   • 上面回答过这个问题
4. 什么是回表？如何避免回表？
   • 回表的定义：在一次查询中，假如需要先根据非主键索引树查询主键值，然后再根据主键值查询主键索引树，这种查询了两遍索引树的情况，叫做回表。
   • 避免回表：
     • 尽量使用主键查询
     • 尽量避免写 select *
5. 索引性能这么好，是不是一个表建立的索引越多越好？
   • 不是。
   • 声明一个索引列，需要建立一个索引树，需要占用空间
   • 假如声明的索引变多了之后，对应的索引树也会变多，查询的效率固然会提升，但是增删改的时候要去改变数据，改变数据势必会改变索引树的结构，维护这些索引树的成本也就提升了，增删改的效率也就降低了。
   • 通常默认为一个表建立的索引不要超过5个。
6. 什么样的列适合当索引？
   • 数据不重复出现的(数据具有区分性。不能用性别gender 这种变化极小的)
   • 值尽量不为空的
   • 业务场景中查询条件比较多的
   • 这一列的值不经常变化的