Summary: Mysql optimization and mysql Optimization

Summary: Mysql optimization and mysql Optimization
Mysql storage engine

Select the appropriate storage engine Innodb myisam

Myisam: writes data very quickly and is suitable for dedecms, phpcms, discuz, Weibo systems, and other systems with many write and read operations.

Innodb: Suitable for systems with strong business logic and with many modification operations, such as ecshop, crm, office system, and mall system. Default storage engine above mysql5.5

Mysql Index

Add Index

1. Add the primary key (primary key index)

Mysql> alter table 'table _ name' add primary key ('column ')

2. add UNIQUE (UNIQUE index)

Mysql> alter table 'table _ name' add unique ('column ')

3. Add an INDEX (Common INDEX)

Mysql> alter table 'table _ name' add index index_name ('column ')

4. Add FULLTEXT (full-text index)

Mysql> alter table 'table _ name' add fulltext ('column ')

5. Add a composite index

Mysql> alter table 'table _ name' add index index_name ('column1 ', 'column2', 'column3 ')

 

Delete Index

 

View Indexes

 

Notes for creating an index

 

Execution Plan

Mainly used to analyze SQL statementsExecution status(Do not execute SQL statements) Obtain SQL statementsWhetherUsedIndex, UsedIndexes.

Syntax: explain SQL statement \ G or desc SQL statement \ G

 

Prefix Index

PassBefore FieldNBitThe created index is called "prefix Index ".

If the n-bit information of the front edge of a field is sufficient to identify the content of the current field, you can obtain the n-bit information of the field and create an index, this index occupies less space and runs faster

Syntax: alter table name add key/index (Field (First n digits ))

 

Query Cache

An internal interface provided by the mysql server for caching select statement resultsMemory Cache System.

If the query cache is enabled, all query results are cached and the same select statement is used. When you perform another query, the cached results are directly returned.

Enable Cache

> Show variables like 'query _ cache % '; // view cache Usage

 

Query_cache_size: cache space size

Query_cache_type: whether cache is enabled

Query_cache_type = 1

Query_cache_size = 134217728

Note: Enable this function in my. ini.

 

Cache failure

If the data in a data table (data modified) or the data table structure (field addition or subtraction) changes, All cached data is cleared, that is, the cache is invalid.

 

Disable Cache

SQL _no_cache does not cache

Select SQL _no_cache * from emp where empno = 123456;

 

View Cache Usage

> Show status like 'qcache % '; // view Cache Usage

 

Partitioning technology

List Partition

List:The condition value is a data zone.Whether to partition a field in a region based on its content value

 

 

Query:Explain partitions select * from p_list where store_id = 20 \ G

Key:When using a partition, the field after where must be a partition field to use the partition.

 

Result of not using partition fields:

 

 Syntax:

Create table p_list (

Id int,

Name varchar (32 ),

Store_id int

) Engine myisam charset utf8

Partition by list (Store_id)(

Partition p_north values in ),

Partition p_east values in ),

Partition p_south values in (3,12, 19,20 ),

Partition p_west values in (8, 14, 15, 16)

);

Range partitioning

Hash Partition

Key Partition

Delete Partition

① Data will not be lost in the key/hash field (data will be re-integrated into the remaining partitions after the partition is deleted)

② Data loss may occur in the range/list Field

2) Delete list-type table shards (data loss occurs)

Alter table p_list drop partition p_north;

Note: The data contained in the partition is also deleted.

 

Add Partition

Method of remainder calculation: key/hash

> Alter table name add partition partitions quantity;

 

Range: range/list

> Alter table name add partition (

Partition name values less than (constant)

Or

Partition name in (n, n, n)

);

 

Note: The field used to create a partition must be a primary key (unique) or a primary key (unique ).

 

 

Frontend Optimization

Static Page

Real static (ob cache) uses the PHP file read/write function and ob cache mechanism to generate static pages

Pseudo-static (rewrite mechanism)

Reduce http requests

Enable the compression (apache configuration) Sprite

JavaScript code Mixing

 

Backend Optimization

Table Design:

Table design conforms to the three paradigm

Select an appropriate field and set it to not null.

Use foreign keys

Index creation to speed up query

Select an appropriate storage engine

 

Query Optimization:

Less Like wildcard and select * are used for connected table queries *

Transaction Processing avoids large transaction operations and improves system concurrency

Database read/write splitting Load Balancing