©2020-2024 ioDraw All rights reserved
<>1. Introduce them separately innodb and myisam?
* MyIASM yes MySQL Default engine , However, it does not provide support for database transactions , Foreign keys and foreign keys are not supported , So when INSERT( insert ) or
UPDATE( to update ) Write on data requires locking the entire table , The efficiency will be lower .ISAM Read operations are fast , And does not occupy a lot of memory and storage resources .
* InnoDB The underlying storage structure is B+ tree , B Each node of the tree corresponds to innodb One of the page,page The size is fixed , It is generally set to
16k. The key value is only the leaf node , The leaf node contains completion data . Generally, it is recommended to add, delete and modify the data entry innodb.
<>2.innodb Data structure of
innodb The data structure of b+ tree , Mainly with b The difference between trees is that there are more horizontal pointers in leaf nodes , It's easier to traverse the data .
<>3.Innodb and myisam Comparison of ?
* innodb Support transaction ,myisam Transaction is not supported .
* innodb Foreign key support ,myisam Foreign keys are not supported .
* myisam Only table level locks are supported ,innodb His locks are more finely grained , Support row level lock , But if innodb Not sure when scanning sql The scope of , Table level locks are also used .
* When a large number of queries are executed myisam Better execution efficiency , It is best to execute a large number of add and delete statements innodb.
<>4. The concept of transaction ?ACID Let's introduce the characteristics of ? Isolation level of database ?
*
affair (TRANSACTION) Represents a complete and indivisible business , Bulk DML Statements succeed or fail at the same time . Atomicity is indivisible
Basic unit , It can be seen as a complete event , Generally, a transaction can correspond to a complete business process .
*
Atomicity （Atomicity） All operations in the entire transaction , It has to be done as a unit （ Or cancel it all ）.
uniformity （Consistency） Before and after the transaction begins and ends , The database is in a consistent state .
** Isolation （Isolation）** One transaction does not affect the operation of other transactions .
persistence （Durability） After the transaction completes , Changes made by the transaction to the database are persisted in the database , It will not be rolled back .
*
** Read uncommitted （READ UMCOMMITTED）** Allows one transaction to see uncommitted changes from other transactions .
** Read submitted （READ COMMITTED）** Allows a transaction to see only changes that have been committed by other transactions , Uncommitted changes are not visible . He is sql
Server,orical Database default level .
** Repeatable （REPEATABLE READ）** Make sure that if the same is executed twice in a transaction SELECT
sentence , All get the same results , Whether other transactions commit these changes or not . He is mysql Default isolation level .
Serialization （SERIALIZABLE） The isolation level is InnoDB Default settings for . Completely isolate one transaction from others .
<>5. Three paradigms of database ?
Data storage performance of database design , And developers have a great deal to do with data . So build a scientific , The standard database needs to meet some specifications to optimize the data storage mode . In relational databases, these specifications can be called paradigms .
*
** There is a primary key , It has atomicity , Fields are indivisible .** That is, all field values are indecomposable atomic values , For example, we often use the address field in the database , But if you need to use a province , city , We need to take it apart , The province , Cities in a separate column .
* ** Make sure that each column in the table is related to the primary key , The non primary key columns in the table do not have partial dependency on the primary key .** Each table is required to describe only one
Something . For example, an order has multiple items , We need to split the order table from the product list , Join representation through an intermediate table .
*
** Make sure that each column is directly related to the primary key column , Not indirectly .** For example, when designing an order data table , The customer number can be used as a foreign key to establish a corresponding relationship with the order table . It is not possible to add additional information about the customer to the order form （ Like names , Company, etc ） Fields for .
<>6. How to separate read and write database ? How to split a table ?
*
First of all, the master Database records database operation log to binlog, Then turn it on from the server io Threads will synchronize binlog From the server to the server relaylog inside （ From the server's cache ）, Finally, start from the server sql Thread relaylog Operation inside sql Execute from the slave library , In this way, read-write separation is realized .
* There are two kinds of table splitting , Vertical split and horizontal split
* Split Vertically ： Vertical split is to split the table according to the field （ For example, the order table has two fields: price and inventory , hold The inventory is kept in a separate table , Separate the inventory into a separate table ）
*
split horizon ： Horizontal split is a matching split according to some rules of the table's fields （ For example, according to the table ID To split ,id by 1-1000 Is a table ,1001-2000 Split this way for the second table ）
<>7. Optimization of query statements ?
* Avoid use != or ＜＞,IS NULL or IS NOT NULL,IN ,NOT IN And so on ;
* Fair use EXISTS,NOT EXISTS clause ;
* Be able to use BETWEEN Don't use it IN;
* Be able to use DISTINCT You don't have to GROUP BY;
* In the program, if you insert multiple data into the same table at one time , It will be more efficient to put it into a statement ;
<>8. What is the index ?
An index is actually a structure that sorts the contents of columns in a database , It's a bit like pinyin or stroke for locating page numbers in our dictionary , It can help us get the content of the database quickly , It's a data structure .
<>9. Advantages and disadvantages of index ?
*
advantage ：
Greatly speed up the speed of data retrieval ;
Create unique index , Ensure the uniqueness of each row data in the database table ;
Acceleration table and connection between tables ;
When using grouping and sorting clauses for data retrieval , It can significantly reduce grouping and sorting time in queries ;
*
shortcoming ：
It takes time to create and maintain indexes , This time increases with the amount of data ;
The index needs to take up physical space ;
When the data in the table is added , When deleting and modifying , Indexes should also be maintained dynamically , Reduced data maintenance speed ;
<>10. When is the index used ? Under what circumstances will it not be used ?
*
Usage ：
Create index in primary key column ;
Join columns to create index when multiple tables join ;
where The column of the clause query ;
It takes a lot of time GROUP BY and ORDER BY Column of ;
*
Not used ：
Do not use indexes for data with a large number of duplicate values （ For example, gender , Commonly used 0 1）;
The table contains less data and does not use indexes ;
Indexing is not recommended for fields that need to be updated frequently ;
<>11. Index invalidation ?
* like with % start , index is not valid ; When like No prefix %, The suffix is % Time , The index is valid ;
* or The index is not used before and after the statement . When or Only one of the left and right query fields is an index , The index is invalid , Only when or When the left and right query fields are indexes , Will take effect ;
* Implicit conversion of data type . as varchar Without single quotation marks, it may be automatically converted to int type , Invalid index , Generate full table scan ;
* Use on index fields not,<>,!=. The not equal operator will never use an index , It will only generate a full scan of the table . optimization method ： key<>0 Change to key>0
or key<0;
<>12. Optimization strategy of index ?
* Leftmost prefix matching principle ;
* Index must be created for external inspection of primary key ;
* Yes where,group by,order by The columns that appear in are indexed ;
* Don't create too many indexes ,
Trade off the number of indexes and DML Relationship between ,DML That is, insertion , Delete data operation . There is a trade-off , The purpose of building index is to improve the query efficiency , But there are too many indexes , Will affect insertion , Speed of data deletion ;
<>13. Left connection , Right connection , The difference of inner connection ? Under what circumstances are they used ?
* If you use the left connection , The main table is on the left , On the right is the slave table , When the right table does not match the data, it will be displayed as null;
* If you use the right connection , The main table is on the right , On the left is the slave table , When the left table does not match the data, it will be displayed as null;
* Internal connection is also a combination of them , Only the data that matches the left and right tables will be displayed ;
* The usage summary is ： Do you want to show it all , What data needs to be displayed , Do you want to display qualified data ;
Technology