文章目录
创建高性能的索引
一、创建原则
- 最左前缀匹配原则
- 较频繁作为查询条件的字段
- 更新频繁字段不合适
- 不能有效区分数据不合适
- 尽量扩展索引,不要新建索引
- 定义外键的数据列一定要建立索引
- 查询很少涉及的列、重复值较多的列不要建立索引
二、索引(存储引擎层)基础
索引的类型
1. B-Tree 索引
MyISAM使用前缀压缩技术使得索引更小---->通过数据的物理位置引用被索引的行
InnoDB(B+Tree)则按照原数据格式进行存储---->根据主键引用被索引的行
B-Tree对索引列是顺序组织存储的
可以使用B-Tree索引的查询类型:
全值匹配、匹配最左前缀、匹配列前缀、匹配范围值、精确匹配某一列并范
围匹配另外一列、只访问索引的查询
限制:
如果不是按照索引的最左列开始查找,则无法使用索引
不能跳过索引中的列
如果查询中有某个列的范围查询,则其右边所有列都无法使用索引优化查找
CREATE TABLE test (
id INT NOT NULL,
last_name CHAR(30) NOT NULL,
first_name CHAR(30) NOT NULL,
PRIMARY KEY (id),
INDEX name (last_name,first_name)
);
name索引用于以下查询中的查找
SELECT * FROM test WHERE last_name='Jones';
SELECT * FROM test
WHERE last_name='Jones' AND first_name='John';
SELECT * FROM test
WHERE last_name='Jones'
AND (first_name='John' OR first_name='Jon');
SELECT * FROM test
WHERE last_name='Jones'
AND first_name >='M' AND first_name < 'N';
在以下查询中,name索引 不用于查找
SELECT * FROM test WHERE first_name='John';
SELECT * FROM test
WHERE last_name='Jones' OR first_name='John';
2.哈希索引(Memory引擎显式支持)
- 基于哈希表实现,只有精确匹配索引所有列的查询才有效
- 对每一行数据,引擎对所有的索引列计算一个哈希码,哈希码存储在索引中,同时在哈希表中保存指向每个数据行的指针
- 限制
哈希索引只包含哈希值和行指针,而不存储字段值
无法用于排序
不支持部分索引列匹配查找
只支持等值比较查询,不支持任何范围查询 - 创建自定义哈希索引–>在查询的WHERE子句中手动指定哈希函数
3.全文索引
查找的是文本中的关键词,更类似于搜索引擎做的事,不是简单地WHERE匹配
索引的优点
大大减少了服务器需要扫描的数据量
帮助服务器避免排序和临时表
索引可以将随机I/O变为顺序I/O
高性能的索引策略
独立的列
前缀索引和索引选择性
前缀索引是一种能使索引更小更快的有效办法
MySQL无法使用前缀索引做ORDER BY和GROUP BY ,也无法使用前缀索引做覆盖扫描
多列索引
选择合适的索引列顺序
索引可以按照升序或者降序进行扫描
将选择性最高的列放到索引最前列
聚簇索引
一种数据存储方式
InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行
InnoBD将通过主键聚集数据,如果没有主键,会选择一个唯一的非空索引代替
聚集数据优点:
- 把相关数据保存在一起
- 数据访问更快
- 使用覆盖索引扫描的查询可以直接使用页节点中的主键值
覆盖索引
一个索引包含(覆盖)所需要查询的字段的值