How long does it take to query 500W records in mysql?

Through the present and the ancient, the ancient tune is single, and the bullet is exhausted.

MySQL is a relational database management system developed by MySQL AB in Sweden and is now part of Oracle. MySQL is one of the most popular relational database management systems, and when it comes to web applications, MySQL is the best RDBMS (Relational Database Management System) application.

Definitely affected.

Common query reasons are as follows:

1. There is no index or no index is used.

PS: Indexes are used to quickly find those records with specific values, and all MySQL indexes are saved in the form of a B-tree. If there is no index, MySQL must scan all records in the entire table starting from the first record until it finds a record that meets the requirements.

The more records in the table, the more expensive the operation becomes. If an index has already been created on the column that is used as the search criteria, MySQL can quickly get the location of the target record without scanning any records. If a table has 1000 records, finding records by index is at least 100 times faster than sequential scanning records.

Index type: Normal index: This is the most basic index type, and there are no restrictions such as uniqueness.

Unique index: It is basically the same as a normal index, but all index columns can only appear once and remain unique.

Primary key: A primary key is a unique index, but must be specified as"primary key"。

Full-text indexing: MySQL supports full-text indexing and full-text retrieval from the beginning. In MySQL, the index type of a full-text index is fulltext. Full-text indexes can be created on columns of type varchar or text.

2. The small IO throughput forms a bottleneck.

PS: This is from the system layer to analyze MySQL is more IO-intensive. In general, database monitoring is also more concerned about IO.

Monitoring command: $iostat -d -k 1 10

The -d parameter shows the usage status of the device (disk). -kSome columns that use blocks force kilobytes; 1 10 means that the data is refreshed every 1 second, for a total of 10 times.

The official is 10s, but this is already extremely slow, and the background of SQL execution without concurrency is not slow, and the foreground is slow.

This is not good, one is that the table structure is not clear, if it is only a few fields, then set an index, if there are too many fields, you have to consider whether to split the table;

The second is that you don't know how to write your SQL, maybe you can optimize the SQL statement;

The third is whether there is any other logical processing when you query the data, and maybe this part can also be optimized.

So that's all that can be done when you're only giving one title.

1. All keywords are indexed.

2. Don't read the whole table if you don't have anything to do, and only extract the records that need to be used.

3. Extract only the fields that need to be used.

Use the select count(*)from table name; For query, the following is an example of querying the number of data entries named 111 in the database history

1. Enter mysql -u root -p on the command line, and then enter the mysql password to enter mysql

2. Enter use history to enter the history database3. Enter the statement select count(*)from 111; Query the total number of data records in **111.

4. As shown in the figure below, you can see that the total number of data records is 1744364

You can use the count function to view it.

For example, the name of the table is test.

You want to query how many records there are in the table.

select count(*)from test;

If you query by condition, you can use the where condition to select count(*)from test where id=1;

If there are more web pages, consider doing page aggregation and adding long-tail keywords. Hope it works for you.

You can find a consultant to consult about this problem, the page of 500w is no longer an ordinary small **.

If the selectivity of the ware type is high (that is, the number of records in each ware type is relatively small), you can consider indexing on the ware type.

100,000 pieces of data is enough, and the best way to do this is to create an index, which is created with the command "create index index index name on table name(index col name,..

index name: This is the name of the index created, and you can name it yourself.

table name: This is the name of the data table, and yours should be the ware detail.

index col name: for which column of data to create an index, this item is very important, you can write ware type, please make sure this row is not a numeric type, because the data type is faster than the character type query, it is best to set this row to a fixed length, so that the query speed is faster, if the length of the row data can be a little shorter, the shorter it means the faster the query speed.