Basic knowledge of MySQL database development for programmers

MySQL can be said to be the most widely used database for programmers. I will share some common knowledge about MySQL database development and the selection of storage engines, design and use of indexes and optimization of SQL statements when a large number of data is inserted. Hope to help you.

　　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:

The following is a code snippet:
Mysql> show index from user;