Existem ferramentas de benchmarking do MySQL? [fechado]

Question

ashuthosh

Asked: 2013-02-19 22:13:28 +0800 CST2013-02-19 22:13:28 +0800 CST 2013-02-19 22:13:28 +0800 CST

A consulta não está usando índices na terceira tabela na junção esquerda

772

Minha consulta não está usando índices na terceira tabela (pci promotion_coupon_images).
Tentei usar índice e forçar índice na consulta, mas não há alteração no resultado.
qualquer pessoa, por favor, me sugira como esta consulta usará índices na tabela (pci promotion_coupon_images)

consulta

select pc.*,pct.pretty_name as coupon_type,pct.description as coupon_type_desc,pci.pretty_name as image_name,pci.width,pci.height from
promotion_coupon_types pct,promotion_coupons pc left join promotion_coupon_images pci on pc.coupon_id = convert(pci.image_key,unsigned integer)
where pc.coupon_type_id=pct.coupon_type_id  and pc.promotion_id=17;

Estrutura da tabela:

mysql>  show create table promotion_coupon_types\G
*************************** 1. row ***************************
       Table: promotion_coupon_types
Create Table: CREATE TABLE `promotion_coupon_types` (
  `row_mod` datetime DEFAULT NULL,
  `row_create` datetime DEFAULT NULL,
  `coupon_type_id` int(11) NOT NULL,
  `pretty_name` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `coupon_type_code` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `description` longtext COLLATE latin1_bin,
  `type` varchar(50) COLLATE latin1_bin DEFAULT NULL,
  `validate_callback` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `calculate_callback` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `remove_callback` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `coupon_constructor_callback` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `alter_subtotal` int(11) DEFAULT NULL,
  `active` int(11) DEFAULT NULL,
  UNIQUE KEY `idx_1505` (`coupon_type_id`),
  UNIQUE KEY `idx_1711` (`coupon_type_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin
1 row in set (0.00 sec)

mysql>  show create table promotion_coupons\G
*************************** 1. row ***************************
       Table: promotion_coupons
Create Table: CREATE TABLE `promotion_coupons` (
  `row_mod` datetime DEFAULT NULL,
  `row_create` datetime DEFAULT NULL,
  `coupon_id` int(11) DEFAULT NULL,
  `promotion_id` int(11) DEFAULT NULL,
  `coupon_type_id` int(11) NOT NULL,
  `pretty_name` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `description` longtext COLLATE latin1_bin,
  `accept_text` longtext COLLATE latin1_bin,
  `active` int(11) DEFAULT NULL,
  `start_date` datetime DEFAULT NULL,
  `end_date` datetime DEFAULT NULL,
  `value` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `min_value` decimal(6,2) DEFAULT NULL,
  `needs_coupon` int(11) DEFAULT NULL,
  `display` int(11) DEFAULT NULL,
  `auto_apply` int(11) DEFAULT NULL,
  `applicable_start_date` datetime DEFAULT NULL,
  `applicable_end_date` datetime DEFAULT NULL,
  `tax_after_discount` int(11) DEFAULT NULL,
  `delivery_type` varchar(50) COLLATE latin1_bin DEFAULT NULL,
  `items_list` longtext COLLATE latin1_bin,
  `display_options` varchar(50) COLLATE latin1_bin DEFAULT NULL,
  UNIQUE KEY `idx_960` (`coupon_id`),
  KEY `idx_1067` (`start_date`),
  KEY `idx_1299` (`promotion_id`),
  KEY `idx_820` (`end_date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin
1 row in set (0.00 sec)

mysql>  show create table promotion_coupon_images\G
*************************** 1. row ***************************
       Table: promotion_coupon_images
Create Table: CREATE TABLE `promotion_coupon_images` (
  `row_mod` datetime DEFAULT NULL,
  `row_create` datetime DEFAULT NULL,
  `image_key` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `data` longtext COLLATE latin1_bin,
  `content_type` varchar(40) COLLATE latin1_bin DEFAULT NULL,
  `width` int(11) DEFAULT NULL,
  `height` int(11) DEFAULT NULL,
  `pretty_name` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  UNIQUE KEY `idx_939` (`image_key`),
  KEY `idx_pci_cmp_n1` (`pretty_name`,`width`,`height`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin
1 row in set (0.00 sec)

plano de explicação de consulta normal

+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
| id | select_type | table | type   | possible_keys | key      | key_len | ref                   | rows | Extra       |
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
|  1 | SIMPLE      | pc    | ref    | idx_1299      | idx_1299 | 5       | const                 |  356 | Using where |
|  1 | SIMPLE      | pct   | eq_ref | idx_1505      | idx_1505 | 4       | cms.pc.coupon_type_id |    1 |             |
|  1 | SIMPLE      | pci   | ALL    | NULL          | NULL     | NULL    | NULL                  |    1 |             |
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
3 rows in set (0.00 sec)

use o plano de explicação do índice:

mysql> explain select pc.*,pct.pretty_name as coupon_type,pct.description as coupon_type_desc,pci.pretty_name as image_name,pci.width,pci.height from promotion_coupon_types pct,promotion_coupons pc left join promotion_coupon_images pci USE INDEX(idx_pci_cmp_n1)  on pc.coupon_id = convert(pci.image_key,unsigned integer)  where pc.coupon_type_id=pct.coupon_type_id  and pc.promotion_id=17;
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
| id | select_type | table | type   | possible_keys | key      | key_len | ref                   | rows | Extra       |
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
|  1 | SIMPLE      | pc    | ref    | idx_1299      | idx_1299 | 5       | const                 |  356 | Using where |
|  1 | SIMPLE      | pct   | eq_ref | idx_1505      | idx_1505 | 4       | cms.pc.coupon_type_id |    1 |             |
|  1 | SIMPLE      | pci   | ALL    | NULL          | NULL     | NULL    | NULL                  |    1 |             |
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
3 rows in set (0.00 sec)



mysql> explain select pc.*,pct.pretty_name as coupon_type,pct.description as coupon_type_desc,pci.pretty_name as image_name,pci.width,pci.height from promotion_coupon_types pct,promotion_coupons pc left join promotion_coupon_images pci USE INDEX(idx_939)  on pc.coupon_id = convert(pci.image_key,unsigned integer)  where pc.coupon_type_id=pct.coupon_type_id  and pc.promotion_id=17;
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
| id | select_type | table | type   | possible_keys | key      | key_len | ref                   | rows | Extra       |
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
|  1 | SIMPLE      | pc    | ref    | idx_1299      | idx_1299 | 5       | const                 |  356 | Using where |
|  1 | SIMPLE      | pct   | eq_ref | idx_1505      | idx_1505 | 4       | cms.pc.coupon_type_id |    1 |             |
|  1 | SIMPLE      | pci   | ALL    | NULL          | NULL     | NULL    | NULL                  |    1 |             |
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
3 rows in set (0.00 sec)

plano de explicação do índice de força:

mysql> explain select pc.*,pct.pretty_name as coupon_type,pct.description as coupon_type_desc,pci.pretty_name as image_name,pci.width,pci.height from promotion_coupon_types pct,promotion_coupons pc left join promotion_coupon_images pci force INDEX(idx_939)  on pc.coupon_id = convert(pci.image_key,unsigned integer)  where pc.coupon_type_id=pct.coupon_type_id  and pc.promotion_id=17;
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
| id | select_type | table | type   | possible_keys | key      | key_len | ref                   | rows | Extra       |
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
|  1 | SIMPLE      | pc    | ref    | idx_1299      | idx_1299 | 5       | const                 |  356 | Using where |
|  1 | SIMPLE      | pct   | eq_ref | idx_1505      | idx_1505 | 4       | cms.pc.coupon_type_id |    1 |             |
|  1 | SIMPLE      | pci   | ALL    | NULL          | NULL     | NULL    | NULL                  |    1 |             |
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
3 rows in set (0.00 sec)

mysql> explain select pc.*,pct.pretty_name as coupon_type,pct.description as coupon_type_desc,pci.pretty_name as image_name,pci.width,pci.height from promotion_coupon_types pct,promotion_coupons pc left join promotion_coupon_images pci force INDEX(idx_pci_cmp_n1)  on pc.coupon_id = convert(pci.image_key,unsigned integer)  where pc.coupon_type_id=pct.coupon_type_id  and pc.promotion_id=17;
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
| id | select_type | table | type   | possible_keys | key      | key_len | ref                   | rows | Extra       |
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
|  1 | SIMPLE      | pc    | ref    | idx_1299      | idx_1299 | 5       | const                 |  356 | Using where |
|  1 | SIMPLE      | pct   | eq_ref | idx_1505      | idx_1505 | 4       | cms.pc.coupon_type_id |    1 |             |
|  1 | SIMPLE      | pci   | ALL    | NULL          | NULL     | NULL    | NULL                  |    1 |             |
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
3 rows in set (0.00 sec)

se eu removi converter inteiro também não há mudança no resultado

mysql> explain select pc.*,pct.pretty_name as coupon_type,pct.description as coupon_type_desc,pci.pretty_name as image_name,pci.width,pci.height from promotion_coupon_types pct,promotion_coupons pc left join promotion_coupon_images pci on pc.coupon_id = pci.image_key where pc.coupon_type_id=pct.coupon_type_id  and pc.promotion_id=17; +----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
| id | select_type | table | type   | possible_keys | key      | key_len | ref                   | rows | Extra       |
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
|  1 | SIMPLE      | pc    | ref    | idx_1299      | idx_1299 | 5       | const                 |  356 | Using where |
|  1 | SIMPLE      | pct   | eq_ref | idx_1505      | idx_1505 | 4       | cms.pc.coupon_type_id |    1 |             |
|  1 | SIMPLE      | pci   | ALL    | idx_939       | NULL     | NULL    | NULL                  |    1 |             |
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
3 rows in set (0.00 sec)

encontre a estrutura da tabela com a nova coluna image_key_int em promotion_coupon_images para lançar índices também adicionados.

mysql> show create table promotion_coupon_images\G
*************************** 1. row ***************************
       Table: promotion_coupon_images
Create Table: CREATE TABLE `promotion_coupon_images` (
  `row_mod` datetime DEFAULT NULL,
  `row_create` datetime DEFAULT NULL,
  `image_key` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `data` longtext COLLATE latin1_bin,
  `content_type` varchar(40) COLLATE latin1_bin DEFAULT NULL,
  `width` int(11) DEFAULT NULL,
  `height` int(11) DEFAULT NULL,
  `pretty_name` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `image_key_int` int(10) unsigned DEFAULT NULL,
  UNIQUE KEY `idx_939` (`image_key`),
  KEY `idx_pci_cmp_n1` (`pretty_name`,`width`,`height`) USING BTREE,
  KEY `idx_img_key_n1` (`image_key_int`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin
1 row in set (0.00 sec)

1 respostas

Voted

Stefan Rogin · Answer 1 · 2013-02-20T01:05:45+08:00

Best Answer

Stefan Rogin

2013-02-20T01:05:45+08:002013-02-20T01:05:45+08:00

O problema é pc.coupon_id = convert(pci.image_key,unsigned integer)que, como disse eelke . Ter uma conversão implícita nega o uso de um índice no MySQL. Infelizmente não consegui encontrar nada sobre a conversão de varchar para int e índices na documentação oficial do MySQL .

Você pode ler mais sobre o assunto aqui .

A solução seria:

altere a tabela promotion_coupon_imagese converta pci.image_keypara unsigned intantes de ingressar
ou adicione outra coluna do tipo int na pcitabela, defina seu conteúdo como convert(pci.image_key,unsigned integer)e use-o para a junção em vez de pci.image_key.

3

A consulta não está usando índices na terceira tabela na junção esquerda

Como fazer a saída do sqlplus aparecer em uma linha?

Selecione qual tem data máxima ou data mais recente

Como faço para listar todos os esquemas no PostgreSQL?

Conceder acesso a todas as tabelas para um usuário

Listar todas as colunas de uma tabela especificada

Como usar o sqlplus para se conectar a um banco de dados Oracle localizado em outro host sem modificar meu próprio tnsnames.ora

Como você mysqldump tabela (s) específica (s)?

Listar os privilégios do banco de dados usando o psql

Como inserir valores em uma tabela de uma consulta de seleção no PostgreSQL?

Como faço para listar todos os bancos de dados e tabelas usando o psql?

A consulta não está usando índices na terceira tabela na junção esquerda

1 respostas

relate perguntas