In-depth analysis of mysql under millions of data

Mysql is widely used as a very excellent free database. Usually, the data of the projects we develop rarely exceed one million. Recently, I have spent a lot of time on in-depth research on the optimization of mysql in the case of millions of data. I encountered many problems and solved them, and I would like to share them with you. Your valuable opinions are welcome!

1. Introduction to million-level data msql test environment

mysql uses count(*) to query the total number of data in the case of 3 million pieces of data (myisam engine). The condition (indexing correctly set) runs fine. For data that is frequently read, we recommend using the myIsam engine...

For details, click: "Millions of data msql test environment introduction"

二, MySQL paging problem under millions of data

We often use paging during the development process. The core technology is to use limit to read data. During the test process of using limit for paging, the following data was obtained:

select * from news order by id desc limit 0,10
耗时0.003秒
select * from news order by id desc limit 10000,10
耗时0.058秒
select * from news order by id desc limit 100000,10
耗时0.575秒
select * from news order by id desc limit 1000000,10
耗时7.28秒

We were surprised to find that the larger the paging starting point of MySQL is when the data volume is large, the slower the query speed. The query speed for 1 million items has already been It takes 7 seconds. This is a value that we cannot accept...

For details, click: "MySQL paging problem under millions of data"

3. Under millions of data Precautions for mysql conditional query and paging query

Continuing from the previous section "Mysql paging problem with millions of data", we add the query conditions:

select id from news
where cate = 1
order by id desc
limit 500000 ,10
查询时间 20 秒

What a terrifying speed! ! Use the knowledge in the first section "Introduction to MySQL Data Test Environment for Millions of Data" to optimize...

Click for details: "Notes on mysql conditional query and paging query under millions of data"

4. The difference between mysql storage engine myIsam and innodb

MySQL has a variety of storage engines, of which MyISAM and InnoDB are two commonly used ones. Here are some basic concepts about these two engines (not an in-depth introduction).

MyISAM is the default storage engine for MySQL. It is based on the traditional ISAM type and supports full-text search, but it is not transaction-safe and does not support foreign keys. Each MyISAM table is stored in three files: the frm file stores the table definition; the data file is MYD (MYData); the index file is MYI (MYIndex)...

Click for details: "mysql storage The difference between the engine myIsam and innodb》

5. Some experiences in MySQL performance optimization

Most MySQL servers have query caching enabled. This is one of the most effective ways to improve performance, and it's handled by the MySQL database engine. When many identical queries are executed multiple times, these query results will be placed in a cache, so that subsequent identical queries will not need to operate the table but directly access the cached results...

Click for details: "Some experiences in MySQL performance optimization"