高性能MySQL（第4版）
最新书摘：
-
jvmpoko2014-12-23其他数据库对于IN完全等同于多个OR条件的子句，因为这两者是完全等价的，在MySQL中这点是不成立的，MySQL对于in的数据先进行排序然后二分查找的方式来判断是否符合条件，这样做的复杂度是O(logn),而OR操作是O(n)
-
jvmpoko2014-12-22一旦客户端发送了请求，它能做的事情只能是等待结果了
-
jvmpoko2014-12-22每次删除数据后，都暂停下再做下次删除，这样可以将服务器上原本一次性的压力分散到一个很长的时间段里，就可以大大降低对服务器的影响
-
jvmpoko2014-12-22在优化有问题的查询的时候，目标应该是找到一个更优的方法获得实际需要的结果而不是一定总是需要从mysql获取一样的结果集
-
夭寺2013-09-24当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影”行。
-
夭寺2013-09-23重写查询，决定查询的读表顺序，选择索引，用户可以传递提示影响优化决策过程
-
[已注销]2012-04-12Placing all of your application’s data in a single MySQL instance simply will not scalewell. Sooner or later you’ll hit performance bottlenecks. The traditional solution inmany types of applications is to buy more powerful servers. This is what’s known as“scaling vertically” or “scaling up.” The opposite approach is to divide your work acrossmany computers, which is usually called “scaling horizontally” or “scaling out.” We’lldiscuss how to combine scale-out and scale-up solutions with consolidation, and howto scale with clustering solutions. Finally, most applications also have some data that’srarely or never needed and that can be purged or archived. We call this approach “scalingback,” just to give it a name that matches the other strategies.
-
[已注销]2012-04-12Prepared statements have a few limitations and caveats:• Prepared statements are local to a connection, so another connection cannot usethe same handle. For the same reason, a client that disconnects and reconnectsloses the statements. (Connection pooling or persistent connections can alleviatethis problem.)• Prepared statements cannot use the query cache in MySQL versions prior to 5.1.• It’s not always more efficient to use prepared statements. If you use a preparedstatement only once, you might spend more time preparing it than you would justexecuting it as normal SQL. Preparing a statement also requires two extra roundtripsto the server (to use prepared statements properly, you should deallocate themafter use).• You cannot currently use a prepared statement inside a stored f...
-
[已注销]2012-04-12MySQL doesn’t always optimize correlated subqueries badly. If you hear advice toalways avoid them, don’t listen! Instead, measure and make your own decision.
-
[已注销]2012-04-12Correlated Subqueries（相关子查询）MySQL sometimes optimizes subqueries very badly. The worst offenders are IN() subqueries in the WHERE clause.
-
[已注销]2012-04-12Using the Performance Schema．．．mysql> SELECT event_name, count_star, sum_timer_wait-> FROM events_waits_summary_global_by_event_name-> ORDER BY sum_timer_wait DESC LIMIT 5;
-
[已注销]2012-04-12The SHOW PROFILE command is a community contribution from Jeremy Cole that’s includedin MySQL 5.1 and newer, and some versions of MySQL 5.0. It is the only realquery profiling tool available in a GA release of MySQL at the time of writing. It isdisabled by default, but can be enabled for the duration of a session (connection) simplyby setting a server variable:mysql> SET profiling = 1;mysql> SELECT * FROM sakila.nicer_but_slower_film_list;[query results omitted]997 rows in set (0.17 sec)The query returned 997 rows in about a sixth of a second. Let’s see what SHOW PROFILES (note the plural) knows about this query:mysql> SHOW PROFILES;+----------+------------+-------------------------------------------------+| Query_ID | Duration | Query |+----------+------------+-----------...
-
[已注销]2012-04-12Most of MySQL’s transactional storage engines don’t use a simple row-locking mechanism.Instead, they use row-level locking in conjunction with a technique for increasingconcurrency known as multiversion concurrency control (MVCC).
-
[已注销]2012-04-12The locking style that offers the greatest concurrency (and carries the greatest overhead)is the use of row locks. Row-level locking, as this strategy is commonly known, isavailable in the InnoDB and XtraDB storage engines, among others.
-
[已注销]2012-04-12MySQL, on the other hand, does offer choices. Its storage engines can implement theirown locking policies and lock granularities. Lock management is a very important decisionin storage engine design; fixing the granularity at a certain level can give betterperformance for certain uses, yet make that engine less suited for other purposes
-
[已注销]2012-04-12As the creators of Percona Server, we’re biased to some extent, but we think this appendixis fairly objective because we provide services, support, consulting, training,and engineering for all of the variants of MySQL. We also invited Brian Aker and MontyWidenius, who created the Drizzle and MariaDB projects, respectively, to contributeto this appendix, so that it wouldn’t just be our version of the story.
-
[已注销]2012-04-12There are currently two solutions that take advantage of this by permitting so-calledNoSQL access to MySQL. The first is a daemon plugin called HandlerSocket, whichwas created at DeNA, a large Japanese social networking site. It permits you to accessan InnoDB Handler object through a simple protocol. In effect, you’re reaching pastthe upper layers of the server and connecting directly to InnoDB over the network.There are reports of HandlerSocket achieving over 750,000 queries per second.HandlerSocket is distributed with Percona Server, and the memcached access to InnoDBis available in a lab release of MySQL 5.6.The second option is accessing InnoDB through the memcached protocol. The lab releasesof MySQL 5.6 have a plugin that permits this.
-
[已注销]2012-04-12Amazon RDS isn’t the only DBaaS game in town for MySQL users. There are alsoservices such as FathomDB (http://fathomdb.com) and Xeround (http://xeround.com).
-
[已注销]2012-04-12The Cisco server has lower per-CPU performance than the EC2 servers. Surprised? We were a bit surprised ourselves.。。。Thus, the explanation for our results is probably as follows: the Ciscoserver’s CPUs are a couple of years old, and are slower than the EC2 servers. But formore complex tasks such as running a database server, the overhead of virtualizationplaces the EC2 servers at a disadvantage. It’s not always easy to distinguish betweenslow CPUs, slow memory access, and virtualization overhead, but in this instance thedifferences seem a bit clearer.
-
员外2012-03-10Sometimes people protest that they just don’t have time to benchmark the server for 8 or 12 hours at 10 different levels of concurrency on two or three server versions. If you don’t have the time to do the benchmarks right, any time you do spend is wasted; it is better to trust other people’s results, instead of doing an incomplete benchmark and getting the wrong answers.