- 微信
- 微博
  
  分享文章到微博
- 复制链接
  
  复制链接到剪贴板

MySQL-索引

大虾好吃吗发表于 2023/07/20 14:58:41 2023/07/20

【摘要】索引介绍索引是对数据库表中一列或者多列的值进行排序的一种结构，使用索引可提高数据库中特定数据的查询速度。索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用指针。使用索引用于快速找出在某个或多个列中有一特定值得行，所有MySQL列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。索引是在存储引擎中实现的，因此，每种存储引擎的索引都不一定完...

索引介绍

索引是对数据库表中一列或者多列的值进行排序的一种结构，使用索引可提高数据库中特定数据的查询速度。

索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用指针。使用索引用于快速找出在某个或多个列中有一特定值得行，所有MySQL列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。

索引是在存储引擎中实现的，因此，每种存储引擎的索引都不一定完全相同，并且每种存储引擎也不一定支持索引类型。

根据存储引擎定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节。 MyISAM ，InnoDB支持btree索引Memory 支持 btree和hash索引

索引的优势

加快查询速度
创建唯一索引来保证数据表中数据的唯一性
实现数据的完整性，加速表和表之间的连接
减少分组和排序的时间

增加索引

增加索引也有许多不利，主要表现在如下几个方面：

创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

索引的分类

唯一索引和普通索引普通索引是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值。唯一索引，索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。主键索引是一种特殊的唯一索引，不允许有空值。
单列索引和组合索引单列索引即一个索引只包含单个列，一个表可以有多个单列索引。组合索引指在表的多个字段组合上创建的索引。只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合。
全文索引 fulltext 全文索引类型为FULLTEXT，在定义索引的列上支持值得全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。MySQL中只有MyISAM存储引擎支持全文索引。
空间索引空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4中，分别是：geometry、point、linstring和polygon 。MySQL使用SPATIAL关键字进行扩展，使得能够用于创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MyISAM的表中创建。

创建索引的规则

(1)创建索引并非是越多越好，一个表中如果有大量的索引，不仅占用磁盘空间，而且会影响insert、delete、update等语句的性能。因为当表中的数据更改时，索引也会进行调整和更新。

(2)数据量小得表最好不要创建索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要长。

(3)避免对经常更新的数据创建索引。而对经常用于查询的字段应该创建索引。

(4)在条件表达式中经常用到的不同值较多的列创建索引

(5)当唯一性是某种数据本身的特征时，我们创建唯一性索引

(6)在频繁进行排序或分组的列上建立索引，如果排序的列有多个，可以创建组合索引

创建索引

Create index 创建索引 
alter table 添加索引 
Create table 表名[字段名 数据类型] [unique唯一索引|fulltext全文索引|spatial空间索引] [index|key] [索引名] (col_name [length]) [asc |desc]

普通索引

普通索引是最基础的索引类型，没有唯一性的限制。作用是只加快对数据的访问速度。

先创建book表，创建索引为year_publication

mysql> CREATE TABLE book
    ->  (
    ->  bookid INT NOT NULL,
    ->  bookname VARCHAR(255) NOT NULL,
    ->  authors VARCHAR(255) NOT NULL,
    ->  info VARCHAR(255) NULL,
    ->  comment VARCHAR(255) NULL,
    ->  year_publication YEAR NOT NULL,
    ->  INDEX(year_publication)
    ->  );
Query OK, 0 rows affected (0.03 sec)

查看创建的索引

注：\G结尾表示纵向查看

mysql> show create table book\G
*************************** 1. row ***************************
       Table: book
Create Table: CREATE TABLE `book` (
  `bookid` int(11) NOT NULL,
  `bookname` varchar(255) COLLATE utf8_bin NOT NULL,
  `authors` varchar(255) COLLATE utf8_bin NOT NULL,
  `info` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `comment` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `year_publication` year(4) NOT NULL,
  KEY `year_publication` (`year_publication`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

explain

使用该命令判断索引是否正在被使用

mysql> explain select * from book where year_publication=1999\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: book
   partitions: NULL
         type: ref
possible_keys: year_publication
          key: year_publication
      key_len: 1
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

TYPE的取值范围 System const ref eq_ref index all range

唯一索引

创建唯一索引唯一索引主要原因是减少查询索引列操作的执行时间。尤其是对比比较庞大的数据表。与普通索引类似，不同点在于：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

创建唯一索引

mysql> CREATE TABLE t1
    ->  (
    ->  id INT NOT NULL,
    ->  name CHAR(30) NOT NULL,
    ->  UNIQUE INDEX UniqIdx(id)
    ->  );
Query OK, 0 rows affected (0.00 sec)

查看唯一索引

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` char(30) COLLATE utf8_bin NOT NULL,
  UNIQUE KEY `UniqIdx` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

单列索引

单列索引：是在数据表中的某一字段上创建的索引，一个表中可以创建多个单列索引。

创建单列索引

mysql> CREATE TABLE t2
    ->  (
    ->  id INT NOT NULL,
    ->  name CHAR(50) NULL,
    ->  INDEX SingleIdx(name)
    ->  );
Query OK, 0 rows affected (0.01 sec)

查看单列索引

mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `name` char(50) COLLATE utf8_bin DEFAULT NULL,
  KEY `SingleIdx` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

组合索引

组合索引：是在多个字段上创建一个索引。遵循最左前缀原则。最左前缀索引最左边的列来匹配行

创建组合索引

mysql> CREATE TABLE t3
    ->  (
    ->  id INT NOT NULL,
    ->  name CHAR(30) NOT NULL,
    ->  age INT NOT NULL,
    ->  info VARCHAR(255),
    ->  INDEX MultiIdx(id,name,age)
    ->  );
Query OK, 0 rows affected (0.02 sec)

查看组合索引

mysql> show create table t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `id` int(11) NOT NULL,
  `name` char(30) COLLATE utf8_bin NOT NULL,
  `age` int(11) NOT NULL,
  `info` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  KEY `MultiIdx` (`id`,`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

组合索引可以起几个索引的作用，但是使用时并不是随意查询哪个字段都是可以使用索引。而是遵循最左前缀：利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。

全文索引

全文索引：FULLTEXT，可以用于全文搜索，5.7版本以前只有MyISAM存储引擎支持，而5.7版本以后innodb存储引擎也支持，并且只为CHAR\VARCHAR和TEXT 列。索引总是对整个列进行，不支持局部索引，适合大型数据的表

创建全文索引

mysql> CREATE TABLE t4
    ->  (
    ->  id INT NOT NULL,
    ->  name CHAR(30) NOT NULL,
    ->  age INT NOT NULL,
    ->  info VARCHAR(255),
    ->  FULLTEXT INDEX FullIdx(info(100))
    ->  )ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

查看全文索引

mysql> show create table t4\G
*************************** 1. row ***************************
       Table: t4
Create Table: CREATE TABLE `t4` (
  `id` int(11) NOT NULL,
  `name` char(30) COLLATE utf8_bin NOT NULL,
  `age` int(11) NOT NULL,
  `info` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  FULLTEXT KEY `FullIdx` (`info`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

例：创建xx34表，info列为全文索引并搜索。

MATCH (col1,col2,…) AGAINST (expr [search_modifier])

 create table xx34(id int,name varchar(20),info varchar(255),fulltext index chidx(info) with parser ngram);
 select * from xx34 where match(info) against("主义");

空间索引

空间索引：必须在MyISAM类型的表中创建，且空间类型的字段必须为非空。

创建空间索引

mysql>  CREATE TABLE t5
    ->  (
    ->  g GEOMETRY NOT NULL,
    ->  SPATIAL INDEX spaIdx(g) 
    ->  )ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

查看空间索引

mysql> show create table t5\G
*************************** 1. row ***************************
       Table: t5
Create Table: CREATE TABLE `t5` (
  `g` geometry NOT NULL,
  SPATIAL KEY `spaIdx` (`g`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

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

Alter table 表名 add [unique唯一索引|fulltext全文索引|spatial空间索引] [index|key] [默认索引名] (定义
索引名[length]) [asc|desc]

添加索引

在book表添加索引

mysql> alter table book add index BKNameIdx(bookname(30));
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

查看索引

mysql> show index from book\G
*************************** 1. row ***************************
        Table: book
   Non_unique: 1
     Key_name: year_publication
 Seq_in_index: 1
  Column_name: year_publication
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: book
   Non_unique: 1
     Key_name: BKNameIdx
 Seq_in_index: 1
  Column_name: bookname
    Collation: A
  Cardinality: 0
     Sub_part: 30
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
2 rows in set (0.00 sec)

添加唯一索引

添加索引

mysql> alter table book add unique index Uniqldx(bookid);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

查看索引

mysql> show index from book\G
*************************** 1. row ***************************
        Table: book
   Non_unique: 0
     Key_name: Uniqldx
 Seq_in_index: 1
  Column_name: bookid
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: book
   Non_unique: 1
     Key_name: year_publication
 Seq_in_index: 1
  Column_name: year_publication
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 3. row ***************************
        Table: book
   Non_unique: 1
     Key_name: BKNameIdx
 Seq_in_index: 1
  Column_name: bookname
    Collation: A
  Cardinality: 0
     Sub_part: 30
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
3 rows in set (0.00 sec)

添加单列索引

添加索引

mysql> alter table book add index BKidex(comment(50));
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

查看索引

mysql> show index from book\G
*************************** 1. row ***************************
        Table: book
   Non_unique: 0
     Key_name: Uniqldx
 Seq_in_index: 1
  Column_name: bookid
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: book
   Non_unique: 1
     Key_name: year_publication
 Seq_in_index: 1
  Column_name: year_publication
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 3. row ***************************
        Table: book
   Non_unique: 1
     Key_name: BKNameIdx
 Seq_in_index: 1
  Column_name: bookname
    Collation: A
  Cardinality: 0
     Sub_part: 30
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 4. row ***************************
        Table: book
   Non_unique: 1
     Key_name: BKidex
 Seq_in_index: 1
  Column_name: comment
    Collation: A
  Cardinality: 0
     Sub_part: 50
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
4 rows in set (0.00 sec)

添加全文索引

添加索引

mysql> CREATE TABLE t6
    ->  (
    ->  id INT NOT NULL,
    ->  info CHAR(255)
    ->  )ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE t6 ADD FULLTEXT INDEX InfoFULIdx(info);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

查看全文索引

mysql> show index from t6\G
*************************** 1. row ***************************
        Table: t6
   Non_unique: 1
     Key_name: InfoFULIdx
 Seq_in_index: 1
  Column_name: info
    Collation: NULL
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: FULLTEXT
      Comment: 
Index_comment: 
1 row in set (0.00 sec)

添加组合索引

添加索引

mysql>  ALTER TABLE book ADD INDEX BKAUthAndInfoIdx(authors(20),info(50));
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

查看索引

mysql> show index from book\G
*************************** 1. row ***************************
        Table: book
   Non_unique: 0
     Key_name: Uniqldx
 Seq_in_index: 1
  Column_name: bookid
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: book
   Non_unique: 1
     Key_name: year_publication
 Seq_in_index: 1
  Column_name: year_publication
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 3. row ***************************
        Table: book
   Non_unique: 1
     Key_name: BKNameIdx
 Seq_in_index: 1
  Column_name: bookname
    Collation: A
  Cardinality: 0
     Sub_part: 30
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 4. row ***************************
        Table: book
   Non_unique: 1
     Key_name: BKidex
 Seq_in_index: 1
  Column_name: comment
    Collation: A
  Cardinality: 0
     Sub_part: 50
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 5. row ***************************
        Table: book
   Non_unique: 1
     Key_name: BKAUthAndInfoIdx
 Seq_in_index: 1
  Column_name: authors
    Collation: A
  Cardinality: 0
     Sub_part: 20
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 6. row ***************************
        Table: book
   Non_unique: 1
     Key_name: BKAUthAndInfoIdx
 Seq_in_index: 2
  Column_name: info
    Collation: A
  Cardinality: 0
     Sub_part: 50
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
6 rows in set (0.00 sec)

添加空间索引

添加索引

mysql> CREATE TABLE t7
    ->  (
    ->  g GEOMETRY NOT NULL
    ->  )ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE t7 ADD SPATIAL INDEX SpatIdx(g);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

查看索引

mysql> show index from t7\G
*************************** 1. row ***************************
        Table: t7
   Non_unique: 1
     Key_name: SpatIdx
 Seq_in_index: 1
  Column_name: g
    Collation: A
  Cardinality: NULL
     Sub_part: 32
       Packed: NULL
         Null: 
   Index_type: SPATIAL
      Comment: 
Index_comment: 
1 row in set (0.00 sec)

创建索引

创建一个book1表

mysql> create table book1 (bookid int not null, bookname varchar(255) not null,
-> authors varchar(255) not null, info varchar(255) null, comment varchar(255) null,
-> year_publication year not null );
Query OK, 0 rows affected (0.01 sec)

普通索引

mysql> create index bknameidex on book1(bookname);
Query OK, 0 rows affected (0.34 sec)
Records: 0 Duplicates: 0 Warnings: 0

单列索引

mysql> create index bkcmtidex on book1 (comment(50));
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

组合索引

mysql> create index bkauthandinfoidex on book1(authors(30),info(50));
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

全文索引

mysql> drop table t6;
Query OK, 0 rows affected (0.00 sec)
mysql> create table t6 ( id int not null, info char(255))engine=myisam;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE FULLTEXT INDEX FullIdex ON t6(info);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

唯一索引

mysql> create unique index uniqidx on book1(bookid);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

空间索引

mysql> drop table t7;
Query OK, 0 rows affected (0.00 sec)
mysql> create table t7 ( g geometry not null )engine=myisam;
Query OK, 0 rows affected (0.00 sec)
mysql> create spatial index spaidx on t7(g);
Query OK, 0 rows affected (0.00 sec)

Records: 0 Duplicates: 0 Warnings: 0

删除索引

先查看book表的索引

mysql> show create table book\G
*************************** 1. row ***************************
       Table: book
Create Table: CREATE TABLE `book` (
  `bookid` int(11) NOT NULL,
  `bookname` varchar(255) COLLATE utf8_bin NOT NULL,
  `authors` varchar(255) COLLATE utf8_bin NOT NULL,
  `info` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `comment` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `year_publication` year(4) NOT NULL,
  KEY `year_publication` (`year_publication`),
  KEY `bknameidx` (`bookname`(30))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

使用alter命令删除索引后，再次查看book表已经删除bknameidx索引。

mysql> alter table book drop index bknameidx;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> show create table book\G
*************************** 1. row ***************************
       Table: book
Create Table: CREATE TABLE `book` (
  `bookid` int(11) NOT NULL,
  `bookname` varchar(255) COLLATE utf8_bin NOT NULL,
  `authors` varchar(255) COLLATE utf8_bin NOT NULL,
  `info` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `comment` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `year_publication` year(4) NOT NULL,
  KEY `year_publication` (`year_publication`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

使用drop index删除

mysql> show create table t7\G
*************************** 1. row ***************************
       Table: t7
Create Table: CREATE TABLE `t7` (
  `g` geometry NOT NULL,
  SPATIAL KEY `spatidx` (`g`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

mysql> drop index spatidx on t7;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t7\G
*************************** 1. row ***************************
       Table: t7
Create Table: CREATE TABLE `t7` (
  `g` geometry NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

点赞
收藏
关注作者

0/1000

抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认取消

加入云驻计划，成为创作者

华为云周边好礼
免费体验产品
特殊身份标识
线下官方门票
内部专家零距离
与10000+优质创作者共同成长

立即加入

MySQL-索引

索引介绍

索引的优势

增加索引

索引的分类

创建索引的规则

创建索引

普通索引

explain

唯一索引

单列索引

组合索引

全文索引

空间索引

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

添加唯一索引

添加单列索引

添加全文索引

添加组合索引

添加空间索引

创建索引

普通索引

单列索引

组合索引

全文索引

唯一索引

空间索引

删除索引

全部回复

设置昵称

关于作者

目录

加入云驻计划，成为创作者

MySQL-索引

索引介绍

索引的优势

增加索引

索引的分类

创建索引的规则

创建索引

普通索引

explain

唯一索引

单列索引

组合索引

全文索引

空间索引

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

添加唯一索引

添加单列索引

添加全文索引

添加组合索引

添加空间索引

创建索引

普通索引

单列索引

组合索引

全文索引

唯一索引

空间索引

删除索引

全部回复

设置昵称

关于作者

目录

加入云驻计划，成为创作者

推荐阅读

相关产品