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

This article mainly introduces "what are the MySQL protocols in Java development". In daily operation, I believe many people have doubts about the MySQL protocol in Java development. 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 doubts about "what are the MySQL protocols in Java development?" Next, please follow the editor to study!

First, the schedule agreement

Fields that express the concept of yes or no must be named as is_xxx. The data type is unsigned tinyint (1 means yes, 0 means no). This rule also applies to odps table creation.

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

[mandatory] Table and field names must use lowercase letters or numbers; the beginning of numbers is prohibited, and only numbers are prohibited between the 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.

Positive example: getter_admin,task_config,level3_name

Counterexample: GetterAdmin,taskConfig,level_3_name

[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.

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

[mandatory] the only index name is the uk_ field name; the normal index name is the idx_ field name.

Description: uk_ is the abbreviation of unique key;idx_ that is index.

[mandatory] 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.

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

[mandatory] 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, the field type is defined as text, and a separate table is created, corresponding with a primary key to avoid affecting the efficiency of other field indexes.

[mandatory] three required fields for the table: id, gmt_create, and gmt_modified.

Note: id must be the primary key, the type is unsigned bigint, when a single table is self-increasing, the step size is 1. The types of gmt_create,gmt_modified are all date_time types.

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

Positive example: tiger_task / tiger_reader / mpp_config

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

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

The [recommended] field allows appropriate redundancy to improve performance, but data synchronization 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.

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

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.

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

Positive example: the age of a person is unsigned tinyint; the sea turtle must be smallint, but if it is the age of the sun, it must be int;. If all the stars add up, then bigint must be used.

II. Index specification

[mandatory] Fields with unique characteristics in business, even combined fields, must be unique indexes.

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 checksum 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.

[force] disable join for more than three tables. For fields that need join, the data types are absolutely consistent; when you associate a query 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.

[force] when building an index on a varchar field, you must specify the index length. There is no need to index the whole field. The index length is 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 (*).

[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, so this index cannot be used if the value on the left is not determined.

[recommended] if there is an order by scenario, please take advantage of 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 axiom? And baked? 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 axib cannot be sorted.

[recommended] use overlay index for query operation to avoid table return operation.

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.

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

Note: MySQL does not skip the offset row, but take the offset+N row, and then return to abandon the previous offset row, return N rows, 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 Magne20) b where a.id=b.id

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

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 explain table, type=index, index physical files are scanned completely, and the speed is very slow. This index level is lower than that of range, which is negligible compared with full table scanning.

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

Positive example: if where axiom? And bounded?, column an is almost close to a unique value, so you only need to build a single idx_a 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 baked? Then even if an is more differentiated, b must be placed at the top of the index.

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

1) mistakenly think that an index is needed for a query.

2) mistakenly think that the index will consume space and seriously slow down the speed of updates and additions.

3) it is mistakenly believed that all unique indexes need to be solved by "check first and insert later" at the application layer.

III. SQL Protocol

[force] do not use count (column name) or count (constant) instead of count (). Count () is the standard syntax for counting rows defined by SQL92, independent of 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.

[force] count (distinct col) calculates the number of non-repeats for the column except NULL. Note that count (distinctcol1, col2) if one of the columns is all NULL, then 0 is returned even if the other column has a different value.

[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 in the following ways: SELECT IF (ISNULL (SUM (g)), 0meme sum (g)) FROM table

[force] use ISNULL () to determine whether it is a NULL value. Note: the direct comparison of NULL to any value is NULL.

Description:

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.

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

[mandatory] Foreign keys and cascading must not be used, and all foreign key concepts must be solved at the application layer.

Explanation: (conceptual explanation) the student_id in the student table is the primary key, then the student_id in the grade sheet is the foreign key. If you update the student_id in the student table and trigger the student_id update in the score sheet, 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.

[mandatory] the use of stored procedures is prohibited, and stored procedures are difficult to debug and extend, let alone portable.

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

[recommended] if the in operation can be avoided, it should be avoided. If it cannot be avoided, the number of collection elements behind the in should be carefully evaluated and controlled within 1000.

[reference] if there is a need for globalization, all characters are stored and represented in utf-8, then note the character counting method:

Description:

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

SELECT CHARACTER_LENGTH ("easy work"); returns 4

If you want to use emoticons, use utfmb4 to store them, and note the difference between it and utf-8 coding.

[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.

IV. ORM Protocol

[mandatory] 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.

[mandatory] the boolean attribute of the 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.

Note: see definition of POJO class and database field definition, it is necessary to add mapping to sql.xml.

[force] do not use resultClass as the return parameter, even if all class property 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.

[force] pay attention to the use of parameters in xml configuration: # {}, # param# do not use ${} this way is prone to SQL injection.

[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,size through subList. OOM has appeared online for this reason.

Positive example: introduce # start#, # size# into sqlmap.xml

Map map = new HashMap ()

Map.put ("start", start)

Map.put ("size", size)

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

[force] when updating a data table record, the corresponding gmt_modified field value of the record must also be updated to the current time.

[recommended] do not write a large and comprehensive data update API, pass it to the POJO class, regardless of whether it is your own target update field or not, use update table set C1 updated value1, c2 updated value2, c3 updated value3; this is not correct. When performing SQL, try not to update unchanged fields. First, it is error-prone; second, it is inefficient; third, binlog increases storage.

[reference] @ Transactional transactions 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.

The compareValue in [reference] is a constant compared with the attribute value, usually a number, which means this condition is taken when it is equal; it is executed if it is not empty and is not null; it is executed when it is not null.

At this point, the study of "what are the MySQL protocols in Java development" 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.