# mysql from zero to fluent: complete resolution of the database optimizes the ten core strategies ## # introduction
Databases are the core components of modern application systems, and their performance directly affects business response speed, user experience and system scalability. As one of the most popular open-source databases, mysql plays a crucial role in various applications. However, as data volume increases and operational complexity increases, the issue of database performance becomes more prominent. The mastery of mysql technology has become an essential skill for developers and dba. This will help you build high-performance database systems。
I. Index optimization strategy
The index is the cornerstone for the optimization of mysql queries. The correct index design can significantly increase the speed of the query, while the wrong index slows down and takes up additional space. First, it is essential to understand the b+tree index principle, which is the most commonly used index structure for mysql. Indexes should be created for the where sub-words, join conditions and high selectivity columns, avoiding indexing on low selectivity columns (e. G. Gender). The composite index is subject to the leftmost prefix principle and the order of the columns is reasonable. The explain analysis and query implementation plan is regularly used to check the use of the index. Avoid over-indexing because each index increases the cost of insert, update, delete. For text searches, the full text index is considered instead of the like fuzzy query。
Ii. Query statement optimization
Inefficient sql statements are the main source of performance problems. Optimizing the query first to avoid using select and only to get the necessary columns. Note the performance of the join operation to ensure that the on condition field is indexed and of a type matching. Rational use of sub-inquiries, rewritten by join as necessary. Avoids a function operation for fields in the where sub-statement, which results in the index being invalidated. Keep an eye on all table scans in large table queries and optimize them for index scanning. When using the limit page break, the depth page break can be changed to the cursor-based page break. Use union all to replace union unless it needs to be heavy. Regularly review slow query logs to find sql statements that need to be optimized。
Database schema design optimization
Good database design is the basis for high performance. Select the appropriate data type, e. G. Use int instead of varchar to store numbers and datetime to store time stamp. Data redundancies are eliminated in accordance with the normative principle, but cross-model design needs to be balanced against query performance. Sets the right primary key for the table, and recommends the use of a self-enhancement id or non-operational proxy key. Avoid excessive varchar fields, especially as part of the index. Separates large fields (e. G. Text/blob) from separate tables to reduce the size of the master table. Use enum or set type to replace string constants to improve query and storage efficiency。
Iv. Business and lock optimization
Rational use of services and locks is essential to co-optability. Keep the services short and avoid lengthy operations in the service. The selection of the appropriate service segregation level according to business needs usually allows rad committed to have a better co-activity than reeatable read. Note the choice of row locks and table locks, and the innodb line locks are more suitable for high-level complication. Avoiding deadlocks and ensuring that multiple matters have access to resources in the same order. For the updating of hotspot data, consideration is given to using an optimistic lock or queue mechanism to mitigate competition. Use select... For update with caution, clearly lock the range and time。
V. Aligning server parameters
Mysql parameter configuration directly affects performance. Key buffer zones include innodb buffer pol size (set at 70-80 per cent of available memory), key buffer size (myisam engine) and query cache size (removed in mysql 8. 0). Adjusts the associated parameters of a connection, such as max conventions, to avoid being too large or too small based on actual simultaneous demand settings. Configures a reasonable slow query threshold (long query time) and opens the slow query log. For writing-intensive applications, adjust innodb log file size and innodb flush log at trx committee. Check regularly the status variables, such as threads conected, innodb row lock time, to see how the server works。
Vi. Storage engine selection and optimization
Mysql supports multiple storage engines with appropriate scenarios. Innodb is the default selection, which supports services, line locks and external keys and is suitable for most oltp scenarios. Myisam applies to situations that read and write less and do not require services, but the table lock is limited and distributed. The memoory engine stores data in memory, suitable for temporary tables and high-frequency read-written cache data. Archiving engine compression is high and suitable for historical data storage. Select the appropriate storage engine according to the data characteristics and access mode and optimize it for a specific engine, such as the innodb page size, myisam key cache, etc。
Vii. Table divisional strategies
For large tables, partitions can significantly improve query performance and management efficiency. Time series data are suitable by range partition, such as monthly log tables. Listed partitions are divided according to discrete values, e. G. Regional divisions. Hash division data distributed evenly to reduce hot spots. Key partitions are similar to hash, but accept only integers or returns integers. The partition tailoring allows queries to access only the relevant partitions and reduces data scanning. However, zoning is not universal and there is a need to weigh the selection of partition keys, the number of partitions and maintenance costs. To avoid over-division, it is generally recommended that the amount of data for individual divisions be no less than 1 million rows。
Viii. Cache strategy optimization
Multilevel caches are an effective means of enhancing mysql performance. Mysql has a cache of queries (pre-$8. 0), an innodb buffer pool, etc. Application layer caches such as redis and memcached can reduce database pressure. Rationalally set the cache failure policy to balance real-time data against performance. For hotspot data, application-level local caches may be considered. Use reading and writing separation to direct the reading request from the library or cache. Take care of the problem of cache penetrators, piercing and avalanches, using solutions such as blonde filters and mutually repulsive locks. Regular analysis of clc rates and adjustment of cbm strategies。
Ix. High availability and extended architecture
As operations grow, the single mysql example may not meet demand. Mainly from reproduction is the basis for achieving reading expansion, separating loads by reading and writing. Use mysql router or an automatic route query. The partition tables address single capacity and performance bottlenecks and can be used in vertical compartments (by business module), horizontal tabulations (by range of data or by hash). Consider using mgr (mysql group regulation) for high-availability and automatic downtime transfer. Cloud database services, such as rds, provide automated backup, monitoring and extension functions to reduce transportation costs. The architecture needs to be designed taking into account data consistency, failure recovery and migration options。
Monitoring and maintenance strategy
Continued monitoring and maintenance are key to ensuring the long-term stability of mysql. Use performance schema and sys schema for in-depth analysis of database performance. Establish monitoring and alerts for key indicators, such as qps, tps, connections, slow search rates, etc. Health checks are carried out regularly, including index efficiency, table debris, lock waiting, etc. Develop a backup policy, combining full and incremental backup, and regularly test recovery processes. Full testing of the version before it is upgraded to avoid compatibility problems. Establish performance baselines to identify potential problems against historical data. Automation of routine maintenance tasks, such as debris collation, statistical updating, etc。
Summary
Mysql database optimization is a system engineering exercise that requires a combination of dimensions such as indexing, querying, design, configuration, architecture, etc. The ten core strategies cover optimization techniques from foundation to advanced, but application needs to be applied flexibly according to the specific business landscape. The fundamental objective of optimization is to balance reading and writing performance, resource utilization and data consistency rather than simply seeking to maximize an indicator. Continuous learning of the new mysql features and continuous refinement of optimization strategies in conjunction with surveillance data will enable the construction of a truly efficient and reliable database system. Bearing in mind that the best is often the in-depth understanding of business logic and data rather than mere technical means。
