ADADADADAD

SQL优化案例分享--联合索引[ mysql数据库 ]

mysql数据库时间：2024-12-03 12:13:22

作者：文/会员上传

如何提高mysql查询效率

简介：

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

以下为本文的正文内容，内容仅供参考！本站为公益性网站，复制本文以及下载DOC文档全部免费。

下面这个SQL如何优化：

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

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

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

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

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

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

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

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

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

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

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

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

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

| 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>descselectsql_no_cachecount(*)astotalfromArt_Worksb,Art_Personaforceindex(PersonCode)whereb.PersonCode=a.PersonCode;

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

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

|1|SIMPLE|a|ref|PersonCode|PersonCode|24|newart.b.PersonCode|13|Usingindex|

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

2rowsinset(0.00sec)

mysql>altertableArt_Worksaddindexidx_PU(PersonCode,PID);

QueryOK,173223rowsaffected(5.73sec)

Records:173223Duplicates:0Warnings:0

mysql>descselectsql_no_cachecount(*)astotalfromArt_Worksb,Art_Personaforceindex(PersonCode)whereb.PersonCode=a.PersonCode;

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

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

|1|SIMPLE|b|ref|PersonCode,idx_PU|idx_PU|25|newart.a.PersonCode|1|Usingwhere;Usingindex|

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

2rowsinset(0.00sec)

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

将本文的Word文档下载到电脑

下载

热门标签: 优化sql

精品

申请https需要什么条件

SQL优化案例分享--联合索引[ mysql数据库 ]

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

精品

热门推荐

大家都在看