数据库索引笔记-CSDN博客

本文链接：https://blog.csdn.net/caoyanzhi/article/details/140899472

索引使用介绍---索引类型

索引使用介绍--- 执行计划分析

查看执行计划格式：explain/desc + SQL

dingzhichaodeMacBook-Pro:vem-operator dzc$ /usr/local/mysql/bin/mysql -h 10.213.3.241 -P6446 -u test_user -ptest_user@123456
mysql> use vem;
Database changed
mysql>
mysql> desc  select * from vem_order vo left join vem_order_detail vod on vo.id = vod.ORDER_ID where vo.id in(1,2);
+----+-------------+-------+------------+------+---------------+--------------+---------+-----------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key          | key_len | ref       | rows     | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+--------------+---------+-----------+----------+----------+-------------+
|  1 | SIMPLE      | vo    | NULL       | ALL  | uk_vem_order  | NULL         | NULL    | NULL      | 32011575 |    20.00 | Using where |
|  1 | SIMPLE      | vod   | NULL       | ref  | IDX_ORDER_ID  | IDX_ORDER_ID | 98      | vem.vo.ID |        1 |   100.00 | NULL        |
+----+-------------+-------+------------+------+---------------+--------------+----

执行计划详解

索引使用介绍---表信息查询

dingzhichaodeMacBook-Pro:vem-operator dzc$ /usr/local/mysql/bin/mysql -h 10.213.3.241 -P6446 -u test_user -ptest_user@123456
mysql>   select * from `information_schema`.`TABLES` where `TABLE_NAME` = 'vem_machine';
+---------------+--------------+-------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+-----------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME  | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME         | UPDATE_TIME         | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT   |
+---------------+--------------+-------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+-----------------+
| def           | vem          | vem_machine | BASE TABLE | InnoDB |      10 | Dynamic    |     105060 |            185 |    19447808 |               0 |     37404672 |   6291456 |           NULL | 2021-03-03 17:56:24 | 2021-03-09 16:26:31 | NULL       | utf8_general_ci |     NULL |                | 自贩机列表      |

表指标信息展示

索引使用介绍---索引信息查询

mysql>   show index from vem_order;
+-----------+------------+--------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name                       | Seq_in_index | Column_name       | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+--------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| vem_order |          0 | PRIMARY                        |            1 | primary_id        | A         |    32011550 |     NULL | NULL   |      | BTREE      |         |               |
| vem_order |          0 | uk_vem_order                   |            1 | ID                | A         |    32011576 |     NULL | NULL   |      | BTREE      |         |               |
| vem_order |          1 | IDX_MACHINEID_PAYCATEGORYNAME  |            1 | MACHINE_ID        | A         |       28255 |     NULL | NULL   |      | BTREE      |         |               |
| vem_order |          1 | IDX_MACHINEID_PAYCATEGORYNAME  |            2 | PAY_CATEGORY_NAME | A         |       31395 |     NULL | NULL   | YES  | BTREE      |         |               |

索引指标信息说明

索引使用介绍---聚集索引

聚集索引就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据。下面举个例子，直观感受下聚集索引。典型的就是表的主键，他就是聚集索引，其他的是辅助索引。

聚集索引的叶子节点称为数据页，每个数据页通过一个双向链表来进行链接，而且数据页按照主键的顺序进行排列。如图所示，每个数据页上存放的是完整的行记录，而在非数据页的索引页中，存放的仅仅是键值及指向数据页的偏移量，而不是一个完整的行记录。如果定义了主键，InnoDB会自动使用主键来创建聚集索引。如果没有定义主键，InnoDB会选择一个唯一的非空索引代替主键。如果没有唯一的非空索引，InnoDB会隐式定义一个主键来作为聚集索引。构建索引的数据结构是B+tree

索引使用介绍---辅助索引

辅助索引，也叫非聚集索引。和聚集索引相比，叶子节点中并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点的索引行还包含了一个书签（bookmark），该书签用来告诉InnoDB哪里可以找到与索引相对应的行数据。构建索引的数据结构是Btree。如果要查询辅助索引中不含有的字段，得先遍历辅助索引，再遍历聚集索引，而如果要查询的字段值在辅助索引上就有，就不用再查聚集索引了，这显然会减少IO操作。比如上图中，以下sql可以直接使用辅助索引。使用辅助索引降低IO次数。

#先找到c=-2的辅助索引对应的聚族索引，然后再查询
select a from where c = -2;

索引使用介绍---覆盖索引

InnoDB存储引擎支持覆盖索引，即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。使用覆盖索引有啥好处？

#表结构
CREATE TABLE `student` (
`id` bigint(20) NOT NULL,
`name` varchar(255) NOT NULL,
`age` varchar(255) NOT NULL,
`school` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`),
KEY `idx_school_age` (`school`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#查询语句
select count(*) from student

查看执行计划如下图，其查询学生数，只走了索引 idx_name，未查询表。这就是覆盖索引的用法。

索引使用介绍---联合索引

联合索引如果索引生效必须带上主索引（聚集索引），而联合索引（辅助索引）的部分就是在主索引数据筛选的基础上再次筛选。比如下图a,b,c构成test表的联合索引索引覆盖：a,b,c、a,c、a,b 索引不覆盖：b,c、c,b。为什么这样？ B+Tree也是一种树结构的数据结构，是树结构其遍历方式分别为先序遍历、中序遍历、后续遍历三种遍历方式。其中先序遍历的原则是根左右，这种遍历的方式树查询效率最高时间复杂度(m底数：阶数目，N待查询数总数)，故MySql采用先序遍历的方式遍历。所以也就有了MySql的最左原则。