Mysql database, inconsistent index character set, slow SQL query, interface timeout

mysql database ,slowsql Slow query causes interface timeout problem

The phenomenon ： Log in slowsql, It can't be queried in the database , The interface timed out and cannot be loaded . Multi table associated query , There is an index, but it is not useful in the production environment .

The analysis process ：
1. View execution plan , explain. In the development environment, the time is very fast , Timeout in production environment , Index failure . Relational query , How many tables are there 10 10000 data ,join Take Cartesian set , The amount of data queried is very large .
2. To optimize the sql, Development environment from 10 Optimize to... In about seconds 0. Seconds . The production environment still times out .
3. Since there are no other error logs , I still doubt the problem on the database side . Because each environment is consistent . First, eliminate the database version problem .
4. Check whether the watch is locked ,show OPEN TABLES where In_use>0. If there is , see 5
5. Query process ,show processlist. After querying the locked process kill fall

reason ：
The index of the associated query , Character set and sort character set are inconsistent , Cause index to fail .