看到叶金荣的一篇关于mysql分页的文章,结合雅虎之前发的一篇PDF 谈谈自己的看法

在叶子的文章里谈到了使用inner join 从而减少了对page的扫描也就是减少了所谓的回表 例如：

SELECT * FROM `t1` INNER JOIN ( SELECT id FROM `t1`ORDER BY id DESC LIMIT 935500,10) t2 USING (id)

通过直接对id的操作 而不是整张表的扫描 通过id 的join 抓出符合条件id 然后通过ID 再去做数据的抓取。这样就避免了对不需要的页面的扫描。

不过这样也不是最佳的方法 还可以通过对id 的 range更加缩小范围 例如：

我们要分100条记录分一页 可以写成

$page_size=100 select * from t where id > 99 order by id asc limit $page_size ; select * from  t where id >199 order by id asc limit $page_size;

Here’s a simple question that I wasn’t able to solve with Google or the MySQL documentation (which is normally excellent). Perhaps it’s mentioned somewhere, but I can’t find it. Here’s the question:

What privileges must a user have to run EXPLAIN for a query?

Is there an EXPLAIN privilege? No, there’s not.

The answer turns out to be really simple: you need the same privileges that you’d need to execute the query itself. At least, as far as I know, that’s the case. I haven’t been able to find a counter-example, and from what I know of the MySQL query execution process, this makes perfect sense.

If you’re not familiar with how EXPLAIN works, it’s roughly like the following. The presence of the EXPLAIN keyword sets a flag in the query plan. The query then executes, but instead of doing the work the query would normally do, it instead writes …

Taxonomy upgrade extras:  galera Backup manager monitoring Operations

FromDual tools provide valuable additional functionality which facilitate and optimize daily operations of your MySQL databases. Since our last newsletter a lot of things have changed in the FromDual tools.

To the MySQL Environment (MyEnv) numerous improvements and suggestions of our customers were added. The most important changes were introduced to the …

InnoDB provides no means to repair corrupted table space. Once a table got corrupt the only way to repair MySQL files is to start it with innodb_force_recovery={4,5,6} in hope that you can dump the table, so you can rebuild the table space from scratch. At least this is what the manual says. But let’s take a closer look at InnoDB corruption. In some case you can repair InnoDB table space much faster.

UPDATE: If corruption is in PRIMARY index check post Recover Corrupt MySQL Database

What InnoDB provides to repair tablespace corruption

InnoDB doesn’t let you repair the table space, but you can rebuild secondary indexes with ALTER TABLE DROP/ADD KEY. That may be very useful in case corruption malformed pages where secondary index is stored.

So, before starting a whole story with innodb_force_recovery, dump, drop, create and reload …

When people use frameworks they often rely on ORMs to generate queries for them. However when they are not explicitly writing the queries themselves it can be difficult to track their source when they show up in MySQL logs.

A pattern I find really useful is adding query comments that contain the source of the query.

For example:

SELECT * FROM users /*application:webapp,category:chill,route:users#get,all*/;

This allows us to quickly find the action that generated the query. At GitHub we use the Marginalia Gem to inject these comments. Marginalia also allows you to inject your own components.

A useful comment component to add is request id. This means you can match requests to slow queries in the logs for debugging.

Last week it was released the RC version of MySQL for Visual Studio 1.2.2. In this blog post we'll be showing one of the new features in this release. And we will be doing a short demo about some of the first steps when starting to use MySQL with .NET applications.

A new major version of Connector/Python is available: v2.0.0 alpha has been been released and is available for download! As with any alpha-software, it’s probably not good to throw it in production just yet.

Our manual has the full change log but here’s an overview of most important changes for this relase.

Some incompatibilities

The world evolves, at least the software does, and Python is not different. I’m not as bold as the guys at Django who dropped support of Python v2.6 with the Django v1.7 release. I’m leaving it in because I’m nice.

Supported Python: 2.6 and 2.7 and 3.3 and 3.4

We do not …

Monitoring flow control in a Galera cluster is very important. If you do not, you will not understand why writes may sometimes be stalled. Percona XtraDB Cluster 5.6 provides 2 status variables for such monitoring: wsrep_flow_control_paused and wsrep_flow_control_paused_ns. Which one should you use?

What is flow control?

Flow control does not exist with regular MySQL replication, but only with Galera replication. It is simply the mechanism nodes are using when they are not able to keep up with the write load: to keep replication synchronous, the node that is starting to lag instructs the other nodes that writes should be paused for some time so it does not get too far behind.

If you are not familiar with this notion, you should read this …

At some point of time every DBA must have come across a situation where he has to enable the general log to debug an issue or even to capture all the queries hitting the DB. In such situations what most DBA’s do is inform the stakeholders that he is  enabling the general log and this might cause a drop in performance and in turn the stakeholders ask a question like
“What will the percentage of performance drop?  Can you give us a number?”

The easy answer is “It is difficult to provide a number”.
 I had wanted to address this, but never really made an effort until I saw the below blog post “PERFORMANCE_SCHEMA VS SLOW QUERY LOG”  http://www.mysqlperformanceblog.com/2014/02/11/performance_schema-vs-slow-query-log/

In that particular blog Peter explains why he …

One of our most useful features is Top Queries, which allows users to examine various metrics for families of similar queries. Once a query family is selected, we can drill down into its query samples. Individual samples are shown on a scatterplot. Selecting individual samples will display its captured SQL, latency, errors, and more.

We collect query samples by decoding the MySQL protocol over TCP and extracting the query text. Not only that, we also capture errors, warnings, and more by inspecting traffic over the wire.

Until now, query samples excluded prepared statements. This is because prepared statements are not sent over the wire as strings of SQL text. Statement preparation and execution is quite different from regular query execution, but in the end we generate a good approximation of what the statement SQL …