MySQL Daily optimization details

# SQL statement Optimization
>
1) Limit the amount of data that is queried once
2) Link query instead of subquery
3) Try not to use SELECT * To list the fields you need to find
4) If the amount of data is particularly large, try to split a complex SQL into multiple SQL completion functions
5) ...
# Choose the right data type

> (1) using the smallest data type that can save data, integer < Date,time < Char,varchar < blob
(2) With simple data types, integers are less expensive than character processing because string comparisons are more complex. For example, the type int stores the time type, the bigint type goes to the IP function
(3) A fixed-length table is faster with a reasonable field property length. Use enum, char instead of varchar
(4) Use NOT NULL to define fields whenever possible
(5) as little as possible with text, not the best sub-table

# Select the appropriate index column
> (1) Queries for frequent columns, columns that appear in the Where,group by,order by,on clause
(2) <,<=,=,>,>=,between,in in the Where condition, and columns that appear as the like string + wildcard character (%)
(3) A column with a small length, the smaller the index field, the better, because the storage unit of the database is the page, the more data you can save in one page, the better
(4) A column with a large dispersion (with many different values) placed in front of a federated index. View the dispersion by counting the different column values to achieve, the larger the count, the higher the degree of dispersion:
```
Mysql> SELECT COUNT (DISTINCT column_name) from table_name;
```

# Learn to use commands to analyze databases

# # Show View status
```
Mysql> SHOW [session| GLOBAL] STATUS like '%status_name% ';
Session (Default): Takes out the execution of the current window
Global: Starting from MySQL to now
```
> View queries (number of insertions Com_insert, number of changes com_insert, number of deletions com_delete)
```
mysql> SHOW STATUS like ' com_select ';
```

> View connections (number of logins)
```
mysql> SHOW STATUS like ' connections ';
```

> Database Run time

```
mysql> SHOW STATUS like ' uptime ';
```

> View Slow query times

```
mysql> SHOW STATUS like ' slow_queries ';
```

> See what the index is used for:

```
mysql> SHOW STATUS like ' handler_read% ';
-Handler_read_key: The higher the value the better, the higher the number of times that the index is queried.
-Handler_read_rnd_next: The higher the value, the less efficient the query.
```

# # Display System variables

```
mysql> SHOW VARIABLES like '%variables_name% ';
```

# # shows the status of the InnoDB storage engine

```
mysql> SHOW ENGINE INNODB STATUS;
```

... Continuous replenishment of

MySQL Daily optimization details