1、分类
MySQL索引分为普通索引、唯一索引、主键索引、组合索引、全文索引。索引不会包含有null值的列,索引项可以为null(唯一索引、组合索引等),但是只要列中有null值就不会被包含在索引中。
(1)普通索引:create index index_name on table(column);
或者创建表时指定,create table(..., index index_name column);
(2)唯一索引:类似普通索引,索引列的值必须唯一(可以为空,这点和主键索引不同)
create unique index index_name on table(column);或者创建表时指定unique index_name column
(3)主键索引:特殊的唯一索引,不允许为空,只能有一个,一般是在建表时指定primary key(column)
(4)组合索引:在多个字段上创建索引,遵循最左前缀原则。alter table t add index index_name(a,b,c);
(5)全文索引:主要用来查找文本中的关键字,不是直接与索引中的值相比较,像是一个搜索引擎,配合match against使用,现在只有char,varchar,text上可以创建全文索引。在数据量较大时,先将数据放在一张没有全文索引的表里,然后再利用create index创建全文索引,比先生成全文索引再插入数据快很多。
2.何时使用索引 (尽量扩展索引、不要新建索引)
MySQL每次查询只使用一个索引。与其说是“数据库查询只能用到一个索引”,倒不如说,和全表扫描比起来,去分析两个索引B+树更加耗费时间。所以where A=a and B=b这种查询使用(A,B)的组合索引最佳,B+树根据(A,B)来排序。
1)主键、唯一(unique)字段
2)使用 where 语句
3)使用聚集函数 如 count()、MAX() 等
4) order by 或 group by 字段
5) 和其他表做连接查询的字段
6)频繁查询的字段
7)不以通配符%开始的模糊查询,如 where name like ‘王%’;如果‘%A%’会造成索引失效
3.何时不使用索引
1)表记录太少
2)重复值比较多的字段,区分度低的字段/数据值较少的字段,如性别(值为0或1)
3)经常插入、修改、删除的表要减少索引
4)text、image等类型不应该建立索引,这些列的数据量大(假如text前10个字符唯一,也可以对text前10个字符建立索引);
5)类型不应该建立索引,这些列的数据量大(假如text前10个字符唯一,也可以对text前10个字符建立索引);
4.索引失效的情况
1)where语句对字段进行null判断 (会导致全表扫描)
2)where 语句中使用 !=或<>操作符
3)where语句使用or来连接条件
4) 使用in和not in 能用between就不用in
5)通配符开头 ‘%A%’
6)where语句中 ‘=’的左边 进行函数、算术运算符或其他表达式 运算。
7)字符串不加单引号 ''(会进行类型转换)