The lecture is divided into two parts

  1. The “Java development Manual” in the MySQL database protocol
  2. T31 design practice

Why study protocol?

  1. Standardize everyone’s behavior to facilitate communication
  2. Write some work experience into the specification to avoid development pitfalls

Note: only record what you think is important here

Build table specifications

  1. Table names do not use plural nouns

  2. Index naming rules

    • Primary key index,pk_ field name
    • Unique index,uk_ field name
    • Common index, idX_ field name

  3. Decimal type is decimal

  4. A table must have three fields: ID, create_time, and update_time

  5. If the meaning of a field is changed or the state of a field is appended, you need to update the field comment in a timely manner

The index code

  1. Business-specific fields, even composite fields, must have unique indexes

    • This is often overlooked in business practice

  2. When creating an index on a VARCHAR field, you must specify the index length

  3. Use overwrite indexes for query operations to avoid back to the table

    What is an overwrite index and what is a back table

    What is cover index, find the data can be found in the index data, such as the user in the table has a name, age, phone field, which created the index name, age, according to the user name query age went to cover index, went back to the table while checking the mobile phone number

    What does it mean to go back to the table? If you want to go back to the table, you have to go back to the table twice

  1. Optimize hyperpagination scenarios with deferred association or subqueries

    select id, name, age from user limit 1000000, 10;

    When offset is large, this paging SQL is slow, and there are two solutions

    1) Assume ids are ordered and know the value of the last ID on the previous page

    select id, name, age from user where id > 9999999 limit 10;

    2) Quickly locate the ID segment to be obtained, and then associate it

    select t1.* from table1 as t1, (select id from table1 where condition limit 100000, 10) as t2 where t1.id = t2.id

The SQL statement

  1. Count (*) is recommended.
  2. Use ISNULL() to determine whether a value ISNULL -NULL is returned for all operations that participate in the operation
  3. If the in operation can be avoided, it cannot be avoided to control the following set element data within 1000

The ORM mapping

  1. Never use * in queries

    • Increased query parser parsing costs
    • Adding or subtracting fields may be inconsistent with the resultMap configuration
    • Useless fields increase network consumption
    • You can use overwrite indexes

  2. Pojo class Boolean attributes cannot be added with IS, whereas database fields must be added with IS_, so fields and attributes are mapped in a resultMap

  3. Don’t write a large, comprehensive data update interface

    • error-prone
    • Low efficiency
    • Add binlog storage

T31 design for actual combat

Design ideas

  1. Demand analysis

    • Extract entities and attributes from nouns
    • Extract relationships from verbs

  2. Draw the e-r diagram

    • Entities correspond to a table
    • If the relationship is many-to-many it also corresponds to a table

  3. Physical figure paintings