Key technologies for improving access speed for large websites-MySQL optimization

1. rational table design (in line with 3NF) 1) What kind of table is in line with 3NF (paradigm) table paradigm first is in line with 1NF, in order to meet 2NF, and further meet 3NF1NF: that is, the columns in the table are atomic and cannot be decomposed. as long as the database is a relational database, 1NF2NF is automatically satisfied: the records in the table are unique and 2NF is satisfied, usually design a primary key (excluding the business logic, generally from 1. table design rationalization (in line with 3NF)

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

First, the table's paradigm conforms to 1NF, 2NF can be satisfied, and 3NF can be further satisfied.

1NF: The table columns are atomic and cannot be decomposed. as long as the database is a relational database, 1NF is automatically satisfied.

2NF: the record in the table is unique, and 2NF is satisfied. generally, a primary key (excluding the business logic, generally self-increasing) is designed to achieve this.

3NF: no redundant data exists in the table, that is, if the table information can be deduced, a separate field should not be set for storage (anti-3NF: in the case of 1-to-N, some fields may be designed to increase the efficiency-moderate redundancy) on the one side)

2) SQL statement optimization

How to quickly locate slow statements from a large project (locate slow queries)

① First, let's know how to query the running status of the mysql database (for example, how long mysql is running and how many times it has been executed: select/update/delete and the current connection)

show?status

Common commands:

Show? Status? Like? "Uptimeshow? Status? Like? "Com_select" (others and so on: com_insert/com_update/com_delete) show? [Session | global]? Status? Like? "Com_select" show? Status? Like? "Connections" // show slow query show? Status? Like? "Slow_queries"

② How to locate slow queries

By default, 10 seconds is a slow query (mysql)

Modify the slow query time of mysql:

show?variables?like?‘long_query_time’set?long_query_time?=?1

Create a large table:

Use Mysql User-defined functions (stored procedures can also be constructed using php)

How to record slow query SQL statements to our logs:

By default, mysql does not record slow queries. you must specify slow queries when starting mysql.

Mysqld ?? -- Safe-mode ?? -- Slow-query-log? [Mysql5.5 and later versions can be specified in my. ini] mysqld ?? -- Log-slow-queries =/tmp/test. log? [After mysql5.0, you can specify it in my. ini]

2. add an appropriate index (index) [s four types: normal index, primary key index, unique index, full-text index]

1) add an index

①? Add primary key index

When a table sets a column as the primary key, the column is the primary key index.

If no primary key index is added during table creation, you can also add a primary key index after the table is created:

Alter? Table? Table name? Add? Primary? Key? (Column name );

②? Common index

Generally, to create a common index, create a table and then create a common index.

Create? Index? Index name? On? Table? (Column)

③? Create full-text index (FULLTEXT)

Full-text indexes are mainly for file and file indexes, such as articles. Full-text indexes are only valid for the MyISAM engine.

How to use full-text index:

Select? *? From? Articles? Where? Body? Like? '% Mysql % ';?? [Full-text index not used] select? *? From? Articles? Where? Match (title, body )? Against ('database ');? [Correct]

Note: fulltext only takes effect in English, and sphek (coreseek) technology is used in Chinese. full-text indexes have a concept called Stop Words (only indexes are created for uncommon words, but some common words won't be created ).

④ Create a UNIQUE index (UNIQUE)

When a column in a table is specified as a unique constraint, it is a unique index.

The unique index can be blank.

Create? Unique? Index? On? Table? (Column );

2) query indexes

Desc? Table name? [Disadvantage: the index name cannot be displayed] show? Index (es )? From? Table name show? Keys? From? Table name

3) delete an index

Alter? Table? Table name? Drop? Index? Index name;

If the primary key index is deleted:

Alter? Table? Table name? Drop? Primary? Key;

4) modify the index

Delete first, and then create again

5) Use of indexes

Impact:

Disk IO usage

Reduce the efficiency of DML (add, delete, modify) statements? --? Binary Tree Index Maintenance

Which columns are suitable for adding indexes:

6) notes for using indexes:

① If the leftmost column is used for the created multi-column index as long as the query conditions are used, the index is generally used;

② For like queries, if the query is '% aaa', no index is used, 'AAA % 'will use the index. the keyword' % 'or' _ 'cannot be added before the like Query. if there is a change in the value, full-text indexing is considered;

③ If there is or in a condition statement, it will not be used even if there is a condition with an index. In other words, all fields used must be indexed;

④ If the column type is a string, you must quote the data in the condition using quotation marks; otherwise, the index will not be used;

⑤ If mysql estimates that using full table scan is faster than using indexes, no indexes will be used.

Why is the query speed faster after an index is created?

Binary Tree algorithm (BTREE) [efficiency? Log2N? For example, 10 searches can theoretically scan 1024 pieces of data]

Explain can help us know how to execute mysql without actually executing an SQL statement. we can use this command to analyze SQL commands.

7) query the index usage

show?status?like?“Header_read%”;

The higher the value, the higher the index usage.

8) add data in large batches

For MyISAM:

Alter? Table? Table_name? Disable? Keys; Loading? Data // insert? Statement alter? Table? Table_name? Enable? Keys;

For InnoDB:

Data to be imported is sorted by primary key

Set? Unique_checks = 0. disable the uniqueness check set? Autocommit = 0. disable automatic submission.

9) SQL optimization tips

When using group? By grouping query is performed after the default grouping, which may reduce the speed.

In group? Add order? By? Null will block sorting.

Use left? Join (left outer join) replaces multi-table query

10) select an appropriate storage engine

MyISAM:

If tables do not have high transaction requirements and are mainly query and addition, consider using the MyISAM storage engine, such as the article table and reply table.

InnoDB:

We recommend that you use InnoDB, such as order table and account table, because the transaction requirements are high and the stored data is important data.

Memory:

If data changes frequently and is frequently queried and modified, use the memory storage engine;

Extremely fast (data is stored in memory)

Differences between MyISAM and InnoDB:

Transaction Security; query and acceleration; support for full-text indexing; lock mechanism (table lock and row lock); foreign key

If your database storage engine is MyISAM, you must regularly clear the fragments (otherwise, the deleted data will never be lost ):

optimize?table?table_name;

3. table sharding technology (horizontal and vertical)

The key is to find the criteria for segmentation.

1) horizontal segmentation

Add data:

Query data:

2) vertical segmentation

Some fields in a table are not frequently concerned during query, but the data volume is large. you can separate these fields to improve efficiency. The frequency of use is not horizontally separated.

4. read/write [write: update/add/delete] separation

If the database is under great pressure and cannot be supported by one machine, you can use mysql replication to synchronize multiple machines to distribute the database pressure.

5. Stored Procedures (no compilation is required for modular programming) 6. MySQL Configuration Optimization (maximum concurrency and cache size)

1) the most important parameter is the memory. We mainly use the innodb engine. so the following two parameters are very tuned?

innodb_additional_mem_pool_size?=?64Minnodb_buffer_pool_size?=1G

2) for myisam, you need to adjust the key_buffer_size

Of course, to adjust the parameters, you still need to check the status. use show? The status statement shows the current status to determine which parameters to adjust.

3) in my. ini, modify Port 3306. the default storage engine and the maximum number of connections

7. Mysql server hardware upgrade (memory and CPU)

If the memory of your machine exceeds 4 GB, you should undoubtedly use a 64-bit operating system and a 64-bit mysql? 5.5.19

8. clear unnecessary data at regular intervals and perform fragment at regular intervals (especially for the MyISAM engine)

Select the appropriate field type, especially the primary key.

Save small but not large, saving space as much as possible

PHP regularly backs up databases

1) manual backup

Mysqldump? -Uxxx? -Pxxx? Database? [Table1? Table2]>? Xxx. SQL

Restore data

source?xxx.sql

2) automatic backup

Write the backup command to a batch processing file, and then run it regularly through the task scheduler (crontab)

The problem is that each time the original backup file is overwritten, it is not conducive to phased Backup. you can solve this problem through php code.

 ?xxx.sql’;    exec($command);?>

Then execute the php code regularly.

MySQL incremental backup

Principle of incremental backup

The Mysql database records user operations on the Mysql database in binary form to the file.

You can use the backup file to back up data when you want to recover it.

Incremental backup records DML statements, table creation statements, and select statements.

Record: Operation statement, operation time, Operation location

How to perform incremental backup and recovery

1) configure my. ini or my. conf to enable binary backup (supported after 5.1 ):

log-bin?=?/path/to/log

2) start mysql to get the file:

Mylog. index ?? Index file? What incremental backup files are available: mylog.000001? Store the user's database operation files

3) you can use the mysqlbinlog program to view the backup file content.

Mysqlbinlog? Backup file path

1) restore data

It can be restored by time or by location.

Deadline

mysql?--stop-datetime=”2014-02-27?17:37:58”?/path/to/log?|?mysql?-uxxx?-pmysql?--stop-position=”111”?/path/to/log?|?mysql?-uxxx?-p

Start time

--start-datetime?/?--start-position

Time period

mysql?--start-datetime=”2013-02-27?17:37:58”?--stop-datetime=”2014-02-27?17:37:58”?/path/to/log?|?mysql?-uxxx?-p

Example

How to use full backup and incremental backup at work:

Perform a full backup every Monday and enable incremental backup. the Expiration Time is set to> = 7 days;

You do not need to enable the timer for incremental backup;

If the database crashes, it can be recovered by time and location.