Mysql optimization tips and mysql tips

Mysql optimization tips and mysql tips

A comprehensive technology for mysql optimization, mainly including

A: Table Design rationalization (in line with 3NF)

B: add an appropriate index. [four types: general index, primary key index, unique index unique, and full-text index]

C: Table sharding Technology (horizontal and vertical)

D: read/write [write: update/delete/add] Separation

E: stored procedure [Modular programming, which can increase the speed]

F: optimize mysql configuration [configure the maximum concurrency my. ini and adjust the cache size]

G: mysql server hardware upgrade

H: regularly clear unwanted data, and regularly perform fragment (MyISAM)

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

PS: Tips for using MYSQL

Know how to query the running status of the mysql database (for example, to know the current mysql running time/The total number of select/update/delete ../the current connection)

1. show status

Common:

Show status like 'uptime ';

Show stauts like 'com _ select' show stauts like 'com _ insert'... update delete

 

☞Show [session | global] status like .... if you do not write [session | global], it is a session by default, which refers to the execution of the current window. If you want to view all the results (from mysql to the present, it should be global)

Show status like 'connections ';

// Display the slow Query Count

Show status like 'slow _ queries ';

2. Modify the slow query of mysql.

Show variables like 'long _ query_time '; // the current slow query time is displayed.

Set long_query_time = 1; // you can modify the slow query time.

3. The command is modified.

Delimiter $

4. explain command

Explain helps us to execute mysql when we don't really execute an SQL statement, so we can analyze the SQL command.

 

 

5. Enable slow query log

By default, mysql does not record slow queries. You must specify slow queries when starting 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 [earlier versions of mysql5.0 can be specified in my. ini]

 

Shut down mysql and start mysql. If the slow query log is enabled, this file is stored by default.

Location recorded in my. ini file

# Path to the database root

Datadir = "C:/Documents and Settings/All Users/Application Data/MySQL Server 5.5/Data /"