How do I ask for help optimizing & fixing queries in MySQL?

MySQL questions are some of my favorites on *.

MySQL问题是*上我最喜欢的一些问题。

Unfortunately, things like this:

不幸的是，这样的事情：

SELECT foo, bar, baz, quux, frozzle, lambchops FROM something JOIN somethingelse ON 1=1 JOIN (SELECT * FROM areyouserious) v ON 0=5 WHERE lambchops = 'good';

make my eyes bleed.

让我的眼睛流血。

Also, attempts at describing your schema often go like this:

此外，尝试描述您的架构通常如下所示：

I have a table CrazyTable with a column that is a date and it has a primary key of Foo_Key but I want to join on SOMETABLE using a substring of column_bar (which is in CrazyTable) which pertains to the phase of the moon (which I store in moon_phases as a thrice-serialized PHP array).

我有一个表CrazyTable，其中一个列是一个日期，它有一个主键Foo_Key但我想使用column_bar的子串（在CrazyTable中）加入SOMETABLE，该子串属于月亮的相位（我存储它）在moon_phases中作为三次序列化的PHP数组）。

Here is an example of a question I asked, that had I not followed the steps below, I would never have gotten a satisfactory answer from anyone: I have no shame..

以下是我问过的一个问题的例子，如果我没有按照以下步骤操作，我将永远不会得到任何人的满意答复：我没有羞耻......

I will answer below with what helps me the most with getting the best answer to your question. What helps you?

我将在下面回答最能帮助我解决问题的方法。什么帮助你？

3 个解决方案

#1

Use SHOW CREATE TABLE

---

This tells me more about your tables than your words ever could:

这告诉我关于你的桌子的更多信息，而不是你的话语：

mysql> show create table magic\G
*************************** 1. row ***************************
       Table: magic
Create Table: CREATE TABLE `magic` (
  `id` int(11) DEFAULT NULL,
  `what` varchar(255) DEFAULT NULL,
  `the` datetime DEFAULT NULL,
  `heck` text,
  `soup_is_good` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

CAVEAT: If you have 70 columns in your table, omit the unnecessary ones. What's necessary?

CAVEAT：如果表中有70列，请省略不必要的列。有什么必要？

• Fields JOINed on
• 字段加入
• Fields SELECTed
• 字段选择
• Fields WHEREed on
• 字段在哪里

Use EXPLAIN

---

This allows me to see how best to optimize your currently working, yet presumably slow query:

这让我可以看到如何最好地优化您当前正在工作但速度慢的查询：

mysql> explain select *     from magic\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: magic
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra: 
1 row in set (0.00 sec)

Use \G

---

Having to scroll right is generally an inconvenience.

向右滚动通常是不方便的。

Usual:

通常：

mysql> select * from magic;
+------------+-------------------------------+---------------------+-------------------+--------------+
| id         | what                          | the                 | heck              | soup_is_good |
+------------+-------------------------------+---------------------+-------------------+--------------+
| 1000000000 | A really long text string yay | 2009-07-29 22:28:17 | OOOH A TEXT FIELD |        100.5 | 
+------------+-------------------------------+---------------------+-------------------+--------------+
1 row in set (0.00 sec)

Better:

更好：

mysql> select * from magic\G
*************************** 1. row ***************************
          id: 1000000000
        what: A really long text string yay
         the: 2009-07-29 22:28:17
        heck: OOOH A TEXT FIELD
soup_is_good: 100.5
1 row in set (0.00 sec)

CAVEAT: \G obviously turns one row of data into several. This becomes equally cumbersome for several rows of data. Do what looks best.

CAVEAT：\ G显然将一行数据转换成几行。对于多行数据，这变得同样麻烦。做什么看起来最好。

Use an external pastebin for obnoxiously large chunks of data:

使用外部pastebin来获取令人讨厌的大块数据：

• Pastie
• Pastie
• gist.github
• gist.github

Let us know your expectations

---

• Slow? - We don't know what slow is to you. Seconds, minutes, hours? It helps to know.
• 慢？ - 我们不知道你的速度有多慢。秒，分钟，小时？它有助于了解。
• Faster - We don't know this either. What's your expectation of fast?
• 更快 - 我们也不知道。你对快速的期望是什么？
• Frequency - Is this a query that you plan to run just once? Daily? Hundreds or thousands of times a day? This helps us know when it's Good Enough.
• 频率 - 这是您计划只运行一次的查询吗？日常？一天数百或数千次？这有助于我们知道何时足够好。

#2

Procedure Analyse

程序分析

select * from yourtable procedure analyse()\G

The above will let others know the max and min values stored in the table. That helps.

以上内容将让其他人知道表中存储的最大值和最小值。这有帮助。

#3

Knowing which indexes you have on the tables concerned is vital, imo. You state you are using a substring of column_bar in the where clause - you may need to denormalize and store this substring in another column and then index it. There again cardinality of the column can make it worthless using an index on that column, if (for example) there are only 2 distinct values present. For a useful video tutorial on Performance Tuning Best Practices watch this youtube video by Jay Pipes.

知道你在相关表上有哪些索引是至关重要的，imo。您声明在where子句中使用了column_bar的子字符串 - 您可能需要对此子字符串进行非规范化并将其存储在另一列中，然后对其进行索引。如果（例如）只有2个不同的值存在，那么列的基数可以使其在该列上使用索引变得毫无价值。有关性能调优最佳实践的有用视频教程，请观看Jay Pipes的YouTube视频。