For programmers, going to any company interview, database is unavoidable. The more developers learn about MySQL, the more things you can do.
To complete business functions, understand basic Sql statements.
Do performance optimization, to understand the index, understand the engine.
Do sub – library sub – table, to understand the master from, understand reading and writing separation.
Do security, to understand permissions, understand backup, understand logs.
Do cloud database, to understand the source code, understand the bottleneck.
Index is a data structure that helps MySQL obtain data efficiently. Index as a key knowledge of MySQL, in the interview frequency is particularly high.
I think I have a good understanding of MySQL index knowledge, and my colleagues warmly call me “Big man” in their work. In order to enter Ali, I specially spent about a month to review relevant knowledge, full of confidence in the resume wrote “proficient in MySQL”, can not think of Ali was severely abused by the interviewer…
I recombed the MySQL related knowledge points, we help me to see the summary is not comprehensive, shortcomings also please point out!
First, database basic knowledge
Why use a database
What is SQL?
What is a MySQL?
What are the three paradigms of database
Mysql > select * from ‘privileges’ where’ privileges’ are stored
How many types of entries are available for MySQL binlog? What’s the difference?
Second, data type
What are the data types of mysql
Three, engine
MySQL storage engine MyISAM is different from InnoDB
What is the difference between MyISAM index and InnoDB index?
Four features of the InnoDB engine
Storage Engine Selection
Four, index,
What is an index?
What are the advantages and disadvantages of indexes?
Index Usage scenarios (emphasis)
What are the types of indexes?
Index data structure (B-tree, hash)
The fundamentals of indexing
What are the indexing algorithms?
Principles of index design?
Principles for index creation (top priority)
There are three ways to create an index: drop an index
What should I pay attention to when creating an index?
Does using indexed queries necessarily improve query performance? why
How do I delete data at the million level or above
The prefix index
What is the leftmost prefix principle? What is the leftmost matching principle
B tree and B+ tree
Benefits of using B trees
Benefits of using B+ trees
What’s the difference between a Hash index and a B+ tree?
Why does the database use B+ trees instead of B trees
B+ trees do not need to query data back to the table when meeting the requirements of clustered index and overwritten index.
What is a cluster index? When to use clustered and non-clustered indexes
Must a non-clustered index be queried back into the table?
What is a federated index? Why do I care about the order in a federated index?
Five, the transaction
What are database transactions?
What are the four properties of ACID?
What is dirty reading? Phantom read? Unrepeatable?
What is the isolation level of a transaction? What is the default isolation level for MySQL?
Six, lock
Do you know about MySQL locks
The relationship between isolation levels and locks
What are the database locks by lock granularity? Locking mechanism and InnoDB locking algorithm
What locks does MySQL have? Locking as above is a bit inefficient
MySQL InnoDB engine row lock how to implement?
There are three locking algorithms for InnoDB storage engine
What is a deadlock? How to solve it?
What are optimistic and pessimistic locks for databases? How do you do that?
Seven, view,
Why use views? What is a view?
What are the characteristics of views?
What are the usage scenarios for views?
Advantages of views
Disadvantages of Views
What is a cursor?
Stored procedures and functions
What is a stored procedure? What are the pros and cons?
Viii. Trigger
What is a trigger? What are the use scenarios for triggers?
What triggers are available in MySQL?
Common SQL statements
What are the main categories of SQL statements
What are superkeys, candidate keys, primary keys, and foreign keys?
What kinds of SQL constraints are there?
Six associated queries
What is a subquery
Three cases of subqueries
In and exists are different in mysql
Varchar differs from char
Meaning of 50 in VARCHar (50)
Meaning of 20 in int(20)
Why is mysql designed this way
Int (10); char(10); varchar(10)
What’s the difference between FLOAT and DOUBLE?
Difference between DROP, DELETE, and TRUNCate
The difference between a UNION and a UNION ALL?
Ten, SQL optimization
How to locate and optimize performance problems of SQL statements? Is the index being used? Or how do I know why this statement is running slowly?
SQL life cycle?
Large table data query, how to optimize
How to handle large pages?
Mysql paging
Slow Query logs
Care about the SQL time in the business system? Statistics too slow query? How are slow queries optimized?
Why try to have a primary key?
Does the primary key use an autoincrement ID or a UUID?
Why is the field required to be not NULL?
If you want to store user password hashes, what fields should be used for storage?
Optimize data access during queries
Optimize long and difficult query statements
Optimize specific types of query statements
Optimizing associated query
Optimized subquery
Optimize LIMIT paging
Optimizing UNION queries
Optimize the WHERE clause
11. Database optimization
Why optimize
4) Database structure optimization
MySQL database CPU up to 500%
How to optimize the large table? A table has nearly ten million data, CRUD is slow, how to optimize? How is cent library cent table done? What problem does cent table cent library have? Does middleware work? Do you know how they work?
The vertical table
Applicable scenario
disadvantages
Level table:
Applicable scenario
Disadvantages of horizontal segmentation
MySQL replication principle and process
What are the solutions for read/write separation?
Backup plan, mysqlDump and XtranBackup implementation principle
What are the repair methods for data table corruption?
Spent a week to sum up the MySQL knowledge, shortcomings also please point out!! If you need this information, I will share it with you for free! Leave a comment or send me a private message!