MySQL是世界上实际最流行的数据库管理系统，是遍布全球编程社区的首选。它有一个系列有趣的特性，在很多方面都很擅长。由于其巨大的人气，在网上可以找到许多MySQL的使用技巧。这里有12个最好的技巧和窍门，所有MySQL数据库开发者都应该了解一下。

Garfielt 翻译于 6天前 3人顶 顶 翻译的不错哦!

避免编辑转储文件 Mysqldump创建的转储文件原本是无害的，但它很容易被尝试去编辑。然而，人们应该知道在任何情况下的试图修改这些文件被证明是有危险的。直观地看 对这些文件的改动会导致数据库损坏，从而导致系统的退化。为了让你的系统免受任何麻烦，你必须避免编辑MySQL转储文件。

Garfielt 翻译于 6天前 0人顶 顶 翻译的不错哦!

MyISAM 块大小 大多数开发者忘记了这一事实，文件系统往往需要一个大的MyISAM块以保证高效运行。许多开发者不知道块大小的设置。.MYI文件存储在 myisam_block_size的设置里，这个设置项可用来修改大的块尺寸。MyISAM块大小的默认值是1K，这不是当前大多数系统的恰当设置。因 此，开发者应该考虑指定一个与之相适应的值。

Garfielt 翻译于 6天前 0人顶 顶 翻译的不错哦!

打开 Delay_Key_Write 为避免系统崩溃时数据库损坏delay_key_write默认是关闭的。有人可能会问，如果是这样的话，为什么要把它放在首位打开呢？从防止数据库每次写MyISAM key文件时刷该文件方面看这是必要的。通过把它打开，开发者可以节省很多时间。参考MySQL官方手册了解你的版本如何把它打开。

Garfielt 翻译于 6天前 0人顶 顶 翻译的不错哦!

Joins（表连接） 创建索引和使用相同的列类型：join（表连接）操作可以在Mysql中被优化。若应用中有许多join操作，可以通过创建相同的列类型上join来优化。创建索引是加速应用的另一种方法。查询修改有助于你找回期望的查询结果。

Garfielt 翻译于 6天前 0人顶 顶 翻译的不错哦!

优化WHERE从句 即使你只搜索一行MySQL也会查询整个表，因此，建议你当只需要一条结果时将limit设置为1。通过这样做，可以避免系统贯穿搜索整个表，从而可以尽可能快找到与你需求相匹配的记录。

Garfielt 翻译于 6天前 1人顶 顶 翻译的不错哦!

在Select查询上使用Explain关键字 你肯定希望得到与任何特定查询相关的一些帮助。Explain关键词在这方面是非常有帮助的。它在你寻求查询到底做了什么时提供了具体细节。例如，在复杂join查询前键入Explain关键词你会得到很多有用的资料。

Garfielt 翻译于 6天前 0人顶 顶 翻译的不错哦!

使用查询缓存优化查询 MySQL的查询缓存是默认启用的。这主要是因为缓存有助于查询的快速执行，缓存可以在相同的查询多次运行使用。你在关键字前加入当前日期、CURRDATE等PHP代码使查询缓存它从而启用此功能。

Garfielt 翻译于 6天前 0人顶 顶 翻译的不错哦!

使用堆栈跟踪隔离Bug 各种Bug可以使用stack_trace隔离出来。一个空指针足以毁掉一段特定的代码，任何开发人员都知道它有这样的能力。了解使用堆栈跟踪的细节，从而在你的代码里避免bug。

Garfielt 翻译于 6天前 0人顶 顶 翻译的不错哦!

设置SQL_MODE 枚举类型总是让人感到非常的疑惑。由于字段可能拥有多个可能的值,这些可能的值包括你指定的和null，在编码时将会出现很多问题，你将永远都会得到一个警告说代码不正确。一个简单的解决办法就是设置SQL_MODE。 //Start mysqld with $–sql-mode=”modes” //or $sql-mode=”modes” (my.ini – Windows / my.cnf – Unix) //Change at runtime, separate multiple modes with a comma $set [GLOBAL|SESSION] sql_mode=’modes’ //TRADITIONAL is equivalent to the following modes: STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, ERROR_FOR_DIVISION_BY_ZERO, and NO_AUTO_CREATE_USER 修改Root密码 修改root密码对于某些特定设置是必不可少的，修改命令如下： //Straightforward MySQL 101 $mysqladmin -u root password [Type in selected password] //Changing users ROOT password $mysqladmin -u root -p [type old password] newpass [hit enter and type new password. Press enter] //Use mysql sql command $mysql -u root -p //prompt “mysql>” pops up. Enter: $use mysql; //Enter user name you want to change password for $update user set password=PASSWORD (Type new Password Here) where User = ‘username’; //Don’t forget the previous semicolon, now reload the settings for the users privileges $flush privileges; $quit

bigtiger02 翻译于 6天前 1人顶 顶 翻译的不错哦!

用MySQL Dump 命令备份数据库 开发者都知道数据库备份的重要性，当系统出现重大故障时能够起到救命的作用。 最简单的备份数据库的方法 1 $mysqldump –user [user name] –password=[password] [database name] > [dump file] //你也可以用简写”-u”,”-p”来分别代替”user”和”password” //将多个数据库导入到一个文件只要在后面添加需要导出数据库的名称： 1 mysqldump –user [user name] –password=[password] 2 [first database name] [second database name] > [dump file] //许多数据库都提供了顺序备份的功能，要备份所有数据库只需要添加–all-databases参数。如果你不喜欢命令行，从Sourceforge上下载automysqlbackup吧。

bigtiger02 翻译于 6天前 2人顶 顶 翻译的不错哦!

调整CONFIG的配置 PERL脚本MySQL Tuner是另一个强大的优化数据库性能的工具，它能够帮助你对MySQL配置来进行多处调整和修改。你可以访问该项目的官网来进一步了解它。

bigtiger02 翻译于 6天前 1人顶 顶 翻译的不错哦!

10+ Useful Tricks Every MySQL Database Developer Must Consider

MySQL is essentially the most popular database management system in the world and is preferred by the programming community all over the globe.It has a range of interesting features and can be enhanced in myriad ways. Owing to its huge popularity, plenty of tips and tricks for MySQL usage are found on the Internet. Here are 12 best tips and tricks any MySQL database developer should consider.

Avoid Editing Dump Files

Mysqldump creates dump files that appear to be pretty harmless in nature and it is quite tempting to edit them. However, one would know how dangerous these can prove to be in case any change is attempted on them. Subtle looking changes to these files can lead to database corruption, thereby resulting in system degeneration. In order to keep your system free from any trouble, you must avoid editing MySQL dump files.

MyISAM Block Size

Most developers would be oblivious of the fact that file systems tend to need a large MyISAM block size for efficient operation. The settings for block size are not known to many developers. The .MYI files happen to store the myisam_block_size setting, which can be edited for large block size. The default value of MyISAM block size is 1k, which is inappropriate for most systems these days. Hence, a developer should consider specifying an appropriate value for the same.

Turn On Delay_Key_Write

The delay_key_write key is turned off by default, for avoiding corrupting database when there is a crash. One may wonder that if this is the case, why turn it on in the first place! This is necessary to prevent the database from flushing the MyISAM key file every single time it writes on it. By turning it on, the developer can save a lot of time. Refer to the official MySQL manual to know how to turn it on for your MySQL version.

Joins

Create index and same column type: Join operations can be optimized in MySQL. Applications with lots of Join operations can be optimized by creating columns being joined of the same type. Creating an index is another way of speeding up an application. Modification of queries will help you in retrieving the results that you desire.

Optimize WHERE Clause

MySQL will query the entire table even if you are searching a single row. Hence, it is advised that you specify the limit equal to one in case you need only one result. By doing this, you will prevent the system from going through the entire table as soon as it finds one record that matches your requirements.

Select Queries by Using Explain Keyword

You would like to have some help in case you are stuck with any particular query. The Explain keyword happens to be incredibly helpful in this regard. It provides the details that you seek about what a particular query is doing. For instance, type Explain keyword in front of a complex query with joins and you will get plenty of helpful material.

Optimize Queries Using Query Cache

MySQL servers have Query Caching enabled by default. This is mainly because caching helps in fast execution of queries and the same query can be run for several times. You may want to enable this function for Current Date, CURRDATE, by adding a PHP code in front of the keyword to enable Query Caching for it.

Isolate Bugs Using Stack Trace

Various bugs can be isolated using stack_trace. A null pointer is capable enough of ruining a particular piece of code and any developer would know its potential to do that. Learn in detail about using stack trace and you would be able to isolate bugs from your code.

Set SQL_MODE

It can be pretty confusing to use ENUMS. These can lead to severe problems in the code owing to the fact that these can have several possible values. The possible values include what you specify and NULL. However, you will always receive warnings when something is not right in the code. An easy fix is to set SQL_MODE to traditional and you will be done.

//Start mysqld with
$–sql-mode=”modes”
//or
$sql-mode=”modes” (my.ini – Windows / my.cnf – Unix)
//Change at runtime, separate multiple modes with a comma
$set [GLOBAL|SESSION] sql_mode=’modes’
//TRADITIONAL is equivalent to the following modes:
STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, ERROR_FOR_DIVISION_BY_ZERO, and NO_AUTO_CREATE_USER

Change the Root Password

Changing the root password is essential for updating certain settings. Here is the code to do that:

//Straightforward MySQL 101
$mysqladmin -u root password [Type in selected password]
//Changing users ROOT password
$mysqladmin -u root -p [type old password] newpass [hit enter and type new password. Press enter]
//Use mysql sql command
$mysql -u root -p
//prompt “mysql>” pops up. Enter:
$use mysql;
//Enter user name you want to change password for
$update user set password=PASSWORD (Type new Password Here) where User = ‘username’;
//Don’t forget the previous semicolon, now reload the settings for the users privileges
$flush privileges;
$quit

MySQL Dump Command for Database Backup

Any developer would know the importance of taking database dump. It can be life saving when there is some significant problem with the system.

Here is an easy way to backup the database:

$mysqldump –user [user name] –password=[password] [database name] > [dump file]
//Also, rather than using “user” and “password” you can use the shortened switches “–u” or “–p”
//Add multiple databases by including them inline. For example:
mysqldump –user [user name] –password=[password] [first database name] [second database name] > [dump file]
//As many databases can be added sequentially inline. To back up all databases just add the operator [–all-databases]. Or if you don’t feel like typing at all, just download ‘automysqlbackup’ from Sourceforge!

Fix Your CONFIG Files

MySQL Tuner, a PERL Script, is another incredible tool for optimizing database performance. There are multiple modifications and tweaks that one can be implemented using MySQL Tuner. One can refer to the official page of the project to know more about it.

转载请注明：jinglingshu的博客 » 个 MySQL 开发者都应该了解的 10 个技巧