Shulou(Shulou.com)05/31 Report--

MySQL how to use multi-column index, I believe that many inexperienced people are helpless about this, this article summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

multi-column index

We often hear people say "index all columns in the WHERE condition," but this advice is very wrong. Creating separate indexes on multiple columns does not improve MySQL query performance in most cases. MySQL introduced a strategy called index merge after 5.0, which allows multiple single-column indexes on a table to be used to locate a given row to some extent. However, when the server does joint operations on multiple indexes, it usually consumes a lot of CPU and memory resources in the caching, sorting and merging operations of the algorithm, especially when some of the indexes are not highly selective and need to merge and scan a large amount of data.

At this point, we need a multi-column index.

case

Create a test database and data tables:

CREATE DATABASE IF NOT EXISTS db_test default charset utf8 COLLATE utf8_general_ci; use db_test; CREATE TABLE payment ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, staff_id INT UNSIGNED NOT NULL, customer_id INT UNSIGNED NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Insert 1000w rows of random data (using stored procedures):

DROP PROCEDURE IF EXISTS add_payment; DELIMITER // create PROCEDURE add_payment(in num INT) BEGIN DECLARE rowid INT DEFAULT 0; SET @exesql = 'INSERT INTO payment(staff_id, customer_id) values (?, ?) '; WHILE rowid

< num DO SET @staff_id = (1 + FLOOR(5000*RAND()) ); SET @customer_id = (1 + FLOOR(500000*RAND())); SET rowid = rowid + 1; prepare stmt FROM @exesql; EXECUTE stmt USING @staff_id, @customer_id; END WHILE; END // DELIMITER ; 或者你可以直接下载使用我的测试数据(也是利用上面的存储过程,但是我之后调整了数据): 测试数据 添加两个单列索引(执行过程要花点时间,建议分开一句一句执行): ALTER TABLE `payment` ADD INDEX idx_customer_id(`customer_id`); ALTER TABLE `payment` ADD INDEX idx_staff_id(`staff_id`); 查询一条数据利用到两个列的索引: select count(*) from payment where staff_id = 2205 AND customer_id = 93112; 查看执行计划: mysql>

explain select count(*) from payment where staff_id = 2205 AND customer_id = 93112; +----+-------------+---------+-------------+------------------------------+------------------------------+---------+------+-------+-------------------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+-------------+------------------------------+------------------------------+---------+------+-------+-------------------------------------------------------------------------+ | 1 | SIMPLE | payment | index_merge | idx_customer_id,idx_staff_id | idx_staff_id,idx_customer_id | 4,4 | NULL | 11711 | Using intersect(idx_staff_id,idx_customer_id); Using where; Using index | +----+-------------+---------+-------------+------------------------------+------------------------------+---------+------+-------+-------------------------------------------------------------------------+ 1 row in set (0.00 sec)

Using intersect(idx_staff_id,idx_customer_id) in type index_merge,Extra;

This is index merging, taking two indexes and merging the two results (intersection or union or both).

Search results:

mysql> select count(*) from payment where staff_id = 2205 AND customer_id = 93112 ; +----------+ | count(*) | +----------+ | 178770 | +----------+ 1 row in set (0.12 sec)

Then delete the index above and add a multi-column index:

ALTER TABLE payment DROP INDEX idx_customer_id; ALTER TABLE payment DROP INDEX idx_staff_id; ALTER TABLE `payment` ADD INDEX idx_customer_id_staff_id(`customer_id`, `staff_id`);

Note that a multi-column index cares about the order of the index columns (customer_id is placed first because it is more selective)

Enquiries:

mysql> select count(*) from payment where staff_id = 2205 AND customer_id = 93112; +----------+ | count(*) | +----------+ | 178770 | +----------+ 1 row in set (0.05 sec)

Find queries with faster multi-column indexes (here the data volume is still small, more obvious when it is larger)

note

The column order of a multi-column index is critical, and there is a rule of thumb for choosing the column order of an index: put selective *** columns at the top of the index (but not absolute). The rule of thumb considers cardinality and selectivity globally, not a specific query:

mysql> select count(DISTINCT staff_id) / count(*) AS staff_id_selectivity, count(DISTINCT customer_id) / count(*) AS customer_id_selectivity, count(*) from payment\G; *************************** 1. row *************************** staff_id_selectivity: 0.0005 customer_id_selectivity: 0.0500 count(*): 10000000 1 row in set (6.29 sec)

Customer_id is more selective, so use it as the *** bit of the index column.

A multi-column index can only match the leftmost prefix, that is:

select * from payment where staff_id = 2205 AND customer_id = 93112 ; select count(*) from payment where customer_id = 93112 ;

Index can be used, but

select * from payment where staff_id = 2205 ; After reading the above, do you know how to use multi-column indexes in MySQL? If you still want to learn more skills or want to know more related content, welcome to pay attention to the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.