Shulou(Shulou.com)06/01 Report--

1. Course introduction

1. Introduce what is mysql optimization

The optimization method of ZUG 2.mysql

The use of the 0.3.Mysql index

4. Sub-meter technology

2. Overview of mysql optimization

Overview: earlier we learned about page static and redis, which speed up by not manipulating the mysql database. But some functions must operate the database, which requires us to optimize the mysql itself.

Common methods for mysql database optimization:

1. The design of the table should be reasonable (meet the 3NF) 3 normal form

two。 Create appropriate index [primary key index | unique index | General index | full-text index | Spatial index]

3. Optimize SQL statements-> locate slow query (explain)

4. Use sub-table technology (focus [horizontal sub-table, vertical sub-table]), partition technology (understand)

5. Read-write separation (configuration)

6. Create appropriate stored procedures, functions, triggers

7. Optimize my.ini, optimize configuration

8. Software and hardware upgrade

3. The table is designed to meet 3NF

Summary: at present, the highest-level paradigm for our table design is "6NF". For PHP programmers, our table meets 3NF.

3.1. 1NF

The so-called 1NF is

(1) refers to the atomicity of the attributes (columns) of a table, that is, the columns of a table can no longer be divided.

(2) there can be no duplicate columns

Special

(1) as long as it is a relational database, it naturally satisfies 1NF.

(2) Common databases

Relational databases (mysql, oracle, sql server,informix, db2, postgres)

Non-relational data (Nosql type database consists of Redis, MongoDB)

3.2. 2NF

The so-called 2NF means that we cannot have a record (row) that is completely duplicated in our table. In general, it is done by setting a primary key, which is self-increasing.

3.3. 3NF (foreign key)

The so-called 3NF means that if the contents of a column can be derived (explicitly or implicitly), then we should not store it in a separate column.

For example: the following is to satisfy 3NF

3.4. Anti-3NF

In general, our watch design is strictly 3NF-compliant, but there are exceptions. Sometimes in order to improve the efficiency of queries, we need to violate 3NF. For example:

4. Construct sea scale and locate slow query

To explain this optimization, we need to build a sea scale (8000000), and each piece of data is different.

4.1. Steps of constructing sea scale

(1) create a test database

(2) create a table

CREATE TABLE dept (/ Department Table /

Deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0

Dname VARCHAR (20) NOT NULL DEFAULT ""

Loc VARCHAR (13) NOT NULL DEFAULT ""

) ENGINE=MyISAM DEFAULT CHARSET=utf8

Join data: dept.sql

# create table EMP employee

CREATE TABLE emp

(empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, / number /

Ename VARCHAR (20) NOT NULL DEFAULT "", / name /

Job VARCHAR (9) NOT NULL DEFAULT "", / work /

Mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0pr / superior number /

Hiredate DATE NOT NULL,/ entry time /

Sal DECIMAL (7) NOT NULL,/ salary /

Comm DECIMAL (7 ~ 2) NOT NULL,/ bonus /

Deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 / Department number /

) ENGINE=MyISAM DEFAULT CHARSET=utf8

Join data: emp.sql

# salary scale

CREATE TABLE salgrade

(

Grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0

Losal DECIMAL (17Pol 2) NOT NULL

Hisal DECIMAL (17Pol 2) NOT NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8

Join data: salgrade.sql

4.2. The problems caused by the sea scale

Look at a case.

4.3. First use the index to get it done.

L add a primary key index to the empno segment

Alter table emp add primary key (empno)

One table (storage engine is MyISAM), corresponding to three files xx.frm table structure xx.MYD data file xx.MYI index file

L pass the test to see the effect

L delete the primary key index of the emp table

Alter table emp drop primary key

4.4. How to locate slow queries (slow query)

Description: by default, mysql does not record slow queries, so when we test, we can specify mysql records slow queries.

There are two ways to turn on slow queries:

When l starts, start like this

Cmd > bin/mysqld.exe-- safe-mode-- slow-query-log

Or

Add the code under [mysqld] in my.ini and restart it

Log-slow-queries = D:/server/mysql/mysqlslowquery.log (note the slash)

Note: mysql5.6 version slow-query-log-file

Long_query_time = 1 specifies a slow query for more than 1 second

L for testing, we modify the long_query_time

L record slow query

Time: 141122 10:39:45User@Host: root [root] @ localhost [127.0.0.1] Query_time: 1.625093 Lock_time: 0.001000 Rows_sent: 0 Rows_examined: 8000000

Use testdb

SET timestamp=1416623985

Select * from emp where ename='IUYTOPUYQWE'

Description: Query_time is the time to query

Lock_time: wait time

4.5. Opening slow query sacrifices the execution efficiency of sql

How do I use slow queries?

After the system is online, the slow query will be started for one week. When you think that the system is slow due to the increase of data. Then open the slow query to find the slow sql statement, and then optimize it.

4.6. How to analyze the problem of a sql statement-explain tool

Basic usage:

Explain sql\ G

L case:

L optimization

Add an index.

4.7. Variable query of mysql

Mysql > show variables

Mysql > show variables like'% xxxx%'

Show tables like 'Datasheet name'; / / query whether a datasheet exists

If you need to know the specific meaning of each variable, you can consult the manual.

5. Detailed explanation of the index (key points)

5.1. Index creation

5.1.1. Creation of primary key index

There are two forms of primary key index creation, 1. When you create a table, you directly specify a column or columns of primary keys, and then there is a primary key index, 2. After adding the table, specify the primary key index

L directly create a primary key index

Note: if it is self-increasing, the primary key cannot be deleted.

L create the table before specifying the primary key

Increase primary key

ALTER TABLE table name ADD PRIMARY KEY (column 1, column name 2.)

Characteristics of primary key index

1. A table can have at most one primary key

two。 A primary key can point to multiple columns (compound primary key)

3. Primary key index is the most efficient, so we should give id, generally id is self-increasing.

4. The primary key index column cannot be repeated and cannot be null

5.1.2. Creation of unique index

L specify a column or columns unique index directly when creating the table

After the table is created, specify a column or columns that are unique indexes

Description: to use the create unique index directive, you must specify an index name.

Description: using the alter table instruction, you can specify an index name or not.

Characteristics of unique index

1) A table can have multiple unique indexes

2) unique indexes cannot be duplicated, but if you do not specify not null, the unique index column can be null, and there can be more than one.

3) when to use a unique index, and when a column of data will not be duplicated.

4) the efficiency of unique index is also very high, so priority can be given to it.

5.1.3. Creation of general index

L specify the index when creating the table, through key or index

L after the table is created, specify a column or columns to index

L add a normal index (2 ways)

L characteristics

1) there can be multiple ordinary indexes in a table, and an ordinary index page can point to multiple columns

2) the data of ordinary index columns can be repeated.

3) the efficiency is relatively low.

5.2. Query of index

L desc table name

L show keys from table name\ G

L show index from table name\ G

L show indexes from table name\ G

5.3. Modification of index

Delete first, then add.

5.4. Deletion of index

DROP INDEX index name ON table

ALTER TABLE table name DROP INDEX index name

5.5. Matters needing attention of index

Disadvantages of the index:

The speed of adding, deleting and correcting is slow..

Advantages:

The query speed is fast.

The index must be built according to your own needs.

Example:

Is the login user name suitable for indexing? The user name is suitable for indexing

Operation log:

Which controller and which method of the user name operation record the operation time?

It is not appropriate to build an index.

6. Optimization of sql statements and correct use of indexes

6.1. For a multi-column (composite) index created, the index is generally used as long as the query condition uses the leftmost column.

Name email

Alter table xxx add index (name,email)

Select from xx where name = 'xxx'

Select from xx where email = 'xxx'

Note: dname is the column on the left, so we find that when we use dname, we use the index, while the following sql statement does not use the index.

6.2. For queries using like, if the query is'% aaa''_aa' will not use the index 'aaa%' will use the index

Note: in the like statement, the index cannot be used if there is or% in the'', and the index can be used if it is in the middle or at the end.

6.3. If there is an or in the condition, all fields of the or must have an index, otherwise the index will not be used

Description: because deptno does not have an index, the entire sql statement does not use an index.

If you also create an index on deptno, you can use the index.

If mysql thinks that a full table scan is more efficient, it will not use an index, but will do a full table scan.

6.4. If the column type is a string, be sure to quote the data in quotation marks in the condition. Otherwise, no index is used.

6.5. In some cases, you can use joins instead of subqueries. Because you don't need to create temporary tables in memory to use join,MySQL

Subquery: select from emp where deptno in (select deptno from dept)

Connection: select from emp left join dept on emp.deptno=dept.deptno where emp.deptno=dept.deptno

6.6. The administrator is importing a large amount of data, which can speed up in this way

Bulk insert data (MySql administrator) understand

For MyISAM:

Alter table table_name disable keys

Perform insert statement import

Alter table table_name enable keys

For Innodb:

1. Sort the data to be imported by primary key

2, set unique_checks=0, turn off the uniqueness check.

3, set autocommit=0, turn off autocommit.

6.7. How to choose a storage engine

The principle of how to choose

(1) MyISAM: default MySQL storage engine. If the application is dominated by read and insert operations, there are only a few update and delete operations, and the transaction integrity requirements are not very high. Its advantage is the speed of access. (especially suitable for forum posts / information tables / news / commodity lists)

(2) InnoDB: provides transaction security with commit, rollback, and crash recovery capabilities. However, compared to MyISAM, write processing is less efficient and takes up more disk space (if security is high, use innodb). [account, credit, balance]

6.8. How to choose the correct data type

6.8.1. Try to choose a small type when meeting the demand.

6.8.2. In applications requiring high precision, it is recommended to use fixed-point numbers to store values to ensure the accuracy of the results. Don't use float for decimal.

For example:

Description: here we look at float (10Magne2), and decimal (10Magne2) decimal are more accurate. So we use the decimal type for columns with high precision.

6.8.3. For tables whose storage engine is MyISAM, defragment them regularly.

Example: when we have a large amount of data in the users table, after we delete the data, we find that the disk space is not reclaimed, so we need to defragment regularly. It is as follows:

Create a table:

Copy a large amount of data into the same table:

Optimize: this command completely deletes the data in the table from the data file.

(this article is originally published by the technical teacher of the source era. Please indicate the source when reproduced. )

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.