Optimizing query speed of MySQL with tens of millions of data using indexes

1. The role of index

Generally speaking, an index is equivalent to the table of contents of a book. When we query based on conditions, if there is no index, we need to scan the entire table. This is OK when the amount of data is small, but once the amount of data exceeds one million or even tens of millions, it often takes tens of seconds or even longer to execute a query SQL. More than 5 seconds is already unbearable.

If the problem can be solved by software, it will not be solved by hardware. After all, hardware code enhancement is expensive and the cost-effectiveness is too low. A low-cost and effective solution is to add indexes appropriately. Proper use of indexes can increase query speed by thousands of times, which is an amazing effect.

(ii) MySQL index types:

There are five types of MySQL indexes: primary key index, normal index, unique index, full-text index, and clustered index (multi-column index).

Unique indexes and full-text indexes are rarely used. We mainly focus on primary key indexes, common indexes, and clustered indexes.

1) Primary key index: The primary key index is an index added to the primary key. When setting the primary key, MySQL will automatically create the primary key index;

2) Ordinary index: an index created on non-primary key columns;

3) Clustered index: An index created on multiple columns.

(III) Index syntax:

View the index of a table: show index from table name;

Create a normal index: alter table table name add index index name (column to add index)

Create a clustered index: alter table table name add index index name (indexed column 1, indexed column 2)

Delete the index of a table: drop index index name on table name;

(IV) Performance testing

Test environment: blogger's work desktop

Processor: Intel Core i5-4460 3.2GHz;

Memory 8G;

64-bit Windows.

1: Create a test table

DROP TABLE IF EXISTS `test_user`;
CREATE TABLE `test_user` (
 `id` bigint(20) PRIMARY key not null AUTO_INCREMENT,
 `username` varchar(11) DEFAULT NULL,
 `gender` varchar(2) DEFAULT NULL,
 `password` varchar(100) DEFAULT NULL
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

The storage engine uses MyISAM because this engine has no transactions and has a very fast insertion speed, which allows us to quickly insert tens of millions of test data. After we insert the data, we change the storage type to InnoDB.

2: Use stored procedures to insert 10 million records

create procedure myproc() 
begin 
declare num int; 
set num=1; 
while num <= 10000000 do 
insert into test_user(username,gender,password) values(num,'confidential',PASSWORD(num)); 
set num=num+1;
end while;
 end

call myproc();

Because of the MyISAM engine used, it only took 246 seconds to insert 10 million records. If it was the InnoDB engine, it would take several hours.

Then change the storage engine back to InnDB. Use the following command: alter table test_user engine=InnoDB; This command takes about 5 minutes to execute, so please be patient.

Tips: This is a test. Do not modify the storage engine at will in the production environment. The alter table operation will lock the entire table. Use with caution. Secondly: The MyISAM engine has no transactions and only writes data to memory and then periodically flushes the data to disk, so a sudden power outage will result in data loss. The InnDB engine writes data into logs and then flushes them to disk periodically, so there is no need to worry about sudden power outages. Therefore, if InnDB can be used in actual production, it should be used.

3: SQL test

select id,username,gender,password from test_user where id=999999

Time taken: 0.114s.

Because we set id as the primary key when we created the table, the primary key index was used when executing this SQL, which made the query speed so fast.

Let's execute select id,username,gender,password from test_user where username='9000000'
Time taken: 4.613s.

We add a normal index to the username column.

ALTER TABLE `test_user` ADD INDEX index_name(username);

This process takes about 54.028s. The index building process will scan the entire table and build indexes one by one, which is of course slow.

Then execute: selectid,username,gender,password from test_user where username='9000000'
Time taken: 0.043s.

Then use username and password to join the query

select id,username,gender,password from test_user where username='9000000' and `password`='*3A70E147E88D99888804E4D472410EFD9CD890AE'

Although we have added an index to the username column, the password column is not indexed. When the index is used to filter the password, the entire table will still be scanned.

The query speed dropped immediately.

Time taken: 4.492s.

When our SQL has multiple columns of filtering conditions, we need to add indexes to multiple columns of the query to form an aggregate index:

Add a clustered index: ALTER TABLE `test_user` ADD INDEX index_union_name_password(username,password)
Then execute:

Time taken: 0.001s.

As mentioned at the beginning, software-level optimization is to add indexes reasonably and optimize slow-executing SQL. The two complement each other and are indispensable. If the query is still slow after adding an index, you should consider whether it is a SQL problem and optimize SQL.

Tips:

1: Even with the index added, the following situations may still require a full table scan:

The index column is a string without quotes;

The index column does not appear after the where condition;

The index column does not appear at the front.

2: Possible situations where the associated query does not use the index include:

The character sets of the associated tables are different;

The character sets of the associated fields are different;

The storage engine is different;

The lengths of the fields are different.

This is the end of this article about optimizing the query speed of MySQL tens of millions of data based on indexes. For more relevant MySQL tens of millions of index optimization query content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!