深入浅出mysql_索引的设计和使用

十 . 索引的设计和使用


10.1 索引概述


MyISAM 和InnoDB创建的索引默认都是BTREE索引
MEMARY的默认索引是HASH 但是也支持BTREE


创建索引：


语法


CREATE [UNIQUE|FULLTEXT|SPATIAL(空间索引) ] INDEX index_name 
[USING index_type] 
ON tablename(index_col_name,.....)


解释 index_col_name: col_name[(length)] [ASC|DESC]
也可以适应 ALTER  TABLE 语法来修改，和create 语法类似


范例


例如，要为 city 表创建了 10 个字节的前缀索引，语法是：
mysql> create index cityname on city (city(10));
Query OK, 600 rows affected (0.26 sec)
Records: 600 Duplicates: 0 Warnings: 0


删除索引


语法


DROP INDEX index_name ON table_name


范例


想要删除 city 表上的索引 cityname，可以操作如下：
mysql> drop index cityname on city;
Query OK, 600 rows affected (0.23 sec)
Records: 600 Duplicates: 0 Warnings: 0


索引的设计原则


搜索的索引列


换句话说，最适合索引的列是出现在 WHERE子句中的列，或连接子句中指定的列，而不是出现在 SELECT 关键字后的选择列表中的列。


使用唯一索引


考虑某列中值的分布，索引列的基数越大 效果越好。
例如，存放出生日期的列具有不同的值，很容易区分各行，而用来记录性别的列只有两个值，则索引没有什么用处，不管搜索那个值都有大约一半的结果


使用短索引：


如对字符串列进行索引时，应该指定一个字符串长度，只要有可能就应该这样做。
例如， 有个char(200)的列，如果在前10个或者前20个字符内，多数值是唯一的，那么就不要对整个列记性索引，对前10个或者前20个字符进行索引能节省大量的索引空间，也可能会是查询更快，较小的索引涉及的磁盘I/O较小，较短的值比较起来也更快。更重要的是较短的键值，索引高速缓存块中能容纳更多的键值，因此，MySQL也可以在内存中容纳更多的值。这样就增加了找到行而不用读取索引中较多块的可能性。


利用最左前缀：


在创建一个n列的索引时，实际是创建了MySQL可利用的n个索引，多列索引可以起几个索引的作用，因为可利用索引中最左边的列进行匹配，这样的列集称为最左前缀


不要过度索引：


占用过多的存储空间；降低写的操作性能；MySQL生成执行计划时要考虑过多的索引，要花费时间，过多的索引也会是MySQL选择不到最好的索引。


对于InnoDB表 ，记录默认会按照一定的顺序保存。如果明确主键，则按照主键，没有主键但是有唯一索引，则按照唯一索引的顺序保存；如果既没有主键也没有唯一索引那么表会自动生成一个内部列。按照主键或者内部列的速度最快，所以要尽可能的指定主键：
选择最长访问的列作为主键，主键选尽可能短的数据类型。


HASH和BTREE


HASH一些重要的特征


1 只用于使用=或<=>操作符的等式比较。
2优化器不能使用 HASH 索引来加速 ORDER BY 操作。
3MySQL 不能确定在两个值之间大约有多少行。如果将一个 MyISAM 表改为 HASH 索引的 MEMORY 表，会影响一些查询的执行效率。
4只能使用整个关键字来搜索一行。


BTREE：
使用 >、 < 、 >= 、<= 、 BETWEEN、 ！= 或者<> 或者LIKE 'abc' (abc 不以通配符开始) 都可以使用相关的列上的索引；