Please put away this database interview summary for spring recruitment

Village rain 2021-01-26 17:44:48
away database interview summary spring


Village Yuyao
  • 1. Database foundation
    • 1.1 The advantages of using a database
    • 1.2 What is? MySQL
    • 1.3 Three paradigms of database
    • 1.4 MySQL The permission table in
  • 2. data type
  • 3. Storage engine
    • 3.1 Common storage engines
    • 3.2 InnoDB vs MyISAM
    • 3.3 How to choose a storage engine
  • 4. stored procedure
    • 4.1 Definition
    • 4.2 Advantages and disadvantages
  • 5. Business
    • 5.1 Transaction definition
    • 5.2 The transaction 4 Big characteristic
    • 5.3 Transaction isolation level
  • 6. lock
    • 6.1 Definition
    • 6.2 The relationship between transaction isolation level and lock
    • 6.3 Definition and solution of deadlock in database
    • 6.4 Optimism lock & Pessimistic locking
  • 7. Indexes
    • 7.1 Definition
    • 7.2 The basic principle
    • 7.3 Index advantages and disadvantages
    • 7.4 Index type
    • 7.5 Index algorithm
    • 7.6 Principles for designing and creating indexes
    • 7.7 B+ Tree index and Hash Index underlying implementation
  • 8. View
    • 8.1 View definition
    • 8.2 View features
    • 8.3 Advantages and disadvantages of view
  • 9. Optimize
    • 9.1 Big watch optimization
  • 10. other
    • 10.1 sql Inject
    • 10.2 NULL And empty string
    • 10.3 How to create users and authorize
    • 10.4 How to delete a table

1. Database foundation

1.1 The advantages of using a database

In the beginning , We keep the data in Memory in , This will ensure that we are very Fast access , But once the power goes out , The data is lost , Can't permanently save . So we put the data in the file in , In this way, we will The ability to store data permanently , But it's going to be frequent every time IO operation , It's much slower than memory , And the query operation is not convenient . therefore , We moved to database Storage , In this way, not only It can store data permanently , And query management is more efficient and convenient .

1.2 What is? MySQL

MySQL Is a relational database management system , Free open source , And easy to expand , Is one of the most popular relational database management systems , stay Java Web The application is very extensive . Its default port is 3306.

1.3 Three paradigms of database

  1. First normal form : Attribute atomicity

The basic paradigm , If the database table All field values are indecomposable atomic values , The first paradigm ;

  1. Second normal form : Record uniqueness , Make sure that every column in the table is related to the primary key

On the basis of the first paradigm , You need to make sure that every column in the database table is related to the primary key , You can't just relate to a part of the primary key ( It's mainly for joint primary key ). namely In a database table , Only one kind of data can be saved in a table , You can't save multiple data in the same database table ;

  1. Third normal form : Field redundancy , Make sure that each column is directly related to the primary key column , There is no delivery dependency

On the basis of the second paradigm , Make sure that Each column of data is directly related to the primary key , Independent of other non primary keys , That is, any field cannot be derived from other fields ;

1.4 MySQL The permission table in

MySQL Access to the database is controlled by the permission table , Generally stored in mysql In the table , from mysql_install_db Script to initialize , Include respectively :

  • user: Record the user account information allowed to connect to the server , Permissions are global ;
  • db: Record the operation permissions of each account on different databases ;
  • table_priv: Record operation permissions at the data table level ;
  • columns_priv: Record data column level operation permissions ;
  • host: coordination db Table provides further control over database level operation permissions on a given host , Authority is not subject to GRANT and REVOKE Influence ;

2. data type

Mainly can be divided into 5 Large type , Under the big type, there are different subtypes :

classification

Type the name

explain

Integer types

tinyInt

Very small integers (8 Bit binary )

smallint

Small integers (16 Bit binary )

mediumint

An integer of medium size (24 Bit binary )

int(integer)

An integer of normal size (32 Bit binary )

Real number type

float

Single-precision floating-point

double

Double precision floating point

decimal(m,d)

Compress the exact number of points

Enumeration type

enum

Date and time type

year

YYYY 1901~2155

time

HH:MM:SS -838:59:59~838:59:59

date

YYYY-MM-DD 1000-01-01~9999-12-3

datetime

YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00~ 9999-12-31 23:59:59

timestamp

YYYY-MM-DD HH:MM:SS 19700101 00:00:01 UTC~2038-01-19 03:14:07UTC

String type

CHAR(M)

M by 0~255 Integer between

VARCHAR(M)

M by 0~65535 Integer between

TINYBLOB

Permissible length 0~255 byte

BLOB

Permissible length 0~65535 byte

MEDIUMBLOB

Permissible length 0~167772150 byte

LONGBLOB

Permissible length 0~4294967295 byte

TINYTEXT

Permissible length 0~255 byte

TEXT

Permissible length 0~65535 byte

MEDIUMTEXT

Permissible length 0~167772150 byte

LONGTEXT

Permissible length 0~4294967295 byte

VARBINARY(M)

Permissible length 0~M Variable length string of bytes

BINARY(M)

Permissible length 0~M Fixed length string of bytes

3. Storage engine

To see MySQL The engine provided in , You can use the following command :

show engines;

3.1 Common storage engines

engine

explain

InnoDB

Provide access to the database ACID Transaction support , At the same time, row level lock and foreign key constraints are provided , It's designed to deal with big data

MyIASM

Default engine , No transaction support , Row level locks and foreign keys are also not supported

MEMORY

All data is stored in memory , Fast access , But the security is low

3.2 InnoDB vs MyISAM

  • InnoDB Of 4 Big characteristic
  1. Insert buffer (Insert Buffer)
  2. The two time to write (Double Write)
  3. adaptive hash index (Ahi)
  4. read-ahead (Read Ahead)
  • Difference between them

Contrast item

MyISAM

Innodb

Storage structure

Each table is stored in three files :1. .frm- Table definition 2. .MYD(MYData)- Data files 3. .MYI(MYIndex)- Index file

All tables are stored in the same data file ( It could be multiple files , Or a separate tablespace file ), The size is only limited by the size of the operating system file , It's usually 2GB

Storage space

Can be compressed , Less storage space

Need more memory and storage , A dedicated buffer pool will be established in main memory for caching data and indexes

Portability 、 Backup and recovery

Data is stored as files , It's convenient in cross platform data transfer , During backup and recovery, you can operate on a table alone

The free solution can be to copy data files 、 Backup binlog, Or use mysqldump, When the data reaches a certain amount ( Dozens of G) It's relatively painful when

File format

Data and index are stored separately , data .MYD, Indexes .MYI

Data and indexes are centrally stored ,.ibd

Record storage order

Save in record insertion order

Insert by primary key size

Foreign keys

I won't support it

Support

Business

I won't support it

Support ( Default REPEATABLE-READ)

Lock support

Table level locking

Row level locking 、 Table level locking , Small locking force and high concurrency

MVVC Support

I won't support it

Support

Crash fix

I won't support it

Support

Hash index

I won't support it

Support

Full-text index

Support

I won't support it

Query performance

Better

Add, delete and modify performance

Better

The amount of statistical data

faster , Internal maintenance of a counter , It can be called directly .

How index is implemented

B+ Tree index ,myisam It's a pile of watches

B+ Tree index ,Innodb It's the index organization table

The main differences between the two are as follows :

  1. InnoDB An index is a clustered index , and MyISAM It's a non clustered index ;
  2. InnoDB The leaf node of the primary key index stores row data , Therefore, the primary key index is efficient ;MyISAM The leaf node of the index stores the row data address , One more addressing operation is needed to get the data ;
  3. InnoDB The leaf node of the non primary key index stores the primary key and other indexed column data , Therefore, it is more efficient to overlay index when querying ;

3.3 How to choose a storage engine

  • MyISAM: default MySQL Plug in storage engine , fit Mainly read write insertion , yes Web、 One of the most commonly used engines in data warehouse and other application environments ;
  • InnoDB: For transactional applications , If The operation frequency of updating and deleting is also high , Or to Ensure data integrity , Support High concurrency 、 Foreign keys and transactions, etc ;
  • Memory: Save all data in RAM in , When you need to quickly find references and other similar data , Can provide faster access to ;
  • Merge: allow MySQL DBA Or developers will equate a series of MyISAM Tables are logically grouped together and referenced as an object , Suitable for data warehouse, etc VLDB Environmental Science ;

4. stored procedure

4.1 Definition

A stored procedure is a programmable function , Create and save in the database , from SQL Statement and some special control structures . Advantage is Allow modular design , That is to say, create it all at once , Multiple calls . It's a precompiled SQL sentence , When multiple execution is required SQL When the sentence is , Using stored procedures is better than just SQL More efficient sentences .

4.2 Advantages and disadvantages

  • advantage
  1. Because it's precompiled , So the execution efficiency is high ;
  2. The code of the stored procedure is directly in the database , Call... Directly through the stored procedure name , Can reduce network communication ;
  3. High safety , The execution of a stored procedure requires a user with certain permissions ;
  4. Be able to reuse , Improve development efficiency ;
  • shortcoming
  1. Debugging difficulty
  2. It's difficult to transplant
  3. Recompile problem , Because stored procedures are compiled before running , So if an object with a reference relationship changes , Affected stored procedures 、 The package needs to be recompiled
  4. If you use a lot of stored procedures in a program , When it is delivered for use, the data structure will change with the change of user requirements , At this time, the cost of system maintenance is high

5. Business

5.1 Transaction definition

A transaction is an indivisible sequence of database operations , It is also the basic unit of database concurrency control , The execution result must make the database switch from one consistency state to another . A transaction is a logical set of operations , Or both , Either not . Transactions can ensure that all changes are saved when the database commits work , Or all changes will not be saved . That is, a transaction is a logical set of operations , Or both , Either not .

5.2 The transaction 4 Big characteristic

Relational databases need to follow ACID The rules :

  1. Atomicity (Atomicity)

Atomicity is an inseparable unit of work in the whole database transaction , Only all database operations in the transaction are successful , It's the success of the whole business , If one of the links fails , So even if it has been successfully implemented SQL The statement must also undo , Roll back to the state before the transaction . That is, atomicity can guarantee The action is either complete , Or it doesn't work at all . Transaction is the smallest execution unit , Division is not allowed .

  1. Uniformity (Consistency)

It refers to the process of changing a database from one consistency state to another . Before and after the start of the transaction , The integrity constraints of the database are not broken . Before and after transaction execution , Data can be consistent , Multiple transactions read the same data .

  1. Isolation, (Isolation)

When accessing the database concurrently , Isolation requires that the operation objects of each read-write transaction can be separated from each other , That is, a user's transaction is not interfered by other transactions , The database between concurrent transactions is independent ;

  1. persistence (Durability)

Indicates that once a transaction is committed , The result is permanent , Its changes to the data in the database are persistent , Even if the database fails, it should not be affected ;

5.3 Transaction isolation level

5.3.1 Dirty reading 、 Fantasy reading & It can't be read repeatedly

Before you know the level of transaction isolation , Let's take a look at these concepts :

  1. Dirty reading (Dirty Read)

Indicates that a transaction has updated a piece of data , Another transaction reads the same data at this time . After the current transaction revocation operation , This will cause the data read by the latter transaction to be incorrect .

  1. Fantasy reading (Phantom Read)

The amount of data in two queries of a transaction is inconsistent , Suppose a transaction queries several columns of data , At the same time, another transaction queries new data at this time , Then the query transaction is in the subsequent query , You will find that the data is richer than the original query data .

  1. It can't be read repeatedly (Non-repeatable Read)

The data of two queries in a transaction is inconsistent , It is possible that a transaction updating the original data was inserted during the two queries .

Be careful : The difference between nonrepeatable reading and unreal reading is :

The point of unrepeatable reading is to modify , For example, read a record many times and find that the values of some columns have been modified , and The focus of unreal reading is to add or delete , For example, read a record many times and find that the number of records increases or decreases .

5.3.2 Isolation level

SQL The standard defines 4 Isolation levels , The isolation levels from low to high are :

  1. READ-UNCOMMITTED( Read uncommitted )

Lowest isolation level , Allow read of uncommitted data changes , May lead to dirty reading 、 Phantom or unrepeatable reading .

  1. READ-COMMITTED( Read committed )

Allow to read data submitted by concurrent transactions , Can prevent dirty reading , But it may lead to unreadable or non repeatable reading .

  1. REPEATABLE-READ( Repeatable )

Multiple reads of the same field are consistent , Unless the data is modified by the transaction itself , It can prevent dirty reading and non repeatable reading , But it can lead to unreal reading .

  1. SERIALIZABLE( Serializable )

Highest isolation level , Completely obey ACID Isolation level , All transactions are executed one by one , In this way, there is no interference between transactions , Can prevent dirty reading 、 Unreal and unrepeatable reading

Isolation level

Dirty reading

It can't be read repeatedly

Fantasy reading

READ-UNCOMMITTED

READ-COMMITTED

REPEATABLE-READ

SERIALIZABLE

6. lock

6.1 Definition

When there are concurrent transactions in the database , It may cause data inconsistency in the database , In this case, in order to ensure the access order , We need to use the lock mechanism .

Locks are designed to support concurrent access to shared resources , Provide data integrity and consistency , So as to ensure that in the case of high concurrency , There is no problem accessing the database ;

6.2 The relationship between transaction isolation level and lock

Isolation level

lock

READ-UNCOMMITTED

Read without a shared lock

READ-COMMITTED

Read operations require shared locks , Release after statement execution

REPEATABLE-READ

Read operations require shared locks , Release after transaction execution

SERIALIZABLE

Lock the entire range of keys , And hold the lock all the time , Until the transaction is complete

6.3 Definition and solution of deadlock in database

  • Definition

The so-called deadlock , It means that two or more processes occupy each other on the same resource , And ask to lock the other party's resources , Which leads to a vicious circle .

  • resolvent
  1. If different programs access multiple tables concurrently , Try to agree to access the table in the same order , So as to greatly reduce the probability of deadlock ;
  2. In the same business , Try to lock all the resources you need at one time , Reduce the probability of deadlock ;
  3. For the business part prone to deadlock , Try using upgrade to lock granularity ;

6.4 Optimism lock & Pessimistic locking

  • Definition

Concurrency control can ensure that when multiple transactions access the same data in the database at the same time, it does not destroy the isolation and unity of transactions , And the unity of database , Concurrency control can be divided into optimistic locking ( Optimistic concurrency control ) And pessimistic lock ( Pessimistic Concurrency ).

  1. Optimism lock

It is assumed that there will be no concurrent conflicts , Data integrity violation when checking only when committing transactions . Lock transactions when modifying data , adopt version To lock in the same way , Generally, version number mechanism or CAS Algorithm to achieve ;

  1. Pessimistic locking

Concurrent conflicts are assumed , Block all operations that may violate data integrity . Lock the transaction after querying the data , Until the transaction is committed , Generally use the lock mechanism in the database to achieve ;

  • Use scenarios

Optimism lock Suitable for Read operations are frequent , But there are fewer write operations The situation of , It's a scene where conflicts rarely happen , This saves the cost of locking , At the same time, increase the throughput of the system ;

Pessimistic locking Suitable for Write operations are frequent , But read operations are less The situation of , That is, the scene of frequent conflicts ;

7. Indexes

7.1 Definition

The so-called index , It's a special kind of document , Contains a reference pointer to all records in the data table . It's a data structure , Database index is a sort data structure in database management system , Can assist in quick query 、 Update data in database tables , Use at the same time B Trees and their varieties B+ Tree . In popular terms, it is equivalent to the contents in our daily dictionary , It can help us quickly find the words we want .

7.2 The basic principle

The final directory to use an index is to quickly find records with specific values , If there is no index , When we need to find a value , You can only traverse the entire table to find , In this way, the search efficiency will be greatly reduced .

The principle of indexing is also very simple , namely Turn unordered data into ordered queries , The steps to query data according to the index are as follows :

  1. Sort the contents of the indexed column
  2. Generate an inverted list of sorting results
  3. Put a data address chain on the inverted content
  4. In the query , First get the contents of the inverted list , Then take out the data address chain , So we can get the data

7.3 Index advantages and disadvantages

  • advantage
  1. The first point is without a doubt Speed up data retrieval ;
  2. The second point is By using index , The ability to use optimized hiders in the query process , Improve performance .
  • shortcoming
  1. In terms of time : Although it can speed up Retrieval , But creating and maintaining indexes also takes time , And with more data , Indexes also need to be maintained dynamically , This will reduce the increase in / Delete / The implementation efficiency of the reform ;
  2. In space : Indexes also need to occupy independent space , So it will take up more physical space with the increase of data ;

7.4 Index type

7.4.1 Logical angle

From the logical point of view, index can be divided into 4 Species index , Namely :

  1. primary key

Duplicate columns are not allowed , It is not allowed to be NULL, There can only be one primary key in a table ;

  1. unique index

Duplicate columns are not allowed , Allow for NULL value , A table allows multiple columns to create unique indexes , You can create a unique index in two ways :

  • Create unique index ALTER TABLE table_name ADD UNIQUE(column)
  • Create a unique composite index ALTER TABLE table_name ADD UNIQUE(column1, column2);
  1. General index

The most basic index type , There is no limit to uniqueness , Allow for NULL value , Create a unique index in two ways :

  • Create a normal index ALTER TABLE table_name ADD INDEX index_name (column);
  • Create common index combinations ALTER TABLE table_name ADD INDEX index_name (column1, column2);
  1. Full-text index

A technology that is also being used in search engines , adopt ALTER TABLE table_name ADD FULLTEXT (column) To create a full-text index ;

  1. Composite index

Multiple column values form an index , Dedicated to combined search , It is more efficient than index merging ;

7.4.2 Physical storage perspective

  1. Clustered index (clustered index)
  2. Nonclustered indexes (non-clustered index)

7.4.3 Data structure perspective

  1. BTREE
  2. HASH
  3. FULLTEXT
  4. R-Tree

7.5 Index algorithm

Common indexing algorithms are Hash Algorithm and B Tree algorithm , Two more algorithms are introduced respectively :

  1. B+ Tree algorithm

Most commonly used MySQL Algorithm , It's also MySQL Default algorithm , It can be used for comparison operators (=、>、<、between etc. ), It can also be used for like The operator , As long as the query condition is a Constants that don't start with wildcards . The underlying implementation is Multiway balanced search tree , Every query starts from the root node , Find the leaf node to get the key value , Then, according to the query, judge whether to return the table to query the data .

  1. Hash Algorithm

Hash Algorithm index can only be used for peer-to-peer comparison (=、>=、<=), And it's not like B Tree index needs to go from root node to branch node , Finally, we can read and write the model text to the page node for many times , It only needs to locate data once , So the retrieval efficiency is much higher than B Tree index . The bottom layer is Hash surface , When searching , Call once Hash Function to get the corresponding key value , And then query back to the table to get the actual data .

  1. Both comparisons
  • Hash It's faster for index to do equivalent query , But you can't do range queries ;
  • Hash Index does not support sorting with index ;
  • Hash Index does not support fuzzy query and leftmost prefix matching of multi column index , because Hash The result of a function is unpredictable ;
  • Hash Index can't avoid query data back to table , but B+ Trees under certain conditions ( Cluster index 、 Overwrite index, etc ) You just need to complete the query through the index ;
  • Hash Index is faster in equivalent query , But not stable , Performance is unpredictable ; but B+ The query efficiency of tree is stable , All queries are from root node to leaf node , And the height of the tree is lower ;

7.6 Principles for designing and creating indexes

7.6.1 Design principles

  1. Choose a unique index

Uniqueness the value of an index is unique , The index can be used to determine a record more quickly ;

  1. Order for frequent need 、 Index fields for grouping and combining operations

For people who often need ORDER BY、GROUP BY、DISTINCT、UNION Fields waiting for operation , A lot of time is wasted in sorting , So we can index it , Avoid sort operations ;

  1. Index fields that are often used as query criteria

If a field is often used as a query condition , The query speed of this field will affect the query speed of the whole table , You can index this field , So as to improve the query speed of the whole table ;

  1. Limit the number of indexes

The more indexes, the better , Each index needs to take up physical space , More indexes take up more physical space , It is very troublesome to reconstruct and update the index when modifying the table ;

  1. Try to use indexes with less data

If the index value is long , Query speed will also be affected ;

  1. Use short index , Try to use prefixes to index

If an index field has a long value , It's best to index with the prefix of the value ;

  1. Delete indexes that are no longer or rarely used

The data in the table has been updated a lot , Or the way data is used is changed , Some of the original indexes may no longer need , At this point, you need to delete these indexes , Reduce the impact of indexes on update operations ;

7.6.2 Creating principles

Using index can improve retrieval efficiency to a certain extent , But it can't be used without limitation , When you create an index , It's better to meet the following principles :

  1. Leftmost prefix matching principle
  2. Index is created only for frequently queried fields
  3. Frequently updated fields are not suitable for creating indexes
  4. Fields with low discrimination are not suitable for indexing
  5. Expand the index as much as possible , Instead of creating a new index
  6. The data column with foreign key must be indexed
  7. It is seldom involved in the query , Moreover, the fields with more duplicate values do not need to be indexed
  8. about text、image、bit Do not index fields of type

7.7 B+ Tree index and Hash Index underlying implementation

  1. Hash Indexes

Hash The bottom layer of the index is actually Hash surface , When searching , Call once Hash Function to get the key value of the response , Then query back to the table to get the data in the database ;

  1. B+ Tree index

B+ At the bottom of the tree is a multi-channel balanced search tree , Every query starts from the root node , Find the leaf node and get the key value to query , Then, according to the query, judge whether to return the table to query the data ;

  1. Hash Index and B+ The difference between trees
  • Hash It's faster for index to do equivalent query , But you can't do a range query . because Hash In the index hash() Function is indexed , The index order cannot be consistent with the original order , Range query... Is not supported ; and B+ All nodes of the tree follow ( The left node is smaller than the parent node , The parent node is smaller than the right node ), Natural support range query ;
  • Hash Index does not support sorting with index ;
  • Hash Index does not support fuzzy query and leftmost prefix matching of multi column index , The principle is also because hash() The unpredictability of a function ;
  • Hash Index must query back to table at any time , but B+ When a tree meets certain conditions, it can complete the query only by index ;
  • Hash Index is faster than query , But it's extremely unstable , Performance is unpredictable , But when there are a lot of repetitions in a key value , It's going to happen Hash Collision , At this point, efficiency can be very low ; and B+ The query efficiency of the tree is relatively stable , All queries are from root node to leaf node , And the height of the tree is lower ;

In view of the above differences , So in most cases , Directly choose B+ Tree index can get stable and better query speed , Instead of using Hash Indexes ;

8. View

8.1 View definition

To improve complexity SQL The reusability of statements and the security of table operations ,MySQL The database management system provides views .

The essence of view is A virtual table , It doesn't exist physically , The content is similar to a real table , Contains a series of named column and row data . But the view does not exist in the database as a stored data value , Row and column data is derived from the underlying table referenced by the query defining the view , And it is generated dynamically when the view is referenced ;

The operation of view generally includes the following four parts :

  • Create view
  • View view
  • Delete view
  • Modify the view

8.2 View features

  1. The columns of a view can come from different tables , It's a new relationship established in the logical sense by the abstraction of a table ;
  2. Views have basic tables ( Real table ) The resulting table ( Virtual table );
  3. The creation and deletion of views will not affect the basic table ;
  4. Update view content ( add to 、 Delete and modify ) Will directly affect the basic table ;
  5. When views come from multiple base tables , Adding and deleting data is not allowed ;

8.3 Advantages and disadvantages of view

8.3.1 advantage

  1. Query simplification , Views can simplify user operations , What you see is what you get ;
  2. Data security , Views enable users to view the same data from multiple perspectives , Users can only query or modify the data they can see , Can provide security protection for confidential data ;
  3. Logical data independence , Views provide a certain degree of logical independence for refactoring databases , Shielding the impact of changes in the structure of real tables ;

8.3.2 shortcoming

  1. Relatively poor performance , Simple queries can also become very complex ;
  2. Modify restrictions , When trying to modify the view , It must be translated into a change that compares some rows in this table . For complex aggregate views , It's basically impossible to change ;

9. Optimize

9.1 Big watch optimization

When MySQL When the number of single table records is too large , Database CURD Performance will be significantly reduced , At this time, the following optimization measures can be taken :

  1. Limit the range of data

Be sure to prohibit query statements without any restrictions on data range conditions , The entire database is queried , Extremely inefficient ;

  1. read / Write separation

The most classic database splitting scheme , The main database is responsible for writing , Read from the library ;

  1. Vertical zones

That is, split according to the relevance of the data table in the database , In short, it refers to the splitting of data tables , Divide a table with more columns into multiple tables . This makes The column data gets smaller , It reduces the number of reads in query Block Count , Less I/O frequency . meanwhile , Vertical partitioning also simplifies the structure of tables , Easy to maintain . however , Vertical splitting also has some disadvantages . First of all, splitting will Causes redundancy in the primary key , At this point, you need to manage redundant columns , At the same time, it will cause Join operation , It can be done in the application layer Join To solve . Besides , Splitting also makes transactions more complex .

  1. Horizontal zoning

Keep the data table structure the same , Store data slices through a certain strategy . In this way, each piece of data is scattered into different tables or libraries , So as to achieve the purpose of distributed , And through horizontal segmentation can support a very large amount of data .

Horizontal splitting is to split the rows of a data table , It can Support a very large amount of data storage , There is also less transformation of application end , however Fragmentation is hard to solve , Cross node Join Poor performance , Complex logic .

Generally speaking, the two common schemes of database partition operation are as follows :

  • Client agent : Sharding logic at the application end , Packaged in jar In bag , By modifying or encapsulating JDBC Layer to achieve .
  • Middleware agent : Add a proxy layer between applications and data , Fragment logic is maintained in middleware services .

10. other

10.1 sql Inject

The parameters passed in by the user match sql The grammar of , So as to destroy the original sql Structural Semantics , So as to achieve the attack effect ;

10.2 NULL And empty string

NULL It's not worth it , It's not empty string , If only ‘’ ( Two single quotes , There are no characters in the middle ), about NOT NULL Columns are allowed , An empty string is also a valid value ;

Right NULL Judge , You need to use IS NULL perhaps IS NOT NULL;

10.3 How to create users and authorize

  1. Create user
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
  1. to grant authorization
GRANT privileges ON databasename.tablename TO 'username'@'host';

10.4 How to delete a table

The way

explain

example

delete

Delete table data only , Support conditional filtering , Support rollback , Log , So it's slower

delete from table_name

truncate

Just delete all data , Conditional filtering is not supported , Rollback is not supported , Do not log , Efficiency is higher than delete

truncate table table_name

drop

Delete table data and delete table structure , Release all the space occupied by the table , Deletion is the most efficient

drop table table_name

This article is from WeChat official account. - Village Yuyao (cunyu1943)

The source and reprint of the original text are detailed in the text , If there is any infringement , Please contact the yunjia_community@tencent.com Delete .

Original publication time : 2021-01-15

Participation of this paper Tencent cloud media sharing plan , You are welcome to join us , share .

版权声明
本文为[Village rain]所创,转载请带上原文链接,感谢
https://javamana.com/2021/01/20210126170156392a.html

  1. 【计算机网络 12(1),尚学堂马士兵Java视频教程
  2. 【程序猿历程,史上最全的Java面试题集锦在这里
  3. 【程序猿历程(1),Javaweb视频教程百度云
  4. Notes on MySQL 45 lectures (1-7)
  5. [computer network 12 (1), Shang Xuetang Ma soldier java video tutorial
  6. The most complete collection of Java interview questions in history is here
  7. [process of program ape (1), JavaWeb video tutorial, baidu cloud
  8. Notes on MySQL 45 lectures (1-7)
  9. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  10. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  11. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  12. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  13. 【递归,Java传智播客笔记
  14. [recursion, Java intelligence podcast notes
  15. [adhere to painting for 386 days] the beginning of spring of 24 solar terms
  16. K8S系列第八篇(Service、EndPoints以及高可用kubeadm部署)
  17. K8s Series Part 8 (service, endpoints and high availability kubeadm deployment)
  18. 【重识 HTML (3),350道Java面试真题分享
  19. 【重识 HTML (2),Java并发编程必会的多线程你竟然还不会
  20. 【重识 HTML (1),二本Java小菜鸟4面字节跳动被秒成渣渣
  21. [re recognize HTML (3) and share 350 real Java interview questions
  22. [re recognize HTML (2). Multithreading is a must for Java Concurrent Programming. How dare you not
  23. [re recognize HTML (1), two Java rookies' 4-sided bytes beat and become slag in seconds
  24. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  25. RPC 1: how to develop RPC framework from scratch
  26. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  27. RPC 1: how to develop RPC framework from scratch
  28. 一次性捋清楚吧,对乱糟糟的,Spring事务扩展机制
  29. 一文彻底弄懂如何选择抽象类还是接口,连续四年百度Java岗必问面试题
  30. Redis常用命令
  31. 一双拖鞋引发的血案,狂神说Java系列笔记
  32. 一、mysql基础安装
  33. 一位程序员的独白:尽管我一生坎坷,Java框架面试基础
  34. Clear it all at once. For the messy, spring transaction extension mechanism
  35. A thorough understanding of how to choose abstract classes or interfaces, baidu Java post must ask interview questions for four consecutive years
  36. Redis common commands
  37. A pair of slippers triggered the murder, crazy God said java series notes
  38. 1、 MySQL basic installation
  39. Monologue of a programmer: despite my ups and downs in my life, Java framework is the foundation of interview
  40. 【大厂面试】三面三问Spring循环依赖,请一定要把这篇看完(建议收藏)
  41. 一线互联网企业中,springboot入门项目
  42. 一篇文带你入门SSM框架Spring开发,帮你快速拿Offer
  43. 【面试资料】Java全集、微服务、大数据、数据结构与算法、机器学习知识最全总结,283页pdf
  44. 【leetcode刷题】24.数组中重复的数字——Java版
  45. 【leetcode刷题】23.对称二叉树——Java版
  46. 【leetcode刷题】22.二叉树的中序遍历——Java版
  47. 【leetcode刷题】21.三数之和——Java版
  48. 【leetcode刷题】20.最长回文子串——Java版
  49. 【leetcode刷题】19.回文链表——Java版
  50. 【leetcode刷题】18.反转链表——Java版
  51. 【leetcode刷题】17.相交链表——Java&python版
  52. 【leetcode刷题】16.环形链表——Java版
  53. 【leetcode刷题】15.汉明距离——Java版
  54. 【leetcode刷题】14.找到所有数组中消失的数字——Java版
  55. 【leetcode刷题】13.比特位计数——Java版
  56. oracle控制用户权限命令
  57. 三年Java开发,继阿里,鲁班二期Java架构师
  58. Oracle必须要启动的服务
  59. 万字长文!深入剖析HashMap,Java基础笔试题大全带答案
  60. 一问Kafka就心慌?我却凭着这份,图灵学院vip课程百度云