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

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

Table-building specification

1. "Force" fields that express the concept or not must be named as is _ xxx. The data type is unsigned tinyint (1 means yes, 0 means no).

Note: if any field is non-negative, it must be unsigned.

Positive example: the name of the field that is logically deleted is is_deleted. 1 indicates deletion and 0 indicates that it has not been deleted.

2. [force] Table names and field names must use lowercase letters or numbers, prohibit the beginning of numbers, and prohibit only numbers in the middle of two underscores. The modification of the database field name is expensive because it cannot be pre-published, so the field name needs to be carefully considered.

Note: MySQL is not case-sensitive under Windows, but it is case-sensitive by default under Linux. Therefore, database names, table names, and field names are not allowed to appear in uppercase letters.

Positive example: aliyun _ admin, rdc _ config, level 3 _ name

Counterexamples: AliyunAdmin, rdcConfig, level _ 3 _ name

3. [force] Table names do not use plural nouns.

Note: the table name should only represent the entity content in the table, not the number of entities, corresponding to the DO class name is also singular, in line with the expression habit.

4. [force] disable reserved words, such as desc, range, match, delayed, etc. Please refer to MySQL official reserved words.

5. The primary key index is called pk_ field name, the unique index name is uk _ field name, and the normal index name is idx _ field name.

Description: pk_ is primary key;uk _ that is unique key;idx _ is the abbreviation of index.

6. [force] the decimal type is decimal, and float and double are prohibited.

Note: when float and double are stored, there is a problem of loss of accuracy, and it is likely to get incorrect results when comparing the values. If the range of data stored exceeds the range of decimal, it is recommended that you split the data into integers and decimals and store them separately.

7. [force] if the length of the stored string is almost equal, use the char fixed-length string type.

8. [force] varchar is a variable-length string with no pre-allocated storage space and a length of not more than 5000. If the storage length is greater than this value, define the field type as text, and use a separate table to correspond with the primary key to avoid affecting the indexing efficiency of other fields.

9. [mandatory] three required fields for the table: id, gmt _ create, and gmt _ modified.

Note: where id must be the primary key, the type is unsigned bigint, when a single table increases itself, the step size is 1. The types of gmt_create,gmt_modified are all date_time types. The former indicates active creation, while the latter indicates passive update.

10. [recommended] the naming of the table is best to add "the role of the business name _ table".

Positive example: alipay _ task / force _ project / trade _ config

11. [recommended] the library name is consistent with the application name as much as possible.

12. [recommended] if you modify the meaning of the field or append the status of the field, you need to update the field comments in a timely manner.

13. The [recommended] field allows appropriate redundancy to improve query performance, but data consistency must be taken into account. Redundant fields should follow:

1) A field that is not modified frequently.

2) it is not a varchar super-long field, let alone a text field.

Positive example: the commodity category name is used frequently, the field length is short, and the name is basically unchanged. The category name can be stored redundant in the associated table to avoid association query.

14. [recommended] Sub-database and sub-table is recommended only if the number of rows in a single table exceeds 5 million rows or the capacity of a single table exceeds 2 GB.

Note: if it is expected that the amount of data after three years will not reach this level at all, please do not divide the database into tables when creating tables.

15. [reference] appropriate character storage length can not only save database table space and index storage, but also improve the retrieval speed.

Positive example: the following table, in which no symbolic values can avoid the misstorage of negative numbers and expand the scope of representation.

Index specification

1. [force] A field with unique characteristics in the business, even if it is a combination of multiple fields, must build a unique index.

Note: do not think that the unique index affects the speed of insert, this speed loss can be ignored, but the improvement of search speed is obvious; in addition, even if a very perfect check control is done in the application layer, as long as there is no unique index, according to Murphy's law, dirty data must be generated.

2. [force] disable join for more than three tables. For the fields that need join, the data types must be absolutely the same; when you associate queries with multiple tables, make sure that the associated fields need to be indexed.

Note: even dual-table join should pay attention to table index and SQL performance.

3. [force] when building an index on the varchar field, you must specify the index length. There is no need to index the whole field. The index length can be determined according to the actual text differentiation.

Note: the length and differentiation of an index are contradictory. Generally, for an index with a length of 20, the discrimination can be as high as 90%, which can be determined by using the discrimination of count (distinct left (column name, index length)) / count (*).

4. [mandatory] Page search is strictly forbidden to be left fuzzy or fully fuzzy. If necessary, please go to the search engine to solve the problem.

Note: the index file has the leftmost prefix matching feature of B-Tree, and this index cannot be used if the value on the left is not determined.

5. [recommended] if there is an order by scenario, please pay attention to the ordering of the index. The last field of order by is part of the composite index and is placed at the end of the index combination order to avoid the occurrence of file _ sort and affect query performance.

Positive example: where a =? And b =? Order by c; index: a _ b _ c

Counterexample: if there is a range lookup in the index, then the index ordering cannot be utilized, such as: WHERE a > 10 ORDER BY b; index a _ b cannot be sorted.

6. [recommended] overlay index is used for query operation to avoid returning to the table.

Explanation: if a book needs to know what the title of Chapter 11 is, will it turn to the corresponding page of Chapter 11? Just browse through the catalog, which plays the role of overwriting the index.

Positive example: can build the type of index: primary key index, unique index, general index, and overlay index is a kind of query effect, with explain results, extra column will appear: using index.

7. [recommended] use delayed association or subquery to optimize super-paging scenarios.

Note: MySQL does not skip the offset line, but take offset + N row, and then return to abandon the previous offset row, return N row, then when the offset is particularly large, the efficiency is very low, either control the total number of pages returned, or SQL rewrite the number of pages that exceed a specific threshold.

Positive example: first quickly locate the id segment to be obtained, and then associate it:

SELECT a.* FROM Table 1 a, (select id from Table 1 where condition LIMIT 1000000 Magazine 20) b where a.id=b.id

8. [recommended] the goal of SQL performance optimization: at least range level is required, and ref level is required. If possible, consts is the best.

Description:

1) there is at most one matching row (primary key or unique index) in a single consts table, and the data can be read in the optimization phase.

2) ref refers to the use of a normal normal index.

3) range searches the range of the index.

Counterexample: the result of the explain table, type = index, the index physical file is fully scanned, and the speed is very slow. This index level is lower than range, which is negligible compared with full table scan.

9. [recommended] when building a combined index, the one with the highest degree of differentiation is on the far left.

Positive example: if where a =? And b =?, column an is close to a unique value, so you only need to create a single idx _ an index.

Note: when there is a mixed judgment condition of non-equal sign and equal sign, please precede the column of the equal sign condition when building the index. Such as: where a >? and b =? Then even if an is more differentiated, b must be placed at the top of the index.

10. [recommended] prevent implicit conversions due to different field types, resulting in index invalidation.

11. [reference] avoid the following extreme misunderstandings when creating an index:

1) it is better to abuse than lack. Think that a query needs to build an index.

2) it is better to lack than to abuse. It is considered that the index will consume space and seriously slow down the speed of update and addition.

3) resist unique indexes. It is considered that the uniqueness of the business needs to be solved by the way of "check first and then insert" in the application layer.

SQL statement

1. [force] do not use count (column name) or count (constant) instead of count (*). Count (*) is the standard statistical row syntax defined by SQL 92, which has nothing to do with database, NULL and non-NULL.

Note: count (*) will count rows with a value of NULL, while count (column name) will not count rows with NULL values for this column.

2. [force] count (distinct col) to calculate the number of non-repeating rows in this column except NULL. Note that count (distinct col 1, col 2) if one column is all NULL, then 0 is returned even if the other column has a different value.

3. [force] when all the values of a column are NULL, the return result of count (col) is 0, but the return result of sum (col) is NULL, so you should pay attention to NPE when using sum ().

Positive example: the NPE problem of sum can be avoided by using the following ways: SELECT IF (ISNULL (SUM (g)), 0, SUM (g)) FROM table

4. [force] use ISNULL () to determine whether it is a NULL value.

Note: the direct comparison between NULL and any value is NULL.

1) the return result of NULLNULL is NULL, not false.

2) the return result of NULL=NULL is NULL, not true.

3) the return result of NULL1 is NULL, not true.

5. [force] when the paging query logic is written in the code, it should be returned directly if count is 0, so as to avoid executing the following paging statements.

6. [mandatory] Foreign keys and cascading are not allowed, and all foreign key concepts must be resolved at the application layer.

Note: take the relationship between students and grades as an example, the student _ id in the student table is the primary key, then the student _ id in the score table is the foreign key. If you update the student _ id in the student table and trigger the student _ id update in the grade table, it is a cascading update. Foreign keys and cascading updates are suitable for low concurrency on a single machine, but not for distributed and high concurrency clusters; cascading updates are strong blocking and there is a risk of database update storm; foreign keys affect the insertion speed of the database.

7. [force] prohibit the use of stored procedures, stored procedures are difficult to debug and expand, let alone portability.

8. [force] when correcting data, when deleting and modifying records, select first to avoid erroneous deletion and confirm that the update statement can be executed.

9. [recommended] if the in operation can be avoided, if it cannot be avoided, you need to carefully evaluate the number of collection elements behind the in and limit it to 1000.

10. [reference] if there is a need for globalization, all characters are stored and represented in utf-8. Note the difference between character statistics functions.

Description:

SELECT LENGTH ("easy work"); return to 12

SELECT CHARACTER _ LENGTH ("easy to work"); returns 4

If you need to store facial expressions, choose utfmb 4 to store them, and note the difference between it and utf-8 coding.

11. [reference] TRUNCATE TABLE is faster than DELETE and uses less system and transaction log resources, but TRUNCATE has no transactions and does not trigger trigger, which may cause accidents, so it is not recommended to use this statement in development code.

Description: TRUNCATE TABLE is functionally the same as a DELETE statement without a WHERE clause.

ORM mapping

1. [force] in a table query, do not use * as the list of fields for the query, and which fields are required must be clearly specified.

Description: 1) increase the parsing cost of the query analyzer. 2) the addition and subtraction fields are easily inconsistent with the resultMap configuration.

2. [force] the Boolean attribute of POJO class cannot be added with is, but the database field must be added with is _, which requires the mapping between fields and attributes in resultMap.

Description: see definition of POJO class and database field definition that it is necessary to add mappings in. In the code generated by MyBatis Generator, you need to make corresponding modifications.

3. [force] do not use resultClass as the return parameter, even if all class attribute names correspond to database fields one by one, it also needs to be defined; conversely, every table must have one corresponding to it.

Description: configure the mapping relationship to decouple the field from the DO class for easy maintenance.

4. [mandatory] sql. Xml configuration parameters use: # {}, # param # do not use ${} this way is prone to SQL injection.

5. [mandatory] queryForList (String statementName, int start, int size) included with iBATIS is not recommended.

Description: its implementation is to get all the records of the SQL statement corresponding to statementName in the database, and then take the subset of start and size through subList.

Positive example: Map map = new HashMap ()

Map.put ("start", start)

Map.put ("size", size)

6. [mandatory] it is not allowed to directly take HashMap and Hashtable as the output of the query result set.

Description: resultClass= "Hashtable", the field name and attribute value are placed, but the type of the value is not controllable.

7. [force] when updating a data table record, the corresponding gmt _ modified field value of the record must be updated to the current time.

8. [recommended] do not write a large and comprehensive data update interface. Input to the POJO class, whether it is your own target update field or not, use update table set c1quantitative value1 direction c2percent value2 and c3benchmark value3; this is not correct. When performing SQL, do not update unchanged fields, first, error-prone; second, inefficient; third, increase binlog storage.

9. [reference] @ Transactional transaction should not be abused. Transactions will affect the QPS of the database. In addition, where transactions are used, various rollback schemes need to be considered, including cache rollback, search engine rollback, message compensation, statistical correction and so on.

10. [reference]

The compareValue in is a constant compared to an attribute value, usually a number, indicating equality with this condition

Executes when it is not empty and is not null

Executes when it is not a null value.

At this point, the study of "what are the specifications of Mysql in Java programming" 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.