Some things about mysql optimization are sorted out

1. Ask for help? contents
2. Separate BLOB or TEXT using the synthetic hash value
3. Fixed points for currency use (decimal or numberic)
4.sql_mode?
5.order by rand() limit 1000;
6. Optimize show status like 'Com' Com_select Com_insert, etc
7. The value of Handler_read_key will be very high, which represents the number of times a row has been read by the index value; a very low value indicates that the performance improvement obtained by increasing the index is not high, because the index is not used very often; a high value of Handler_read_rnd_next means that the query is running inefficiently and index recovery should be built. This value means the number of requests to read the next 1 line in the data file. If you are doing a lot of table scans, this value is higher. It is common to note that a table index is incorrect or that a written query does not utilize an index.
8. Periodic analysis of table ANALYZE TABLE CHECK TABLE CHECKSUM TABLE
9. Optimization table OPTIMIZE TABLE
10. Import big data :Myisam ALTER TABLE DISABLE KEYS the data ALTER TABLE tblname ENABLE KEYS;
Innodb SET UNIQUE_CHECKS=0 SET AUTOCOMMIT=0
11. Optimize insert:LOAD DATA INFILE replace ignore
12. Optimize group by ORDER BY NULL
13.show status like 'Table%'; show status like 'innodb_row_lock%';
14.CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;
15. Important parameters affecting the performance of Mysql:
key_buffer_size: note: the key cache (variable key_buffer_size) is Shared by all threads; Other caches used by the server are allocated as needed. This parameter applies only to the myisam storage engine.
table_cache: the number of caches of open tables in the database. table_cache is related to max_connections. For example, for 200 parallel running joins, the table cache should be at least 200 * N, where N is the maximum number of tables in a join of queries that can be executed. You also need to reserve some additional file descriptors for temporary tables and files.
innodb_buffer_pool_size: the size of the memory buffer that caches InnoDB data and indexes. The higher you set this value, the less disk I/O you need to access the data in the table.

innodb_flush_log_at_trx_commit:0|1|2
innodb_additional_mem_pool_size:1M
innodb_table_locks:0|1
innodb_lock_wait_timeout:
innodb_support_xa: this parameter is used to set whether distributed transactions are supported or not. The default value is ON or 1, indicating support for distributed transactions. If you are sure that distributed transactions are not required in your application, you can turn this parameter off, reducing the number of disk flushes and achieving better InnoDB performance.
innodb_doublewrite:
innodb_log_buffer_size:
innodb_log_file_size:

1. Database design
Try to design your database to take up less disk space.
1). Use smaller integer types whenever possible (mediumint is better than int).
2). Define the field as not null whenever possible, unless this field requires null. (this rule only applies if the field is KEY)
3). If you do not use variable length fields such as varchar, use a fixed size record format such as char. (CHAR is always faster than VARCHR)
4). The primary index of the table should be as short as possible so that each record has a name tag and is more efficient.
5). Create only the indexes you really need. Indexing is good for retrieving records, but bad for saving them quickly. If you are always searching on the combined fields of the table, create indexes on those fields. Part 1 of the index must be the most commonly used field. If you always need to use many fields, you should first copy these fields to make the index more compact.
(this table is only suitable for MYISAM engine, but it is not important for INNODB to save records, because INNODB is transaction based, if you want to save records quickly, especially when importing records in large quantities.)
6). All data must be processed before it is saved to the database.
7). All fields must have default values.
8). In some cases, splitting a frequently scanned table into two is much faster. This is especially true when it is possible to scan a dynamic format table for related records using a smaller static format table.
(the specific performance is: MERGE type of MYISAM table, as well as the partition common to MYISAM and INNODB, see the manual for details)
9). Do not use foreign keys where they are not used.

2. Use of the system
1). Close the connection to MYSQL in time.
2).explain complex SQL statement (this will determine how best to optimize your SELECT statement.)
3). If two relational tables are to be compared, the fields to be compared must be of the same type and length.
4).LIMIT should be used with order by or distinct if possible.
5). If you want to clear the table of all records, it is recommended to use truncate table tablename instead of delete from tablename.
One problem, though, is that truncate does not roll back in transactions. Because she's going to call create table.
(Truncate Table statement first deletes the table and then reconstructs, which is at the file level, so N is much faster.)
Practical examples:
song2 is the INNODB table.

mysql> select count(1) from song2;
+----------+
| count(1) |
+----------+
|   500000 |
+----------+
1 row in set (0.91 sec)
mysql> delete from song2;
Query OK, 500000 rows affected (15.70 sec)
mysql> truncate table song2;
Query OK, 502238 rows affected (0.17 sec)

6). When you can use STORE PROCEDURE or USER FUNCTION (ROUTINE always reduces the overhead on the server side)
7). Multiple record insertion formats are used in one insert statement, and large amounts of data are imported using load data infile, which is much faster than indert alone. ());
(what's more, when inserting a large number of records into the MYISAM table, disable KEYS before setting up KEYS. The specific statement is as follows:
ALTER TABLE TABLE1 DISABLE KEYS;ALTER TABLE TABLE1 ENABLE KEYS;
For INNNODB, set autocommit=0; After: set autocommit=1; It's more efficient.)
8). Regularly use OPTIMIZE TABLE to clean up the pieces.
9). In addition, data of type date should be saved in type unsigned as soon as possible if frequent comparison is needed.

3. System bottleneck
1). Disk search.
Parallel searches, where data is stored on multiple disks, can speed up the search time.
2). Disk read-write (IO)
Data can be read from multiple media in parallel.
3). CPU cycle
The data is stored in main memory, so you have to increase the number of CPU to process the data.
4). Memory bandwidth
When CPU tries to store more data in CPU's cache, memory bandwidth becomes a bottleneck.