Why MySQL queries are slow [finishing edition]

In the previous blog in succession to record the query efficiency of the article. Today in the collation, write some of their own notes are as follows:
Common reasons for slow queries are as follows:
1. No index or index is not used.
PS: Indexes are used to quickly look for records with specific values, and all MySQL indexes are saved as B-trees. If there is no index, MySQL must start scanning all records of the entire table from the first record until it finds a record that meets the requirements. The higher the number of records in the table, the higher the cost of this operation. If an index has been created on a column that is a search condition, MySQL can quickly get to where the target record is without scanning any records. If a table has 1000 records, finding records by index is at least 100 times times faster than sequential scan records.
Index Type :
Normal index: This is the most basic index type, no uniqueness, and so on.
Uniqueness Index: Basically the same as a normal index, but all indexed columns can only appear once and remain unique.
Primary key: The primary key is a unique index, but must be specified as "PRIMARY key".
Full-Text indexing: MySQL supports full-text indexing and full-text retrieval starting from 3.23.23. In MySQL, the index type of the full-text index is fulltext. A full-text index can be created on a varchar or text-type column.
2, the IO throughput is small to form a bottleneck.
PS: This is from the system layer to analyze MySQL is more consumption io. General database monitoring is also more concerned about IO.
Monitoring command: $iostat-D-K 1 10
The parameter-D indicates that the device (disk) usage status is displayed; k Some columns that use block are forced to use kilobytes; 1 10 indicates that the data is refreshed every 1 seconds and is displayed 10 times.
3. Insufficient memory
Monitoring memory usage: Vmstat [-n] [delay [times]]
Memory

SWPD: Switch to memory on swap memory (default in kilobytes) • If the value of SWPD is not 0 or larger, such as more than 100M, but Si, so has a long value of 0, this situation we can not worry about, does not affect the system performance. Free: Idle physical memory buff: Buffer cache memory for block device read/write buffering cache: As page cache memory, file system cache if the cache value is large, it indicates that the cache has more than the number of files, If the files that are frequently accessed are available to the cache, the disk's read Io bi is very small.
4. Slow network speed
Ping Ip-t See if there is a packet loss.
5. The amount of data is too large for a single query.
For example, there is no paging query, one time to extract tens of thousands of records. The database may get stuck.
6, there is a deadlock
The so-called deadlock: refers to two or more than two processes in the execution process, because of the competition for resources caused by a mutual waiting phenomenon, if there is no external force, they will not be able to proceed.
Show InnoDB status checks engine status to see which statements produce deadlocks.
Execute show processlist to find the deadlock thread number. Then kill Processno.
7. An unnecessary row or column is returned
General Query SQL statements Be sure to specify the fields explicitly. Instead of using * to query
8. Note the difference between Union and union all. UNION All good
The Union will filter out duplicate records after the table link is made, so the resulting set of results will be sorted after the table is connected, the duplicate records are deleted and the results returned. So the union all efficiency must be high!
9.

Why MySQL queries are slow [finishing edition]