Detailed description of MySQL three practical development knowledge

In fact, the bottleneck of project applications is still on the db end. With only a small amount of data and a small amount of concurrency, we don't need much skill to get the desired 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 share with you the topic of database development and optimization.

In fact, the bottleneck of project applications is still on the db end. With only a small amount of data and a small amount of concurrency, we don't need much skill to get the desired 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 share with you the topic of database development and optimization.

In fact, the bottleneck of project applications is still on the db end. With only a small amount of data and a small amount of concurrency, we don't need much skill to get the desired 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,The advantage is 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,In addition, 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 | NULL   || BTREE|   |
| user  |    1 | user     |1 | username    | A   |  NULL |     NULL | NULL   || BTREE|   |
| user  |    1 | user     |2 | order | A   |  NULL |     NULL | NULL   || BTREE|   |
| user  |    1 | user     |3 | email | A   |  NULL |     NULL | NULL   | YES  | BTREE|   |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.00 sec)

User is the name of the Union Index, which contains three columns: username, order, and email. Next, execute the following SQL statement and use the explain command to analyze the running result.

Code:

mysql> explain select * from user where username='leehui'; +----+-------------+-------+------+---------------+------+---------+-------+------+--------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+--------+ | 1 | SIMPLE| user | ref | user | user | 152 | const | 1 | Using where | +----+-------------+-------+------+---------------+------+---------+-------+------+--------+ 1 row in set (0.00 sec) mysql> explain select * from user where pws='123'; +----+-------------+-------+------+---------------+------+---------+------+------+---------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+---------+ | 1 | SIMPLE| user | ALL | NULL | NULL | NULL | NULL | 2 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+---------+ 1 row in set (0.00 sec)

In the two SQL statements, we can find that, although the first SQL statement is useless, all index columns are used. However, because the leftmost column is used, the combined index is enabled, the second sentence does not use the leftmost column, so the index is not used.

2. About the like keyword

For queries using like, note that only % of the column is not indexed with the first character. The following shows the queries using like. The first is that the index is used, and the second is that the index is not used.

Code:

mysql> explain select * from user where username like'lee%'; +----+-------------+-------+-------+---------------+------+---------+------+------+---------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+---------+ | 1 | SIMPLE| user | range | user | user | 152 | NULL | 1 | Using where | +----+-------------+-------+-------+---------------+------+---------+------+------+---------+ 1 row in set (0.00 sec) mysql> explain select * from user where username like'%lee'; +----+-------------+-------+------+---------------+------+---------+------+------+----------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+----------+ | 1 | SIMPLE| user | ALL | NULL | NULL | NULL | NULL | 2 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+----------+ 1 row in set (0.00 sec)

3. View index usage

Run the following command:

mysql> show status like 'Handler_read_key'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | Handler_read_key | 0 | +------------------+-------+ 1 row in set (0.00 sec)

If the index is working, Handler_read_key will be very high. If the value of Handler_read_rnd_next in the query is very high, it indicates that the query is inefficient and the application of the index is not reasonable.

SQL statement optimization during mass Inserts

When a large number of inserts, especially concurrent inserts, mysql often has to bear a higher load. You can use the mysql administortar health check to find that its avg value is quite high, in this case, the first thing to do is to optimize the SQL statement. Compare the following two sentences, and the latter is much faster than the former. Because a large number of connections are reduced.

Copy the content to the clipboard code:

insert into test values(aa,bb) insert into test values(cc,dd) insert into test values (aa),(bb),(cc),(dd)

In one of my practical applications, I often use insert delayed into to replace insert into because hundreds of concurrent inserts are required, the difference between the former and the latter is that when the insert statement is executed, data is stored in the memory queue and executed when the database is idle, but a message indicating successful insertion is returned immediately. When using insert delayed into, note that mysql_insert_id () cannot be used because no insert is performed. This statement is not suitable for especially important data to avoid data loss.

Miscellaneous

1. mysql myisam table cannot be accessed after 4 GB

The myisam engine supports 4 GB by default, and innodb can theoretically reach 6 TB. If the size of a single table exceeds 4 GB, all tables may be inaccessible. Run the following command to increase the maximum data volume of a table:
Copy content to clipboard
Code:

mysql> alter table user MAX_ROWS=1000000000 AVG_ROW_LENGTH=15000; Query OK, 2 rows affected (0.09 sec) Records: 2 Duplicates: 0 Warnings: 0

In this way, the data file can be about TB after modification.