索引类型:
普通索引
唯一索引
主键索引
组合索引
全文索引
1.普通索引
最基本的索引,没有任何限制
创建方式:
CREATE INDEX `CODE_INDEX` ON `vanora_test` (`code`) USING BTREE;
CREATE TABLE `vanora_test` (
`code` varchar(20) NOT NULL,
`NAME` varchar(30) CHARACTER SET utf8 DEFAULT NULL,
`Phone` varchar(15) DEFAULT NULL,
INDEX `CODE_INDEX` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk
删除索引:
ALTER TABLE `vanora_test` DROP INDEX `CODE_INDEX` ;
2.唯一索引
类似普通索引,不同是:索引列必须唯一,单允许空值。如果是组合索引,则列值组合必须唯一
创建方式:
CREATE UNIQUE INDEX `CODE_INDEX` ON vanora_test(`code`);
CREATE TABLE vanora_test_INDEX (
`code` varchar(20) NOT NULL,
`NAME` varchar(30) CHARACTER SET utf8 DEFAULT NULL,
`Phone` varchar(15) DEFAULT NULL,
UNIQUE INDEX `U_INDEX` (CODE)
) ENGINE=InnoDB DEFAULT CHARSET=gbk
删除索引
ALTER TABLE `vanora_test` DROP INDEX CODE_INDEX
3.主键索引
特殊的唯一索引,一个表只能有一个主键,不允许有空值,一般在新建表的时候会同时创建主键索引
创建方式:
ALTER TABLE VANORA_TEST ADD PRIMARY KEY (CODE);
CREATE TABLE vanora_test_INDEX (
`code` varchar(20) NOT NULL,
`NAME` varchar(30) CHARACTER SET utf8 DEFAULT NULL,
`Phone` varchar(15) DEFAULT NULL,
PRIMARY KEY(`CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk
删除方式
ALTER TABLE vanora_test DROP PRIMARY KEY;
id是自增长的主键索引,删除时注意:
CREATE TABLE `vanora_test` (
`code` varchar(20) NOT NULL,
`id` int(10) NOT NULL AUTO_INCREMENT,
`NAME` varchar(30) CHARACTER SET utf8 DEFAULT NULL,
`city_code` int(10) DEFAULT NULL,
`Phone` varchar(15) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=135 DEFAULT CHARSET=gbk
alter table vanora_test DROP PRIMARY KEY;(错误)
alter table vanora_test DROP id;(直接删除字段)
删除索引:
alter table vanora_test modify id int(10) NOT NULL;(去掉自增)
alter table vanora_test drop primary key;
4.组合索引
ALTER TABLE vanora_test ADD INDEX CODE_ID_INDEX (CODE,id);
使用组合索引遵循最左前缀集合,比方索引是key index(a,b,c),这种索引支持a|a,b|a,b,c 三种组合查找,其他情况,组合索引没有用处
复合索引的结构与电话簿类似,人名由姓和名构成,电话簿首先按姓氏对进行排序,然后按名字对有相同姓氏的人进行排序。如果您知 道姓,电话簿将非常有用;如果您知道姓和名,电话簿则更为有用,但如果您只知道名不姓,电话簿将没有用处。
5.全文索引
主要用来查找文本中的关键字,而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同,它更像是一个搜索引擎,而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用,而不是一般的where语句加like。它可以在create table,alter table ,create index使用,不过目前只有char、varchar,text 列上可以创建全文索引。值得一提的是,在数据量较大时候,现将数据放入一个没有全局索引的表中,然后再用CREATE index创建fulltext索引,要比先为一张表建立fulltext然后再将数据写入的速度快很多。
创建
CREATE TABLE `full_table` (
`id`int(11) NOT NULL AUTO_INCREMENT ,
`title`char(255) CHARACTER NOT NULL ,
`content` text CHARACTER NULL ,
`time`int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
FULLTEXT (content)
);
ALTER TABLE full_table ADD FULLTEXT index_content(content);
缺点
1.提高了速度,但是降低了更细表的速度,更新表时,不仅要保存数据,还要保存一下索引文件
2.建立索引会占用磁盘空间
注意
1.索引列不包含有null的值
有null值的列不会被包含在索引中,组合索引只要有一列是null,组合索引无效
2.使用短索引
提高查询速度,节省磁盘空间
3.索引列排序
查询只使用一个索引,where使用了索引,order by中不再使用索引
4.like
like 'aa%' 使用了索引
like '%aa' 没有使用索引
5.在索引列进行运算,索引失效
SELECT * FROM order WHERE DATE_FORMAT(Create_Time,'%Y%m%d')>='20190301' LIMIT 10;
6.
可以使用索引的:,>=,BETWEEN,IN
可以使用索引的:<>,not in ,!=