Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to write efficient MySQL applications". In daily operation, I believe many people have doubts about how to write efficient MySQL applications. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the questions of "how to write efficient MySQL applications". Next, please follow the editor to study!

MySQL has a well-deserved reputation for being a very fast database server, and it is also very easy to set up and use. With its growing popularity as a back-end database of the site, its effect began to improve significantly last year. But many MySQL users know more about how to create a database and write queries against it. Just as thousands of people learn Unix by experimenting with Linux in their spare time, many people learn relational databases by playing MySQL. Most of these MySQL novices have no background in relational database theory, nor do they have time to read the full text of the MySQL manual.

Therefore, we decided to explore some ways that you can tune MySQL for optimized performance. After reading this article, you will understand some techniques to help you design your MySQL database and query, which is worth your application efficiency. We will assume that you are familiar with the basics of MySQL and SQL, but not that you have extensive knowledge of both.

Store only the information you need

This sounds like common sense, but people often use the "kitchen sewer" approach to database design. They think that everything that a possible item requires should be stored and a database designed to hold the owner's data. You need to be realistic about your needs and determine what information you really need. You can often generate some data at will without storing it in a database table. In this case, it makes sense from an application developer's point of view.

For example, the product table of an online catalog may contain the name, introduction, size, weight, and price of various products. In addition to the price, you may want to store the tax and shipping costs associated with each project. But you don't have to do that. First of all, taxes and transportation costs can be easily calculated (by your application or MySQL). Second, if taxes and shipping costs change, you may have to write the necessary queries to update the tax and shipping rates in each product record.

Sometimes people think it's too difficult to add fields to the database table later, so they feel compelled to define as many columns as possible. This is an obvious conceptual error. In MySQL, you can easily modify the table definition to suit your changing needs with the ALTER TABLE command.

For example, if you suddenly realize that you need to add a level column to your product list (maybe you want to allow users to rate products in your catalog), you can do this:

ALTER TABLE products ADD rank INTEGER

This adds an integer level column to your product list. See the MySQL manual for a complete description of what you can do with ALTER TABLE.

Only ask for what you need-- be clear

It may seem like common sense to say "store only what you need", but this is often overlooked. Why? Because requirements change frequently during an application development, many queries end up like this:

SELECT * FROM sometable

Requiring all columns is obviously the most labor-saving thing when you are not sure which column you will need, but as your table grows and modifies, this can become a performance problem. It's best to take some time after your initial development is complete and determine what you really need from your query:

SELECT name, rank, description FROM products

This leads to the related view that code maintenance is more important than performance. Most languages (Perl, Python, PHP, Java, etc.) allow access to the results of a query through field names and numbers, which means that you can access named fields or field 0 to get the same data.

In the long run, it is best to use column names rather than their numbering locations. Why? Because the relative position of the ground columns in a table or a query can be changed. They may change in the table due to repeated use of ALTER TABLE, and they will change in the query because the query has been rewritten and forgot to update the application logic to match.

Of course, you still need to be careful to change the list! But if you use a column name instead of a label location, if the column name changes, you can use grep to search the source code or use the editor's search ability to find the code you need to modify.

Standardize your table structure

If you've never heard of "data normalization" before, don't be afraid. Normalization can be a complex topic, and you can really benefit from understanding only the most basic concepts of normalization.

The easiest way to understand it is to think that your watch is a spreadsheet. If you want to track your CD collection with a report, you can design it as shown in figure 1:

Figure 1

Album track1 track2 track10

--

Billboard Top Hits-1984 Loverboy Shout St. Elmo's Fire

(Billy Ocean) (Tears for Fears) (John Parr)

It seems reasonable. Most CD only have 10 songs, right? Not really. What if you have a CD with 100 songs and a few with more than 20 songs changed. This means that in this way, in extreme cases, you will need a very wide table (or a table with more than 100 fields) to hold all the data.

The goal of normalizing the table structure is to minimize the number of "empty units". In the case of the above CD table, if you allow CD to contain 100 songs, you will have a lot of such empty units. Whenever you deal with a list of fields that may be expanded to the same number as the CD table, it is a sign that you need to split your data into two or more tables, and then you access and get the data you need.

Many novice relational databases don't really know what a relationship is in a relational database management system. In a nutshell, just as a set of information exists in different tables that can be held together based on a common data join (JOIN), unfortunately, this sounds more academic and vague, but the CD database presents a specific case where we can look at how to standardize the data.

The understanding that each CD list has a fixed set of attributes (title, artist, year, category) and an indefinite set of attributes (repertoire) gives us some ideas on how to divide them into interrelated tables.

You can create a table of all albums and their fixed attributes, and another table that contains all the tracks for these albums. Instead of thinking horizontally (like a table), you think vertically-- as if you create a list instead of a row-- and create a table structure like figure 2:

The album number (MySQL Mirror is automatically generated for you because we use the AUTO_INCREMENT attribute on the column) Associates different tracks to a given album, and the album_id field in the tracks table matches an id in the album table. To get all the tracks for a given album, you should use the following query:

QUOTE: at this point, the study on "how to write efficient MySQL applications" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.