MySQL索引

什么是索引？

索引是一种特殊的数据结构，由数据表中的一列或者多列组成，可以用来快速查询数据库中的某一特定值的记录。

索引的类型有哪些？

按照数据结构的维度进行划分：

  • BTree索引
  • 哈希索引
  • RTree索引
  • 全文索引
    • 对文本的内容进行分词，进行索引。目前只有CHAR、VARCHAR、TEXT列上可以创建全文索引。一般不会使用，效率较低，通常使用搜索引擎如ElasticSearch代替。

按照应用的维度划分：

  • 单列索引

  • 联合索引

    • 多列值组成一个索引，专门用于组合搜索，其效率大于索引合并

  • 主键索引

    • 加速查询+列值唯一（不可以有NULL）

  • 普通索引

    • 仅加速查询

  • 唯一索引

    • 加速查询+列值唯一（可以有NULL）

  • 覆盖索引

    • 一个索引包含或者说覆盖所有需要查询的字段值

  • 全文索引

联合索引：

使用表中的多个字段创建索引，就是联合索引，也叫组合索引或者复合索引。

以score和name两个字段建立联合索引。

1
ALTER TABLE `cus_order` ADD INDEX id_score_name(score, name);

最左匹配原则，在使用联合索引的时候，mysql会根据联合索引中的字段顺序，从左到右依次到查询条件中去匹配，如果查询条件中存在与联合索引中最左侧字段相匹配的字段，则就会使用字段过滤掉一批数据，直至联合索引中全部字段匹配完成，或者在执行过程中遇到范围查询（如 >**、<** ）才会停止匹配。对于 >=**、<=、BETWEEN、like** 前缀匹配的范围查询，并不会停止匹配。所以，我们在使用联合索引时，可以将区分度高的字段放在最左边，这也可以过滤更多数据。

在navicat中创建索引

使用索引注意事项

选择合适的字段创建索引

  • 不为NULL字段：索引字段的数据应该尽量不为NULL，因为对数据为NULL的字段，数据库较难优化。如果字段频繁查询，但又避免不了为NULL，建议使用1，true，false这样语义较为清晰的短值或者短字符作为替代。

  • 被频繁查询的字段：

  • 被作为查询条件的字段

  • 频繁需要排序的字段

  • 被经常频繁用于连接的字段：

  • 尽可能考虑建立联合索引而不是单列索引

索引失效的情况

为什么要用索引？

这里我首先在数据库中插入了500多万条数据

创建数据库表：

1
2
3
4
5
6
CREATE TABLE `tb_data` (
  `id` int(11) DEFAULT NULL,
  `user_name` varchar(100) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `random` double DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

这里我用java的代码来往数据库中反复的插入数据， 具体的代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
package com.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class InsertDataDemo {
    static Connection conn = null;

    public static void initConn() throws ClassNotFoundException, SQLException {

        String url = "jdbc:mysql://localhost:3306/testdb?"
                + "user=root&password=xxxxxx&useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=UTC";

        try {
            // Dynamically load mysql driver
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("Successfully loaded MySQL driver");
            conn = DriverManager.getConnection(url);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    public static String randomStr(int size) {
        // Define an empty string
        String result = "";
        for (int i = 0; i < size; ++i) {
            // Generate an int type integer between 97 ~ 122
            int intVal = (int) (Math.random() * 26 + 97);
            // Force conversion (char) intVal Convert the corresponding value to the corresponding character, and splicing the characters
            result = result + (char) intVal;
        }
        // Output string
        return result;
    }


    public static void insert(int insertNum) {
        // open time
        Long begin = System.currentTimeMillis();
        System.out.println("Start Inserting Data...");
        // sql prefix
        String prefix = "INSERT INTO tb_data (id, user_name, create_time, random) VALUES ";

        try {
            // save the sql suffix
            StringBuffer suffix = new StringBuffer();
            // Set the transaction to non-automatic commit
            conn.setAutoCommit(false);
            PreparedStatement pst = conn.prepareStatement("");
            for (int i = 1; i <= insertNum; i++) {
                // Build sql suffix
                suffix.append("(" + i + ",'" + randomStr(8) + "', SYSDATE(), " + i * Math.random() + "),");
            }
            // Build a complete sql
            String sql = prefix + suffix.substring(0, suffix.length() - 1);
            // Add execution sql
            pst.addBatch(sql);
            // perform the operation
            pst.executeBatch();
            // commit the transaction
            conn.commit();

            // close the connection
            pst.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        // End Time
        Long end = System.currentTimeMillis();
        System.out.println("insert" + insertNum + " data data is completed!");
        System.out.println("Time-consuming : " + (end - begin) / 1000 + "seconds");
    }


    public static void main(String[] args) throws SQLException, ClassNotFoundException, InterruptedException {

        initConn();

        for (int i = 0; i < 100; i++) {
            insert(50000);
            Thread.sleep(1000);
        }
        conn.close();

    }
}

可以看到现在数据库中一共有523万条数据

image-20240315211801264

现在我来随机进行一条user_name数据的查询，可以看到耗时39秒

image-20240315211945502

慢SQL查询

这里也是一道常见的面试问题，一般而言，可以在MySQL配置文件中开启慢查询日志，打开my.ini文件，找到slow_query_log，将其参数修改为ON，同时可以设置long_query_time来定义慢查询的阈值（以秒为单位，默认为10秒）。然后我们可以在本地的hostname-slow.log文件中查看到之前的慢sql语句。

注意：这里的hostname是指你笔记本/服务器的主机名称

打开文件，直接到最后：

image-20240315212601235

可以看到我刚进行的几条sql语句因为时间过长都被记录在日志当中了。然后开发人员就可以根据SQL语句来进行针对性的优化，这里我进行设置索引

这里也可以使用EXPLAIN语句查看这条SQL语句慢的原因：可以看到这里涉及到了523w行记录，所以会慢，因此我们可以考虑添加数据库索引

image-20240315215146207

创建索引

1
CREATE INDEX idx_user_name ON tb_data (user_name);

这样的方式我们就可以在tb_data表中给user_name列创建了一个名为idx_user_name的索引

image-20240315215538262

创建索引的时间也比较漫长，可以看到花费了一百多秒

image-20240315221023511

索引在本地也会生成一个.MYI的文件，思想是以空间换时间

现在我们在执行刚刚的查询操作：

1
SELECT id from tb_data WHERE user_name="gdpzfbho"

image-20240315215701951

可以看到查询时间肉眼可见的降低。

同时再次使用explain语句进行查询

image-20240315221419192

发现这条命令已经是通过索引走的了

查看数据库引擎

image-20240315220851579