MySql optimization solution, mysql Optimization

MySql optimization solution, mysql Optimization

MysqlSummary

U Mysql database Optimization Technology

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)

G: optimization of mysql statements

 

 

 

 

 

Below are some basic mysql usage commands.

 

I. view basic commands in mysql

(1): check which databases are currently available

Show databases;

(2): Use the mysql database

Use test;

(3): View tables in the current database

Show tables;

(4): view the commands created in the preceding grade Table.

Show create table grade;

(5): view the structure of the student table

Desc student;

(6): view the storage engines supported by the database

Show engines;

Show engines \ G; // \ G to make the results more beautiful

(7): view the default storage engine

Show variables like 'Storage _ engine ';

 

Ii. modify a table in mysql

(1) change the course Field Data Type of the grade Table to varchar (20)

 

Alter table grade modify course varchar (20 );

 

(2) Change the s_num field to the front of course.

 

Alter table grade modify s_num int (10) after id;

 

(3) Rename the grade field to score

 

Alter table grade change grade score varchar (10 );

 

(4) Delete the foreign key constraint of grade

Alter table grade drop foreign key grade_fk;

 

(5) change the storage engine of grade to INnoDB

 

Alter grade engine = INnoDB;

 

(6) Delete the student address field

Alter table student drop address;

 

(7) Add the phone field in the student table

Alter table student add phone int (10 );

 

(8) change the table name of grade to gradeinfo.

Lter table grade rename gradeinfo;

 

(9): Delete the student table

Drop table student; // The parent table studnet can be deleted only because the foreign key has been previously deleted.

 

 

 

 

 

Mysql optimization query tool: three types of tools: Use indexes, use EXPLAIN to analyze queries, and adjust the internal configuration of MySQL

 

 

Create INDEX idx_typeid on book1 (typeid );

 

When MySQL creates an index, it has different details for different data tables: MyISAM data table, the data will be saved in the data file, and its index value will be saved in the index file, the BDB processing program saves the data value and index value of the same BDB data table in the same file, the InnoDB processing program stores the data and index values of all InnoDB data tables in the same tablespace.

 

You must also consider its disadvantages when creating an index. First, indexes consume disk space (which is already very cheap). The more indexes, the more space they consume. For MyISAM data tables, too many indexes will give priority to the index file and the data file size limit. For a BDB data table, because it stores both the data value and index value in the same file, increasing the index will make it faster to reach the size limit of the BDB data file. InnoDB Data Tables share the storage space in the InnoDB tablespace. Therefore, increasing indexes will inevitably increase the InnoDB tablespace consumption speed. However, as long as the disk space can be increased, the InnoDB tablespace can be expanded by adding components.

 

Second, too many indexes will slow down the insert, delete, and modify operations on indexed data columns. Because when writing records, MySQL must also modify all the indexes related to it.

 

Optimization during Table Design

You can set the length of fields in the relevant table to the same value. This reduces the number of matching times during queries.

 

 

 

 

 

MySql optimization solution Summary: http://www.cnblogs.com/xiaoblog/p/4209930.html

Database optimization and some common database application functions and methods: http://www.cnblogs.com/yydcdut/p/3888308.html