Shulou(Shulou.com)05/31 Report--

In this article, the editor introduces in detail the "MySQL SQL optimization, index optimization, locking mechanism, master-slave replication knowledge", detailed content, clear steps, and proper handling of details. I hope that this article "MySQL SQL optimization, index optimization, locking mechanism, master-slave replication knowledge" can help you solve your doubts.

Introduction of 0 storage engine

Myisam storage: if the table is not transaction-oriented and mainly queries and additions, we consider using myisam storage engines, such as posting tables in bbs and replying tables.

Need to be defragmented regularly (because deleted data still exists): optimize table table_name

InnoDB storage: high requirements for transactions, saved data are important data, we recommend using INN0DB, such as order table, account table.

The interview asked the difference between MyISAM and INNODB:

1. Transaction security

two。 Query and add speed

3. Support for full-text indexing

4. Locking mechanism

5. Foreign key MyISAM does not support foreign keys, INNODB supports foreign keys.

Mermory storage: for example, our data changes frequently, does not need to be stored in the database, and is queried and modified frequently at the same time, we consider using memory

See what storage engine mysql provides: show engines

View mysql's current default storage engine: show variables like'% storage_engine%'

1 performance analysis of SQL

Reasons for SQL performance degradation:

1. The query statement is badly written.

2. Index invalidation (data change)

3. Too many join related queries (design defects or compelling requirements)

4. Server tuning and parameter setting (buffering, number of threads, etc.)

The usual SQL tuning process is:

Observe and run for at least 1 day to see the slow SQL of production.

Open the slow query log, set the threshold, such as slow SQL for more than 5 seconds, and grab it.

Explain + slow SQL analysis.

Show profile .

Or DBA, the operation and maintenance manager, tune the parameters of the SQL database server.

Summary:

1. Open and capture slow query

2. Explain + slow SQL analysis

3. Show profile queries the execution details and life cycle of SQL in the Mysql server

4. Parameter tuning of SQL database server

2 loading order of common JOIN query SQL execution

Handwriting order:

SELECT DISTINCT FROM JOIN on / / join_codition: for example, the employee's department ID and the department table key id are the same WHERE GROUP BY HAVING ORDER BY LIMIT

MySQL machine reading order:

1 FROM 2 ON 3 JOIN 4 WHERE 5 GROUP BY 6 HAVING 7 SELECT8 DISTINCT 9 ORDER BY 10 LIMIT

Summary:

The running order is up and down.

Seven ways to write JOIN

Create a table to insert data (left and right main foreign keys are connected):

CREATE TABLE tbl_dept (id INT (11) NOT NULL AUTO_INCREMENT, deptName VARCHAR (30) DEFAULT NULL, locAdd VARCHAR (40) DEFAULT NULL, PRIMARY KEY (id)) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 / / set storage engine, primary key auto-growth and default text character set CREATE TABLE tbl_emp (id INT (11) NOT NULL AUTO_INCREMENT, NAME VARCHAR (20) DEFAULT NULL, deptId INT (11) DEFAULT NULL, PRIMARY KEY (id), KEY fk_dept_Id (deptId) # CONSTRAINT 'fk_dept_Id' foreign key (' deptId') references' tbl_dept' ('Id')) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 INSERT INTO tbl_dept (deptName,locAdd) VALUES ('RD',11); INSERT INTO tbl_dept (deptName,locAdd) VALUES (' HR',12); INSERT INTO tbl_dept (deptName,locAdd) VALUES ('MK',13); INSERT INTO tbl_dept (deptName,locAdd) VALUES (' MIS',14); INSERT INTO tbl_dept (deptName,locAdd) VALUES ('FD',15); INSERT INTO tbl_emp (NAME,deptId) VALUES INSERT INTO tbl_emp (NAME,deptId) VALUES; INSERT INTO tbl_emp (NAME,deptId) VALUES INSERT INTO tbl_emp (NAME,deptId) VALUES ('s 9 minutes 51); # result of query execution mysql > select * from tbl_dept +-+ | id | deptName | locAdd | +-- + | 1 | RD | 11 | 2 | HR | 12 | 3 | MK | 13 | 4 | MIS | 14 | 5 | FD | 15 | + -+-+ 5 rows in set (0.00 sec) mysql > select * from tbl_emp +-+ | id | NAME | deptId | 1 | Z3 | 1 | 2 | Z4 | 1 | 3 | Z5 | 1 | 4 | w5 | 2 | 5 | w6 | 2 | 6 | S7 | 3 | 7 | S8 | 4 | 8 | S9 | 51 | | +-+ 8 rows in set (0.00 sec) |

1. Inner join: only the common parts of deptId and id

2. Left join (full A): common data in the first seven articles; Article 8. Unique data in Table a, null in Table B.

3. Right join (all B): common data in the first seven articles; Article 8, unique data in Table b, null in Table a

4. Left join unique A: the unique part of Table A.

5. Right join unique B: the unique part of Table B.

6. Full join:MySQL does not support full join, so the middle part is removed with full a + full b focus union.

The union keyword can be merged to remove duplicates

7, An and B have their own unique sets

3 Index introduction 3.1 what is the index

MySQL's official definition of Index is: index is a data structure that helps MySQL to obtain data efficiently (the essence of index is data structure, sorting + query functions).

The purpose of indexing is to improve query efficiency, which can be compared to dictionaries.

If we want to look up the word "mysql", we definitely need to locate the m letter, then find the y letter from the bottom down, and find the rest of the sql.

If there is no index, then you may need to look for them one by one. What if I want to find the words that begin with Java? Or what about the words that begin with Oracle?

Do you think this can't be done without an index?

The index can be understood as a sorted fast lookup data structure.

The following figure is an example of a possible indexing method:

If: look for the book No. 4, scan the code and get the corresponding number 91: 34 to the right, 91 to 89 to the right, and then find it (after three comparisons, you can find it, and then retrieve the corresponding physical address)

In order to speed up the search of Col2, you can maintain a binary search tree shown on the right, each node contains an index key value and a pointer to the physical address of the corresponding data record, so that the corresponding data can be obtained within a certain complexity, thus the qualified records can be quickly retrieved.

Conclusion: in addition to the data, the database system also maintains data structures that meet specific search algorithms, which refer to (point to) data in some way, so that advanced search algorithms can be implemented on these data structures. This kind of data structure is the index.

Generally speaking, the index itself is so large that it is impossible to store it all in memory, so the index is often stored on disk in the form of an index file.

What we usually call an index, if not specifically specified, refers to an index organized by a B-tree (multi-search tree, not necessarily binary). Among them, clustered index, secondary index, overlay index, composite index, prefix index and unique index all use B+ tree index by default, collectively referred to as index. Of course, in addition to indexes such as B+ tree, there are also hash indexes (hash index) and so on.

3.2 Index advantages and disadvantages

Advantages:

Similar to the bibliographic index of university library, it can improve the efficiency of data retrieval and reduce the IO cost of database.

The data is sorted by index column, which reduces the cost of data sorting and the consumption of CPU.

Disadvantages:

In fact, an index is also a table, which holds the primary key and index fields and points to the records of the entity table, so the index column also takes up space (space).

Although indexing greatly improves query speed, it also slows down the speed of updating tables, such as INSERT, UPDATE, and DELETE on tables. Because when updating the table, MySQL should not only save the data, but also save the index file every time the fields with index columns are updated, the index information will be adjusted after the key value changes brought about by the update.

Indexing is only one factor in improving efficiency. If your MysQL has tables with a large amount of data, you need to take the time to study how to build the best indexes, or optimize queries.

3.3 Index classification and indexing command statements

Primary key index: the index value must be unique and cannot be NULL

The first kind: CREATE TABLE table_name (id int PRIMARY KEY aoto_increment,name varchar (10))

The second kind: ALTER TABLE table_name ADD PRIMARY KEY (columnName)

Normal index: index values can appear multiple times

The first kind: CREATE INDEX index_name on table_name (columnName)

The second kind: ALTER TABLE table_name ADD INDEX index_name (columnName)

Full-text index: mainly for text retrieval, such as articles, full-text index is only valid for MyISAM engine, and only for English content

Create when creating a table

# create tables CREATE TABLE articles (id INT UNSIGNED ATUO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR, body TEXT, FULLTEXT (title,body)) engine=myisam charset utf8; # specify engine # use select * from articles where match (title,body) against ('English content') # effective only for English content # Note # 1. Fultext index is valid only for myisam in mysql # 2, flltext provided by mysq1 is only valid for English-> sphinx (coreseek) technology processing Chinese clerk # 3, usage is match (field name.) Against ('keywords') # 4. Full-text indexing is called a stop word, because creating an index in a text is an infinite number, so some common words and characters are not created. These words are called stop words.

ALTER TABLE table_name ADD FULLTEXT index_name (columnName)

Unique index: the value of the index column must be unique, but a null value of NULL is allowed, and there can be multiple values.

The first kind: CREATE UNIQUE INDEX index_name ON table_name (columnName)

The second kind: ALTER TABLE table_name ADD UNIQUE INDEX index_name ON (columnName)

Single-valued index: that is, an index contains only a single column, and a table can have multiple single-column indexes.

The first kind: CREATE INDEX index_name ON table_name (columnName)

The second kind: ALTER TABLE table_name ADD INDEX index_name ON (columnName)

Select * from user where name='';// often looks up the name field to index create index idx_user_name on user (name)

Composite index: that is, an index contains multiple columns

The first kind: CREATE INDEX index_name ON table_name (columnName1,columnName2...)

The second kind: ALTER TABLE table_name ADD INDEX index_name ON (columnName1,columnName2...)

Select * from user where name='' and email='';// often looks up the name and email fields to index create index idx_user_name on user (name, email)

Query index

The first kind: SHOW INDEX FROM table_name

The second kind: SHOW KEYS FROM table_name

Delete index

The first kind: DROP INDEX index_name ON table_name

The second kind: ALTER TABLE table_name DROP INDEX index_name

Delete primary key index: ALTER TBALE table_name DROP PRIMARY KEY

3.4 Index structure and retrieval principle

MySQL index structure:

BTree index

Hash index

Full-text full-text index

R-Tree index

Introduction to initialization

A b + tree, a light blue block we call a disk block, you can see that each disk block contains several data items (shown in dark blue) and pointers (shown in yellow), such as disk block 1 contains data items 17 and 35, contains pointers P1, P2, P3

P1 represents blocks less than 17, P2 represents blocks between 17 and 35, and P3 represents blocks greater than 35.

The real data exists in leaf nodes: 3, 5, 9, 10, 13, 15, 28, 29, 36, 60, 75, 79, 90, 99.

Non-leaf nodes only do not store real data, but only store data items that guide the search direction, such as 17 and 35 do not really exist in the data table.

Search process

If you are looking for data item 29, disk block 1 is first loaded from disk into memory, and an IO occurs. Use binary search to determine 29 between 17 and 35 in memory, lock the P2 pointer of disk block 1, memory time is negligible because it is very short (compared to disk IO), load disk block 3 from disk to memory through the disk address of P2 pointer of disk block 1, the second IO,29 occurs between 26 and 30, lock the P2 pointer of disk block 3, load disk block 8 into memory through pointer, and the third IO occurs. At the same time, do a binary search in memory to find 29, end the query, a total of three IO

The real situation is that a three-tier b + tree can represent millions of data. If millions of data lookups require only three IO, the performance improvement will be huge. If there is no index, each data item will have to have one IO, then a total of millions of IO will be required. Obviously, the cost is very high.

3.5 what are the appropriate conditions for indexing

The primary key automatically establishes a unique index

Fields that are frequently used as query criteria should be indexed

The fields associated with other tables in the query are indexed by foreign key relationships.

The selection of single key / combined index, who? (tends to create composite indexes under high concurrency)

The sorted field in the query, if the sorted field is accessed by index, the sorting speed will be greatly improved.

Statistics or grouping fields in a query

3.6 what situations are not suitable for indexing

Fields that are not used in Where conditions are not indexed

Too few table records (more than 300w)

Tables that are often added and deleted (improve the query speed, but slow down the speed of updating the table, such as INSERT, UPDATE, and DELETE the table. Because when updating the table, MySQL not only saves the data, but also saves the index file)

Data is duplicated and evenly distributed in table fields, so only the most frequently queried and sorted data columns should be indexed. Note that if a data column contains a lot of duplicate content, indexing it does not have much practical effect. (for example, nationality, gender)

If a table has 100000 rows of records, there is a field A with only T and F values, and the distribution probability of each value is about 50%, then indexing the table A field will generally not improve the query speed of the database.

The selectivity of the index refers to the ratio of the number of different values in the index column to the number of records in the table. If there are 2000 records in a table and the table index column has 1980 different values, the selectivity of the index is 1980 and 2000. 99. The closer the selectivity of an index is to 1, the more efficient the index will be.

4 performance analysis 4.1 prerequisite knowledge of performance analysis

MySQL Query Optimizer (query optimizer) ["kw" ri] ["pt" ma "z"]

The optimizer module in Mysql is specially responsible for optimizing SELECT statements. Its main function is to provide the Query requested by the client with what he thinks is the best execution plan by calculating and analyzing the statistical information collected in the system. (he thinks the best data retrieval method, but not necessarily DBA thinks it is the best, this part is the most time-consuming)

When the client requests a Query from MySQL, the parser module completes the request classification, distinguishes it from SELECT and forwards it to MySQL Query Optimizer, MySQL Query Optimizer will first optimize the whole Query and convert the budget of some constant expressions into constant values directly. And simplify and transform the query conditions in Query, such as removing some useless or obvious conditions, structural adjustment and so on. Then analyze the Hint information in the Query, if any, to see if displaying the Hint information can fully determine the execution plan of the Query. If no Hint or Hint information is sufficient to fully determine the execution plan, the statistics of the objects involved will be read, the corresponding calculation and analysis will be written according to the Query, and then the final execution plan will be obtained.

Common bottlenecks in MySQL:

When CPU:CPU is saturated, it usually occurs when data is loaded into memory or read from disk.

IO: disk I / O bottleneck occurs when loading data is much larger than memory capacity

Server hardware performance bottlenecks: top,free,iostat and vmstat to view the performance status of the system

4.2 introduction to Explain

Use the EXPLAIN keyword to simulate the optimizer's execution of SQL queries to know how MySQL handles your SQL statements. Analyze the performance bottleneck of your query or table structure

Official website address

The role of Explain:

Reading order of the table

Operation type of data read operation

Which indexes can be used

Which indexes are actually used

References between tables

How many rows per table are queried by the optimizer

Use Explain:

Explain + sql statement

Information contained in the execution plan (key): | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

Mysql > select * from tbl_emp +-+ | id | NAME | deptId | 1 | Z3 | 1 | 2 | Z4 | 1 | 3 | Z5 | 1 | 4 | w5 | 2 | 5 | w6 | 2 | 6 | S7 | 3 | 7 | S8 | 4 | 8 | S9 | 51 | | +-+ 8 rows in set (0.00 sec) mysql > explain select * from tbl_emp | +-+ | id | select_ Type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -- + | 1 | SIMPLE | tbl_emp | NULL | ALL | NULL | 8 | 100.00 | NULL | + -+ 1 row in set 1 warning (0.00 sec) 4.3 explanation of information fields contained in the execution plan (top priority)

Information contained in the execution plan (key): | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

Interview focus: id, type, key, rows, Extra

Id (order in which tables are read)

The sequence number of a select query that contains a set of numbers indicating the order in which the select clause or action table is executed in the query

There are three situations:

1. Id is the same, and the execution order is from top to bottom (T1, T3, T2)

2. Id is different. If it is a subquery, the sequence number of id will be incremented. The higher the id value, the higher the priority, the first to be executed (T3, T1, T2).

3. Id is the same and different, but exists at the same time. Go first with large numbers and the same numbers from top to bottom (T3, S1, T2)

Select_type (operation type of data read operation)

The type of query is mainly used to distinguish between ordinary query, federated query, subquery and so on.

SIMPLE ['s' npl]: a simple select query that does not include subqueries or UNION

PRIMARY: if the query contains any complex subsections, the outermost query is marked (the last loaded one)

SUBQUERY ["kw" ri]: contains a subquery in the SELECT or WHERE list

DERIVED [d "ra" vd]: subqueries included in the FROM list are marked as DERIVED (derivative) MySQL will recursively execute these subqueries, putting the results in a temporary table

UNION ["ju" ni "n]: if the second SELECT appears after UNION, it will be marked as UNION;. If UNION is included in the subquery of the FROM clause, the outer SELECT will be marked as: DERIVED.

UNION RESULT [r expressions z "lt]: the SELECT that gets the result from the UNION table (two select statements are merged with UNION)

Table (displays the name of the table executed)

Show which table the data in this row is about

Type (access type arrangement)

Displays what type of query is used

Access type order: system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

There are eight common types of type:

The result values from best to worst are (key): system > const > eq_ref > ref > range > index > ALL

Generally speaking, you have to ensure that the query reaches at least range level, preferably ref

detailed description

System: the table has only one row of records (equal to the system table). This is a special column of const type, which usually does not appear, and this can be ignored.

Const: indicates that it is found once through the index, and const is used to compare primary key or unique indexes. Because only one row of data is matched, MySQL can quickly convert the query to a constant if the primary key is placed in the where list.

Eq_ref: unique index scan, with only one record in the table matching for each index key. Common in primary key or unique index scans.

Ref: a non-unique index scan that returns all rows that match a single value, which is essentially an index access that returns all rows that match a single value. However, it may find multiple rows that meet the criteria, so it should be a mixture of lookups and scans.

Range: only retrieve a given range of rows, using an index to select rows. The key column shows which index is used, which is usually the query of between, in, etc., in your where statement. This range scan index scan is better than a full table scan, because it only needs to start at a certain point in the index, while the conclusion is that you don't have to scan all the indexes.

Index:Full Index Scan,index differs from ALL in that the index type only traverses the index column. This is usually faster than ALL, because index files are usually smaller than data files (that is, although both all and Index read the full table, index reads from the index, while all reads from the hard disk)

All:Full Table Scan, which will traverse the entire table to find matching rows

Work case: manager this SQL I ran Explain analysis, there may be ALL full table scan on the system, it is recommended to try to optimize. I changed this SQL. I wrote it this way after I optimized it. The effect has changed from ALL to …

Possible_keys (which indexes can be used)

Displays one or more indexes that may be applied to this table. If there is an index for the fields involved in the query, the index will be listed, but not necessarily used by the query (the system believes that some indexes will be used in theory).

Key (which indexes are actually used)

The index actually used. If NULL, the index is not used (either not built or invalidated)

If an override index is used in a query, it appears only in the key list

Override index: the index field created is the same as that of the query, as shown in the following figure

Key_len (number of bytes consumed)

Represents the number of bytes used in the index, which can be used to calculate the length of the index used in the query. The shorter the length, the better without losing accuracy.

The value displayed by key_len is the maximum possible length of the index field, not the actual length used, that is, key_len is calculated according to the table definition, not retrieved from within the table.

Ref (references between tables)

Shows which column of the index is used and, if possible, a constant. Which columns or constants are used to find values on indexed columns.

Rows (how many rows per table are queried by the optimizer)

Based on the table statistics and index selection, roughly estimate the number of rows to be read to find the required records (the smaller the better)

When not indexed:

After indexing: the number of rows scanned is reduced

Extra ["ekstr"]

Contains additional information that is not suitable for display in other columns but is very important

Information types: Using filesort, Using temporary, Using index, Using where, Using join buffer, impossible where, select tables optimized away, distinct

Using filesort (needs optimization)

Indicates that mysql sorts the data using an external index instead of reading it in the order in which it is indexed within the table. The sort operation that cannot be done with indexes in MySQL is called "file sorting".

Using temporary (needs optimization)

Enables intermediate results to be saved with temporary tables, and MysQL uses temporary tables when sorting query results. Common in sorting order by and grouping query group by

Using index (good)

Indicates that the overlay index (Covering Index) is used in the corresponding select operation to avoid accessing the data rows of the table, which is very efficient!

Situation 1:

Case 2:

Override index / index override (Covering Index).

Understanding way 1: select data columns only need to be obtained from the index, do not have to read the data rows, MySQL can use the index to return the fields in the select list, without having to read the data file again according to the index, in other words, the query column will be overwritten by the index.

Way 2: an index is an efficient way to find a row, but a general database can also use an index to find the data of a column, so it doesn't have to read the entire row. After all, index leaf nodes store the data they index; when you can get the data you want by reading the index, you don't need to read rows. An index that contains (or overwrites) data that satisfies the query results is called an overlay index.

Note:

If you want to use an override index, be sure to note that only the columns you need are extracted from the select list, not select*

Because if you index all the fields together, the index file will be too large and the query performance will be degraded.

Using where: indicates that where filtering is used.

Using join buffer: connection cache used

The value of the impossible where:where clause is always false and cannot be used to get any tuples

Select tables optimized away

In the case of no GROUPBY clause, optimizing the MIN/MAX operation based on the index, or optimizing the COUNT (*) operation for the MyISAM storage engine, you do not have to wait until the execution phase to calculate, and the optimization is completed at the stage of query execution plan generation.

Distinct

Optimize the distinct operation and stop looking for the same value after finding the first matching tuple.

Practice

Write out the execution order of the table in the following figure

The first row (execution order 4): id column 1, primary that is the first select,select_type column in union indicates that the query is an outer query, and the table column is marked to indicate that the query result comes from a derived table, where 3 in derived3 represents that the query is derived from the third select query, that is, select with id 3. [select d1.name... ]

The second line (execution order 2): id is 3, which is part of the third select in the entire query. Because the query is contained in from, it is derived. [select id,namefrom t1 where other_column='']

The third line (execution order 3): the subquery select_type in the select list is subquery, which is the second select in the entire query. [select id from t3]

The fourth line (execution order 1): select_type is union, indicating that the fourth select is the second select in union, and execute [select name,id from T2] first.

The fifth row (execution order 5): represents the stage of reading rows from the temporary table of union, and the representation of the table column performs the union operation with the results of the first and fourth select. [two result union operations]

5 Index optimization 5.1 case of index single table optimization

Build a table:

CREATE TABLE IF NOT EXISTS article (id INT (10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, author_id INT (10) UNSIGNED NOT NULL, category_id INT (10) UNSIGNED NOT NULL, views INT (10) UNSIGNED NOT NULL, comments INT (10) UNSIGNED NOT NULL, title VARCHAR (255) NOT NULL, content TEXT NOT NULL) INSERT INTO article (author_id,category_id,views,comments,title,content) VALUES (1) mysql > select * from article), (2) (2), (2), (2), (2), (2), (2), (2), (2), (2), (1) (1), (1) (1) (1) (1) (1) (1) (1) (1) (1) (1) (1) (1) (1) (1) (1) (1) (1) (1) (1) (1) (1) (1) (2) (2) (2) (2) (2) (2) (2) (2) (2) (2) (2) (2) (2) (2) (2) (2) (2) (2) (2) (2) (2) (2) (2) (2) ( +-+ | id | author_id | category_id | views | comments | title | content | + -+ | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 3 | 1 | 1 | 3 | 3 | 3 | 3 | +-+ 3 rows in set (0.00 sec)

Case

Requirement: query the article_id with the largest number of views when category_id is 1 and comments is greater than 1

/ / function implementation mysql > SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1 +-+-+ | id | author_id | +-+-+ | 3 | 1 | +-+-+ 1 row in set (0.00 sec) / / explain analysis mysql > explain SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1 +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+-- + | 1 | SIMPLE | article | NULL | ALL | NULL | 3 | 33.33 | Using where Using filesort | +-+- -+ 1 row in set 1 warning (0.00 sec)

Conclusion: it is clear that type is the ALL, the worst-case scenario. There is also Using filesort in Extra, which is also the worst-case scenario. Optimization is necessary.

Start optimization

Create a new index (add an index to the field used after the WHERE statement)

Creation method:

Create index idx_article_ccv on article (category_id,comments,views)

ALTER TABLE 'article' ADD INDEX idx_article_ccv (' category_id, 'comments',' views')

Index is not very useful, delete: DROP INDEX idx_article_ccv ON article

Conclusion:

Type becomes range, which is bearable. But it is still unacceptable to use Using filesort in extra.

But we have built an index, so why doesn't it work?

This is because according to how BTree indexes work, sort category_id first, then comments if you encounter the same category_id, and then sort views if you encounter the same comments.

When the comments field is in the middle position in the federated index, because the comments > 1 condition is a range value (the so-called range), MySQL cannot use the index to retrieve the following views part, that is, the index after the range type query field is invalid.

Improve

Compared to the last time the index was created, no index was created for the comments field this time

Conclusion: type becomes ref,ref and Using filesort in const,Extra also disappears, and the result is very ideal.

5.2 case of optimization of two tables in index

Build a table:

CREATE TABLE IF NOT EXISTS class (id INT (10) UNSIGNED NOT NULL AUTO_INCREMENT, card INT (10) UNSIGNED NOT NULL, PRIMARY KEY (id)); CREATE TABLE IF NOT EXISTS book (bookid INT (10) UNSIGNED NOT NULL AUTO_INCREMENT, card INT (10) UNSIGNED NOT NULL, PRIMARY KEY (bookid)); INSERT INTO class (card) VALUES (FLOOR (1 + (RAND () * 20) INSERT INTO class (card) VALUES (FLOOR (1 + (RAND () * 20)); INSERT INTO class (card) VALUES (FLOOR (1 + (RAND () * 20); INSERT INTO class (card) VALUES (FLOOR (1 + (RAND () * 20); INSERT INTO class (card) VALUES (FLOOR (1 + (RAND () * 20); INSERT INTO class (card) VALUES (FLOOR (1 + (RAND () * 20); INSERT INTO class (card) VALUES (FLOOR (1 + (RAND () * 20) INSERT INTO class (card) VALUES (FLOOR (1 + (RAND () * 20)); INSERT INTO class (card) VALUES (FLOOR (1 + (RAND () * 20); INSERT INTO class (card) VALUES (FLOOR (1 + (RAND () * 20); INSERT INTO class (card) VALUES (FLOOR (1 + (RAND () * 20); INSERT INTO class (card) VALUES (FLOOR (1 + (RAND () * 20); INSERT INTO class (card) VALUES (FLOOR (1 + (RAND () * 20) INSERT INTO class (card) VALUES (FLOOR (1 + (RAND () * 20)); INSERT INTO class (card) VALUES (FLOOR (1 + (RAND () * 20); INSERT INTO class (card) VALUES (FLOOR (1 + (RAND () * 20); INSERT INTO class (card) VALUES (FLOOR (1 + (RAND () * 20); INSERT INTO class (card) VALUES (FLOOR (1 + (RAND () * 20); INSERT INTO class (card) VALUES (FLOOR (1 + (RAND () * 20) INSERT INTO class (card) VALUES (FLOOR (1 + (RAND () * 20)); INSERT INTO class (card) VALUES (FLOOR (1 + (RAND () * 20); INSERT INTO book (card) VALUES (FLOOR (1 + (RAND () * 20); INSERT INTO book (card) VALUES (FLOOR (1 + (RAND () * 20); INSERT INTO book (card) VALUES (FLOOR (1 + (RAND () * 20); INSERT INTO book (card) VALUES (FLOOR (1 + (RAND () * 20) INSERT INTO book (card) VALUES (FLOOR (1 + (RAND () * 20)); INSERT INTO book (card) VALUES (FLOOR (1 + (RAND () * 20); INSERT INTO book (card) VALUES (FLOOR (1 + (RAND () * 20); INSERT INTO book (card) VALUES (FLOOR (1 + (RAND () * 20); INSERT INTO book (card) VALUES (FLOOR (1 + (RAND () * 20); INSERT INTO book (card) VALUES (FLOOR (1 + (RAND () * 20) INSERT INTO book (card) VALUES (FLOOR (1 + (RAND () * 20)); INSERT INTO book (card) VALUES (FLOOR (1 + (RAND () * 20); INSERT INTO book (card) VALUES (FLOOR (1 + (RAND () * 20); INSERT INTO book (card) VALUES (FLOOR (1 + (RAND () * 20); INSERT INTO book (card) VALUES (FLOOR (1 + (RAND () * 20); INSERT INTO book (card) VALUES (FLOOR (1 + (RAND () * 20) INSERT INTO book (card) VALUES (FLOOR (1 + (RAND () * 20)); INSERT INTO book (card) VALUES (FLOOR (1 + (RAND () * 20); INSERT INTO book (card) VALUES (FLOOR (1 + (RAND () * 20)); INSERT INTO book (card) VALUES (FLOOR (1 + (RAND () * 20)); / / query mysql > select * from class +-+ | id | card | +-- +-+ | 1 | 17 | 2 | 2 | 3 | 18 | 4 | 4 | 4 | 4 | 4 | 4 | 6 | 8 | 9 | 8 | 1 | 9 | 18 | 10 | 6 | 11 | 15 | 15 | 13 | 12 | 14 | 15 | 15 | 18 | 16 | 17 | 18 | 18 | 5 | 19 | | | 7 | | 20 | 1 | | 21 | 2 | +-+-+ 21 rows in set (0.00 sec) mysql > select * from book | +-+-+ | bookid | card | +-+-+ | 1 | 8 | 2 | 14 | 3 | 3 | 4 | 16 | 5 | 8 | 6 | 12 | 17 | 8 | 8 | 9 | 10 | 10 | 3 | 11 | 4 | 12 | 12 | | | 13 | 9 | 14 | 7 | 15 | 6 | 16 | 8 | 17 | 3 | 18 | 11 | 19 | 5 | | 20 | 11 | +-+-+ 20 rows in set (0.00 sec) |

Start Explain analysis: type are all all and need to be optimized (there is always a table to add index drivers)

The left join indexes the left table.

Delete index: drop index y on class

The left join adds an index to the right table

Delete index: drop index Y on book

Case study: if someone else's index is in the wrong position, you only need to adjust the order of the left and right tables when you query.

Conclusion:

The type in the second line becomes ref,rows and less, and the optimization is more obvious. This is determined by the left join feature. The LEFT JOIN condition is used to determine how to search for rows from the right table, which must be on the left, so the right is our key point, and we must index the right table (small tables drive large tables).

Join left, add index to right table

Similarly: join on the right and index on the left

5.3 case of optimization of three tables of index

Build a table:

CREATE TABLE IF NOT EXISTS phone (phoneid INT (10) UNSIGNED NOT NULL AUTO_INCREMENT, card INT (10) UNSIGNED NOT NULL, PRIMARY KEY (phoneid)) ENGINE=INNODB;INSERT INTO phone (card) VALUES (FLOOR (1 + (RAND () * 20); INSERT INTO phone (card) VALUES (FLOOR (1 + (RAND () * 20); INSERT INTO phone (card) VALUES (FLOOR (1 + (RAND () * 20)); INSERT INTO phone (card) VALUES (FLOOR (1 + (RAND () * 20) INSERT INTO phone (card) VALUES (FLOOR (1 + (RAND () * 20)); INSERT INTO phone (card) VALUES (FLOOR (1 + (RAND () * 20); INSERT INTO phone (card) VALUES (FLOOR (1 + (RAND () * 20); INSERT INTO phone (card) VALUES (FLOOR (1 + (RAND () * 20); INSERT INTO phone (card) VALUES (FLOOR (1 + (RAND () * 20); INSERT INTO phone (card) VALUES (FLOOR (1 + (RAND () * 20) INSERT INTO phone (card) VALUES (FLOOR (1 + (RAND () * 20)); INSERT INTO phone (card) VALUES (FLOOR (1 + (RAND () * 20); INSERT INTO phone (card) VALUES (FLOOR (1 + (RAND () * 20); INSERT INTO phone (card) VALUES (FLOOR (1 + (RAND () * 20); INSERT INTO phone (card) VALUES (FLOOR (1 + (RAND () * 20); INSERT INTO phone (card) VALUES (FLOOR (1 + (RAND () * 20) INSERT INTO phone (card) VALUES (FLOOR (1 + (RAND () * 20)); INSERT INTO phone (card) VALUES (FLOOR (1 + (RAND () * 20); INSERT INTO phone (card) VALUES (FLOOR (1 + (RAND () * 20)); INSERT INTO phone (card) VALUES (FLOOR (1 + (RAND () * 20)); / / query mysql > select * from phone +-+-+ | phoneid | card | +-+-+ | 1 | 10 | 2 | 13 | 3 | 17 | 4 | 5 | 5 | 12 | 6 | 7 | 15 | 8 | 17 | 9 | 17 | 10 | 14 | | 11 | 19 | 12 | 13 | 13 | 5 | 14 | 8 | 15 | 2 | 16 | 8 | 17 | 11 | 18 | 14 | 19 | 13 | 20 | 5 | +-+-+ 20 rows in set (0.00 sec)

Delete their indexes using the two tables in the previous section:

The query statement of the three tables should be: SELECT * FROM class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card

Create an index:

The right table book of the first LFET JOIN should be indexed

Alter table `book`add index Y (`card`)

The right table phone of the second LFET JOIN should be indexed

Alter table `phone` add index z (`card`)

Explain Analysis:

The last two lines of type are all ref and the total rows is optimized very well, and the effect is good. Therefore, the index is best set in the fields that need to be queried frequently.

Conclusion:

Optimization of Join statement

Minimize the total number of NestedLoop loops in Join statements: "always use small result sets to drive large result sets (for example, the book's type table drives the book's name table)."

Optimize the inner loop of NestedLoop first to ensure that the Join conditional field on the driven table in the Join statement has been indexed.

When it is impossible to guarantee that the Join condition field of the driven table is indexed and the memory resources are sufficient, do not be too stingy with the setting of JoinBuffer.

5.4 Index invalidation

Build a table:

CREATE TABLE staffs (id INT PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR (24) NOT NULL DEFAULT'' COMMENT' name', `age`INT NOT NULL DEFAULT 0 COMMENT' age', `pos` VARCHAR (20) NOT NULL DEFAULT'' COMMENT' position', `add_ time`TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT' entry time') CHARSET utf8 COMMENT' employee record Table' INSERT INTO staffs (`name`, `age`, `pos`, `add_ time`) VALUES ('z3paramel22); INSERT INTO staffs (`name`, `age`, `pos`, `add_ time`) VALUES (' July',23,'dev',NOW ()); INSERT INTO staffs (`name`, `age`, `pos`, `add_ time`) VALUES ('2000); ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos (`name`, `age`, `pos`)

Index failure cases:

1. Full value matching is my favorite.

2. The best left prefix rule (important! ): if you index multiple columns, follow the leftmost prefix rule. This means that the query starts at the leftmost front column of the index and does not skip the middle column of the composite index.

The middle column cannot be broken:

3. Not doing anything on the index column (calculation, function, (automatic or manual) type conversion) will cause the index to fail and turn to full table scan.

4. The storage engine cannot use the column to the right of the scope condition in the index (the range becomes invalid after the range, and the range column is not a query but a sort).

5. Try to use overlay indexes (queries that only access the index (index columns are consistent with query columns) to reduce select *.

6. The inability of mysql to use an index when it is not equal to (! = or) will cause a full table scan.

7. Is null, is not null also cannot use indexes.

8. Like begins with a wildcard ('% abc …' Mysql index invalidation becomes a full table scan operation (% written on the rightmost index will not fail, or overwrite the index).

Question: how to solve the problem that the index is not used when like'% string%'? Use the method of overwriting index!

Build a table:

CREATE TABLE `tbl_ user` (`id` INT (11) NOT NULL AUTO_INCREMENT, `name` VARCHAR (20) DEFAULT NULL, `age`int (11) DEFAULT NULL, `email` VARCHAR (20) DEFAULT NULL, PRIMARY KEY (`id`) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;INSERT INTO tbl_user (`name`, `age`, `email`) VALUES INSERT INTO tbl_user (`name`, `age`, `email`) VALUES ('3cc3mpl 24pr select 163.com'); INSERT INTO tbl_user (`name`, `age`, `email`) VALUES (' 4dd4pr 26recorder dan163.com'); / / query mysql > select * comm +-+ | id | name | age | email | +-- + | 1 | 1aa1 | 21 | a@163.com | | 2 | 2bb2 | 23 | b@163.com | | 3 | 3cc3 | 24 | c@163.com | 4 | 4dd4 | 26 | d @ 163.com | +-+ 4 rows in set (0.00 sec)

Create an index:

CREATE INDEX idx_user_nameAge ON tbl_user (NAME,age)

The index was used successfully:

Index invalidation:

Summary:% is written on the far right, and if it has to be written on the far left, use an overlay index

9. The index of a string without single quotation marks is invalid.

Explain Analysis:

10. Use less or, because the index will fail when you use it to connect.

5.5 Analysis of Index face Test questions

Build a table:

Create table test03 (id int primary key not null auto_increment, C1 char (10), c2 char (10), c3 char (10), c4 char (10), c5 char (10)); insert into test03 (C1 char (10), c4 char (10), c5 char (10) Insert into test03 (c1meme, c2meme, c4meme, c5) values; insert into test03 (c1meme, c2meme, c4lemagerie, c5'); values (d1pimi, c2pas, c4pas, c5s); insert into test03 (c1meme, c2mings, c3mings, c4mings, c5'); insert into test03 (c1mins, c2mins, c3mins, c4pics, c5) values (e1memes, e2mings, 3pics, e4cycles, and e5'); / / View the table structure mysql > select * from test03 +-- +-+ | id | C1 | c2 | c3 | c4 | c5 | +-+-+ | 1 | A1 | a2 | a3 | A4 | a5 | 2 | b1 | B2 | b3 | b4 | b5 | 3 | C1 | c2 | c3 | c4 | c5 | 4 | D1 | d2 | d3 | d4 | d5 | E1 | e2 | E3 | E4 | e5 | +-+ 5 rows in set (0.00 sec)

Index:

Create index idx_test03_c1234 on test03 (C1 and c2); / / check the index mysql > show index from test03 + -+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +- -+- -+ | test03 | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | test03 | 1 | idx_test03_c1234 | 1 | C1 | A | 5 | NULL | NULL | YES | BTREE | | test03 | 1 | idx_test03_c1234 | 2 | c2 | A | 5 | NULL | NULL | YES | BTREE | | test03 | 1 | idx_test03_c1234 | 3 | c3 | A | | 5 | NULL | NULL | YES | BTREE | | test03 | 1 | idx_test03_c1234 | 4 | c4 | A | 5 | NULL | NULL | YES | BTREE | +-- | -+- -+-+ 5 rows in set (0.00 sec)

1) add columns one by one

2) the order of exchange conditions does not affect the index, but it is best to write SQL according to the order of indexing.

3) limit the scope

4) order by

5) group by

Fixed value, range or sort. Generally speaking, order by gives a range.

Basically, group by needs to be sorted, and temporary tables will be generated.

Recommendations:

For single-valued indexes, try to choose an index that is more filtered for the current query.

When selecting a combined index, the most filtered field in the current Query is in the order of the index fields, and the more left the better.

When choosing a combined index, try to select an index that can contain more fields in the where clause in the current query.

As far as possible by analyzing the statistical information and adjusting the writing of query to achieve the purpose of selecting the appropriate index.

5.6 Summary

Optimize and sum up the formula

Full value matching is my favorite, the leftmost prefix should be followed.

The leading brother can't die, and the middle brother can't break.

There is less computation on the index column, and all invalidation after the range.

LIKE percentage write is the most right, overwrite index does not write *

Unequal null values and OR, we should pay attention to the impact of the index.

VAR quotes should not be lost, and there is a knack for SQL optimization.

6 query interception analysis 6.1 small tables drive large tables

EXISTS syntax: SELECT... FROM table WHERE EXISTS (subquery)

The syntax can be understood as: put the data of the main query into the subquery for conditional verification, and determine whether the data results of the main query can be retained according to the verification results (TRUE or FALSE).

Tip:

EXSTS (subquey) only returns TRUE or FALSE, so the SELECT * in a subquery can also be SELECT 1 or select'Xquery. Officially, the SELECT list is ignored during actual execution, so there is no difference.

The actual execution process of the exists subquery may have been optimized rather than compared one by one as we understand it. If you are worried about the efficiency, you can conduct a practical test to determine whether there is an efficiency problem.

Exist subqueries can also be replaced by conditional expressions, other subqueries or JOIN. Which is the best one that requires specific analysis of specific problems?

In and exists usage:

6.2 Optimization of Order by keyword sorting

1. In the clause after ORDER BY, try to use Index to sort, but avoid using FileSort to sort.

Build a table:

Create table tblA (# id int primary key not null auto_increment, age int, birth timestamp not null); insert into tblA (age, birth) values (22, now ()); insert into tblA (age, birth) values (23, now ()); insert into tblA (age, birth) values (24, now ()); create index idx_A_ageBirth on tblA (age, birth); / / query mysql > select * from tblA +-+-+ | age | birth | +-+-+ | 22 | 2021-04-04 19:31:45 | | 23 | 2021-04-04 19:31:45 | | 24 | 2021-04-04 19:31:45 | +- -+ 3 rows in set (0.00 sec) mysql > show index from tblA +- +-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +- -+- -+ | tbla | 1 | idx_A_ageBirth | 1 | age | A | 3 | NULL | NULL | YES | BTREE | | tbla | 1 | idx_A_ageBirth | 2 | birth | A | 3 | NULL | NULL | | BTREE | | +-+ | -+ 2 rows in set (0.00 sec)

Concern: will Using filesort be generated after order by

MySQL supports two ways of sorting, FileSort and lIndex,Index are efficient, which means that MySQL scans the index itself to complete the sorting. The efficiency of FileSort is low.

If ORDER BY meets two conditions, it will be sorted using Index:

The ORDER BY statement uses the leftmost front column of the index.

The combination of the where clause and the Order BY clause conditional column satisfies the leftmost front column of the index.

2. Complete the sorting operation on the index as much as possible, following the best left prefix for indexing

3. If it is not on the index column, mysql's filesort has two algorithms (auto-start)

Two-way sorting

MySQL4.1 used to use two-way sorting, which literally means to scan the disk twice and finally get the data, read the row pointer and OrderBy column, sort them, and then scan the sorted list and re-read the corresponding data output from the list according to the values in the list.

Take the sort field from the disk, sort it in buffer, and then get the other fields from the disk.

Take a batch of data and scan the disk twice. As we all know, I\ O is very time-consuming, so after mysql4.1, there is a second improved algorithm, that is, one-way sorting.

One-way sorting

Read all the columns needed by the query from disk, sort them according to the order by column in buffer, and then scan the sorted list for output, which is faster and avoids the second reading of the data. And turn random IO into sequential IO, but it uses more space because it keeps each line in memory

Conclusions and problems arising from them

Since one-way is late, it is generally better than two-way.

But there is a problem with using a single channel. In sort_buffer, method B takes up a lot more space than method A, because method B takes out all the fields, so it is possible that the total size of the data taken out exceeds the capacity of sort_buffer, resulting in only taking the data of sort_buffer capacity size for sorting (creating tmp files, multiplexing), and then fetching it.

Sort_buffer capacity size, rearrange. As a result, multiple times of Icano.

Originally, I wanted to save one IWeiO operation, but it led to a large number of Icano operations, but the loss outweighed the gain.

4. Optimization strategy

Increase the setting of sort_buffer_size parameters

Increase the setting of max_length_for_sort_data parameters

Why?

5. Small summary:

6.3 Group by optimization

The essence of group by is to sort before grouping, following the best left prefix built by the index.

When the index column cannot be used, increase the setting of the max_length_for_sort_data parameter + increase the setting of the sort_buffer_size parameter.

Where is higher than having, so don't go to having if you can write the conditions that are limited by where.

6.4 slow query log (key)

Introduction:

The slow query log of MySQL is a kind of log record provided by MySQL, which is used to record statements whose response time exceeds the threshold in MySQL. Specifically, SQL whose running time exceeds the long_query_ time value will be recorded in the slow query log.

Specifically, SQL whose running time exceeds the long_query_ time value will be recorded in the slow query log. The default value for long_query_time is 10, which means to run a statement for more than 10 seconds.

It is up to him to check which SQL exceeds our maximum endurance time. For example, if a sql is executed for more than 5 seconds, even if the SQL is slow, we hope to collect more than 5 seconds of sql and conduct a comprehensive analysis combined with the previous explain.

Operation instructions:

By default, the MySQL database does not enable slow query daily speed, so we need to set this parameter manually.

Of course, it is generally not recommended to enable this parameter if it is not needed for tuning, because turning on the slow query log will have a more or less performance impact. The slow log supports writing log records to a file.

Check whether and how to turn it on:

Default: SHOW VARIABLES LIKE'% slow_query_log%'; ["ve" ri "bls]

Enabled: set global slow_query_log=1;, is only valid for the current database. If MySQL is restarted, it will become invalid.

If you want to take effect permanently, you must modify the configuration file my.cnf (as do other system variables)

Modify the my.cnf file, add or modify the parameters slow_query_log and slow_query_log_file under [mysqld], and then restart the MySQL server. The following two lines will also be configured into the my.cnf file

Slow_query_log = 1slow_query_log_file=/var/lib/mysqatguigu-slow.log

With regard to the slow query parameter slow_query_log_file, it specifies the storage path of the slow query log file, and the system defaults to a default file host_name-slow.log (if the parameter slow_query_log_file is not specified)

After enabling the slow log, what kind of SQL will be recorded in the slow log?

This is controlled by the parameter long_query_time. By default, the value of long_query_time is 10 seconds. Command: SHOW VARIABLES LIKE 'long_query_time%'

You can use the command to modify it, or you can modify it in the my.cnf parameter.

If the elapsed time is exactly equal to long_query_time, it will not be recorded. In other words, in the mysql source code, it is judged to be greater than long_query_time, not greater than or equal to.

Slow SQL threshold time for naming modification: set global long_query_time=3; [naming l modification bl]

If you don't see any changes, reopen the connection, or change the sentence: show global variables like 'long_query_time'

Record slow SQL and follow-up analysis:

Suppose we successfully set the slow SQL threshold time to 3 seconds (set global long_query_time=3;).

Simulated timeout SQL:select sleep (4)

Query how many slow query records there are in the current system: show global status like'% Slow_queries%'; ["ste records"]

Set the slow SQL threshold time in the configuration file (permanent):

# [mysqld] configuration: slow_query_log=1;slow_query_log_file=/var/lib/mysql/atguigu-slow.loglong_query_time=3;log_output=FILE

Log analysis tool mysqldumpslow

In a production environment, if you want to manually analyze logs, find and analyze SQL, it is obviously a manual task. MySQL provides a log analysis tool, mysqldumpslow.

Check out mysqldumpslow's help information, mysqldumpslow-- help.

Common mysqldumpslow help information:

S: indicates how it is sorted

C: number of visits

L: lock time

R: returns the record

T: query time

Al: average lock time

Ar: the average number of records returned

At: average query time

T: that is, how many pieces of data are returned.

G: followed by a regular matching pattern, case-insensitive

Common reference for work:

Get the 10 SQL:mysqldumpslow-s r-t 10 / var/lib/mysql/atguigu-slow.log with the most returned recordsets

Get the 10 most visited SQL:mysqldumpslow-s c-t 10 / var/lib/mysql/atguigu-slow.log

Get the first 10 query statements sorted by time with a left link: mysqldumpslow-s t-t 10-g "left join" / var/lib/mysql/atguigu-slow.log

It is also recommended to use these commands in combination with │ and more, otherwise the screen burst may occur: `mysqldumpslow-s rmurt 10 / ar/lib/mysql/atguigu-slow.log | more

6.5 bulk insert data script

1. Create a table:

Create database bigData;use bigData;// Department Table CREATE TABLE dept (id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, dname VARCHAR (20) NOT NULL DEFAULT "", loc VARCHAR (13) NOT NULL DEFAULT "") ENGINE=INNODB DEFAULT CHARSET=utf8 / / employee form CREATE TABLE emp (id int unsigned primary key auto_increment, empno mediumint unsigned not null default 0, / / No. Ename varchar (20) not null default ", / / name job varchar (9) not null default", / / Job mgr mediumint unsigned not null default 0, / / Superior number hiredate date not null, / / entry time sal decimal (7) not null, / / salary comm decimal (7) not null / / bonus deptno mediumint unsigned not null default 0 / / Department No.) ENGINE=INNODB DEFAULT CHARSET=utf8

2. Set the parameter log_bin_trust_function_creators

Create a function, if an error is reported: This function has none of DETERMINISTIC.

Because it is too slow to query logs, and because we have enabled bin-log, we have to specify a parameter for our function

Show variables like 'log_bin_trust_function_creators';set global log_bin_trust_function_creators=1

After the parameters are added, the above parameters will disappear again if mysqld is restarted. Permanent method:

Under windows: my.ini [mysqld] plus log_bin_trust_function_creators=1

Under linux: / etc/my.cnf, my.cnf [mysqld] and log_bin_trust_function_creators=1

3. Create functions to ensure that each piece of data is different.

Randomly generate a string

Delimiter $$# modify the command Terminator in order for the stored procedure to work properly. Two $$denote the end of create function rand_string (n int) returns varchar (255) begin declare chars_str varchar (100) default 'abcdefghijklmnopqrstuvwxyz'; declare return_str varchar (255) default''; declare i int default 0; while I

< n do set return_str = concat(return_str,substring(chars_str,floor(1+rand()*52),1)); set i=i+1; end while; return return_str;end $$ 随机产生部门编号 delimiter $$create function rand_num() returns int(5)begin declare i int default 0; set i=floor(100+rand()*10); return i;end $$ 4、创建存储过程 创建往emp表中插入数据的存储过程 delimiter $$create procedure insert_emp(in start int(10),in max_num int(10)) #max_num：表示插入多少条数据begin declare i int default 0; set autocommit = 0; #关闭自动提交，避免写一个insert提交一次，50w条一次性提交 repeat set i = i+1; insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values((start+i),rand_string(6),'salesman',0001,curdate(),2000,400,rand_num()); until i=max_num end repeat; commit;end $$ 创建往dept表中插入数据的存储过程 delimiter $$create procedure insert_dept(in start int(10),in max_num int(10))begin declare i int default 0; set autocommit = 0; repeat set i = i+1; insert into dept(deptno,dname,loc) values((start+i),rand_string(10),rand_string(8)); until i=max_num end repeat; commit;end $$ 5、调用存储过程 往dept表中插入数据 mysql>

DELIMITER; # modifies the default ending symbol to (;), previously changed to # # mysql > CALL insert_dept (100,10); Query OK, 0 rows affected (0.01sec)

Insert 500000 data into the emp table

Mysql > DELIMITER; mysql > CALL insert_emp (100001, 500000); Query OK, 0 rows affected (27.00 sec)

View the running results

Mysql > select * from dept +-+ | id | deptno | dname | loc | +-- + | 1 | 101 | mqgfy | ck | | 2 | 102 | wgighsr | kbq | | 3 | 103 | gjgdyj | brb | 4 | 104 | gzfug | p | | 5 | keitu | cib | 6 | nndvuv | csue | | 7 | 107 | cdudl | tw | 8 | 108 | aafyea | aqq | 9 | 109 | zuqezjx | dpqoyo | | 10 | pam | cses | +-- + 10 rows in set (0.00 sec) mysql > select * from emp limit 10 | # View the first 10 pieces of data (50W is too much) +-+ | id | empno | ename | job | mgr | hiredate | sal | comm | deptno | + -+ | 1 | 100002 | xmbva | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 400.00 | 2 | 100003 | aeq | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 3 | 100004 | cnjfz | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 4 | 100005 | wwhd | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 5 | 100006 | e | salesman | 1 | 2000.00 | 400.00 | 400.00 | 6 | 100007 | yjfr | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 100008 | xlp | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 8 | 100009 | mp | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 9 | 100010 | tcdl | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 10 | 100011 | akw | salesman | 1 | 2021-04-05 | 2000.00 | 2000.00 | .00 | 106 | +-+ 10 rows in set (0.00 sec) 6.6 Show Profile for sql analysis (top priority)

Show Profile is the resource consumption provided by mysql that can be used to analyze statement execution in the current session. Can be used to measure the tuning of SQL

Official website document

By default, the parameter is turned off and the results of the last 15 runs are saved

Analysis steps:

1. Check whether the current version of mysql supports: show variables like 'profiling'.

It is off by default and needs to be turned on before use.

2. Enable function. It is disabled by default. It needs to be enabled before use: set profiling=on.

3. Run SQL (run casually for testing)

Mysql > select * from emp group by id limit 1500X MySQL > select * from emp group by id%20 order by 5

4. View the result: show profiles

Mysql > show profiles +-+ | Query_ID | Duration | Query | +-+- -+-- + | 1 | 0.00204000 | show variables like 'profiling' | | 2 | 0.55134250 | select * from emp group by id limit 150000 | | 3 | 0.56902000 | select * from emp group by id%20 order by 5 | +- -+-+ 3 rows in set 1 warning (0.00 sec)

5. Diagnostic SQL,show profile cpu,block io for query ID number; (ID number is the number in step 4 Query_ID column)

Mysql > show profile cpu,block io for query 3 +-- +-+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | | +-+ + | starting | 0.000049 | 0.000000 | 0.000000 | NULL | NULL | | checking permissions | 0.000005 | 0.000000 | NULL | NULL | | Opening tables | 0.000012 | 0.000000 | 0.000000 | NULL | NULL | init | 0.000021 | 0.000000 | 0.000000 | NULL | NULL | System lock | 0.000009 | 0.000000 | 0.000000 | NULL | NULL | | optimizing | 0.000003 | 0.000000 | 0.000000 | NULL | NULL | | statistics | 0.000017 | 0.000000 | 0.000000 | NULL | NULL | preparing | 0.000008 | 0.000000 | 0.000000 | NULL | NULL | Creating tmp table | 0.000045 | 0.000000 | 0 . 000000 | NULL | NULL | | Sorting result | 0.000004 | 0.000000 | 0.000000 | NULL | NULL | | executing | 0.000002 | 0.000000 | 0.000000 | NULL | NULL | | Sending data | 0.568704 | 0.546875 | NULL | NULL | | Creating sort index | 0.000048 | 0. 000000 | 0.000000 | NULL | NULL | | end | 0.000003 | 0.000000 | 0.000000 | NULL | NULL | | query end | 0.000005 | 0.000000 | 0.000000 | NULL | NULL | | removing tmp table | 0.000006 | 0.000000 | 0.000000 | NULL | NULL | | query end | | 0.000003 | 0.000000 | 0.000000 | NULL | NULL | | closing tables | 0.000004 | 0.000000 | 0.000000 | NULL | NULL | freeing items | 0.000061 | 0.000000 | 0.000000 | NULL | NULL | | cleaning up | 0.000015 | 0.000000 | 0.000000 | NULL | NULL | +-- +-+ 20 rows in set 1 warning (0.00 sec)

Parameter remarks (written in code): show profile cpu,block io for query 3; (cpu,block in such code)

ALL: displays all the cost information.

BLOCK IO: displays the cost associated with the block lO.

CONTEXT SWITCHES: context switching related overhead.

CPU: displays CPU-related overhead information.

IPC: displays information about sending and receiving overhead.

MEMORY: displays memory-related overhead information.

PAGE FAULTS: displays overhead information related to page errors.

SOURCE: displays cost information related to Source_function,Source_file,Source_line.

SWAPS: displays information about the cost associated with the number of exchanges.

6. Conclusions that should be paid attention to in daily development (these four problems are serious in the Status column)

Converting HEAP to MyISAM: the query result is so large that there is not enough memory to be moved to disk.

Creating tmp table: create temporary tables, copy data to temporary tables, and delete them after use

Copying to tmp table on disk: it's dangerous to copy temporary tables in memory to disk!

Locked: locked

6.7 Global query log

Never turn on this feature in a production environment, only in a test environment!

First: profile enabled. In the my.cnf of mysq l, the settings are as follows:

# enable general_log=1# to record the path of log file general_log_file=/path/logfile# output format log_output=FILE

The second kind: encode enabled. The command is as follows:

Set global general_log=1

Set global log_output='TABLE'

After that, the sql statements you write will be recorded in the geneial_log table in the mysql library, which can be viewed with the following command:

Mysql > select * from mysql.general_log +- -+ | event_time | user_host | thread_id | server_id | command_type | argument | +-- -+ | 2021-04-05 19 _ 28.182473 | root [root] @ localhost [:: 1] | 5 | 1 | Query | Select * from mysql.general_log | +-- +-- -+ 1 row in set (0.00 sec) 7 MySQL locking mechanism 7.1 Overview

Definition:

Lock is a mechanism by which a computer coordinates multiple processes or threads to access a resource concurrently.

In the database, in addition to the contention of traditional computing resources (such as CPU, RAM, Imax O, etc.), data is also a kind of resource shared by many users. How to ensure the consistency and effectiveness of data concurrent access is a problem that must be solved in all databases, and lock conflicts are also an important factor affecting the performance of database concurrent access. From this point of view, locks are particularly important and more complex for databases

Example: JD.com Shopping

For example, when we buy an item on JD.com, there is only one item in stock. If there is another person to buy it at this time, how to solve the problem of whether you bought it or another person bought it?

Transactions must be used here. We first take the quantity of items from the inventory table, then insert the order, insert the payment form information after payment, and then update the quantity of items. In this process, locks can be used to protect limited resources and solve the contradiction between isolation and concurrency.

Classification of locks:

From the type of operation on the data (read / write)

Read lock (shared lock): for the same data, multiple reads can be performed at the same time without affecting each other.

Write lock (exclusive lock): it blocks other write and read locks before the current write operation is completed.

From the granularity of data operations

Watch lock

Row lock

7.2 Table lock (biased)

Features: inclined to MyISAM storage engine, low overhead, fast locking; no deadlock; large locking granularity, the highest probability of lock conflict and the lowest degree of concurrency.

Explanation of the case of reading lock 1

Case analysis

Table building table

Create table mylock (id int not null primary key auto_increment, name varchar (20) default') engine myisam;insert into mylock (name) values ('a'); insert into mylock (name) values ('b'); insert into mylock (name) values ('c'); insert into mylock (name) values ('d'); insert into mylock (name) values ('e'); # query mysql > select * from mylock +-+ | id | name | +-+-+ | 1 | a | 2 | b | 3 | c | 4 | d | 5 | e | +-+-- + 5 rows in set (0.00 sec)

Manually add table locks: lock table table name read (write), table name 2 read (write), other

Mysql > lock table mylock read;Query OK, 0 rows affected (0.00 sec)

Check the locks added to the table: show open tables

Mysql > show open tables +-+-- + | Database | Table | | In_use | Name_locked | +-+-- + | performance | _ schema | events_waits_summary_by_user_by_event_name | 0 | 0 | performance_schema | events_waits_summary_global_by_event_name | 0 | 0 | performance_schema | events_transactions_summary_global_by_event_name | 0 | 0 | performance_schema | replication_connection_status | | 0 | 0 | mysql | time_zone_leap_second | 0 | 0 | mysql | columns_priv | 0 | 0 | my | test03 | | | 0 | 0 | | bigdata | mylock | 1 | 0 | 1 indicates that # In_use is locked |

Release Lock: unlock tables

Mysql > unlock tables;Query OK, 0 rows affected (0.00 sec) # check mysql > show open tables again +-+-- + | Database | Table | | In_use | Name_locked | +-+-- + | performance | _ schema | events_waits_summary_by_user_by_event_name | 0 | 0 | performance_schema | events_waits_summary_global_by_event_name | 0 | 0 | performance_schema | events_transactions_summary_global_by_event_name | 0 | 0 | performance_schema | replication_connection_status | | 0 | 0 | mysql | time_zone_leap_second | 0 | 0 | mysql | columns_priv | 0 | 0 | my | test03 | | | 0 | 0 | | bigdata | mylock | 0 | 0 |

Add read lock-add read lock to mylock table (read blocking write example)

Reading lock case explanation 2

Add a write lock to the mylock table (write blocking read example of the MylSAM storage engine)

Before executing the query statement (SELECT), MyISAM automatically adds read locks to all tables involved, and automatically adds write locks to the tables involved before adding, deleting or modifying operations.

There are two modes of table-level locks for MySQL:

Table shared read lock (Table Read Lock)

Table exclusive write lock (Table Write Lock)

Combined with the above table, when you operate on the MyISAM table, you will have the following situations:

Read operations on MyISAM tables (with read locks) do not block read requests from other processes to the same table, but block write requests to the same table. Only when the read lock is released will the write operations of other processes be performed.

The write operation to the MyISAM table (add write lock) blocks the read and write operations of other processes to the same table, and only when the write lock is released will the read and write operations of other processes be performed.

Highlight! In short, the read lock blocks writing, but not reading. On the other hand, write locks block both reading and writing.

Table lock summary

See which tables are locked: show open tables

How to analyze table locking

Table locking on the system can be analyzed by checking the table_locks_waited and table_locks_immediate state variables

Mysql > show status like 'table_locks%' +-+-+ | Variable_name | Value | +-- +-+ | Table_locks_immediate | 170 | | Table_locks_waited | 0 | +-- +- -+ 2 rows in set (0.00 sec)

There are two state variables that record the table-level locking within MySQL. The two variables are described as follows:

Table_locks_immediate: the number of times a table-level lock is generated, indicating the number of queries in which the lock can be acquired immediately, plus 1 for each lock acquired immediately

Table_locks_waited (key): the number of waits due to table-level lock contention (the number of times the lock cannot be acquired immediately, plus 1 for each waiting time). A high value indicates that there is a serious table-level lock contention.

In addition, MyISAM's read-write lock scheduling is write-first, which makes MyISAM unsuitable for writing as the main table engine. Because other threads cannot do anything after writing the lock, a large number of updates will make it difficult for the query to get the lock, resulting in permanent blocking

7.3 Row lock (overwrite)

Inclined to the InnoDB storage engine, the overhead is high and the locking is slow; deadlocks occur; the locking granularity is the smallest, the probability of lock conflicts is the lowest, and the concurrency is the highest.

There are two biggest differences between InnoDB and MyISAM: first, it supports transactions (TRANSACTION); second, it uses row-level locks.

Because row locks support transactions, review old knowledge:

Transaction (Transaction) and its ACID attribute

Problems caused by concurrent transaction processing

Transaction isolation level

1) A transaction is a logical processing unit composed of a set of SQL statements. A transaction has the following four attributes, usually referred to as the ACID attribute of a transaction:

Atomicity: a transaction is an atomic operation unit in which all or none of the changes to the data are performed.

Consistent: data must be in a consistent state at the beginning and completion of a transaction. This means that all relevant data rules must be applied to the modification of the transaction to maintain data integrity; at the end of the transaction, all internal data structures (such as B-tree indexes or two-way linked lists) must also be correct.

Lsolation: the database system provides an isolation mechanism to ensure that transactions are executed in an "independent" environment that is not affected by external concurrent operations. This means that the intermediate state during the transaction is not visible to the outside, and vice versa.

Durable: after a transaction is completed, its modification of the data is permanent, even in the event of a system failure.

2) problems caused by concurrent transaction processing

Update lost (Lost Update)

When two or more transactions select the same row and then update the row based on the initially selected value, the problem of missing updates occurs because each transaction is unaware of the existence of other transactions-the last update overwrites updates made by other transactions.

For example, two programmers modify the same java file. Each programmer changes its copy independently, and then saves the changed copy, thus overwriting the original document. The editor who finally saved a copy of his changes overwrites the changes made by the previous programmer.

This problem can be avoided if another programmer cannot access the same file before one programmer completes and commits the transaction.

Dirty reading (Dirty Reads)

A transaction is modifying a record, and the data of this record is in an inconsistent state before the transaction is completed and committed; at this time, another transaction also reads the same record, if uncontrolled, the second transaction reads these "dirty" data and makes further processing accordingly, resulting in uncommitted data dependencies. This phenomenon is vividly called "dirty reading".

Bottom line: transaction A reads the data that transaction B has modified but not yet committed, and operates on the basis of this data. At this point, if the B transaction is rolled back, the data read by An is invalid and does not meet the consistency requirements.

Non-repeatable (Non-Repeatable Reads)

At some time after reading some data, a transaction reads the previously read data again, only to find that the data it reads has changed, or that some records have been deleted. This phenomenon is called "unrepeatable reading".

Bottom line: transaction A reads the modified data that has been committed by transaction B, which does not conform to isolation.

Fantasy reading (Phantom Reads)

A transaction re-reads the previously retrieved data under the same query conditions, only to find that other transactions insert new data that meets its query conditions. This phenomenon is called "phantom reading".

Bottom line: transaction A reads the new data submitted by transaction B, which does not conform to isolation.

One more word: phantom reading is similar to dirty reading. Dirty reading is the modification of data in transaction B; illusory reading is the addition of data in transaction B.

3) transaction isolation level

"dirty reading", "non-repeatable reading" and "phantom reading" are all problems of database read consistency, which must be solved by the database to provide a certain transaction isolation mechanism.

The stricter the transaction isolation of the database, the less the side effects, but the greater the cost, because transaction isolation is essentially to make transactions "serialized" to a certain extent, which is obviously contradictory to "concurrency". At the same time, different applications have different requirements for read consistency and transaction isolation. For example, many applications are not sensitive to "unrepeatable reading" and "phantom reading" and may be more concerned about the ability to access data concurrently.

Often see the transaction isolation level of the current database: show variables like 'tx_isolation'

Mysql > show variables like 'tx_isolation' +-+-+ | Variable_name | Value | +-+-+ | tx_isolation | REPEATABLE-READ | +-+-+ 1 row in set 1 warning (0.00 sec) # default: MySQL avoids dirty reads and non-repeatable line locks

Build a table:

CREATE TABLE test_innodb_lock (an INT (11), b VARCHAR (16) ENGINE=INNODB;INSERT INTO test_innodb_lock VALUES); INSERT INTO test_innodb_lock VALUES (3 recordings 3'); INSERT INTO test_innodb_lock VALUES (4, '4000'); INSERT INTO test_innodb_lock VALUES (5 dagger 5000'); INSERT INTO test_innodb_lock VALUES (6, '6000'); INSERT INTO test_innodb_lock VALUES (7 recordings 7000') INSERT INTO test_innodb_lock VALUES (8, '8000'); INSERT INTO test_innodb_lock VALUES (9 recordings 9000'); INSERT INTO test_innodb_lock VALUES (1 recordings b1'); CREATE INDEX test_innodb_a_ind ON test_innodb_lock (a); CREATE INDEX test_innodb_lock_b_ind ON test_innodb_lock (b); / / View mysql > select * from test_innodb_lock +-+-+ | a | b | +-+-+ | 1 | B2 | | 3 | 3 | 4 | 4000 | 5 | 5000 | 6 | 6000 | 7 | 7000 | 8 | 8000 | 9 | 9000 | 1 | b1 | +-+-+ 9 rows in set (sec) mysql > show index from test_innodb_lock +- -- +-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +- -+ -+ | test_innodb_lock | 1 | test_innodb_a_ind | 1 | a | A | 8 | NULL | NULL | YES | BTREE | | | | test_innodb_lock | 1 | test_innodb_lock_b_ind | 1 | b | A | 9 | NULL | NULL | YES | BTREE | +-| -+- -- +

Basic demonstration of row locking (two clients update the same row of records)

Wonder why both of them need commint.

Index failure row lock variable table lock

Upgrade an indexed row lock to a table lock

Gap lock

What is a gap lock?

When we retrieve data using range conditions instead of equality conditions, and request shared or exclusive locks, InnoDB locks the index entries of existing data records that meet the conditions. Records whose key values are within the range of conditions but do not exist are called "GAP".

InnoDB also locks this "gap", and this locking mechanism is called gap lock (Next-Key lock).

Harm

Because if Query passes through a range lookup during execution, he will lock all index key values in the entire range, even if the key value does not exist.

Gap lock has a fatal weakness, that is, when a range key value is locked, even some non-existent key values will be innocently locked, so that any data in the range of lock key values cannot be inserted when locked. In some scenarios, this can cause great harm to performance.

Interview questions: how to lock a row

Begin (write your own operation in the middle) commit

Row lock summary

Summary:

Because Innodb storage engine implements row-level locking, although the performance loss caused by the implementation of locking mechanism may be higher than that of table-level locking, it is much better than MyISAM's table-level locking in terms of overall concurrency processing capacity. When the system concurrency is high, the overall performance of Innodb will have obvious advantages compared with MylISAM.

However, Innodb's row-level locking also has its fragile side, when we use it improperly, it may make the overall performance of Innodb not only not higher than MyISAM, but even worse.

How to analyze row locking?

Analyze row lock contention on the system by checking the lnnoDB_row_lock state variable: show status like 'innodb_row_lock%'

Mysql > show status like 'innodb_row_lock%' +-- +-+ | Variable_name | Value | +-+-+ | Innodb_row_lock_current_waits | 0 | Innodb_row_lock_time | | 0 | | Innodb_row_lock_time_avg | 0 | Innodb_row_lock_time_max | 0 | | Innodb_row_lock_waits | 0 | 0 | +-+-+ 5 rows in set (0.00 sec) |

The description of each state quantity is as follows:

Innodb_row_lock_current_waits: number of locks currently waiting

Innodb_row_lock_time: the total length of time since the system was booted to the present lock

Innodb_row_lock_time_avg: average time spent waiting

Innodb_row_lock_time_max: the time it takes to wait for the most common one since the system starts up

Innodb_row_lock_waits: the total number of times the system has waited since the system started

For these five state variables, the more important ones are:

Lnnodb_row_lock_time (total waiting time)

Innodb_row_lock_time_avg (average waiting time)

Lnnodb_row_lock_waits (total number of waits)

Especially when the waiting times are high and the waiting time is not small, we need to analyze why there is so much waiting in the Show Profile system, and then start to specify the optimization plan according to the analysis results.

Optimization suggestion

Let all data retrieval be done through indexing as much as possible to avoid upgrading from indexed row locks to table locks.

Reasonably design the index to minimize the scope of the lock.

Search conditions as few as possible to avoid gap locks

Try to control the transaction size and reduce the amount of locked resources and time length.

The lowest possible level of transaction isolation

Page lock

The overhead and locking time are between table lock and row lock; deadlocks occur; lock granularity is between table lock and row lock, and the concurrency is average. (just get to know it)

8 the basic principles of master-slave replication 8.1 replication

Slave reads binlog from master for data synchronization

Schematic:

The MySQL replication process is divided into three steps:

1. Master records the changes to the binary log (binary log). These recording processes are called binary log events, binary log events

2. Slave copies the binary log events of master to its relay log (relay log)

3. Slave redoes the events in the relay log and applies the changes to its own database. MySQL replication is asynchronous and serialized

8.2 basic principles of replication

There is only one master per slave

There can be only one unique server ID per slave

Each master can have multiple salve

The biggest problem with replication is latency.

8.3 one master, one slave common configuration

1. The version of mysql is consistent and the background runs as a service.

Second, both master and slave are configured under the [mysqld] node, all in lowercase.

The host modifies the my.ini configuration file:

1. [must] the master server has a unique ID:server-id=1

2. [must] enable binary logging

Log-bin= 's own local path / mysqlbin

Log-bin=D:/devSoft/MySQLServer5.5/data/mysqlbin

3. [optional] enable error log

Log-err= 's own local path / mysqlerr

Log-err=D:/devSoft/MySQLServer5.5/data/mysqlerr

4. [optional] root directory

Basedir= "own Local path"

Basedir= "D:/devSoft/MySQLServer5.5/"

5. [optional] temporary directory

Tmpdir= "own Local path"

Tmpdir= "D:/devSoft/MySQLServer5.5/"

6. [optional] data directory

Datadir= "own local path / Data/"

Datadir= "D:/devSoft/MySQLServer5.5/Data/"

7. Mainframe, both read and write

Read-only=O

8. [optional] set the database not to be replicated

Binlog-ignore-db=mysql

9. [optional] set the database to be replicated

The name of the primary database that binlog-do-db= needs to replicate

Modify the my.cnf configuration file from the machine:

1. [must] unique ID:vim etc/my.cnf from the server (enter the modified configuration file)

... # server-id=1 / / Note crane... server-id=1 / / on.

2. [optional] enable binary logging

Third, configuration file, ask the host and slave to restart the background mysql service

Hosts: manual restart

Linux slave naming:

Service mysql stop

Service mysql start

Fourth, the host slave computer shuts down the firewall

Windows shuts down manually

Turn off the virtual machine linux firewall: service iptables stop

Establish an account on the Windows host and authorize slave

GRANT REPLICATION SLAVE ON. TO 'zhangsan'@' from machine database IP' IDENTIFIED BY' 123456'

Refresh: flush privileges

Query the status of master

Show master status

Record the values of File and Position

Do not operate the master server MYSQL after performing this step to prevent the status value of the master server from changing

Configure the host to be replicated on the Linux slave

CHANGE MASTER TO MASTER_HOST=' host IP'

MASTER_USER='zhangsan'

MASTER_PASSWORD='123456'

MASTER_LOG_FILE='File name'

MASTER_LOG_POS=Position digit

Start copy from server function: start slave

Show slave status\ G (if the following two parameters are both Yes, the master / slave configuration is successful!)

Slave_IO_Running:Yes

Slave_SQL_Running:Yes

VII. Host newly created libraries, newly created tables, insert records, and slave replication

Host operation

Slave (automatic synchronization)

8. How to stop replication from the service: stop slave

What if there is a piece of data not for the time being?

Slave:

Host (need to recheck the scale):

Read here, this "MySQL SQL optimization, index optimization, lock mechanism, master-slave replication knowledge" article has been introduced, want to master the knowledge of this article also need to practice and use in order to understand, if you want to know more about the article, welcome to pay attention to the industry information channel.

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.