创建mysql性能索引

简介

索引在mysql叫做“键（key）”是存储引擎用于快速查找记录的一种数据结构，这是索引的基本功能，下面将讨论索引的其他有用的属性。

当表的数据量较小时索引对查询效率的提升并不明显，当数据量越来越大时索引可以将查询效率提高几个数量级，但当数据量超大时索引开始变得无助了，因为数据量大到一定程度后索引结构开始变得十分复杂，管理索引变得较为吃力，反而可能对查询效率起到反作用。

索引基础

怎么理解索引是如何工作的呢？翻开书的目录可以大概了解，索引就是对一堆数据进行区分每个区界进行贴标签，下次来时就可以快速的找到。

如下面的语句：

mysql> select user_name from user where user_id=5

mysql将使用索引找到user_id=5的数据行，然后再找到你想要的列。

索引类型

在mysql中，索引是在存储引擎层实现的，不同的存储引擎工作的方式并不一样，并不是所有存储引擎都支持所有类型索引，就算不同存储引擎都支持一种索引，他们的实现方式很可能会不同。

下面我们先看看mysql支持的索引类型，以及他们的优点和缺点。

B-Tree 索引

B-Tree 索引是最常见的索引，大多数mysql索引都支持这种索引，它是用数据结构来存储数据的。

我们使用“B-Tree”是因为create table和其他语句使用该关键字，但不代表存储引擎都是使用这种数据结构存储索引的，如NDB集群存储引擎内部实际上使用了T-Tree存储索引，即使它的名字是BTree,InnoDB则是使用B+Tree。

存储引擎以不同的方式使用B-Tree ,性能也有所不同，例如MyISAM使用前缀压缩技术使得索引更小，但InnoDB则按照原数据格式进行存储。再如MyISAM索引通过数据的物理位置引用被索引的行，InnoDB则根据主键引用被索引的行。

B-Tree通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。下面图大致反映InnoDB索引是如何工作的。MyISAM结构有所不同但基本思想类似。

从图可以看出来索引叶节点最终指向的是数据，B-Tree提供一种存储的数据结构，左边的指向的是小于key1的值，中间的是大于等于key1小于keyN的值，右边的是大于keyN的值，从而可以证明B-Tree所有的值都是按照顺序进行存储，然后给这些值画界限，想要的时候根据界限快速定位，避免按顺序扫描，所以很适合查找范围数据。

create table people （
last_name vachar(50) not null,
fist_name vachar(50) not null,
dob date not null ,
gender enum('m','f')not null,
key(last_name,fist_name,dob)
）;

可以发现索引对多个值进行排序的依据是create table语句中定义的顺序，先按last_name再按fist_name,最后按dob。

B-Tree索引值适用于全键值、健值范围或键前查找。

全值匹配
指的是和索引中的所有列进行匹配，如查找姓名+出生的人。

匹配最左前缀
如查找所有姓名为Allen的人，即使用索引的第一列。

匹配范围值
如查找姓名在Allen和Barrymore之间的人。

下面是一些关于B-Tree索引的限制：

• 如果不是按照索引最左列开始查找，则无法使用该索引，如上面的例子你无法使用该索引查找名字为bill或者特定生日的人。也就说如果你想组合索引来进行查询一定要涉及最左列开始，不能跳过左边的索引列，如你的查询条件可以是where last_name='xx' and fist_name='xx' 不能是where fist_name='xx' and dob='xx'
• 如果查询中要查询某个列的范围，则该列右边的列将使用不上索引，如where last_name='Smith' and fist_name='J%' and dob='1976-1cha2-23',这是内部设计的问题可能会在更新版本中解决，如果范围查找的范围有限可以使用多个等值条件来代替范围查找。

读到这里可以发现索引顺序是多么重要，所以在使用时可能为了解决这个问题会多弄几个相同列但顺序不同的索引。

哈希索引

哈希索引是基于哈希表实现的，由于存储引擎会对所有的索引列生成一个哈希码，所以只有精确匹配索引所有列索引才会有效。哈希码是一个较小的值，并且不同的键值行计算出来的哈希码也不一样。哈希表把所有哈希码存储在索引中并保存指向数据行的指针。

目前只有Memory引擎显示的支持哈希索引，这也是Memory默认的索引类型。Memory引擎支持非唯一哈希索引，这是数据库世界里比较与众不同的，如果多个列的哈希值相同，索引会以链表的方式存储多个记录到哈希条目中。

create table testhash(
fname varchar(50) not null,
lname varchar(50) not null,
key using hash(fname)
)engine=memory;

select * from testhash;

fname	lanme
Arjen	Lentz
Baron	Schwartz
Peter	Zaitsev
Vadim	Tkacheko

哈希索引的数据结构如下（哈希码是假设的）：

哈希码	值
2568	指向第1行的指针
3256	指向第4行的指针
4567	指向第3行的指针
4589	指向第2行的指针

注意哈希码排列是顺序的，但是指向数据行的指针不是。

哈希索引的限制

• 哈希索引只包含哈希行码和指向行的指针十分紧凑，索引想要得到查询的值必须访问数据行，内存中的还好，磁盘中的效率就没那么高了。
• 哈希索引数据不是按照列值顺序存储的，所以无法用于排序。
• 哈希索引的哈希值是根据所有索引列生产的，所以不支持部分索引列匹配查找。
• 哈希索引查找数据的速度非常快，除非存在哈希码相同需要通过链表的指针遍历数据行，但是已经缩小了范围。
• 如果哈希码有很多组重复的话，索引维护也会带来较高代价，例如删除某个哈希值重复的数据需要删除相应索引，这又要遍历数据行进行确定了。
• 哈希索引只支持等值比较，包括=、in()、<=>。

除了Memory引擎,InnoDB有种特殊的索引“自适应哈希索引”，当InnoDB注意到某个索引值被使用的非常频繁，它会在内存中基于B-Tree之上再建一个哈希索引，这样能提高查询速率，这是InnoDB的自发行为，不需要的话可以关闭。

创建自定义哈希索引，如果存储引擎不支持哈希索引。则可以模拟InnoDB一样创建哈希索引，这样就可以用很小的索引为超长的健创建索引。

思路很简单：在B-Tree之上建立一个伪哈希索引，实际上还是通过B-Tree查找，但它使用的是哈希码而不是键本身进行查找。

例如： 使用B-Tree来存储URL，那么存储的内容就会很大，因为URL本身就很长

select id from url where url="https://www.baidu.com/";

若删除原来url列上的索引，新增一个被索引的url_crc列，使用crc32做哈希索引

select id from url where url="https://www.baidu.com/" and url_crc=crc32("https://www.baidu.com/");

这样查询效率会非常高。mysql优化器会选择性能高的url_crc完成查找，由于哈希值可能会重复，专业维护起来比较麻烦，可以手动维护也可以加触发器。

索引的优点

• 索引最基本的优点是提高查询速度，减少要扫描的数据量。
• 索引可以帮助排序和避免服务器建临时表，如B-Tree索引。
• 索引随机I/O变成顺序I/O。

如何评价某个索引是否合适某个查询呢？

《数据库索引设计与优化》一书中介绍了“三星评价”：
一星——将相关的索引记录放在一起（组合索引）；
二星——索引数据的顺序和查询排列顺序一致（B-Tree索引）。
三星——查询的列索引已经存储。

高性能的索引策略

独立的列

索引的列不能是表达式的一部分，也不能是函数参数，因为mysql无法识别处理，如下：

select * from user where user_id+1=6;

select * from user where todays(current_date)-todays(user_birth)=100;

前缀索引和索引的选择性

前缀索引顾明思议就是截取列数据左边一部分作为索引存储数据。为什么搞这么麻烦？因为当为比较长数据列做索引时需要存储较大会影响索引速度，那么有什么办法可以解决呢，那就是截取部分，虽然会出现相同前缀但是只要足够长可以把不应该出现相同的概率降到很低，怎么评判这个标准？那就是索引的选择性。

索引的选择性，名字听上去很拗口，其实就是索引不重复值和数据表的记录总数的比值，该比值要尽量接近于索引列原来不重复值和数据表的记录总数的比值。如下：

select count(distnct titlename)/count(titlename) from title

原来比值：count(distnct titlename)/count(titlename)=0.312

select 
count(distnct left（titlename,4）)/count(titlename),
count(distnct left（titlename,5）)/count(titlename),
count(distnct left（titlename,6）)/count(titlename),
count(distnct left（titlename,7）)/count(titlename)
from title

各前缀索引比值：
count(distnct left（titlename,4）)/count(titlename)=0.223
count(distnct left（titlename,5）)/count(titlename)=0.256
count(distnct left（titlename,6）)/count(titlename)=0.282
count(distnct left（titlename,7）)/count(titlename)=0.310

所以截取长度7就差不多了

alter table title add key(titlename(7))

前缀索引可以使索引更小、更快，但是无法用于group by 、order by 和覆盖扫描。
order by 排序是不大可能的，因为就算前缀的比值和原来一样，后面的部分是不确定的，group by 到是可能可以，前面部分都不同了后面就不用管了。

多列索引

很多人对多列索引理解不够，常见的错误就算为每个列创建单独索引，或者按照错误的顺序创建多列索引。

在更早版本的msql中只能使用其中某一单列索引，但是在多个条件查询时没有哪一个独立索引是非常有效的，例如在student 表中为user_id 和 user_phone做单列索引，下面语句在老的mysql版本中将会全表扫描也就是说不会用上索引。

select  user_id ,user_phone from user where user_id=‘1’or phone=‘12345678912’

除非你改成

select user_id ,user_phone from user where user_id=‘1’
union all
select user_id ,user_phone from user where phone=‘12345678912’ and user_id<>‘1’

但在mysql 5.0 和以上版本中可以使用单列索引，它会将结果进行合并，如语句中使用 and 或者 or 。通过explain可以看到：
extra:using union(primary,idx_user_phone) ;using where

索引合并策略是一种 优化的结果，但更多的说明了索引设计的不好：

• 当查询涉及多列时需要设计多列索引，而不是为每个列设计单独索引。
• 合并策略是一种妥协策略，它是先根绝单独列查出来，然后进行合并，需要进行缓存，合并时还要检查是否满足要求（剔除重复等）效率不高，有时还不如全表扫描。

如果在explain中看到合并索引应该进行优化，也可以通过optimizer_switch来关闭索引合并功能，或者使用ignore index提示让优化器忽略某些索引。

选择合适的索引列顺序

索引顺序适用于B-Tree索引。索引的顺序依赖于使用该索引的查询，并同时需要考虑更好的满足排序和分组。

当不考虑分组和排序时，将选择性最高的列放在最左边，这样能快速的过滤。如下：

select 
count(distinct user_id)/count(*),
count(distinct user_phone)/count(*)
from user

count(distinct user_id)/count(*)=1
count(distinct user_phone)/count(*)=0.658

所以当查询条件涉及user_id 和 user_phone时创建的索引要把user_id 放在前面。

聚簇索引

聚簇索引不是单独的一种索引类型，而是一种数据的存储方式。具体要看其实现方式，InnoDB的聚簇索引就是B-Tree索引保存了整个数据行。

数据的物理顺序和索引顺序一样，物理顺序只有一种，所以一个表只有一个聚簇索引，一般就是主键索引。

下图展示了聚簇索引中的记录是如何存放的，被索引的列是主键列：

如果没有定义的主键，InnoDB会找一个唯一非空的列来代替，如果没有就创建一个隐式的主键。索引如果换了存储引擎将可能导致表不可用。

聚集索引的优点：

• 根据主键缩小查找范围，避免磁盘IO负载太大。
• 聚簇索引包含了数据行，访问数据快。

聚集索引的缺点：

• 插入速度严重依赖于插入顺序，按照默认的主键顺序插入是加载数据到InnoDB表中最快的，但是如果不是按照顺序加载，最好在加载完成后使用optimizer table命令重新组织下。
• 更新聚簇索引列的代价比较高，因为涉及的是整个数据行，所有有一定的I/O代价。
• 聚簇索引在页满时插入新的数据时会新创建页，只放一条数据占用磁盘。
• 聚簇索引可能会导致全表扫描速度慢，尤其是页分裂导致数据存储不连续。
• 二级索引可能比想象的要大，因为在二级索引的叶子中包含了引用行的主键列。
• 二级索引访问需要两次索引查找，而不是一次。

覆盖索引

所谓的覆盖索引就是指，索引的内容包括了你想要查询的列，所以覆盖索引不仅考虑到了查询条件还考虑到要查询的列，这样就不用再去读取数据行了，速度肯定更快，但是索引包含的内容不要太多，不然内存放不下就麻烦了。

例子：
有个覆盖索引（user_id,user_name）

select user_id,user_name from user 

explain下会看到extra列可以看到using index

优点：

• 索引条目通常远小于数据行，所以可以帮助I/O密集型应用减少数据的搬运，更容易放进内存，所以提升效率明显。
• 覆盖索引可以对InooDB的聚簇索引进行改进，InooDB的二级索引在叶子节点保存了行的主键，为啥不可以学覆盖索引包含点其他内容呢？

不是所有的存储擎都支持覆盖索引，不是所有索引类型都支持覆盖索引，当然B-Tree支持。

有时覆盖索引无法使用，虽然覆盖了查询条件，如下：

select * from user where user_id='xx' and user_name like 'feng%'

无法使用索引的原因：覆盖索引没有覆盖所有查询的列。(mysql 5.5及以下版本索引支持简单的比较操作（>,<,=,!=）和一个通配符在右边（‘xx%’）)

那么没办法解决吗？覆盖索引只要满足查询条件我们还是可以做一定的调整进行利用的，记住主键是默认有索引的，可以利用上。

select * 
from user
join (
select user_id
from user
where user_id='xx' and user_name like 'feng%'
) as u
on u.user_id=user.user_id

这样本来就缩小了范围加上user_id本来就有索引索引查询效率还是可以的。

使用索引扫描进行排序

如果Extra出来的type的值为index，则说明Mysql使用了索引扫描排序。

扫描索引本身是很快的，但是如果需要查询全部列那么需要频繁随机的去读取数据行，所以按顺序全表扫描通常比索引扫描排序快。

MySql要尽可能满足排序和查找行。

索引的列的顺序和排序方向要和order by 一样，如果查询涉及多张表，只有Order by 所要的字段都只在一个表时才能使用。order by个查询限制一样：满足最左前缀要求。

有一种情况order by可以不满足最左前缀的要求，就是前导列为常量。如下：
表有包含（user_birth,user_id,user_name）

select * from user where user_birth='1999-09-21'
order by user_id,user_name

下面是一些不能使用索引做排序的查询：

• 查询使用了不同方向的排序

...where user_birth='1999-09-21' order by user_id desc,user_name asc

• 使用了索引不存在的列

...where user_birth='1999-09-21' order by user_id,user_address

• 无法合成最左前缀

...where user_birth='1999-09-21' order user_name

• 不是常数，后面的索引列无法使用

...where user_birth>'1999-09-21' order user_name

• user_id上用了范围查找

...where user_birth='1999-09-21' and user_id in ('xx','yy') order by user_name

压缩（前缀压缩）索引

MyISAM使用前缀压缩来减少索引的大小，减少占用内存空间，减少从磁盘掉索引进内存的次数，提高查询速度。默认只压缩字符串，但通过参数设置也可以对整数压缩。

方法：通过比较两个值，如果存在两个前缀一样，那么相同的地方可以只存一份，相同的部分用字节数表示。如common,community，可以这样表示”common”,”4,unity”（但是被压缩的要有指针指向没被压缩的——我自己想的），MyISAM对指针也采用类似的方法进行压缩。

缺点：
这样的缺点就是要想确认被压缩的值还要去找没被压缩的值，所以有利就有弊，没有完美的事物。

可以在create_table语句中指定pack_keys参数来控制索引压缩的方式。

冗余和重复索引

MySql允许在同一个列上创建多个索引，所以你会有意无意的创建重复索引而不受限制。MySql需要维护重复索引，并且优化器在查询的时候需要考虑用哪个，所以会影响性能。

重复索引指的是在相同列上创建相同顺序相同类型的索引，应该避免这样的徒劳工，如下：

create table user{
user_id int not null primary key,
...
unique(user_id),
index(user_id)
}engine=InnoDB;

主键和唯一限制都是通过索引来实现的，所以没必要重复弄，主键本来就不能重复。

冗余索引和重复索引有所不同，冗余并不是完全相同，只是可以替代。如创建了索引（A,B），再创建索引（A）就是冗余了。

一般情况下不需要冗余索引，应该尽量对现有索引进行扩展，但是有时候扩展会对使用现有索引方带来影响，例如：在原来的int单列上扩展一个很长的varchar，那么性能可能会下降，首先装进内存就比原来慢了，特别是做覆盖索引的时候。

解决冗余索引和重复索引就是删掉，那么怎么找到这些不必要的索引呢？可以通过写一些 复杂的访问INFORMATION_SCHEMA表的查询来找，不过还有两个更简单的方法。可使用Shlomi Noach的common_schema中的一些视图来定位，common_schema是一系列可以安装到服务器上常用的存储和视图。此外PerconaToolkit中的pt_duplicate_key_checker，该工具通过分析表结构来找出冗余和重复的索引。

索引和锁

索引可以让查询锁定更少的行，锁定超过需要的行会增加锁争用并减少并发性。