When it comes to Mysql optimization, we must clarify three points. First, not all optimizations are effective. Second, the stability of the system and the availability of business logic are often more important than performance optimization. Third, optimize the cooperation of various departments.

Programmers usually optimize sql statements and indexes

Optimized process

sql statement and index optimization – > data table optimization – > system configuration optimization – > hardware improvement optimization

The performance improvement is getting lower and lower in order, and the cost is getting higher and higher.

There are two types of optimization

• Emergency optimization
• Conventional optimization

First, emergency optimization (view the currently blocked SQL sessions):

1. show processlist; (view the status of each session connected to the database)

# Show the first 100 threads
show processlist;



# If you want to list all threads, use
show full processlist; 

Result view

Id       #ID identification; Use when you want to kill a statement
User     #Currently connected user
Host     #Displays which port of which ip the connection originated from
db       #Database name used
Command  #Connection status， Generally sleep; Query; Connect;
Time     #Connection duration， The unit is seconds
State    #Displays the status of the current sql statement
Info     #Show this sql statement

2. Explain (analyze query plan) , show index from table

3. Judge the index problem (yes or no, unreasonable) or the business logic and Sql statement itself through the execution plan

4. show status like ‘%lock%’; # Query lock status

5. kill id; # Kill the problematic connection Id is show processonlist; Id of

Next, perform regular tuning (by viewing slow logs, optimize for sql with poor performance)

1. View the slowlog, analyze the slowlog, and analyze the slow query statements.             

2. Check all slow statements one by one according to a certain priority.             

3. Analyze the top sql, debug explain, and check the execution time of the statement.             

4. Adjust the index or the statement itself

Mysql database storage engine

• InnoDB storage engine
• MyISAM storage engine
• etc.

What is InnoDB

What is MySIAM

The difference between the two:

        1,MySIAM Transaction not supported, not secure, but InnoDB Is thread safe
        2,MySIAM The granularity of locks is table, not table InnoDB Row level locking is supported, so InnoDB Faster when multithreading
        3,MySIAM Foreign keys are not supported, InnoDB Support for foreign keys
        4,MySIAM Relatively simple, efficiency higher than InnoDB，Small applications can be considered MySIAM

InnoDB Storage can be committed, rolled back and crash recovered, but the efficiency of write operation will be lower, and it will occupy more resources and memory to retain data and indexes
    1,provide ACID(Atomicity, consistency, isolation, persistence)，Achieve standard database isolation levels
    2,use count(*)The entire table will be scanned to calculate how many rows
    3,With row locks, the granularity is smaller, the write operation will not lock all tables, the multithreading efficiency is higher, and the performance is better even if there are updates and inserts
    4,The surface cleaning is slow(Processing data one by one)，First write the operation to the transaction log and then delete it, so it's best to clear the table directly drop，Create a new table

Storage optimization

         1,Disable indexing: when inserting records,Mysql Each record will be indexed. If the amount of data is large, the speed will be seriously affected. After the data is inserted, you can open the index.
         2,Disable uniqueness check: when inserting records, if there is uniqueness check, if the amount of data is large, it will seriously affect the speed. When a large amount of data is inserted, just turn on the uniqueness check.
         3,Disable foreign key checking: the same as disabling uniqueness checking.
         4,Batch insert data: one Sql Insert multiple records.
         5,Prohibit automatic submission: turn off the automatic submission of transactions, and turn on the automatic submission of transactions after the data insertion is completed. SET autocommit 
= 0;    0 Yes to disable automatic submission, 1 to enable automatic submission

Index optimization (why can indexes be optimized?)

Type of index

 Transaction and foreign key are not supported. This storage engine can be selected for query and insertion
        The table will be stored in three files
            1) frm: Storage table definition (table structure and other information)  
            2) MYD(MYData)，Store data
            3) MYI(MYIndex)，Storage index  
        Provide repair tools, use CHECK TABEL To test the health of the table, you can use REPAIR TABLE To repair
        Support full-text indexing( Mysql5.6 Only before MySIAM (supported)

What is index invalidation?

Index invalidation is: when a query is needed, the established index is not used, resulting in full table scanning, resulting in low efficiency!

How to solve it?

        1,Query usage like For example, query the name name = '%aaa'It won't fail, but name = 'aaa%'，Will cause the index to fail
        2,Query usage or，If necessary or，Then let or All fields above are indexed
        3, String type data lookup without'',Will cause the index to fail
        4, Queries are null value

Table structure optimization:

         1, Try to define the field as non empty. If there is an empty value, it is extremely prone to full table scanning with index failure in the future.
         2,Use small data types, such as
         3,Rational use of redundant fields
         4,Don't have too many table fields

Table split:

• Vertical splitting (dividing the fields in the table into multiple tables) requires the use of redundant fields (using join), which makes it difficult to control transactions and query.
• Horizontal splitting (splitting the data of a large table into multiple data with the same table structure) is a common database and table splitting. When the amount of data is large, the maintenance time is long.

Table partition:

Special instructions: The brilliance of solving problems hides the pain of knocking bugs. All things enter samsara, and no one can escape! The above articles are my actual operation and written notes. There will be no full-text embezzlement of other people's articles! Please don't embezzle directly!