SQL statement Optimization (iii)

4.1 Optimizing the types of tables

In MySQL, you can use the function Procedureanalyse () to analyze the currently applied table, and modify the function to make recommendations on the data types of the columns in the data table, and the user can consider whether or not to implement them according to the actual situation of the application.

Mysql> SELECT * from Duck_cust procedure analyse () \g
1. Row ***************************
Field_name:sakila.duch_cust.cust_num
Min_value:1
Max_value:6
Min_length:1
Max_length:1
empties_or_zeros:0
nulls:0
avg_value_or_avg_length:3.5000
std:1.7078
Optimal_fieldtype:enum (' 1 ', ' 2 ', ' 3 ', ' 4 ') notnull
2. Row ***************************
... ...

4.2 Large Storage Solution

1. Sub-database sub-table

2. Partitioning

Main Purpose:

1. Reduce the number of records in a table

2. Reduce the burden on the operating system

MyISAM Read lock

1.lock table T1 Read

2. Open another MySQL connection terminal, then try:

SELECT * FROM t1

3. Insert, update, and delete T1 This table, you will find that all the data is stuck on the terminal and there is no real operation.

4. Read locking is useful when we are backing up large amounts of data.

mysqldump-uroot-p123 Test >test.sql

MyISAM Write lock

1.lock Table T1 Write

2. Open another MySQL terminal, try to select, INSERT, UPDATE and delete this table T1, you will find that all can not operate, will stay on the terminal, only wait for the first terminal operation, the second terminal can be really executed.

3. The write lock on the visible table is more restrictive than the read lock

4. In general, we seldom go explicitly to read and write locks on the table, MyISAM will automatically lock.

Slow query log

1. About Slow queries

Open an account and set a slow query time:

Vi/etc/my.cnf

Log_slow_queries=slow.log

Long_query_time=5

SQL statement Optimization (iii)