当前位置：网站首页>MySQL is the most complete arrangement (interview questions + Notes + Guide Map), and the big interview companies are no longer stumped by mysql

MySQL is the most complete arrangement (interview questions + Notes + Guide Map), and the big interview companies are no longer stumped by mysql

2022-04-21 21:40:00 【InfoQ】

Preface

As a programmer , Yes MySQL It must not be strange , Especially the Internet industry , Yes MySQL The use of is more . For job seekers ,MySQL It's also the key point of interview , A lot of people have big factory dreams , But because MySQL Defeat the battle . actually ,MySQL It is not difficult to , Today's most complete MySQL summary , Help you to the factory “ fire ”, Interviews are no longer MySQL It's hard .
Be careful ： About MySQL To organize the contents of , Including interview questions 、 Learning notes 、 Use documentation and Xmind Some parts of the mind map , Need full HD version 《MySql Learning resources gift bag 》 Your friends can scan below ：

01、MySQL  Summary of interview questions

1.1 MySQL  Interview questions （ Base part ）：
  • drop、truncate、 delete difference
  • What are the three paradigms of database ?
  • union and union all What's the difference ?
  • char、varchar2、varchar What's the difference? ？
  • What are the merge queries ？
  • SQL Statement execution order
  • null The meaning of
  • MySQL、SqlServer、oracle Write out character store 、 String to time
  • update Can statement modify data in result set ？
  • B Trees and B+ The difference between trees
  • Have you seen the index ?  The principle of indexing
  • Type of index ,  Such as primary key index
  • see SQL Implementation plan
  • There are 100000 pieces of data ,  Write SQL Statement to query several pieces of data with a larger value in a certain field
  • The difference between subquery and associated query
  • MySQL InnoDB、Mysaim Characteristics ？
  • The difference between optimistic lock and pessimistic lock ？？
  • The difference between row lock and table lock ？
  • What is the database isolation level ？ What's the role ？
  • MySQL The basic principle of active and standby synchronization .
  • How to optimize database performance （ Indexes 、 Sub database and sub table 、 The batch operation 、 Paging algorithm 、 Upgrade your hard drive SSD、 Business optimization 、 Master-slave deployment ）
  • SQL Under what circumstances index will not be used （ It doesn't contain , It's not equal to , function ）
  • In general, what fields should be indexed （ Filter the fields with the most data ）
  • MySQL,B+ The index to achieve , Row lock implementation ,SQL Optimize
  • How to solve the problem of high concurrent inventory reduction
  • Several granularity of database transaction
1.2 MySQL  Interview questions （ The actual part ）：
  • Three paradigms of database , Design data table according to seckill scene
  • Master-slave replication of database
  • How to solve deadlock
  • mysql How to solve the problem of concurrency （ Through transaction 、 Isolation level 、 lock ）
  • Function of trigger ？
  • What is stored procedure ？ What to call ？
  • Advantages and disadvantages of stored procedures ？
  • The difference between stored procedures and functions
  • The function of index ？ And its advantages and disadvantages ？
  • What fields are suitable for indexing
  • What are the index types ？
  • What is business ？ What is a lock ？
  • What is view ？ What is a cursor ？
  • Advantages and disadvantages of views
  • List several ways to connect tables , What's the difference? ？
  • The difference between primary key and foreign key ？
  • Query statements in the database is slow , How to optimize ？
  • What are the three paradigms of database ？
  • Varchar2 and varchar What's the difference? ？
  • Oracle and Mysql The difference between ？
  • order by And group by The difference between
1.3 MySQL  Interview questions （ Advanced part ）：
  • Please explain the concept and main features of relational database ？
  • Please name a typical product of relational database 、 Features and application scenarios ？
  • Please explain the concept and main features of non relational database ？
  • Please name the typical products of non relational database 、 Features and application scenarios ？
  • Please describe in detail  SQL  Statement classification and corresponding representative keywords .
  • Please describe in detail  char(4) and  varchar(4) The difference between .
  • How to authorize  oldboy  User from  172.16.1.0/24  Access database .
  • What is?  MySQL  Multiple instances , How to configure  MySQL  Multiple instances ？
  • How to strengthen  MySQL  Security , Please give the feasible concrete measures ？
  • delete  and  truncate  The difference between deleting data ？
  • MySQL Sleep  How to solve the problem of too many threads ？
  • sort_buffer_size  Parameter function ？ How to make online modification effective ？
  • How to clean up correctly online  MySQL binlog？
  • Binlog  What are the working modes ？ What are the characteristics of each , How to choose ？
  • Misoperation performed a  drop  library  SQL  sentence , How to recover completely ？
  • mysqldump  Backup uses -A -B  Parameters , How to recover single table ？
  • detailed  MySQL  Master-slave replication principle and configuration of master-slave complete steps .
  • How to open the slave library  binlog  function ？
  • MySQL  How to realize bidirectional master-slave replication , And explain the application scenario ？
  • MySQL  How to achieve cascade synchronization , And explain the application scenario ？
  • MySQL  How to solve the problem of master-slave replication ？
  • How to monitor whether master-slave replication fails ？
  • MySQL  How to separate read from write in database ？
  • The production of one master and many slaves goes down , How to restore by hand ？
MySQL Interview answers and Analysis ：

null
02、 About MySQL  The actual use of
2.1 MySQL  Performance optimized 21 Best practice
  • Optimize your query for query caching
  • EXPLAIN  Yours  SELECT  Inquire about
  • Use when there is only one line of data  LIMIT 1
  • Index search fields
  • stay  Join  Use examples of a similar type for tables , And index it
  • Do not  ORDER BY RAND()
  • avoid  SELECT *
  • Always set one... For each table  ID
  • Use  ENUM  instead of  VARCHAR
  • from  PROCEDURE ANALYSE()  Get advice
  • Use as much as possible  NOT NULL
  • Prepared Statements
  • Unbuffered queries
  • hold  IP  Save the address as  UNSIGNED INT
  • A fixed length watch will be faster
  • Vertical segmentation
  • Break up the big  DELETE  or  INSERT  sentence
  • The smaller the column, the faster
  • Choose the right storage engine
  • Use an object relational mapper (Object Relational Mapper)
  • look out “ Permanent link ”

null
2.2 MySQL  Performance tuning and architecture design —— Full volume
  • The basic chapter ：
MySQLI Basic introduction 、MySQL Architecture Composition 、MySQL Introduction to storage engine 、MySQL security management 、MySQL Backup and recovery
  • Performance optimization ：
influence MySQLServer Performance related factors 、MySQI Database locking mechanism 、MySQL database Query The optimization of the 、MySQL database Schema Design performance optimization 、MySQLServer performance optimization 、 Common storage engine optimization
  • Architecture design ：
MySQL Basic principles of extensible design 、 Extensibility design MySQLReplication、 Data segmentation of extensibility design 、 Extensibility design C ache And Se ar ch The use of 、MySQLCluster、 Idea and scheme of high availability design 、 High availability design MySQL monitor

null
2.3 MySQL From introduction to project practice
  • The first 1 piece   Basic knowledge of
Hello MySQL、 Database exploration 、MySQL Use of common management tools 、MySQL  Basic operation of database
  • The first 2 piece   Core applications
Basic operation of data table 、MySQL View 、MySQL Data types and operators for 、MySQL function 、MySQL  Database query statement details 、MySQL Database data and index operation 、 Stored procedures and stored functions 、 Use MySQL trigger
  • The first 3 piece   The core technology
MySQL Database rights management and recovery 、MySQL Database replication 、MySQL Log management 、 utilize MySQL Building distributed applications 、MySQL The query cache 、MySQL Use of error codes and messages
  • The first 4 piece   Advanced applications
stay C# To realize MySQL Database connection 、 stay Java To realize MySQL Database connection 、 stay PHP To realize MySQL Database connection
  • The first 5 piece   Project practice
Project actual combat coordination stage —— Project development and planning 、 The entry stage of project practice —— Forum management system database development 、 Project actual combat improvement stage —— Enterprise member management system database development 、 Advanced stage of project practice —— Database development of news release system

null
03、 About learning  MySQL The mind map of （Xmind）
MySQL  optimization problem （Xmind）
Business 、 lock 、sql Optimization principle 、JION Principle 、 Execution plan and execution details 、 Execute the process 、 The effect of table structure on performance 、 Indexes

null

summary

To make a long story short , Learning is one's own business , As a developer ,MySQL It has to be mastered , If you have a dream of a big factory , The foundation can't be too bad , If you are right about MySQL There are still many puzzles , So this MySQL Sum up the big gift bag must have ！

版权声明
本文为[InfoQ]所创，转载请带上原文链接，感谢
https://yzsam.com/2022/04/202204212135290845.html

边栏推荐

猜你喜欢

随机推荐