1, Say something MySQL The internal execution process of executing a query statement ?

* The client connects to the MySQL The server .
* After the connector permission is verified , First query whether there is query cache , If there is cache ( This statement has been executed before ) Directly return cached data , If there is no cache, enter the analyzer .
* The analyzer will analyze the syntax and morphology of query statements , judge SQL Whether the grammar is correct , If the query syntax is wrong, the error message will be directly returned to the client , If the syntax is correct, enter the optimizer .
* The optimizer optimizes query statements , For example, there are multiple indexes in a table , The optimizer will determine which index performs better .
* The optimizer enters the actuator after execution , The executor starts to execute statements for query and comparison , Until all data meeting the conditions are queried , Then return .
2,MySQL Tips “ This column does not exist ” Which node is it reported to ?

         This error is reported during the execution to the analyzer stage , because MySQL Will be checked in the analyzer phase SQL Correctness of statements .

3,MySQL What are the advantages and disadvantages of query caching ?

         The advantage is high efficiency , If there is already a cache, the result will be returned directly .

         The disadvantage of query cache is that the cache hit rate is relatively low due to too frequent invalidation , Any update table operation will empty the query cache , Therefore, the query cache is very easy to fail .

4, How to close MySQL Query cache function of ?

        MySQL Query cache is on by default , to configure querycachetype Parameter is DEMAND( Use on demand ) Turn off query cache ,MySQL 8.0
After that, the function of query cache was directly deleted .

5,MySQL What are the common engines of ?

        MySQL The commonly used engines are InnoDB,MyISAM,Memory etc. , from MySQL 5.5.5 Version start InnoDB
Becomes the default storage engine .

6,MySQL Can I set the database engine for the table level ? How to set ?

         sure .
         stay create table Used in statements engine= Engine name ( such as Memory) To set the storage engine of this table .
create table student( id int primary key auto_increment, username
varchar(120), age int ) ENGINE=Memory
7, Common storage engines InnoDB and MyISAM What's the difference? ?

        InnoDB and MyISAM The biggest difference is InnoDB Support transactions , and MyISAM Transactions are not supported .

      The differences are as follows :

*  InnoDB Support safe recovery after crash ,MyISAM Post crash safe recovery is not supported ;
* InnoDB Support row level lock ,MyISAM Row level lock is not supported , Only table lock is supported ;
* InnoDB Support foreign keys ,MyISAM Foreign keys are not supported ;
* MyISAM support FULLTEXT Full text index of type ,InnoDB I won't support it FULLTEXT Full text index of type , however InnoDB have access to
sphinx The plug-in supports full-text indexing , And the effect is better ;
* InnoDB Primary key query performance is higher than MyISAM.
8,InnoDB What are the characteristics ?

(1) Insert buffer (insert buffer):

         For inserts and updates of nonclustered indexes , Not every time it is directly inserted into the index page , Instead, first determine whether the inserted nonclustered index page is in the buffer pool , If in , Then insert directly , otherwise , Put it into an insert buffer first .

         It seems that the nonclustered index of cheating database has been inserted into the leaf node , Then perform the merge operation of inserting buffer and non clustered index leaf nodes at a certain frequency , At this time, multiple inserts can usually be merged into one operation , This greatly improves the performance of inserting and modifying nonclustered indexes .

(2) Write twice (double write):

         Write twice to InnoDB It brings reliability , It is mainly used to solve some write failures (partial page write).

        doublewrite It consists of two parts , Some are in memory doublewrite buffer , Size is
2M, The other part is continuous in the shared table space on the physical disk 128 Pages , That is, two areas , The size is also 2M.

When the job of buffer pool is refreshed , Do not write directly to the hard disk , But through memcpy Function copies dirty pages to memory first doublewrite buffer, Later passed
doublewrite buffer Write it twice more , Every write 1M To the physical disk of the shared tablespace , Then call immediately fsync function , Synchronize disk .

(3) adaptive hash index (adaptive hash index):

         because InnoDB I won't support it hash Indexes , But in some cases hash Efficient indexing , So it appeared adaptive hash index
function , InnoDB The storage engine monitors the lookup of the index on the table , If the establishment is observed hash When indexing can improve performance , Then it will be established automatically hash Indexes .

9, What are independent table spaces and shared table spaces ? What is the difference between them ?

         Shared tablespace : It refers to all table data of the database , Index files are all placed in one file , By default, the file path of this shared tablespace is data Under the directory .

         Independent tablespace : Each table will be stored as an independent file .

         The biggest difference between a shared table space and an independent table space is if the table is placed in a shared table space , Even if the table is deleted, the space will not be deleted , So the watch is still big , The independent table space will be cleared if the table is deleted .

10, How to set independent tablespaces ?

         Independent tablespaces are defined by parameters innodbfileper_table Controlled , Set it to ON Independent table space , from MySQL 5.6.6
After version , This value defaults to ON Yes .

11, Where is the structure information of the table ?

         The storage space occupied by the table structure definition is relatively small , stay MySQL 8 before , The definition information of table structure exists with .frm In the file with suffix , stay MySQL 8
after , It is allowed to store the definition information of the table structure in the system data table .

12, What is overlay index ?

         Just scan the index to get the desired data without returning to the table .

13, What call back table query ?

         After querying the primary key index in the general index , Go back to the process of searching the primary key index tree , We call it back table query .

14, If you put one InnoDB Delete the primary key of the table , Is there no primary key , There is no way to query the back table ?

         You can query back to the table , If the primary key is deleted , that InnoDB Will generate a length of 6 Byte rowid As primary key .

15, Execute a update After statement , I'll do it again hexdump Command direct view ibd Document content , Why didn't you see the data change ?

         Probably because update After statement execution ,InnoDB Just make sure you finish it redo log, Memory , You may not have time to write the data to disk .

16, What is the difference between a memory table and a temporary table ?

         Memory table , Refers to the use Memory Engine table , Table creation syntax is create table …
engine=memory. The data of this kind of table is stored in memory , It will be cleared when the system restarts , But the table structure is still there .

         cursor , Various engine types can be used . If used InnoDB Engine or MyISAM Temporary table of engine , When writing data, it is written to the disk .

17, What problems will concurrent transactions bring ?

         Dirty reading , Modification missing , Non repeatable , Unreal reading

18, What are dirty reading and unreal reading ?

         Dirty read refers to a transaction that reads uncommitted data from another transaction during processing ;

         Phantom reading refers to that the result sets returned by multiple queries in the same transaction are different ( For example, line records are added or reduced ).

19, Why does unreal reading appear ? What problems will fantasy reading bring ?

         Because row locks can only lock existing rows , There is no restriction on the operation of new insertion , So it is possible to produce unreal reading .

         The problems caused by unreal reading are as follows : Destruction of row lock semantics ; Destroy data consistency .

20, How to avoid unreal reading ?

        MySQL Storage engine InnoDB Isolation level RR Solved the unreal reading problem

21,VARCHAR and CHAR What is the difference between ? What are the applicable scenarios ?

        VARCHAR and CHAR The biggest difference is ,VARCHAR The length of is variable , and CHAR Is a fixed length .

        CHAR It is more suitable for short length fields and fixed length fields , Such as ID number , Mobile number, etc , Otherwise, it is suitable for use VARCHAR.

22,MySQL Which data type should be used to store the amount ? Why? ?

        MySQL The stored amount should be used decimal , Because if you store other data types , such as float There is a risk of data loss after the decimal point .

         of course , It can also be stored as int type , Before storage 19.38 Meta convert to 1938 branch , And then stored in the database

23,limit 10,2 What is the meaning of ?

         Before removal 10 After data , Query two pieces of information .

24,now() and current_date() What's the difference? ?

        now() Returns the current time, including date, hour, minute and second 2019-05-04 19:23:45

        current_date() Only return the current time 2019-05-04

25, How to calculate the total number of pieces by de duplication ?

         use distinct duplicate removal , use count Total number of Statistics

        select count(distinct f) from t

26, There are several ways to delete table data ? What's the difference between them ?

         There are two ways to delete data :delete and truncate, Their differences are as follows :

        delete Can add where Delete some data conditionally ,truncate Cannot add where Condition can only delete the whole table ;

        delete The deletion information of will be displayed in MySQL Recorded in the log of , and truncate The deletion information of is not recorded in MySQL In the log of , therefore detele
Information can be retrieved and truncate The information of cannot be retrieved ;

        truncate Because the log is not recorded, the execution efficiency ratio is delete fast .
delete from t where username='redis'; truncate table t;
27,MySQL Several fuzzy queries are supported in ? What's the difference between them ?

        MySQL Two fuzzy queries are supported in :regexp and like,like It is used for fuzzy matching of any multi character or any single character , and regexp
The matching method of regular expressions is supported , Offer ratio like More matching methods .

        regexp and like Examples of the use of are as follows :
select * from person where uname like '%SQL%'; select from person where uname
regexp '.SQL*.';
28,count(column) and count() What's the difference? ?**

        count(column) and count() The biggest difference is that the statistical results may be inconsistent ,count(column) Statistics will not count column values as null
Data of , and count() All information will be counted , So the final statistical results may be different .

29, What is a view ? How to create a view ?

         A view is a virtual table , It has the same function as the physical table , You can add views , change , Check operation . A view is usually a subset of rows or columns of a table or tables . The view creation script is as follows :
create view vname as select column_names from table_name where condition
30, What are the advantages of view ?

         Easier access to data , Compared with multi table query ;

         View can provide security protection for confidential data ;

         The modification of the view will not affect the basic table , Independent operation unit is provided , Relatively light weight .

31, use delete How to retrieve the erroneously deleted data ?

         Can be used Flashback The tool recovers the data through flashback .

32,Flashback What is the principle of restoring data ?

        Flashback The principle of restoring data is to modify binlog Content of , Take back the original library and replay , So as to realize data retrieval .

©2019-2020 Toolsou All rights reserved,
Solve in servlet The Chinese output in is a question mark C String function and character function in language MySQL management 35 A small coup optimization Java performance —— Concise article Seven sorting algorithms (java code ) use Ansible Batch deployment SSH Password free login to remote host according to excel generate create Build table SQL sentence Spring Source code series ( sixteen )Spring merge BeanDefinition Principle of Virtual machine installation Linux course What are the common exception classes ?