索引
数据库的索引就像一本书的目录,能够加快数据库的查询速度。
MYSQL索引有四种单列索引 PRIMARY、INDEX、UNIQUE、FULLTEXT, 其中PRIMARY、INDEX、UNIQUE是一类,FULLTEXT是一类。
这四种都是单列索引,也就是他们都是作用于单个一列,所以也称单列索引;
但是一个索引也可以作用于多个列上,称为组合索引或复合索引。
单列索引
新建一张测试表
CREATE TABLE T_USER( ID INT NOT NULL,USERNAME VARCHAR(16) NOT NULL);
(1)PRIMARY:主键索引。索引列唯一且不能为空;一张表只能有一个主键索引(主键索引通常在建表的时候就指定)
CREATE TABLE T_USER(ID INT NOT NULL,USERNAME VARCHAR(16) NOT NULL,PRIMARY KEY(ID))
(2)NORMAL:普通索引。索引列没有任何限制;
建表时指定
CREATE TABLE T_USER(ID INT NOT NULL,USERNAME VARCHAR(16) NOT NULL,INDEX USERNAME_INDEX(USERNAME(16))) //给列USERNAME建普通索引USERNAME_INDEX
ALTER语句指定
ALTER TABLE T_USER ADD INDEX U_INDEX (USERNAME) //给列USERNAME建普通索引 U_INDEX
删除索引
DROP INDEX U_INDEX ON t_user //删除表t_user中的索引U_INDEX
(3)UNIQUE:唯一索引。索引列的值必须是唯一的,但允许有空;
建表时指定
CREATE TABLE t_user(ID INT NOT NULL,USERNAME VARCHAR(16) NOT NULL,UNIQUE U_INDEX(USERNAME)) //给列USERNAME添加唯一索引T_USER
ALTER语句指定
ALTER TABLE t_user ADD UNIQUE u_index(USERNAME) //给列T_USER添加唯一索引u_index
删除索引
DROP INDEX U_INDEX ON t_user
(4)FULLTEXT:全文搜索的索引。FULLTEXT 用于搜索很长一篇文章的时候,效果最好。用在比较短的文本,如果就一两行字的,普通的 INDEX 也可以。索引的新建和删除和上面一致,这里不再列举...
组合索引(复合索引)
新建一张表
CREATE TABLE T_USER(ID INT NOT NULL,USERNAME VARCHAR(16) NOT NULL,CITY VARCHAR(10),PHONE VARCHAR(10),PRIMARY KEY(ID) )
组合索引就是把多个列加入到统一个索引中,如新建的表T_USER,我们给USERNAME+CITY+PHONE创建一个组合索引
ALTER TABLE t_user ADD INDEX name_city_phone(USERNAME,CITY,PHONE) //组合普通索引ALTER TABLE t_user ADD UNIQUE name_city_phone(USERNAME,CITY,PHONE) //组合唯一索引
这样的组合索引,其实相当于分别建立了(USERANME,CITY,PHONE USERNAME,CITY USERNAME,PHONE)三个索引。
为什么没有(CITY,PHONE)索引呢?这是因为MYSQL组合查询“最左前缀”的结果。简单的理解就是只从最左边开始组合。
并不是查询语句包含这三列就会用到该组合索引:
这样的查询语句才会用到创建的组合索引
SELECT * FROM t_user where USERNAME="parry" and CITY="广州" and PHONE="180"SELECT * FROM t_user where USERNAME="parry" and CITY="广州"SELECT * FROM t_user where USERNAME="parry" and PHONE="180"
这样的查询语句是不会用到创建的组合索引
SELECT * FROM t_user where CITY="广州" and PHONE="180"SELECT * FROM t_user where CITY="广州"SELECT * FROM t_user where PHONE="180"
索引不足之处
(1)索引提高了查询的速度,但是降低了INSERT、UPDATE、DELETE的速度,因为在插入、修改、删除数据时,还要同时操作一下索引文件;
(2)建立索引未见会占用一定的磁盘空间。
索引使用注意事项
(1)只要列中包含NULL值将不会被包含在索引中,组合索引只要有一列含有NULL值,那么这一列对于组合索引就是无效的,所以我们在设计数据库的时候最好不要让字段的默认值为NULL;
(2)使用短索引
如果可能应该给索引指定一个长度,例如:一个VARCHAR(255)的列,但真实储存的数据只有20位的话,在创建索引时应指定索引的长度为20,而不是默认不写。如下
ALTER TABLE t_user add INDEX U_INDEX(USERNAME(16)) 优于 ALTER TABLE t_user add INDEX U_INDEX(USERNAME)
使用短索引不仅能够提高查询速度,而且能节省磁盘操作以及I/O操作。
(3)索引列排序
Mysql在查询的时候只会使用一个索引,因此如果where子句已经使用了索引的话,那么order by中的列是不会使用索引的,所以order by尽量不要包含多个列的排序,如果非要多列排序,最好使用组合索引。
(4)Like 语句
一般情况下不是鼓励使用like,如果非使用,那么需要注意like"%aaa%"不会使用索引;但like“aaa%”会使用索引。
(5)不使用 NOT IN和<>操作
索引方式 HASH和 BTREE比较
(1)HASH
用于对等比较,如"="和" <=>"
(2)BTREE
BTREE索引看名字就知道索引以树形结构存储,通常用在像 "=,>,>=,<,<=、BETWEEN、Like"等操作符查询效率较高;
通过比较发现,我们常用的是BTREE索引方式,当然Mysql默认就是BTREE方式。
原文参考:/parryyang/p/5900926.html
如果觉得《sql:索引:mysql索引PRIMARY NORMAL UNIQUE FULLTEXT 区别和使用场合》对你有帮助,请点赞、收藏,并留下你的观点哦!