1. Concurrency of things? Transaction isolation levels, what problems does each level cause? What is the default level of MySQL? Things should be completely isolated from each other, in order to avoid the problems caused by concurrent things, however, it will have great impact on performance, because things must run in order, in the actual development, in order to improve performance, things will run at a lower isolation level, things attribute to specify the isolation level could be isolated by things.
Concurrency issues for transactions
1) Dirty read: Transaction A reads the data updated by transaction B, and then B rolls back the data, so the data A reads is dirty.
2) Non-repeatable read: transaction A reads the same data for many times, and transaction B updates and commits the data during the process of multiple reads by transaction A. As A result, when transaction A reads the same data for many times, the data read by this transaction will be inconsistent.
3) Illusory reading: Illusory reading solves the problem of non-repeated reading and ensures that the query results in the same transaction are the state at the beginning of the transaction (consistency).
Transaction isolation level
1) Read uncommitted: Another transaction has modified data, but has not committed it, and the SELECT in this transaction will read dirty reads of the uncommitted data.
2) Non-repeatable read: transaction A reads the same data for many times, and transaction B updates and commits the data during the process of multiple reads by transaction A. As A result, when transaction A reads the same data for many times, the data read by this transaction will be inconsistent.
3) Repeatable read: In the same transaction, the result of the SELECT operation is the state at the start of the transaction. Therefore, the same SELECT operation will read the same result. However, there will be illusions.
4) Serialization: The highest isolation level at which no exceptions are generated. Concurrent transactions, as if transactions are executed sequentially one by one.
MySQL default transaction isolation level is REPEATable -read.
What are the differences between MySQL storage engine MylSAM and InnoDB? 1) InnoDB supports transactions, MylSAM does not, which is very important. Transaction is a kind of advanced processing, such as in some series of deletion and revision as long as which error can be rolled back to restore, and MylSAM can not.
2) MylSAM is suitable for queries and insert-oriented applications. InnoDB is suitable for frequently modified and safe applications.
InnoDB supports foreign keys, MylSAM does not.
4) InnoDB is the default engine since MySQL5.5.5.
5) InnoDB does not support FULLTEXT indexes.
6) InnoDB does not store the number of rows in a table, such as selectCount ()fromtable, InnoDB needs to scan the entire table to calculate the number of rows, but MylSAM simply reads the saved number of rows. Note that MylSAM also needs to scan the entire table when the count() statement contains the WHERE condition.
7) For auto-growing fields, InnoDB must contain an index for that field only, but in MylSAM tables, it is possible to create a joint index with other fields.
8) InnoDB does not re-create tables when deleting FROM tables, but deletes them row by row, which is very slow. MylSAM will rebuild the table.
Update table set a=1 where user like %lee% ‘
3. What is a temporary table? When will temporary tables be dropped? DROP TEMPORARY TABLE IF EXISTS temp_tb;
2) Temporary tables are only visible in the current connection. When a connection is closed, MySQL automatically drops the table and frees all space. Therefore, you can create temporary tables with the same name in different joins and manipulate temporary tables belonging to this join.
Create a TEMPORARY table. Create a TEMPORARY table. Create a TEMPORARY table.
CREATE TEMPORARY TABLE tmp\_table (
Copy the codeNAME VARCHAR (10) NOTNULL,
Copy the codetime date NOT NULL
Copy the codeselect \* from tmp\_table;
Copy the code4, About the two storage engines provided by MySQL database, MylSAM and InnoDB? 1) INNODB will support some advanced features of relational database, such as transaction function and row-level lock, MylSAM does not support.
2) MylSAM has better performance and takes up less storage space, so the storage engine you choose depends on your application.
3) If your application must use transactions, you should definitely choose INNODB engine. Note, however, that INNODB’s row-level locking is conditional. If the WHERE condition does not use a primary key, the table is locked. DELETE statements such as DELETE FROM mytable.
4) If your application has high query performance requirements, use MylSAM. MylSAM is separate from data and its indexes are compressed to make better use of memory. So its query performance is significantly better than INNODB’s. Compressed indexes can also save some disk space. MylSAM has full-text indexing capabilities, which can greatly optimize the efficiency of LIKE queries.
MySQL > select * from B+Tree; 1) The particularity of Hash index structure, its retrieval efficiency is very high, index retrieval can be located at one time;
2) B+ tree indexes need to go from root to branch to finally access as many IO visits as they do to page nodes;
Why use B+ tree index instead of Hash index? A Hash index
1) Hash indexes can only be used for “=”, “IN” and “” queries, not for range queries, because the size relationship of Hash values processed by the corresponding Hash algorithm cannot be guaranteed to be exactly the same as before the Hash operation.
2) Hash indexes cannot be used to avoid sorting because the size of the Hash value is not necessarily the same as the key before the Hash operation;
3) Hash indexes cannot be queried using partial index keys. For a combined index, the combined index keys are used to compute the Hash value together, not separately. Therefore, the Hash index cannot be used when the first one or more index keys are used to query the Hash index.
4) Hash indexes cannot avoid table scanning at any time. Because different index keys have the same Hash value, the query cannot be directly completed from the Hash index even if the number of records of data that meet the Hash key value is selected.
5) Hash indexes do not necessarily perform better than B+ tree indexes when a large number of Hash values are equal. Storage Hash conflict
B + Tree index:
1) In MySQL, only the HEAP/MEMORY engine displays support for Hash indexes.
2) The common InnoDB engine uses B+ tree index by default, which will monitor the index usage on the table in real time. If it is considered that the establishment of hash index can improve query efficiency, If a table is almost entirely in the buffer pool, MySQL will use the index key prefix to create a hash index. Then establishing a hash index can speed up the equivalent query
The obvious differences between a B+ tree index and a hash index are:
1) If it is equivalent query, hash index obviously has absolute advantage, because it only needs to go through one algorithm to find the corresponding key value; The premise is that the keys are unique. If the key value is not unique, we need to find the position of the tart first, and then scan backward according to the S table, until we find the corresponding song;
If it is a range query search, at this time the hash element citation is useless, because the original is ordered key values, after the hash algorithm, may become discontinuous, there is no way to use the index to complete the range query search; 2) hash index can not use the index to complete the sort, and like, XXX %, such a partial fuzzy query (this partial fuzzy query, in fact, is also a range query);
3) Hash indexes also do not support left-most matching rules for multi-column joint indexes;
4) The keyword retrieval efficiency of B+ tree index is relatively average, unlike that of B tree, which fluctuates greatly. In the case of a large number of repeated key values, the efficiency of hash index is also extremely low, because of the so-called hash collision problem.
5) In most scenarios, there will be range query, sorting, grouping and other query features, using B+ tree index will be ok
What is the difference between a clustered index and a non-clustered index? Fundamental difference
The fundamental difference between a clustered index and a non-clustered index is whether the table records are sorted in the same order as the index.
Clustered index
1) The order of clustered index table records is consistent with the order of the index (so the query efficiency is fast, as long as the first index value record is found, the rest of the continuous records are stored in the same physical continuity. The disadvantage of clustered index correspondence is that it is slow to modify because the data pages are reordered when the records are inserted to ensure that the physical and index order of the records in the table is consistent.
2) Clustered index is similar to xinhua dictionary using pinyin to search for Chinese characters. Pinyin search table is arranged in order of a to Z, just like the same logical order in physical order.
Nonclustered index
1) The non-clustered index establishes the logical order of the records in the table, but the physical records and indexes are not necessarily the same. Both indexes use B+ tree structure. The leaf layer of the non-clustered index does not overlap with the actual data page, but uses the way that the leaf layer contains a pointer to the records in the table in the data page. Non-clustered indexes have many levels and do not cause data rearrangement. The non-clustered index is similar to the query of Chinese characters in Xinhua Dictionary by partial radical. The retrieval table may be arranged in accordance with horizontal, vertical and apostrophe, but because the text is a~ Z pinyin order, it is similar to the logical address and physical address does not correspond.
2) The case is suitable for grouping, large number of different values, frequently updated columns, these situations are not suitable for clustered index.
MySQL > select * from ‘MySQL’; 1) slowQuerylog Slow query enable status.
Slowquerylog_file Slowquerylog_file Slowquerylog_file Slowquerylog_file slowquerylog_file slowquerylog_file slowquerylog_file slowquerylog_file slowquerylog_file slowquerylog_file slowquerylog_file slowquerylog_file slowquerylog_file slowquerylog_file slowquerylog_file slowquerylog_file
3) LongQueryTime The number of seconds before the query is recorded.
8. What are the solutions for MySQL high concurrency environment? 1) Database and table: horizontal database and table are distributed from single point to multi-point database to reduce the pressure of single point database.
2) Cluster solution: solve the single point DB access problem caused by DB outage.
3) Read/write separation strategy: greatly improves the speed and concurrency of Read data in applications. Unable to resolve high write pressures.
9. From the analysis of innoDB index structure, why can’t index key length be too long? If a key is too long, the number of keys that can be stored on a page decreases, which indirectly leads to an increase in the number of pages in the index tree and index level, affecting the overall query change efficiency.
How to restore MySQL data to any point in time? Restore to any point in time based on the premise that periodic full backup and increased binlog backup are performed. Recovery to any point in time After full backup recovery, then play back the enhanced binlog until the specified point in time.
MYSQL > alter database master/slave delay 1) Semi-synchronous replication
Since MySQL5.5, MySQL has supported semi-synchronous replication. Semi-synchronous replication is between asynchronous replication and synchronous replication. The master database does not immediately return the results to the client after executing a transaction, but needs to wait for at least one secondary database to receive the results and write them to the relay log. Compared with asynchronous replication, semi-synchronous replication improves data security and causes a TCP/IP round-trip delay.
Syncbinlog = 1, 2) the main library configuration innodbflushlogattrxcommit = 1
The default value of syncbinlog is 0. MySQL does not synchronize binlogs to disk. The value is how many binlogs are written to disk.
Innodbflushlogattzxcommit 1 said every transaction commit or transaction outside south instructions are needed to flush log to the disk.
3) Optimize the network. Upgrade the Slave hardware configuration. Upgrade 5.7 Parallel Replication is used
12, a table has nearly ten million data,CRUD is relatively diffuse, how to optimize? 1) Table splitting can be done to reduce the number of single table fields and optimize the table structure.
2) Check the field order of the primary key index under the condition that the primary key is valid, so that the field order of the condition in the query statement is consistent with the field order of the primary key index.
3) Under the condition of single table, SQL statement, row index optimization, so as to improve the speed of data retrieval.
13, under high concurrency, how to safely modify the same row of data? 1) Pessimistic lock
2) FIFO (First Input, First Output) cache queue ideas
3) Use optimistic locks
What type of data structure is used by a relational database? 1) The data structure of the database includes nonlinear data structure, tree data structure and set data structure
2) Data structures are stored in two-dimensional tables (nonlinear data structures).
3) Two-dimensional tables and relationships
Relational – two-dimensional table; 4) B+Tree index — Tree data structure Hash index — set data structure
Note: Four basic data structures. Linear data structures (one-to-one relationships between elements) are subdivided into arrays, linked lists, queues, and stacks. , tree data structure (such as binary tree, balanced binary tree, B+ tree), set data structure, graph data structure 1) The index of data is implemented as B+Tree and hash index structure to store data.
2) B+Tree is a multi-path balanced query Tree, so its nodes are naturally ordered (the left child node is smaller than the parent node, and the parent node is smaller than the right child node), so there is no need to do full table scan for range query.
3) The underlying Hash index is a Hash table, which is a structure that stores data in key-value mode. Therefore, the storage relationship of multiple data has no sequential relationship at all.
I hope the above content can help you. Many PHPer will encounter some problems and bottlenecks when they are advanced, and they have no sense of direction when writing too many business codes. I have sorted out some information, including but not limited to: Distributed architecture, high scalability, high performance, high concurrency, server performance tuning, TP6, Laravel, Redis, Swoft, Kafka, Mysql optimization, shell scripting, Docker, microservices, Nginx, etc. Many knowledge points can be free to share with you