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

Transaction level

The SQL standard defines four types of isolation levels, including specific rules that define which changes inside and outside the transaction are visible and which are not. Low-level isolation levels generally support higher concurrent processing and have lower system overhead.

Read Uncommitted (read uncommitted)

At this isolation level, all transactions can see the execution results of other uncommitted transactions. This isolation level is rarely used in practical applications because its performance is not much better than other levels. Reading uncommitted data is also known as Dirty Read.

Read Committed (read submission)

This is the default isolation level for most database systems (but not the default for MySQL). It satisfies the simple definition of isolation: a transaction can only see changes that have been committed to the transaction. This isolation level also supports so-called non-repeatable reads (Nonrepeatable Read), because other instances of the same transaction may have a new commit during the instance processing, so the same select may return different results.

Repeatable Read (reread)

This is the default transaction isolation level for MySQL, which ensures that multiple instances of the same transaction see the same rows of data when reading data concurrently. But in theory, this leads to another thorny problem: Phantom Read. To put it simply, phantom reading means that when the user reads a range of data rows, another transaction inserts a new row in that range, and when the user reads the range of data rows, they will find a new "phantom" row. InnoDB and Falcon storage engines solve this problem through multi-version concurrency control (MVCC,Multiversion Concurrency Control) mechanisms.

Serializable (serializable)

This is the highest isolation level, and it solves the problem of phantom reading by forcing the ordering of transactions so that they cannot conflict with each other. In short, it adds a shared lock to each read row of data. At this level, it can lead to a lot of timeouts and lock competition.

These four isolation levels are implemented with different lock types, which are prone to problems if the same data is read. For example:

Dirty reading (Drity Read): one transaction has updated a piece of data, and another transaction has read the same data at this time. For some reason, if the previous RollBack operates, the data read by the latter transaction will be incorrect.

Non-repeatable read (Non-repeatable read): data inconsistencies between two queries of a transaction, which may be due to the insertion of original data updated by a transaction between the two queries.

Phantom Read: the number of data pens is inconsistent in two queries of a transaction, for example, one transaction queries several columns of data (Row), while another transaction inserts new columns of data at this time, the previous transaction will find several columns of data that it did not have before in the next query.

In MySQL, these four isolation levels are implemented, each of which may cause problems as follows:

Invalid index:

An index does not always take effect. For example, the following situations will invalidate the index:

1. If there is an or in the condition, it will not be used even if there is a conditional index (which is why or is used as little as possible)

Note: if you want to use or and want the index to take effect, you can only index every column in the or condition.

two。 For multi-column indexes, which are not the first part of the use, the index will not be used

The 3.like query starts with%

4. If the column type is a string, be sure to quote the data in quotation marks in the condition, otherwise the index is not used

5. If mysql estimates that using a full table scan is faster than using an index, do not use an index

In addition, check the usage of the index

Show status like 'Handler_read%'

You can pay attention to:

Handler_read_key: the higher the value, the better. A higher value indicates the number of times to query using the index.

Handler_read_rnd_next: the higher the value, the less efficient the query.

The cases of index failure are summarized as follows:

The data rows on the request table exceed 30% of the total records of the table and become a full table scan.

Null value exists on index column on predicate

Index column conditions on predicates use functions

The index column condition on the predicate is related to the operation.

The index column condition on the predicate uses the NOT IN operator

In a composite index, the first index column uses a range query-only partially or not.

In a composite index, the first query condition is not the leftmost index column

The leftmost column of fuzzy query criteria starts with the wildcard%

Memory tables (HEAP tables) use range retrieval or ORDER BY when using HASH indexes

Table associated fields are of different types (including some lengths, but for example varchar (10) and char (10), MYSQL is internally optimized)

Index type (not strictly classified by purpose)

General index, this is the most basic index, there are no restrictions

Unique index, similar to normal index, index column value must be unique, null value is allowed

Full-text indexing, indexing based on stemming, mostly for BLOB data types

Single-column index, an index created based on only one column

Multi-column index, an index based on multiple columns. Column order is very important.

Spatial index, used as geographic data storage

The primary key index, which is a special unique index, does not allow null values and is usually created when the table is created.

Advantages and disadvantages of index

Advantages of indexing

Greatly reduces the amount of data that the server needs to scan

Can help the server avoid sorting or reduce the use of temporary table sorting

The index can be changed from random I to O to sequential I to O

Shortcomings of the index

Disk space is required, so redundant and inefficient indexes will take up a lot of disk space

Reduce DML performance, any addition or deletion of data will need to adjust the corresponding index, and even index splitting will occur.

The index will produce corresponding fragments, resulting in maintenance overhead

Composite index:

A federated index is also called a composite index. For composite indexes: Mysql uses fields in the index from left to right, and a query can use only a portion of the index, but only the leftmost part. For example, the index is key index (a _ r _ b _ r _ c). It can be searched with three combinations of a | aPermian b | aPermian bPermec, but not bPermie c. The index is very effective when the leftmost field is a constant reference.

An index on two or more columns is called a composite index.

With additional columns in the index, you can narrow your search, but using an index with two columns is different from using two separate indexes. The structure of the composite index is similar to that of the phone book, where the first name consists of a last name and a first name. The phone book first sorts pairs by last name, and then sorts people with the same surname by first name. The phone book will be very useful if you know your last name; if you know your first name and first name, the phone book will be more useful, but if you only know your first name, the phone book will be useless.

So when creating a composite index, you should carefully consider the order of the columns. A composite index is useful when performing a search on all columns in an index or only on the first few columns; a composite index is not useful when searching only any subsequent column.

Such as: to establish a compound index of name, age and gender.

Create table test (

An int

B int

C int

KEY a (a _ r _ b _ m c)

)

Excellent: select * from test where axiom 10 and b > 50

Poor: select * from test where A50

Excellent: select * from test order by a

Poor: select * from test order by b

Poor: select * from test order by c

Excellent: select * from test where axiom 10 order by a

Excellent: select * from test where axiom 10 order by b

Poor: select * from test where axiom 10 order by c

Excellent: select * from test where a > 10 order by a

Poor: select * from test where a > 10 order by b

Poor: select * from test where a > 10 order by c

Excellent: select * from test where axioms 10 and bundles 10 order by a

Excellent: select * from test where axioms 10 and bundles 10 order by b

Excellent: select * from test where axioms 10 and bundles 10 order by c

Excellent: select * from test where axioms 10 and bundles 10 order by a

Excellent: select * from test where axiom 10 and b > 10 order by b

Poor: select * from test where axiom 10 and b > 10 order by c

Index principle

1. The fewer indexes, the better

Reason: mainly when modifying the data, the first index should be updated to slow down the writing speed.

two。 The narrowest field is on the left side of the key.

3. Avoid file sort sorting, temporary tables and table scans.

B-tree, index

Introduction to the principle of B+/-Tree B-Tree

B-Tree is a multipath search tree (not binary):

1. Define that any non-leaf node has at most M sons, and M > 2

two。 The number of sons in the root node is [2, M]

3. The number of sons of non-leaf nodes other than root nodes is [M _ pr _ 2, M]

4. Each node stores at least 2-1 (rounded up) and at most 1 keyword (at least 2 keywords).

5. Number of keywords for non-leaf nodes = number of pointers to sons-1

6. Keywords for non-leaf nodes: K [1], K [2], … , K [M-1]; and K [I] < K [ionization 1]

7. Pointer of non-leaf node: P [1], P [2], … , P [M], where P [1] points to the subtree where the keyword is less than K [1], P [M] points to the subtree whose keyword is greater than K [M-1], and other P [I] points to the subtree where the keyword belongs to (K [I-1], K [I]).

8. All leaf nodes are in the same layer.

Such as: (Mitte3)

Characteristics of B-tree:

1. Keyword sets are distributed throughout the tree.

two。 Any keyword appears and only appears in one node

3. The search may end at non-leaf nodes.

4. Its search performance is equivalent to doing a binary search in the full set of keywords.

5. Automatic hierarchical control

The search of the B-tree starts from the root node and searches for the keyword (ordered) sequence in the node. If it hits, it ends, otherwise it enters the son node that the query keyword belongs to; repeat until the corresponding son pointer is empty, or it is already a leaf node.

B+Tree introduction

The B + tree is a variant of the B-tree and is also a multipath search tree:

1. Its definition is basically the same as that of B-tree, except that:

two。 The subtree pointer of the non-leaf node is the same as the number of keywords

3. The subtree pointer P [I] of a non-leaf node points to a subtree whose key value belongs to [K [I], K [itree 1]) (B-tree is an open interval)

5. Add a chain pointer to all leaf nodes

6. All keywords appear in the leaf node.

Such as: (Mitte3)

The search of B + is basically the same as that of B-tree, except that the B + tree is hit only when it reaches the leaf node (B-tree can be hit on non-leaf node), and its performance is also equivalent to doing a binary search in the full set of keywords.

Features of B+:

1. All keywords appear in the linked list of leaf nodes (dense index), and the keywords in the linked list happen to be ordered

two。 It is impossible to hit at a non-leaf node.

3. A non-leaf node is equivalent to an index (sparse index) of a leaf node, and a leaf node is a data layer that stores (keywords) data.

4. More suitable for file indexing system

Index in mysql

B+Tree is widely used as index in MySQL, but the implementation is different according to clustered index and non-clustered index.

Clustering index

The so-called clustering index means that the main index file and the data file are the same file, and the cluster index is mainly used in the Innodb storage engine. In this index implementation, the data on the leaf node of B+Tree is the data itself, and key is the primary key. If it is a general index, data points to the corresponding primary index, as shown in the following figure:

Add a pointer to the adjacent leaf node in each leaf node of the B+Tree to form a B+Tree with sequential access pointers. The purpose of this optimization is to improve the performance of interval access. For example, in figure 4, if you want to query all data records whose key is from 18 to 49, when 18 is found, you can access all data nodes at once simply by traversing the node and pointer order, which greatly mentions the efficiency of interval query.

Non-clustered cable

Non-clustered index refers to the data on the leaf node of B+Tree, which is not the data itself, but the address where the data is stored. The primary index is no different from the secondary index, except that the key in the primary index must be unique. Mainly used in MyISAM storage engine, as shown below:

A non-clustered index has one more IO operation to read data than a clustered index, so the lookup performance is poor.

Comparison between Myisam index and InnoDB index

MyisAM supports full-text indexing (FULLTEXT) and compressed indexing, but InnoDB does not.

InnoDB supports transactions, but MyisAM does not

The MyisAM stores data sequentially, the index leaf node stores the corresponding data row address, and the secondary index is much the same as the primary key index; the InnoDB primary key node stores the data row at the same time, and other secondary indexes save the values of the primary key index.

MyisAM key value separation, index loading memory (key_buffer_size), data cache depending on operating system; InnoDB key value saved together, index and data loaded into InnoDB buffer pool; MyisAM primary key (unique) index is stored in ascending order, InnoDB is not necessarily

The cardinality of the MyisAM index (which can be seen by the Cardinality,show index command) is accurate, and InnoDB is the estimate. Here is the knowledge of information statistics. MyisAM statistics are saved on disk and updated in the alter table or Analyze table operation, while InnoDB estimates are saved in the cache when the table is first opened

MyisAM uses incremental storage when dealing with string indexing. For example, if the first index is' preform', the second is' preformence', then the second save is'7. This obvious advantage is to shorten the index, but the disadvantage is that it does not support reverse order extraction of the index. It must be traversed sequentially to get the index.

Why choose B+/-Tree?

Generally speaking, the index itself is too large to be stored in memory, so the index is often stored on disk in the form of an index file. In this way, the disk Imax O consumption will be generated in the index search process, which is several orders of magnitude higher than the memory access. Therefore, the most important index to evaluate the quality of a data structure as an index is the progressive complexity of the number of disk Imax O operations in the search process. In other words, the structure of the index should minimize the number of access to disk Imando O during the lookup process.

To put it simply, memory is made up of a series of storage units, each of which stores data of a fixed size and has a unique address. When the memory needs to be read, the address signal is put on the address bus and transmitted to the memory, the memory parses the signal and locates it to the memory unit, and then the data on the memory unit is put on the data bus and transmitted back.

When writing memory, the data and unit address to be written by the system are put on the data bus and address bus respectively, and the memory reads the contents of the two buses and does the corresponding write operation.

Memory access efficiency is related to the number of times, reading A data first or then reading A data will not affect the access efficiency. On the other hand, disk access is not the same, and disk Imax O involves mechanical operation. A disk is made up of circular disks of the same size and coaxial, and the disk can be rotated (each disk must be rotated at the same time). There is a magnetic head bracket on one side of the disk, and a set of magnetic heads are fixed by the magnetic head bracket, and each head is responsible for accessing the contents of a disk. The magnetic head does not move and the disk rotates, but the magnetic arm can move back and forth to read data on different tracks. A track is a series of concentric rings (such as the icon red circle) that are divided around the disk. The track is divided into small segments, called sectors, which is the smallest storage unit of the disk.

When the disk is read, the system transmits the logical address of the data to the disk, and the control circuit of the disk will resolve the physical address, that is, which track and which sector. So the head needs to move back and forth to the corresponding track, and the time consumed is called seek time, and then the disk rotates to transfer the corresponding sector under the head, and the time consumed is called rotation time. Therefore, the appropriate operation sequence and data storage can reduce the seek time and rotation time.

To minimize Icano operations, disk reads are pre-read every time, and the size is usually an integral multiple of the page. Even if you only need to read one byte, the disk reads a page of data (usually 4K) and puts it into memory, which exchanges data with the disk in pages. Because the locality principle holds that usually when a data is used, the data near it will be used immediately.

B-Tree: if you need to access 4 nodes for one search, and the database system designer uses the disk pre-read principle to design the size of the node to one page, then it only takes one Imax O operation to read a node. To complete this retrieval operation, you need a maximum of 3 iThink O (root node resident memory). The smaller the data record is, the more data is stored in each node, the smaller the height of the tree is, the less the Icano operation is, and the retrieval efficiency is improved.

B+Tree: only key is stored in non-leaf nodes, which greatly reduces the size of non-leaf nodes, so that each node can store more records, the tree is shorter, and there are fewer operations. So B+Tree has better performance.

InnoDB and Myisam

There are six major differences between the two:

MyISAMInnoDB

The difference in composition: each MyISAM is stored as three files on disk. The name of the first file starts with the name of the table, and the extension indicates the file type.

The .frm file stores the table definition.

The extension of the data file is .MYD (MYData).

The extension of the index file is .myi (MYIndex). Disk-based resources are InnoDB tablespace data files and its log files. The size of InnoDB tables is only limited by the size of operating system files, which is generally 2GB.

Transaction processing: MyISAM type tables emphasize performance, and their execution is faster than InnoDB type, but do not provide transaction support InnoDB provides transaction support transactions, foreign keys and other advanced database functions

SELECT

UPDATE

INSERT

Delete

If you perform a lot of SELECT,MyISAM is a better choice 1. If your data does a lot of INSERT or UPDATE, you should use the InnoDB table for performance reasons

When 2.DELETE FROM table, InnoDB does not re-establish the table, but deletes it row by row.

3.LOAD TABLE FROM MASTER operation does not work on InnoDB. The solution is to change InnoDB table to MyISAM table first, and then to InnoDB table after importing data, but it does not apply to tables that use additional InnoDB features (such as foreign keys).

The operation on AUTO_INCREMENT is the internal processing of one AUTO_INCREMEN column per table.

MyISAM automatically updates this column for INSERT and UPDATE. This makes the AUTO_INCREMENT column faster (at least 10%). After the value at the top of the sequence is deleted, it can no longer be used. When the AUTO_INCREMENT column is defined as the last column of a multi-column index, a value deleted from the top of the sequence can be reused.

The AUTO_INCREMENT value can be reset with ALTER TABLE or myisamch

For fields of type AUTO_INCREMENT, the InnoDB must contain an index with only that field, but in the MyISAM table, a join index can be established with other fields

Better and faster auto_increment processing

If you specify an AUTO_INCREMENT column for a table, the InnoDB table handle in the data dictionary contains a counter called the auto-growth counter, which is used to assign a new value to the column.

The automatic growth counter is stored only in main memory, not on disk

For the algorithm implementation of the calculator, please refer to the

AUTO_ index lists how it works in InnoDB

The specific number of rows of the table

Select count (*) from table,MyISAM simply reads out the saved rows. Note that when the count (*) statement contains the where condition, the operation of the two tables is the same. The specific number of rows of the table is not saved in InnoDB. That is to say, when select count (*) from table is executed, InnoDB scans the whole table to calculate how many rows there are.

Lock

Watch lock

Provides row locks (locking on row level) and unlocked reads consistent with Oracle types (non-locking read in

SELECTs), in addition, the row lock of the InnoDB table is not absolute. If the MySQL cannot determine the range to be scanned when holding a SQL statement, the InnoDB table will also lock the whole table, such as how the update table set num=1 where name like "% aaa%" MySQL storage engine MyISAM and InnoDB choose

MySQL has a variety of storage engines, each of which has its own advantages and disadvantages, which can be selected and used: MyISAM, InnoDB, MERGE, MEMORY (HEAP), BDB (BerkeleyDB), EXAMPLE, FEDERATED, ARCHIVE, CSV, BLACKHOLE.

Although MyISAM and InnoDB are not the only storage engines in MySQL, they are commonly used. There may be webmasters who have not paid attention to MySQL's storage engine, but in fact, storage engine is also a major point in database design, so which storage engine should blog system use?

Let's take a look at the differences between the two storage engines.

First, InnoDB supports transactions, but MyISAM does not, which is very important. Transactions are an advanced way of processing, such as in some column additions and deletions, as long as any error can be rolled back, but MyISAM is not.

2. MyISAM is suitable for query and insert-based applications, and InnoDB is suitable for frequent modifications and applications involving high security.

3. InnoDB supports foreign keys, but MyISAM does not.

Since MySQL5.5.5, InnoDB is the default engine

5. InnoDB does not support indexes of type FULLTEXT

6. The number of rows of the table is not saved in InnoDB, such as select count (*) from table, InnoDB needs to scan the entire table to calculate the number of rows, but MyISAM can simply read out the number of saved rows. Note that MyISAM also needs to scan the entire table when the count (*) statement contains where conditions

For self-growing fields, the InnoDB must contain an index with only that field, but a federated index can be established with other fields in the MyISAM table

8. When emptying the entire table, InnoDB is deleted row by row, which is very slow. MyISAM rebuilds the table

9. InnoDB supports row locks (or locking entire tables in some cases, such as update table set axiom 1 where user like'% lee%').

Through the above nine differences, combined with the characteristics of personal blog, the personal blog system is recommended to use MyISAM, because the main operation in the blog is to read and write, there are few chain operations. So choose MyISAM engine to make your blog open page more efficient than InnoDB engine blog, of course, it is only personal advice, most blogs still choose carefully according to the actual situation.

About MyISAM and InnoDB choosing to use:

MYISAM and INNODB are two storage engines provided by the Mysql database. It can be said that each has its own advantages and disadvantages. INNODB supports some advanced features of relational databases, such as transaction capabilities and row-level locks, which MYISAM does not support. MYISAM has better performance and takes up less storage space. Therefore, the choice of storage engine depends on the specific application.

If your application must use transactions, there is no doubt that you should choose the INNODB engine. Note, however, that INNODB's row-level locks are conditional. When the primary key is not used in the where condition, the whole table is still locked. Delete statements such as DELETE FROM mytable.

If your application requires high query performance, you should use MYISAM. MYISAM indexes are separate from data, and their indexes are compressed, making better use of memory. Therefore, its query performance is obviously better than that of INNODB. The compressed index can also save some disk space. MYISAM has the function of full-text indexing, which can greatly optimize the efficiency of LIKE queries.

Some people say that MYISAM can only be used for small applications, but this is just a prejudice. If the amount of data is large, this needs to be solved by upgrading the architecture, such as sub-table and sub-database, rather than relying solely on the storage engine.

There are other theories:

Now generally choose innodb, mainly myisam full table lock, read-write serial problems, parallel efficiency locking table, inefficient myisam generally will not be selected for read-write-intensive applications.

About the default storage engine for Mysql databases:

MyISAM and InnoDB are two storage engines of MySQL. If it is installed by default, it should be InnoDB. You can find default-storage-engine=INNODB; in the my.ini file. Of course, you can specify the appropriate storage engine when you create the table. The corresponding information can be seen through show create table xx.

Comparison of InnoDB and MyISAM in Mysql MyISAM:

Each MyISAM is stored as three files on disk. The name of the first file starts with the name of the table, and the extension indicates the file type. The .frm file stores the table definition. The data file has the extension .MYD (MYData).

MyISAM tables can be compressed, and they support full-text search. Transactions are not supported, and foreign keys are not supported. If things are rolled back, it will result in incomplete rollback, which is not atomic. Table locking is performed during updata, and the amount of concurrency is relatively small. It is better to perform a large amount of SELECT,MyISAM.

The index of MyISAM is separated from the data, and the index is compressed, so the memory usage increases a lot. Can load more indexes, and Innodb is that indexes and data are tightly bundled, no compression is used, which makes Innodb larger than MyISAM.

Indexes, not data, are cached in MyISAM. On the other hand, data is cached in InnoDB. Relatively speaking, the larger the server memory, the greater the advantage of InnoDB.

Advantages: query data is relatively fast, suitable for a large number of select, can be full-text index.

Disadvantages: do not support transactions, do not support foreign keys, concurrency is small, not suitable for a large number of update

InnoDB:

This type is transaction safe. . It has the same features as the BDB type, and they also support foreign keys. InnoDB tables are very fast. It has richer features than BDB, so if you need a transaction-secure storage engine, it is recommended. Row locking is performed on the table in update, and the concurrency is relatively large. If your data does a lot of INSERT or UPDATE, you should use the InnoDB table for performance reasons.

Advantages: support transactions, support foreign keys, large amount of concurrency, suitable for a large number of update

Disadvantages: query data is relatively fast, not suitable for a large number of select

For InnoDB-type tables that support things, the main reason for affecting speed is that the default setting of AUTOCOMMIT is on, and the program does not explicitly call BEGIN to start a transaction, resulting in automatic Commit for each insert, seriously affecting the speed. You can call begin before executing the sql, and multiple sql forms a single thing (even if the autocommit is open), which will greatly improve performance.

The basic difference is that the MyISAM type does not support advanced processing such as transactions, while the InnoDB type does.

Tables of type MyISAM emphasize performance and perform several times faster than type InnoDB, but do not provide transaction support, while InnoDB provides advanced database functions such as transaction support already external keys.

Spring transactions:

The transaction attributes of the Propagation class in Spring are explained in detail:

PROPAGATION_REQUIRED: supports the current transaction. If there is no current transaction, create a new transaction. This is the most common choice.

PROPAGATION_SUPPORTS: supports the current transaction, and if there is no transaction, it is executed in a non-transactional manner.

PROPAGATION_MANDATORY: supports the current transaction and throws an exception if there is no current transaction.

PROPAGATION_REQUIRES_NEW: create a new transaction and suspend the current transaction if it exists.

PROPAGATION_NOT_SUPPORTED: performs the operation in a non-transactional manner, suspending the current transaction if there is a current transaction.

PROPAGATION_NEVER: executes in a non-transactional manner, throwing an exception if a transaction currently exists.

PROPAGATION_NESTED: supports the current transaction, executes a nested transaction if the current transaction exists, and creates a new transaction if there is no current transaction.

Thing timeout setting:

@ Transactional (timeout=30) / / 30 seconds by default

Transaction isolation level:

@ Transactional (isolation = Isolation.READ_UNCOMMITTED)

Reading uncommitted data (dirty reading, non-repeatable reading) is basically not used

@ Transactional (isolation = Isolation.READ_COMMITTED)

Read submitted data (unrepeatable and phantom readings occur)

@ Transactional (isolation = Isolation.REPEATABLE_READ)

Repeatable (there will be hallucinations)

@ Transactional (isolation = Isolation.SERIALIZABLE)

Serialization

MYSQL: default to REPEATABLE_READ level

SQLSERVER: default is READ_COMMITTED

Dirty read: one transaction reads uncommitted update data from another transaction

Non-repeatable: in the same transaction, the results returned by reading the same data multiple times are different, in other words

Subsequent reads can read updated data that has been committed by another transaction. On the contrary, "readable" is repeated multiple times in the same transaction

When reading data, it is guaranteed that the read data is the same, that is, subsequent reads cannot read the updated data that has been committed by another transaction.

Illusion: one transaction reads insert data committed by another transaction

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.