SQLite的性能和限制
|
Performance and Limitations
SQLite is a speedy database. But the words speedy,fast,peppy,or quick are rather subjective terms. To be perfectly honest,there are things SQLite can do faster than other databases,and there are things that it cannot. Suffice it to say,within the parameters for which it has been designed,SQLite can be said to be consistently fast and efficient across the board. SQLite uses B-trees for indexes and B+-trees for tables, the same as most other database systems. For searching a single table,it is as fast if not faster than any other database on average. Simple SELECT,INSERT,and UPDATE statements are extremely quick—virtually at the speed of RAM (for in-memory databases) or disk. Here SQLite is often faster than other databases, because it has less overhead to deal with in starting a transaction or generating a query plan and because it doesn’t incur the overhead of making a network calls to the server or negotiating authentication and privileges. Its simplicity here makes it fast. As queries become larger and more complex,however,query time overshadows the network call or transaction overhead,and the game goes to the database with the best optimizer. This is where larger, more sophisticated databases begin to shine. While SQLite can certainly do complex queries,it does not have a sophisticated optimizer or query planner. You can always trust SQLite to give you the result,but what it won’t do is try to determine optimal paths by computing millions of alternative query plans and selecting the fastest candidate,as you might expect from Oracle or PostgreSQL. Thus,if you are running complex queries on large data sets,odds are that SQLite is not going to be as fast as databases with sophisticated optimizers. So,there are situations where SQLite is not as fast as larger databases. But many if not all of these conditions are to be expected. SQLite is an embedded database designed for small to medium-sized applications. These limitations are in line with its intended purpose. Many new users make the mistake of assuming that they can use SQLite as a drop-in replacement for larger relational databases. Sometimes you can; sometimes you can’t. It all depends on what you are trying to do. In general,there are two major variables that define SQLite’s main limitations: ?Concurrency（并发）. SQLite has coarse-grained locking,which allows multiple readers but only one writer at a time. Writers exclusively lock the database during writes, and no one else has access during that time. SQLite does take steps to minimize the amount of time in which exclusive locks are held. Generally,locks in SQLite are kept for only a few milliseconds. But as a general rule of thumb,if your application has high write concurrency (many connections competing to write to the same database) and it is time critical,you probably need another database. It is really a matter of testing your application to know what kind of performance you can get. We have seen SQLite handle more than 500 transactions per second for 100 concurrent connections in simple web applications. But your transactions may differ in the number of records being modified or the number and complexity of the queries involved. Acceptable concurrency all depends on your particular application and can be determined empirically only by direct testing. In general, this is true with any database: you don’t know what kind of performance your application will get until you do real-world tests. ?Networking（网络）. Although SQLite databases can be shared over network file systems, the latency associated with such file systems can cause performance to suffer. Worse,bugs in network file system implementations can also make opening and modifying remote files—SQLite or otherwise—error prone. If the file system’s locking does not work properly,two clients may be allowed to simultaneously modify the same database file,which will almost certainly result in database corruption. It is not that SQLite is incapable of working over a network file system because of anything in its implementation. Rather,SQLite is at the mercy of the underlying file system and wire protocol,and those technologies are not always perfect. For instance,many versions of NFS have a flawed fcntl() implementation,meaning that locking does not behave as intended. Newer NFS versions,such are Solaris NFS v4,work just fine and reliably implement the requisite locking mechanisms needed by SQLite. However,the SQLite developers have neither the time nor the resources to certify that any given network file system works flawlessly in all cases. Again,most of these limitations are intentional,resulting from SQLite’s design. Supporting high write concurrency,for example,brings with it great deal of complexity,and this runs counter to SQLite’ssimplicity in design. Similarly,being an embedded database,SQLite intentionally does not support networking. This should come as no surprise. In short,what SQLite can’t do is a direct result of what it can. It was designed to operate as a modular,simple,compact,and easy-to-use embedded relational database whose code base is within the reach of the programmers using it. And in many respects,it can do what many other databases cannot,such as run in embedded environments where actual power ? Windowing functions. One of the new feature sets specified in ANSI SQL 99 are From: The Definitive Guide to SQLite (2nd edition) （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |