10 MySQL索引选择与使用
索引概述
每种存储引擎对每个表至少支持16个索引,总索引长度至少256字节.
MyISAM和InnoDB的表默认创建BTREE索引.MEMORY引擎默认使用HASH索引,但也支持BTREE
MySQL目前不支持函数索引,但支持前缀索引.
MyISAM支持的前缀索引最大长度为1000字节;InnoDB支持的前缀索引最大程度是767字节.
CREATE TABLE 创建索引时是按照字符数计量的,所以对于多字节的字符集,要考虑字符和字节的关系.
MySQL还支持FULLTEXT(全文本)索引,用于全文搜索.5.0版本目前只有MyISAM存储引擎支持FULLTEXT索引,且只限于CHAR,VARCHAR,TEXT类型的数据列.
索引总是对整个行进行的,不支持局部(前缀)索引.
创建索引的语法:
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[USING index_type]
ON tbl_name (index_col_name,...) ;
也可以使用ALTER TABLE 语法增加索引.
ALTER TABLE tbl_name ADD [ UNIQUE | FULLTEXT | SPATIAL ] INDEX idx_name index_col_name ;
index_col_name :
col_name[(length)] [ASC|DESC]
删除索引的语法
DROP INDEX index_name ON tbl_name ;
索引设计原则:
1).搜索的索引列:
是指出现在WHERE字句中的列,或连接子句中指定的列;而不是SELECT关键字后的列.
2).使用唯一索引:
索引的列的基数越大,索引效果越好.比如存放生日的列具有不同值,可以区分出各行;而存放性别的列,只有'F'或'M' , 搜索出来的基本五五开,索引的意义就不大.
3).使用短索引:
如果对字符串进行索引,应该指定一个前缀长度.例如有个CHAR(200)的列,如果在前10-20个字符内,多数值是唯一的,那就不要对整个列进行索引.这样磁盘I/O操作更少,缓存中的块能容纳更多的键值对.
4).利用左前缀: -- todo 这tm解释,作者你自己能看懂嘛?
在创建一个n列的索引时,实际是创建了MySQL可利用的n个索引.多列索引可起到多个索引的作用,因为可利用索引中最左边的列集来匹配行.这样的列集被称为最左前缀.
5).不要过度使用索引,
过犹不及.多了,或者很多不需要的索引会导致磁盘IO.修改表也会对索引进行更新甚至重构.也会给查询优化带来更多的工作.也会导致MySQL在查询时使用不到最好的那个索引.
6).对于InnoDB存储引擎的表
记录的保存顺序:
有明确主键,按照主键顺序;
没有主键但有唯一索引列,按索引列顺序;
没有唯一索引,MySQL生成内部列,按照内部列排序;
使用主键/内部列进行访问是最快的.所以InnoDB表尽量显示指定主键.
如果同时有几列都是唯一的,都可以作为主键,选择比较短的数据类型.可以减少磁盘IO,以及提高索引效果.
BTREE索引与HASH索引
HASH索引:
只用于使用 = , <=> 这种操作符进行等式比较.
优化器不能使用HASH索引来加速ORDER BY 操作.
MySQL不能确定在两个值之间大概有多少数据行.如果将MyISAM表改为HASH索引的MEMORY表,会影响一些查询的执行效率.
只能使用整个关键字来搜索一行.
BTREE索引:
当是一哦那个>,<,>=,<=,BETWEEN,!=,<> 或者LIKE 'pattern'(其中pattern不以*开头) , 都可以使用相关列上的索引.
范围查询时,HASH索引,其实还是扫描的全盘,而BTREE则是范围搜索.
可以使用 EXPLAIN 语句来观察.如 : explain SELECT * FROM tbl_name WHERE .... ;
