【转】MySQL开发者亟需了解的12个技巧与窍门
【转】MySQL开发者需要了解的12个技巧与窍门 MySQL是世界上实际最流行的数据库管理系统,是遍布全球编程社区的首
【转】MySQL开发者需要了解的12个技巧与窍门MySQL是世界上实际最流行的数据库管理系统,是遍布全球编程社区的首选。它有一个系列有趣的特性,在很多方面都很擅长。由于其巨大的人气,在网上可以找到许多MySQL的使用技巧。这里有12个最好的技巧和窍门,所有MySQL数据库开发者都应该了解一下。
避免编辑转储文件
Mysqldump创建的转储文件原本是无害的,但它很容易被尝试去编辑。然而,人们应该知道在任何情况下的试图修改这些文件被证明是有危险的。直观地看对这些文件的改动会导致数据库损坏,从而导致系统的退化。为了让你的系统免受任何麻烦,你必须避免编辑MySQL转储文件。
MyISAM 块大小
大多数开发者忘记了这一事实,文件系统往往需要一个大的MyISAM块以保证高效运行。许多开发者不知道块大小的设置。.MYI文件存储在myisam_block_size的设置里,这个设置项可用来修改大的块尺寸。MyISAM块大小的默认值是1K,这不是当前大多数系统的恰当设置。因此,开发者应该考虑指定一个与之相适应的值。
打开 Delay_Key_Write
为避免系统崩溃时数据库损坏delay_key_write默认是关闭的。有人可能会问,如果是这样的话,为什么要把它放在首位打开呢?从防止数据库每次写MyISAM key文件时刷新该文件方面看这是必要的。通过把它打开,开发者可以节省很多时间。参考MySQL官方手册了解你的版本如何把它打开。
Joins(表连接)
创建索引和使用相同的列类型:join(表连接)操作可以在Mysql中被优化。若应用中有许多join操作,可以通过创建相同的列类型上join来优化。创建索引是加速应用的另一种方法。查询修改有助于你找回期望的查询结果。
优化WHERE从句
即使你只搜索一行MySQL也会查询整个表,因此,建议你当只需要一条结果时将limit设置为1。通过这样做,可以避免系统贯穿搜索整个表,从而可以尽可能快找到与你需求相匹配的记录。
在Select查询上使用Explain关键字
你肯定希望得到与任何特定查询相关的一些帮助。Explain关键词在这方面是非常有帮助的。它在你寻求查询到底做了什么时提供了具体细节。例如,在复杂join查询前键入Explain关键词你会得到很多有用的资料。
使用查询缓存优化查询
MySQL的查询缓存是默认启用的。这主要是因为缓存有助于查询的快速执行,缓存可以在相同的查询多次运行使用。你在关键字前加入当前日期、CURRDATE等PHP代码使查询缓存它从而启用此功能。
使用堆栈跟踪隔离Bug
各种Bug可以使用stack_trace隔离出来。一个空指针足以毁掉一段特定的代码,任何开发人员都知道它有这样的能力。了解使用堆栈跟踪的细节,从而在你的代码里避免bug。
设置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
用MySQL Dump 命令备份数据库
开发者都知道数据库备份的重要性,当系统出现重大故障时能够起到救命的作用。
最简单的备份数据库的方法
$mysqldump –user?[user name]?–password=[password]?[database name]?>?[dump file]//你也可以用简写"-u","-p"来分别代替"user"和"password"
//将多个数据库导入到一个文件只要在后面添加需要导出数据库的名称:
mysqldump –user?[user name]?–password=[password]
[first database name]?[second database name]?>?[dump file]
//许多数据库都提供了顺序备份的功能,要备份所有数据库只需要添加--all-databases参数。如果你不喜欢命令行,从Sourceforge上下载automysqlbackup吧。
调整CONFIG的配置
PERL脚本MySQL Tuner是另一个强大的优化数据库性能的工具,它能够帮助你对MySQL配置来进行多处调整和修改。你可以访问该项目的官网来进一步了解它。
转自:http://flycars001.iteye.com/blog/1972984

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

Big data structure processing skills: Chunking: Break down the data set and process it in chunks to reduce memory consumption. Generator: Generate data items one by one without loading the entire data set, suitable for unlimited data sets. Streaming: Read files or query results line by line, suitable for large files or remote data. External storage: For very large data sets, store the data in a database or NoSQL.

MySQL query performance can be optimized by building indexes that reduce lookup time from linear complexity to logarithmic complexity. Use PreparedStatements to prevent SQL injection and improve query performance. Limit query results and reduce the amount of data processed by the server. Optimize join queries, including using appropriate join types, creating indexes, and considering using subqueries. Analyze queries to identify bottlenecks; use caching to reduce database load; optimize PHP code to minimize overhead.

Backing up and restoring a MySQL database in PHP can be achieved by following these steps: Back up the database: Use the mysqldump command to dump the database into a SQL file. Restore database: Use the mysql command to restore the database from SQL files.

How to insert data into MySQL table? Connect to the database: Use mysqli to establish a connection to the database. Prepare the SQL query: Write an INSERT statement to specify the columns and values to be inserted. Execute query: Use the query() method to execute the insertion query. If successful, a confirmation message will be output.

One of the major changes introduced in MySQL 8.4 (the latest LTS release as of 2024) is that the "MySQL Native Password" plugin is no longer enabled by default. Further, MySQL 9.0 removes this plugin completely. This change affects PHP and other app

To use MySQL stored procedures in PHP: Use PDO or the MySQLi extension to connect to a MySQL database. Prepare the statement to call the stored procedure. Execute the stored procedure. Process the result set (if the stored procedure returns results). Close the database connection.

Creating a MySQL table using PHP requires the following steps: Connect to the database. Create the database if it does not exist. Select a database. Create table. Execute the query. Close the connection.

Oracle database and MySQL are both databases based on the relational model, but Oracle is superior in terms of compatibility, scalability, data types and security; while MySQL focuses on speed and flexibility and is more suitable for small to medium-sized data sets. . ① Oracle provides a wide range of data types, ② provides advanced security features, ③ is suitable for enterprise-level applications; ① MySQL supports NoSQL data types, ② has fewer security measures, and ③ is suitable for small to medium-sized applications.
