How to optimize common databases

?

Directory

first, the Common Database optimization method: ???? 1

second, improve efficiency, anti-three paradigms: ???? 1

First Paradigm: ???? 1

Second paradigm: ???? 1

The third paradigm: ???? 1

inverse three-paradigm design: ???? 2

third, positioning slow query. ???? 3

iv. Explanation of the index: ???? 6

Five, explain the explanation of the tool ???? 9

Six, the index application explanation: ???? Ten

Seven, the locking mechanism of concurrent processing: ???? -

Eight, sub-table technology ???? -

ix. Zoning Technology ???? -

Ten , other tuning: ???? -

?

?

First, the common database optimization method:

We talked about the static, Memcache is mainly to query the database less, or do not query the database. A Web site, you must query the database, the index also to optimize the database.

1, the design of the table to conform to the three paradigms.

2, add the appropriate index, the index has a large impact on the query speed, you must add an index. Primary key index, unique index, normal index, full-text index

3, add appropriate stored procedures, triggers, transactions and so on.

4. Read/write separation (master-slave database)

5, some optimization of SQL statements, (query execution slow SQL statement)

6, sub-table partition

Sub-table: Divide a large table into multiple sheets. Partitioning: Assigning a table to a different area of storage,

7, the MySQL server hardware upgrade operation.

Second, improve efficiency, anti-three paradigms:

First paradigm:

Atomicity: The field in the table can no longer be segmented, as long as the relational database, the natural automatically meet the first paradigm.

Relational database: (with row and column concepts) Mysql,sql Server,oracle,db2,infomix,sybase,postgresql

At design time, there is a library-"table-" field-"specific record (content): When you store data, you design the field.

Non-relational database (NoSQL database): memcache/redis/momgodb/, etc.

Second paradigm:

There is no identical record in a table, which can be resolved by a primary key.

The third paradigm:

The table cannot store redundant data.

?

Inverse three-paradigm design:

?

?

Third, positioning slow query.

Slow query: Find in a Web site, query slow statement, you can open a log file, record query slow SQL statements. By default, the slow query log is turned off, and the default record time is SQL statements that exceed 10 seconds.

1, to record slow query way to start MySQL,

First turn off MySQL and go to the MySQL installation directory.

Turn off the MySQL service: You can click Stop on the service name of the MYSQLD service-services-

Slow query: Mysqld.exe--safe-mode--slow-query-log

{The installation directory for MySQL}>bin/mysqld.exe--safe-mode--slow-query-log

Perform inefficient SQL statements with slow query log positioning. The slow query log records all the SQL statements that have been set for execution time beyond Long_query_time.

Execution: The following has been started:

?

2. View the storage path of the slow query log.

When the slow query log is turned on, a slow query log file is created. The log file is saved in the directory of the database, and the directory of the database can be viewed through the configuration file.

?

?

3. Process testing:

View slow query record time under current database:

Show variables like ' long_query_time ';

To modify the slow query time:

Set long_query_time=2;

Test by using one of the following functions:

The benchmark (count,expr) function can test the time required to perform a count of expr operations

In general, there are statements that are slow to query, which is caused by not adding indexes.

Before the index is added:

After adding an index:

Iv. explanation of the index:

Set up the test table:

CREATE TABLE User (

ID int primary KEY auto_increment,

Name varchar (+) NOT null default ' ',

Age tinyint unsigned NOT null default 0,

Email varchar (+) NOT null default ' ',

ClassID int NOT NULL default 1

) engine MyISAM charset UTF8;

Insert into user values (null, ' Xiaogang ', ', ' [email protected] ', 4),

(NULL, ' Xiaohong ', ', ' [email protected] ', 2),

(NULL, ' Xiaolong ', +, ' [email protected] ', 2),

(NULL, ' Xiaofeng ', $, ' [email protected] ', 3),

(NULL, ' Xiaogui ', "," [email protected] ', 3);

? ?

To create a class table:

CREATE TABLE Class (

ID int NOT NULL default 0,

ClassName varchar (+) NOT null default ' '

) engine MyISAM charset UTF8;

INSERT into class values (1, ' Java '), (2, '. NET '), (3, ' PHP '), (4, ' C + + '), (5, ' iOS ');

?

1. Primary KEY index

You can add CREATE TABLE EMP (ID int primary key) in the build tables

After creating the table, add: ALTER TABLE TableName add PRIMARY key (column 1, column 2)

Features of the primary key index:

(1) There is at most one primary key index in a table

(2) A primary key index can point to multiple columns

(3) Column with primary key index, cannot have duplicate value, and cannot have null

(4) The primary key index is highly efficient.

?

2. Unique index

Can be added when creating a table: Create TABLE EMP (name varchar (+) unique)

After you have completed the table, add:

ALTER TABLE tablename add unique [index name] (column name)

Unique index Features:

(1) There can be multiple unique indexes in a table

(2) A unique index can point to multiple columns,

such as ALTER TABLE tablename add unique [index name] (column 1, column 2)

(3) If not NULL is not specified on a unique index, the column can be empty and can have more than one null.

(4) The unique index is more efficient.

?

3. General Index

Using normal indexes is primarily to improve query efficiency

?

Add ALTER TABLE tablename ADD index [index name] (column 1, column 3)

?

4. Full-Text Indexing

MySQL's own full-text index mysql5.5 does not support Chinese, supports English, and requires the table's storage engine to be MyISAM. If you want to support Chinese, there are two options,

(1) Use Aphinx Chinese version coreseek (to replace full-text indexing)

(2) Plugin mysqlcft.

5. View Index

(1) Show index from table name

(2) Show indexes from table name

(3) Show keys from table name

(4) DESC table name

6. Delete Index

(1) Deletion of primary key index:

ALTER TABLE TableName DROP PRIMARY key; Note: When you delete a primary key index, you first remove the Auto_increment property.

?

(2) Deletion of unique indexes

ALTER TABLE tablename DROP INDEX unique index name

(3) Deletion of normal index:

ALTER TABLE tablename DROP INDEX Normal index name

?

7, the main problem of adding indexes:

(1) More frequent as the query criteria field should create an index

???? SELECT * from emp where empno = 1

???? Fields with poor uniqueness are not suitable for creating indexes individually, even if they are frequently used as query criteria

???? SELECT * from emp where sex = ' male '

???? Fields that are updated very frequently are not suitable for creating indexes

???? SELECT * from emp where Logincount = 1

(2) does not appear in the WHERE clause the field should not create a cable

The index is at the cost, although the query speed is increased, but it will affect the efficiency of the deletion. And the index file takes up space.

?

?

V. Explanation of explain tools

?

The tool can analyze the efficiency of SQL execution, but does not execute SQL statements. The main purpose is to see if the SQL statements are indexed.

Syntax: Explain SQL statement \g or DESC SQL statement \g

When working with indexes:

?

When the index is not used:

?

Parameter description of the Explain tool:

The following information is generated:
Select_type: Represents the type of query.
Table: Tables for output result sets
Type: Represents the table's connection types
Possible_keys: The index that may be used when representing the query
Key: Represents the actual index used
Key_len: Length of index field
Rows: Number of rows scanned (estimated number of rows)
Extra: Description and description of the performance

?

Six, the index application explanation:

1. For a multi-column (composite) index that is created, the index is generally used as long as the query condition uses the leftmost column.

Mysql> ALTER TABLE user add index (name,email);

Query OK, 5 rows affected (0.08 sec)

Records:5 duplicates:0 warnings:0

?

?

2. For queries that use like, if the query is "%xxx", the index will not be used, and ' XXX% ' will use the index.

?

?

?

Note: In some cases, a like query is used, for example, to search for the title of a song through lyrics and to search for a movie name through the story. With the help of tools, sphinx the Coreseek software inside.

?

3. If there is or in the condition, the index field of or must be indexed, otherwise the index cannot be used.

After the email is added to the index, the index is used in the test.

4. If the column type is a string, be sure to use quotation marks in the condition to reference the data, otherwise do not use the index.

?

5. Optimizing GROUP BY statements

By default, MySQL sorts all group by Col1,col2. This is in contrast to specifying the order by Col1,col2 type in the query, and if the query includes group by but the user wants to avoid the consumption of the sort results, you can use ORDER by NULL to suppress the ordering.

?

?

6. When the amount of data fetched exceeds 20% of the data in the table, the optimizer does not use the index, but the full table scan.

The number of rows scanned is too much, and the optimizer thinks that the entire table scans the block that the peso attracts.

?

7. View the usage of the index

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.

8, the corresponding large-volume insert data, need to pay attention to:

Mass Insert data (MySQL Administrator) learn
For MyISAM:

Disable Indexing First:
ALTER TABLE table_name disable keys;
Loading Data//insert statement; Execute INSERT statement

After executing the INSERT statement, open the index and add the index uniformly.
ALTER TABLE table_name enable keys;

?

For InnoDB:
1, sort the data you want to import by primary key
2,set unique_checks=0, turn off the uniqueness check.
3,set autocommit=0, turn off auto-commit.

Seven, the locking mechanism of concurrent processing:

For example, perform the following actions:

(1) Remove the ID value from the database,

(2) Add 1 operation.

(3) After the modification is complete, save to the database.

For example, the value of the original ID is 100,== "101

The above steps are performed 100 times and finally become 200

?

There are two users executing simultaneously.

A User:

100

101

?

B User:

100

101

?

Through the locking mechanism to solve the problem,

Lock mechanism: At execution time, only one user obtains the lock, the other user is in a blocking state, and needs to wait for unlocking.

There are several forms of the MySQL lock:

Table-level Lock: The cost is small, lock fast, the probability of lock conflict is the highest, the least concurrency. The MyISAM engine belongs to this type.

Row-level locks: high overhead, slow locking, the lowest probability of a lock collision, and the highest degree of concurrency. InnoDB belongs to this type.

1, the table lock demonstration;

Read operations on the MyISAM table (read-Lock) do not block other processes from reading requests to the same table, but block write requests to the same table. The operation of other processes is performed only when the read lock is released.

When a table is added to a read lock, other processes can only query for the table and will be blocked when modified.

The current process is capable of performing query operations and cannot perform modification operations. You cannot operate on a table that is not locked.

The syntax of the lock table:

Lock table Name Read|write

You can also lock multiple tables with the syntax: Lock table Table 1 Read|wirte, table 2 Read|wirte

Writes to the MyISAM table (with write locks) block any operation of other processes on the locked table, cannot read and write,

After the table is added to the lock, only the current process can perform any action on the locked table. Operations on other processes are blocked.

?

2, the row lock demonstration

The InnoDB storage engine is implemented by locking the index entries on the index, which means that the InnoDB uses row-level locks only if the data is retrieved by index criteria, otherwise INNODB uses a table lock.

Syntax for row locks:

Begin

SQL statements

Commit

When a row lock is turned on, the current process cannot manipulate the record with the same ID as the current process when it performs an operation on a record.

PHP has a file lock, in the actual project most of the use of file locks, because the table lock, will block, when adding a write lock on some tables, other processes can not be manipulated. This can block the entire Web site, slowing down the speed of the site.

?

Similar interview questions:

A commodity, inventory, there is one more, then there are two users at the same time request to place an order, how to prevent all orders to succeed, but no delivery.

Eight, sub-table technology

Table: Divide a large table into several small tables:

Vertical split:

Inside the DEDECMS, vertical partitioning:

In a database you want to store a variety of data, such as article data, movies, music, commodity data,

Content Main Table + additional tables:

Content Main Table: some common information that stores various kinds of data, such as the name of the data, add time, etc.

You can use multiple attached tables, and additional tables store unique information for some data.

The main reason: the data access in the content Main table is more frequent.

?

Horizontal split:

Modulo by ID

?

IX. Zoning Technology

is to store a table in a different area of the disk, which is still a table.

1, the basic concept:

There are 4 types of partitions after mysql5.1:

(1) Range – This mode allows data to be divided into different ranges. For example, you can divide a table into several partitions by year.

(2) List (pre-defined list) – This mode allows the system to split the data through predefined list values

(3) hash (hash) – This mode allows the calculation of the hash key of one or more columns of the table, and finally partitions the data region of the hash code with different values. For example, you can create a table that partitions the primary key of a table.

(4) Key (key value)-an extension of the above hash mode, where the hash key is generated by the MySQL system.

2 Range Partition:

If you create a table like the one below, the table holds staff records for 20 supermarkets, and the 20 supermarkets are numbered from 1 to 20. If you want to divide it into 4 small partitions, you can use the range partition to create the following data table.

To create a range partition syntax:
CREATE TABLE EMP (

ID int NOT NULL,

Name varchar (+) NOT null default ' comment ' clerk's names ',

store_id int not null comment ' supermarket number range is 1-20 '

) Engine MyISAM CharSet UTF8

Partition by Range (STORE_ID) (

Partition P0 values less than (6),//is a storage area where the value of store_id is smaller than 6.

Partition P1 values less than (11),//is a storage area where the value of store_id is greater than or equal to 6 smaller than 11.

Partition P2 values less than (16),

Partition P3 values less than (21)

)

INSERT into EMP values (1, ' Yang over ', 1)--à data is stored in the P0 area

INSERT into EMP values (23, ' Little Dragon Girl ')--the data is stored in the P2 area

?

INSERT into EMP values (100, ' Li Mo sorrow ') =à data is stored in the P2 area.

?

Test whether the partition is used when fetching data:

?

?

When extracting the data, the criteria must be partition by range (store_id) and the field inside the range.

3, the list partition and the range partition have the similar place,

Example: If you create a table as follows, the table holds the staff records for 20 supermarkets, the 20 supermarkets are numbered from 1 to 20. And these 20 supermarkets are distributed in 4 distribution areas, as shown in the following table:

CREATE TABLE EMP (

ID int NOT NULL,

Name varchar (+) NOT null default ' ',

store_id int NOT NULL

)

Partition by List (store_id) (

Partition P0 values in (5,6,7,8),

Partition P1 values in (11,3,12,11),

Partition P2 values in (16),

Partition P3 values in (21)

)

Note: When using a partition, the field after the where must be a partition field in order to use the partition.

?

4, the limitation of the partition table;

(1) You can only partition an integer column of a data table, or a data column can be converted to an integer column by a partition function

(2) Maximum number of partitions cannot exceed 1024

(3) If a unique index or primary key is included, the partition column must be contained in all unique indexes or primary keys.

(4) Partitioning by date is very good, because many date functions can be used. However, there are not too many partitioning functions that are appropriate for a string.

10. Other Tuning:

1. Select the appropriate storage engine (MyISAM InnoDB)

• MyISAM: The default MySQL storage engine. If the application is read-only and insert-based, there are few updates and deletions, and the integrity of the transaction is not very high. Its advantage is the fast access. (especially for forum post tables)
• InnoDB: Provides transactional security with commit, rollback, and crash resiliency. However, compared to MyISAM, write processing is less efficient and takes up more disk space (use InnoDB if the security requirements are high). [Account, points]
• Memory/heap [some frequently accessed, frequently changing, and unnecessary data storage: such as user status]
• Description: The data for the memory table is in RAM, so the operation speed is fast, but the missing is when MySQL restarts, data is lost, but the structure of the table is in.
• Note: Starting with mysql5.5.x, the default storage engine change to INNODB,INNODB is designed for maximum performance when dealing with large amounts of data. Its CPU efficiency could be unmatched by any other disk-based relational database engine.

2, the choice of data type

(1) In the application of high precision, it is recommended to use fixed-point number to store the value, in order to ensure the accuracy of the results. Decimal do not use float

(2) to use to store mobile phone number, which type is more appropriate. If we want to use char (11), if the character set is utf8, how many bytes are consumed. 11*3==33, if the GBK character set occupies 11*2=22 bytes,

If the bigint type is stored, it occupies 8 bytes,

(3) If you want to store the IP address. If you use char (è ) to take up a lot of bytes, can you use an integer type to store it?

You can convert an IP address to an integer by using a function. You can use int to store

Inet_aton (): Converts the IP address to an integer.

Inet_ntoa (): Converts an integer to an IP address.

When removed:

(4) Select the minimum integer type as required. For example, the user online status: offline, online, leave, busy, implicit, etc., can be expressed as 0,1,2,3,5, there is no need to use char () or varchar () type to store the string.

3, the MyISAM watch the timing maintenance

For the MyISAM storage engine, the Optimize table table name needs to be executed periodically, and the Optimize table statement eliminates the disk fragmentation caused by the deletion and update, thus reducing wasted space.

CREATE TABLE TEMP2 (id int) Engine=myisam;

INSERT into TEMP2 values (1);

Before the capacity of the data:

Syntax format: Optimize table name:

After the cleanup has finished fragmentation.

How to optimize common databases