set global optimizer_seitch=”index_merge=off”    

+—-+————-+——-+——–+——————————–+————–+———+——————-+——+————-+
 

 

在MySQL 5.1 中第叁回引进了optimizer_switch 系统变量,能够

 

    -> a.first_released  

mysql> select @@optimizer_switch;

 
这里根本思量的是何许减小索引占用的长空。贰个更加小的目录意味着越来越少的磁盘I/O
成本,而那又象征能越来越快地走访到需

+—-+————-+—————-+————-+———————————————+——————————–+———+——+——+——————————————————————————+

Records: 0  Duplicates: 0  Warnings: 0  

mysql> explain
selectproduct_id,main_product_id,is_main_product,external_product_id
from product where shop_id = ‘5939’ AND status in  (‘0’) AND
main_product_id in (‘0’) AND product_type in (‘0′,’1’) order by
operator_datedesc limit 800,100;

  

type类型是
index_merge(索引合并排序)。业务必要或滥建索引,数据表上会建大多目录。针对这几个查询,能够通过修改optimizer_switch来优化优化器行为:

    ->  WHERE a.name = ‘Greatest Hits’  

| @@optimizer_switch                                                  
                                                  |

注意

 

从MySQL 5.0
初叶,在各自例外景况中优化器也许会选择三个以上的目录,但是在前期的本子中那样做会促成查询运营尤其缓慢。

其它一种艺术:若是创立的目录不创设,能够建构复合索引,然后删掉这么些单列索引,那样sql效用也会进步。

    ->   ADD INDEX name_part3(name(5));  

mysql参数optimizer_switch

+—-+————-+——–+——-+—————+————+———+——+——+————-+
 

+—-+————-+—————-+————-+———————————————+——————————–+———+——+——+——————————————————————————+

  

 在大家生产条件上,某天mysql服务器压力特别大,load一度达到了100,查询发掘数据库中有雅量的sql语句state
状态result sorting ,result
sorting这种排序特别消耗cpu和内部存款和储蓄器财富。收取当中的一条sql查看施行布署

[Comment]:随着数据容积的增添,特别是当先内部存款和储蓄器和磁盘最大体量的时候,为贰个大型列创造索引恐怕

 

+—-+————-+——–+——-+—————+——+———+——+——+————-+
 

+————————————————————————————————————————+

 

 

而所提供的局地列索引满意了WHERE
条件。怎样挑选妥帖的尺寸取决于数量的布满以及走访路线。这几天平昔不可信赖的主意总结索

+————————————————————————————————————————+

 

 

+—-+————-+——–+——+—————+———+———+——-+——+————-+
 

+————————————————————————————————————————+

4503.com, WHERE a.name = ‘Greatest Hits’  

|  1 |SIMPLE      | product_common |index_merge |
main_product_id,shop_id,status,product_type
|shop_id,main_product_id,status | 5,5,5   | NULL |  810 |
Usingintersect(shop_id,main_product_id,status); Using where; Using
filesort

看下边包车型大巴事必躬亲:

 

[sql] 

 

技巧

mysql
5.1中开首引进optimizer_switch,
调控mysql优化器行为。他有部分结实集,通过on和off调控开启和关闭优化器行为。使用…

+—-+————-+——–+——-+—————+————+———+——+——+————-+
 

| id | select_type |table          |type        |possible_keys        
                     |key                           | key_len | ref  |
rows |Extra                                                            
          |

mysql> EXPLAIN SELECT artist_id,name,founded  

以此艺术也行会有一定风险,影响别的sql,在劳务器端操作要深谋远虑  

两个每秒推行一千次的查询来讲那也是万分有含义的属性进步。比方,把一个原先必要20
阿秒试行的每秒运营1 000 次的询问的

|
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on

因而导致分析器以为不可能差不离地通过数11遍index就能够遍历出多少,故而Extra栏里面就不曾出现Using
Index的唤起。

在mysql优化语句进度中,可通过安装optimizer_switch调整优化行为。

 WHERE name = ‘Queen’  

mysql 5.第11中学起先引入optimizer_switch,
调节mysql优化器行为。他有局地结实集,通过on和off调节开启和停业优化器行为。使用限期全局和对话多少个品级,在5.5中optimizer_swtich
可取结果如下,分化mysql版本可取结果分化。5.1和5.6参阅官方文书档案。

基数的目录带来的熏陶。局地索引是不是适用取决于数量是怎么样访问的。从前介绍覆盖索引时,你可以阅览记录三个缺乏版本的name
列不会对实行过

 

mysql> EXPLAIN SELECT artist_id, name  

 

+—-+————-+——–+————-+—————+————–+———+——+——+———————————————+
 

4 数个目录合併的意况

   

[sql] 

 WHERE name = ‘Queen’  

| id | select_type | table  | type  | possible_keys | key        |
key_len | ref  | rows | Extra       |  

    ->  WHERE name LIKE ‘Queen%’;  

ALTER TABLE artist

|  1 | SIMPLE      | artist | index_merge | name,founded  |
name,founded | 257,2   | NULL |  499 | Using union(name,founded); Using
where |  

 OR (type = ‘Band’ AND founded = ‘1942’);  

  ADD INDEX name_part(name(20));  

高基数的目录施行O卡宴 操作时会现身这种这种索引合併操作。请

    ->  FROM artist  

5 创造越来越好的MySQL 索引

|  1 | SIMPLE      | artist | range | name_part2    | name_part2 | 12
     | NULL |   93 | Using where |  

    ->  FROM artist  

    ->   WHERE name = ‘Queen’  

 

|  1 | SIMPLE      | artist | ref  | founded       | founded | 2       |
const |  498 | Using where |  

会对系统全部质量有影响。覆盖索引对于那多少个运用了累累十分小尺寸的主码和外键约束的巨型规范化格局以来是爱不忍释的优化措施。

总的来讲有些索引对like的功能不是很分明的,或者跟数据布满范围关于,也许那93条数据总体战胜在逐个数据库块中,

+—-+————-+——–+——-+—————+——+———+——+——+————-+
 

|  1 | SIMPLE      | artist | index_merge | name,founded  |
name,founded | 257,2   | NULL | 5900 | Using sort_union(name,founded);
Using where |  

MySQL之SQL语句最优化–索引 1
五个索引取并集组合 [sql] ALTER TABLE album ADD INDEX name_release
(name,first_released); EXPLAIN SELECT a.name, ar.name, a.f…

| id | select_type | table  | type  | possible_keys | key  | key_len
| ref  | rows | Extra       |  

 

   

  

 INNER JOIN artist ar USING (artist_id)  

 …..  

 WHERE name LIKE ‘Queen%’;  

    ->   FROM artist  

 

 DROP INDEX name_part,  

  

目录合併能够带来异常高的八面后珑。数据库写操作的性质参谋因素也一致会潜移默化到获取数据的最优的多寡访问路线。

在它们InnoDB 表索引上将主码增加为最终八个要素。 当QEP 在Extra
列中彰显Using index 时,那并不意味在访

SET @@session.optimizer_switch=’index_merge_intersection=on’;  

2 rows in set (0.00 sec)  

    ->   FROM album a  

mysql> ALTER TABLE artist  

| id | select_type | table  | type        | possible_keys | key      
   | key_len | ref  | rows | Extra                                  |  

 

+—-+————-+——–+————-+—————+————–+———+——+——+—————————————-+
 

1 row in set (0.00 sec)  

1 三个索引取并集组合

 FROM artist  

来明显的本性升高,它被称为覆盖索引。覆盖索引得名于它满意了询问中给定表用到的全数的列。想

+—-+————-+——–+————-+—————+————–+———+——+——+———————————————+
 

 

合理的调解你的目录对优化来讲是十二分关键的,尤其是对此高吞吐量的应用程序。尽管对举行时间的改进仅仅是数阿秒,但对于

1 row in set (0.01 sec)  

 

  

在那几个示例中,Extra前面没有出现Using
Index,所以在目录中著录全名并不曾带来特出的好处。

ALTER TABLE album ADD INDEX name_release (name,first_released);  

 OR founded = 1942\G  

mysql> EXPLAIN SELECT artist_id, name  

Extra: Using union(name,founded); 选择了union的同步索引格局,取合集.

将会生硬革新性能,并且取得越来越大的体积和扩充性。为SQL
查询创立最优索引能够以为是一项措施。

SELECT 语句中的全部列。

 

珍视用的可比多的2个新鲜的目录

   

应当时时事争论估多列索引是还是不是比让优化器合併索列功用更加高。多个单列索引和四个多列索引到底哪个更有优势?那么些主题素材

[sql] 

[sql] 

count了下SELECT count(*) FROM artist WHERE name LIKE ‘Queen%’;
才93条记录,而SELECT count(*) FROM
artist;有577983条记下,依据布满的景况,能够走索引,难道是name(20)的20定义的太长了?

+—-+————-+——–+——-+—————+——+———+——+——+————-+
 

那终人命关天特点意味着InnoDB
引擎中装有非主码索引都包涵主码列了。并且对于那个从MyISAM
存款和储蓄引擎调换过来的表,经常会

震古烁今的性子进步。索引也恐怕使原本实施高效的询问的施行时间压缩多少微秒。在高并发系统中,将一千 000 条查询减弱几纳秒

|  1 | SIMPLE      | artist | index_merge | name,founded  |
name,founded | 257,2   | NULL |  499 | Using union(name,founded); Using
where |  

+—-+————-+——–+——-+—————+————+———+——+——+————-+
 

第一种: 最常见的目录合并的操作是七个索引取并集,当用户对四个有很

    ->  WHERE name LIKE ‘Queen%’;  

  WHERE type = ‘Band’  

  mysql> ALTER TABLE artist  

  mysql> EXPLAIN SELECT artist_id, name  

    ->  OR (type = ‘Band’ AND founded = ‘1942’);  

 FROM artist  

| id | select_type | table  | type        | possible_keys | key      
   | key_len | ref  | rows | Extra                                  |  

看结果,再用name(5) 试试看。