MySQL索引

索引分类

按功能分：普通索引，唯一索引，主键索引，全文索引

按物理实现分：聚簇索引，非聚簇索引

按字段个数分：单列索引，联合索引

索引的创建

隐式创建

主键，外键，唯一约束，这些字段会在表创建的时候隐式的创建索引

显示创建

创建表的时候创建索引

1，创建普通索引

INDEX inx_id(id)  inx_id：索引名称   id：添加索引的字段 如果不写索引名称默认字段名为索引名

2，创建唯一性索引

第一种方式创建数据库的时候给字段添加唯一性约束会自动创建唯一性索引

第二种 UNIQUE INDEX inx_id(id)  

3，创建联合索引

INDEX inx_mul(id,name,age)   联合索引只有最左侧索引可以单独使用，其余的不可以单独使用

表创建完成后添加索引

1，创建普通索引

ALTER TABLE book ADD INDEX inx_id(id)

2，创建唯一性索引

ALTER TABLE book ADD UNIQUE INDEX inx_name(`name`)

3，创建联合索引

ALTER TABLE book ADD INDEX inx_mul(info,author)

索引的删除

ALTER TABLE book DROP INDEX inx_id

添加AUTO_INCREMENT约束的唯一索引和主键索引不能被删除

索引创建原则

1，字段的数值有唯一性限制的创建唯一性索引 具有唯一特性的字段即使时联合字段也必须创建成唯一性索引

2，频繁作为where查询条件的字段

3，经常order by和group by的字段

order by 和group by同时使用的时候 先执行group by在执行order by 创建联合索引的时候一定要gruop by 字段在前

4，update和delete的条件字段 

5，需要distinct的字段

6，多表连接时首先对where字段创建索引其次对连接字段创建索引

7，创建索引的数据大小在满足使用的情况下应该选择占用空间小的数据类型

比如，tinyint,mediumint,int,bigint同时满足条件的时候选用tinyint类型

8，字符串前缀创建索引，如果字符串很长可以截取部分字符串来创建索引

alter table book add index(address(20)) 一般来说截取前20个区分度可以达到90%以上

9，区分度高的字段适合创建索引 select count(distinct 字段名)/count(*) from 表名 区分度计算公式

越接近1区分度越高

10，在创建联合索引的时候使用频繁的字段放在联合索引的最左侧 

where a=100 and b<10 在这种情况下创建的联合索引a在最左侧

11，多个字段都要创建索引的情况下 联合索引优于单值索引

索引数目

一般来说一张表创建的索引数目不超过6个

1，索引越多占据的自盘空间越大

2，索引会降低insert,delete,update语句的性能，更新表中数据的时候索引也会进行更新和维护

3，优化器在进行优化的时候会对所有可能用到的索引进行评估，如果多个索引可以用于查询会增加优化器的优化时间

索引失效

1，在sql语句中包含以下操作会导致索引失效：索引字段有计算操作，使用函数，类型转换(索引字段为字符串类型而查找条件为整型)

2，范围条件右边的索引字段失效

CREATE INDEX idx_age_classId_name ON student(age,classId,NAME);
WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ; 

上述sql中classId使用了范围条件所以classId右边的索引就失效了(主要和创建索引的顺序有关和where中的顺序无关)

3，索引字段使用不等于(!=)会造成索引失效

4，not null 和 not like也会造成索引失效

5，模糊查询以%开始会造成索引失效

6，or前后存在非索引字段会造成索引失效