MySQL "Turn" of database optimization

1. Optimize flowchart

MySQL optimization (mainly increase the database select query, make the query faster)

2. optimizing the MySQL aspect

Mainly from the following four aspects to optimize MySQL

① Storage Tiers: How to select a database engine, select the appropriate field column type

② design layer: satisfies the three normal forms of the table, establishes the appropriate index, partitions the table

③sql layer: Design Better SQL statements

④ Architecture Layer: MSYQL read/write separation

2. engine for database tables

1. Introduction to the engine

Different data-table engines have different ways of storing data, and their index structure is not the same, providing distinct functionality.

For example: The InnoDB engine provides transactional functionality, and the MyISAM engine has no

2. related operations for the engine of the database table

①, see which data table engines are supported by the database

After interview basically can answer MyISAM and InnoDB engine difference can

3. the difference between the MyISAM engine and the InnoDB engine

The difference between MyISAM and InnoDB engines:

①INNODB engine support transaction, MyISAM not supported

②innodb supports row locks, MyISAM supports table locks

③myisam mainly used in high concurrency of some sites, InnoDB mainly used in logical (support transactions) relatively high some sites

command to modify the engine of a table

ALTER TABLE name Engine=innodb

How the 4.myisam engine and the InnoDB engine generate data files

① creating a MyISAM engine table

Open the Data directory of the MySQL installation directory to view the created table structure file:

②INNODB engine

View the data directory of the MySQL installation directory to see the structure of the table:

Its data file (table name. MYD) and the index file (table name) of the data table. MYI) Where are you going?

4. Backup of the database

mysqldump command to back up the database:

Backup operation (mysqldump):

Restore operation:

First set up a database such as (DB2)

command to perform the restore (MySQL)

MySQL Visualizer backup and restore:

Backup operation:

Restore operation:

5. how the Myiam engine and InnoDB engine insert data (problem)

①myisam Engine Table Insert Data

②INNODB engine inserts the same data

Summarize:

The ①myisam engine inserts data without inserting it in the order of the primary key, without a sort action inside

② when inserting data using InnoDB, the interior will be sorted according to the primary key, and the ordering process will need to consume time and memory.

So if we're going to do mass data insertions using MyISAM's engine,

3. Select the appropriate field type

1. basic principles for selecting fields

① is stored using the smallest column type (if you can use tinyint, do not use int), because large data types occupy a larger byte space, so retrieving data is slower.

② when defining a table's field type, it is recommended to set to NOT NULL, because a null takes up a byte of space, preferably setting a default value such as (varchar NOT NULL default ")

③ If a value is positive, we should set it to unsigned because the amount of data stored in the unsigned type is larger.

2. MySQL field type review

char (fixed length string type): occupies 0-255 characters

varchar (variable-length string type): 0-65535, 1-2-byte space to save the data type itself

Text: Basic No Limit

Date: Dates: As of 2016-09-12

DateTime: DateTime: 2016-09-12 12:12:12

Enum: enum type; generally used in radio enum (' Male ', ' female ')

Set: The data type of the set: generally used in multi-select Set (' Learn code ', ' Eat ', ' sleep ');

3. How to select a field type to store data

Question 1: What is the best type of storage for a time?

Is it recommended that you use int? because using the int type is convenient for converting to a date or datetime, it can also be convenient for a comparison query of our time.

Sometimes you can use Datatime to get the data out so you don't need to convert it in a template.

Issue 2: Storing an indeterminate piece of data (e.g., title of article)

This time use varchar (20);

Because the length of the title is not sure how long, we use varchar, if the char type is used to store it, it consumes the specified amount of space, wasting space, and retrieving data slower

such as storing MD5 encrypted password with char (32),

If mobile phone number 11 is: char (11)

IP and digital conversions in problem 3:mysql

You can use int to store our IP addresses:

IP and Digital conversions in PHP:

Ip2long (' IP address '): Convert IP addresses to numbers

Long2ip (): Convert the number to an IP address

4. three paradigms for the construction of tables

When we build a table, we basically meet the three paradigms.

1. First paradigm (1NF)

The first paradigm guarantees the atomicity of the field,

If you create a user table

ID Name Address

1 small white Guangdong Guangzhou Tianhe District

2 Little black Guangdong Shenzhen Bao ' an district

The Address field for the above user table does not meet the first normal form, because the address value can be divided into provincial, city, and detailed addresses

Modify the table structure above to:

ID Name Province City area

1 small white Guangdong Guangzhou Tianhe District

2 Little black Guangdong Shenzhen Bao ' an district

2. second paradigm (2NF)

The second paradigm is to ensure that each field and primary key can not have transitive dependencies

Order Form:

order_id room_id Username Card phone

1 202 Small white 234553X 13433333333

2 203 Small white 234553X 13433333333

The above order form has a problem, if a customer has more than one order, so long corresponds to a number of order records, will produce more than one user's relevant information, then the more users out of the information there is data redundancy

The retrofit order form is as follows, to set a table for user information individually

Order Form

order_id _id user_id

1 202 8

2 203 8

User table

USER_ID User name Card phone

8 Small white 234553X 13433333333

3. The third paradigm (3NF)

To ensure that each field and primary key are directly dependent, the above-mentioned completion of the second paradigm already satisfies our third paradigm.

The basic table to be manipulated for the next order is as follows:

Order Information Form

Order Address

User Information table

Product Information Sheet

Commodity coupon Form

Commodity attribute Table

Product Picture Table

Product Classification Table

5. the index in MySQL

1. Introduction to the index

Index of Life:

The catalogue of books, the public transport cards,

Using the index can speed up the select query, but it will be less efficient for our write (Insert,update,delete) operations.

2. classification of MySQL indexes

① primary key Index primary key cannot be null NOT NULL a table can have only one primary key index

② Unique index Unique key can be empty, a table can have multiple unique indexes

③ Normal index Key can be empty, a table can have more than one normal index

④ Full-Text index fulltext index (varchar text) (Sphinx)

Composite Index: An index that consists of multiple fields

3. Index Basic Operations

There are generally two ways to create an index

① specifying an index when creating a table (Create)

② How to modify the table structure (alter)

① primary key index and additions and deletions

A. Creation of primary key indexes

CREATE TABLE Tab_name (

ID int NOT NULL auto_increment

Primary KEY (ID)

)

If a field is autogrow, it must be a primary key value, but the primary key value does not have to grow automatically

Delete PRIMARY KEY index: ALTER TABLE tab_name drop PRIMARY key

(Note: If a primary key field is automatically growing, you must delete the auto-growing attribute before you can continue to delete the primary key)

Add a PRIMARY KEY index: ALTER TABLE Tab_name add primary key (ID)

② General index additions and deletions

CREATE TABLE table_name (

ID int NOT NULL auto_increment,

Name varchar (+) NOT null default ' ',

Email varchar (+) NOT null default ';

Primary key (ID),//primary key

Unique key (email),//Unique

Key Key_name (name)//Common key

)

Modifying the structure index of a table

The addition of a unique index

ALTER TABLE table_name ADD unique index UNI_EM (email)

ALTER TABLE table_name add unique key uni_em (email)

Adding operations to normal indexes

ALTER TABLE table_name add key index_name (name)

ALTER TABLE table_name ADD index index_name (name)

4. construct a large data table

Can make a worm copy

Insert into User (username) select username from user

Insert into User (Username,email) select Username,email from user

example, a test that uses a primary key and does not use a primary key to query a record

① record information using the primary key id=2000000

② do not use primary key index for querying

5.explain Analyzing index usage for SQL statements

You can use explain to know the basics of using an index for an SQL statement, just add a explain or desc in front of the query statement to better optimize our SQL statement

Such as: Explain SELECT * from users where id=200000;

Desc SELECT * from users where id=200000;

The primary key index was used:

A full table scan was performed without using the index query

6. How to use the index correctly

① Enter the field following the where condition, we can set the field to index

Set the field No_index to normal index

To view the Build Table statement:

Again, you can see that the index is used:

② usage of fuzzy query index

There are two general conditions for keyword queries:

Like '% keyword% ' ========> not time to index

Like ' keyword% ' ========> use to index query

The index query is not used as long as the keyword has a% left

③ Optimizing an ORDER BY statement using an index

④, optimizing paging limit statements

⑤ the leftmost principle of composite index (interview frequently asked)

As long as the field that we queried after the where condition appears in the leftmost field of the composite index, it is used to the index query.

Create a composite index first

Build a composite index, username, and email fields as composite indexes

ALTER TABLE users add index Index_user_email (username,email);

A.

B.

If you want to use the following fields of a composite index, you must first use the leftmost field

Index length calculation for composite indexes:

215 = (30+40) *3+2*2 (variable type itself 2) +1 (NULL)

Utf-8 Next character occupies 3 bytes

GBK: The next character takes up 2 bytes

Six. Slow log query

Brief introduction

When a SQL execution is slow (10 seconds), we can record the function of a slow log query via MySQL

This slow SQL, we can define a SQL statement that we think is slower (10 seconds), then the SQL statements over 10 seconds are recorded so we can optimize our problematic SQL statement

Turn on slow log queries

① turn on slow log query:

② Modify the MySQL configuration file, add the following two options

Remember to save and restart the MySQL server.

③, test:

Open the slow log file as follows:

Note: When MySQL queries, sometimes (the default) has a cache, we can disable the cache through the following configuration

You can also query cached data without querying.

Select Sql_no_cache from table_name ...... .....

Turn from

MySQL https://www.toutiao.com/i6501466752893846029/of database optimization

MySQL "Turn" of database optimization