1. Why use auto-increment columns as primary keys
1. If we define a PRIMARY KEY, InnoDB will select the PRIMARY KEY as the clustered index. If a primary key is not explicitly defined, InnoDB selects the first unique index that does not contain a NULL value as the primary key index. If there is no such unique index, then InnoDB chooses the built-in 6-byte ROWID as the implicit clustered index. (The ROWID increases with the primary key as the row record is written. This ROWID is not as referential as Oracle’s ROWID and is implicit.) 2, if the table on the primary key, and then insert a new record, record will be added to the current order index node position follow-up (primary key inserted into the highest performance, because is the order of), when a page write full, will automatically create a new page 3, if the use of the primary key (if the id number or student id, etc.), Since each into the approximation of random key values, so every time a new record to be inserted into the existing index page to somewhere in the middle The MySQL had to in order to insert a new record to the appropriate location and mobile data, or even the target page might have been back to disk and clear it from the cache, at this time again want to read from the disk back, it cost a lot of, The frequent movement and paging resulted in a large number of fragments, resulting in a less compact index structure that would have to be reconstructed by OPTIMIZE TABLE and populate the page.
Why does adding an index to a field improve query efficiency
1. The value of the field to which the index is added is stored on the leaf node of B + Tree constructed by the index and stored by sorting; 2. If relevant queries come in, the data page will be obtained through B + Tree created by the index (B + Tree is compatible with binary search method, and the corresponding data page can be found only after several IO consumption); 3. After finding the data page, load the page into the buffer pool, and then get specific data from the data page in memory;
B+ tree index and hash index
B+ tree is a balanced multi-branch tree structure. The height difference from the root node to each leaf node is no more than 1, and there are Pointers linked to each other among nodes of the same level in an orderly manner, as shown in the figure below:
Hash index is to use a certain hash algorithm to convert the key value into a new hash value. When searching, it does not need to search step by step from root node to leaf node like B+ tree, but only needs a hash algorithm, which is out of order, as shown in the figure below:
4. Advantages of Hash Indexes
Equivalent query, hash index has absolute advantage (provided: there are not a large number of duplicate keys, if a large number of duplicate keys, hash index efficiency is very low because of the so-called hash collision problem.) ; In MySQL, adaptive hash indexes are turned on in the buffer pool.
What is your understanding of MySQL clustered index
1. Selection of clustered index: the primary key created by display will be selected first as the clustered index; If not, select the first non-empty unique index as the clustered index. If none is available, an instance-level ROWID is created as the clustered index. 2, the characteristics of clustered index: the order of the key values determines the physical order of the table data rows; A leaf node holds an entire row of data; Only one clustered index can be created for a table.
How does MySQL optimize the write operation of common indexes
For example, the insert operation of an ordinary index, the insert of the leaf node of the non-clustered index is no longer sequential, then it is necessary to visit the non-clustered index page discreetly, and the performance of the insert operation is reduced due to the existence of random reads. MySQL uses the Insert Buffer feature to optimize writes to normal indexes. For the insert operation of the non-clustered index, it is not directly inserted into the index page every time, but first determines whether the inserted non-clustered index page is in the buffer pool, if so, it is directly inserted. If not, put it in an INSERT BUFFER object first. The INSERT Buffer and secondary index page child nodes are then merged with a frequency and frequency that usually merges multiple inserts into a single operation (because it is on a single index page), which greatly improves performance for non-clustered index inserts.
Seven, talk about the use of prefix index matters needing attention
When building a prefix index, the most important thing is to define a good length and grasp the degree, which can save the use of memory and reduce the extra query cost. Prefix indexes have an effect on overwrite indexes
8. What do indexes affect
Indexing is related to MySQL performance and affects many aspects; The most important points are: improve read performance; Reduce lock waits and deadlocks In master-slave replication, SQL threads use indexes for playback to reduce master-slave delay.
Under what circumstances should we build no or less indexes
If the number of records in the table is very small, it is not recommended to create an index on the table. If the number of records in the table is very small, it is not recommended to create an index on the table. If the number of records in the table is very small, it is not recommended to create an index on the table. However, if the level is above 10,000, it is recommended to create an index
10. How to restrict a certain IP user under a certain IP segment from being allowed to log in
Using the characteristics of accurate IP matching of users; If the original user ergou@’192.168.58.%’, create a new user, such as ergou@’192.168.58.51′, and reset the password; This will cause the user to log in through user ergou, which will match to ergou@’192.168.58.51′.
Delete is different from truncate
1, DELETE is a logical delete, delete data by line, low efficiency, support rollback; Truncate is a physical delete that frees up space. It is fast and does not support rollback. The purge thread does not allow the purge thread to delete the delete
Why is it recommended to use NOT NULL constraints on fields in production
2. Null values require more storage space and add one more byte (after the index is established on the NULL column) 3. The result is inaccurate. Count (a field) does not contain a null value
When is an index not used
EXPLAIN SELECT * FROM ORDERS WHERE O_CUSTKEY = O_CUSTKEY +1; SELECT * FROM ORDERS WHERE O_CUSTKEY = O_CUSTKEY +1; EXPLAIN SELECT * FROM ORDERS WHERE O_CUSTKEY = CEIL (O_CUSTKEY); Explain select * from emp where ename=007; If the character set of the association condition is different, the column type that does not use the association condition is different, and the index is not used. Select * from emp; . Explain select * from emp where empno > 7000; . The index itself is invalid. Explain select * from emp where ename like ‘%s’; . SELECT * FROM EMP WHERE EMPNO NOT IN (111,9999); SELECT * FROM EMP WHERE EMPNO NOT IN (111,9999); . ! = explain select * from emp where empno! = 9999;
Sixteen, four levels of isolation
If you read read-uncommitted RU, you will not generate a dirty read. If you read read-uncommitted RU, you will generate a non-repeatable read. Repeatable-read RR does not generate dirty reads and does not generate non-repeatable reads. Will create a phantom (but InnoDB prevents phantom by default, using locks); Serializeable, will not produce dirty reads, will not produce non-repeatable reads; There is no illusion; Perfectly transactional, but with minimal performance
In the 17th and MVVC
MVCC (Multi-version Concurrency Control) The biggest benefit of MVCC is that read does not lock, read and write do not conflict. In OLTP applications, it is very important to read and write without conflict, which greatly increases the system’s concurrency performance. At present, almost all RDBMS support MVCC. MVCC consists of the transaction ID in the data page, the rollback pointer + the undo log, and the read view
XVIII. Two-phase commit of transactions
If you have a prepare event, you will write the xid to the redo log. If you have a prepare event, you will write the xid to the redo log
A binlog is a redo log
InnoDB storage engine is unique to the redo log, binlog is not separate storage engine ② record content is different, redo log is a physical logic log, records the page changes process; The binlog is a logical log that records the contents of the specific operations of the transaction. The redo log is written first, and then the binlog is written again. How to quickly migrate a large table to another database instance? How to implement transactions? How to prevent phantasmaging at the RR isolation level? How to avoid deadlock in production 24, under different isolation levels, different index under innodb row locking granularity is what kind of principle of twenty-five, mysqldump backup 26, the innodb storage engine conflicts of row locks When 27, MySQL backup, How to make a consistent backup? The principle of master-slave replication? The bottleneck of master-slave replication architecture? How to reduce master-slave replication delay How to revert a database to any given day? What are the improvements in master-slave replication in 5.7 and 8.0? Why is DML slower