SQL优化案例分享--联合索引
发布时间：2022-04-01 02:06:30 所属栏目：MySql教程 来源：互联网
导读：下面这个SQL如何优化： desc select count(*) as total from Art_Person a, Art_Works b where a.PersonCode=b.PersonCode; +----+-------------+-------+-------+---------------+------------+---------+---------------------+--------+-------------+ |
|
下面这个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_WorksG *************************** 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_PersonG *************************** 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) （编辑：财气旺网 - 财气网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐