SQL優化案例分享--聯合索引

2024-09-07 22:12:32

字體：大中小

來源：轉載

供稿：網友

下面這個SQL如何優化：

desc select count(*) as total from Art_Person a, Art_Works b where a.PersonCode=b.PersonCode;

+----+-------------+-------+-------+---------------+------------+---------+---------------------+--------+-------------+

| id | select_type | table | type | possible_keys | key        | key_len | ref                 | rows   | Extra       |

+----+-------------+-------+-------+---------------+------------+---------+---------------------+--------+-------------+

| 1 | SIMPLE      | b     | index | PersonCode    | PersonCode | 25      | NULL                | 166904 | Using index |

| 1 | SIMPLE      | a     | ref   | PersonCode    | PersonCode | 24      | newart.b.PersonCode |      1 | Using index |

+----+-------------+-------+-------+---------------+------------+---------+---------------------+--------+-------------+

2 rows in set (0.00 sec)

mysql> show profile for query 2;

+----------------------+----------+

| Status               | Duration |

+----------------------+----------+

| starting             | 0.000149 |

| checking permissions | 0.000015 |

| checking permissions | 0.000015 |

| Opening tables       | 0.000049 |

| System lock          | 0.000032 |

| init                 | 0.000065 |

| optimizing           | 0.000032 |

| statistics           | 0.000053 |

| preparing            | 0.000039 |

| executing            | 0.000019 |

| Sending data         | 2.244108 |

| end                  | 0.000042 |

| query end            | 0.000008 |

| closing tables       | 0.000023 |

| freeing items        | 0.000038 |

| logging slow query   | 0.000007 |

| logging slow query   | 0.000008 |

| cleaning up          | 0.000008 |

+----------------------+----------+

18 rows in set (0.00 sec)

mysql> show create table Art_Works/G

*************************** 1. row ***************************

Table: Art_Works

Create Table: CREATE TABLE `Art_Works` (

`PID` int(11) NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`PID`),

KEY `ViewCount` (`ViewCount`),

KEY `PersonCode` (`PersonCode`) USING BTREE,

KEY `GoodsStatus` (`GoodsStatus`) USING BTREE,

KEY `CreateTime` (`CreateTime`) USING BTREE,

KEY `RelWorkID` (`RelWorkID`) USING BTREE

) ENGINE=MyISAM AUTO_INCREMENT=210549 DEFAULT CHARSET=utf8

mysql> show create table Art_Person/G

*************************** 1. row ***************************

Table: Art_Person

Create Table: CREATE TABLE `Art_Person` (

`PID` int(11) NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`PID`),

UNIQUE KEY `MemberID` (`MemberID`),

KEY `PersonCode` (`PersonCode`) USING BTREE

) ENGINE=MyISAM AUTO_INCREMENT=8699 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

解決辦法（索引的問題）：帶著主鍵，改成聯合索引。count() 的時候帶上主鍵就ok了不然不會走的。其實這個索引就是為了小表驅動大表，只是大表的索引對count（）而言沒用。加上主鍵就可以了。

mysql> alter table Art_Person add index idx_PU(PersonCode,PID);帶著主鍵，改成聯合索引。

Query OK, 8666 rows affected (0.49 sec)

Records: 8666 Duplicates: 0 Warnings: 0

mysql> alter table Art_Works add index idx_PU(PersonCode,PID); 帶著主鍵，改成聯合索引。

Query OK, 166904 rows affected (6.02 sec)

Records: 166904 Duplicates: 0 Warnings: 0

mysql> desc select sql_no_cache count(*) as total from Art_Works b,Art_Person a force index (PersonCode) where b.PersonCode=a.PersonCode;

+----+-------------+-------+-------+-------------------+------------+---------+---------------------+------+--------------------------+

| id | select_type | table | type | possible_keys     | key        | key_len | ref                 | rows | Extra                    |

+----+-------------+-------+-------+-------------------+------------+---------+---------------------+------+--------------------------+

| 1 | SIMPLE      | a     | index | PersonCode        | PersonCode | 24      | NULL                | 8666 | Using index              |

| 1 | SIMPLE      | b     | ref   | PersonCode,idx_PU | idx_PU     | 25      | newart.a.PersonCode |    1 | Using where; Using index |

+----+-------------+-------+-------+-------------------+------------+---------+---------------------+------+--------------------------+

2 rows in set (0.00 sec)

下面是刪除索引，看看count(1)這么走。

mysql> alter table Art_Person drop index idx_PU ;

Query OK, 8666 rows affected (0.45 sec)

Records: 8666 Duplicates: 0 Warnings: 0

mysql> alter table Art_Works drop index idx_PU ;

Query OK, 166904 rows affected (3.90 sec)

Records: 166904 Duplicates: 0 Warnings: 0

mysql> select sql_no_cache count(1) as total from Art_Works b,Art_Person a force index (PersonCode) where b.PersonCode=a.PersonCode;

+--------+

| total |

+--------+

| 166657 |

+--------+

1 row in set (2.38 sec)

mysql> alter table Art_Works add index idx_PU(PersonCode,PID);

Query OK, 166904 rows affected (4.32 sec)

Records: 166904 Duplicates: 0 Warnings: 0

mysql> select sql_no_cache count(1) as total from Art_Works b,Art_Person a force index (PersonCode) where b.PersonCode=a.PersonCode;

+--------+

| total |

+--------+

| 166657 |

+--------+

1 row in set (0.44 sec)

mysql> desc select sql_no_cache count(1) as total from Art_Works b,Art_Person a force index (PersonCode) where b.PersonCode=a.PersonCode;

+----+-------------+-------+-------+-------------------+------------+---------+---------------------+------+--------------------------+

| id | select_type | table | type | possible_keys     | key        | key_len | ref                 | rows | Extra                    |

+----+-------------+-------+-------+-------------------+------------+---------+---------------------+------+--------------------------+

| 1 | SIMPLE      | a     | index | PersonCode        | PersonCode | 24      | NULL                | 8666 | Using index              |

| 1 | SIMPLE      | b     | ref   | PersonCode,idx_PU | idx_PU     | 25      | newart.a.PersonCode |    1 | Using where; Using index |

+----+-------------+-------+-------+-------------------+------------+---------+---------------------+------+--------------------------+

2 rows in set (0.00 sec)

下面是去掉大表的索引：把大表的索引去掉 count(PersonCode) 也沒用，還是不走索引

mysql> alter table Art_Works drop index idx_PU ;

Query OK, 166904 rows affected (3.82 sec)

Records: 166904 Duplicates: 0 Warnings: 0

mysql> desc select sql_no_cache count(b.PersonCode) as total from Art_Works b,Art_Person a force index (PersonCode) where b.PersonCode=a.PersonCode;

+----+-------------+-------+-------+---------------+------------+---------+---------------------+--------+-------------+

| id | select_type | table | type | possible_keys | key        | key_len | ref                 | rows   | Extra       |

+----+-------------+-------+-------+---------------+------------+---------+---------------------+--------+-------------+

| 1 | SIMPLE      | b     | index | PersonCode    | PersonCode | 25      | NULL                | 166904 | Using index |

| 1 | SIMPLE      | a     | ref   | PersonCode    | PersonCode | 24      | newart.b.PersonCode |     13 | Using index |

+----+-------------+-------+-------+---------------+------------+---------+---------------------+--------+-------------+

2 rows in set (0.00 sec)

mysql> select sql_no_cache count(b.PersonCode) as total from Art_Works b,Art_Person a force index (PersonCode) where b.PersonCode=a.PersonCode;

+--------+

| total |

+--------+

| 166657 |

+--------+

1 row in set (2.47 sec)

mysql> alter table Art_Works add index idx_PU(PersonCode,PID);

Query OK, 166904 rows affected (4.23 sec)

Records: 166904 Duplicates: 0 Warnings: 0

mysql> select sql_no_cache count(b.PersonCode) as total from Art_Works b,Art_Person a force index (PersonCode) where b.PersonCode=a.PersonCode;

+--------+

| total |

+--------+

| 166657 |

+--------+

1 row in set (0.44 sec)

=====================下面是線上實驗結果========================================

mysql> desc select sql_no_cache count(*) as total from Art_Works b,Art_Person a force index (PersonCode) where b.PersonCode=a.PersonCode;

+----+-------------+-------+-------+---------------+------------+---------+---------------------+--------+-------------+

| id | select_type | table | type | possible_keys | key        | key_len | ref                 | rows   | Extra       |

+----+-------------+-------+-------+---------------+------------+---------+---------------------+--------+-------------+

| 1 | SIMPLE      | b     | index | PersonCode    | PersonCode | 25      | NULL                | 173223 | Using index |

| 1 | SIMPLE      | a     | ref   | PersonCode    | PersonCode | 24      | newart.b.PersonCode |     13 | Using index |

+----+-------------+-------+-------+---------------+------------+---------+---------------------+--------+-------------+

2 rows in set (0.00 sec)

mysql> alter table Art_Works add index idx_PU(PersonCode,PID);

Query OK, 173223 rows affected (5.73 sec)

Records: 173223 Duplicates: 0 Warnings: 0

mysql> desc select sql_no_cache count(*) as total from Art_Works b,Art_Person a force index (PersonCode) where b.PersonCode=a.PersonCode;

+----+-------------+-------+-------+-------------------+------------+---------+---------------------+------+--------------------------+

| id | select_type | table | type | possible_keys     | key        | key_len | ref                 | rows | Extra                    |

+----+-------------+-------+-------+-------------------+------------+---------+---------------------+------+--------------------------+

| 1 | SIMPLE      | a     | index | PersonCode        | PersonCode | 24      | NULL                | 8910 | Using index              |

| 1 | SIMPLE      | b     | ref   | PersonCode,idx_PU | idx_PU     | 25      | newart.a.PersonCode |    1 | Using where; Using index |

+----+-------------+-------+-------+-------------------+------------+---------+---------------------+------+--------------------------+

2 rows in set (0.00 sec)

（編輯：武林網）

上一篇：如何用pt-online-schema-change在線修改表字段長度

下一篇：分布式數據庫事務的兩階段提交介紹