mysql 主键失效_MySQL索引（索引失效）

索引

索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

myisam存储引擎，数据文件、索引文件、表结构文件分开存储

innodb存储引擎，数据和索引存储在一个文件中

B+tree索引

hash索引

hash索引(只有memory存储引擎支持)，查找一条记录的速度非常快

B+tree索引(Innodb和MyISAM支持)，更适合排序等操作

适合作索引的数据类型

(1)较小的数据类型，需要更少的空间

(2)简单的数据类型，整型比字符串开销小

(3)避免使用null，null会导致索引失效

主键索引

唯一索引

普通索引

全文索引

组合索引

显示当前表的所有索引信息

show index from tableName\G

1.普通索引

create index idx_name on tableName(colName(length))

如果是char或者varchar length可以小于实际的长度，如果是blob和text 必须指定length

alter table tableName add index idx_name(colName)

drop index idx_name on tableName

一张表可以有多个普通索引

2. 唯一索引

列的值必须唯一，允许用null值

如果是组合索引，列的值的组合必须唯一

create union index idx_name on tableName(colName)

alter table table_name add unique idx_name(colName)

一张表可以有多个唯一索引

3.主键索引

主键的数据类型最好为整型

主键索引只能有一个

主键列的值必须唯一并且不能是null

自动增长的列一定是主键

主键列不一定是自动增长的

alter table tableName add primary key(col_name)

4.全文索引

innodb不支持

alter table tableName add fulltext idx_name(col_name)

索引失效

1.最佳左前缀原则

组合索引，不按索引定义时制定的顺序 的最左列开始

2.like ‘%Z’

like模糊查询时， 以%开头，导致索引失效

3.范围之后全失效(>

如果是主键或者索引列是整数，索引不会失效

4.遇到null值，索引失效

5.索引列上的显式或者隐式运算，导致索引失效

6.order by

由于查询只使用一个索引，因此，如果where语句使用了索引，order by语句不会使用

7.不在一个方向的order by导致索引失效

全asc 或 全desc

8.组合索引，中间跳过了某一列，后面的列全失效

9.is null is not null != <> 都会导致索引失效，如果这些用在主键列上，仍会使用索引

10.字符串类型不加单引号，导致索引失效

不加单引号，会有隐式的类型转化(索引列上的计算会导致索引失效)

11.用or连接导致索引失效

or条件有未建立索引的列导致索引失效

应当避免select * ，使用覆盖索引

count(1)或者count(col)代替count(*)

列的可取值较少时，不建议使用索引(性别)

使用char代替varchar