小工具知识网

请输入关键词
当前位置： 首页 > 行业知识 > MySQL之索引详解

行业知识

MySQL之索引详解

2026-10-29 17:46:00 来源：互联网转载

华子目录

  • 索引概述
    • 优缺点
    • 索引的原理
    • 索引的设计原则
    • 索引结构
      • B-tree（多路平衡查找树）
      • B+tree
      • Hash
      • 为什么InnoDB存储引擎选择B+tree？
      • 索引分类
        • 聚集索引选取规则
        • 单列索引和多列索引
        • 前缀索引
        • 创建索引
          • 1.创建表时创建索引
          • 2.在已经存在的表上创建索引
          • 3.使用alter table语句创建索引
          • 使用计划查询SQL使用索引情况（==explain==）
          • 查看索引
          • 删除索引
          • ���例

            索引概述

            • 索引（index） 是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引
            • 无索引时，需要一条一条查找每一条数据，有索引时就不需要逐一查询数据
            • 在数据库中用来加速对表的查询；通过使用快速路径访问方法快速定位数据,减少了磁盘的I/O；与表独立存放，但不能独立存在，必须属于某个表；由数据库自动维护，表被删除时，该表上的索引自动被删除；
            • 索引的作用类似于书的目录，几乎没有一本书没有目录，因此几乎没有一张表没有索引。

              优缺点

              • 索引的原理

                就是把无序的数据变成有序的查询

                1. 把创建的索引的列的内容进行排序
                2. 对排序结果生成倒排表
                3. 在倒排表内容上拼上数据地址链
                4. 在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据

                索引的设计原则

                为了使索引的使用效率更高，在创建索引的时候必须考虑在哪些字段上创建索引和创建什么类型的索引。

                1. 选择惟一性索引
                2. 为经常需要排序、分组和联合操作的字段建立索引
                3. 为常作为查询条件的字段建立索引
                4. 限制索引的数目
                5. 尽量使用数据量少的索引
                6. 尽量使用前缀来索引
                7. 删除不再使用或者很少使用的索引

                索引结构

                MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含以下几种：

                索引结构描述
                B+tree索引最常见的索引类型，大部分引擎都支持B+tree索引
                Hash索引底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，通常使用较少
                R-tree（空间索引）空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
                Full-text（全文索引）是一种通过建立倒排索引,快速匹配文档的方式。类似于Lucene,Solr,ES

                B-tree（多路平衡查找树）

                B+tree

                Hash

                为什么InnoDB存储引擎选择B+tree？

                索引分类

                分类含义特点关键字
                主键索引针对于表中主键创建的索引默认自动创建，只能存在一个primary
                唯一索引避免同一个表中某数据列中的值重复可以有多个unique
                常规索引快速定位特定数据可以有多个无
                全文索引全文索引查找的是文本中的关键字，而不是比较索引中的值可以有多个fulltext

                在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

                分类含义特点
                聚集索引将数据存储与索引放到了一块，索引结构的叶子节点保存了完整的行数据必须存在，且只有一个
                非聚集索引（二级索引）将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键可以存在多个

                聚集索引选取规则

                • 如果存在主键，主键索引就是聚集索引
                • 如果不存在主键，将使用第一个唯一(unique)索引作为聚集索引
                • 如果没有主键，也没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引
                  • 第一个执行效率高：因为第一个查的是主键索引，只查一张表。第二个先查name字段的索引，再根据name字段的索引值进行回表查询，查两张表。

                    单列索引和多列索引

                    MySQL中的单列索引和多列索引都是用于提高数据库查询效率的工具，它们有一些不同之处。

                    1. 单列索引：

                      • 单列索引是针对表中的单个列创建的索引。
                      • 它可以加速针对该列的查找、排序和过滤操作。
                      • 适用于单列条件查询，例如：SELECT * FROM table WHERE column = value;
                      • 单列索引可以包括在多列查询中，但只有第一列索引将被用于加速查找。
                      • 创建单列索引的语法示例：CREATE INDEX index_name ON table_name (column_name);

                      • 多列索引：

                        • 多列索引是针对表中多个列组合而成的索引。
                        • 它可以加速涉及这些列组合的查询，例如联合查询或者多列条件查询。
                        • 当查询涉及到多个列时，多列索引通常比单列索引更有效。
                        • 多列索引的列顺序非常重要，因为只有查询中使用的列的左侧前缀才会被索引所利用。
                        • 创建多列索引的语法示例：CREATE INDEX index_name ON table_name (column1, column2, ...);

                    前缀索引

                    在 MySQL 中，你可以创建前缀索引来提高查询效率。前缀索引是指只对列值的一部分进行索引，而不是整个列值。这在某些情况下可以减少索引的大小，并提高查询性能，尤其是对于较大的列类型（如 TEXT 或 VARCHAR）。

                    要在 MySQL 中创建前缀索引，你可以使用以下语法：

                    CREATE INDEX index_name ON table_name (column_name(prefix_length));

                    在这里，prefix_length 是你希望索引的列值的前缀长度。以下是一个示例：

                    CREATE TABLE my_table (    id INT AUTO_INCREMENT PRIMARY KEY,    name VARCHAR(100));CREATE INDEX idx_name_prefix ON my_table (name(10));

                    在这个示例中，我们为 name 列创建了一个前缀长度为 10 的索引。这意味着索引将仅包含 name 列值的前 10 个字符。你可以根据你的需求调整 prefix_length 的值。

                    请注意，使用前缀索引时需要注意选择适当的前缀长度。如果前缀长度太短，可能会导致索引失效，而如果太长，可能会增加索引的大小并降低性能提升效果。因此，需要根据你的数据和查询模式来选择合适的前缀长度。

                    创建索引

                    • 创建索引是指在某个表的一列或多列上建立一个索引，以便提高对表的访问速度。
                    • 创建索引有三种方式，这三种方式分别是创建表时创建索引、在已经存在的表上创建索引和使用alter table语句来创建索引。

                      1.创建表时创建索引

                      创建表的时候可以直接创建索引，这种方式最简单、方便。其基本形式如下：

                      mysql> create table 表名(    -> 列名 数据类型 约束,    -> 列名 数据类型 约束,    -------------------    -> 列名 数据类型 约束,    -> [unique/fulltext] index 索引名(列名 [asc/desc])    -> );
                      mysql> create table mytable(    -> id int auto_increment primary key,    -> name varchar(20),    -> age int(3),    -> phone int unique,    -> unique index index_mytable_phone(phone),#创建唯一索引    -> index index_mytable_name(name(3)),#创建前缀索引    -> index index_mytable_age(age)#创建常规索引    -> );Query OK, 0 rows affected, 2 warnings (0.07 sec)mysql> show index from mytable;+---------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| Table   | Non_unique | Key_name            | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |+---------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| mytable |          0 | PRIMARY             |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       || mytable |          0 | phone               |            1 | phone       | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       || mytable |          0 | index_mytable_phone |            1 | phone       | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       || mytable |          1 | index_mytable_name  |            1 | name        | A         |           0 |        3 |   NULL | YES  | BTREE      |         |               | YES     | NULL       || mytable |          1 | index_mytable_age   |            1 | age         | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |+---------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
                      mysql> create table mytable(    -> id int auto_increment primary key,    -> name varchar(20),    -> age int(3),    -> phone int unique,    -> index index_mytable_name(name(3)),    -> index index_mytable_age(age)    -> );Query OK, 0 rows affected, 1 warning (0.05 sec)mysql> show index from mytable;+---------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| Table   | Non_unique | Key_name           | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |+---------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| mytable |          0 | PRIMARY            |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       || mytable |          0 | phone              |            1 | phone       | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       || mytable |          1 | index_mytable_name |            1 | name        | A         |           0 |        3 |   NULL | YES  | BTREE      |         |               | YES     | NULL       || mytable |          1 | index_mytable_age  |            1 | age         | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |+---------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

                      注：MySQL8会为主键和唯一和外键自动创建索引

                      2.在已经存在的表上创建索引

                      前提是：该表上无索引，需要手动添加索引

                      mysql> create [unique/fulltext] index 索引名 on 表名(列名);  #创建单列索引mysql> create [unique/fulltext] index 索引名 on 表名(列名1,列名2...);  #创建多列索引 
                      mysql> create table mytable(    -> id int auto_increment primary key,    -> name varchar(20)    -> );mysql> create unique index index_mytable_name on mytable(name(3));mysql> show index from mytable;+---------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| Table   | Non_unique | Key_name           | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |+---------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| mytable |          0 | PRIMARY            |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       || mytable |          0 | index_mytable_name |            1 | name        | A         |           0 |        3 |   NULL | YES  | BTREE      |         |               | YES     | NULL       |+---------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

                      3.使用alter table语句创建索引

                      mysql> alter table 表名 add [unique/fulltext] index 索引名(列名);
                      mysql> create table mytable(    -> id int auto_increment primary key,    -> name varchar(20)    -> );#通过添加索引的方式添加约束mysql> alter table mytable add unique index index_mytable_name(name);   mysql> show index from mytable;+---------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| Table   | Non_unique | Key_name           | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |+---------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| mytable |          0 | PRIMARY            |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       || mytable |          0 | index_mytable_name |            1 | name        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |+---------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

                      使用计划查询SQL使用索引情况（explain）

                      mysql> desc stu;+---------+-------------+------+-----+---------+----------------+| Field   | Type        | Null | Key | Default | Extra          |+---------+-------------+------+-----+---------+----------------+| id      | int         | NO   | PRI | NULL    | auto_increment || name    | varchar(20) | NO   |     | NULL    |                || age     | int         | NO   |     | NULL    |                || classid | int         | NO   | MUL | NULL    |                |+---------+-------------+------+-----+---------+----------------+mysql> show index from stu;+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| Table | Non_unique | Key_name            | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| stu   |          0 | PRIMARY             |            1 | id          | A         |           2 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       || stu   |          1 | stu_classid_foreign |            1 | classid     | A         |           2 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+mysql> select * from stu;+-----+--------+-----+---------+| id  | name   | age | classid |+-----+--------+-----+---------+| 101 | 小天   |  18 |    1001 || 102 | 小明   |  20 |    1003 || 103 | 小红   |  13 |    1002 |+-----+--------+-----+---------+mysql> explain select * from stu where id=101;+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+|  1 | SIMPLE      | stu   | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+mysql> explain select * from stu where id=103;+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+|  1 | SIMPLE      | stu   | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
                      explain分析结果的含义:id: 每一行的编号select_type: 查询类型。这里是 SIMPLE，表示这是一个简单的查询。table：这是表的名字。partitions: 所使用的分区（如果有）    type：连接操作的类型，ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）    possible_keys：可能可以利用的索引的名字    Key：它显示了MySQL实际使用的索引的名字。如果它为空（或NULL），则MySQL不使用索引。    key_len：索引中被使用部分的长度，以字节计。    ref：它显示的是列的名字（或单词“const”），MySQL将根据这些列来选择行    rows：MySQL所认为的它在找到正确的结果之前必须扫描的记录数。显然，这里最理想的数字就是1    filtered: 表示按表的过滤条件过滤后的结果所占百分比。    Extra：这里可能出现许多不同的选项，其中大多数将对查询产生负面影响

                      查看索引

                      mysql> show index from 表名;

                      删除索引

                      mysql> drop index 索引名 on 表名;

                      案例

                      按照下列需求，完成索引的创建

                      1. name字段为姓名字段，该字段的值可能会重复，为该字段创建索引（创建一个常规索引）
                      mysql> create index index_stu_name on stu(name);
                      1. phone手机号字段的值是非空且唯一（primary），为该字段创建唯一索引（创建一个唯一索引）
                      mysql> create unique index index_stu_phone on stu(phone);
                      1. 为profession、age、status创建联合索引（多个常规索引）
                      mysql> create index index_stu_profession_age_status on stu(profession,age,status);
                      1. 为email建立合适的索引来提升查询效率（创建一个常规索引）
                      mysql> create index index_stu_email on stu(email);

上一篇：掌握图画英文翻译，一文轻松搞定正确表达！

下一篇：网络推广的目标怎么写?

最新文章

图文

热门标签

友情链接