索引的简介
什么是索引?
索引是一个单独的,存储在磁盘中上的数据库结构,它们包含着对数据表里的所有记录的引用指针。使用索引可以快速的找出在某列或多列中有特定值的行。
索引的优点:
通过创建唯一索引,来保证数据库表中的每一行数据的唯一性。
• 可以加快数据的检索速度。
• 可以保证表数据的完整性与准确性
索引的缺点:
索引需要占用物理空间。
• 对表中的数据进行改动时,索引也需要跟着动态维护,降低了数据的维护速度。
索引的常见类型:
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)