Introduction to mysql test environment with 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!

Related recommendations: "MySQL Tutorial"

Test Environment

The total number of data is 3 million, occupying about 1G of disk space

Data structure

表1 news [ 文章表 引擎 myisam 字符集 utf-8 ]
-----------------------------------------------------
idint11主键自动增加
cateint11索引
titlevarchar200标题(便于基础搜索做了索引)
contenttext文章正文
dateint11文章发布时间(时间戳形式)

表2 cate [ 文章分类表 引擎 myisam 字符集 utf-8 ]
-----------------------------------------------------
cate_idint11主键自动增加
cate_namevarchar200文章标题

Total number of queries

myIsam 引擎下
select count(*) as total from news
//耗时 0.001秒 极快 
//带上条件
select count(*) as total from news where cate = 1
耗时 0.046秒 可以接受的速度
innodb 引擎下
select count(*) as total from news
//耗时 0.7秒 很慢
select count(*) as total from news where cate = 1
耗时 0.7秒 很慢

Why are the query speeds of the two engines so different? ?

InnoDB does not save the specific number of rows in the table. That is to say, when executing select count(*) from table, InnoDB has to scan the entire table to calculate how many rows there are.

MyISAM simply reads the number of saved rows.

Note that when the count(*) statement contains the where condition, the operations of the two tables are somewhat different. InnoDB type tables use count(*) or count (primary key), plus the where col condition. The col column is a column with a unique constraint index other than the primary key of the table. This way the query speed will be very fast. That is to avoid a full table scan.

Summary

mysql uses count(*) to query the total number of data with 3 million pieces of data (myisam engine) and contains conditions (indexes are set correctly) and the running time is normal . For data that is frequently read we recommend using the myIsam engine.

The above is the detailed content of Introduction to mysql test environment with millions of data. For more information, please follow other related articles on the PHP Chinese website!