一. 概述
所有 MySQL 列类型都可以被索引, 是提高 select 查询性能的最佳方法. 根据存储引擎可以定义每个表的最大索引数和最大索引长度, 每种引擎对每个表至少支持 16 个索引, 总索引长度至少为 256 字节.
myisam 和 innodb 引擎的表默认是 btree 索引, 支持前缀索引, 前缀索引长度跟存储引擎相关, 对于 myisam 引擎 , 长度可达 1000 字节长, 对于 innodb 长度可达 767 字节, 在使用多字节字符集的列指定前缀长度时要考虑.
支持全文索引(fulltext), 只有 myisam 引擎支持, 只限于 char,varchar,text 列. 默认 memory 引擎使用 hash 索引, 也支持 tbree 索引.
1. 例如, 要为 city 表创建了 10 个字节的前缀索引, 语法是:
-- 为 city 表的 cityname 字段创建 10 个字节的前缀索引
CREATE INDEX ixcityname ON city(cityname(10));
-- 索引查看
EXPLAIN SELECT * FROM city WHERE cityname='';
2. 删除索引
DROP INDEX ixcityname ON city;
二 . 设计索引的原则:
1. 索引使用在 where 后的列, 而不是 select 选择的列.
2. 索引列的基数越大, 索引效果越好.
3. 使用短索引, 如果对字符串进行索引, 应该指定一个前缀长度. 如果在前 10 个或 20 个字符内, 多数值是惟一的, 那么就不要对整个列进行索引. 这样能够节省索引空间.
4. 不过度使用索引. 因为占用磁盘空间, 降低写操作性能.
5. innodb 表的普通索引都会保存主键的键值, 所以主键的键值尽可能选择较短的类型.
6. 利用最左前缀, 在创建一个 n 列索引时, 实际是创建了 MySQL 可利用的 n 个索引, 多列索引可起几个索引的作用, 因为可利用索引中最左边的列集来匹配行.
-- 创建 city 表的多列复合索引
CREATE INDEX ix1 ON city(cityname(10),citycode);
-- 走索引的语句
- EXPLAIN SELECT * FROM city WHERE cityname=''AND citycode='';
- EXPLAIN SELECT * FROM city WHERE cityname=''
-- 不走索引
EXPLAIN SELECT * FROM city WHERE citycode=''
三. btree 索引与 hash 索引
memory 引擎可以选择使用 btree 或 hash 索引, 两种不同类型的索引各有其不同的适用范围, hash 索引使用需要注意:
1. 只用于使用 = 或 <=>操作符的等式比较.
2. 优化器不能使用 hash 索引来加速 order by 操作.
3. MySQL 不能确定在两个值之间大约有多少行. 如果将一个 myisam 表改为 hash 索引的 memory 表, 会影响一些查询的执行效率.
4. 只能使用整个关键字来搜索一行.
下面来演示下:
-- 在 city 表上添加 city_memory 表
CREATE TABLE city_memory SELECT * FROM city
-- 添加外键
ALTER TABLE city_memory ADD KEY idx_fk_country_id(country_id) ;
-- 添加主键
ALTER TABLE city_memory ADD PRIMARY KEY(city_id);
-- city 的 btree 走索引
EXPLAIN SELECT * FROM city WHERE country_id> 1 AND country_id <10
-- city_memory 的 hash 不走索引
- EXPLAIN SELECT * FROM city_memory WHERE country_id> 1 AND country_id <10
- EXPLAIN SELECT * FROM city_memory WHERE country_id> 1
- EXPLAIN SELECT * FROM city_memory WHERE country_id < 10
-- city_memory 的 hash 走索引
- EXPLAIN SELECT * FROM city_memory WHERE country_id= 10
- EXPLAIN SELECT * FROM city_memory WHERE country_id IN (10,11);
总结: 大多数 MySQL 索引 (如 primary key, unique index , index, fulltext index) 在 btree 中存储, 只有空间列类型索引使用 rtree, 并且 memory 表支持 hash 索引.
来源: http://www.linuxidc.com/Linux/2018-09/154330.htm