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: 0mysql> 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: 0mysql> 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 setmysql> 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)
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！