MySQL Optimization tips

A comprehensive technology for MySQL optimization, mainly including

A: Rationalization of table Design (3NF compliant)

B: Add appropriate index (index) [Four kinds: normal index, primary key index, unique index unique, full-text index]

C: Sub-table technology (horizontal split, vertical division)

D: Read and write [write: Update/delete/add] Separation

E: Stored procedure [modular programming, can improve speed]

F: Configuration optimization for MySQL [Configure max concurrency number My.ini, resize cache]

G:mysql Server hardware Upgrade

H: Timed to remove unwanted data, timed defragmentation (MyISAM)

=============================================================

Ps:mysql Use Tips

Find out how some of the running states of the MySQL database are queried (for example, to know the current MySQL run time/How many times Select/update/delete was executed)./Current connection)

1. Show status

Commonly used for:

Show status like ' uptime ';

Show stauts like ' Com_select ' show stauts like ' Com_insert ' ... Analogy Update Delete

? Show [Session|global] status like .... If you do not write [Session|global] The default is session sessions, referring to the execution of the current window, if you want to see all (from MySQL boot to present, then should global)

Show status like ' connections ';

Show Slow query times

Show status like ' Slow_queries ';

2, modify the slow query MySQL.

Show variables like ' long_query_time '; Can display the current slow query time

Set long_query_time=1;//can modify slow query time

3, modify the command end

Delimiter $$

4. Explain command

Explain can help us perform MySQL execution when we don't actually execute a SQL statement, so we use our parsing SQL instructions.

5. Turn on slow query log

By default, our MySQL does not log slow queries, you need to specify a slow query to log when you start MySQL

Bin\mysqld.exe--safe-mode--slow-query-log [mysql5.5 can be specified in My.ini]

Bin\mysqld.exe–log-slow-queries=d:/abc.log [Low version mysql5.0 can be specified in My.ini]

Close MySQL first, and then start, if the slow query log is enabled, the file is placed by default in the

Location recorded in the My.ini file

#Path to the database root

Datadir= "C:/Documents and Settings/all users/application data/mysql/mysql Server 5.5/data/"

MySQL Optimization tips