MySQL Optimization point section

1, the Rationality of database table design

1) Three paradigms

One paradigm: atomicity, attribute is not divided;

Two paradigms: no partial dependence,

Example: (school number, course name) → (name, age, score, credit), the existence of part of the dependency (school number) → (name, age)

(School number, name, age), (course name, credit), (school number, course name, grade),

Three paradigms: no transitive dependence,

Example: (school number) → (name, age, gender, department, office address, Office phone)

Transitive dependencies:

(school number) → (department) → (Department office location, Department of Telephone)

Re-split ...

2) Inverse paradigm:

Photo table (photo ID, name, click Count, Album ID, upload time)

Album table (album ID, name, time)

If there is a need for album Clicks and is frequent, you need to add a "click" Redundancy field to the album table.

Specification for adding redundant fields:

A one-to-many situation

Redundant fields should be as far as possible on the "one" side.

If you put the redundant field "album name" in the photo table, it is convenient to ask the album name. But it creates great space waste and greatly improves the cost of modification.

3) Anti-foreign key

There are foreign key relationships, but foreign key constraints are not added.

Disadvantages of foreign keys: slightly

2. Optimization of SQL statements

1) Five types of SQL statements

Ddl

Dml

Select

DTL Transaction Control Statement Commit\rollback\savepoint

DCL Data Control Statement Grant\revork

The core of SQL optimization is select, and you know why.

2) Show status command

To view the current state of the database, several more useful states are:

A) Show status like ' com% ' <=> show session status like ' com% '//Current console situation

b) Show global status ' com% '; The state of the database from boot to current

c) Show status like ' Connections ' shows the number of linked databases

D) Show status like ' Uptime ' server working time (seconds)

e) Show status like ' slow_queries ' number of slow queries (default is 10 seconds)

3) Here we optimize the focus is slow query.

A) show variables like ' Long_query_time '

The default is 10 seconds, which requires a bit higher and we set it to 1 seconds.

Set long_query_time = 1

Play a huge table and test performance.

Show status like ' Slow_queres '

The current slow query is found to be 0 at this time.

b) can customize function + stored procedure, create a huge table

A custom function that produces a random string:

Delimiter $$

Drop function if exists rand_string;

Create function rand_string (n INT)

Returns varchar (255)

Begin

Declare chars_str varchar (+) Default

' ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ ';

Declare return_str varchar (255) default ';

Declare I int default 0;

While I < n do

Set return_str = concat (return_str,substring (Chars_str,floor (1+rand () *52), 1));

Set i = i + 1;

End while;

Return Return_str;

End $$

Delimiter; $$

Stored procedures:

Drop table if exists emp;

Create Table EMP (

Id int PRIMARY KEY,

Name varchar (255),

DESCP varchar (255),

Gene varchar (16)

);

Delimiter $$

Drop PROCEDURE if exists proc_insertemp;

CREATE PROCEDURE proc_insertemp (in Start int (ten), in Max_num Int (10))

Begin

Declare I int default 0;

Set autocommit = 0;

Repeat

Set i = i + 1;

Insert into EMP values ((start+i), rand_string (6), ' Salesman ', ' Mans ');

Until i = Max_num

End repeat;

Commit;

End $$

Delimiter; $$

Using Stored Procedures

Call Proc_insertemp (10000, 20000);

c) MySQL supports the logging of slow query statements to the programmer for analysis.

The default is not enabled.

Go to MySQL installation directory and start--slow-query-log

d) Index

Show indexes from TB

PRIMARY key index ALTER TABLE TB add primary key (KeyName);

A unique index unique is that the column is unique and is also indexed.

Normal index

Full-text index Fullindex (Mylsam support only)

Compound indexes (multiple columns together, federated indexes) are left-to-right in order.

Chinese index

Sphinx + Chinese Word coreseek

4) Explain instruction

Explain select * from TB where id = 2000

Select_type:simple

Table:tb

Type:all//Search Type

Possible_keys:primary//indexes that may be used

Key:primary//Actual index used

Key_len:

Ref

Rows:1//out of how many records, because there is an index, so is 1

extra:using where//using temporary using filesort, etc.

The logic of querying the whole table is unreasonable in the real project, and the logic of paging must exist. Pagination must be indexed

5) Index Add scene

A) More frequent fields that are query criteria should create an index

b) fields with poor uniqueness are not suitable for creating indexes individually, even if they are frequently used as query criteria

SELECT * from TB where sex = ' n Male ';

c) Update very frequent fields that are not suitable for adding indexes.

6) does not use the index condition

A) like% will not be used in the front of the index, placed in the middle and back to use;

b) For composite indexes, indexes are generally used as long as the query condition uses the leftmost column. If you use only the right column, it will not be used.

c) If MySQL estimates that using a full table scan is faster than using an index, the index is not used.

7) Considerations for using Indexes

A) How to detect if an index is valid

Show status like ' handler_read% '

b) The higher the Handler_read_key value, the more times it is queried using the index

c) Higher Handler_read_key value, indicating low query efficiency

8) Common Skills

For high-volume insert data

A) MyISAM first close the keys, after the import and open;

ALTER TABLE table_name disable keys;

Loading data;

ALTER TABLE table_name enable keys;

b) To sort InnoDB data, turn off the uniqueness check (not to insert one check per piece), turn off autocommit

Set Unique_check = 0;

Set autocommit = 0;

Group by will be sorted by default and can be disabled by order by NULL;

Subqueries generate temporary tables that can be replaced with joins;

In applications with high precision, it is recommended to use fixed-point numbers to store numeric decimal, rather than floating-point numbers, to ensure the accuracy of the results. such as 100,000,003,200, insert float (10,2) type is 10000000.31.

Date type to choose the earliest type of storage that satisfies the application, depending on the actual need. With time stamps, it is convenient to search by range. For example, three days before the record. Note, however, that the int timestamp can only be represented to 2038 years.

The storage of pictures takes path storage. Even dedicated picture server (Picture bed)

9) The difference between Mylsam and InnoDB

The former does not support foreign keys;

The former does not support transactions and foreign keys

The former has a storage cache and needs to manually reclaim outdated data. MyISAM Create a table that corresponds to three files, and if InnoDB has only one file *.frm

For MyISAM databases, scheduled cleanup is required.

Optimize table name.

3. Database parameter Configuration

Make the cache settings larger:

Innodb_additional_mem_pool_size = 64M

Innodb_buff_pool_size = 1G

Key_buff_size

4. Hardware configuration and operating system

Memory over 4G with 64-bit system

5. Separation of reading and writing of sub-table

1) Table segmentation, Horizontal segmentation (sub-database table), vertical segmentation (small size of the table)

2) Read/write separation: Relieve query pressure

A) Determine the SQL statement of the request, judge the DML statement, then the master processing, slave timing synchronization master data.

b) If the read SQL is judged, the LVS will read from the slave.

MySQL Optimization point section