Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what are the skills in using MySQL". In daily operation, I believe that many people have doubts about the skills in using MySQL. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful for you to answer the doubts about "what are the skills in using MySQL?" Next, please follow the editor to study!

MySQL naming convention

1. Library names, table names and field names must be in lowercase letters and separated by underscores.

A) MySQL has a configuration parameter lower_case_table_names, which cannot be changed dynamically. The default value of Linux system is 0, that is, the database table name is stored in the actual situation and is case-sensitive. If it is 1, it is stored in lowercase and is case-insensitive. If it is 2, store it in reality, but compare it in lowercase.

B) if the case is mixed, multiple tables such as abc, Abc, ABC and so on may coexist, which can easily lead to confusion.

C) the field name display is case-sensitive, but actually makes it indistinguishable, that is, it is not possible to create two fields with the same name but different case.

D) for uniform specification, library names, table names, and field names are in lowercase letters.

2. Library name, table name and field name are prohibited from exceeding 32 characters.

Library names, table names, and field names support up to 64 characters, but more than 32 characters are prohibited in order to standardize, easy to identify and reduce the amount of transmission.

3. MySQL reserved words are prohibited for library names, table names and field names.

When attributes such as library name, table name, field name, etc., contain reserved words, the SQL statement must refer to the attribute name in reverse quotation marks, which will make the writing of SQL statements and the escape of variables in SHELL scripts very complicated.

Skills of using MySQL

1. Split the fields with large fields and low access frequency into separate tables for storage, and separate hot and cold data.

It is conducive to the effective use of cache, prevent reading of useless cold data, less disk IO, and ensure that hot data is resident in memory and high cache hit rate.

2. The table must have a primary key. It is recommended to use the UNSIGNED auto-increment column as the primary key.

Tables do not have primary keys, and INNODB sets hidden primary key columns by default; tables without primary keys are very difficult to locate data rows and reduce the efficiency of row-based replication.

3. Redundant indexes and duplicate indexes are prohibited.

Index is a double-edged sword, which will increase the maintenance burden and IO pressure. For redundant indexes, prefix indexes can be used to speed up and reduce the maintenance burden. Primary key a share Uniq index a; duplicate indexes increase the maintenance burden and take up disk space without any benefit.

4. Use the overlay index reasonably to reduce IO and avoid sorting.

The overlay index can get all the required fields from the index, so as to avoid secondary lookup back to the table and save IO.

In the INNODB storage engine, secondary index (non-primary key index, also known as secondary index, secondary index) does not directly store row addresses, but primary key values.

If users need to query data columns that are not included in secondary index, they need to find the primary key value through secondary index, and then query other data columns through the primary key, so they need to query twice. The overlay index can get all the needed data in one index, so it is more efficient.

For example, SELECT email,uid FROM user_email WHERE uid=xx, if uid is not the primary key, you can add the index as index (uid,email) in due course to achieve a performance improvement.

At this point, the study of "what are the use skills in MySQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.