MySQL Note dot

MySQL optimization:

Each InnoDB table must have a primary key
Limit the number of indexes on a table and avoid establishing duplicate and redundant indexes
Careful selection of the order of compound index key values

Prioritize the smallest data types that meet storage needs
n in varchar (n) represents the number of characters, not the number of bytes
Use UTF8 to store Chinese characters varchar (255) = 765 bytes
Too much of the length will consume more memory
Avoid using TEXT,BLOB data types

Avoid using joins to correlate too many tables
One table per join consumes a portion of memory (join_buffer_size)
Generates temporary table operations that affect query efficiency
MySQL allows a maximum of 61 tables, with no more than 5 recommendations

Reduce the number of interactions with the database
Database is more suitable for batch operations
Combine multiple identical operations together to improve processing efficiency

Use in instead of or
In does not exceed 500 values
In operations can effectively use the index

Do not use ORDER by rand () to randomly sort
All eligible data is loaded into memory for sorting
Consumes a lot of CPU and IO and memory resources
It is recommended to get a random value in the program and then get the data from the database

function conversions and calculations for columns are prohibited in the WHERE clause
function conversions or calculations on columns can result in an unusable index

Use UNION ALL instead of union when there is clearly no duplicate value
Union will put all the data into the temporary table and then redo the operation
UNION all will no longer redo the result set
Super privilege can only be left to the DBA to handle the problem

For program connection database account, follow the principle of least privilege

Split complex large SQL into multiple small SQL
MySQL a SQL can only be calculated using one CPU
SQL split can improve processing efficiency by parallel execution

Batch write operations of more than 1 million lines, to be operated multiple times in batches
High-volume operations can cause severe master-slave delays
The Binlog log is a row format that generates a large number of logs
Avoid large transaction operations

Prohibit granting super privileges to accounts used by programs
Also allows 1 users with super privileges to connect when the maximum number of connections is reached

MySQL Note dot