MySQL: How to optimize MySQL performance?

[Chen Xi to work hard] : hello, I am Chen Xi, very glad you can read, nickname is to hope that they can continue to improve, toward excellent programmers! The blog comes from the summary of the problems encountered in the project and programming, and occasionally THERE will be reading and sharing. I will update the Summary of relevant knowledge points such as Java front end, background, database and project case successively. Thank you for your reading and attention. We quarrymen, with the heart of the cathedral, may we go to our respective loves…

The usual way to optimize

1. Maximum connection number optimization

• 1. Query the maximum number of connections

• Method 1: SQL command query

show variables like '%max_connections%';
Copy the code

• Method 2: Can also be viewed by DBMS (Navicat, Workbeanch)

• 2. Change the maximum number of connections

• Method 1: MODIFY SQL instructions

//This modification will reset after restarting the databaseset global max_connections=300 
Copy the code

• Method 2: Modify the my.ini file (permanently)

• Find the configuration file in your drive and modify it

Enable query caching

Special note: Query caching has been deprecated since MySQL 5.7.20 and removed in MySQL 8.0.

• Briefly describe the operations in MySQL5.0

Why does MySQL8.0 remove the query cache function directly?

• One way to say this is that query caching is not recommended,Because query caching often does more harm than good.The query cache invalidates very frequently,Whenever an update is made to a table, all query caches on that table are cleared.So it’s very likely that you struggled to save the results, and an update wiped them out before you even used them. For databases under pressure to update, the hit ratio of the query cache can be very low. Unless your business has a static table that is updated over a long period of time, such as a system configuration table, queries from that table are suitable for query caching.

Third, engine optimization

• Run the following SQL to query the storage engine in mysql

SHOW ENGINES
Copy the code

• InnoDB storage engine
• InnoDB is the preferred engine for transactional databases. InnoDB is currently the default transactional engine for MYSQL and is the most important and widely used storage engine. Support for transaction security tables (ACID), row locking and foreign keys.
• The main features of InnoDB are:
• InnoDB provides MySQL with an ACID-compliant storage engine with commit, rollback and crash recovery capabilities.
• ②InnoDB is designed for maximum performance in handling large amounts of data. Its CPU efficiency is probably unmatched by any other disk-based relational database engine lock
• ③InnoDB storage engine is fully integrated with MySQL server, InnoDB storage engine maintains its own buffer pool for caching data and indexes in main memory. InnoDB keeps its tables and indexes in a logical table space that can contain several files (or raw disk files). This is different from MyISAM tables where, for example, each table is stored in separate files. InnoDB tables can be any size, even on operating systems where file sizes are limited to 2GB
• (4) InnoDB supports foreign key integrity constraints. When storing data in a table, each table is stored in the order of the primary key. If the primary key is not specified in the table definition, InnoDB will generate a 6-byte ROWID for each row and use it as the primary key
• InnoDB is used in many large database sites that require high performance
• Application scenarios:Because of its support for transaction processing, support for foreign keys, crash repair capabilities and concurrency control. If you need high transaction integrity (such as banks) and concurrency control (such as ticketing), InnoDB has great advantages. If you need a database with frequent updates and deletes, you can also choose InnoDB because it supports commit and rollback transactions.

• MyISAM storage engine

• MyISAM is based on and extends the ISAM storage engine. It is one of the most commonly used storage engines in Web, data warehousing, and other application environments. MyISAM has high insert and query speed, but does not support things and foreign keys.

• MyISAM features:

• 1, support large files (up to 63 bit file length)

• Dynamically sized rows produce less fragmentation when delete is mixed with update and insert operations.

• 3, the maximum number of MyISAM indexes is 64, and the maximum number of columns per index is 16

• 4. The maximum key length is 1000 bytes, which can also be changed by compilation. For keys longer than 250 bytes, a key larger than 1024 bytes will be used

• BLOB and TEXT columns can be indexed

• 6. NULL is allowed in the column of the index. This value is 0~1 bytes per key

• 7. All numeric key values are stored in high byte priority to allow a higher index compression

• Mysql > alter table AUTO_INCREMENT Update InnoDB AUTO_INCREMENT update InnoDB AUTO_INCREMENT update InnoDB AUTO_INCREMENT update InnoDB AUTO_INCREMENT update InnoDB AUTO_INCREMENT update InnoDB AUTO_INCREMENT

• 9. Data files and index files can be placed in different directories

• 10. Each character column can have a different character set

• Tables with VARCHAR can have fixed or dynamic record lengths

• 12. VARCHAR and CHAR columns can be up to 64KB

• Scenario: If the table is mainly used for inserting new records and reading records, then choosing MyISAM can be efficient for processing.

• View and modify the engine
• ① Check the engine

show create tableTablename;SHOW TABLE STATUS FROM sites WHERE NAME='site';  
SHOW TABLE STATUS FROM db_name WHERE NAME='table_name';  
Copy the code

• ② Modify the engine list

alter table table_name engine=innodb;  
alter table table_name engine=myisam;  
Copy the code

Supplementary knowledge

• Row locks
• 1.Read lock optimistic lock: Allows other transactions to query data, but not modify it

select column from table where conditions lock inShare mode;Copy the code

• 2.Write lock pessimistic lock: Does not allow other transactions to query and modify data

select math from zje where math >60 forThe update;Copy the code

• Summary:
• A. Row locks are only available in InnoDB
• B. Commit the transaction to release the lock
• C. The lock must be enabled within a transaction

Engine optimization summary

• ① Multiple tables in a database can use different engines to meet various performance and practical requirements. Using the right storage engine will improve the performance of the entire database
• ②InnoDB provides commit, rollback, crash recovery and concurrency control capabilities, which are suitable for tables with high frequency of data update operations
• ③MyISAM engine can provide high query efficiency and is suitable for data tables with frequent query operations

Index of four.

• Here is easy to query can set the index, so that the query efficiency becomes higher

• Aggregated indexes match from left to right. That is, the ID must be matched before the name query can be matched.
• Full text search methods:
• SELECT * FROM article WHERE MATCH(title, content) AGAINST(' query string ')
• Full-text indexes are only suitable for MyISAM engine data tables. And it can only search in English

Five.SQL statement optimization

• SQL optimization is the focus of query optimization, query optimization is the focus of index building.

So query optimization is mainly about avoiding queries that cause index invalidation.

• Avoid null on index columns.
• ② Do not perform arithmetic operations on indexed columns. : select age+1 from user
• ③ Avoid implementation! Operations such as = or <>, is NULL or is not NULL, and in May result in full table traversal.
• ④ Fuzzy query can only use the right %.
• ⑤ Use as few parentheses as possible after the WHERE statement, or do not appear parentheses nested parentheses.

summary

1Don't have too many tables200Zhang)2The number of columns in a single table cannot be too high40Column)3),32Bit system maximum single table4G,64There is no limit to the bits.Copy the code

Thank you so much for reading this, if this article has been helpful to you, please leave a like 👍 follow ❤️ share 👥 comment 💬 Thanks!!