Sort out some mysql-optimized items

I have sorted out some mysql-optimized items and collected them online for my friends to learn.

I have sorted out some mysql-optimized items and collected them online for my friends to learn.

1. query help? Contents
2. Use the merged hash value to separate BLOB or TEXT
3. Number of points used for currency (decimal or numberic)
4. SQL _mode?
5. order by rand () limit 1000;
6. Optimize show status like 'com' Com_select Com_insert and so on to check whether there are many inserts or queries
7. the value of Handler_read_key is very high. This value indicates the number of times a row is read by the index value. A very low value indicates that the performance improvement caused by increasing the index is not high because the index is not frequently used. the high value of Handler_read_rnd_next means that the query operation is inefficient and the index remediation should be established. This value indicates the number of requests read from the next row in the data file. If you are performing a large number of table scans, this value is high. It usually indicates that the table index is incorrect or the index is not used for the written query.
8. Regular analysis TABLE ANALYZE TABLE CHECK TABLE CHECKSUM TABLE
9. OPTIMIZE TABLE
10. Import big data: Myisam alter table tblname disable keys loading 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 that affect Mysql performance:
Key_buffer_size: Description: The key cache (variable key_buffer_size) is shared by all threads. Other caches used by the server are allocated as needed. This parameter is only applicable to the myisam storage engine.
Table_cache: Number of opened table caches in the database. Table_cache is related to max_connections. For example, for 200 concurrent connections, the table should be slowed down by at least 200 * N. Here, N is the maximum number of tables in a join where the query can be executed. You also need to retain some additional file descriptors for temporary tables and files.
Innodb_buffer_pool_size: size of the memory buffer for caching InnoDB data and indexes. The higher you set this value, the less disk I/O required for accessing table data.

Innodb_flush_log_at_trx_commit: 0 | 1 | 2
Innodb_additional_mem_pool_size: 1 M
Innodb_table_locks: 0 | 1
Innodb_lock_wait_timeout:
Innodb_support_xa: Specifies whether to support distributed transactions. The default value is ON or 1, indicating that distributed transactions are supported. If you confirm that the application does not need to use distributed transactions, you can disable this parameter to reduce the number of disk refreshes and achieve better InnoDB performance.
Innodb_doublewrite:
Innodb_log_buffer_size:
Innodb_log_file_size:

1. Database Design
Try to occupy less disk space for database design.
1). Use smaller integer types as much as possible. (mediumint is more suitable than int ).
2). Try to define the field as not null unless this field needs to be null. (this rule is only applicable when the field is KEY)
3). If variable length fields are not used, such as varchar, 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. In this case, each record has a name Mark and is more efficient.
5). Create only the required indexes. Indexing is conducive to record retrieval, but is not conducive to quick record storage. If you always need to search on the combined fields of the table, create an index on these fields. The first part of the index must be the most commonly used field. If you need to use many fields, you should first copy these fields to make the index more compressed.
(This table is only applicable to the MYISAM engine. For INNODB, it does not matter much when saving records, because INNODB is based on transactions. If you want to save records quickly, especially when a large number of import records are used)
6). All data must be processed before being saved to the database.
7). All fields must have default values.
8). In some cases, splitting a frequently scanned table into two tables is much faster. When scanning a dynamic table to obtain relevant records, it may use a smaller static table.
(For details, see the manual)
9). Do not use the foreign key whenever possible.

2. usage of the system
1). Close the connection to MYSQL in time.
2) explain complex SQL statements. (In this way, you can determine how to optimize your SELECT statement)
3). If two joined tables are to be compared, the type and length of the fields to be compared must be the same (INDEX is created when data is huge)
4) the LIMIT statement should be followed by order by or distinct as much as possible. This can avoid a full table scan.
5). If you want to clear all records of a table, we recommend that you use truncate table tablename instead of delete from tablename.
However, there is a problem that truncate will not roll back in transaction processing. Because she wants to call the create table statement.
(The Truncate Table statement first deletes the Table and then recreates it. This is a file-level statement, so it is naturally faster than N)
Test example:
Song2 is an INNODB table.
The Code is as follows:
Mysql> select count (1) from song2;
+ ---------- +
| Count (1) |
+ ---------- +
| 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 the store procedure or user function can be used. (ROUTINE always reduces the overhead on the server)
7 ). insert multiple records in One insert statement. in addition, using load data infile to import a large amount of data is much faster than the pure indert. (in MYSQL, insert into tableq values (),(),... ();)
(In addition, when inserting a large number of records in the MYISAM table, disable the function before creating the KEYS. The specific statement is as follows:
Alter table TABLE1 disable keys; alter table TABLE1 enable keys;
For INNNODB tables, set autocommit = 0 before insertion, and set autocommit = 1 after insertion. This is more efficient .)
8). optimize table is often used to sort fragments.
9). Data of the date type should be saved as quickly as possible in the unsigned int type if compared frequently.

3. system bottlenecks
1). Disk search.
In parallel search, data is stored separately in multiple disks to accelerate the search time.
2). disk read/write (IO)
Data can be read concurrently from multiple media.
3). CPU cycle
Data is stored in the primary memory. In this way, you need to increase the number of CPUs to process the data.
4). memory bandwidth
When the CPU needs to store more data in the CPU cache, the memory bandwidth becomes a bottleneck.