1. Optimize indexes, SQL statements, analysisSlow query;
2. When designing tables, strictly follow the database design paradigm to design the database;
3. We can also use our The business architecture is cached, static and distributed;
4. Instead of full-text indexing, use Xunsearch,ESOr index on the cloud server;
5. If the efficiency is still not good enough, you can use the master-slave method to separate data reading and writing;
6. You can add memcached cache to put frequently accessed but infrequently changed data into the memcached cache server, which can save disk I/O;
7. Hardware can also be optimized. At the hardware level, we can use better hard drives (solid-state drives) and some disk array technologies (raid0, raid1, raid5)
- raid0: the simplest (two pieces of hardware add 100G 100G=200G)
- Raid1: Mirror volume, copy the same data into two copies. It can be read from A/B immediately, which is more efficient. Even if the hard disk is damaged, a piece of data will not be lost;
- raid5: 3 hard drives, if one is broken, the other two can still work.
8. If it is still slow, don’t split the table first. You can use MySQL’s own table partitioning technology to divide the data into different files. This will allow the disk to read more quickly. It is more efficient;
9. You can divide the table vertically and put the data that is not frequently read into another table (saving disk I/O);
10. The amount of data is extremely large, and it will be difficult for us to optimize it. In this case, we can use database middleware to divide the data into databases, tables, and machines. (Principle: data routing);
11. In addition, some faster storage methods can also be used, such as NoSQL to store some data that we need to access frequently (after taking out the database, Then take out some other data from NoSQL);
12. In addition, there are some table engine options, and parameter optimization is also related. Tips are all ways to optimize MySQL;
Slow query: refers to the SQL statement query that exceeds the specified time. The method to analyze the performance of MySQL statement query is besides making explain output The execution plan also allows MySQL to record queries that exceed the specified time.
Xunsearch: A free and open source professional full-text search solution designed to help general developers quickly and easily build their own full-text search engines for existing massive data. Full-text search can help reduce the search load on the server and greatly improve search speed and user experience.
ElasticSearch: A search server based on Lucene. It provides a distributed multi-user capable full-text search engine based on a RESTful web interface.
Table Engine | Advantages | Disadvantages |
---|---|---|
The reading speed is fast and does not occupy a lot of memory and storage resources | Does not support transactions and cannot be fault-tolerant; if the hard disk crashes, the data files cannot be restored (regular backup) | |
ISAM extended format and default data engine provide a large number of index and field management functions that are not available in ISAM. MYISAM uses table locks | Does not support transactions, does not support foreign keys | |
Supports transactions, supports foreign keys | Data reading is relatively slow Slow | |
Allows temporary tables that only reside in memory, so it is faster than ISAM and MYISAM | The data managed is not Stable, if the data is not saved before shutting down, all data will be lost (delete the table after the table is used up); it is very useful when you need select expressions to select and manipulate data |
Features | |
---|---|
has atomicity, that is, the information in | column cannot be further divided [unique column] |
Based on the first normal form, each instance or | row of the data table must be uniquely distinguished (a primary key is built on each row) [row unique] |
Based on the second normal form, a database that does not contain non-primary key columns that are already in other tables |
The above is the detailed content of Learn how MySQL is optimized. For more information, please follow other related articles on the PHP Chinese website!