coding... next.|
收藏闪存小组博问

nonsenseLive

园龄：4年9个月粉丝：0关注：3

📂mysql
🔖mysql索引
2022-07-13 09:49阅读: 287评论: 0推荐: 0

mysql组合索引探索

目录

组合索引

什么是组合索引

组合索引（Compound Index）是指由多个列所组合而成的 B+树索引，这和B+ 树索引的原理完全一样，只是单列索引是对一个列排序，现在是对多个列排序。

简单来说就是有多个列组合而成的索引。

为什么要使用组合索引

首先我们先创建一个测试表，并填充一些测试数据

mysql> CREATE TABLE `test_multi_index` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `code` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `value` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `desc` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `c1` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `c2` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `c3` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `c4` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `i_test` (`name`,`code`,`value`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

使用php进行随机数据填充

use Illuminate\Support\Str;

Route::get('/test', function() {
    for ($a = 0; $a < 10; $a++) {
        $data = [];
        for ($i = 0; $i < 5000; $i++) {
            $data[] = [
                'code' => Str::random(20),
                'name' => Str::random(50),
                'value' => Str::random(50),
                'desc' => rand(1, 10) > 2 ? Str::random(50) : null,
                'c1' => rand(1, 10) > 3 ? Str::random(50) : null,
                'c2' => rand(1, 10) > 4 ? Str::random(50) : null,
                'c3' => rand(1, 10) > 5 ? Str::random(50) : null,
                'c4' => rand(1, 10) > 6 ? Str::random(50) : null,
            ];
        }
        DB::table('test_multi_index')->insert($data);
    }
});

mysql> select count(*) from test_multi_index;
+----------+
| count(*) |
+----------+
|   640001 |
+----------+
1 row in set (0.11 sec)

填充数据量在64万左右。

当我们使用单列索引的情况下，有多个where条件列，那么会使用多个索引吗？

mysql> alter table test_multi_index add index i_name(name) using btree;
Query OK, 0 rows affected (6.30 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table test_multi_index add index i_code(code) using btree;
Query OK, 0 rows affected (4.83 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc select * from test_multi_index where name like 'a%' and code like 'b%';
+----+-------------+------------------+------------+-------+---------------+--------+---------+------+-------+----------+-----------------------------------------------+
| id | select_type | table            | partitions | type  | possible_keys | key    | key_len | ref  | rows  | filtered | Extra                                         |
+----+-------------+------------------+------------+-------+---------------+--------+---------+------+-------+----------+-----------------------------------------------+
|  1 | SIMPLE      | test_multi_index | NULL       | range | i_name,i_code | i_code | 1022    | NULL | 43198 |     7.03 | Using index condition; Using where; Using MRR |
+----+-------------+------------------+------------+-------+---------------+--------+---------+------+-------+----------+-----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select * from test_multi_index where name like 'a%' and code like 'b%' limit 1;
+----+----------------------------------------------------+----------------------+----------------------------------------------------+----------------------------------------------------+------+----------------------------------------------------+----------------------------------------------------+----------------------------------------------------+
| id | name                                               | code                 | value                                              | desc                                               | c1   | c2                                                 | c3                                                 | c4                                                 |
+----+----------------------------------------------------+----------------------+----------------------------------------------------+----------------------------------------------------+------+----------------------------------------------------+----------------------------------------------------+----------------------------------------------------+
| 51 | AM5Z9acnOkUuyJ8G2b2wFc4tpqpkkGZRjWRJcmQuA1sRKNYo8D | BfEn0r2omxocCrtVH6an | 46X2iZUHkggp5AX4Cd54903Hwsp23viBuOqhPNjRLSNdeSsb73 | f44udNYtLILvC3GI5QiMrfJiUSxP9KLHVQ6aHBnJlDHgCvBhwr | NULL | RiTjKkuKXA3yPEjZNwxuw3HZVzH6Sq1jVDdMdP1ebbbuFflI44 | 54E3XCTcpLO2WgAAx1sN5hj6L4vtmYeUcFVjkGDU3qq5b7n6g8 | y0XyQ0VutrVKwIXrp1UW30TUw34IZLd9ZWkaPRxh2nOe4I5TVp |
+----+----------------------------------------------------+----------------------+----------------------------------------------------+----------------------------------------------------+------+----------------------------------------------------+----------------------------------------------------+----------------------------------------------------+
692 rows in set (1.93 sec)

mysql> alter table test_multi_index drop index i_name;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table test_multi_index drop index i_code;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table test_multi_index add index i_test(name,code);
Query OK, 0 rows affected (7.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc select * from test_multi_index where name like 'a%' and code like 'b%';
+----+-------------+------------------+------------+-------+---------------+--------+---------+------+-------+----------+----------------------------------+
| id | select_type | table            | partitions | type  | possible_keys | key    | key_len | ref  | rows  | filtered | Extra                            |
+----+-------------+------------------+------------+-------+---------------+--------+---------+------+-------+----------+----------------------------------+
|  1 | SIMPLE      | test_multi_index | NULL       | range | i_test        | i_test | 2044    | NULL | 40226 |    11.11 | Using index condition; Using MRR |
+----+-------------+------------------+------------+-------+---------------+--------+---------+------+-------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select * from test_multi_index where name like 'a%' and code like 'b%' limit 1;
+----+----------------------------------------------------+----------------------+----------------------------------------------------+----------------------------------------------------+------+----------------------------------------------------+----------------------------------------------------+----------------------------------------------------+
| id | name                                               | code                 | value                                              | desc                                               | c1   | c2                                                 | c3                                                 | c4                                                 |
+----+----------------------------------------------------+----------------------+----------------------------------------------------+----------------------------------------------------+------+----------------------------------------------------+----------------------------------------------------+----------------------------------------------------+
| 51 | AM5Z9acnOkUuyJ8G2b2wFc4tpqpkkGZRjWRJcmQuA1sRKNYo8D | BfEn0r2omxocCrtVH6an | 46X2iZUHkggp5AX4Cd54903Hwsp23viBuOqhPNjRLSNdeSsb73 | f44udNYtLILvC3GI5QiMrfJiUSxP9KLHVQ6aHBnJlDHgCvBhwr | NULL | RiTjKkuKXA3yPEjZNwxuw3HZVzH6Sq1jVDdMdP1ebbbuFflI44 | 54E3XCTcpLO2WgAAx1sN5hj6L4vtmYeUcFVjkGDU3qq5b7n6g8 | y0XyQ0VutrVKwIXrp1UW30TUw34IZLd9ZWkaPRxh2nOe4I5TVp |
+----+----------------------------------------------------+----------------------+----------------------------------------------------+----------------------------------------------------+------+----------------------------------------------------+----------------------------------------------------+----------------------------------------------------+
692 rows in set (0.55 sec)

可以看出这里分析的时候是有用到两个索索引，但是执行sql的时候只使用了i_code索引，猜测是因为使用i_code命中的行数比i_name多。mysql自动优化，选择使用了i_code索引。稍后再进行验证。
稍微设计下验证方法，再创建两个索引，分别使用 where name like 'a%' 和 where code like 'b%'查看影响的行数。在sql中强制使用i_name、i_code查看查询耗时。

mysql> alter table test_multi_index add index i_name(name) using btree;
Query OK, 0 rows affected (6.84 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table test_multi_index add index i_code(code) using btree;
Query OK, 0 rows affected (4.71 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table test_multi_index drop index i_test;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc select * from test_multi_index where name like 'a%';
+----+-------------+------------------+------------+-------+---------------+--------+---------+------+-------+----------+----------------------------------+
| id | select_type | table            | partitions | type  | possible_keys | key    | key_len | ref  | rows  | filtered | Extra                            |
+----+-------------+------------------+------------+-------+---------------+--------+---------+------+-------+----------+----------------------------------+
|  1 | SIMPLE      | test_multi_index | NULL       | range | i_name        | i_name | 1022    | NULL | 43820 |   100.00 | Using index condition; Using MRR |
+----+-------------+------------------+------------+-------+---------------+--------+---------+------+-------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> desc select * from test_multi_index where code like 'b%';
+----+-------------+------------------+------------+-------+---------------+--------+---------+------+-------+----------+----------------------------------+
| id | select_type | table            | partitions | type  | possible_keys | key    | key_len | ref  | rows  | filtered | Extra                            |
+----+-------------+------------------+------------+-------+---------------+--------+---------+------+-------+----------+----------------------------------+
|  1 | SIMPLE      | test_multi_index | NULL       | range | i_code        | i_code | 1022    | NULL | 43198 |   100.00 | Using index condition; Using MRR |
+----+-------------+------------------+------------+-------+---------------+--------+---------+------+-------+----------+----------------------------------+
1 row in set, 1 warning (0.01 sec)

mysql> desc select * from test_multi_index force index(i_name) where name like 'a%' and code like 'b%';
+----+-------------+------------------+------------+-------+---------------+--------+---------+------+-------+----------+-----------------------------------------------+
| id | select_type | table            | partitions | type  | possible_keys | key    | key_len | ref  | rows  | filtered | Extra                                         |
+----+-------------+------------------+------------+-------+---------------+--------+---------+------+-------+----------+-----------------------------------------------+
|  1 | SIMPLE      | test_multi_index | NULL       | range | i_name        | i_name | 1022    | NULL | 43820 |    11.11 | Using index condition; Using where; Using MRR |
+----+-------------+------------------+------------+-------+---------------+--------+---------+------+-------+----------+-----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> desc select * from test_multi_index force index(i_code) where name like 'a%' and code like 'b%';
+----+-------------+------------------+------------+-------+---------------+--------+---------+------+-------+----------+-----------------------------------------------+
| id | select_type | table            | partitions | type  | possible_keys | key    | key_len | ref  | rows  | filtered | Extra                                         |
+----+-------------+------------------+------------+-------+---------------+--------+---------+------+-------+----------+-----------------------------------------------+
|  1 | SIMPLE      | test_multi_index | NULL       | range | i_code        | i_code | 1022    | NULL | 43198 |    11.11 | Using index condition; Using where; Using MRR |
+----+-------------+------------------+------------+-------+---------------+--------+---------+------+-------+----------+-----------------------------------------------+
1 row in set, 1 warning (0.01 sec)

mysql> select * from test_multi_index force index(i_name) where name like 'a%' and code like 'b%';
+----+----------------------------------------------------+----------------------+----------------------------------------------------+----------------------------------------------------+------+----------------------------------------------------+----------------------------------------------------+----------------------------------------------------+
| id | name                                               | code                 | value                                              | desc                                               | c1   | c2                                                 | c3                                                 | c4                                                 |
+----+----------------------------------------------------+----------------------+----------------------------------------------------+----------------------------------------------------+------+----------------------------------------------------+----------------------------------------------------+----------------------------------------------------+
| 51 | AM5Z9acnOkUuyJ8G2b2wFc4tpqpkkGZRjWRJcmQuA1sRKNYo8D | BfEn0r2omxocCrtVH6an | 46X2iZUHkggp5AX4Cd54903Hwsp23viBuOqhPNjRLSNdeSsb73 | f44udNYtLILvC3GI5QiMrfJiUSxP9KLHVQ6aHBnJlDHgCvBhwr | NULL | RiTjKkuKXA3yPEjZNwxuw3HZVzH6Sq1jVDdMdP1ebbbuFflI44 | 54E3XCTcpLO2WgAAx1sN5hj6L4vtmYeUcFVjkGDU3qq5b7n6g8 | y0XyQ0VutrVKwIXrp1UW30TUw34IZLd9ZWkaPRxh2nOe4I5TVp |
+----+----------------------------------------------------+----------------------+----------------------------------------------------+----------------------------------------------------+------+----------------------------------------------------+----------------------------------------------------+----------------------------------------------------+
692 rows in set (1.86 sec)

mysql> select * from test_multi_index force index(i_code) where name like 'a%' and code like 'b%';
+----+----------------------------------------------------+----------------------+----------------------------------------------------+----------------------------------------------------+------+----------------------------------------------------+----------------------------------------------------+----------------------------------------------------+
| id | name                                               | code                 | value                                              | desc                                               | c1   | c2                                                 | c3                                                 | c4                                                 |
+----+----------------------------------------------------+----------------------+----------------------------------------------------+----------------------------------------------------+------+----------------------------------------------------+----------------------------------------------------+----------------------------------------------------+
| 51 | AM5Z9acnOkUuyJ8G2b2wFc4tpqpkkGZRjWRJcmQuA1sRKNYo8D | BfEn0r2omxocCrtVH6an | 46X2iZUHkggp5AX4Cd54903Hwsp23viBuOqhPNjRLSNdeSsb73 | f44udNYtLILvC3GI5QiMrfJiUSxP9KLHVQ6aHBnJlDHgCvBhwr | NULL | RiTjKkuKXA3yPEjZNwxuw3HZVzH6Sq1jVDdMdP1ebbbuFflI44 | 54E3XCTcpLO2WgAAx1sN5hj6L4vtmYeUcFVjkGDU3qq5b7n6g8 | y0XyQ0VutrVKwIXrp1UW30TUw34IZLd9ZWkaPRxh2nOe4I5TVp |
+----+----------------------------------------------------+----------------------+----------------------------------------------------+----------------------------------------------------+------+----------------------------------------------------+----------------------------------------------------+----------------------------------------------------+
692 rows in set (0.39 sec)

可以看出验证结果和我们猜测相反，这是因为我们刚才思路出现问题，当命中行数越多时，需要排查的行数越多。只有命中行数越少，排除错误行数的时间就会越少，才能更有效的节约资源，降低查询时间。

创建组合索引

mysql> ALTER TABLE `test_multi_index`  ADD INDEX `i_test`(`name`, `code`, `value`) USING BTREE;
Query OK, 0 rows affected (9.54 sec)
Records: 0  Duplicates: 0  Warnings: 0

删除组合索引

mysql> ALTER TABLE `xiuxian`.`test_multi_index`  DROP INDEX `i_test`;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

如何判别索引是否生效

通过mysql的执行计划分析sql语句，当 key 字段值为 i_test 即你创建的索引名称时，说明当前 sql 使用了组合索引。

mysql> desc
    -> select * from test_multi_index where `name` like 'a%' and `code` like 'b%';
+----+-------------+------------------+------------+-------+---------------+--------+---------+------+-------+----------+-----------------------+
| id | select_type | table            | partitions | type  | possible_keys | key    | key_len | ref  | rows  | filtered | Extra                 |
+----+-------------+------------------+------------+-------+---------------+--------+---------+------+-------+----------+-----------------------+
|  1 | SIMPLE      | test_multi_index | NULL       | range | i_test        | i_test | 2044    | NULL | 44582 |    11.11 | Using index condition |
+----+-------------+------------------+------------+-------+---------------+--------+---------+------+-------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> desc
    -> select * from test_multi_index where `code` like 'b%';
+----+-------------+------------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table            | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+------------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | test_multi_index | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 623523 |    11.11 | Using where |
+----+-------------+------------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

性能对比

mysql> select * from test_multi_index where `name` like 'a%' and `code` like 'b%';
+--------+----------------------------------------------------+----------------------+----------------------------------------------------+----------------------------------------------------+----------------------------------------------------+----------------------------------------------------+------+----------------------------------------------------+
| id     | name                                               | code                 | value                                              | desc                                               | c1                                                 | c2                                                 | c3   | c4                                                 |
+--------+----------------------------------------------------+----------------------+----------------------------------------------------+----------------------------------------------------+----------------------------------------------------+----------------------------------------------------+------+----------------------------------------------------+
| 142627 | a00sr37rcgaVwDQ2ADPFMVXzqXqjLmpIUNNcBsnE6nxyerDOMf | bfO5wEfO7UKGEju1FkVU | t7V4CnCkgGOH3v3jNybpoRrNJjMeO7Y68reLBGf53QaHwOKdQg | DUwPlLHOxr8VtJ5R24TXWN4OsUCIma9IT62lkUrpHTkD58M5bf | TlCnUXFYda6KJIsSs6SfpcHK3DcUjxdGOgnCMts8Qupj7yJnFo | SvJK7sT7Z9rbVqUIJ7UpeubCfwcLHqBBonHWUxkssk2Nk3vQdY | NULL | tX0mW4jw7vvzfdABnwPqMq68DqZsa8Acxqq9iDYXotJU19YqD8 |
+--------+----------------------------------------------------+----------------------+----------------------------------------------------+----------------------------------------------------+----------------------------------------------------+----------------------------------------------------+----------------------------------------------------+----------------------------------------------------+
692 rows in set (2.26 sec)

mysql> ALTER TABLE `test_multi_index`  ADD INDEX `i_test`(`name`, `code`, `value`) USING BTREE;
Query OK, 0 rows affected (7.88 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from test_multi_index where `name` like 'a%' and `code` like 'b%';
+--------+----------------------------------------------------+----------------------+----------------------------------------------------+----------------------------------------------------+----------------------------------------------------+----------------------------------------------------+------+----------------------------------------------------+
| id     | name                                               | code                 | value                                              | desc                                               | c1                                                 | c2                                                 | c3   | c4                                                 |
+--------+----------------------------------------------------+----------------------+----------------------------------------------------+----------------------------------------------------+----------------------------------------------------+----------------------------------------------------+------+----------------------------------------------------+
| 142627 | a00sr37rcgaVwDQ2ADPFMVXzqXqjLmpIUNNcBsnE6nxyerDOMf | bfO5wEfO7UKGEju1FkVU | t7V4CnCkgGOH3v3jNybpoRrNJjMeO7Y68reLBGf53QaHwOKdQg | DUwPlLHOxr8VtJ5R24TXWN4OsUCIma9IT62lkUrpHTkD58M5bf | TlCnUXFYda6KJIsSs6SfpcHK3DcUjxdGOgnCMts8Qupj7yJnFo | SvJK7sT7Z9rbVqUIJ7UpeubCfwcLHqBBonHWUxkssk2Nk3vQdY | NULL | tX0mW4jw7vvzfdABnwPqMq68DqZsa8Acxqq9iDYXotJU19YqD8 |
+--------+----------------------------------------------------+----------------------+----------------------------------------------------+----------------------------------------------------+----------------------------------------------------+----------------------------------------------------+------+----------------------------------------------------+
692 rows in set (0.42 sec)

可以很明显的看出，有索引查询耗费时间 0.42s 没有使用索引消耗时间 2.26s 时间相差5倍多。当数据量越来越大，这个差异会更加明显。

本文作者：nonsenseLive

本文链接：https://www.cnblogs.com/venchi/articles/16470917.html

版权声明：本作品采用知识共享署名-非商业性使用-禁止演绎 2.5 中国大陆许可协议进行许可。

posted @   nonsenseLive  阅读(287)  评论(0)  编辑  收藏  举报
点击右上角即可分享
微信分享提示
AI IDE Trae
评论
收藏
关注
推荐
深色
回顶
收起