索引

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

 

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