Three practical MySQL development knowledge items _ MySQL

I want to explain the three practical development knowledge of MySQL. In fact, the bottleneck of the project application is still on the db side. with only a small amount of data and a small amount of concurrency, we don't need much skills to get the expected results, however, when the amount of data reaches a certain level, every detail of the program, the database design will affect the system performance. Here, we will discuss and analyze the topics of database development and optimization. please also improve them. here we will discuss the following topics and I will first express my own opinions.

1. storage engine selection

2. Design and Use of indexes

3. SQL statement optimization during mass insert

Storage Engine selection

Statement: The database versions used in this article are MYSQL 5. here I will compare the two storage engines MyISAM and InnoDB. The first difference is:

1. MyISAM does not support transactions and foreign keys. it has the advantage of high access speed and fast batch insertion speed. If a large number of operations are select and insert, we recommend that you use this storage engine. However, in my actual application, when batch insertion is too frequent, when the data volume reaches a certain level, the table is damaged.

2. InnoDB supports transaction processing, but it is less efficient than the former, and its indexes and data occupy more disk space. When we store some key data and need to perform transaction operations on it, we can choose innodb. of course, I don't think it should be too large.

Design and Use of indexes

A table without indexes is horrible. unless there is not much data in it, how can we design indexes reasonably? I am afraid not everyone understands that the design and use of indexes are briefly analyzed here.

1. The index is usually set to the column in The where clause. if you set the column after select, this makes no sense. Of course, you need to sort a column. the column after order by can also be indexed.

2. Using a unique index, the primary key is the best example. if you create a large number of index columns that are repeated, such as gender, such indexes will not speed up the search. As for why, please understand the indexing principles on your own.

3. if possible, limit the index length. for example, if the index column is char (100), most of the first 10 characters are unique. set the index length to 10, using short indexes can speed up queries and save hard disk space.

4. the left prefix feature of the index. in essence, the union index also creates multiple indexes. Is it better to establish the Union index or create multiple indexes separately? Obviously, the former is better. with the left prefix feature, indexes will be used as long as the leftmost column of the Union index is used.

5. of course, the last thing we should talk about is not to over-use indexes. the more indexes, the slower the insertion speed. especially when the data volume is large, a large number of indexes will consume a lot of hard disk space, unnecessary waste.

Here are a few columns to describe how to use the index:

1. left prefix of the Union index

First look at the index structure:

Code:

Mysql> show index from user;
+ ------- + ------------ + ---------- + -------------- + ------------- + ----------- + ------------- + ---------- + -------- + ------------ + ----------- +
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+ ------- + ------------ + ---------- + -------------- + ------------- + ----------- + ------------- + ---------- + -------- + ------------ + ----------- +
| User | 0 | PRIMARY | 1 | user_id | A | 2 | NULL | BTREE |
| User | 1 | user | 1 | username | A | NULL | BTREE |
| User | 1 | user | 2 | order | A | NULL | BTREE |
| User | 1 | user | 3 | email | A | NULL | YES | BTREE |
+ ------- + ------------ + ---------- + -------------- + ------------- + ----------- + ------------- + ---------- + -------- + ------------ + ----------- +
4 rows in set (0.00 sec)