问题描述
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.
这篇关于我如何寻求帮助优化 &修复 MySQL 中的查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！