Shulou(Shulou.com)06/01 Report--

1. Select the most applicable field attribute

MySQL can well support access to a large amount of data, but in general, the smaller the table in the database, the faster the query will be executed on it.

Therefore, in order to achieve better performance when creating the table, we can set the width of the fields in the table as small as possible.

For example, when defining the zip code field, if you set it to CHAR, it obviously adds unnecessary space to the database.

Even using the type VARCHAR is superfluous, because CHAR (6) can accomplish the task very well.

Similarly, if possible, we should use MEDIUMINT instead of BIGIN to define integer fields

Another way to improve efficiency is to set the field to NOTNULL whenever possible, so that the database does not have to compare null values when executing queries in the future. For some text fields, such as "province" or "gender", we can define them as ENUM types. Because in MySQL, ENUM types are treated as numeric data, and numeric data is processed much faster than text types

In this way, we can improve the performance of the database.

2. Use JOIN to replace subquery (Sub-Queries)

MySQL has supported SQL's subqueries since 4. 1. This technique can use the select statement to create a single-column query result, which can then be used as a filter condition in another query. For example, if we want to delete a customer without any order in the customer basic information table, we can use a subquery to extract all the customer ID that placed the order from the sales information table, and then pass the result to the main query, as shown below:

DELETEFROMcustomerinfo

WHERECustomerIDNOTin (SELECTCustomerIDFROMsalesinfo)

Using subqueries, you can complete many SQL operations that logically require multiple steps to complete at one time, while avoiding transaction or table locking.

And it's easy to write. However, in some cases, subqueries can be JOIN more efficiently. Substitution

For example, suppose we want to take out all the users who do not have an order record, we can complete it with the following query:

SELECT*FROMcustomerinfo

WHERECustomerIDNOTin (SELECTCustomerIDFROMsalesinfo)

If you use connection (JOIN).. To complete this query, it will be much faster.

Especially when there is an index on CustomerID in the salesinfo table, the performance will be better, as shown in the query:

SELECT*FROMcustomerinfo

LEFTJOINsalesinfoONcustomerinfo.CustomerID=salesinfo.CustomerID

WHEREsalesinfo.CustomerIDISNULL

Connect (JOIN).. The reason why it is more efficient

Because MySQL does not need to create temporary tables in memory to complete this logically two-step query.

3. Use UNION instead of manually created temporary tables

MySQL has supported union queries since version 4.0, which can merge two or more select queries that need to use temporary tables in a single query. At the end of the client query session, the temporary table is automatically deleted to ensure that the database is neat and efficient. When using union to create a query, we only need to use UNION as the keyword to concatenate multiple select statements, and note that the number of fields in all select statements should be the same

The following example demonstrates a query that uses UNION

SELECTName,PhoneFROMclientUNION

SELECTName,BirthDateFROMauthorUNION

SELECTName,SupplierFROMproduct

4. Transaction

Although we can use subqueries (Sub-Queries), JOIN (join), and UNION (UNION) to create a variety of queries, not all database operations can be done with one or a few SQL statements. More often, you need to use a series of statements to accomplish some kind of work. But in this case, when one of the statements in this block goes wrong, the operation of the whole block becomes uncertain. Imagine that if you want to insert some data into two associated tables at the same time, it may happen that after a successful update in the first table, there is a sudden unexpected condition in the database, resulting in the operation in the second table not being completed, thus resulting in incomplete data and even destroying the data in the database. To avoid this, you should use transactions, which either succeed or fail for every statement in the block. In other words, you can maintain the consistency and integrity of the data in the database. Things start with the BEGIN keyword and end with the COMMIT keyword. A SQL operation failed in between

Then, the ROLLBACK command can restore the database to the state it was before BEGIN started.

BEGIN; INSERTINTOsalesinfoSETCustomerID=14;UPDATEinventorySETQuantity=11WHEREitem='book';COMMIT

Another important role of transactions is when multiple users use the same data source at the same time

It can use the method of locking the database to provide a secure access way for users, which can ensure that the operation of users will not be disturbed by other users.

5. Lock the table

Although transaction is a very good way to maintain database integrity, it sometimes affects the performance of database because of its exclusivity, especially in large application systems. Because the database will be locked during the execution of the transaction, other user requests can only wait temporarily until the transaction ends. If a database system is used by only a few users, the impact of transactions will not be a big problem; but suppose there are thousands of users accessing a database system at the same time, such as an e-commerce website. there will be serious response delays.

In fact, in some cases we can get better performance by locking the table.

The following example uses the locking table method to complete the function of the transaction in the previous example.

LOCKTABLEinventoryWRITESELECTQuantityFROMinventoryWHEREItem='book'

...

UPDATEinventorySETQuantity=11WHEREItem='book';UNLOCKTABLES

Here, we use a select statement to take the initial data, and through some calculations, use the update statement to update the new value into the table

A LOCKTABLE statement that contains the WRITE keyword ensures that before the UNLOCKTABLES command is executed

There will be no other access to insert, update or delete inventory

6. Use foreign keys

The method of locking the table can maintain the integrity of the data, but it cannot guarantee the relevance of the data. At this point, we can use foreign keys.

For example, foreign keys can ensure that each sales record points to an existing customer

Here, foreign keys can map the CustomerID in the customerinfo table to the CustomerID in the salesinfo table

Any record without a legitimate CustomerID will not be updated or inserted into the salesinfo

CREATETABLEcustomerinfo (CustomerIDINTNOTNULL,PRIMARYKEY (CustomerID)) TYPE=INNODB;CREATETABLEsalesinfo (SalesIDINTNOTNULL,CustomerIDINTNOTNULL

PRIMARYKEY (CustomerID,SalesID)

FOREIGNKEY (CustomerID) REFERENCEScustomerinfo (CustomerID) ONDELETECASCADE) TYPE=INNODB

Notice the parameter "ONDELETECASCADE" in the example. This parameter ensures that when a customer record in the customerinfo table is deleted

All records related to the customer in the salesinfo table are also automatically deleted. If you want to use foreign keys in MySQL

It is important to remember that when creating a table, define the type of the table as the transaction safety table InnoDB type

This type is not the default type for MySQL tables

The method of definition is to add TYPE=INNODB to the CREATETABLE statement

7. Use the index

Indexing is a common way to improve database performance, which enables the database server to retrieve specific rows much faster than without an index

Especially when the query contains commands such as MAX (), MIN () and ORDERBY, the performance improvement is more obvious.

Which fields should be indexed?

In general, indexes should be based on fields that will be used for JOIN,WHERE judgment and ORDERBY sorting

Try not to index a field in the database that contains a large number of duplicate values.

For a field of type ENUM, it is possible to have a large number of duplicate values

For example, "province" in customerinfo.. Field, indexing on such a field will not help; on the contrary, it may degrade the performance of the database. We can create the appropriate index at the same time as we create the table, or we can use ALTERTABLE or CREATEINDEX to create the index later. In addition, MySQL supports full-text indexing and search since version 3.23.23. A full-text index is an index of type FULLTEXT in MySQL, but can only be used for tables of type MyISAM. For a large database, it would be very fast to load the data into a table without an FULLTEXT index, and then create an index using ALTERTABLE or CREATEINDEX. If the data is loaded into a table that already has an FULLTEXT index, the execution process will be very slow

8. Optimized query statement

In most cases, using an index can improve the speed of the query

However, if the SQL statement is not used properly, the index will not play its due role.

Here are a few aspects that should be paid attention to.

First of all, it is best to compare fields of the same type.

Before the MySQL3.23 version, this was even a necessary condition

For example, you cannot compare an indexed INT field to a BIGINT field, but as a special case

When fields of type CHAR and field of type VARCHAR are the same size, they can be compared

Second, try not to use functions on indexed fields.

For example, using the YEAE () function on a field of type DATE will prevent the index from working as it should

Therefore, the following two queries return the same results, but the latter is much faster than the former.

Third, when searching for character fields, we sometimes use LIKE keywords and wildcards

Although this is simple, it is also at the expense of system performance.

For example, the following query will compare each record in the table.

SELECT*FROMbooks

WHEREnamelike "MySQL%"

But if you switch to the following query, the result is the same, but much faster:

SELECT*FROMbooks

WHEREname > = "MySQL" andname < "MySQM"

Finally, care should be taken to avoid letting MySQL do automatic type casting in the query, because the conversion process can also render the index ineffective.

1. Optimize your MySQL query cache

Query caching can be enabled by querying on the MySQL server. Letting the database engine process quietly in the background is one of the most effective ways to improve performance

When the same query is executed multiple times, it is quite fast if the result is extracted from the cache. But the main problem is that it's so easy to hide.

So much so that most of our programmers ignore it. In some processing tasks, we can actually prevent query caching from working

1. / / query cache does NOT work

2. $r = mysql_query ("SELECT username FROM user WHERE signup_date > = CURDATE ()")

3.

4. / / query cache works!

5. $today = date ("Y-m-d")

6. $r = mysql_query ("SELECT username FROM user WHERE signup_date > ='$today'")

7.

8. / / query cache does NOT work

9. $r = mysql_query ("SELECT username FROM user WHERE signup_date > = CURDATE ()")

10.

11. / / query cache works!

12. $today = date ("Y-m-d")

13. $r = mysql_query ("SELECT username FROM user WHERE signup_date > ='$today'")

two。 Use EXPLAIN to make your SELECT query clearer

Using the EXPLAIN keyword is another MySQL optimization technique that lets you know what kind of query operation MySQL is doing.

This can help you identify the bottleneck and show what went wrong with the query or table structure.

The results of the EXPLAIN query can tell you which indexes are being referenced, how the tables are scanned and sorted, and so on.

Implement a SELECT query (preferably a more complex one with joins mode) and add your keyword explanation to it

Here we can use phpMyAdmin, which will tell you the results in the table. For example, if I am executing joins

I'm forgetting to add a column to an index. EXPLAIN can help me find the problem.

After adding an index to group_id field

3. Use LIMIT 1 to get a unique line

Sometimes, when you want to query a table, you know you only need to look at a row. A very unique record that you might go to.

Or just happens to check the number of records that exist, and they all satisfy your WHERE clause.

In this case, adding a LIMIT 1 will make your query more efficient

In this way, the database engine will stop scanning when it finds only 1, instead of scanning the entire table or index.

1. / / do I have any users from Alabama?

2. / / what NOT to do:

3. $r = mysql_query ("SELECT * FROM user WHERE state = 'Alabama'")

4. If (mysql_num_rows ($r) > 0) {

5. / /...

6.}

7. / / much better:

8. $r = mysql_query ("SELECT 1 FROM user WHERE state = 'Alabama' LIMIT 1")

9. If (mysql_num_rows ($r) > 0) {

10. / /...

11.}

4. Retrieval fields in the index

An index is not just a primary or unique key. If you want to search for any column in the table, you should always point to the index

5. Ensure that the index of the connection is of the same type

If your application contains multiple join queries, you need to make sure that your linked columns are indexed on both sides of the table

This affects how MySQL optimizes inner join operations. In addition, the added columns must be of the same type

For example, you add a DECIMAL column while adding an int column in another table

MySQL will not be able to use at least one of these metrics. Even if the character encoding must be of the same string type.

1. / / looking for companies in my state

2. $r = mysql_query ("SELECT company_name FROM users

3. LEFT JOIN companies ON (users.state = companies.state)

4. WHERE users.id = $user_id ")

5.

6. / / both state columns should be indexed

7. / / and they both should be the same type and character encoding

8. / / or MySQL might do full table scans

6. Do not use the BY RAND () command

This is a trap that many novice programmers will fall into. You may unwittingly create a terrible peace.

This trap is created when you use the BY RAND () command.

If you really need to display your results at random, there are many better ways to do so

It's true that this requires more code, but it avoids performance bottlenecks.

The problem is that MySQL may execute the BY RAND () command for each separate row in the table

(this consumes the processing power of the processor), and then returns you only one row.

1. / / what NOT to do:

2. $r = mysql_query ("SELECT username FROM user ORDER BY RAND () LIMIT 1")

3. / / much better:

4. $r = mysql_query ("SELECT count (*) FROM user")

5. $d = mysql_fetch_row ($r)

6. $rand = mt_rand (0penny [0]-1)

7.

8. $r = mysql_query ("SELECT username FROM user LIMIT $rand, 1")

7. Avoid SELECT * commands as much as possible

The more data you read from the table, the slower the query becomes. He increased the amount of time the disk needed to operate.

Or if the database server is separate from the WEB server

You will experience a very long network delay simply because data is transferred unnecessarily between servers.

It is a very good habit to always specify the columns you need.

1. / / not preferred

2. $r = mysql_query ("SELECT * FROM user WHERE user_id = 1")

3. $d = mysql_fetch_assoc ($r)

4. Echo "Welcome {$d ['username']}"

5. / / better:

6. $r = mysql_query ("SELECT username FROM user WHERE user_id = 1")

7. $d = mysql_fetch_assoc ($r)

8. Echo "Welcome {$d ['username']}"

9. / / the differences are more significant with bigger result sets

8. Get advice from PROCEDURE ANALYSE ()

PROCEDURE ANALYSE () allows MySQL's column structure analysis and the actual data in the table to give you some advice.

If you already have actual data in your table, it can serve your important decisions.

9. Prepared statement

Prepared statements can help you both in terms of performance optimization and security.

The prepared statement can effectively protect the application from SQL injection by filtering bound variables by default.

Of course, you can also filter manually, but because of the forgetful character of most programmers, it is difficult to achieve results.

1. / / create a prepared statement

2. If ($stmt = $mysqli- > prepare ("SELECT username FROM user WHERE state=?")) {

3. / / bind parameters

4. $stmt- > bind_param ("s", $state)

5. / / execute

6. $stmt- > execute ()

7. / / bind result variables

8. $stmt- > bind_result ($username)

9. / / fetch value

10. $stmt- > fetch ()

11. Printf ("% s is from% s\ n", $username, $state)

12. $stmt- > close ()

13.}

10. Store IP addresses as unsigned integers

When creating a VARCHAR (15), many programmers do not realize that they can store IP addresses as integers

When you have an INT type, you only take up 4 bytes of space, which is a fixed size field.

You must make sure that the column you are operating on is of type UNSIGNED INT, because the IP address will use 32-bit unsigned integer

$r = "UPDATE users SET ip = INET_ATON ('{$_ SERVER ['REMOTE_ADDR']}') WHERE user_id = $user_id"

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.