Mysql查询可通过给条件字段添加索引提高查询速度

前言

当使用sql语句查询表数据时，会发现随着表中记录的增多，查询的速度也会也来越慢，特别是那种日志记录，少则几十万，多则上百万，甚至上千万数据，如果查询一次耗时太长，会严重影响业务逻辑，这时候可以考虑给经常作为条件的字段添加索引了，这样做会大大加快查询速度，这里所说的条件字段，就是指sql语句中放到where条件中用于筛选记录的字段，关于加索引提高查询速度的做法，我们可以做一下试验，对比一下看看是否真的有效。

测试环境

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.21-log MySQL Community Server (GPL)
Copyright © 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

测试过程

1. 首先创建一个不带有索引的数据表 tb_without_index

create table tb_without_index(id int, num int, money int);

2. 然后创建一个存储过程，用来给无索引数据表填充数据，命名为fill_tb_without_index

CREATE PROCEDURE `fill_tb_without_index`()
BEGIN
    DECLARE i int default 1;

    WHILE i <= 100000 do
        insert into tb_without_index values(i, i, i);
        set i = i + 1;
    END WHILE;
END

3. 接着创建一个带有索引用来做对比的数据表 tb_with_index

create table tb_with_index(id int, num int, money int, key `id_index`(id));

4. 同样创建一个给带索引数据表填充数据的存储过程 fill_tb_with_index

CREATE PROCEDURE `fill_tb_with_index`()
BEGIN
    DECLARE i int default 1;

    WHILE i <= 100000 do
        insert into tb_with_index values(i, i, i);
        set i = i + 1;
    END WHILE;
END

5. 分别调用存储过程来填充数据，每个表填充需要20多秒，还是挺费时间的

mysql> call fill_tb_without_index();
Query OK, 1 row affected (25.48 sec)

mysql> call fill_tb_with_index();
Query OK, 1 row affected (25.64 sec)

查询对比

1. 对于单条数据的查询对比

mysql> select * from tb_with_index where id = 67853;
+-------+-------+-------+
| id    | num   | money |
+-------+-------+-------+
| 67853 | 67853 | 67853 |
+-------+-------+-------+
1 row in set (0.05 sec)

mysql> select * from tb_without_index where id = 67853;
+-------+-------+-------+
| id    | num   | money |
+-------+-------+-------+
| 67853 | 67853 | 67853 |
+-------+-------+-------+
1 row in set (0.08 sec)

2. 对于范围数据的查询对比

mysql> select count(id) from tb_without_index where id > 87862;
+-----------+
| count(id) |
+-----------+
|     12138 |
+-----------+
1 row in set (0.09 sec)

mysql> select count(id) from tb_with_index where id > 87862;
+-----------+
| count(id) |
+-----------+
|     12138 |
+-----------+
1 row in set (0.05 sec)

结果分析

• 通过上面两种情况的对比，我们可以发现虽然每组对比只差零点零几秒的时间，但是从耗时来看有索引的表格查询比没有索引的表格查询节省了大约40%的时间，由此可见，给待查字段添加上索引，确实可以加快查询速度。
• 既然加上索引的效率可以提升这么多，那么可不可以把所有字段都加上索引呢？答案是不可以，这一点可以从测试过程的第5步结果来分析，这一步中给表格 tb_without_index 添加10万条数据耗时25.48秒，给表格 tb_with_index 添加10万条数据耗时25.64秒，也就是给有索引的表添加数据时要多花0.16秒的时间，这不是偶然的，可以反复测试，每次的测试结果都是有索引表的数据插入过程更耗时一点。
• 通过上面的对比和分析，可以知道，虽然添加索引可以加快查找速度，但是会拖慢插入和更新的速度，因为在有索引的数据表上更新和插入需要多花费时间来维护索引，至于两者之间的平衡，就需要使用者自己把握了。

添加索引

1. 像上面提到的那样，可以在建表的时候就定义好索引，查询表结构发现字段id所在行的Key列值为Mul，表示它的值是可以重复的索引，其他两个字段都没有

create table tb_with_index(id int, num int, money int, key `id_index`(id));
mysql> desc tb_with_index;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  | MUL | NULL    |       |
| num   | int(11) | YES  |     | NULL    |       |
| money | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.05 sec)

2. 在已有的表格上创建索引，比如可以在列num上创建一个索引，语法：CREATE INDEX index_name ON table_name(column_list)

mysql> CREATE INDEX num_index ON tb_with_index(num);
Query OK, 0 rows affected (0.23 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb_with_index;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  | MUL | NULL    |       |
| num   | int(11) | YES  | MUL | NULL    |       |
| money | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.05 sec)

3. 修改表结构添加索引，比如可以给列num添加一个索引，语法：ALTER TABLE table_name ADD INDEX index_name(column_list)

mysql> ALTER TABLE tb_with_index ADD INDEX money_index(money);
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb_with_index;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  | MUL | NULL    |       |
| num   | int(11) | YES  | MUL | NULL    |       |
| money | int(11) | YES  | MUL | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.06 sec)

查看索引

可以查看一个表上的所有索引信息，语法为：show index from table_name，查询结果如下

mysql> show index from tb_with_index;
+---------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ ---------------+
| Table         | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment  | Index_comment |
+---------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ ---------------+
| tb_with_index |          1 | id_index    |            1 | id          | A         |       98715 | NULL     | NULL   | YES  | BTREE      |          |               |
| tb_with_index |          1 | num_index   |            1 | num         | A         |      100035 | NULL     | NULL   | YES  | BTREE      |          |               |
| tb_with_index |          1 | money_index |            1 | money       | A         |      100035 | NULL     | NULL   | YES  | BTREE      |          |               |
+---------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ ---------------+
3 rows in set (0.06 sec)

总结

1. 给条件字段添加索引可以大大加快数据的查询速度，提高系统的性能。
2. 不要考虑在所有的字段上添加索引，创建索引和维护索引都要耗费时间，这种时间随着数据量的增加而增加。
3. 适合添加索引的字段：总是作为条件查询的字段、常用来做连接的字段、作为主键或者强调唯一的列上。
4. 不适合加索引的字段：块数据类型的字段、取值很少的字段。