时间：2021-07-01 10:21:17 帮助过：12人阅读
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)
SQL优化案例分享--联合索引
标签：sql优化