MYSQL索引

MySQL索引类型

MySql常见索引类型有:主键索引、唯一索引、普通索引、全文索引、组合索引

索引创建

PRIMARY KEY(主键索引)

​ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) ​

UNIQUE(唯一索引)

​ALTER TABLE `table_name` ADD UNIQUE (`column`)​

INDEX(普通索引)

​ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) ​

FULLTEXT(全文索引)

​ALTER TABLE `table_name` ADD FULLTEXT ( `column` )​

组合索引(包含多列的索引,与单行索引相对

​ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )​

说明:关于主键索引,为表定义主键将自动创建主键索引,而且在数据库管理工具中“设计表”中的“索引”项也无法看到创建好的主键索引。

查看索引

​SHOW INDEX FROM table_name​

删除索引

​DROP INDEX index_name ON tableName​

MySQL各种索引区别

索引

特点

唯一索引(UNIQUE)

与"普通索引"类似,不同的就是:索引列的值必须唯一,但允许有空值

主键索引(PRIMARY)

它 是一种特殊的唯一索引,不允许有空值。

普通索引(INDEX)

最基本的索引,没有任何限制

全文索引(FULLTEXT )

仅可用于 MyISAM 表, 用于在一篇文章中,检索文本信息的, 针对较大的数据,生成全文索引很耗时耗空间

组合索引

为了更多的提高mysql效率可建立组合索引,遵循”最左前缀“原则

触发索引

MySQL只对 <,<=,=,>,>=,BETWEEN,IN,以及某些时候的 LIKE 才会触发索引。

索引失效

  1. 全值匹配,索引最高效
  2. 最佳左前缀法则:复合索引,查询从索引最左开始,且不能跳过
  3. 复合索引,带头大哥不能少,索引的第一个字段不能丢失
  4. 复合索引,中间兄弟不能断;
  5. 不在索引列上做任何操作(计算,函数,自动或手动类型转换),会导致索引失效
  6. 存储引擎不能使用索引中范围条件右边的列:查询范围(比较),右边的列索引失效
  7. 尽量使用覆盖索引(只访问索引的查询(查询列和索引列一直)),减少SELECT *;
  8. 使用不等于(!= 或 <>)的时候无法使用索引
  9. is null 或is not null 无法使用索引
  10. like以通配符开头('%abb')导致索引失效,可以放右边使用
  11. 覆盖索引可以解决%%:查的字段和索引字段一致
  12. 字符串不加单引号,索引失效(mysql自动做类型转换)
  13. 少用or,导致索引失效

索引的不足

  1. 虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一索引文件。
  2. 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件会得膨胀很快。