IT序号网

mysql操作命令梳理(1)-索引

lxf 2021年06月14日 数据库 335 0

1、创建索引
索引的创建可以在CREATE TABLE语句中进行,也可以单独用CREATE INDEX或ALTER TABLE来给表增加索引。以下命令语句分别展示了如何创建主键索引(PRIMARY KEY),联合索引(UNIQUE)和普通索引(INDEX)的方法。

?
1
2
3
4
5
mysql> ALTER TABLE 表名 ADD INDEX 索引名 列名;
mysql> ALTER TABLE 表名 ADD UNIQUE 索引名 列名;
mysql> ALTER TABLE 表名 ADD PRIMARY KEY 索引名 列名;
mysql> CREATE INDEX 索引名 ON 表名 列名;
mysql> CREATE UNIQUE INDEX 索引名 ON 表名 列名;
?
1
2
3
4
例如:
mysql> ALTER TABLE `article` ADD INDEX `id`;               //给article表增加id索引
或者:
mysql> ALTER TABLE `article` ADD INDEX (`id`,`order_id`);         //给article表增加id索引,order_id索引

2、重建索引
重建索引在常规的数据库维护操作中经常使用。在数据库运行了较长时间后,索引都有损坏的可能,这时就需要重建。对数据重建索引可以起到提高检索效率。

?
1
mysql> REPAIR TABLE 表名 QUICK;

3、查询数据表索引
MySQL查询表索引命令的有两种命令形式:

?
1
2
3
mysql> SHOW INDEX FROM 表名;
或者:
mysql> SHOW keys FROM 表名;

比如:

?
1
2
3
4
5
6
7
8
9
10
mysql> SHOW INDEX FROM uc_member;
+ -----------+------------+---------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
| Table     | Non_unique | Key_name            | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+ -----------+------------+---------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
| uc_member |          0 | PRIMARY             |            1 | member_id       | A         |        1099 |     NULL | NULL   |      | BTREE      |         |
| uc_member |          1 | idx_nickname_passwd |            1 | member_nickname | A         |         549 |     NULL | NULL   |      | BTREE      |         |
| uc_member |          1 | idx_nickname_passwd |            2 | member_password | A         |        1099 |     NULL | NULL   |      | BTREE      |         |
| uc_member |          1 | member_mobile       |            1 | member_mobile   | A         |        1099 |     NULL | NULL   |      | BTREE      |         |
+ -----------+------------+---------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.00 sec)

解释:
Non_unique 如果索引不能包括重复词,则为0。如果可以,则为1。
Key_name 索引的名称。
Seq_in_index 索引中的列序列号,从1开始。
Column_name 列名称。
Collation 列以什么方式存储在索引中。在MySQL中,有值‘A'(升序)或NULL(无分类)。
Cardinality 索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数,所以即使对于小型表,该值也没有必要是精确的。基数越大,当进行联合时,MySQL使用该索引的机 会就越大。
Sub_part 如果列只是被部分地编入索引,则为被编入索引的字符的数目。如果整列被编入索引,则为NULL。
Packed 指示关键字如何被压缩。如果没有被压缩,则为NULL。
Null 如果列含有NULL,则含有YES。如果没有,则该列含有NO。
Index_type 用过的索引方法(BTREE, FULLTEXT, HASH, RTREE)。

4、删除索引
删除索引可以使用ALTER TABLE或DROP INDEX语句来实现。
DROP INDEX可以在ALTER TABLE内部作为一条语句处理,其格式如下:

?
1
2
3
4
mysql> DROP index 索引名 ON 表名 列名;
mysql> ALTER TABLE 表名 DROP INDEX 索引名 列名;
mysql> ALTER TABLE 表名 DROP UNIQUE 索引名 列名;
mysql> ALTER TABLE 表名 DROP PRIMARY KEY 索引名 列名;

在上面前三条语句中,都删除了table_name中的索引index_name。
而在最后一条语句中,只在删除PRIMARY KEY索引中使用,因为一个表只可能有一个PRIMARY KEY索引,因此也可不指定索引名。
如果没有创建PRIMARY KEY索引,但表具有一个或多个UNIQUE索引,则MySQL将删除第一个UNIQUE索引。
如果从表中删除某列,则索引会受影响。对于多列组合的索引,如果删除其中的某列,则该列也会从索引中删除。
如果删除组成索引的所有列,则整个索引将被删除。

---------------------------------------索引、主键、唯一索引、联合索引梳理--------------------------------------------
说下Mysql索引概念:
索引就好比一本书的目录,它会让你更快的找到内容,显然目录(索引)并不是越多越好,假如这本书1000页,有500也是目录,它当然效率低,目录是要占纸张的,而索引是要占磁盘空间的。

Mysql索引主要有两种结构:hash和B+树:
hash:hsah索引在mysql比较少用,他以把数据的索引以hash形式组织起来,因此当查找某一条记录的时候,速度非常快.当时因为是hash结构,每个键只对应一个值,而且是散列的方式分布.所以他并不支持范围查找和排序等功能.
B+树:b+tree是mysql使用最频繁的一个索引数据结构,数据结构以平衡树的形式来组织,因为是树型结构,所以更适合用来处理排序,范围查找等功能.相对hash索引,B+树在查找单条记录的速度虽然比不上hash索引,但是因为更适合排序等操作,所以他更受用户的欢迎.毕竟不可能只对数据库进行单条记录的操作.

Mysql常见索引有:主键索引、唯一索引、普通索引、全文索引、组合索引

?
1
2
3
4
5
PRIMARY KEY (主键索引):    ALTER TABLE 表名 ADD PRIMARY KEY 列名
UNIQUE (唯一索引):     ALTER TABLE 表名 ADD UNIQUE 列名
INDEX (普通索引):    ALTER TABLE 表名 ADD INDEX 索引名 列名
FULLTEXT(全文索引):  ALTER TABLE 表名 ADD FULLTEXT 列名
组合索引: ALTER TABLE 表名 ADD INDEX 索引名 (列名1,列名2, 列名3)

Mysql各种索引区别:
普通索引:最基本的索引,没有任何限制
唯一索引:与"普通索引"类似,不同的就是:索引列的值必须唯一,但允许有空值。
主键索引:它 是一种特殊的唯一索引,不允许有空值。
全文索引:仅可用于 MyISAM 表,针对较大的数据,生成全文索引很耗时好空间。
组合索引:为了更多的提高mysql效率可建立组合索引,遵循”最左前缀“原则。

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针。
普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。

普通索引允许被索引的数据列包含重复的值。如果能确定某个数据列将只包含彼此各不相同的值,在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。也就是说,唯一索引可以保证数据记录的唯一性。

主键,是一种特殊的唯一索引,在一张表中只能定义一个主键索引,主键用于唯一标识一条记录,使用关键字 PRIMARY KEY 来创建。
索引可以覆盖多个数据列,如像INDEX(columnA, columnB)索引,这就是联合索引。

主键分为复合主键和联合主键
复合主键就是指你表的主键含有一个以上的字段组成 。
例如:

?
1
2
3
4
5
6
7
create table test
(
name varchar (19),
id number,
value varchar (10),
primary key (id, name )
)

上面的id和name字段组合起来就是你test表的复合主键 (若其一为单索引字段时,左边的id才会有索引)
它的出现是因为你的name字段可能会出现重名,所以要加上ID字段这样就可以保证你记录的唯一性
一般情况下,主键的字段长度和字段数目要越少越好。

联合主键,顾名思义就是多个主键联合形成一个主键组合,体现在联合。
(主键原则上是唯一的,别被唯一值所困扰。)
索引可以极大的提高数据的查询速度,但是会降低插入、删除、更新表的速度,因为在执行这些写操作时,还要操作索引文件。

举一个简单的例子
主键A跟主键B组成联合主键
主键A跟主键B的数据可以完全相同(困扰吧,没关系),联合就在于主键A跟主键B形成的联合主键是唯一的。
下例主键A数据是1,主键B数据也是1,联合主键其实是11,这个11是唯一值,绝对不充许再出现11这个唯一值。(这就是多对多关系)
主键A数据 主键B数据
1      1
2      2
3      3
主键A与主键B的联合主键值最多也就是
11
12
13
21
22
23
31
32
33


评论关闭
IT序号网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!