Features
Features:

Product Tour >

Edraw AI >

Paid Plans:

Individuals >

Business >

Eduaction >
Resources
Blog

History

How-tos & Tips

Discovery

Biography

Business Analysis

Examples

AI concept Map

Free AI Mind Map Generator

Onenote Mind Map

Bcg Matrix Examples

Nike Marketing Strategy

Unilever SWOT Analysis

Make Mind Maps in Google Docs

Guide

FAQs

What's New

Resource Center
Templates
All Templates

Brain Storming Templates

Strategy and Planning Templates

Project Management Templates

Product Management Templates

Human Resources Templates

Agile Workflow Templates

Marketing Templates

Education Templates

Fun and Games Templates

User Gallery
Download
Pricing
Enterprise

MindMap Gallery MySql practical lecture 45

MySql practical lecture 45

This is a mind map of 45 practical lessons on MySql. Through continuous learning and practice, you can better use MySQL to meet business needs and improve data storage and processing efficiency.

Edited at 2023-12-21 17:35:18

PlotWizard

Recent works View more works>>

MySql practical lecture 45

PlotWizard

Recent works View more works>>

Recommended to you
Outline

MySql practical lecture 45

8. Are transactions isolated or not?

The concept of views in MySql

One is view. It is a virtual table defined with a query statement. When called, the query statement is executed and the results are generated. The syntax for creating a view is create view..., and its query method is the same as that of a table

The other is the consistent read view used by InnoDB when implementing MVCC, which is used to support the implementation of RC (Read Committed) and RR (Repeatable Read) isolation levels.

InnoDB takes advantage of the feature of "all data has multiple versions" (MVCC) to achieve the ability to "create snapshots in seconds".

1. Update data is read first and then written, and this read can only read the current value, which is called "current read" 2.select will generate a snapshot read

RC (read committed), RR (repeatable read)

Under the read-committed isolation level, a view is generated before each statement is executed, and the query only recognizes data that has been submitted before the statement is started;

Under the isolation level of repeatable read, a view will be generated when the transaction starts and will be used thereafter. Only when a DML statement is executed, the current read will be generated. The query will only recognize the data that has been submitted before the transaction is started.

10.Why does MySQL sometimes choose the wrong index?

reason

The optimizer incorrectly analyzed the number of rows that needed to be scanned

For problems caused by inaccurate index statistics, you can use analyze table to re-analyze the table.

Index selection exceptions and handling

Check for phrases

select * from mdm_company where a between 1 and 100 and b between 1000 and 50000 order by b limit 1;

The optimizer will think that index b has been sorted, so choosing to use index b will avoid sorting and just take the values in order. Therefore, even if the number of scanned rows is large, it will be judged to be less costly.

Use force index to forcefully select an index

select * from mdm_company force index (a) where a between 1 and 100 and b between 1000 and 50000 order by b limit 1;

Assuming that both a and b have indexes, then you only need to scan 100 rows to index a, and then push down the index b to filter the data.

shortcoming:

If the index name is changed, the SQL needs to be changed.

Modify the statement to direct MySQL to use the index we want

Directly delete misused indexes

12. Why does my MySQL "tremble"?

Buffer Pool in innoDB The memory pages in the buffer pool have three states

The first type: unused

The second type: clean pages that have been used and written to disk

The third type: Dirty pages that have been used but have not been written to the disk

What are dirty pages?

When the memory data page is inconsistent with the disk data page, we call this memory page a "dirty page". After the memory data is written to the disk, the contents of the data pages in the memory and the disk are consistent, which is called a "clean page"

When the database is shaking, dirty pages are being flushed. There are four situations when the database flushes dirty pages.

Situation one:

The redo log is full. At this time, the system will stop the update operation and execute the redo log to disk.

This situation is what InnoDB should try to avoid. Because when this happens, the entire system can no longer accept updates, and all updates must be blocked. If you look at the monitoring, the number of updates will drop to 0 at this time

Situation 2:

The system has insufficient memory and needs to eliminate memory. At this time, the data of dirty pages needs to be written to the disk.

Situation three:

Dirty pages will also be flushed when the system is idle.

Situation four:

When Mysql is shut down normally, dirty pages will also be flushed.

14.count(*) is so slow, what should I do?

count() is an aggregate function. The returned result set is judged row by row. If the parameter of the count function is not NULL, the cumulative value is increased by 1, otherwise it is not added. Finally return the cumulative value

Sort by efficiency count(field)<count(primary key id)<count(1)≈count(*)

Mysql implementation

The MyISAM engine stores the total number of rows of a table on the disk, so when executing count(*), it will directly return this number, which is very efficient;

The InnoDB engine is in trouble. When it executes count(*), it needs to read the data out of the engine line by line, and then accumulate the count.

Why does InnoDB not use Count(*) to count the number of records like MyISAM?

First of all, the MyISAM storage engine does not support transactions, but InnoDB does support transactions. InnoDB uses MVCC multi-version concurrency control. The number of records obtained by different transactions should be different, so you cannot use variable records like MyISAM to record the number of records.

Optimization of the total number of records in InnoDB statistics

Non-clustered indexes do not store data. One page IO can obtain more records, so the Mysql optimizer will find the smallest tree to traverse.

Can I use show tables instead of count(*)?

No, because although the show tables command has a parameter TABLE_ROWS that is used to display how many rows the table currently has, this command counts indexes, and there are errors in index statistics.

The reason why placing the count in Redis cannot ensure that the count is accurate and consistent with the data in the MySQL table is that the systems composed of these two different storage systems do not support distributed transactions and cannot obtain accurate and consistent views.

13.Why is the table file size unchanged when half of the table data is deleted?

In order to avoid index rearrangement, when deleting data, the data will not actually be deleted from the disk, but the space will only be marked for reuse. Therefore, when you execute the delete command to delete the entire table, all data pages will be marked as reusable, but the file on the disk will not become smaller.

Page splitting (out-of-order insertion)

Execute the alter table A engine=InnoDB command to rebuild the table. This command will complete the creation of temporary tables, transfer data, exchange table names, and delete old tables. This command will block DML statements in versions before 5.5, but not after 5.6.

Obviously, the step that takes the most time is the process of inserting data into the temporary table. If new data is written to table A during this process, data loss will occur. Therefore, there can be no updates in Table A during the entire DDL process. In other words, this DDL is not Online

Page merge phenomenon

11.How to add index to string field?

How to create an index for a field with a very long character length, such as a mailbox

prefix index

Problems

This will increase the number of rows that need to be scanned, affecting performance.

If the discrimination is not high, the number of rows that need to be scanned will increase. After matching the results on the index tree You need to go back to the primary key index to get the record. If you find that they are not equal, you need to go back to the index number again to read the next record.

Solution

select count(distinct left(email,4))as L4, count(distinct left(email,5))as L5, count(distinct left(email,6))as L6, count(distinct left(email,7))as L7, from SUser;

Calculate the distinction. If the distinction is not less than L*95%, it is suitable to establish a prefix index.

Affect index coverage

select id,email from SUser where email='zhangssxyz@xxx.com';

If the index field email uses the index structure of the entire string, then you only need to query the results on the index tree of email, and use index coverage to directly return the result set. And if the emila field uses a prefix index, the table must be returned to determine the value of the email field.

Create a complete index directly, which may take up more space; Create a prefix index to save space, but it will increase the number of query scans, and covering indexes cannot be used; Store in reverse order and then create a prefix index to bypass the problem of insufficient discrimination of the prefixes of the string itself; Create a hash field index, the query performance is stable, and there is additional storage and calculation consumption. Like the third method, range scanning is not supported.

9. How to choose between ordinary index and unique index?

Update difference

For a normal index, after finding the first record (5,500) that meets the condition, you need to find the next record until you encounter the first record that does not meet the k=5 condition.

For a unique index, since the index defines uniqueness, the search will stop after the first record that meets the conditions is found.

The efficiency difference is negligible and can be ignored The InnoDB engine reads one page of data at a time, each page is 16KB, if k=5 It happens to be the last record of the current page, then another IO operation is required.

Differences in insert statements

What is Change Buffer

When a data page needs to be updated, if the data page is in memory, it will be updated directly. If the data page is not yet in memory, InooDB will cache these update operations in the change buffer without affecting data consistency. , so there is no need to read this data page from disk. When the next query needs to access this data page, read the data page into memory, and then perform operations related to this page in the change buffer. In this way, the correctness of the data logic can be ensured.

Can only be used on ordinary indexes

Change Buffer is suitable for scenarios with more writing and less reading, such as logging systems and accounting systems. If it is read immediately after writing, then the cost of maintaining the Change Buffer is too high and the merge is constant.

Change Buffer operations will also be recorded in the redo log, so even if the database restarts abnormally, the data in the Change Buffer has not had time to be executed, and the data will not be lost.

The difference between unique index and ordinary index update operations

If the data to be updated happens to be in the current memory

Ordinary indexes directly insert data at the specified location, while unique indexes need to first determine whether there is an index conflict before inserting data.

If the data to be updated is not in the current page

The statement ends when the ordinary index stores the record in the Change Buffer, but the unique index needs to determine whether there is an index conflict. It needs to read the data page into the memory first, and then insert the value after determining that there is no conflict.

7. How to reduce the impact of row locks on performance?

In InnoDB transactions, row locks are added when needed, but they are not released immediately when they are no longer needed. Instead, they need to be released after the transaction is committed.

If you need to lock multiple rows in your transaction, you should place the locks that are most likely to cause lock conflicts and affect concurrency as far back as possible (for example, if update personal balance and update company balance are executed in the same transaction, then you should Execute update to update the company balance at the end, because the impact of adding a row lock to the company's row record will be greater than adding a row lock to the personal balance)

How to solve the deadlock in InnoDB?

Causes of deadlock

Two transactions occupy the resources needed by each other

Solution

Go directly to wait until timeout. This timeout can be set through the parameter innodb_lock_wait_timeout. InnoDB's default timeout is 50s

If a deadlock problem occurs and you need to wait for 50 seconds, the impact on the business will be huge. If we change the timeout If the setting is very short, if it is other normal business that needs to be locked, the lock will be lost in a short time, which will affect the business.

Initiate deadlock detection, and after discovering a deadlock, proactively roll back a transaction in the deadlock chain to allow other transactions to continue executing. Set the parameter innodb_deadlock_detect to on, indicating that this logic is turned on. Consistent reading does not detect deadlocks.

Each newly blocked thread must determine whether its own addition will cause a deadlock. This is an operation with a time complexity of O(n). Assuming that there are 1,000 concurrent threads that want to update the same row at the same time, then the number of deadlock detection operations is of the order of 1 million. Although the final detection result is that there is no deadlock, a large amount of CPU resources will be consumed during this period. Therefore, you will see high CPU utilization, but not many transactions per second.

How to solve the performance problems caused by such hot row updates?

Control the number of concurrent operations on the same row

Turn off deadlock checking (there is a very serious security problem and will cause a large number of business timeouts)

Convert a single line modification to a multi-line modification