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

编程入门行业动态更新时间:2024-10-28 20:22:47

本文介绍了我如何寻求帮助优化 &amp;修复 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: