未加星标

Top 6 MySQL DBA Mistakes

字体大小 | |
[数据库(mysql) 所属分类 数据库(mysql) | 发布者 店小二03 | 时间 2016 | 作者 红领巾 ] 0人收藏点击收藏

To err is human, or so the saying goes. Unfortunately, in the world of IT, innocuous mistakes made early on can lead to really daunting problems down the road. While you can never eliminate human error or bad decisions, there are steps that you can take to minimize the likelihood of finding yourself in a pickle due to a hasty decision arrived at in the spur of the moment or a mistake made early on in design. In today’s article, we’ll go over a few of the most common DBA mistakes to avoid. Some of these relate specifically to mysql, while others may be applicable to any RDBMS.

1.Using the Wrong Storage Engine

MySQL has a number of database engines, but the two most common are MyISAM and InnoDB. Prior to MySQL v5.5.5, the default engine was MyISAM. Since then, it’s InnoDB. The latter's strengths are the enforcing of foreign key constraints and transaction support. That's where changes made by two or more operations are handled as a single unit of work, so that all of the changes are either applied, or reverted.

These two features are absent from the MyISAM engine. Another potential drawback of MyISAM is that DML statements obtain an exclusive lock on the table. All other operations must therefore wait until the current operation concludes. This can have a detrimental performance as usage grows.

This is not to say that you should simply accept the default InnoDB engine every time. Benchmarks show that MyISAM tables beat every other engine for speed and use fewer system resources. Hence, if you’re creating a fairly simple or test database or table, and don't require the features provided by InnoDB, then MyISAM should be up for consideration.

Related Articles MySQL Numeric Overflow Gotcha An Overview of the MySQL Performance Schema MySQL Date Gotchas Importing Into MySQL from Other Databases


Top 6 MySQL DBA Mistakes
Figure 1: Table Explorer in MySQL Workbench 2.Not Specifying or Using the Wrong CHARSET

Just as MySQL has a default engine type, there is also a default CHARSET, which defines how string data is encoded in the database. The default is “Latin1,” but this may not support all the characters you might want to use. Developers in English speaking countries have a nasty habit of discounting internationalization considerations when designing their databases and applications. Your best choice for supporting a wide variety of languages is UTF-8 encoding. To utilize UTF-8 encoding on a table, add "DEFAULT CHARSET=utf8" right after the "ENGINE=xxx":

CREATE TABLE mywork.articles ( id int UNSIGNED NOT NULL AUTO_INCREMENT, title VARCHAR(255) NOT NULL, body MEDIUMTEXT, PRIMARY KEY (id)) ENGINE = InnoDB CHARSET = utf8; 3.Using the Wrong Data Type

MySQL offers a range of numeric, string, and time data types. In fact, the large number of data types can make choosing the right one a challenge. I've seen people use varchars for almost every field, thinking that it will simplify things. It won't. There's a reason that MySQL recognizes so many different data types. It might make database management easier, but MySQL will become a "dumb" data store and that may lead to problems.

Here a few considerations:

Using a CHAR(n) type can be better than a VARCHAR(n) if you’re always going to be storing n characters. Specify UNSIGNED when you will only need to store positive numbers. Using the correct size of an integer can allow MySQL to index columns faster. For instance, an INT(10) will index much faster than a BIGINT(20). 4. Selecting All Columns in Queries

The ubiquitous "SELECT * " may be quicker to write than selecting a bunch of columns but it can greatly increase the amount of data that the database server needs to return to your application, especially on really large core tables.

In most cases, you don’t need to use all columns in a query’s result set and would be better served by specifying just the column names that you will actually be referencing. This reduces not only the time to transmit the data, but also how much memory is required to store the query result set.

5. Neglecting to Perform Regularly Scheduled Backups

True story, I once recorded a CD with a very established producer whose backup practices put many IT shops too shame. He was once haphazard towards backups, that is until he lost all of his work after a computer crash. After that, he backed up each and every recording session to another hard drive, and to a DVD, which he stored down the street at another location.

Although a rare occurrence, I have still been witness to many a loss of data due to malfunction or disaster. So much can go wrong: databases can fail; hard drives can suddenly stop; servers can explode in a puff of smoke; Web hosts can go bankrupt. Losing your MySQL data can be catastrophic to your business, so ensure you have automated backups or replication in place.

6. Incorrect Indexing

As a general rule of thumb, indexes should be applied to any column named in the WHERE clause your SELECT queries. By indexing a column, the database engine can find all the records that match much more quickly. Otherwise, the SQL engine has to look at every single record in the table to locate all the records you may be requesting. Indexed integer columns are faster that indexed character columns, so whenever possible, use integer columns.

When it comes to indexing, less is more as indexes are regenerated during every table INSERT or UPDATE operation. Therefore, you have to weigh a query’s retrieval speed versus DML (Data Manipulation Language) operations. Your best bet is to only add indexes when necessary and leave other columns alone.

Conclusion

It has been said that to learn from one’s mistakes is smart, while learning from other people’s mistakes is wise. If you come away with just one tip from today’s article and avoid a calamity in the future, then I will consider my job done here today.

Have you ever made a MySQL blunder and aren’t ashamed to share it? Then add a comment or email me at rgconsulting(AT)robgravelle(DOT)com (replace the (AT) and (DOT) with the ‘@’ and ‘.’ Characters respectively) and tell me about it. I might feature it in a future article.

See all articles by Rob Gravelle

本文数据库(mysql)相关术语:navicat for mysql mysql workbench mysql数据库 mysql 存储过程 mysql安装图解 mysql教程 mysql 管理工具

主题: SQLMySQLInnoDBUTAUAUTRIMRYEDI
分页:12
转载请注明
本文标题:Top 6 MySQL DBA Mistakes
本站链接:http://www.codesec.net/view/481422.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mysql) | 评论(0) | 阅读(35)