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

This article mainly explains "the function of MySQL and the method of performance tuning". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "the function of MySQL and the method of performance tuning".

1. Type of multi-table query

External join: including LEFT/RIGHT/FULL (not supported by fully connected MySQL), which returns all data of at least one table

Internal connection: INNER JOIN... ON = WHERE

Contains self-join (single table query), equivalent join (condition =), unequal join (condition is not =)

Merge query: through the keyword UNION to achieve, multiple SELECT statements query multiple tables into a single table!

II. Stored procedures

Execute a set of SQL statements as a whole (indirectly ensuring the atomicity of the transaction)

Transaction ACID: atomicity, consistency, isolation, persistence

IV. Problems and isolation levels of transactions

1. Multithreaded transactions may occur: dirty reading, non-repeatable reading, phantom reading.

2. Transaction isolation level: read uncommitted, read committed, repeatable, serializable

V. locking mechanism

Exclusive lock: FOR UPDATA (pessimism lock)

Shared lock: LOCK IN SHARE MODE (optimistic lock)

VI. Index mechanism

The index optimization mechanism is very simple. The columns that are defined as indexes will be loaded into memory in advance (the storage mode can be B-tree or hash, etc.). Then, when querying, go to the index column of memory first to query. After querying the corresponding row, get the row, and then go to the IO query on disk to extract data. It is certainly faster to match conditions in memory than on disk!

1. According to the storage type, it can be divided into B-tree index (InnoDB) and hash index (MEMORY).

two。 Index category

0) General index

1) unique index (UNION INDEX): the value of the restricted index must be unique

2) full-text index (FULL TEXT): mainly related to the data type: CHAR/VARCHAR/TXET field, so that you can query fields of string type with a large amount of data!

3) Multi-column index: associate multiple fields! But multi-column indexes are enabled only if the first field is used in the query!

VII. MySQL performance optimization

1. Use indexes, but do not build indexes for tables that are not often queried, because this will aggravate performance!

two。 Use cach

3. Avoid using SELECT * (do not query more columns than required)

4. It is customary to use EXPLAIN to view the execution of query statements,

5. Using the storage engine is much faster than executing one statement at a time!

6. Using UNION is much faster than OR for multi-conditional merge queries!

7. LIKE is very inefficient, use FULL TEXT index instead!

8. Choose the right storage engine: in most cases, choosing the default InnoDB is correct!

9. Carefully design data types

1) smaller ones are usually better, and if the values to be written are small, there is no need to give them a long data type and word length

2) simple ones are better: for example, shaping 0,1 means faster than correct and incorrect characters.

3) avoid NULL as much as possible: set it to NOT NULL (because it is related to the use of the index and will make the index effective)

10. Partition table: at that time, when the data is very large, it is necessary to partition the data into separate tables, otherwise the query performance of the full table will be very slow!

11. View: stores the results of the SELECT statement (which can be very complex in a multi-table query) into a temporary table. When you need to access the view, you can access the temporary table directly! (equivalent to caching)

12. Optimized query of SQL statement!

At this point, I believe that you have a deeper understanding of "the function of MySQL and the method of performance tuning". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.