做网站一定要自己搭建服务器吗,学做网网站论坛,武宣县住房和城乡建设局网站,海门建设局网站一. 前言
当我们对多列索引的理解不够深刻的时候,往往会为每个列创建独立的索引或者按照错误的顺序创建多列索引。
二. 案例说明 问题一: 为每个列创建独立索引 CREATE TABLE t(c1 INT,c2 INT,c3 INT,KEY(c1),KEY(c2),KEY(c3)
);这种索引策略,一般都是由于人们听到一些专家诸…一. 前言
当我们对多列索引的理解不够深刻的时候,往往会为每个列创建独立的索引或者按照错误的顺序创建多列索引。
二. 案例说明 问题一: 为每个列创建独立索引 CREATE TABLE t(c1 INT,c2 INT,c3 INT,KEY(c1),KEY(c2),KEY(c3)
);这种索引策略,一般都是由于人们听到一些专家诸如把Where条件里面的列都建上索引这样模糊的建议导致的。这样一来最好也只能是一星索引,其性能比真正最优的索引可能差几个数量级。有时如果无法设计一个三星索引,那么不如忽略掉Where子句,集中精力优化索引顺序,或者创建一个全覆盖索引。
2.1 三星索引
一星: 索引将相关的记录放在一起 二星: 如果索引中的数据顺序和查找顺序一致 三星: 如果索引中的列包含了查询中需要的全部列
三星索引的相关实践可以参考: 三星索引实战
2.2 Mysql底层对单列索引的处理
在多个列上建立独立的单列索引大部分情况下并不能提高Mysql的查询性能。Mysql 5.0 和更新版本引入了一种叫索引合并(index merge)的策略,一定程度上可以使用表上的多个单列索引来定位指定的行。更早版本的 Mysql只能使用其中某一个单列索引,然而这种情况下没有哪一个独立的单独索引是非常有效的。例如下表 film_actor在字段 film_id 和 actor_id 各有一个单列索引。但对于下面这个查询的 Where 条件这两个单列索引都不是好的选择:
建表语句:
CREATE TABLE film_actor (id int(20) NOT NULL AUTO_INCREMENT COMMENT 主键id,actor_id int(20) NOT NULL COMMENT 演员id,film_id int(20) DEFAULT NULL COMMENT 电影id,PRIMARY KEY (id) USING BTREE,KEY idx_film_id (film_id) USING BTREE,KEY idx_actor_id (actor_id) USING BTREE
) ENGINEInnoDB AUTO_INCREMENT5 DEFAULT CHARSETutf8mb4查询语句:
SELECT film_id, actor_id FROM film_actor
WHERE actor_id 1 OR film_id 1;执行计划:
在老的 Mysql版本中,Mysql对这个查询会使用全表扫描。除非改下成如下的两个查询UNION的方式:
SELECT film_id, actor_id FROM film_actor WHERE actor_id 1
UNION ALL
SELECT film_id, actor_id FROM film_actor WHERE film_id 1
AND actor_id 1执行计划:
但在Mysql 5.0和更新的版本中,查询能够同时使用两个单列索引进行扫描,并将结果进行合并。这种算法有三个变种: OR条件的联合(union),AND条件的相交(intersection),组合前两种情况的联合及相交。 Tips: 索引合并通过能在执行计划的 Extra上看到 2.3 索引合并背后的问题
索引合并策略有时候是一种优化的结果,但实际上更多的时候说明表的索引建的很糟糕。
当出现服务器对多个索引做相交操作时(通常有多个AND条件),通常意味着需要一个包含所有相关列的多列索引,而不是多个独立的单列索引。当服务器需要对多个索引做联合操作时(通常有多个OR条件),通常需要消耗大量CPU和内存资源在算法的缓存,排序和合并操作上。特别是当中有些索引选择性不高,需要合并扫描返回大量数据的时候。更重要的是,优化器不会把这些计算到查询成本(cost) 中,优化器只关心随机页面读取。这会使得查询的成本被低估,导致该执行计划还不如直接走全表扫描。这样做不但会消耗更多的CPU和内存资源,还可能会影响查询的并发性,但如果是单独运行这样的查询则往往会忽略对并发性的影响。通常来说,还不如像在Mysql 4.1 或者更早的时代一样,将查询改写成 UNION的方式往往更好。
如果在EXPLAIN中看到索引合并,应该好好检查一下查询和表的结构,看是不是已经是最优的。也可以通过参数 optimizer_switch 来关闭索引合并功能,也可以使用 Ignore index 提示让优化器忽略掉某些索引
