High Performance MySQL Reading Notes
High Performance MySQL is a classic book. From the application layer to the database to the hardware platform, all the tuning techniques and common problems are mentioned. Various concepts and techniques of the database are commonly used, such as indexes, partitions, and Sharding. However, you still have to learn how to improve it.
Chapter 1: MySQL Architecture and History
1.1 Transaction Isolation Level
1.2 Implicit and Explicit Locking
This makes the transaction highly concurrent, because unlocking does not have to happen at the end of the transaction. Its
The problem of deadlock is not solved.Because it is not required in order in the locking phase. For example, if two transactions apply for the and B locks respectively, and then apply for the lock of the other party, the transaction enters the Deadlock State.
1.3 Multiversion Concurrency Control
Innodb only uses the MVCC name to provide non-blocking read operations.
Chapter 4: Optimizing Schema and Data Types 4.1 Choosing Optimal Data Types
Smaller is usually better (the Smaller the better): Because it takes up less disk space, memory, and CPU cache, the Smaller the size usually means the faster.
Simple is good (Simple is good): Because the character set and sorting rule (Collation) Make the string more complex, we should use Integer and other built-in types instead of strings to Save the Date time or IP address.
Avoid NULL if possible (Avoid NULL as much as possible): MySQL has special processing logic for NULL, so NULL will make the index, index statistics, and Value Comparison more complex.
4.2 Using ENUM Instead Of A String Type
4.3 Cons of A Normalized Schema
JOIN makes it impossible for us to complete sorting and filtering through an index at the same time. Change to non-Standardization => SELECT .. FROM user_message WHERE account_type = 'premium 'order BY published desc limit 10 then the indexes on (account_type, published) can efficiently complete the task!
These technologies are essentially designed to accelerate query operations, but the cost is to slow down write operations and increase development complexity.
4.5 Materialized Views
4.6 Counter Tables
Ps: If you need to refresh the counter every day, add the Time column: create table daily_hit_counter (day date not null, slot tinyint unsigned not null primary key, cnt int unsigned not null, primary key (day, slot) ENGINE = InnoDB;
Pss: if you do not want to INSERT initial data every day, use the following SQL: mysql> INSERT INTO daily_hit_counter (day, slot, cnt) VALUES (CURRENT_DATE, RAND () * 100, 1) on duplicate key update cnt = cnt + 1;
Psss: to reduce the number of counters to save space, you can execute a scheduled task to merge all records to Slot 0:
5.1 B-Tree Family
The core of various variants is the internal node degree (such as memory-based T-tree and disk-based B-tree), storage usage (B-tree and B + tree), and so on.
Three disk IO operations and three memory lookup operations. The file name search in the memory is an ordered table structure, which can be used to improve efficiency. IO operations are the deciding factor that affects the efficiency of the entire B-tree search. Of course, if we use the disk storage structure of the balanced binary tree for search, the disk is 4 times, up to 5 times, and the more files, b-tree performs less I/O operations on disks than Binary Trees.
