数据库索引对高并发环境下查询时间的提升

1. 环境准备

1.1 数据准备

在对查询速度进行研究之前，我们需要较大数据量进行支撑。详见 《向数据库中插入千万级数据》

1.2 更改max_connections属性

默认情况下MySQL对最大连接数的限制为151（具体值视版本情况而定），但是这样在进行并发测试时显然是不够用的，如果超出连接数就会抛出异常：

我们需要执行下列命令，更改max_connections属性值：

set global max_connections = 500 #具体值自己指定

查看变量值：

show variables like '%max_connections%'

2. 测试

package _12MySQL并发连接数;

import java.sql.*;
import java.util.Random;
import java.util.concurrent.CyclicBarrier;

/**
 * @Auther: 田金东
 * @Date: 11/1/0001
 **/
public class ConcurentQueryTime {

    /**
     * 数据库连接
     */
    private static final String URL = "jdbc:mysql://127.0.0.1:3306/test?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8";
    private static final String DRIVER = "com.mysql.cj.jdbc.Driver";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "xxx";

    /**
     * 查询所用到的t_name池
     */
    private static final String[] T_NAME_POOL = {"744460", "479960", "539256", "196155", "504987", "889640", "252627", "208560", "278120", "32388", "203583", "249630", "39767", "53060", "376608", "62040", "385060", "227631", "591619", "188079", "705312", "68761", "228510", "593838", "181786", "211380", "403476", "64577", "656117", "654456", "89890", "441651", "282324", "179295", "152577", "85323", "574407", "224772", "58662", "489405", "299512", "299091", "29585", "84459", "107260", "347106", "567816", "47550", "18264", "423468", "281601", "375444", "36644", "59967", "422936", "983740", "238441", "136347", "143572", "127017", "465960", "661024", "283401", "306348", "571140", "447228", "21745", "104208", "15090", "276424", "527177", "157748", "865340", "585837", "193218", "38818", "295764", "123162", "249520", "211300", "761840", "97056", "423262", "110", "67838", "62118", "58459", "271990", "11081", "402195", "183478", "184926", "166744", "26804", "401312", "515898", "176836", "12804", "728992", "303570"};

    /**
     * 并发查询数
     */
    private static final int MAX_QUERY = 300;

    /**
     * 并发测试线程
     */
    private static class Query implements Runnable {


        private CyclicBarrier cyclicBarrier;

        public Query(CyclicBarrier cyclicBarrier) {
            this.cyclicBarrier = cyclicBarrier;
        }

        @Override
        public void run() {
            try {
                Connection connection = ConcurentQueryTime.getConnection();
                cyclicBarrier.await();//设置屏障
                long start = System.currentTimeMillis();//获取开始时间
                int index = randomIndex();
                String tname = T_NAME_POOL[index];
                PreparedStatement preparedStatement = connection.prepareStatement("select id,t_name,t_password,sex,description,pic_url,school_name,regist_date,remark from student where t_name=?");
                preparedStatement.setString(1, tname);
                ResultSet resultSet = preparedStatement.executeQuery();
                long end = System.currentTimeMillis();//获取结束时间
                System.out.println(Thread.currentThread() + " 查询" + tname + " 花费：" + (end - start) + "毫秒");
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }


    public static void main(String[] args) {
        CyclicBarrier cyclicBarrier = new CyclicBarrier(MAX_QUERY);
        for (int i = 0; i < MAX_QUERY; i++) {
            new Thread(new Query(cyclicBarrier), "查询线程" + i).start();
        }
    }


    /**
     * 获取数据库连接
     *
     * @return
     * @throws Exception
     */
    private static Connection getConnection() throws Exception {
        Class.forName(DRIVER);
        Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        return connection;
    }

    /**
     * 随机获取查询t_name池的索引
     *
     * @return
     */
    private static int randomIndex() {
        Random random = new Random();
        int i = random.nextInt(T_NAME_POOL.length);
        return i;
    }

}

我们在创建索引之前，我们执行测试代码查看查询时间：

可以在没有索引的情况下，我们根据t_name字段查询数据库，可以发现查询速度非常之慢，部分查询时间达到了分钟级别。

执行下列语句，为t_name字段创建数据库索引：

alter table student add index t_name_index (t_name) ; #创建普通索引

索引建立后我们再次执行测试代码，可以发现查询效率有质的飞跃：