Some pointers may be invalid and cause the dump to abort

运行一条SQL导致MYSQL数据库crash

mysql关键log

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7f04182aa4d8): SELECT         case when gender = '1' then '   ' WHEN gender = '0' then '   ' ELSE '            ' end name,         count(0) manNumber         FROM  (         SELECT         s.face_id,         s.count_face,         s.device_id,         s.groupGender,         s.gender,         ide.client         FROM  (         SELECT  face_id,count_face,device_id,GROUP_CONCAT(gender SEPARATOR ',') as groupGender ,SUBSTRING_INDEX( GROUP_CONCAT(gender SEPARATOR ','), ',',- 1 ) as gender  FROM  (         SELECT  face_id,COUNT(face_id) as count_face ,device_id,gender,GROUP_CONCAT(gender SEPARATOR ',') AS `groupGender`         FROM  statistics.stay_person          WHERE gender != '-1'                              AND DATE_FORMAT(start_time, '%Y-%m-%d') >= '2020-12-14'                                           AND DATE_FORMAT(start_time, '%Y-%m-%d') <= '2020-12-14'          GROUP  BY  face_id,gender,device_id         ) this GROUP  BY   face_id,count_face,device_id HAVING  MAX(count_face)         ) s LEFT  JOIN  iposm.device ide on 
Connection ID (thread ID): 14
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

完整sql

select age as name,COUNT(age) as manNumber
        from  (
        select s.face_id,s.count_face,ide.brand,groupAge,age as ageNumber,
        CASE
        WHEN age  >= '0'  AND age   <= '19' THEN '0~19岁'
        WHEN age  >= '20' AND age   <= '24' THEN '20~24岁'
        WHEN age  >= '25' AND age   <= '29' THEN '25~29岁'
        WHEN age  >= '30' AND age   <= '34' THEN '30~34岁'
        WHEN age  >= '35' AND age   <= '39' THEN '35~39岁'
        WHEN age  >= '40' THEN '40岁以上'
        end as age
        from  (
        SELECT  face_id,count_face,device_id,GROUP_CONCAT(age order by age ASC) as groupAge,SUBSTRING_INDEX( 		 GROUP_CONCAT(age order by age ASC), ',',- 1 ) as age
        FROM  (
        SELECT  face_id,COUNT(face_id) as count_face ,device_id,age,GROUP_CONCAT(age SEPARATOR ',') AS `groupGender`
        FROM  A
        GROUP  BY  face_id,age,device_id
        ) this GROUP  BY   face_id,count_face,device_id HAVING  MAX(count_face)
        ) s LEFT  JOIN  B ide on  s.device_id = ide.id
	
        )   s where s.age IS NOT NULL  group by age

原因分析

命令执行缓慢,MySQL服务器卡死，导致CPU飙升而crash
运行某一条SQL导致mysql crash,基本上是sql身的问题，开始运行没有问题是因为数据量比较小，当数据量上来就会发生问题

我的解决方案

去掉最外层的 IS NOT NULL
因为 IS NOT NULL它会全表去进行查找，导致sql运行缓慢。最重要的是找到导致sql本身导致慢问题。