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不以通配符开始)操作时,可以使用相关列的索引。