Optimize the overview

  • Take certain steps to become excellent.
  • In order to be more excellent and “discard the dross, take the essence”;
  • Discarding the dregs in order to be better at something;
  • Giving up something less important in order to be better at something;
  • Ways/techniques, etc to make sb/STH better;
  • In the field of computer algorithms, optimization usually refers to the algorithm to get a better solution to the required problem

An SQL-inspired thought

This is an SQL report query, I find it is very slow to download the Excel report, please optimize it

Common optimization methods

  1. Server hardware
  2. MySql server optimization
  3. SQL optimization by itself
  4. Anti-paradigm design optimization
  5. Physical design optimization (field type, length design, storage engine selection)
  6. The index optimization

Antiformal design

  1. Antiformalization is about formalization
  2. De-canonization is an appropriate violation of the requirements of the database design paradigm for performance and read efficiency
  3. A small amount of redundancy is allowed, in other words antiparadigm is the use of space in exchange for time

The three major paradigm

The first paradigm of database design

  1. All fields in a database table have a single attribute
  2. Single-attribute columns are composed of primitive data types
  3. The tables designed are simple two-dimensional tables

The second paradigm of database design

A table is required to have only one business primary key, that is, a table conforming to the second normal form cannot have a dependency on only partial primary keys for non-primary key columns

The third paradigm of database design

It means that each non-non-primary attribute is neither partially dependent nor transitively dependent on the business primary key, i.e. transitively dependent on the non-primary key to the primary key on a second normal form basis

Physical design

Select the appropriate data types for the fields in the table

When multiple data types can be selected for a single column

  1. Numerical types are preferred
  2. Next are date and time types
  3. Finally, there are character types
  4. For data types of the same level, the data types that occupy less space should be preferred

What is the index?

An Index is a data structure that helps MySQL obtain data efficiently. You get the essence of an index: an index is a data structure.

The simplest index

A slightly more complex index

MySql > create index

Above is the data table, with two columns and seven records. The one on the far left is the physical address of the data record

The index classification

Basic grammar

End of this article! Like this article friends help forward and attention, thank you!

PS: This article complete video to obtain address: Java learning, interview; Obtain documents and videos for free