深入浅出MySQL读书笔记1

第10章 索引的设计和使用

索引用于快速找出在某个列中有一特定值的行。如果不用索引，MySQL必须从第1条记录开始然后读完整个表直到找出相关的行。表越大，花费的时间越多。如果用索引，MySQL能快速到达一个位置去搜索数据文件的中间，没有必要看所有的数据。索引是提高SELECT操作性能的最佳途径 。

• 10.1 索引举例

explain select * from city where cityname = 'city1' \G 

id: 1
select_type: SIMPLE
table: city
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5
filtered: 20.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)

create index cityname on city(city(5));

explain select * from city where cityname = 'city1' \G

id: 1
select_type: SIMPLE
table: city
partitions: NULL
type: ref
possible_keys: cityname
key: cityname
key_len: 18
ref: const
rows: 1
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)

drop index cityname on city;

• 10.2    设计索引的原则

1. 最适合索引的列是出现在where子句中的列，或连接子句中指定的列，而不是出现在select关键字后的选择表中的列。
2. 使用唯一索引，索引的列的基数越大，索引的效果越好。
3. 使用短索引，如果对字符串列进行索引，应该指定一个前缀长度，因为前10个或者20个字符多数值可能是唯一的，那么就不要对整个列进行索引，即加快索引，还能容纳更多的键值。
4. 利用最左前缀。
5. 不要过度使用索引。
6. 对于InnoDB存储引擎的表，记录按照一定的默认顺序
   1. 如果有明确定义的主键，则按照主键顺序保存。
   2. 如果没有主键，但是有唯一索引，那么就是按照唯一索引的顺序保存。
   3. 即没有主键也没有唯一索引，表会自动生成一个内部列，按照这个列的顺序保存。
   4. 按照主键或者内部列的顺序访问是最快的，所以InnoDB表尽量设置主键。
   5. 当表中同时有几个列都是唯一，都可以做主键时，要选择最常使用的列作为主键。
   6. 注意：InnoDB表的普通索引都会保存主键的键值，所以主键要尽量尽可能选择较短的数据类型，可以有效地减少索引的磁盘占用，提高索引的缓存效果。

提示：BREE索引使用>、<、>=、<=、BETWEEN、!=、<>、或者LIKE'pattern'(其中'pattern'不以通配符开始)，都可以使用相关列上的索引。