所有分类
  • 所有分类
  • 未分类

MySQL索引的类型

简介

本文介绍MySQL的索引的类别。

按索引列的类型分类

普通索引、主键索引、唯一索引、外键索引、全文索引、联合索引。

索引类型说明
普通索引最基本的索引,它没有任何限制。 命令格式(help ‘create table’): {INDEX|KEY} [index_name] [index_type] (key_part,…)      [index_option] … 删除索引命令格式: ALTER TABLE TABLE_NAME DROP {INDEX|KEY} index_name 或者 DROP INDEX index_name ON tbl_name
    [algorithm_option | lock_option] …
唯一索引与普通索引类似。 不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。 如果向唯一索引的列插入同样的数据会报错。 命令格式(help ‘create table’): [CONSTRAINT [symbol]] UNIQUE       [INDEX|KEY]
      [index_name] [index_type] (key_part,…)
      [index_option] …
主键索引特殊的唯一索引,一个表只能有一个主键,不允许有空值。 命令格式(help ‘create table’): [CONSTRAINT [symbol]] PRIMARY KEY
      [index_type] (key_part,…)
      [index_option] …
外键索引1. 两表必须是InnoDB表,MyISAM不支持。
2. 外键列必须建立了索引,MySQL 4.1.2以后建立外键时会自动创建索引,较早的版本需显式建立。
3. 外键关系的两个表的列必须是数据类型相似,也就是可以相互转换类型的列,比如int和tinyint可以,而int和char则不可以。 命令格式(help ‘create table’): [CONSTRAINT [symbol]] FOREIGN KEY
      [index_name] (col_name,…)
      reference_definition
全文索引主要用来查找文本中的关键字,而不是直接与索引中的值相比较。 命令格式(help ‘create table’) {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (key_part,…)
      [index_option] …
联合索引多个字段上创建的索引,使用相关字段作为条件查询数据时遵循最左前缀集合。

按索引的数据结构分类

B-Tree索引,哈希索引,空间数据索引(R-Tree),全文索引,其他索引

索引类型优点缺点
B-Tree索引 (B+树)可以全键值、键值范围、最左前缀查找组合索引必须满足最左前缀。 如果查询中有某个列的范围查询,则其右边所有列都无法使用索引优化査找。
哈希索引查找高效。(只支持等值操作:=, IN(), <=>)不支持部分索引列查询。 无法排序,不支持范围查询。 哈希冲突时索引维护的代价很高
空间数据索引(R-Tree)可以用任意维度组合查询MySQL支持不完善。 必须使用GIS相关函数。
全文索引
其他索引

1

评论3

请先

  1. 联合索引那边后半句是不是写错了,联合索引应该不管唯一性的吧?只有联合唯一索引才会阻止索引组合相同的值插入
    goldencowcow 2024-03-04 1
    • 是有问题,已修复
      自学精灵 2024-03-04 1
  2. 1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如: select id from t where num is null -- 可以在num上设置默认值0,确保表中num列没有null值,然后这样查询: select id from t where num= 3.应尽量避免在 where 子句中使用!=或操作符,否则引擎将放弃使用索引而进行全表扫描。 4.应尽量避免在 where 子句中使用or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如: select id from t where num=10 or num=20 -- 可以这样查询: select id from t where num=10 union all select id from t where num=20 5.in 和 not in 也要慎用,否则会导致全表扫描,如: select id from t where num in(1,2,3) -- 对于连续的数值,能用 between 就不要用 in 了: select id from t where num between 1 and 3 6.下面的查询也将导致全表扫描:select id from t where name like ‘%李%’若要提高效率,可以考虑全文检索。
    华山栈道008 2023-11-30 3
显示验证码
没有账号?注册  忘记密码?

社交账号快速登录