Mysql performance optimized 10 key skills and practices to rationalize database table structure and index
The design of the database table structure is the cornerstone of performance optimization. First, the model database design principles should be followed to avoid data redundancy and updating anomalies. Selecting the appropriate data type is essential to optimize performance, for example, using int instead of varchar values and using datetime or timestamp storage time. Creates an index for columns that frequently appear in the where sentences, join conditions, order by and grup by sentences. However, the index is not as good as it is, and needs to balance the cost of query performance with insert, update, delete. For string columns, you can consider using prefix indices to reduce index size. Regularly analyse slow query logs, identify missing indexes, and use explain commands to analyze query implementation plans to ensure that the index is used correctly。
Optimizing sql query statement
Inefficient sql statements are the main cause of performance problems. Queries should be prepared in such a way as to avoid the use of select and to specify the columns required to reduce the costs of network transmission and data resolution. Careful use of sub-inquiries and, where possible, rewrite using join, which is usually optimized by the database engine. Avoids a function operation or expression calculation of a field in a where sub-statement, which causes the index to lapse. For example, `where year (create time) = 2023 `unable to use effectively the index on `create time'should read `where create time > = `2023-01 ' and create time
Effective use of indexing policy
In addition to creating the necessary indices, effective indexing strategies are needed. When you understand and use the leftmost prefix principle to create a composite index, put the high-profile column in front. For multi-column queries, a well-designed composite index is usually better than a single-column index. Use the over-cover index, which contains all fields required for queries, so that the database engine can obtain data directly from the index without returning the table and greatly enhances the speed of queries. Periodically check and remove unused or redundant indexes that reduce the performance of writing operations and occupy additional space. Use the unique index to ensure data uniqueness, which also enhances query efficiency。
Configure and optimize mysql server parameters
Mysql's default configuration is usually for a generic scenario and needs to be adjusted to actual hardware resources, data volume and access patterns. Key buffer zones, such as `innodb buffer pool size` (innodb buffer pool size), should be set at 50-80 per cent of available memory to slow the memory of frequently visited data. Resize `innodb log file size ' (resize log files) to reduce disk i/o. Rationally configure parameters related to the number of connections, such as `max conventions ' , to avoid excessive connections leading to resource depletion. Adjustment of relevant i/o parameters such as `innodb io capacity ' according to the performance of storage equipment (e. G. Hdd or ssd). Monitor the status of the database, using commands such as `show stattus ' and `show variables ' to aid the optimization。
Select and optimize storage engines
Mysql supports multiple storage engines, most commonly innodb and myisam. Innodb is the default storage engine, which supports services, line locks and external key restraints and applies to most scenarios that require high co-activity and service safety. Myisam may have a faster reading speed in a read- or write-only scenario, but does not support service and line locks. Appropriate storage engines should be selected based on the characteristics of the application (e. G. Reading and writing ratios, need for service support). For innodb, parameters such as surface space management and log refreshing strategies could be optimized to further enhance performance。
Perform query cache optimization
The query cache can store the select query statement and its result set, and when you encounter exactly the same query, you can return the result of the cache, avoiding the cost of understanding and executing the query. However, in mysql 8. 0, the query cache has been removed because, in a high complication environment, the cost of the cache failure may be greater than its proceeds. For the application of mysql versions (e. G. 5. 7) that still support queries, query caches can be enabled and adjusted by setting parameters such as `query cache size ' . For tables that read and write less frequently and that are not frequently updated, the query cache can result in significant performance improvements. It should be noted, however, that any modification to the table would invalidate the relevant cache。
Database partitions and tabulations
When the amount of data in the table is very large, the ability to query and maintain is significantly reduced. The use of zoning or tabulation strategies could be considered at this time. The partitioning is the distribution of data from a table into different physical sub-tables according to certain rules (e. G. Scope, list, hash), but remains a logical table for application. This helps to improve the efficiency of queries, particularly when they involve scope queries, where the optimizer can scan only the relevant partitions. The tabulation splits data levels into physical tables with identical structures, requiring routeing and management at the application level. Divisions and tabulations can effectively reduce the amount of data in individual tables, increase the speed of queries and facilitate the archiving of old data。
Optimizing database architecture and separation from reading and writing
A single database server may be a bottleneck for high-small and high-availability applications. The reading and writing separation can be achieved using the main reproduction structure. Writing operations (insert, update, delete) are concentrated on the main library, while reading operations (seleect) are dispersed to one or more libraries. This not only provides for the sharing of loads of the main library, but also increases the availability of the system, from which one can be upgraded as a new repository when the main library fails. Automatic routing for reading and writing at the application level or through intermediates (e. G. Mysql router, proxysql). In addition, vertical splits (distribution of tables of different modules to examples of different databases) or horizontal splits (separate libraries) could be considered to further spread the pressure。
Regular maintenance and monitoring of databases
Regular database maintenance is essential to ensure long-term high performance. The `optimize table ' commands are used to organize the fragments, particularly for frequent updates, which allow for the recovery of space and increase the efficiency of access. `analize tabele ' is regularly implemented to update statistical information on tables and help optimizers generate more accurate implementation plans. A monitoring system is in place to keep track of key indicators in the database, such as qps (surveys per second), tps (services per second), connections, slow queries, buffer pool hit rate, etc. Monitoring allows for the timely detection of performance bottlenecks and potential problems, and prevents them. Sets a security alarm for slow-search and error logs。
Application layer optimization and cache policy
The performance optimization of the database is not just a dba task; the design of the application layer is equally important. Avoiding n+1 queries in applications, i. E. Obtaining a list by a query and recycle searching for details of each item in the list, using join or batch queries instead. The introduction of a cache layer is an effective means of reducing database pressure. You can use memory keys such as redis, memcached to store cache heat point data, session information or complex query results. Reasonable setting of cache expiration times and phase-out strategies. Through application layer caches, a large number of requests for reading can be blocked prior to the database, significantly reducing the database load and increasing the overall response speed of the system。
