MySQL之索引(索引作用与代价、分类、基本操作)_索引有何作用?使用索引有何代价?-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_43683907/article/details/98362412

本文详细介绍了MySQL索引的作用，包括如何提升查询速度但牺牲写操作效率，以及索引的分类：主键、唯一、普通和全文索引。同时，文章讲解了索引的基本原理，通过实例对比了使用索引前后的查询性能差异，并提供了创建、查询和删除索引的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

索引特性

1.索引：
1.1作用：
索引在数据库中的作用与目录在书籍中的作用类似，都是用来提高查找信息的速度。索引提高数据库的性能，不用加内存，不用修改程序，不用调sql，只要执行正确的create index ，查询的速度就可以提高成百上千倍。因此索引的价值在于提高一个海量数据的检索速度。
1.2代价：查询速度提高是以数据修改操作(插入、更新、删除)的速度为代价的，这些写操作，增加了大量的IO。另外索引还会在硬盘上占用相当大的空间。(以空间换时间)

2.索引分类：

主键索引(primary key)
唯一索引(unique)
普通索引(index)
全文索引(fulltext)

3.索引基本原理
基于一张海量数据表分别进行查询

未使用索引查询

mysql> select * from emp where empno=998877;
+--------+--------+----------+------+---------------------+---------+--------+--------+
| empno  | ename  | job      | mgr  | hiredate            | sal     | comm   | deptno |
+--------+--------+----------+------+---------------------+---------+--------+--------+
| 998877 | eAnNbS | SALESMAN | 0001 | 2019-07-26 00:00:00 | 2000.00 | 400.00 |    105 |
+--------+--------+----------+------+---------------------+---------+--------+--------+
1 row in set (5.41 sec)

可以看出耗时为5.41秒，这还只是在本机一个人操作的情况下

使用索引查询

mysql> alter table EMP add index(empno);
mysql> select * from emp where empno=123456;
+--------+--------+----------+------+---------------------+---------+--------+--------+
| empno  | ename  | job      | mgr  | hiredate            | sal     | comm   | deptno |
+--------+--------+----------+------+---------------------+---------+--------+--------+
| 123456 | JYWOee | SALESMAN | 0001 | 2019-07-26 00:00:00 | 2000.00 | 400.00 |     88 |
+--------+--------+----------+------+---------------------+---------+--------+--------+
1 row in set (0.01 sec)

在数据库中查找数据时，存在两种方法：第一种就是全表扫描，即查找所需要的数据要从表的第一条记录开始扫描，一直到表的最后一条记录(上述中的未使用索引查询语句结果)；第二种方法：使用索引查询，添加字段索引后，索引会形成一颗二叉树，采用二分查找的思想，对于8000000，最多只查找23次。

4.创建索引

4.1创建主键索引

第一种方式：在创建表时，直接在字段名后指定primary key

create table user1(id int primary key,name varchar(20));

第二种方式：在创建表的最后，指定某列为主键索引

create table user2(id int,name varchar(20), primary key(id));

第三种方式：创建表后再添加主键

create table user3(id int,name varchar(20));
alter table user3 add primary key(id);

主键索引的特点：

一张表中，最多有一个主键索引
主键索引的效率高(主键不可重复)
创建主键索引的列，它的值不能为null,且不能重复
主键索引的列基本上都是int型

4.2唯一索引的创建

第一种方式：在定义表时，在某列后面指定unique唯一属性

create table user4(id int primary key,name varchar(20) unique);

第二种方式：创建表时，在表的后面指定某列或者某几列为unique

create table user5(id int primary key,name varchar(20),unique(name));

第三种方式：创建表后再添加唯一索引

create table user6(id int primary key,name varchar(20));
alter table user6 add unqiue(name);

唯一索引的特点：

一个表中可以有多个唯一索引
查询效率高
如果在某一列建立唯一索引，必须保证该列不能有重复数据
如果一个唯一索引上指定not null，等价于主键索引

4.3普通索引的创建

第一种方式：在表的最后定义，指定某列为索引

create table user8(id int primary key,
     name varchar(20),
     email varchar(30),
     index(name)
     );

第二种方式：创建完表后指定某列为普通索引

create table8(id int primary key,
     name varchar(20),
     email varchar(30)
);
alter table user8 add index(name);

第三种方式：创建一个索引名为idx_name的索引

create table user9(id int primary key,
      name varchar(20),
      email varchar(30)
);
create index idx_name on user9(name);

普通索引特点：

一张表可以有多个普通索引，它在实际开发中使用较多
如果某列需要创建索引，但是该列有重复值时，就要使用普通索引

4.4全文索引
当对文章字段或有大量文字的字段进行检索时，会使用到全文索引。MySQL提供全文索引机制，要求表的存储引擎必须是MyISAM，而且默认的全文检索支持英文，不支持中文。如果对中文进行全文检索，可以使用sphinx的中文版(coreseek)

--创建表
mysql> CREATE TABLE articles (
    -> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    -> title VARCHAR(200),
    -> body TEXT,
    -> FULLTEXT (title,body)
    -> )engine=MyISAM;
Query OK, 0 rows affected (0.03 sec)

--添加记录
mysql> INSERT INTO articles (title,body) VALUES
    -> ('MySQL Tutorial','DBMS stands for DataBase ...'),
    -> ('How To Use MySQL Well','After you went through a ...'),
    -> ('Optimizing MySQL','In this tutorial we will show ...'),
    -> ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
    -> ('MySQL vs. YourSQL','In the following database comparison ...'),
    -> ('MySQL Security','When configured properly, MySQL ...');
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

查询有没有’database’数据

如果使用如下的查询方式，虽然查询出数据，但是没有使用到全文索引

mysql> select * from articles where body like '%database%';
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
+----+-------------------+------------------------------------------+

通过explain工具查看key,确定是否使用到全文索引

mysql> explain select * from articles where body like '%database%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: articles
   partitions: NULL
         type: ALL
possible_keys: NULL
          ~~key: NULL~~   ==>key为null,表示没有用到索引
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 16.67
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

采用以下方式使用全文索引

mysql> select * from articles
    -> where match(title,body) against('database');
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
+----+-------------------+------------------------------------------+

使用explain工具分析这个sql语句

mysql> explain select * from articles where match(title,body) against('database')\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: articles
   partitions: NULL
         type: fulltext
possible_keys: title
          ~~key: title~~   ==>key为建表时定义的全文索引字段
      key_len: 0
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

5.查询索引

第一种方法：
show keys from table_name

mysql> show keys from articles\G
*************************** 1. row ***************************
        Table: articles   ==>表名
   Non_unique: 0      ==>0表示唯一索引
     Key_name: PRIMARY   ==>主键索引
 Seq_in_index: 1
  Column_name: id    ==>索引在哪个字段
    Collation: A
  Cardinality: 6
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE       ==>以二叉树形式的索引
      Comment:
Index_comment:

第二种方法：
show index from table_name;
第三种方法：desc table_name;

mysql> desc articles\G
*************************** 1. row ***************************
  Field: id
   Type: int(10) unsigned
   Null: NO
    Key: PRI
Default: NULL
  Extra: auto_increment

6.删除索引

删除主键索引 alter table table_name drop primary key;
删除其他索引alter table table_name drop index 索引名 索引名就是show keys from 表名中的 key_name字段
删除索引drop index 索引名 on table_name

mysql> drop index title on articles;
Query OK, 6 rows affected (0.02 sec)