1、所有MySQL列类型都可以被索引,对相关列使用索引是提高 SELECT 操作性能的最佳途径;么中存储引擎对每个表至少支持 16 个索引,总索引长度至少为 256 字节;
2、MyISAM 和 InnoDB 存储引擎的表默认创建的都是 BTREE 索引,MySQL目前不支持函数索引(8.0待验证),但是支持前缀索引;还支持全文本(FULLTEXT)索引
-- 索引在创建表时可以同时创建,也可以随时增加新的索引
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING index_type] ON table_name(index_col_name,...)
-- index_col_name:col_name[(length)][ASC|DESC]
-- 为 city 表创建10个字节的前缀索引
CREATE INDEX city_name ON city (city(10));
-- 删除索引
DROP INDEX index_name on table_name
3、索引设计原则
3.1、搜索的索引列,不一定是说要选择的列(最适合索引的列是出现在 WHERE 或连接子句中的列,而不是 SELECT 选择的列)
3.2、使用唯一索引(索引的列的基数越大,索引的效果越好 -- 索引列的值的唯一性越大越好)
3.3、使用短索引
3.4、利用最左索引(在创建一个 n 列的索引时,实际是创建了MySQL可利用的 n 个索引。多列索引可起几个索引的作用,因为可利用索引中最左边的列集来匹配行)
3.5、不要过度索引
3.6、对于 InnoDB 存储引擎的表,记录默认会按照一定的顺序保存,如果有明确定义的主键,则按照主键顺序保存(如果没有主键,但是有唯一索引,那么就按照唯一索引的顺序保存;如果同时没有主键和唯一索引。那么表中会自动生成一个内布列。按照主键或内部列进行访问是最快的,所以 InnoDB 尽量指定主键。尽量选择最常作为访问条件的列作为主键。InnoDB 表的普通索引都会保存主键的简直,因此主键尽量选择较短的数据类型)
4、BTREE & HASH
MEMORY 存储引擎的表可以选择 BTREE | HASH 索引。
4.1、HASH:只用于使用 = 或 <=> 操作符的等式比较;优化器不能用来加速 ORDER BY 操作;MySQL 不能确定在两个值之间大约有多少行;只能使用整个关键字来搜索一行