您好,登錄后才能下訂單哦!
下面這個(gè)SQL如何優(yōu)化:
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)
解決辦法(索引的問題):帶著主鍵,改成聯(lián)合索引。count() 的時(shí)候 帶上 主鍵 就ok了 不然不會(huì)走的。其實(shí)這個(gè)索引就是為了小表驅(qū)動(dòng)大表,只是大表的索引 對(duì)count()而言 沒用。加上 主鍵 就可以了。
mysql> alter table Art_Person add index idx_PU(PersonCode,PID);帶著主鍵,改成聯(lián)合索引。
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); 帶著主鍵,改成聯(lián)合索引。
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)
=====================下面是線上實(shí)驗(yàn)結(jié)果========================================
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)
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。