Summary of MySQL optimization scenarios

Optimization technology of U MySQL Database

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)

u What kind of table is in line with 3NF (paradigm)

The form of the table is first in line with 1NF to meet the 2NF and further meet the 3NF

1NF: The column of the table is atomic, non-decomposition, that is, column information, can not be decomposed, only the database is a relational database (Mysql/oracle/db2/informix/sysbase/sql server), automatically meet the 1NF

? Classification of databases

Relational database: Mysql/oracle/db2/informix/sysbase/sql server

Non-relational database: (Feature: Object-oriented or set)

NoSQL database: MongoDB (characterized by document-oriented)

2NF: The record in the table is unique and satisfies 2NF, usually we design a primary key to implement

3NF: There is no redundant data in the table, that is, the information of the table, if it can be deduced, it should not be a separate design of a field to store. For example, the following design is not satisfied with 3NF:

Anti-3NF: However, no redundant database is not necessarily the best database, sometimes in order to improve operational efficiency, it is necessary to reduce the paradigm standard, appropriate retention of redundant data. The practice is to adhere to the third paradigm when designing the conceptual data model, and to lower the standard of normalization into the design of the physical data model. Lowering the paradigm is adding fields, allowing redundancy .

Optimization of the U-SQL statement itself

The question is: how to quickly locate a slow-executing statement from a large project. (Locate slow query)

① First we understand how some of the running states of the MySQL database are queried (such as how many times the current MySQL runtime/total number of select/update/delete are executed)./Current connection)

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 ';

② How to locate slow queries

Building a large table (4 million)---Stored procedure build

By default, MySQL thinks 10 seconds is a slow query.

L Modify the slow query for MySQL.

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

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

Build Big Table--big table record has the request, the record is different only then uses, otherwise the test effect and the real difference is big.

Create:

CREATE Table Dept (/* Department table */

Deptno mediumint UNSIGNED not NULL DEFAULT 0,/* number */

Dname VARCHAR () not NULL DEFAULT "",/* name */

Loc VARCHAR (+) not NULL DEFAULT ""/* location */

) Engine=myisam DEFAULT Charset=utf8;

CREATE TABLE EMP

(Empno mediumint UNSIGNED not NULL DEFAULT 0,/* number */

Ename VARCHAR () not NULL DEFAULT "",/* name */

Job VARCHAR (9) Not NULL DEFAULT "",/* work */

Mgr Mediumint UNSIGNED not NULL DEFAULT 0,/* Superior Number */

HireDate Date not null,/* entry time * *

Sal DECIMAL (7,2) Not null,/* salary */

Comm DECIMAL (7,2) not null,/* Bonus */

Deptno mediumint UNSIGNED not NULL DEFAULT 0/* Department number */

) Engine=myisam DEFAULT Charset=utf8;

CREATE TABLE Salgrade

(

Grade Mediumint UNSIGNED not NULL DEFAULT 0,

Losal DECIMAL (17,2) is not NULL,

Hisal DECIMAL (17,2) not NULL

) Engine=myisam DEFAULT Charset=utf8;

Test data

INSERT into Salgrade VALUES (1,700,1200);

INSERT into Salgrade VALUES (2,1201,1400);

INSERT into Salgrade VALUES (3,1401,2000);

INSERT into Salgrade VALUES (4,2001,3000);

INSERT into Salgrade VALUES (5,3001,9999);

In order for the stored procedure to execute properly, we need to modify the command execution Terminator

Delimiter $$

Create function rand_string (n INT)

Returns varchar (255) #该函数会返回一个字符串

Begin

#chars_str定义一个变量 Chars_str, the type is varchar (100), the default value ' ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFJHIJKLMNOPQRSTUVWXYZ ';

DECLARE chars_str varchar (+) Default

' ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFJHIJKLMNOPQRSTUVWXYZ ';

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 $$

If you want to use it in your program, it is ok!

Create a stored procedure

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

Begin

declare i int default 0;

#set autocommit = 0 Set autocommit to 0

Set autocommit = 0;

Repeat

Set i = i + 1;

INSERT into EMP values ((start+i), rand_string (6), ' salesman ', 0001,curdate (), 2000,400,rand_num ());

Until I = Max_num

End repeat;

Commit

End $$

#调用刚刚写好的函数, 1.8 million records, starting from number 100,001th

Call Insert_emp (100001,4000000);

③ at this point we will be counted if a statement execution takes more than 1 seconds.

④ If a slow query SQL is recorded in one of our logs

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/"

⑤ test, you can see that our MySQL slow SQL statement is recorded in the log.

Optimization issues.

Through the explain statement can be analyzed, mysql how to execute your SQL statement, the use of this tool to put a bit, one will say.

Add index "small suggestion:"

U four indexes (primary key index/UNIQUE index/full-text index/Normal index)

1. Add to

1.1 Primary Key Index additions

The column is the primary key index when a table is set to a column as the main key

CREATE TABLE AAA

(id int unsigned primary key auto_increment,

Name varchar (+) NOT null Defaul ");

This is the ID column, which is the primary key index.

If you do not specify a primary key index when you create the table, you can also add, after creating the table, the directive:

ALTER TABLE name add primary key (column name);

Example:

CREATE table BBB (ID int, name varchar (+) NOT null default ');

ALTER TABLE BBB add PRIMARY key (ID);

1.2 General Index

In general, the creation of a normal index is to create a table first, and then create a normal index

Like what:

CREATE TABLE CCC (

ID int unsigned,

Name varchar (32)

)

Create index index name on table (column 1, column name 2);

1.3 Creating a full-text index

Full-text indexing, mainly for the retrieval of files, text, such as articles, full-text indexing is useful for MyISAM.

Create:

CREATE TABLE Articles (

ID INT UNSIGNED auto_increment not NULL PRIMARY KEY,

Title VARCHAR (200),

Body TEXT,

Fulltext (Title,body)

) Engine=myisam CharSet UTF8;

INSERT into articles (Title,body) VALUES

(' MySQL Tutorial ', ' DBMS stands for DataBase ... '),

(' How to use the MySQL well ', ' after you went through a ... '),

(' Optimizing MySQL ', ' in this tutorial we'll show ... '),

(' 1001 MySQL Tricks ', ' 1. Never run mysqld as root. 2 ... '),

(' MySQL vs. Yoursql ', ' in the following database comparison ... '),

(' MySQL Security ', ' when configured properly, MySQL ... ');

How to use full-text indexing:

Error usage:

SELECT * from articles where body like '%mysql% '; "Do not use to full-text indexing"

Prove:

Explain select * from articles where body like '%mysql% '

The correct usage is:

SELECT * from articles where match (title,body) against (' database '); Can

? Description

1. Fulltext indexes in MySQL are only valid for MyISAM
2. MySQL self-provided fulltext for English effective->sphinx (coreseek) Technology processing Chinese
3. Use the Match (field name:) against (' keyword ')
4. A full-text index is called a stop word, because in a text, creating an index is an infinite number, so, for some common words and characters, it is not created, these words, called stop words.

1.4 Unique Indexes

① when a column of a table is specified as a unique constraint, this column is a unique index

CREATE TABLE DDD (ID int primary key auto_increment, name varchar (+) unique);

At this point, the Name column is a unique index.

The unique field can be null and can have more than one null, but it cannot be duplicated if it is specific.

The primary key field, cannot be null, and cannot be duplicated.

② After creating a table, go to create a unique index

CREATE TABLE eee (ID int primary key auto_increment, name varchar (32));

Create unique index index name on table name (list:);

1. Query index

DESC table Name "The disadvantage of this method is that the index name cannot be displayed."

Show index (ES) from table name

Show keys from table name

1. Delete

ALTER TABLE name DROP INDEX name;

If you delete the primary key index.

ALTER TABLE name drop PRIMARY key [there is a small problem here]

1. Modify

Delete and re-create it first.

Considerations for using the U index

The cost of the index:

1. Disk space consumption
2. has an effect on DML operations, slowing down

What columns do you fit to add an index on?

Summary: A field that meets the following criteria should create an index.

A: Be sure to use B in the Where bar: the contents of the field are not unique in several values (Sex) C: field content does not change frequently.

U Considerations for Using Indexes

Putting the Dept table, I add several departments:

ALTER TABLE Dept Add index My_ind (dname,loc); Dname the left column, Loc is the right column.

Note that if we have a composite index in our table (the index is on more than one column), we notice at this point:

1, for a multi-column index that is created, the index is generally used as long as the query criteria uses the leftmost column.

Explain select * FROM dept where loc= ' AAA ' \g

The index is not used

2, for queries that use like, the query will not use the index if it is '%aaa '

' aaa% ' will use the index.

For example: Explain select * FROM dept where dname like '%aaa ' \g

The index cannot be used, that is, in the like query, the key ' keyword ', the first, cannot use the characters such as% or _. If you must have a change in the previous value, consider using full-text index->sphinx.

1. If there is or in the condition, it will not be used even if there is a conditional index. In other words, all the fields that are required to be used must be indexed and we recommend that you try to avoid using the OR keyword

SELECT * FROM dept where dname= ' xxx ' or loc= ' xx ' or deptno=45

1. If the column type is a string, be sure to use quotation marks to reference the data in the condition. Otherwise, the index is not used. (when added, the string must be "), that is, if the column is a string type, be sure to include it with '.

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

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

How to view the Index usage:

Show status like ' handler_read% ';

We can note:
Handler_read_key: The higher the value the better, the higher the number of times that the index is queried.

Handler_read_rnd_next: The higher the value, the less efficient the query.

Tips for U-SQL statements

1. When you use the group by group query, the default grouping is also sorted and may slow down.

Like what:

Adding ORDER by null after group by will prevent sorting.

1. In some cases, you can use a connection to replace a subquery. Because using Join,mysql, you do not need to create temporary tables in memory.

SELECT * FROM dept, EMP where Dept.deptno=emp.deptno; [Simple handling method]

SELECT * FROM dept LEFT join EMP on DEPT.DEPTNO=EMP.DEPTNO; [Left outer connection, more ok!]

How to choose the storage engine for MySQL

In development, we often use the storage engine myisam/innodb/memory

MyISAM storage: If the table is not high on the transaction and is query-and-add-based, we consider using the MyISAM storage engine. , such as BBS in the posting table, reply to the table.

INNODB Storage: High transaction requirements, the data stored is important data, we recommend the use of INNODB, such as order form, account table.

Ask the difference between MyISAM and InnoDB

1. Transaction security

2. Query and add speed

3. Support Full-Text indexing

4. Locking mechanism

5. Foreign key MyISAM does not support foreign key, INNODB support foreign key. (in PHP development, the foreign key is not usually set, usually in the program to ensure the consistency of the data)

Memory storage, such as our data changes frequently, do not need storage, but also frequent queries and changes, we consider using memory, very fast.

U if your database's storage engine is MyISAM, be sure to remember to defragment it regularly

To illustrate:

CREATE TABLE test100 (id int unsigned, name varchar (+)) Engine=myisam;

INSERT into test100 values (1, ' aaaaa ');

INSERT into test100 values (2, ' bbbb ');

INSERT into test100 values (3, ' CCCCC ');

We should define the MyISAM to be organized.

Optimize table test100;

mysql_query ("Optimize tables $ table name");

Summary of MySQL optimization scenarios