我如何寻求帮助优化 &修复 MySQL 中的查询?

2021-12-20 00:00:00 optimization mysql

MySQL 问题是我在 StackOverflow 上最喜欢的一些问题.

MySQL questions are some of my favorites on StackOverflow.

不幸的是，事情是这样的:

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';

让我的眼睛流血.

此外，描述您的架构的尝试通常是这样的:

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

我有一个表 CrazyTable，其中一列是日期，它的主键是 Foo_Key，但我想使用 column_bar 的子字符串(在 CrazyTable 中)加入 SOMETABLE，该子字符串与月相有关(我将其作为三次序列化的 PHP 数组存储在 Moon_phases 中).

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).

这是我问的一个问题的例子，如果我没有按照以下步骤操作，我永远不会从任何人那里得到满意的答案:我没有羞耻感..

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?

推荐答案

使用 SHOW CREATE TABLE

<小时>

这比你的话更能告诉我你的桌子:

Use SHOW CREATE TABLE

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

mysql> show create table magicG
*************************** 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:如果您的表格中有 70 列，请省略不必要的.什么是必要的?

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

  • 加入的字段
  • 选择的字段
  • 所在的字段

这让我可以了解如何最好地优化您当前正在工作但可能很慢的查询:

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

mysql> explain select *     from magicG
*************************** 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)

使用G

<小时>

必须向右滚动通常很不方便.

通常:

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)

更好:

mysql> select * from magicG
*************************** 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)

警告: G 显然将一行数据变成了几行.这对于几行数据来说同样麻烦.做看起来最好的事情.

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

对令人讨厌的大块数据使用外部粘贴箱:

Use an external pastebin for obnoxiously large chunks of data:

  • 馅饼
  • gist.github
  • 慢? - 我们不知道对你来说慢是什么.秒、分、小时?了解它会有所帮助.
  • 更快 - 我们也不知道这一点.您对快速的期望是什么?
  • 频率 - 这是您计划只运行一次的查询吗?日常?一天几百次还是几千次?这有助于我们知道什么时候足够.
  • 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.

相关文章