我已经优化了这个查询,使其在一开始的一半时间内运行。
现在我想改进它的执行,创建一些索引,但还没有找到方法
查询是:
SELECT straight_join CONCAT(IFNULL(u.nombre,''), ' ', IFNULL(u.apellidos,''), ' ', IFNULL(u.apellido2,''), ' ; ', IFNULL(u.correo,''), ' ; ', IFNULL(u.telefono,''), ' ',
IFNULL(u.horario,''), ' ; ', IFNULL(u.telefono2,''), ' ', IFNULL(u.horario2,''), ' ; ', IFNULL(u.fecha_prox_llamada,'')) AS datos,
usuariohist1.web, usuariohist1.comentario, u.usuario_id, estad.estad_id,
estad.fecha, estad.tipoinf, estad.web_id, estad.procede, estad.convocatoria_id, eventos.evento as curso,
centros.centro, centros.centro_id
FROM centros
INNER JOIN convocatorias ON centros.centro_id = convocatorias.centro_id
INNER JOIN eventos ON eventos.evento_id = convocatorias.evento_id
INNER JOIN estad ON estad.convocatoria_id = convocatorias.convocatoria_id
INNER JOIN usuarios AS u ON estad.usuario_id = u.usuario_id
LEFT JOIN (select web, comentario, usuario_id FROM usuariohist order by fecha desc, id desc ) as usuariohist1
ON u.usuario_id = usuariohist1.usuario_id OR u.n_usuario = usuariohist1.usuario_id
WHERE centros.segcupones = 1 and u.fecha_prox_llamada = '2013-02-13'
group by u.usuario_id, estad.web_id, estad.convocatoria_id
order by estad.fecha desc G
执行计划为:
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: centros
type: ALL
possible_keys: PRIMARY,centro_id,centro_id_2
key: NULL
key_len: NULL
ref: NULL
rows: 2622
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: convocatorias
type: ref
possible_keys: PRIMARY,FK_convocatorias_evento_id,FK_convocatorias_centro_id
key: FK_convocatorias_centro_id
key_len: 3
ref: formaweb_op.centros.centro_id
rows: 66
Extra: Using where
*************************** 3. row ***************************
id: 1
select_type: PRIMARY
table: eventos
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: formaweb_op.convocatorias.evento_id
rows: 1
Extra:
*************************** 4. row ***************************
id: 1
select_type: PRIMARY
table: estad
type: ref
possible_keys: FK_Estad_Convocatorias,FK_Estad_Usuarios
key: FK_Estad_Convocatorias
key_len: 4
ref: formaweb_op.convocatorias.convocatoria_id
rows: 4
Extra:
*************************** 5. row ***************************
id: 1
select_type: PRIMARY
table: u
type: eq_ref
possible_keys: PRIMARY,usuario_id
key: PRIMARY
key_len: 4
ref: formaweb_op.estad.usuario_id
rows: 1
Extra: Using where
*************************** 6. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 23971
Extra:
*************************** 7. row ***************************
id: 2
select_type: DERIVED
table: usuariohist
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 28594
Extra: Using filesort
7 rows in set (0.48 sec)
我已经在centros表上创建了一个覆盖索引(centro_id、centro、segcupones),但性能并没有改善(或者说改善得很少)。现在执行计划显示使用索引,但也使用临时和文件排序。
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: centros
type: index
possible_keys: PRIMARY,centro_id,centro_id_2,centro_id_3
key: centro_id_3
key_len: 307
ref: NULL
rows: 2873
Extra: Using where; Using index; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: convocatorias
type: ref
possible_keys: PRIMARY,FK_convocatorias_evento_id,FK_convocatorias_centro_id
key: FK_convocatorias_centro_id
key_len: 3
ref: formaweb_op.centros.centro_id
rows: 66
Extra: Using where
*************************** 3. row ***************************
id: 1
select_type: PRIMARY
table: eventos
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: formaweb_op.convocatorias.evento_id
rows: 1
Extra:
*************************** 4. row ***************************
id: 1
select_type: PRIMARY
table: estad
type: ref
possible_keys: FK_Estad_Convocatorias,FK_Estad_Usuarios
key: FK_Estad_Convocatorias
key_len: 4
ref: formaweb_op.convocatorias.convocatoria_id
rows: 4
Extra:
*************************** 5. row ***************************
id: 1
select_type: PRIMARY
table: u
type: eq_ref
possible_keys: PRIMARY,usuario_id
key: PRIMARY
key_len: 4
ref: formaweb_op.estad.usuario_id
rows: 1
Extra: Using where
*************************** 6. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 23971
Extra:
*************************** 7. row ***************************
id: 2
select_type: DERIVED
table: usuariohist
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 28594
Extra: Using filesort
7 rows in set (0.54 sec)
我还为表usuariohist(DERIVED)创建了索引,但没有成功,查询计划仍然相同。我尝试过这些索引:(fecha,id)、(usuario_id,fecha,id)和覆盖索引(usuaio_id,ffeha,id,web,comentario)。
相关表格索引:
CREATE TABLE `centros` (
...FIELDS...
PRIMARY KEY (`centro_id`),
KEY `FK_Centros_Aux_Paises` (`pais_id`),
KEY `FK_Centros_Aux_Provincias` (`provincia_id`),
KEY `FK_Centros_Centros_Grupo` (`grupo_id`),
KEY `centro_id_2` (`centro_id`,`tipouniversidad`),
KEY `centro_id` (`centro_id`,`padre_id`,`provincia_id`),
KEY `centro_id_3` (`centro_id`,`centro`,`segcupones`),
CONSTRAINT `FK_Centros_Aux_Paises` FOREIGN KEY (`pais_id`) REFERENCES `aux_paises` (`pais_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_Centros_Aux_Provincias` FOREIGN KEY (`provincia_id`) REFERENCES `aux_provincias` (`provincia_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_Centros_Centros_Grupo` FOREIGN KEY (`grupo_id`) REFERENCES `centros_grupo` (`grupo_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=3373 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `convocatorias` (
...FIELDS...
PRIMARY KEY (`convocatoria_id`),
KEY `FK_Convocatorias_Centros` (`centro_id`),
KEY `FK_Convocatorias_Eventos` (`evento_id`),
CONSTRAINT `FK_Convocatorias_Centros` FOREIGN KEY (`centro_id`) REFERENCES `centros` (`centro_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_Convocatorias_Eventos` FOREIGN KEY (`evento_id`) REFERENCES `eventos` (`evento_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `eventos` (
...FIELDS...
PRIMARY KEY (`evento_id`),
KEY `evento` (`evento`,`temario`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `estad` (
...FIELDS...
CONSTRAINT `FK_Estad_Convocatorias` FOREIGN KEY (`convocatoria_id`) REFERENCES `convocatorias` (`convocatoria_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_Estad_Usuarios` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios` (`usuario_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=360851 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `usuarios` (
...FIELDS...
PRIMARY KEY (`usuario_id`),
KEY `FK_Usuarios_WEBS` (`web_id`),
KEY `usuario` (`usuario`,`clave`),
KEY `usuario_id` (`usuario_id`,`web_id`),
CONSTRAINT `FK_usuarios_web_id` FOREIGN KEY (`web_id`) REFERENCES `webs` (`web_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=256323 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `usuariohist` (
...FIELDS...
PRIMARY KEY (`id`),
KEY `usuario_id` (`usuario_id`,`fecha`,`id`),
KEY `usuario_id_2` (`usuario_id`,`fecha`,`id`,`web`,`comentario`(255))
) ENGINE=InnoDB AUTO_INCREMENT=28153 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
任何人都知道我如何优化这个查询创建索引,请解释解决方案。
提前感谢!
只有几个指针-我现在没有时间进行完整的分析:
对于任何要达到其最大潜力的查询,如果提供了显式值,那么如果它们驱动查询,将有很大帮助
您的查询有centros.segcupones和u.fecha_prox_lamada的特定值,但最后一个没有索引,第一个只有联合索引,所以您的第一个调用端口是为u.fecha_pro_llamada创建一个索引,然后也许可以查看center。segcupones上的单个索引的运行情况。
wayyyy对于一个索引问题来说内容太多了。。。不管你应该索引你所有的主键和外键,以及你加入的任何东西……如果你是在日期加入的,你也可以在那个字段上索引。还要记住,您将希望从组级别索引到成员级别,因此,如果您有三个字段来标识一个成员,则您将希望使用这三个字段进行索引。例如,ClientCd-ID-Sub_ID一起将创建一个代理密钥,以及一个唯一标识符,你可能希望对这三个字段进行索引,以使数据库搜索尽可能高效,同时忽略该人的任何人口统计信息。。。