mysql语句count(1)_mysqlÖÐcount(1)Óëcount(*)±È½Ï

mysqlÖÐcount(1)Óëcount(*)±È½Ï

2019/10/10/17:36:22  ÔĶÁ£º1157  À´Ô´£º¹È¸èSEOËã·¨  ±êÇ©£º

·òΨSEOÊÓƵ½Ì³Ì

sqlµ÷ÓÅ,Ö÷ÒªÊÇ¿¼ÂǽµµÍ:consistent getsºÍphysical readsµÄÊýÁ¿.

count(1)Óëcount(*)±È½Ï:

Èç¹ûÄãµÄÊý¾Ý±íûÓÐÖ÷¼ü,ÄÇôcount(1)±Ècount(*)¿ì,Èç¹ûÓÐÖ÷¼üµÄ»°£¬ÄÇÖ÷¼ü£¨ÁªºÏÖ÷¼ü£©×÷ΪcountµÄÌõ¼þÒ²±Ècount(*)Òª¿ì,Èç¹ûÄãµÄ±íÖ»ÓÐÒ»¸ö×ֶεĻ°ÄÇcount(*)¾ÍÊÇ×î¿ìµÄÀ²,count(*) count(1) Á½Õ߱ȽÏ,Ö÷Òª»¹ÊÇÒªcount(1)ËùÏà¶ÔÓ¦µÄÊý¾Ý×Ö¶Î.

Èç¹ûcount(1)ÊǾÛË÷Òý,id,Äǿ϶¨ÊÇcount(1)¿ì¡£µ«ÊDzîµÄºÜСµÄ,ÒòΪcount(*),×Ô¶¯»áÓÅ»¯Ö¸¶¨µ½ÄÇÒ»¸ö×Ö¶Î,ËùÒÔû±ØҪȥcount(?)£¬ÓÃcount(*),sql»á°ïÄãÍê³ÉÓÅ»¯µÄ.

countÏê½â:count(*)½«·µ»Ø±í¸ñÖÐËùÓдæÔÚµÄÐеÄ×ÜÊý°üÀ¨ÖµÎªnullµÄÐÐ,È»¶øcount(ÁÐÃû)½«·µ»Ø±í¸ñÖгýÈ¥nullÒÔÍâµÄËùÓÐÐеÄ×ÜÊý,ÓÐĬÈÏÖµµÄÁÐÒ²»á±»¼ÆÈë.

distinct ÁÐÃû,µÃµ½µÄ½á¹û½«ÊdzýȥֵΪnullºÍÖظ´Êý¾ÝºóµÄ½á¹û

×ܽáÈýÌõ¾­Ñé:

1.ÈκÎÇé¿öÏÂSELECT COUNT(*) FROM tablenameÊÇ×îÓÅÑ¡Ôñ£»

2.¾¡Á¿¼õÉÙSELECT COUNT(*) FROM tablename WHERE COL = 'value’ÕâÖÖ²éѯ£»

3.¶Å¾øSELECT COUNT(COL) FROM tablenameµÄ³öÏÖ¡£

¹ú¸öÕÒÒ»ÎÄÕ²»¶®Ó¢ÎÄûÒë,´úÂëÈçÏÂ:COUNT(*)vsCOUNT(col)

LookingathowpeopleareusingCOUNT(*)andCOUNT(col)itlookslikemostofthemthinktheyaresynonymsandjustusingwhattheyhappentolike,whilethereissubstantialdifferenceinperformanceandevenqueryresult.

Letslookatthefollowingseriesofexamples:

CREATETABLE`fact`(

`i`int(10)unsignedNOTNULL,

`val`int(11)defaultNULL,

`val2`int(10)unsignedNOTNULL,

KEY`i`(`i`)

)ENGINE=MyISAMDEFAULTCHARSET=latin1

mysql>selectcount(*)fromfact;

+———-+

|count(*)|

+———-+

|7340032|

+———-+

1rowinset(0.00sec)

mysql>selectcount(val)fromfact;

+————+

|count(val)|

+————+

|7216582|

+————+

1rowinset(1.17sec)

mysql>selectcount(val2)fromfact;

+————-+

|count(val2)|

+————-+

|7340032|

+————-+

1rowinset(0.00sec)

As this is MYISAM table MySQL has cached number of rows in this table. This is why it is able to instantly answer COUNT(*) and

COUNT(val2) queries, but not COUNT(val). Why ? Because val column is not defined as NOT NULL there can be some NULL values in it and so MySQL have to perform table scan to find out. This is also why result is different for the second query.

So COUNT(*) and COUNT(col) queries not only could have substantial performance performance differences but also ask different question.

MySQL Optimizer does good job in this case doing full table scan only if it is needed because column can be NULL.

Now lets try few more queries,´úÂëÈçÏÂ:mysql>selectcount(*)fromfactwherei<10000;

+———-+

|count(*)|

+———-+

|733444|

+———-+

1rowinset(0.40sec)

mysql>explainselectcount(*)fromfactwherei<10000G

***************************1.row***************************

id:1

select_type:SIMPLE

table:fact

type:range

possible_keys:i

key:i

key_len:4

ref:NULL

rows:691619

Extra:Usingwhere;Usingindex

1rowinset(0.00sec)

mysql>selectcount(val)fromfactwherei<10000;

+————+

|count(val)|

+————+

|720934|

+————+

1rowinset(1.29sec)

mysql>explainselectcount(val)fromfactwherei<10000G

***************************1.row***************************

id:1

select_type:SIMPLE

table:fact

type:range

possible_keys:i

key:i

key_len:4

ref:NULL

rows:691619

Extra:Usingwhere

1rowinset(0.00sec)

mysql>selectcount(val2)fromfactwherei<10000;

+————-+

|count(val2)|

+————-+

|733444|

+————-+

1rowinset(1.30sec)

mysql>explainselectcount(val2)fromfactwherei<10000G

***************************1.row***************************

id:1//phpfensi.com

select_type:SIMPLE

table:fact

type:range

possible_keys:i

key:i

key_len:4

ref:NULL

rows:691619

Extra:Usingwhere

1rowinset(0.00sec)

As you can see even if you have where clause performance for count(*) and count(col) can be significantly different. In fact this example shows just 3 times performance difference because all data fits in memory, for IO bound workloads you frequently can see 10 and even 100 times performance difference in this case.

The thing is count(*) query can use covering index even while count(col) can’t. Of course you can extend index to be (i,val) and get query to be index covered again but I would use this workaround only if you can’t change the query (ie it is third party application) or in case column name is in the query for reason, and you really need count of non-NULL values.

It is worth to note in this case MySQL Optimizer does not do too good job optimizing the query. One could notice (val2) column is not null so count(val2) is same as count(*) and so the query could be run as index covered query. It does not and both queries have to perform row reads in this case.´úÂëÈçÏÂ:mysql>altertablefactdropkeyi,addkey(i,val);

QueryOK,7340032rowsaffected(37.15sec)

Records:7340032Duplicates:0Warnings:0

mysql>selectcount(val)fromfactwherei<10000;

+————+

|count(val)|

+————+

|720934|

+————+

1rowinset(0.78sec)