MySQL database optimization

One of the problems encountered MySQL:

1, read and write highly concurrent low efficiency problem —- Solution: clusters, distributed.

2, reading and writing low efficiency of massive data problem —– Solution: sub-table, and warehouses.

3, high availability and scalability —- Solution: dynamic expansion servers from a single point of failure, disaster recovery.



Second, the relational database optimized 🙁 principles: the first single, the multi-machine).

2.1 stand-alone optimization:

1. Slow SQL definitions -> analyze Slow SQL– solve slow SQL.

Design, indexing, optimization engine 2. table. 3. The part table (Table vertical component, the horizontal sub-table), the partition, the optimization sub-library. 4. cache cluster do.

5.SQL statement optimizer



Over 2.2 optimization unit (divided into a plurality of databases):


1. The separate read and write (to ensure that the master-slave synchronization):


28 principle: if you have 10 databases, two specialize in CRUD database, eight specialize in database queries.


2. Cache Clusters do


Third, the positioning slow SQL


3.1 check the database status:


3.1.1 View Runtime: show status like ‘uptime’;


3.1.2 CRUD execution times:


      Show status like ‘Com_%’;


      Show status like ‘Com_update%’


      Show status like ‘Com_delete%’


      Show status like ‘Com_insert%’


      Show status like ‘Com_select%’


    create drop truncate (reset table) delete


GLOBAL (Global), SESSION (current session)


    show  global  status  like  ‘Com_select%’;


    show  session  status  like  ‘Com_select%’;


3.1.3 See All Connections:


      Show status like ‘connections’;


      Show status like ‘Max_used_connections’;




3.2 View slow queries


3.2.1 Check the number of slow queries


      Show status like ‘slow_queries’;


      Select * from dept;


      Select * from emp where ….;


Tell MySQL 3.2.2 What is slow, slow query threshold setting.


      Show variables like ‘long_query_time’;


      Set long_query_time=0.5


      Show VARIABLES like ‘%slow%’;


Fourth, the analysis of slow SQL

(1) Explain select * from emp where empno = 459: This query analysis




Fifth, stand-alone table design and optimization — engine

Table 5.1 — structural design and anti 3NF 3NF

1NF: table columns: atomic properties, no longer decomposed.

2NF: primary key uniqueness.

3NF: Table no redundant data.

Anti 3NF: appropriate to increase the field to allow redundancy —– can improve query performance.

5.2 MySQL storage engine

Note: In the construction of the table when you can specify the storage engine, or the default is not specified, the default configuration file my.ini.

MySQL engine Category: myisam, innodb, memory.

5.2.1 The difference between MyISAM and INNODB (the main difference)

1. Transaction Security: MyISAM does not support transactions, INNODB support.


2. Query and add speed: MyISAM is fast, INNODB slow.


3. Support full-text indexing: MyIsam support, innodb is not supported.


4. Lock Mechanism: MyIsam innodb lock table row lock.


5. Foreign Key: MyISAM does not support foreign keys, foreign keys INNODB support (usually foreign key is not provided, to ensure that data consistency is typically in the program).

5.2.2 engine usage scenarios

MyISAM storage engine:

If the table is less demanding on the transaction, while based on the query and add-oriented, we consider the use of myisam storage engine, such as the post bbs table, response form.

INNODB storage engine:

High transaction requirements, the saved data are important data, we recommend the use of INNODB, such as the Orders table, the account table.

5.2.3 add, modify engine

1, designated storage engine when creating the table:

Create table table name (field list) engine storage engine name;

2, modify the storage engines:

         alter table table_name engine=innodb;

Six single index optimization —

The concept: just a data structure used to improve query performance, but delete, add, modify relatively slow. It is a space for time strategy. Category: General Index: duplicate values, may be added at any field above.

Unique index: can not be duplicated, the above may be added at any field, can be null, and may have a plurality of null.

Primary key indexes: unique and not null value.

Full-text index: es operate in place of the query data.

6.1 Mysql engine allows common index types:



hash: Like Map, you can find through a key value directly.

B-tree: Use binary tree to save the index.



6.2 Use of the index

Add Index:

Method a: create index index_emp_empno on emp (empno);


Method two: alter table emp add index index_emp_empno (empno);


Query Index: show index from emp;


Delete Index: alter table emp drop index index_emp_empno;


Modify the index: delete plus.

6.3 Conditions for creating an index:


1 must often be used in conditions where, often, or sort, order by the back of the field.


2. The content of this field is not level change frequently.

3. The content of this field is not the unique value of several (eg: sex).


6.4 to create an index of tips:

How to distinguish between the index columns:

General index (separate index): The index is only created a column above.


Composite index (multi-column index): The index is only created multiple columns above.


6.4.1 For composite indexes created, if the first index is not used (dname), the index is no use.

alter table dept add index my_indx (dname, loc); // dname is on the left column, loc is on the right column


explain select * from dept where dname = ‘aaa’ // index is valid

explain select * from dept where loc = ‘aaa’ // index is invalid (not used dname)

6.4.2 For queries like, ‘% aaa’: use index is invalid, ‘aaa%’: use an index, ‘% aaa%’: use index is invalid.

explain select * from dept where dname like ‘% aaa’ // index is invalid

explain select * from dept where dname like ‘aaa%’ // index is valid

explain select * from dept where dname like ‘% aaa%’ // index is invalid

When can use the index in other words, requires the use of all fields, must be used alone; 6.4.3 in Sql statement has or is, as long as there is one condition can not use an index, the index of this statement is not valid.

explain select * from dept where dname = ‘aaa’; // index is valid


explain select * from dept where loc = ‘aaa’; // Invalid Index


select * from dept where dname = ‘xxx’ or loc = ‘xx’; // index is invalid and therefore

4.4.4 If the column type is a string, it must be used in quotes in the condition data. Otherwise the index is invalid.

expain select * from dept where dname = ‘111’; // index is valid

expain select * from dept where dname = qqq; // will complain

4.4.5 If mysql estimated using the full table scan faster than using an index, the index is not used.


Such as: table there is only one record



Leave a Reply