MySql > MySql > MySql > MySql > MySql > MySql

Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”

This article has participated in the “Digitalstar Project” and won a creative gift package to challenge the creative incentive money.

This is the second article of MySQL knowledge system summary series. The main content of this article is to analyze SQL step by step through explain, and tune SQL statements by modifying SQL statements and building indexes. You can also view the log to understand the execution of SQL, but also introduced the MySQL database row lock and table lock.

Explain return column profiles

1. Type Common keywords

System > const > eq_ref > ref > range > index > all.

1. System: The table has only one row.
2. Const: at most one row in the table.
3. Eq_ref: Reads a row from the table for each combination of rows from the previous table. This is probably the best join type, except for const;
4. Ref: For each combination of rows from the previous table, all rows with matching index values are read from the table;
5. Range: Retrieves only rows in a given range, using an index to select rows. Range can be used when comparing keyword columns with constants using the =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, or IN operators;
6. Index: This join type is the same as ALL, except that only the index tree is scanned. This is usually faster than ALL because index files are usually smaller than data files;
7. All: full table scan.

In real SQL optimization, the ref or range level is finally reached.

2, Extra common keywords

Using index: Get information only from the index tree without going back to the table;

Using WHERE: The WHERE clause is used to restrict which row matches the next table or is sent to the customer. Unless you specifically ask for or check ALL rows from the table, the query may have some errors if the Extra value is not Using WHERE and the table join type is ALL or index. The query needs to be returned to the table.

Using temporary: mysql creates a temporary table to hold results, typically when a query contains GROUP BY and ORDER BY clauses that can list columns in different cases.

Trigger index code instance

1, build a table sentence + joint index

CREATE TABLE `student` (
  `id` int(10) NOT NULL,
  `name` varchar(20) NOT NULL,
  `age` int(10) NOT NULL,
  `sex` int(11) DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
  `phone` varchar(100) DEFAULT NULL,
  `create_time` timestamp NULL DEFAULT NULL,
  `update_time` timestamp NULL DEFAULT NULL,
  `deleted` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `student_union_index` (`name`,`age`,`sex`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Copy the code

2, use primary key query

3, use the federated index query

4, join index, but not in the order of the index

Note: Because of the mysql optimizer, if the order is not consistent with the index, the index will be triggered, but the order should be consistent in the actual project.

SQL > alter table associative index

3, single table SQL optimization

Alter table student alter table student alter table student

alter table student add index student_union_index(name,age,sex);
Copy the code

3. Change the index order

Because of the process of writing SQL

select distinct ... from ... join ... on ... where ... group by ... having ... order by ... limit ...
Copy the code

The parsing process

from ... on ... join ... where ... group by ... having ... select distinct ... order by ... limit ...
Copy the code

Therefore, I suspect that the order in which the joint index is built causes the index trigger to fail. Are you sure? Just try it.

alter table student add index student_union_index2(age,sex,name);
Copy the code

Delete old and unused indexes:

drop index student_union_index on student
Copy the code

The index change name

ALTER TABLE student RENAME INDEX student_union_index2 TO student_union_index
Copy the code

After changing the index order, the type level changes from index to range. Range: Retrieves only rows in a given range, using an index to select rows.

Note: In invalidates the index, so using WHERE is triggered, which results in a query back to the table.

4, Get rid of in

1. Build a predicate sentence

CREATE TABLE `student` (
  `id` int(10) NOT NULL,
  `name` varchar(20) NOT NULL,
  `age` int(10) NOT NULL,
  `sex` int(11) DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
  `phone` varchar(100) DEFAULT NULL,
  `create_time` timestamp NULL DEFAULT NULL,
  `update_time` timestamp NULL DEFAULT NULL,
  `deleted` int(11) DEFAULT NULL,
  `teacher_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Copy the code

CREATE TABLE `teacher` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `course` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Copy the code

2. Left connection query

Explain select s.title,t. title from student s left join teacher t on S.title = T.ID where T. course = 'math'Copy the code

As you saw in the previous article, small tables drive large tables in federated queries. Small tables are also called drive tables. This is a double for loop, where the small table is the outer loop and the second table (the large table) is the inner loop.

Although the end result of the loop is the same, with the same number of loops, for double loops it is generally recommended to put the small loop in the outer layer and the large loop in the inner layer. This is a programming language optimization principle.

Code test again:

Student data: four

Teacher data: three

According to theoretical analysis, teacher should be the driver table.

SQL statements should be changed to:

explain select teacher.name,student.name from teacher left join student on teacher.id = student.id where teacher.course = 'math'Copy the code

Optimization usually requires an index, so how to add an index? Which table to add an index to?

The basic idea is that indexes should be built on frequently used fields.

Id = student. Id = student. Id = student.

Left join on; Because it’s the driver table.

alter table teacher add index teacher_index(id);
alter table teacher add index teacher_course(course);
Copy the code

Note: if a using join buffer appears in extra, it indicates that mysql is aware of the poor performance of SQL writing.

3, summary

1, exist and in

Select name,age from student exist/in (subquery);Copy the code

If the data set of the main query is large, use in;

If the subquery data set is large, exist is used.

2, Order by optimization

Using filesort has two algorithms: double sort, double sort (according to the number of I/OS)

Before MySQL4.1, dual sort was used by default; Dual-path: scans disks twice. (1) Read the sorting fields from disks and sort the sorting fields. ② Get other fields).

After MySQL4.1, single-way sort is used by default; Single-path: Read only once (all fields) and sort in buffer. However, single-way sorting has some pitfalls (not necessarily only one I/O, but multiple I/OS).

Note: Single-path sort takes up more buffer than double-path sort.

If the data volume is large, you can increase the size of the buffer.

set max_length_for_sort_data = 1024; The unit is byte.Copy the code

If the value of max_LENGTH_FOR_sort_data is too low, the MySQL base will automatically switch from single-path to dual-path.

Too low means that the total size of the column exceeds the number of bytes defined by max_LENGTH_FOR_sort_DATA.

Improve the order by query strategy:

1. Choose single or double channels to adjust the buffer capacity.
2. Avoid select * from student; (① MySQL bottom need to translate *, consumption of performance; ② * never trigger index overwriting using index);
3. Using filesort; using filesort;
4. Ensure the consistency of all sort fields (ascending or descending);

SQL sequence -> slow log query

Slow query log is a type of log provided by MySQL. It records THE SQL statements whose response time exceeds the threshold (long_query_time, 10 seconds by default).

Slow logging is turned off by default, turned on for development tuning, and turned off for final deployment.

1. Slowly query logs

(1) Check whether slow log query is enabled:

show variables like '%slow_query_log%'

(2) Temporary opening:

set global slow_query_log = 1;
Copy the code

MySQL > restart MySQL

service mysql restart;

(4) Permanent opening:

/etc/my.cnf

Place it under [mysqld] :

    slow_query_log=1
    slow_query_log_file=/var/lib/mysql/localhost-slow.log
Copy the code

2, the threshold

(1) Check the default threshold:

show variables like '%long_query_time%'
Copy the code

(2) Modify the default threshold temporarily:

set global long_query_time = 5;
Copy the code

(3) Permanently modify the default threshold:

/etc/my.cnf

Place it under [mysqld] :

long_query_time = 5;

MySQL > select * from sleep;

select sleep(5);

(5) Query SQL whose execution time exceeds the threshold:

show global status like '%slow_queries%';

Slow query logs –> mysqlDumpslow

1. Mysqldumpslow tool

2, query the slow SQL under different conditions

(1) Return 3 SQL with the most records

mysqldumpslow -s r -t 3 /var/lib/mysql/localhost-slow.log

(2) Get the three most frequently accessed SQL

mysqldumpslow -s c -t 3 /var/lib/mysql/localhost-slow.log

(3) Sort by time, the first 10 SQL statements containing left JOIN query statements

mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/localhost-slow.log

Analyzing massive data

1, show profiles

Turn on this feature: set profiling = on;

Show Profiles records the time spent on all SQL queries after all profileing calls.

The disadvantage is that it is not precise enough to determine which part of the execution consumes time, such as CPU, IO.

2, accurate analysis, SQL diagnosis

Show profile all for query Query_id obtained in the previous step.

3. Query logs globally

show variables like ‘%general_log%’

Enable global logging:

set global general_log = 1;

set global log_output = table;

X. Detailed explanation of lock mechanism

1. Operation classification

Read and write: Multiple read operations on the same data can be performed simultaneously without interference.

Write lock: If the current write operation is not complete, other read and write operations cannot be performed.

2. Scope of operation

Table lock: Locks a table at a time.

For example, MyISAM storage engine uses table locks, which have low overhead, fast locking and no deadlocks. However, the scope of lock is large, prone to conflict and low concurrency.

Row lock: Locks one data item at a time.

For example, InnoDB storage engine uses row lock, which is expensive, slow and prone to deadlock. Lock scope is small, lock conflict is not easy, high concurrency (very small probability of high concurrency problems: dirty read, unreal read, unrepeatable read)

Lock table 1 read/write, table 2 read/write,…

To view a locked table:

show open tables;

3, add read lock, code example

Session 0: lock table student read; select * from student; Delete from student where id = 1; Select * from user; Delete from user where id = 1; -- Add, delete and change, noCopy the code

If A session has A read lock on table A, the session can read but cannot write to table A. That is, if A read lock is added to table A, the current session can only read table A and cannot read other tables

Session 1: select * from student; Delete from student where id = 1; Session 1: select * from user; Delete from user where id = 1; -- Add, delete and change, yesCopy the code

Session 0 assigns A lock to table A. Other sessions can read and write data from other tables. Session 0 assigns A lock to table A.

4, add write lock