【MySQL】索引(下)

于 2025-03-02 08:00:00 发布
阅读量739 收藏 52
点赞数 66
分类专栏： MySQL 文章标签： mysql android 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/2302_81805546/article/details/145834304
版权

欢迎拜访：雾里看山-CSDN博客
本篇主题：【MySQL】索引(下)
发布时间：2025.3.2
隶属专栏：MySQL

在这里插入图片描述

目录

索引操作

主键索引

特点

  • 一个表中，最多有一个主键索引，当然可以使符合主键
  • 主键索引的效率高（主键不可重复）
  • 创建主键索引的列，它的值不能为null，且不能重复
  • 主键索引的列基本上是int

语法

  1. 在创建表的时候，直接在字段名后指定 primary key
create table user1(id int primary key, name varchar(30));
  1. 在创建表的最后，指定某列或某几列为主键索引
create table user2(id int, name varchar(30), primary key(id));
  1. 创建表以后再添加主键
create table user3(id int, name varchar(30));
alter table user3 add primary key(id);

案例

mysql> alter table test1 add primary key(id);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from test1 \G
*************************** 1. row ***************************
        Table: test1
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
1 row in set (0.00 sec)

唯一键索引

特点

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

语法

  1. 在表定义时，在某列后直接指定unique唯一属性。
create table user4(id int primary key, name varchar(30) unique);
  1. 创建表时，在表的后面指定某列或某几列为unique
create table user5(id int primary key, name varchar(30), unique(name));
  1. 创建表以后再添加唯一键
create table user6(id int primary key, name varchar(30)）；
alter table user6 add unique(name);

案例

mysql> alter table test1 add unique(name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from test1 \G
*************************** 1. row ***************************
        Table: test1
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: test1
   Non_unique: 0
     Key_name: name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
2 rows in set (0.01 sec)

普通索引

特点

  • 一个表中可以有多个普通索引，普通索引在实际开发中用的比较多
  • 如果某列需要创建索引，但是该列有重复的值，那么我们就应该使用普通索引

语法

  1. 在表的定义最后，指定某列为索引
create table user8(id int primary key,
	name varchar(20),
	email varchar(30),
	index(name) 
);
  1. 创建完表以后指定某列为普通索引
create table user9(id int primary key, name varchar(20), email varchar(30));
alter table user9 add index(name);
  1. 创建一个索引名为 idx_name 的索引
create table user10(id int primary key, name varchar(20), email varchar(30));
create index idx_name on user10(name);

案例

mysql> alter table test1 add index(name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

mysql> create index myindex on test1(name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from test1 \G
*************************** 1. row ***************************
        Table: test1
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: test1
   Non_unique: 1
     Key_name: myindex
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
2 rows in set (0.00 sec)

复合索引

特点

  • 提高多列查询效率
  • 遵循最左前缀规则
  • 覆盖索引可能性增加
  • 节省索引空间
  • 优化排序操作

语法

  1. 在表的定义最后，指定某几列为索引
create table user8(id int primary key,
	name varchar(20),
	email varchar(30),
	index(name, email) 
);
  1. 创建完表以后指定某列为复合索引
create table user9(id int primary key, name varchar(20), email varchar(30));
alter table user9 add index(name, email);
  1. 创建一个索引名为 idx_name 的复合索引
create table user10(id int primary key, name varchar(20), email varchar(30));
create index idx_name on user10(name, email);

案例

mysql> alter table test1 add index(name,email);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from test1 \G
*************************** 1. row ***************************
        Table: test1
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: test1
   Non_unique: 1
     Key_name: name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 3. row ***************************
        Table: test1
   Non_unique: 1
     Key_name: name
 Seq_in_index: 2
  Column_name: email
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
3 rows in set (0.00 sec)

全文索引

当对文章字段或有大量文字的字段进行检索时，会使用到全文索引。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.02 sec)

mysql> desc articles;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| title | varchar(200)     | YES  | MUL | NULL    |                |
| body  | text             | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 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.02 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from articles;
+----+-----------------------+------------------------------------------+
| id | title                 | body                                     |
+----+-----------------------+------------------------------------------+
|  1 | MySQL Tutorial        | DBMS stands for DataBase ...             |
|  2 | How To Use MySQL Well | After you went through a ...             |
|  3 | Optimizing MySQL      | In this tutorial we will show ...        |
|  4 | 1001 MySQL Tricks     | 1. Never run mysqld as root. 2. ...      |
|  5 | MySQL vs. YourSQL     | In the following database comparison ... |
|  6 | MySQL Security        | When configured properly, MySQL ...      |
+----+-----------------------+------------------------------------------+
6 rows in set (0.02 sec)

查询有没有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 ... |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)

可以用explain工具看一下，是否使用到索引

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.03 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 ...             |
+----+-------------------+------------------------------------------+
2 rows in set (0.01 sec)

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用到了title
      key_len: 0
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

查询索引

  1. 第一种方法: show index from 表名;
mysql> show index from test1 \G
*************************** 1. row ***************************
        Table: test1       <= 表名
   Non_unique: 0           <= 0表示唯一索引
     Key_name: PRIMARY     <= 主键索引
 Seq_in_index: 1           
  Column_name: id          <= 索引在哪列
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE       <= 以B+树形式的索引
      Comment: 
Index_comment: 
1 row in set (0.03 sec)
  1. show keys from 表名
  2. desc 表名；（信息比较简略）

删除索引

  1. 第一种方法-删除主键索引： alter table 表名 drop primary key;
mysql> alter table test1 drop primary key;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0 

mysql> show index from test1 \G
Empty set (0.00 sec)
  1. 其他索引的删除： alter table 表名 drop index 索引名；
    索引名就是show keys from 表名中的 Key_name字段
mysql> alter table test1 drop index name;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
  1. drop index 索引名 on 表名

索引创建原则

  • 比较频繁作为查询条件的字段应该创建索引
  • 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
  • 更新非常频繁的字段不适合作创建索引
  • 不会出现在where子句中的字段不该创建索引

⚠️ 写在最后：以上内容是我在学习以后得一些总结和概括，如有错误或者需要补充的地方欢迎各位大佬评论或者私信我交流！！！

评论 43
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

雾里看山

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值