高性能MySQL笔记(第五章 创建高性能的索引) 01

p177~p228. 分2次, p177~p197, p198~p228. 此为第1次.

索引类型

BTree索引

支持特性

• 全值匹配
• 匹配最左前缀. 匹配一个多列索引的第一列.
• 匹配列前缀. 匹配某一列的开头部分.
• 匹配范围值.
• 精确匹配某一列并范围匹配另外一列.
• 只访问索引的查询, 只查询索引不查询数据行, 也叫"覆盖索引"

限制

• 不是按索引的最左列开始查找, 无法使用索引.
• 不能跳过索引中的列. 如多列索引包含列1,列2,列3. 查询语句为列1='a' and 列3='b', 则只能使用列1的索引.
• 如果查询中有某个列的范围查询, 则右边的查询无法使用索引.

哈希索引

将索引列(一般是一个很长的列, 如url)进行计算, 得到一个哈希码, 哈希码是一个较小的值. 用哈希码来查询.
Memory引擎
只有Memory引擎直接支持哈希索引. 只支持等值查询(=,IN(),<=>).

其他引擎创建自定义哈希索引
创建额外的计算列, 来存储哈希值. 查询时通过哈希值列进行查询.

select id from url where url="http://www.mysql.com";
select id from url where url="http://www.mysql.com" and url_crc=CRC32("http://www.mysql.com");//添加了哈希值列后的查询

索引优点

使用索引的3个优点

1. 大大减少了服务器需要扫码的数据量
2. 可以帮助服务器避免排序和临时表
3. 可以将随机I/O变为顺序I/O

索引评价的3星系统

• 一星: 将相关的记录放到一起.
• 二星: 索引中的数据顺序与查找中的排列顺序一致.
• 三星: 索引中的列包含了查询需要的全部列.

高性能的索引策略

独立的列

查询的列, 不能是表达式, 或者是函数的参数.

select actor_id from actor where actor_id + 1=5;//索引失效
select ... where TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <=10;//索引失效

前缀索引和索引选择性

如果索引一个很长的字符列, 则所以变得很大且慢. 可以选择索引部分前缀字符, 可以节约索引空间, 提高索引效率.
但是如何选择适合的索引长度, 即前N位可以大致代表整体的情况. 如何选择N, 就要看索引选择性.

select count(DISTINCT city)/count(*) from city_demo;//计算列总体的选择性.
select count(DISTINCT LEFT(city,3))/count(*) as sel3,
count(DISTINCT LEFT(city,4)/count(*) as sel4,
count(DISTINCT LEFT(city,5)/count(*) as sel5,
count(DISTINCT LEFT(city,6)/count(*) as sel6,
count(DISTINCT LEFT(city,7)/count(*) as sel7 from city_demo;//计算N为不同值时, 索引的选择性

当索引选择性与总体选择性相当时, 基本可以判断索引比较适合.

多列索引

一般很多人会把"where条件里面的列都加上索引", 这样最多达到"一星"标准, 性能可能比真正最优的索引差几个数量级.

创建多列索引时, 顺序也很重要, 要把选择性高的列(重复性低的列)放在前面.