A partial collation of MySQL database optimization

I wrote an article about this before.

Why does "original" use data indexing to improve efficiency? (This article provides an overview of MySQL) (update)

This time, the main focus is on two common storage engines.

We generally perform MySQL database optimization from two aspects:

A, optimization of SQL statements.

In this case, we need to pay special attention when we write SQL, and pay great attention when we are building the table.

1 Try not to perform the operation on the column, which will invalidate the index.

2 Using a join, you should use a small result set to drive a large result set. Split the complex join into multiple query. When multiple joins, it is most likely to lead to lock tables and blockages.

3 Avoid using a percent of percent when using like for fuzzy queries. This will invalidate the index even if there is an index.

Such as:

SELECT * from TABLENAME WHERE name like '%wang% '

Need to be optimized:

SELECT * from TABLENAME WHERE name >= ' Wang ' and name < ' Wanh '

In this way, the index will be used. The premise is that you have added an index to the column name.

4 saves memory by listing only the fields of the query. If the field is not many, you can also use *

5 Save interactivity with BULK INSERT statements.

6 when the limit base is larger, use between. Such as

SELECT * from TABLENAME LIMIT 100000,10

Optimization under

SELECT * from TABLENAME between 100000,100010

However, there is a problem with between, if there is a deletion in the middle, then the data must be less

7 when fetching multiple random records, do not use Rand (). You can generate random numbers in PHP and then use the in

8 Avoid using NULL.

9 Do not use COUNT (ID), use COUNT (*)

10 do the related sort work in the index as much as possible

In the key field, it is worth appearing in the Where column, which is very different from building an index. The query speed is nearly 100 times times the difference

It's not useful to build an index, but a poor index can cause a slow query.

The more indexes you have, the better, the cost of MySQL maintenance index

B Server-related optimizations

1 Selecting the Storage engine

Whether to choose MyISAM or InnoDB depends on your situation.

First understand the difference between the two: Mysiam table-level lock. There is no transaction mechanism. Read fast. InnoDB supports transactional, row-level locks. InnoDB is a row-level lock, which can result in significant consumption relative to table-level locks. However, in the case of large system concurrency, performance is higher than Mysiam. The index of InnoDB not only caches the index itself but also caches the data.

InnoDB requires more memory support. However, hardware input is now relatively inexpensive.

In this place need to know a r/w read and write ratio. Show Global Status View Com_select represents the number of times the SELECT statement was executed, Com_insert represents the number of times the INSERT statement was executed. By calculating the statement scale of the read type and write type. We probably get a read-write ratio.

The ideal situation is 100:1, when r/w is less than 10:1, it is written as the main. Generally, this value is 30:1

We give the principle of a storage engine choice:

1 with MyISAM

A r/w > 100:1 and less update

b Concurrency not high no transaction required

Low data size in C table

D Limited hardware resources such as small memory

2 with InnoDB

a r/w < 20:1 OR < 10:1 and update frequent

b table Data volume is large, about 10 million of the concurrency of large

C High Security and availability. such as the transaction mechanism

A partial collation of MySQL database optimization