Dicas MySQL

7/27/2019 Dicas MySQL

1/3

Top 10 MySQL Best PracticesJanuary 12, 2011

Rob Gravelle lists his top 10 MySQL best practices for database administrators, architects, developers, and security personnel.

Many groups and individuals are involved in the field of data management, from MySQL administrators, architects,developers, as well as infrastructure support people. Each of these plays a part in the security, maintenance, andperformance of a MySQL installation. Therefore, when speaking of best practices, one has to consider which of thesefunctions that specific practice pertains to. In this top 10 list, I will try to include a bit from each discipline. I haveconsidered my own experiences as well as consulted with numerous other sources to compile this final list. Whether ornot you agree with each and every item and their ordering, the important thing is to at least consider each of thepoints raised here today. So, without further ado, here is my personal top 10 list.

1. Index Search FieldsYou should always index columns that you plan on searching on. Creating an index on a field in a table createsanother data structure which holds the field value, and pointer to the record it relates to. This index structure is thensorted, allowing binary searches to be performed on it. An index can be defined for a single column or multiplecolumns of a given table.

This rule also applies to fields where partial string searches will be performed on the start of the field. For instance thephrase "last_name LIKE 'rob%'" will use the index, whereas "WHERE last_name LIKE '%ert%'" will not.

This does not imply that the more indexes you have, the better. While that is true to a point, keep in mind that everyindex takes up disk space. In a MyISAM database, the index file can quickly reach the size limits of the underlying filesystem if many fields within the same table are indexed.

2. Avoid Using "SELECT *" in your QueriesAs a general rule, the more data is read from the tables, the slower a query becomes. Considering that someproduction tables may contains dozens of columns, some of which comprises of huge data types, it would be foolhardyto select all of them. A database server which is separate from the web server will only aggravate this issue, due tothe data having to be transferred across the network.

To reiterate, it is a good habit to always specify which columns you need when writing your SELECT statements.

3. Set a password for the "root" user and then rename the user.

Here's a security tip. Much like with UNIX, the first thing you should do with a clean MySQL install is set a passwordfor the root user:

$ mysqladmin -u root password NEWPASSWORD

Even better, once you've set the password, change the name of the "root" user to something else. A hacker on aMySQL server will likely target the root, both for its superuser status and because it is a known user. By changing thename of the root user, you make it all the more difficult for hackers to succeed using a brute-force attack. Use thefollowing series of commands to rename the "root" user:

$ mysql -u root -pmysql> use mysql;mysql> update user set password=PASSWORD("NEWPASSWORD") whereUser='';mysql> flush privileges;mysql> quit

0 MySQL Best Practices DatabaseJournal.com http://www.databasejournal.com/features/mysql/print.php/3918631

22/03/201


2/3

4. Tune your Queries with EXPLAIN

The EXPLAIN keyword is undoubtedly the most instructive analytical tool in the MySQL arsenal. Using it can give youvaluable insight on the steps that MySQL is taking to execute your query. This can help you spot the bottlenecks andother problems with your query or table structures.

In my Optimizing MySQL Query Retrieval Speed through Table Joins (http://www.databasejournal.com/features/mysql /article.php/3915606/Optimizing-MySQL-Query-Retrieval-Speed-Through-Table-Joins.htm) article, I included how to use EXPLAINto ascertain the efficiency of the table joins in the following query statement:

explainselect a.au_lname,a.au_fname,ta.royaltyper,t.title,t.royaltyfrom authors a,titleauthor ta,titles twhere a.au_id = ta.au_idand ta.title_id = t.title_id

EXPLAIN produced the following results:

id select_type table type possible_keys key key_len refrows Extra1 SIMPLE ta ALL (NULL) (NULL) (NULL) (NULL)8001 SIMPLE t ref NewIndex NewIndex 23 crosstab_article.ta.title_id100 Using where1 SIMPLE a ALL (NULL) (NULL) (NULL) (NULL)1000 Using where;

Using join buffer

The results of an EXPLAIN query will show you which indexes are being utilized, how the table is being scanned andsorted, and other useful information. At the very least, you can be sure that the lower the numbers appear in therows column, the faster the query should run.

5. Index and Use Same Column Types for JoinsIf your query contains many joins, you need to make sure that the columns that make up a join are indexed on bothtables. This will allow MySQL to better optimize the join operation.

Likewise, the columns that are joined must share the same type. For instance, if you join a DECIMAL column to one of the type INT, MySQL will be unable to use at least one of the indexes. String type columns must also use the samecharacter encoding.

6. LIMIT 1 When Getting a Unique Row

There are some queries that are meant to only return one row, such as those which fetch a unique record, or thatverify whether or not there are any records that satisfy the WHERE clause. In such cases, adding LIMIT 1 to yourquery can increase performance. This reduces execution time because the database engine will stop scanning forrecords after it finds the first matching record, instead of going through the whole table or index.

A second popular usage is in subqueries. In the following SELECT statement, we want to retrieve the first s2 fieldvalue sorted by the s1 column. We can then match it against the outer query values:

SELECT * FROM t1 WHERE s1 = (SELECT s2 FROM t2 ORDER BY s1 LIMIT 1);

7. Hide MySQL from the Internet

Most experienced database administrators and security personnel know to never host the database under the Webserver's root. For Web-enabled applications, MySQL should be hidden behind a firewall and communication should onlybe enabled between application servers and your Web servers. Another option is to use MySQL's skip-networkingoption. When it is enabled, MySQL only listens for local socket connections and ignores all TCP ports.


22/03/201


3/3

8. Use the Smallest Data Types Possible

This would seem to be common sense to me, because of my programming background. When I was attending college,there was still some of the "memory is scarce" philosophy carried over from the days of 256 MB hard drives.Nowadays, no one seems to care one iota about memory or hard drive space. "Memory is cheap!" is the new adage.While it is true in dollar terms, it still takes longer to read in large data types than smaller ones, as the former requiremore disk sectors to be read into memory.

The moral of the story is to ignore the temptation to immediately jump the largest data type when designing yourtables. Consider using an int rather than a bigint . You should also avoid large char(255) text fields when a varchar orsmaller char() will suffice. Using the right data type will fit more records in memory or index key block, meaning fewerreads, ergo faster performance.

9. Create Views to Simplify Commonly-used Table Joins

As discussed in my Writing Reusable Queries in MySQL (http://www.databasejournal.com/) article, views help to bothsimplify complex schemas and to implement security. One way that views contribute to security is to hide auditingfields from developers. They can also be used to filter out unindexed columns, leaving only fields that are fastest tosearch on. The only caveat to using this technique is that you must be fairly sure that you won't need to access one of the hidden table columns in the future; not an easy thing to do!

10. Take Advantage of Query Caching

The query cache stores the text of a SELECT statement together with the corresponding result set. If an identicalstatement is received later, the server retrieves the results from the query cache rather than parsing and executingthe statement again. The query cache is shared among sessions, so a result set generated by one client can be sent i nresponse to the same query issued by another client. Most MySQL servers have query caching enabled by default. It'sone of the most effective methods of improving performance.

It's a beautiful thing, but query caching isn't without limitations. Take the following statement:

SELECT emp_id,bonus_id

FROM bonusesWHERE YEAR(award_date) = Year(CURDATE());

The problem here is that queries, which contain certain non-deterministic functions - that is those which MySQL

cannot calculate in advance - like NOW() and RAND() are not cached. Fortunately, there is an easy fix to prevent thisfrom happening. That is to store the function results to a variable:

SET @year = Year(CURDATE());SELECT emp_id,

bonus_idFROM bonusesWHERE YEAR(award_date) = @year;

And that's my personal top 10 best practices in MySQL. One thing that I noticed while conducting my research for thisarticle is that there is a wide range of opinions as to which practices should be worthy of a spot in the top 10. In myestimation, there are many factors that may affect an item's weight for you, including your particular role, experience,line of business, software versions, and hardware configuration, to name but a few. I would welcome you to add yourown contributions as comments. Every tip helps!

See All Articles by Columnist Rob Gravelle


22/03/201

Documents

Dicas MySQL