1,概述
MyISAM
和
InnoDB
存储引擎的表默认创建的都是
BTREE
索引。
MySQL
目前还不支持函
数索引,但是支持前缀索引,即对索引字段的前
N
个字符创建索引。
例如,要为
city
表创建了
10
个字节的前缀索引,语法是:
mysql> create index cityname on city (city(10));
Query OK, 600 rows affected (0.26 sec)
Records: 600 Duplicates: 0 Warnings: 0
2,设计索引的原则
索引的设计可以遵循一些已有的原则,创建索引的时候请尽量考虑符合这些原则,便于
提升索引的使用效率,更高效地使用索引。
(1)
搜索的索引列,不一定是所要选择的列。换句话说,最适合索引的列是出现在
WHERE
子句中的列,或连接子句中指定的列,而不是出现在
SELECT
关键字后的选择列表中的列。
(2)
使用唯一索引。考虑某列中值的分布。索引的列的基数越大,索引的效果越好。例
如,存放出生日期的列具有不同值,很容易区分各行。而用来记录性别的列,只含有
“ M”
和
“F”
,则对此列进行索引没有多大用处,因为不管搜索哪个值,都会得出大约一半的行。
(3)
使用短索引。如果对字符串列进行索引,应该指定一个前缀长度,只要有可能就应
该这样做。例如,如果有一个
CHAR(200)
列,如果在前
10
个或
20
个字符内,多数值是惟一
的,那么就不要对整个列进行索引。对前
10
个或
20
个字符进行索引能够节省大量索引空间,
也可能会使查询更快。较小的索引涉及的磁盘
IO
较少,较短的值比较起来更快。更为重要
的是,对于较短的键值,索引高速缓存中的块能容纳更多的键值,因此,
MySQL
也可以在
内存中容纳更多的值。这样就增加了找到行而不用读取索引中较多块的可能性。
(4)
利用最左前缀。在创建一个
n
列的索引时,实际是创建了
MySQL
可利用的
n
个索引。
多列索引可起几个索引的作用,因为可利用索引中最左边的列集来匹配行。这样的列集称为
最左前缀。
(5)
不要过度索引。不要以为索引“越多越好”,什么东西都用索引是错误的。每个额
外的索引都要占用额外的磁盘空间,并降低写操作的性能。在修改表的内容时,索引必须进
行更新,有时可能需要重构,因此,索引越多,所花的时间越长。如果有一个索引很少利用
或从不使用,那么会不必要地减缓表的修改速度。此外,
MySQL
在生成一个执行计划时,
要考虑各个索引,这也要花费时间。创建多余的索引给查询优化带来了更多的工作。索引太
多,也可能会使
MySQL
选择不到所要使用的最好索引。只保持所需的索引有利于查询优化。
3,BTREE 索引与 HASH 索引
MEMORY
存储引擎的表可以选择使用
BTREE
索引或者
HASH
索引,两种不同类型的索引
各有其不同的适用范围。
HASH
索引有一些重要的特征需要在使用的时候特别注意,如下所
示。
(1)
只用于使用
=
或
<=>
操作符的等式比较。
(2)优化器不能使用
HASH
索引来加速
ORDER BY
操作。
(3)MySQL
不能确定在两个值之间大约有多少行。如果将一个
MyISAM
表改为
HASH
索
引的
MEMORY
表,会影响一些查询的执行效率。
(4)
只能使用整个关键字来搜索一行。
而对于
BTREE
索引,当使用
>
、
<
、
>=
、
<=
、
BETWEEN
、
!=
或者
<>
,或者
LIKE 'pattern'
(其
中
'pattern'
不以通配符开始)操作符时,都可以使用相关列上的索引。
下列范围查询适用于
BTREE
索引和
HASH
索引:
SELECT * FROM t1 WHERE key_col = 1 OR key_col IN (15,18,20);
下列范围查询只适用于
BTREE
索引:
SELECT * FROM t1 WHERE key_col > 1 AND key_col < 10;
SELECT * FROM t1 WHERE key_col LIKE 'ab%' OR key_col BETWEEN 'lisa' AND 'simon';
例如,创建一个和
city
表完全相同的
MEMORY
存储引擎的表
city_memory
:
mysql> CREATE TABLE city_memory (
-> city_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
-> city VARCHAR(50) NOT NULL,
-> country_id SMALLINT UNSIGNED NOT NULL,
-> last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,
-> PRIMARY KEY (city_id),
-> KEY idx_fk_country_id (country_id)
-> )ENGINE=Memory DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)
mysql> insert into city_memory select * from city;
Query OK, 600 rows affected (0.00 sec)
Records: 600 Duplicates: 0 Warnings: 0
当对索引字段进行范围查询的时候,只有
BTREE
索引可以通过索引访问:
mysql> explain SELECT * FROM city WHERE country_id > 1 and country_id < 10 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: city
type: range
possible_keys: idx_fk_country_id
key: idx_fk_country_id