字节流的博客

MySQL 索引

1. 创建和删除索引

  • 创建索引: CREATE INDEX index_name ON table_name(field); 或者 CREATE INDEX index_name ON table_name(field(lenth); (前length个字节的前缀索引);
  • 删除索引: DROP INDEX index_name ON table_name;

2. 设计原则

  • 搜索的索引列,不一定是要选择的列: 即 where 或连接子句中的列而不是 select 后面出现的列;
  • 使用唯一索引: 在出现不同值较多的列添加索引;
  • 使用短索引: 如对字符串列进行前缀索引,节省索引空间,少数据量 IO 快,提高查询速度;
  • 利用最左前缀: 在创建n列索引时,MySQL 实际上创建了 n 个列的索引. 多列索引可起 n 列索引的作用,因为可利用索引中最左边的列集进行匹配.这样的列集被称为最左前缀
  • 不要过度索引: 索引太多影响数据插入性能,因为每次修改表中数据,索引都要更新,或者重构;
  • 对于 InnoDB 存储引擎的表,尽量自己定义主键,如果没有定义主键,会按一定的顺序保存,如果有主键,按主键顺序保存;另外,InnoDB的表的索引都会存储主键的值,索引 InnoDB 的表的主键尽量选择较短的数据类型。

3. BTREE 索引和 HASH 索引

MEMORY 存储引擎的表可以选择使用 BTREE 和 HASH 索引。

(1) HASH 索引适用条件

  • 只使用 = 或 <=> 比较;
  • 优化器不能使用 HASH 索引加速 order by 操作;
  • MySQL 不能确定两个值之间大于有多少行,如果一个 MyISAM 表改成 HASH 索引的 MEMORY 表,会影响查询效率;
  • 只能使用正个关键字来搜索一行;

(2) BTREE 索引适用条件

  • 、<、>=、<=、BETWEEN、!=、<>或者 like ‘pattern’(patter不以通配符开始)操作时,可以使用相关列的索引。

Thanks! 😊