索引是什么?
索引就像一本书的目录,方便我们快速找到自己要的内容和数据值,但是也会占一定的磁盘内存。
索引类型:主键索引Primary key、唯一索引Unique、普通索引Index、全文索引Fulltext。前三者是一类,FULLTEXT全文索引是一类,他们都是“单列索引”,只能作用到单个列上(即一个字段),一张表中可以有多个列建立索引,但是如果一个索引要作用到多列就称为“组合索引”或者“复合索引”。
如何创建索引且上述索引在什么情况下使用:
(1)PRIMARY:主键索引,索引列唯一且不能为空;一张表只能有一个主键索引(主键索引通常在建表的时候就指定)
CREATE TABLE T_USER(ID INT NOT NULL,USERNAME VARCHAR(16) NOT NULL,PRIMARY KEY(ID))
(2)UNIQUE:唯一索引,索引列的值必须是唯一的,但允许有空。
- 建表时指定
CREATE TABLE T_USER(ID INT NOT NULL,USERNAME VARCHAR(16) NOT NULL,UNIQUE U_INDEX(USERNAME)) //给列USERNAME添加唯一索引U_INDEX
- ALTER语句指定
ALTER TABLE T_user ADD UNIQUE U_INDEX(USERNAME) //给列USERNAME添加唯一索引U_INDEX
- 删除索引
DROP INDEX U_INDEX ON t_user //删除表t_user中的索引U_INDEX
- 建表时指定
CREATE TABLE T_USER(ID INT NOT NULL,USERNAME VARCHAR(16) NOT NULL,INDEX U_INDEX(USERNAME(16)) //给列USERNAME建普通索引U_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
(4)FULLTEXT:全文搜索的索引。FULLTEXT 用于搜索很长一篇文章的时候,效果最好。用在比较短的文本,如果就一两行字的,普通的 INDEX 也可以。索引的新建和删除和上面一致。
(5)组合索引
CREATE TABLE T_USER(ID INT NOT NULL,USERNAME VARCHAR(16) NOT NULL,CITY VARCHAR(10) NOT NULL,PHONE VARCHAR(11),PRIMARY KEY(ID))
组合索引就是把多个列加到一个索引中来,以下我们来给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)三个索引。
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"
索引不足之处
索引使用注意事项
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
HASH索引查找单条数据时速度快,以键值对的形式存储,呈散列方式分布,并不支持范围查找和排序等功能
(2)BTREE
BTREE索引看名字就知道索引以树形结构存储,通常用在像 "=,>,>=,<,<=、BETWEEN、Like"等操作符查询效率较高;MYSQL是使用最频繁的索引数据结构。是Innodb和MyIsam存储引擎模式的索引类型,查找单条数据没有HASH索引快,但是适合排序,区间查询
通过比较发现,我们常用的是BTREE索引方式,当然Mysql默认就是BTREE方式。
题外知识:存储引擎和BTREE
BTREE在MyIsam引擎中存储的是实际数据的地址值,也就是说它的索引和实际数据是分开的,只不过使用索引指向实际数据,这种索引模式称为非聚集索引
BTREE在innodb引擎中存储的是实际数据值,这种索引称为聚集索引