学习MYSQL笔记分享(三)——索引

Valky
阅读24 喜欢0 教程 更新2019-11-26

一、索引概述 

索引是一张描述所以咧的列值与原配表中的记录行之间——对应关系的有序表,可以加快数据库的查询速度,用来定位。 
(1)顺序访问 
(2)索引访问 select * from table1 where id=10000有索引和没索引的区别 

二、索引的存储与分类 

索引在物理上分为 

(1)B-树索引,目前大部分索引以这种方法存储,也叫BTREE索引,采用树的数据结构形式存储文件 - 聚集索引:键值相近的记录元组存储的物理位置相同,一个表只能有一个聚集索引 - 覆盖索引:包含满足查询的所有数据 
(2)哈希索引,也叫散列索引或 HASH索引,根据索引列对应的哈希值来获取表中的记录行 

索引在逻辑上分为 

(1)普通索引(INDEX),INDEX或KEY创建 
(2)唯一索引(UNIQUE),索引列中所有值只能出现一次 
(3)主键(PRIMARY KEY),每个表只能有一个主键 
(4)全文索引(FULLTEXT), 只能在VARCHAR或TEXT,MyISAM中创建 
(5)空间索引,地理空间数据 

索引在实际应用中 

(1)单列索引 
(2)组合索引(col1,col2,col3)在中分为单列索引,双列索引和多列索引 

三、索引的创建 

1.使用CREATE INDEX创建索引

 CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
 [USING{BTREE|HASH}]
 ON tbl_name(col_name[(length)] [ASC|DESC],...)

不能使用CREATE INDEX 创建PRIMARY KEY索引

CREATE INDEX index_customers
ON customers(cust_name(3) ASC);
//在customers表中,根据客户姓名列的前三个字符采用默认索引类型创建一个升序索引(ASC)index_customers
SHOW INDEX FROM //查看
CREATE INDEX index_cust
USING BTREE
ON customers(cust_name,cust_id);
//在表customers中根据cust_name和cust_id采用BTREE创建复合索引index_cust
USE db_test;
CREATE INDEX index_subject
ON content(subject(3) ASC);
//用CREATE INDEX在db_test数据库的content表中,根据留言标题列的前三个字符采用默认的索引类型创建一个升序索引index_subject


2.使用CREATE TABLE创建索引

CREATE TABLE tbl_name(...
INDEX | KEY [index_name](index_col_name,...)
PRIMARY KEY (index_col_name,...)
UNIQUE [INDEX|KEY][index_name](index_col_name,...)

注意:数据库管理系统对于主键自动生成唯一索引,所以也是一个特殊的索引。 主键和索引的区别: 一个表 有多个唯一性索引,只有一个主键 主键一般要建,避免一张表中有相同记录,索引一般可以不建

USE mysql_test;
CREATE TABLE seller(
    seller_id int NOT NULL AUTO_INCREMENT,
    seller_name char(50) NOT NULL,
    seller_address char(50) NOT NULL,
    seller_contact char(50) NOT NULL,
    product_type int(5) NULL,
    sales int NULL,
    PRIMARY KEY (seller_id,product_type),
    INDEX index_seller(sales)
);
//在mysql_test数据库上创建一个包含卖家id、姓名、 地址、联系方式、售卖产品类型、当月销量内容的产品卖家信息表seller,在创建表的同时添加由卖家id和售卖产品类型组成的联合主键,并在当月销量上创建索引。
CREATE TABLE tbl_name(i int NOT NULL PRIMARY KEY)
=
CREATE TABLE rbl_name(i int NOT NULL,PRIMARY KEY(i))


3.使用ALTER TABLE创建索引

ALTER TABLE tbl_name ADD PRIMARY KEY(index_col_name,...)
ALTER TABLE tbl_name ADD INDEX [index_name](index_col_name,...)
ALTER TABLE tbl_name ADD UNIQUE [INDEX|KEY][index_name](index_col_name,...)
ALTER TABLE seller
ADD INDEX index_seller_name(seller_name);
//使用ALTER TABLE 语句在表seller的姓名列上添加一个唯一索引,名为index_seller_name

题目: 1.创建普通索引时,通常使用的关键字是INDEX或KEY 2.创建唯一性索引时,通常使用的关键字是UNIQUE 四、索引的查看

SHOW {INDEX|INDEXES|KEYS} {FROM|IN} tbl_name [{FROM|IN} db_name] [WHERE expr]


、索引的删除 1.使用DROP INDEX 删除索引

DROP INDEXX indexx_name ON tbl_name
DROP INDEX index_cust ON customers; //删除customers中的索引index_cust


2.使用ALTER TABLE删除索引

ALTER TABLE tbl_name DROP PRIMARY KEY
ALTER TABLE tbl_name DROP INDEX index_name
ALTER TABLE customers DROP PRIMARY KEY,DROP INDEX index_customers;
//使用ALTER TABLE删除customers的主键和索引index_customers

注意:所删除的列是索引的组成部分,则该列也从索引中删除组成索引的所有列都删除,整个索引将被删除。