mysql的in与excit_干货|mysql:exists还是in?哪个性能好?为什么?

作者:wanber

来源:牛客网在项目中经常会用到in和exists,他们功能几乎一样,那么性能有什么差别呢?

性能分析说明:SELECT * FROM A WHERE id IN (SELECT id FROM B);外表指A,子表指B

in 是把外表和子表作hash 连接,而exists是对外表作loop循环,每次loop循环再对子表进行查询。

1、in语句:

SELECT * FROM A WHERE id IN (SELECT id FROM B);

等价于:1、SELECT id FROM B ----->先执行in中的查询

2、SELECT * FROM A WHERE A.id = B.id

它查出B表中的所有id字段并缓存起来,检查A表的id是否与B表中的id相等,如果相等则将A表的记录加入结果集中(实际上是和A表进行连接),直到遍历完A表的所有记录.

2、exists语句:

SELECT * FROM a WHERE EXISTS(SELECT 1 FROM b WHERE B.id = A.id);

以上查询等价于:

1、SELECT * FROM A;

2、SELECT I FROM B WHERE B.id = A.id;

exists是对外表做loop循环,每次loop循环再对子表进行访问。select a.* from A a where exists(select id from B b where a.id=b.id) ,exists()会执行A.length次,它并不缓存exists()结果集,因为exists()结果集的内容并不重要,重要的是结果集中是否有记录,如果有则返回true,没有则返回false.当B表比A表数据大时适合使用exists(),因为它没有那么遍历操作,只需要再执行一次查询就行.

如:A表有10000条记录,B表有1000000条记录,那么exists()会执行10000次去判断A表中的id是否与B表中的id相等.

如:A表有10000条记录,B表有100000000条记录,那么exists()还是执行10000次,因为它只执行A.length次,可见B表数据越多,越适合exists()发挥效果.

再如:A表有10000条记录,B表有100条记录,那么exists()还是执行10000次,还不如使用in()遍历10000*100次,因为in()是在内存里遍历比较,而exists()需要查询数据库,我们都知道查询数据库所消耗的性能更高,而内存比较很快.

3、not in 和 not exists

使用not in 会内外表都全表扫描;使用not exists还能用上内表(子表)的索引。所以,一定建议使用Not exists

如何选择使用in 或者 exists?

看了那么多,估计你也已经懵逼了,下面开始讲人话:

in 是先查子表,存起来,然后A一个个去查,时间复杂度o(nm)。

exists是先查外表,再去看一个个存不存在,时间复杂度o(nb+树查询时间)

那么你可能会问,这样看exists肯定会比in快啊。等等,别着急,in查到的子表存到内存里了,exists去b+树中查还是查数据库,是基于磁盘的...

所以,如何选型呢?一般来讲用这种方式:

子表数据量比外表数据量少,使用in。

子表数据量比外表数据量大,使用exists。

子表与外表数据量大小差不多,用in与exists的效率相差不大。

欢迎关注公众号:牛客NOIP竞赛学

  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Thank you for purchasing or considering the purchase of Windows 7 In Depth. It’s amazing the changes that 20-odd years can bring to a com- puter product such as Windows. When we wrote our first Windows book back in the mid-1980s, our publisher didn’t even think the book would sell well enough to print more than 5,000 copies. Microsoft stock wasn’t even a blip on most investors’ radar screens. Boy, were they in the dark! Who could have imagined that a little more than a decade later, anyone who hoped to get hired for even a temp job in a small office would need to know how to use Microsoft Windows, Office, and a PC. Fifteen or so Windows books later, we’re still finding new and excit- ing stuff to share with our readers. Who could have imagined in 1985 that a mass-market operating system two decades later would have to include support for so many technolo- gies, most of which didn’t even exist at the time: DVD, DVD±RW, CD-R and CD-RW, Internet and intranet, MP3, MPEG, WMA, DV, USB, FireWire, APM, ACPI, RAID, UPS, PPOE, Gigabit Ethernet, 802.11g, WPA2, IPv6, Teredo, speech recognition, touch and pen interfaces, fault tolerance, disk encryption and compression…? The list goes on. And that 8GB of disk space Windows 7 occupies? It would have cost about half a million dollars in 1985. Today, it costs less than a dollar. In 1981, when we were building our first computers, the operating sys- tem (CP/M) had to be modified in assembly language and recompiled, and hardware parts had to be soldered together to make almost any new addition (such as a video display terminal) work. Virtually nothing was standardized, with the end result being that computers remained out of reach for average folks. Together, Microsoft, Intel, and IBM changed all that. Today you can pur- chase a computer, a printer, a scanner, an external disk drive, a key- board, a modem, a monitor, and a video card over the Internet, plug Introduction From the Library of Lee Bogdanoff Introduction them in, install Windows, and they’ll work together. The creation and adoption (and sometimes forcing) of hardware and software standards that have made the PC a household appliance the world over can largely be credited to Microsoft, like it or not. The unifying glue of this PC revolution has been Windows. Yes, we all love to hate Windows, but it’s here to stay. Linux and Mac OS X are formidable alterna- tives, but for most of us, at least for some time, Windows and Windows applications are “where it’s at.” And Windows 7 ushers in truly significant changes to the landscape. That’s why we were excited to write this book. Why This Book? We all know this book will make an effective doorstop in a few years. You probably have a few already. (We’ve even written a few!) If you think it contains more information than you need, just remember how helpful a good reference can be when you need it at the 11th hour. And we all know that computer technology changes so fast that it’s sometimes easier just to blink and ignore a phase than to study up on it. Windows 7 is definitely a significant upgrade in Windows’ security and sophistication—one you’re going to need to understand. If you’re moving up to Windows 7 from Windows XP, you should know that Windows 7 is a very dif- ferent animal. Yes, the graphics and display elements are flashier, but it’s the deeper changes that matter most. With its radically improved security systems, revamped Control Panel, friendlier net- work setup tools, new problem-tracking systems, improved power management and usability tools for mobile computers, and completely revamped networking and graphics software infrastructures, Windows 7 leaves XP in the dust. And if you’re moving up from Vista, you’ll be very pleasantly surprised at the improvements. Vista got a bad rap, perhaps for some good reasons: It was slow, required too much RAM, had driver issues, and annoyed users with its User Account Control prompts. Windows 7 fixes all of that, thank goodness! Think of Windows 7 as Vista after three years at a spa/reform school. It’s leaner, stronger, more refined, and ever so polite. In all ways, Windows 7 is superior to any operating sys- tem Microsoft has ever produced. Is Windows 7 so easy to use that books are unnecessary? Unfortunately, no. True, as with other releases of Windows, online help is available. As has been the case ever since Windows 95, how- ever, no printed documentation is available (to save Microsoft the cost), and the Help files are writ- ten by Microsoft employees and contractors. You won’t find criticisms, complaints, workarounds, or suggestions that you use alternative software vendors, let alone explanations of whyyou have to do things a certain way. For that, you need this book! We will even show you tools and techniques that Microsoft’s insiders didn’t think were important enough to document at all. You might know that Windows 7 comes in a bewildering array of versions: primarily Home Premium, Professional, Enterprise, and Ultimate (not to mention Starter, intended for relatively primitive “netbook” computers and emerging markets; Home Basic, sold only in emerging markets; and several extra versions sold in the European Union to comply with antitrust court-mandated restrictions). But Windows 7 is Windows 7, and all that really distinguishes the versions is the availability of various features. Mostof the differences matter only in the corporate world, where Windows 7 will be managed by network administrators, so most corporate users won’t need to 2 From the Library of Lee Bogdanoff 3 Why This Book? worry about them. For the remaining features, we tell you when certain features do or don’t apply to your particular version of Windows 7. (And we show you how to upgrade from one version to a better version, if you want the features your copy doesn’t have!) In this book’s many pages, we focus not just on the gee-whiz side of the technology, but why you should care, what you can get from it, and what you can forget about. The lead author on this book has previously written 17 books about Windows, all in plain English (several bestsellers), designed for everyone from rank beginners to full-on system administrators deploying NT Server domains. The coauthor has designed software and networks for more than 20 years and has been writing about Windows for 10 years. We work with and write about various versions of Windows year in and year out. We have a clear understanding of what confuses users and system administrators about installing, configuring, or using Windows, as well as (we hope) how to best convey the solu- tions to our readers. While writing this book, we tried to stay vigilant in following four cardinal rules: •Keep it practical. •Keep it accurate. •Keep it concise. •Keep it interesting, and even crack a joke or two. We believe that you will find this to be the best and most comprehensive book available on Windows 7 for intermediate through advanced users. And whether you use Windows 7 yourself or support others who do, we firmly believe this book will address your questions and needs. Our book addresses both home and business computer users. We assume you probably are not an engineer, and we do our best to speak in plain English and not snow you with unexplained jargon. As we wrote, we imagined that you, our reader, are a friend or co-worker who’s familiar enough with your computer to know what it’s capable of, but might not know the details of how to make it all happen. So we show you, in a helpful, friendly, professional tone. In the process, we also hope to show you things that you might not have known, which will help make your life easier—your com- puting life, anyway. We spent months and months poking into Windows 7’s darker corners so you wouldn’t have to. And, if you’re looking for power-user tips and some nitty-gritty details, we make sure you get those, too. We try to make clear what information is essential for you to understand and what is optional for just those of you who are especially interested. We’re also willing to tell you what we don’t cover. No book can do it all. As the title implies, this book is about Windows 7. We don’t cover setting up the Server versions of this operating system, called Windows 2000 Server, Windows Server 2003, and Windows Server 2008. However, we do tell you how to connect to and interact with these servers, and even other operating systems, including Mac OS X, Linux, and older variants of Windows, over a local area network. Because of space limitations, there is only one chapter devoted to coverage of Windows 7’s numer- ous command-line utilities, its batch file language, Windows Script Host, and Windows PowerShell. For that (in spades!), you might want to check Brian’s book Windows 7 and Vista Guide to Scripting, Automation, and Command Line Tools, which is due to be published in the fall of 2009. From the Library of Lee Bogdanoff Introduction Even when you’ve become a Windows 7 pro, we think you’ll find this book to be a valuable source of reference information in the future. Both the table of contents and the very complete index will provide easy means for locating information when you need it quickly. How Our Book Is Organized Although this book advances logically from beginning to end, it’s written so that you can jump in at any location, quickly get the information you need, and get out. You don’t have to read it from start to finish, nor do you need to work through complex tutorials. This book is broken down into seven major parts. Here’s the skinny on each one: Part I, “Getting Started with Windows 7,” introduces Windows 7’s new and improved features and shows you how to install Windows 7 on a new computer or upgrade an older version of Windows to Windows 7. It also shows you how to apply service packs to keep your version of Windows 7 up-to- date. Finally, we take you on a one-hour guided tour that shows you the best of Windows 7’s fea- tures and walks you through making essential settings and adjustments that will help you get the most out of your computer. In Part II, “Using Windows 7,” we cover the core parts of Windows 7, the parts you’ll use no matter what else you do with your computer: managing documents and files, using the Windows desktop, starting and stopping applications, searching through your computer’s contents, printing, and using the desktop gadgets and other supplied accessories. Don’t skip this section, even—or rather, espe- cially—if you’ve used previous versions of Windows. Windows 7 does many things differently, and you’ll want to see how to take advantage of it! Windows 7 has great tools for viewing, playing, creating, editing, and managing music, movies, and pictures. In Part III, “Multimedia and Imaging,” we show you how to use the new Windows Media Player, burn CDs, extract and edit images from cameras and scanners, send faxes, and create DVDs. Finally, we show you how to use Windows Media Center, which lets you view all that stuff and, on a properly equipped computer, record and play back your favorite TV shows. We even show you how to burn DVDs from your recorded shows and discuss compression options for storage consider- ations and format options for playback on other devices. In Part IV, “Windows 7 and the Internet,” we first help you set up an Internet connection and then move on to explain Windows 7’s Internet tools. We provide in-depth coverage of the new and improved (and safer!) Internet Explorer. The final chapter shows you how to diagnose Internet con- nection problems. Networks used to be found only in high-falutin’ offices and corporate settings. Now, any home or office with two or more computers should have a network. A LAN is inexpensive, and with one you can share an Internet connection, copy and back up files, and use any printer from any computer. In Part V, “Networking,” we walk you through setting up a network in your home or office, and show you how to take advantage of it in day-to-day use. We also show you how easy it is to share a DSL or cable Internet connection with all your computers at once, show you how to network with other operating systems, and, finally, help you fix it when it all stops working. Part VI, “Maintaining Windows 7,” covers system configuration and maintenance. We tell you how to work with the Control Panel and System Administration tools, provide tips and tricks for cus- tomizing the graphical user interface to maximize efficiency, explain how to manage your hard disk 4 From the Library of Lee Bogdanoff 5 Conventions Used in This Book and other hardware, and describe a variety of ways to upgrade your hardware and software (includ- ing third-party programs) for maximum performance. We show you how to troubleshoot hardware and software problems, edit the Windows Registry, and, for real power users, how to use and tweak the command-line interface. When Windows was introduced over two decades ago, computer viruses, online fraud, and hacking were only starting to emerge as threats. Today (thanks in great part to gapingsecurity holes in pre- vious versions of Windows), computer threats are a worldwide problem, online and offline. In Part VII, “Security,” we provide a 360-degree view of Windows 7’s substantial improvements in security. Here you’ll find out both what Windows 7 will do to help you, and what you must do for yourself. We cover protection against viruses and spyware, loss and theft, hackers and snoops, and fraud and spam—in that order. Part VIII, “Windows On the Move,” shows you how to get the most out of Windows 7 when either you or your computer, or both, are on the go. We show you how to use wireless networking safely, how to get the most out of your laptop, and how to connect to remote networks. We also show you how to use Remote Desktop to reach and use your own computer from anywhere in the world. We finish up with a chapter about the cutting edge in laptops and desktops—pen and touch computing using the Tablet-PC features of Windows 7. Appendix A, “Using Virtualization on Windows 7,” explains how to use a newly released, free ver- sion of the Microsoft Virtual PC program to run older XP programs under Windows 7. For some users, this can be an excellent alternative to creating a dual-boot system with XP and Windows 7.

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值