昨天弄了句SQL,把数据库给弄crash了,引以为戒
差不多是这个样子的
这个in里面有个嵌套語句
可以看到,mysql执行这句话时,会有很多步骤,当数据量一大,它就不行了
差不多是这个样子的
SELECT Photo.* FROM Photo WHERE (Photo.Parent in (select CONCAT('AssociationEvent-', AssociationEvent.ID) as Parent from AssociationEvent WHERE AssociationEvent.Association = 98 union select CONCAT('PersonAssociation-', PersonAssociation.ID) as Parent from PersonAssociation WHERE PersonAssociation.IsBrothersOnly = 0 and PersonAssociation.Association = 98)) ORDER BY Sequence DESC LIMIT 8
这个in里面有个嵌套語句
mysql> explain SELECT Photo.* FROM Photo WHERE (Photo.Parent in (select CONCAT('AssociationEvent-', AssociationEvent.ID) as Parent from AssociationEvent WHERE AssociationEvent.Association = 98 union select CONCAT('PersonAssociation-', PersonAssociation.ID) as Parent from PersonAssociation WHERE PersonAssociation.IsBrothersOnly = 0 and PersonAssociation.Association = 98)) ORDER BY Sequence DESC LIMIT 8 ;
+----+--------------------+-------------------+--------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------------------+--------+---------------+----------+---------+------+------+-------------+
| 1 | PRIMARY | Photo | index | NULL | Sequence | 5 | NULL | 3199 | Using where |
| 2 | DEPENDENT SUBQUERY | AssociationEvent | system | NULL | NULL | NULL | NULL | 1 | |
| 3 | DEPENDENT UNION | PersonAssociation | system | NULL | NULL | NULL | NULL | 1 | |
| NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------------+-------------------+--------+---------------+----------+---------+------+------+-------------+
4 rows in set (0.00 sec)
可以看到,mysql执行这句话时,会有很多步骤,当数据量一大,它就不行了