MySQL Tens data volume optimization method accumulation

1, sub-database sub-tableObviously, a main table (that is, very important tables, such as user tables) unlimited growth is bound to seriously affect performance, sub-library and sub-table is a very good solution, that is, the performance optimization approach, now the case is that we have a 1000多万条 record of the user table members, query very slow, A colleague's approach is to hash it into 100 tables, from Members0 to Members99, and then distribute the records to these tables according to the mid, which is probably the way the code looks: <?phpfor ($i =0; $i < $i + +) {//echo]     CREATE TABLE db2.members{$i} like Db1.members<br> "; echo "INSERT into members{$i} SELECT * from the members WHERE mid0={$i}<br>";}? > 2. Modify MySQL table structure without downtimeSame as the members table, the design of the table structure is not reasonable, with the database constantly running, its redundant data is also a huge growth, colleagues used the following method to deal with: first create a temporary table: creating table members_tmp like Members then modify the table structure of the members_tmp as a new structure, and then use the above for loop to export data, because 10 million of the data one-time export is not right, mid is the primary key, an interval of an interval of the guide, basically is an export 50,000 bar, This is omitted. Then rename the new table to replace it: RENAME table members to members_bak,members_tmp to the members; that's it, basically, no loss, no downtime. Update table structure, But in fact the Rename period table is locked, so choose the time when the operation is a skill. After this operation, so that the original 8G of the table, all of a sudden into the 2G more also talked about the type of float field in MySQL when the strange phenomenon, that is, in the PMA see the number is not as a condition to query

3, Common SQL statement optimization:

1. Database (table ) design is reasonable

Our tables are designed to conform to the 3NF 3 paradigm (canonical mode ), and sometimes we need appropriate inverse paradigms

2. optimization of SQL statements (indexes, common tips .)

3. Configuration of the data (cache set large )

4. Proper hardware configuration and operating system (read/write Separation .)

3NF of data

1NF: It is atomic, indivisible . ( As long as the relational database is used, it is automatically compliant )

2NF: On the basis of satisfying 1NF, we consider whether to meet 2NF: As long as the record of the table satisfies uniqueness , it is said that the same table, you can not appear exactly the same record , generally speaking we design a primary key in the table .

3NF: On the basis of satisfying 2NF, we consider whether to satisfy 3NF: That is, our field information can be derived by the associated relationship . Usually we handle it through a foreign key )

Inverse Paradigm : why it is necessary to reverse the paradigm :

(The function of the album corresponds to the design of the database )

appropriate inverse paradigm .

Optimization of SQL statements

There are several types of SQL statements

DDL (data Definition language ) [create alter drop]

DML (Data Manipulation language ) [insert Delete upate]

Select

DTL (Data transaction statement ) [commit rollback savepoint]

DCL (Data Control statement ) [Grant revoke]

Show Status Command

This command can display the current status of your MySQL database . Our main concern is "com" at the beginning of the instructions

Show status like ' com% ' <=> show session status like ' com% '//show current console situation

Show global status like ' com% '; Shows the number of times a database has started from a query

To display the number of connected databases

Show status like ' Connections ';

Here we optimize the focus on slow queries . (By default, is ten) mysql5.5.19

Show how to view slow queries

Show variables like ' Long_query_time '

In order to teach, we have a huge table (MySQL stored procedure )

The goal is to look at how to deal with, in the massive table, the query speed quickly !

SELECT * from EMP where empno=123456;

Requirements: How to find a select of slow query in a project, MySQL database support to put slow query statements, recorded in the log, the programmer analysis . (note, however, that it does not start by default .)

Steps :

1. To start MySQL like this

Go to MySQL installation directory

2. Start Xx>bin\mysqld.exe–slow-query-log This note

Test , for example, we put

SELECT * from EMP where empno=34678;

It took 1.5 seconds and I'm now optimizing .

Quick Experience : index The empno of the EMP table .

ALTER TABLE EMP Add primary key (EMPNO);

Delete primary key index

ALTER TABLE EMP Drop PRIMARY Key

Then, check the speed faster .

L Index Principle

Introduces a very important tool , explain, which can analyze SQL statements to predict the efficiency of your SQL execution .

His basic usage is :

Explain SQL statement \g

Based on the information returned, we can see whether the SQL statement uses an index, how many records to take out , and how to sort .

L It is appropriate to add indexes on what columns

① indexes on columns that are frequently queried .

② column of data, the content is only a few values , not very suitable for indexing .

③ content changes frequently, inappropriate and indexed

L Types of indexes

① PRIMARY key index (a column is set as the primary key, which is the key index )

② Unique index (unique) (that is, the column is unique and also indexed)

③ index (normal index)

④ Full-text index (fulltext)

SELECT * from article where content like '% Jet Li % ';

Hello, I am a boy

Hi, I'm a boy. = Chinese Sphinx

⑤ composite Index (Dole together )

CREATE index myind on table name (column 1, column 2);

L How to create an index

If you create a unique/normal /fulltext index

1. Create [unique| Fulltext] Index index name on table name (column name ...)

2. ALTER TABLE name Add index index name (column name ...)

If you want to add a primary key index

ALTER TABLE name add primary key (column ...)

Delete Index

1. Drop INDEX index name on table name

2. ALTER TABLE name DROP index index_name;

3. ALTER TABLE name drop PRIMARY key

Display Index

Show index (ES) from table name

Show keys from table name

DESC Table Name

How to query the index of a table

Show indexes from table name

L Considerations for using indexes

The most important condition for querying to use an index is to use the index in the query criteria.

It is possible to use an index in the following situations:
1, for a multi-column index that is created, the index is generally used as long as the query criteria uses the leftmost column.
2, for queries that use like, the query is '? A ' will not be used to index aaa% '.

The following tables will not use the index:
1, if there is or in the condition , it will not be used even if there is a conditional index.
2, for multi-column indexes, not the first part of the use, the index is not used.
3, the likequery starts with a %
4, if the column type is a string, be sure to use quotation marks in the condition to reference the data. Otherwise, the index is not used.
5, if MySQL estimates that using a full table scan is faster than using an index, the index is not used.

L How to detect if your index is valid

Conclusion: The larger the Handler_read_key, the less

Handler_read_rnd_next is as small as possible.

Fdisk

Find

L What's the difference between MyISAM and InnoDB?

1. MyISAM does not support foreign keys , InnoDB support
2. MyISAM does not support transactions and foreign keys are not supported .
3. The storage of data information is handled differently . (If the storage engine is MyISAM, create a table for three files: if it is InnoDB there is only one file *.frm, the data is stored to ibdata1)

For MyISAM databases, scheduled cleanup is required

Optimize table name

L Common Methods of SQL optimization

1. Disable sorting by using ORDER by null

For example SELECT * FROM Dept GROUP by ename ORDER by null

2. In applications with high precision, it is recommended to use fixed-point numbers (decimal) to store the values to ensure the accuracy of the results

3. If the field is an index of a character type, be sure to enclose it as a conditional query, or the index will not be valid.

4. If the primary key index is not used, then querying for the for update will cause the table to lock. Easy to cause the card to die.

10,000,003,200

CREATE TABLE sal (T1 float (10,2));

CREATE TABLE Sal2 (T1 decimal (10,2));

Q ? in php , int If it is a signed number, the maximum value . int-4*8=32 2 31-1

Horizontal division of the L table

L Split Table vertically

If your database's storage engine is MyISAM, then when you create a table, the last three files . *.frm record table structure . *.myd Data *.myi This is the index .

mysql5.5.19 version of his database file, which is placed by default (see configuration in the My.ini file .)

L Read and write separation

MySQL Tens data volume optimization method accumulation