He estado luchando cuando se trata de optimizar la siguiente consulta (Ejemplo 1):
SELECT `service`.* FROM ( SELECT `storeUser`.`storeId` FROM `storeUser` WHERE `storeUser`.`userId` = 1 UNION SELECT `store`.`storeId` FROM `companyUser` INNER JOIN `store` ON `companyUser`.`companyId` = `store`.`companyId` WHERE `companyUser`.`userId` = 1 UNION SELECT `store`.`storeId` FROM `accountUser` INNER JOIN `company` ON `company`.`accountId` = `accountUser`.`accountId` INNER JOIN `store` ON `company`.`companyId` = `store`.`companyId` WHERE `accountUser`.`userId` = 1 ) AS `storeUser` INNER JOIN `service` ON `storeUser`.`storeId` = `service`.`storeId` LIMIT 10;La subconsulta debería devolver algo como "1","2","3,"4"
De todos modos, es súper lento y tarda unos 48 segundos en dar una respuesta, aunque la subconsulta en sí, se ejecutó en una consola diferente, tarda unos 0,0020 ms en dar resultados.
Lo mismo se aplica si coloco la subconsulta dentro de un IN (Ejemplo 2):
SELECT `service`.* FROM `service` WHERE 1 AND `service`.`storeId` IN ( SELECT `storeUser`.`storeId` FROM `storeUser` WHERE `storeUser`.`userId` = 1 UNION SELECT `store`.`storeId` FROM `companyUser` INNER JOIN `store` ON `companyUser`.`companyId` = `store`.`companyId` WHERE `companyUser`.`userId` = 1 UNION SELECT `store`.`storeId` FROM `accountUser` INNER JOIN `company` ON `company`.`accountId` = `accountUser`.`accountId` INNER JOIN `store` ON `company`.`companyId` = `store`.`companyId` WHERE `accountUser`.`userId` = 1 ) LIMIT 10;Sin embargo, si simplemente pongo los valores devueltos por esa consulta, manualmente, es básicamente al instante:
SELECT `service`.* FROM `service` WHERE 1 AND `service`.`storeId` IN ( "1", "2", "3", "4", "5" ) LIMIT 10;Es importante mencionar que revisé los índices en las uniones y todo parece estar en su lugar, y EXPLAIN [consulta] devuelve una puntuación filtrada de 100 para básicamente todo.
Editar:
Perdón por no proporcionar suficiente información antes, espero que esto pueda ser más útil:
MySQL 5.7, Storage engine: InnoDB EXPLAINs 1.) StoreUser id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra 1 | SIMPLE | storeUser | NULL | ref | PRIMARY, storeUserUser | PRIMARY | 4 | const | 1 |100.00 | Using index 2.) CompanyUser id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra 1 | SIMPLE | companyUser | NULL | ref | PRIMARY,companyUserCompany,companyUserUser | companyUserUser | 4 | const | 30 | 100.00 | Using index 1 | SIMPLE | store | NULL | ref | storeCompany | storeCompany | 4 | Table.companyUser.companyId | 5 | 100.00 | Using index 3.) AccountUser id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra 1 | SIMPLE | accountUser | NULL | ref | PRIMARY,accountUserUser | accountUserUser | 4 | const | 1 | 100.00 | Using index 1 | SIMPLE | company | NULL | ref | PRIMARY,companyAccount | companyAccount | 4 | Table.accountUser.accountId | 305 | 100.00 | Using index 1 | SIMPLE | store | NULL | ref | storeCompany | storeCompany | 4 | Table.company.companyId | 5 | 100.00 | Using index 4.) Whole query (Example 2) id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra 1 | PRIMARY | service | NULL | ALL | NULL | NULL | NULL | NULL | 2836046 | 100.00 | Using where 2 | DEPENDENT SUBQUERY | storeUser | NULL | eq_ref | PRIMARY,storeUserStore,storeUserUser | PRIMARY | 8 | const,func | 1 | 100.00 | Using index 3 | DEPENDENT UNION | store | NULL | eq_ref | PRIMARY,storeCompany | PRIMARY | 4 | func | 1 | 100.00 | NULL 3 | DEPENDENT UNION | companyUser | NULL | eq_ref | PRIMARY,companyUserCompany,companyUserUser | PRIMARY | 8 | const,Table.store.companyId | 1 | 100.00 | Using index 4 | DEPENDENT UNION | companyUser | NULL | ref | PRIMARY,accountUserUser | accountUserUser | 4 | const | 1 | 100.00 | Using index 4 | DEPENDENT UNION | store | NULL | eq_ref | PRIMARY,storeCompany | PRIMARY | 4 | func | 1 | 100.00 | NULL 4 | DEPENDENT UNION | company | NULL | eq_ref | PRIMARY,companyAccount | PRIMARY | 4 | Table.store.companyId | 1 | 100.00 | Using where NULL | UNION RESULT | <union2,3,4>| NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporaryNo nos mostró sus índices o la salida de EXPLAIN, por lo que todo esto son conjeturas.
Claramente, es la subconsulta en su segundo ejemplo la que no está optimizada. Esa subconsulta es una UNIÓN con tres ramas. ¿La forma en que aborda los problemas de rendimiento? Analice y optimice cada rama de la UNIÓN por separado.
Ciertamente necesita algunos índices mejores, a menos que su servidor de base de datos sea demasiado pequeño o esté mal configurado. Eso es muy raro, así que trabajemos en índices.
La primera rama es
SELECT storeUser.storeId FROM storeUser WHERE storeUser.userId = 1 Este índice compuesto cubre esa consulta. Intenta agregarlo. Si tiene un índice separado solo en el userId de usuario, suéltelo cuando agregue este.
ALTER TABLE storeUser ADD INDEX userId_storeId (userId, storeId);La segunda rama es
SELECT store.storeId FROM companyUser INNER JOIN store ON companyUser.companyId = store.companyId WHERE companyUser.userId = 1Las subconsultas con operaciones JOIN son un poco engañosas para optimizar sin acceso a la salida EXPLAIN, por lo que esto es una conjetura. Sin embargo, supongo que estos índices ayudarán. (Suponiendo que usa InnoDB y el PK en la tienda es storeId).
ALTER TABLE companyUser ADD INDEX userId_companyId (userId, companyId); ALTER TABLE store ADD INDEX companyId (companyId);Un análisis similar se aplica a la tercera rama de la UNIÓN.
Y, agregue este índice. Su EXPLAIN indica que falta, por lo que se requiere un escaneo completo de la tabla de esa tabla grande.
ALTER TABLE service ADD INDEX storeId (storeId); Una vez más , sería mucho más fácil ayudarlo si nos mostrara las definiciones de su tabla con índices. SHOW CREATE TABLE service; por ejemplo, nos mostraría lo que necesitamos para su mesa de service . Sugerencia profesional cuando solucione problemas de rendimiento de este tipo, siempre verifique dos veces sus índices . Pregúntame cómo sé eso cuando tienes un par de horas libres.
Consejo profesional Sea obsesivo con el formato de sus consultas para que sean legibles. Usted, usted mismo dentro de un año y sus compañeros de trabajo aún no nacidos necesitan leer y razonar sobre ellos. En mi forma de pensar, eso significa saltarse esos tontos acentos graves.
Quizás necesites repensar el esquema. Parece que necesita una tabla para "usuario" en lugar de, o además de, las 3 tablas para diferentes tipos de "usuarios".
Mientras tanto, es probable que estos índices compuestos ayuden al rendimiento en cualquiera de las dos formulaciones:
storeUser: INDEX(storeId, userId) storeUser: INDEX(userId, storeId) service: INDEX(storeId) store: INDEX(companyId, storeId) companyUser: INDEX(userId, companyId) company: INDEX(accountId, companyId) accountUser: INDEX(userId, accounted)Al agregar un índice compuesto, DROP índice(s) con las mismas columnas iniciales. Es decir, cuando tenga ÍNDICE(a) e ÍNDICE(a,b), deseche el primero.
En particular, storeUser huele como una tabla de mapeo de muchos a muchos. Si es así, consulte Mapeo Many:many para obtener más información.
En general IN( SELECT ... ) no se optimiza bien, pero es posible que encuentre lo contrario para su consulta.
Lamento no dar más detalles sobre los esquemas, pero no se me permitió compartirlo aquí, de todos modos, el problema estaba en otra parte: la mesa de servicio estaba recibiendo una gran cantidad de solicitudes, algunas acciones incluso la bloqueaban, terminando en tiempos lentos cada vez que accedíamos a esa tabla, hemos arreglado nuestro otro proceso y ahora funciona muy bien. Aprecio enormemente su tiempo y esfuerzo, gracias.