mysql必备知识之常用索引(普通索引与唯一索引)

索引的简介

什么是索引?

索引是一个单独的,存储在磁盘中上的数据库结构,它们包含着对数据表里的所有记录的引用指针。使用索引可以快速的找出在某列或多列中有特定值的行。

索引的优点:

通过创建唯一索引,来保证数据库表中的每一行数据的唯一性。
• 可以加快数据的检索速度。
• 可以保证表数据的完整性与准确性

索引的缺点:

索引需要占用物理空间。
• 对表中的数据进行改动时,索引也需要跟着动态维护,降低了数据的维护速度。

索引的常见类型:

index:普通索引

• unique:唯一索引

• primary key:主键索引

• foreign key:外键索引

• fulltext: 全文索引

• 组合索引

普通索引与唯一索引

什么是普通索引

普通索引(index)顾名思义就是各类索引中最为普通的索引,主要任务就是提高查询速度。其特点是允许出现相同的索引内容,允许空(null)值

什么是唯一索引?

唯一索引:(unique)顾名思义就是不可以出现相同的索引内容,但是可以为空(null)值

如何创建普通索引或者唯一索引?

创建表的时候创建:

mysql> create table test (
                        id int(7) zerofill not null,
                        username varchar(20),
                        servnumber varchar(30),
                        password varchar(20),
                        createtime datetime,
                        index (id)
                  )DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)
mysql> show create table test;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id` int(7) unsigned zerofill NOT NULL,
  `username` varchar(20) DEFAULT NULL,
  `servnumber` varchar(30) DEFAULT NULL,
  `password` varchar(20) DEFAULT NULL,
  `createtime` datetime DEFAULT NULL,
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)

直接为表添加索引:
alter table 表名 add index 索引名称 (字段名称);

mysql> alter table test add unique unique_id (id);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table test;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id` int(7) unsigned zerofill NOT NULL,
  `username` varchar(20) DEFAULT NULL,
  `servnumber` varchar(30) DEFAULT NULL,
  `password` varchar(20) DEFAULT NULL,
  `createtime` datetime DEFAULT NULL,
  UNIQUE KEY `unique_id` (`id`),
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)

直接创建索引
create index 索引 on 表名 (字段名);

mysql> create index index_username on test (username);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show create table test;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                     |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id` int(7) unsigned zerofill NOT NULL,
  `username` varchar(20) DEFAULT NULL,
  `servnumber` varchar(30) DEFAULT NULL,
  `password` varchar(20) DEFAULT NULL,
  `createtime` datetime DEFAULT NULL,
  KEY `id` (`id`),
  KEY `index_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)

查看索引

show index from 表名

mysql> show index from test;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test  |          1 | id             |            1 | id          | A         |           0 | NULL     | NULL   |      | BTREE      |         |               |
| test  |          1 | index_username |            1 | username    | A         |           0 | NULL     | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.04 sec)

如何删除索引

1.drop index 索引名称 on 表名;
2.alter table 表名 drop index 索引名;

mysql> drop index id on test;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table test drop index index_username;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show index from test;
Empty set

mysql> show create table test;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                               |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id` int(7) unsigned zerofill NOT NULL,
  `username` varchar(20) DEFAULT NULL,
  `servnumber` varchar(30) DEFAULT NULL,
  `password` varchar(20) DEFAULT NULL,
  `createtime` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)
  • 2
    点赞
  • 15
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值