About MySQL Database optimization

MySQL database optimization related knowledge:

The choice of two common engines

1, MyISAM

Default engine when MySQL version is less than 5.5

Advantages: Good at data processing, high-speed reading and writing; The data is stored in the order of insertion, the insertion speed is fast, and the space consumption is small.

Features: Full-text Indexing support (InnoDB with version greater than or equal to 5.6), can use Myisampack to complete the compression function of the data

Disadvantage: Only table-level locking is supported, concurrent inserts are supported, and insert operations in write operations do not affect other operations. Transaction not supported

2, InnoDB

Default engine with version greater than or equal to 5.5

Pros: Provide transactional, row-level locking, foreign key constraints, and focus on data integrity and security

Features: Transaction, FOREIGN KEY constraint, maintain data integrity; good at concurrent processing, support row-level locking

Cons: Data is stored in the order of the primary key, and the sorting is inefficient when inserted

Second, the choice principle of data type

On the premise of satisfying the requirements:

1, the data as small as possible, so that the occupied storage space is small

2, as long as possible, so that the occupied storage space fixed, reduce the loss of the calculation when the variable length

3. Use integers whenever possible

Iii. Common means: Index

1. Concept

Index is the use of keywords to make a recorded part of the data and record location has a direct correspondence

2. Type of index

① Primary index (primary key): Requires that the keyword cannot be duplicated and cannot be null while adding a PRIMARY key constraint

② Unique index: Requires that the keyword cannot be duplicated while adding a unique constraint

③ Normal index: no requirement for keywords

④ Full-text index (fulltext index): The source of the keyword is a special keyword extracted from the field

⑤ composite index, extracting keywords from multiple fields requires a composite index

3. Add Index

ALTER TABLE table_name Add {(primary key), (unique index), (index), (fulltext Index)} index_name (' field name ');

4. Delete Index

ALTER TABLE table_name DROP {(primary key), (index) ...} index_name;

Tips: You can use explain to get execution plans for query statements

5. Usage Scenarios

① Index Search: Conditionally filtered fields add indexes, such as Where,select

② Index Sort: if order by

③ Index overrides: If the field following the where has already appeared in multiple fields after the Select, just add the composite index of the field after select

6. Rules of Use

① Column Independence: Index fields need to be guaranteed independently on one side

② left principle: The matching pattern must be on the left to determine that it cannot start with a wildcard, that is, the condition after like cannot begin with% _

③ Compound index left rule: Compound Index associates multiple fields, where when only the leftmost field of a composite index is effective

Use of ④or: you need to ensure that the conditions on or both sides have an available index, and the query uses the index

⑤mysql Smart selection: Querying using an index results in a large number of random Io, which is automatically deprecated when the random IO is larger than the sequential traversal of IO

⑥ is the best for searching, sorting, and overwriting at the same time

7. Prefix index (a scheme for indexing keywords that cannot be used for index overrides)

Index ' index_name ' (' Index_field ' (n)) indexed using the first N characters of the Index_field

Tips for determining N:

① calculation of maximum identification:

Select All_count/count (distinct field name) from TABLE_NAME, the closer the value is to the limit

② Calculate the identification of the first n characters:

Select All_count/count (Distinct substr (field name, 1, N)) from table_name; increase the value of N to the maximum of N to establish the index of the first n characters

8. Full-Text Indexing

Give a chestnut to solve the problem of matching queries like '%keyword% '

Select * from articles where the title like '%database% ' or body like '%database% '; At this time cannot establish ordinary index, the query does not conform to the left principle, the establishment also cannot use. At this point, the full-text index can function:

ALTER TABLE articles add fulltext index ' title_body ' (' title ', ' Body ');

The full-text index match syntax is then used with match () against () to take effect. Used in this chestnut

SELECT * from article where match (title, body) against (' database ');

Note: ① does not work on Chinese, ② words such as in a are not meaningful, because the full-text index is the extracted keyword in the data

9. Data structure of the index (hash, b-tree, clustered index/cluster index)

Four, query cache Query_cache

Cache area that stores the query results of a select for two uses

To open a method:

①show variables like ' query_cache% ';

②set Global query_cache_type = 1; Turn on query caching

③set Global query_cache_size = 1024*1024*32;//set query cache to 32M

Note: ① query strictly since the SELECT statement itself, the statement order, casing can not be changed; ② cannot contain dynamic data; ③ can use Sql_no_cache statements to make the statement non-cached, such as select Sql_no_cache * from emp where empno = 1234567;

V. Zoning (PARTITION)

Store data from a table separately in different regions

1. Create partitions, specify options for partitioning when creating tables

CREATE TABLE table_name (definition) partition by partition algorithm (parameter) partitioning options

Tips: Use show variables like ' Have_partitionong ' to see if partitioning is supported

2. Four partitioning algorithms

Remainder: Key,hash

Condition: List,range

For example:

CREATE TABLE xxx (XX) partition by key (ID) partitions 5;//divide the table by ID into 5 extents

Paritition by Hash (month (date)) Parititions 12;//takes the table out of date with a 12 month

Paritition by List (month (date)) (Paritition spring values in (3,4,5), Paritition summer values in (6,7,8,9), partition AUT Umn values in (10,11), partition winter values in (12,1,2));//Use list to divide by specific values

Partition by range (date) (partition p_80 values less than (1990), partition p_90 values less than, partition P_00 values less than maxvalue);//partitioning with Range

3. Managing partitions

① take out Key has in

Increase the number of partitions: Add partition Partitions N

Reduced number of partitions: Coalesce partition N

② conditions in the list range

Add Partition: Add partition (Pritition p_new00 values less Than (2010));

Delete partition: Drop partition partition_name;//Note that will cause deleted partition data to be lost

4. Select the partitioning algorithm

① average allocation: Key take-off partition according to primary key

② According to a business logic partition: Select the most easily filtered field, such as the integer type

VI. Consolidation of tables

You can use Mrg_myisam to combine multiple MyISAM tables with the same structure

Vii. logic

① concurrency of SQL: Less use of multiple table operations, such as subqueries, Jion, etc. to execute complex SQL differencing multiple times

② the insertion of large amounts of data:

For MyISAM: It is recommended to use ALTER TABLE table_name disable keys to disable index constraints, and after a large amount of data has been inserted, use ALTER TABLE table_name enable keys to open

For InnoDB:

Drop INDEX, drop constraint to keep the primary key BEGIN TRANSACTION|set autocommit=0; The data itself has been sorted by the primary key value] A large number of insert commits; Add index, add constraint

In addition, INSERT into table_name values (), (), () ... To 10-magnitude units can be, not too much

Eight, slow query log

Locating a slow query statement

Show variables like ' slow_query% '//view slow query log status and location

Show variables like '%long_query% '//Set the time critical point for fast and slow queries

About MySQL Database optimization