SQL优化案例分享--联合索引

z7369

　　下面这个SQL如何优化：
　　desc select count(*) as total from Art_Person a, Art_Works b where a.PersonCode=b.PersonCode;
　　+----+-------------+-------+-------+---------------+------------+---------+---------------------+--------+-------------+

　　|>　　+----+-------------+-------+-------+---------------+------------+---------+---------------------+--------+-------------+
　　|  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>>　　Query OK, 8666 rows affected (0.49 sec)
　　Records: 8666  Duplicates: 0  Warnings: 0

　　mysql>>　　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;
　　+----+-------------+-------+-------+-------------------+------------+---------+---------------------+------+--------------------------+

　　|>　　+----+-------------+-------+-------+-------------------+------------+---------+---------------------+------+--------------------------+
　　|  1 | SIMPLE      | a     | index | PersonCode        | PersonCode | 24      | NULL                | 8666 | Using index              |

　　|  1 | SIMPLE      | b     | ref   | PersonCode,idx_PU |>　　+----+-------------+-------+-------+-------------------+------------+---------+---------------------+------+--------------------------+
　　2 rows in set (0.00 sec)
　　下面是删除索引，看看count(1)这么走。

　　mysql>>　　Query OK, 8666 rows affected (0.45 sec)
　　Records: 8666  Duplicates: 0  Warnings: 0

　　mysql>>　　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>>　　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;
　　+----+-------------+-------+-------+-------------------+------------+---------+---------------------+------+--------------------------+

　　|>　　+----+-------------+-------+-------+-------------------+------------+---------+---------------------+------+--------------------------+
　　|  1 | SIMPLE      | a     | index | PersonCode        | PersonCode | 24      | NULL                | 8666 | Using index              |

　　|  1 | SIMPLE      | b     | ref   | PersonCode,idx_PU |>　　+----+-------------+-------+-------+-------------------+------------+---------+---------------------+------+--------------------------+
　　2 rows in set (0.00 sec)
　　下面是去掉大表的索引：把大表的索引去掉  count(PersonCode) 也没用，还是不走索引

　　mysql>>　　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;
　　+----+-------------+-------+-------+---------------+------------+---------+---------------------+--------+-------------+

　　|>　　+----+-------------+-------+-------+---------------+------------+---------+---------------------+--------+-------------+
　　|  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>>　　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)