一、索引创建的三个方法:
1、建表时增加索引create table
create table forIndex (
ID INT AUTO_INCREMENT ,
NAME VARCHAR(30) NOT NULL,
AGE TINYINT,
IDCARD VARCHAR(18) UNIQUE,
GENDER TINYINT,
PRIMARY KEY PRIMARY_KEY (ID),
INDEX INDEX_NAME (NAME)
)
2、alter table add index
ALTER TABLE forIndex ADD PRIMARY KEY(ID);新增主键
ALTER TABLE forIndex ADD INDEX INDEX_NAME(NAME);新增普通索引
ALTER TABLE forIndex ADD UNIQUE INDEX UNIQUE_INDEX_IDCARD(IDCARD);新增唯一索引
3、create index
这种方法无法指定主键
CREATE INDEX INDEX_NAME ON forIndex (NAME);新增普通索引
CREATE UNIQUE INDEX UNIQUE_INDEX_IDCARD ON forIndex (IDCARD);增加唯一索引
补充:bigint 从-2^63(-9223372036854775808)~2^63-1(9223372036854775807)的整型数据,8个字节(建表时的length只是用于显示的长度);
int从-2^31(-2,147,483,648)~2^31-1(2,147,483,647)的整型数据,4个字节;
smallint从-2^15(-32,768)~2^15-1(32,767)的整型数据,2个字节;
tinyint从0~255,1个字节;
二、索引分类:
1、普通索引:最基本的索引类型,无唯一性限制创建方式如一
2、唯一索引:基本索引加上值必须唯一的限制创建方式如一
3、主键 :是一种唯一索引用PRIMARY KEY指定方式如一
4、全文索引:可在text或者varchar类型的字段上创建方式如下:
新增一个varchar类型字段description
※ ALTER TABLE forIndex ADD DESCRIPTIONVARCHAR(255) ;此处如果不加(数字)无法成功
※可以在创建表的时候直接创建全文索引但是这样效率不高
※使用ALTER table forIndex ENGINE=myisam(5.6版本以下innodb存储引擎不支持全文索引)
ALTER TABLE forIndex ADD FULLTEXT INDEXfulltext_description(description)
三、索引使用
1、单列索引:单列索引顾名思义就是单个字段做单独索引
例如:Name字段加了索引,直接使用
SELECT *FROM forIndex where Name='n'
2、多列索引:顾名思义多列组成一个索引
ALTER TABLEforIndex ADD INDEX INDEX_MULTI_NAME_FIRSTNAME(FIRSTNAME,NAME);
ALTER TABLEforindex DROP INDEX INDEX_NAME;删除INDEX_NAME索引
select *from forindex where FIRSTNAME='赵' AND`NAME`='C'; 使用索引INDEX_MULTI_NAME_FIRSTNAME
select *from forindex where FIRSTNAME='赵' ANDage=3; 使用索引INDEX_MULTI_NAME_FIRSTNAME
select *from forindex where FIRSTNAME='赵'; 使用索引INDEX_MULTI_NAME_FIRSTNAME
select *from forindex where NAME='C'; 不使用索引INDEX_MULTI_NAME_FIRSTNAME
3、可以考虑使用索引的主要有两种类型的列:在WHERE子句中出现的列,在join子句中出现的列;
四、索引的缺点
虽然索引能提高查询效率,但是会降低更新的效率,并且索引也需要占用磁盘空间