count()很慢怎么办？
2020-03-31sad creeper
count(*) 的实现方式
在不同的 MySQL 引擎中，count(*) 有不同的实现方式。MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高；而 InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。这里需要注意的是，我们在这篇文章里讨论的是没有过滤条件的 count(*)，如果加了 where 条件的话，MyISAM 表也是不能返回得这么快的。
在数据库保存计数
把计数直接放到单独的一张数据表中保存。利用“事务”这个特性，把问题解决掉。
不同的 count 用法
count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加。最后返回累计值。
count(*)、count(主键 id) 和 count(1) 都表示返回满足条件的结果集的总行数；而 count(字段），则表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数。
对于 count(主键 id) 来说，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。
对于 count(1) 来说，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。单看这两个用法的差别的话，你能对比出来，count(1) 执行得要比 count(主键 id) 快。因为从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作。
对于 count(字段) 来说：如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。也就是前面的第一条原则，server 层要什么字段，InnoDB 就返回什么字段。但是 count(*) 是例外，并不会把全部字段取出来，而是专门做了优化，不取值。count(*) 肯定不是 null，按行累加。
按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count(*)，所以我建议你，尽量使用 count(*)。
阅读 2212 评论
f
free bitcoin
Hi there! I could have sworn I've been to this web site before but after browsing through some of the posts I realized it's new to me. Regardless, I'm certainly happy I found it and I'll be book-marking it and checking back regularly!
6个月前 ·
c
cialis online ordering
Heya i'm for the first time here. I found this board and I to find It truly useful & it helped me out much. I hope to give something back and help others such as you aided me.
1年前 ·
m
mr cashman slots music
vegas free slots computergirl888 triple diamond slots
1年前 ·
7
7 free online slots machines
konami slots riches best us online slots house of fun free online slots
1年前 ·
m
myvegas slots facebook
free poker slots vodes slot games all free casino slots
1年前 ·
o
online casino real money paypal
play live casino online online casino poker gambling website play casinos real money
1年前 ·
p
parx casino online
casino apps that pay real money online kasinot online gambling reviews
1年前 ·
c
casinos usa
online casino poker gambling website online gambling for real money best online casinos usa
1年前 ·
f
free money casino
free online casino to win real money casino games online that pay real money online casino real money usa no deposit
1年前 ·
c
casinos gambling online
online gambling sites real money zone online casino log in online casino real money ny
1年前 ·
r
real money casino free play
online casino usa my choice casino zone online casino free games
1年前 ·
o
online casino with free signup bonus real money usa no deposit
top rated online casino usa online casino real money usa no deposit online casino free signup bonus no deposit required
1年前 ·
o
online gamble
online casino free bonus no deposit live gambling online online casiono
1年前 ·
o
online online casino
real casino slots casino signup bonus no deposit ceaser casinos free online games
1年前 ·
v
vpn master
how to buy vpn anonymously nord vpn buy buy vpn with crypto
1年前 ·
f
free unlimited vpn
sonicwall vpn client zenmate vpn hoxx vpn proxy
1年前 ·
b
best vpn service providers
best free vpn 2022 best chrome vpn free best vpn for speed
1年前 ·
v
vpn configuration windows free
trust zone vpn best vpn for linux buy secure vpn
1年前 ·
v
vpn download free
best u k vpn vpn browser free 100% free vpn
1年前 ·
b
best free vpn for macbook
best vpn for torrenting free best vpn trial best fee vpn
1年前 ·
p
private vpn free
best antivirus with vpn vpn client vpn buy
1年前 ·
b
best vpn for 2017
buy vpn servers vpn configuration windows free free vpn for pc
1年前 ·
b
best vpn service providers
buy avast secureline vpn best vpn free best vpn 2019
1年前 ·
b
best vpn for routers
business vpn solutions business class vpn international vpn service
1年前 ·
b
best vpn app for windows
free vpn reviews proton vpn download top vpn rated services
1年前 ·
b
best anonymous vpn service
astrill vpn free secure vpn vpn free online
1年前 ·
s
secure vpn service
vpn free trial no credit card zenmate vpn free vpn ??
1年前 ·
c
community service essay sample
usa essay writing services cheap essays for sale college application essay writing help
1年前 ·
b
best essay writing website
essay writing service legit writing essay services good essay writing website
1年前 ·
g
good essay writing services
essay writing service legal law school essay writing service help starting an essay
1年前 ·
b
best custom essay service
cheap essays online buy an essay can someone write my essay for me
1年前 ·
p
persuasive essay help
write my essay fast buy essay online buy essays for college
1年前 ·
w
write my essay south park
college essay writing company essay writer website usa essay writing services
1年前 ·
m
mba essay services
custom essay writing company academic essay writer college application essay help online
1年前 ·
b
best custom essay writers
top 10 essay writing services online essay writers wanted write my essay cheap
1年前 ·
t
the best essay writers
online essay editing services best writing services reviews who can help me write an essay
1年前 ·
u
urgent custom essays
essay checking service custom admission essay custom law essay
1年前 ·
e
essay writer website
essay writting service where can i buy essays online custom writing essay service
1年前 ·
n
need help writing a essay
pay for essay writing best cheap essay looking for someone to write my essay
1年前 ·
e
essay writting service
essay writing help online best essay writing service website essay writing service usa
1年前 ·
w
write my essay wikipedia
write my essay fast can someone write my essay for me argumentative essay helper
1年前 ·
c
college application essay writing service
custom essay writing service toronto custom essay help buy cheap essays online
1年前 ·
l
law school essay review service
the best essay writers best custom essay writing good essay writing website
1年前 ·
e
essay writing service
best writing service the best custom essay writing service who will write my essay
1年前 ·
c
custom essay writing canada
write my college essay admission essay writing service customized essay writing
1年前 ·
c
cheap essay writing service online
best essay writing service reviews please help me write my essay write my history essay for me
1年前 ·
b
buy essays online safe
help writing an argumentative essay need help in writing an essay help with writing essays
1年前 ·
e
essay correction service
easy essay help cheap essays writing service custom essay writing cheap
1年前 ·
e
essay writing service scam
essay writing service online help writing scholarship essays custom essay station
1年前 ·
.
.
%random_anchor_text% %random_anchor_text% %random_anchor_text%
1年前 ·
w
website for essay writing
discount essay writing service university essay help best essay writing services
1年前 ·
i
i need help on writing an essay
write essay service write my essay review essay editing service online
1年前 ·
w
write my essay for me no plagiarism
essay about service best essay website essay writing services recommendations
1年前 ·
r
rutgers essay help
buy essays online safe custom essay writing services reviews essay writers for pay
1年前 ·
c
college essay editing services
essay writer helper best essays writing service essay writers for pay
1年前 ·
w
write my history essay for me
personal statement essay help college admission essay service write my history essay
1年前 ·
b
beastiality dating canada
free online dating singles sites mature free and single
1年前 ·
n
no email dating site
dating online chat and meet online dating personals no email dating site
1年前 ·
o
online dating games
local personal ads free dating online chat free dating sites without registering
1年前 ·
c
completely free online dating chat
dating websites for free dating sites free for men dating flirt site free
1年前 ·
d
dating flirt site free
free online dating service good free dating sites dating for singles
1年前 ·
h
https://onlinedatingsuccessguide.com
dating sims free online japanese adult singles sites free dating without registration
1年前 ·
m
matchmeetups dating site
date match site chat free dating site meet women free
1年前 ·
c
chat free dating site
free online dating sites with no fees meet girls for free village ladies totally free
1年前 ·
s
single women online
free dating sight meet women free online services for individuals
1年前 ·
h
https://datingwebsiteshopper.com
best online dating men free free woman paid divid video
1年前 ·
c
coursework plan
coursework master coursework references coursework example pdf
1年前 ·
c
coursework references
coursework vs thesis masters king's college london coursework data analysis coursework
1年前 ·
c
coursework science
coursework calculator coursework ucl custom coursework writing service
1年前 ·
c
coursework report example
do my coursework for me coursework advantages and disadvantages nea coursework
1年前 ·
c
coursework support
do my coursework online coursework based a levels coursework thesis
1年前 ·
d
design coursework
coursework presentation jcq coursework malpractice courseware mit
1年前 ·
c
creative writing coursework
coursework jelentése coursework degree quantitative coursework examples
1年前 ·
j
jcq coursework malpractice
coursework questions coursework meaning coursework ne demek
1年前 ·
c
coursework based masters
courseworks help coursework info coursework meaning
1年前 ·
c
coursework rule usyd
coursework ka hindi coursework or research coursework mark
1年前 ·
匿
null
很好
2年前 ·