- 创建索引:
创建索引主要有几种形式,主要区分是在建表的前后进行创建索引。
建表的时候创建索引:
CREATE TABLE table_name(
column1 类型,
column2 类型,
...
key/index key/index_的名字(column1,column2) -- 括号的字段可以有多个,这时候就是联合索引了
);
建表之后创建,删除索引:
#建表之后创建索引,但是这样创建索引的话,只能创建一个索引
CREATE INDEX/KEY 索引的名字 ON table_name(column1,cloumn2,......,columnN);
或者可以利用ALTER TABLE来实现创建索引。
# 创建索引,这时候括号中有多个字段,这时候是联合索引。
ALTER TABLE table_name ADD INDEX/KEY 索引的名字(column1,column2,.....columnN);
# 如果需要添加多个索引的时候,虽然可以写多个ALTER TABLE语句,但是可以只写语句
# 只需要写多个ADD即可。
ALTER TABLE table_name ADD INDEX/KEY 索引名字1(column1,column2,......columnN),
ADD INDEX/KEY 索引名字2(column1,column2,......columnN);
- 删除索引
# 建表之后删除索引,但是这一种方式只能删除一种索引,并不能一次删除多个索引
DROP INDEX/KEY 索引的名字 ON table_name;
# 删除单个索引
ALTER TABLE table_name DROP INDEX/KEY 索引名字;
# 如果需要删除多个索引时,虽然可以写多个ALTER TABLE语句,但是可以只写一个,只是需要
# 写多个DROP INDEX/KEY子句。
ALTER TABLE table_name DROP INDEX/KEY 索引名字1,
DROP INDEX/KEY 索引名字2,
DROP INDEX/KEY 索引名字n;
利用SHOW INDEX FROM table_name,就可以获取这个表中的索引信息了:
其中获取的结果的各个字段表示的含义如下:
- Table:索引所在的表名
- Non-unique:非唯一的索引。可以看到PRIMARY KEY是0,因为主键必须是唯一的。
- Key-name:表示索引的名字,我们通过这个名字来删除索引。
- Seq_in_index:表示当前这个字段在索引的位置。例如index_id_name(id,name),其中的id将会是1,name的Seq_in_index的值为2.
- Column_name:索引的字段名。例如index_id_name(id,name)中的id,name分别可以作为它的值.
- Collation:列以什么方式存储在索引中。可以是A或者NULL。B+树索引总是A,即排序的。如果使用了Heap存储引擎,并且建立Hash索引,这里就会显示NULL了。因为Hash根据Hash桶来存放索引数据,而不是对数据进行排序。
- Cardinality:非常关键的值,表示索引中唯一值的数目的估计值。Cardinality表的行数应尽可能接近1,如果非常小,那么需要考虑是否还需要建这个索引。
- Sub_part:表示这个字段是一部分被索引,还是全部被索引,如果值为NULL,表示索引整个列,否则如果是一个数字n,表示所以这个列的前n部分。
- Packed:关键字如何被压缩。如果没有被压缩,则为NULL。
- 10.NULL:索引的列中是否含有NULL值。
- Index_type:表示索引的类型。因为InnerDB存储引擎只支持B+树索引,所以显示BTREE。
- Comment:注释。
利用索引进行查询数据:
SELECT column1,column2,columnN FROM table_name
FORCE INDEX(索引名)
WHERE xxx
GROUP BY XXX -- 然而使用GROUP BY的时候,必须需要保证前面检索的column1,column2,columnN和GROUP BY后面的字段名相同,否则就会发生报错
HAVING YYY
ORDER BY ZZZ
LIMIT M OFFSET N;
对应练习:
对first_name创建唯一索引uniq_idx_firstname
针对上面的salaries表emp_no字段创建索引idx_emp_no