八、索引的设计和使用（MySQL读书笔记）

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 不能确定在两个值之间大约有多少行；只能使用整个关键字来搜索一行