MySQL Database optimization Summary

Source: Internet
Author: User
Tags add time utf 8

First, the optimization operation of common 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, timely improve efficiency

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.

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-

{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.

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.

The slow query log is stored in the MySQL data directory. The My.ini configuration file has the data directory

#Pathto the database root

DataDir =" C:/documentsand settings/all users/application data/mysql/mysql Server 5.5/data/"


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.


Iv. Index

Set up the test table:


CREATE TABLE user (   ID int primary key auto_increment,   name varchar (+) NOT null default ", age   tinyint unsign  Ed NOT null default 0,   email varchar (+) NOT null default ",   ClassID int NOT null default 1) engine MyISAM CharSet Utf8;insert into uservalues (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 UTF 8;insert into Classvalues (1, ' Java '), (2, '. NET '), (3, ' PHP '), (4, ' C + + '), (5, ' iOS ');


1. Primary KEY index

You can add createtable emp (ID int primary key) in the build table

You can add: altertable tablename Add primary key (column 1, column 2) after creating the table

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: CreateTable 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 TABLETABLENAME 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. Use of explain

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

Vi. Index

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

the query condition is not used to the leftmost column, so the index is not used.




The leftmost column of the composite index is used in the condition, so the query is used to the index.



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, such as searching for a song name through lyrics and a plot search for a movie name. 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



Attention:

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


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,setunique_checks=0, turn off the uniqueness check.

3,setautocommit=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

By locking mechanism to solve

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.

The MySQL lock is available in the following ways:

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, the syntax is: locktable 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.

A similar question:

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

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


Ninth, Partition

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,    Namevarchar (+) NOT null default ' comment ' Employee name ',   store_id int NOT NULL Comment ' Supermarket number range is 1-20 ') engine MyISAM charset utf8partition by Range (store_id) (   partition p0 values less than (6),//s The tore_id value is less than 6 of the storage area.   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 (+),   partition P3 values (+ than)) insert into EMP values (1, ' haha ', 1)--à data is stored in P0 Zone INSERT INTO EMP values (23, ' Ha ha ', 15)--à data is stored in the P2 area insert into EMP values (100, ' Hee Hee ', 11) =à data is stored in the P2 area.


Test whether the partition is used when fetching data:


When extracting the data, the criteria must Partitionby range (store_id), and the field within range.


3. The list partition has a similar place to the range partition

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 Li St (store_id) (   partition p0 values in (5,6,7,8),   partition P1 values in (11,3,12,11),   partition P2 values in ( (+),   partition P3 values in (21))


Note: When partitioning is used, the where The fields that follow must be partition fields before they can be used to partitions.


4. Limitations 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

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 (15) è to occupy a lot of bytes, can you use 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.

Syntax format: optimizetable table name

Cleaning up the Shard will reduce wasted space!

MySQL Database optimization Summary

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.