One, database knowledge (general)

1. Primary key, foreign key, superkey, candidate key Superkey: The set of attributes that uniquely identify a tuple in a relationship is called a relational superkey. An attribute can be used as a superkey, or a combination of attributes can be used as a superkey. Superkeys contain candidate keys and primary keys. Candidate key: is the minimum superkey, that is, a superkey with no redundant elements. Primary key: A combination of data columns or attributes in a database table that uniquely and completely identify a stored data object. A data column can have only one primary key, and the value of the primary key cannot be missing, that is, cannot be Null. Foreign key: The primary key of another table that exists in one table is called the foreign key of that table.

2. Why use autoincrement column as primary key? If a PRIMARY KEY is defined, InnoDB selects the PRIMARY KEY as the clustered index. If no PRIMARY KEY is explicitly defined, InnoDB selects the first unique index that does not contain a NULL value as the PRIMARY KEY. If there is no such unique index, InnoDB selects the first unique index that does not contain a NULL value as the PRIMARY KEY. InnoDB selects the built-in 6-byte ROWID as the implied clustered index (ROWID increases with the primary key as row records are written, and this ROWID is not referential like ORACLE’s ROWID, which is implied). The data records themselves are stored on the leaf node of the main index (a B+Tree). This requires that data records within the same leaf node (size of a memory page or disk page) be stored in primary key order, so every time a new record is inserted, MySQL inserts it into the appropriate node and location based on its primary key. If the page reaches the load factor (InnoDB defaults to 15/16), Is opening a new page (nodes) if the table USES the primary key, then insert a new record, record will add to the current index node position follow-up, when a page write full, will automatically create a new pages if the use of the primary key (if the id number or student id, etc.), since each into the key value approximation of random, So every time a new record to be inserted into the existing index page, at a certain position in the middle of the MySQL at this time have to in order to insert a new record to the appropriate location and mobile data, or even the target page might have been back to disk and clear it from the cache, at this time to read from the disk back again, it cost a lot of, at the same time, frequent moving, paging are responsible for a large number of pieces, We ended up with an index structure that wasn’t compact enough, and then had to go through the OPTIMIZE TABLE to rebuild the TABLE and OPTIMIZE the fill page.

3. What does a trigger do? A trigger is a special stored procedure that is triggered by an event and executed. It can enforce constraints to maintain data integrity and consistency, and it can track operations within the database to prevent unauthorized updates and changes. You can cascade. For example, a trigger on one table contains a data operation on another table that causes the trigger on that table to fire.

4. What are stored procedures? With what? A stored procedure is a precompiled SQL statement that has the advantage of allowing modular design, meaning that it is created once and can be called many times later in the program. If an operation requires multiple SQL executions, using stored procedures is faster than simply executing SQL statements. Call: 1) A stored procedure can be called with a command object. 2) It can be called by external programs, such as Java programs.

5. What are the advantages and disadvantages of stored procedures? Advantages: 1) The stored procedure is precompiled and executed efficiently. 2) Stored procedure code is directly stored in the database, through the stored procedure name directly call, reduce network communication. 3) High security. Users with certain permissions are required to execute stored procedures. 4) Stored procedures can be reused to reduce the workload of database developers. Disadvantages: Poor portability

(1) A stored procedure is used to return specific data to a database where users perform specific operations or tasks (such as insert, delete, etc.). (2) Stored procedures are declared in procedure and functions in function. (3) Stored procedures do not need to return types; functions must return types. (4) Stored procedures can be executed as separate PL-SQL, functions cannot be executed as separate PLSQL and must be part of expressions. (5) Stored procedures can only return values through out and in/out. Functions can use out and in/out as well as return. (6) SQL statements (DML or SELECT) cannot call stored procedures, while functions can.

7. What are views? What is a cursor? View: A virtual table that has the same functions as a physical table. Views can be added, modified, looked up, or manipulated, usually by rows or subsets of columns in one or more tables. Changes to the view affect the base table. It makes it easier for us to get data compared to multi-table queries. Cursor: the effective processing of the result set as a unit. Cursors can be fixed to specific rows in the cell, retrieving one or more rows from the current line of the result set. You can modify the current row of the result set. Cursors are not generally used, but they are important when you need to process data item by item.

8. What are the pros and cons of views? Advantages: 1 Access to the database, because the view can selectively select parts of the database. 2) Users can get results from complex queries through simple queries. 3) Maintain data independence and try to retrieve data from multiple tables. 4) Different views can be generated for the same data. Disadvantages: Performance: When querying a view, you must convert the view’s query to a base table query. If the view is defined by a complex multi-table query, then you cannot change the data

Create table truncate delete table truncate delete table truncate delete 2) TRUNCate deletes data in the table. When inserting data, the self-growth ID starts from 1. 3) delete the table from which the data is stored. (1) The DELETE statement deletes a row from the table each time and saves the row deletion operation as a transaction in the log for rollback. TRUNCATE TABLE deletes all data from the TABLE at one time, and individual deletion operations are not logged. Deleted rows cannot be restored. And no table-related delete triggers are activated during the delete process. Fast execution speed. (2) Space occupied by tables and indexes. After a table is TRUNCATE, the space occupied by the table and index is restored to the initial size. The DELETE operation does not reduce the space occupied by the table or index. The drop statement frees all space occupied by the table. (3) Generally, DROP > TRUNCate > DELETE (4) Application range. TRUNCATE only applies to TABLE; DELETE can be table or view (5) TRUNCATE and DELETE only DELETE data, while DROP deletes the entire table (structure and data). (6) TRUNCate and DELETE without WHERE: delete data only, not table structure; DROP removes dependent, trigger index; Stored procedures/functions that depend on this table are retained with a state of: INVALID. (7) The DELETE statement is data Maintain Language (DML). This operation will be put into the rollback segment and take effect after the transaction is committed. If there is a corresponding tigger, it will be fired at execution time. Truncate and DROP are DATA define languages (DLLS). The operation takes effect immediately. The original data cannot be rolled back because it is not in the rollback segment. (9) In the case of no backup, use DROP and TRUNCate with caution. To delete some rows, use delete and be careful to constrain the scope of influence with WHERE. The rollback section should be large enough. To drop a table, use drop; To preserve a table and delete data unrelated to a transaction, use TRUNCate. If it is related to a transaction, or if the teacher wants to trigger, use delete. (10) Truncate table name fast, and high efficiency, because: Truncate TABLE is functionally the same as a DELETE statement without a WHERE clause: Both DELETE all rows in the table. However, TRUNCATE TABLE is faster than DELETE and uses less system and transaction log resources. The DELETE statement deletes one row at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE deletes data by releasing the data pages used to store TABLE data, and only the release of the pages is recorded in the transaction log. (11) TRUNCATE TABLE deletes all rows in the TABLE, but the TABLE structure, columns, constraints, indexes, etc., remain unchanged. The count used by the new row identity is reset to the seed of the column. If you want to preserve the identity count value, use DELETE instead. To DROP the TABLE definition and its data, use the DROP TABLE statement. (12) For tables referenced by FOREIGN KEY constraints, the TRUNCATE TABLE cannot be used, but the DELETE statement without the WHERE clause should be used. Since TRUNCATE TABLE is not logged, it cannot activate the trigger.

10. What is a temporary table and when will a temporary table be deleted? You can manually DROP a TEMPORARY TABLE: DROP TEMPORARY TABLE IF EXISTS temp_tb; Temporary tables are only visible in the current connection. When a connection is closed, MySQL automatically drops the table and frees all space. Therefore, you can create temporary tables with the same name in different joins and manipulate temporary tables belonging to this join. The syntax for creating a TEMPORARY table is similar to that for creating a table except that you add the keyword TEMPORARY, for example: CREATE TEMPORARY TABLE tmp_table ( NAME VARCHAR (10) NOT NULL, time date NOT NULL ); select * from tmp_table;

11. Describe the difference between non-relational database and relational database. Advantages of non-relational databases: Performance: NOSQL is based on key-value pairs, which can be imagined as the correspondence between primary keys and values in a table, and does not need to be parsed by the SQL layer, so performance is very high. Scalability: Also because there is no coupling between data based on key-value pairs, it is very easy to scale horizontally. Relational database advantages: Complex queries: You can easily use SQL statements to do very complex data queries from one table or multiple tables. Transaction support: Enables high security data access requirements. Others: 1. For these two kinds of databases, the strength of the other party is its weakness, and vice versa. 2.NOSQL databases gradually begin to have some complex query functions of SQL databases, such as MongoDB. 3. Support for transactions can also be implemented with some system-level atomic operations such as optimistic locks to curve the country, such as Redis set NX.

12. What is a database paradigm that designs tables based on a scenario? First normal form :(make sure each column remains atomic) all field values are non-decomposable atomic values. The first paradigm is the most basic paradigm. A database table satisfies the first normal form if all field values in the table are non-decomposable atomic values. Proper adherence to the first paradigm depends on the actual requirements of the system. For example, some database systems need to use the “address” attribute, originally directly designed “address” attribute into a database table field on the line. However, if the system often accesses the “city” part of the “address” attribute, it is necessary to re-split the “address” attribute into multiple parts such as province, city, and detailed address for storage, so that it will be very convenient to operate on a part of the address. This design satisfies the first paradigm of databases, as shown in the following table. The user information shown in the above table follows the requirements of the First paradigm, which makes it very convenient to classify the cities used by users and improves the performance of the database. Second normal form :(ensure that each column in the table is related to the primary key) in a database table, only one type of data can be stored in a table, not multiple data in the same database table. The second normal form is a step up from the first. The second normal form requires that every column in a database table is associated with a primary key, not just a part of the primary key (primarily for union primary keys). That is to say, in a database table, a table can only save one kind of data, can not save multiple data in the same database table. For example, if you want to design an order information table, because there may be multiple items in the order, use the order number and item number as the joint primary key of the database table. Third normal form :(make sure that each column is directly related to the primary key column, not indirectly.) every column in a table is directly related to the primary key, not indirectly. The third normal form requires that each column in the data table is directly related to the primary key, not indirectly. For example, when designing an order data table, the customer number can be used as a foreign key to establish a relationship with the order table. It is not possible to add fields to the order form for other information about the customer, such as name, company affiliation, etc. BCNF: conforms to 3NF, and the primary attribute does not depend on the primary attribute. If the relational pattern is in the second normal form and each attribute does not pass dependent on the key code, R is in the BC normal form. In general, the conditions of the BC normal form are variously equivalent: the left side of each nontrivial dependency must contain a key code; Each determinant must contain a key code. The BC paradigm checks for both non-primary and primary attributes. When only non-primary attributes are examined, it becomes the third normal form. Any relationship that satisfies BC normal form must satisfy the third normal form. It can also be said that if a relationship reaches the third normal form and it has only one candidate code, or each of its candidate codes is a single attribute, then the relationship naturally reaches BC normal form. Generally, a database design that conforms to 3NF or BCNF is sufficient. The fourth normal form: requires that many-to-many relationships within the same table be deleted. Fifth normal form: Rebuild the original structure from the final structure.

13. What are inner join, outer join, cross join, Cartesian product, etc.? Inner join: joins only matched rows left outer join: contains all rows from the left table (regardless of whether there are rows matching them in the right table), and all matched rows from the right table right outer join: Contains all rows in the right table (regardless of whether there are rows matching them in the left table), and all rows matching in the left table e.g. 1: SELECT a.,b. FROM luntan LEFT JOIN usertable as B ON A.user name= B.user name SELECT a.,b. FROM city as a FULL OUTER JOIN user as b ON a.user =b.username; SELECT a.,b. FROM city as a FULL OUTER JOIN user as B ON A.user = B.user name Cross connection: Generates the Cartesian product – it does not use any matching or selection criteria, but matches every row in one data source with every row in another data source. For example: SELECT type,pub_name FROM titles CROSS JOIN publishers ORDER BY type

14. How to use varchar and char?

1. The length of a char is immutable, whereas the length of a varchar is variable. Define a char[10] and vARCHar [10]. If you save ‘CSDN’, then the char will still be 10 characters in length, except for the character ‘CSDN’, followed by six Spaces, and vARCHar will immediately change the length to 4. When taking data, char trim() is not needed.

2. Char is still much faster than vARCHar because of its fixed length, which makes it easier for programs to store and search. Char also pays the price of space because it is fixed in length, so it inevitably has extra space placeholders that take up space, which is a space-for-time efficiency. Varchar takes space efficiency as the first priority.

3. A char is stored in one byte for ASCII characters and two bytes for Chinese characters. Varchar is stored in two bytes for each English character and two bytes for Each Chinese character. 4. Both stores data that is not Unicode character data.

SQL languages are divided into four categories: data query language DQL; data manipulation language DML; data definition language DDL; data control language DCL. Data query language DQL The basic structure of data query language DQL consists of SELECT clause, FROM clause, and WHERE clause: SELECTFROMWHERE data manipulation language DML Data manipulation language DML has three forms: 1) INSERT2) update: Data definition Language (DDL) is used to create various objects in the database —– tables, views, indexes, synonyms, clusters, etc. The CREATE TABLE/VIEW/INDEX/SYN/CLUSTER TABLE VIEW INDEX synonyms Clusters of DDL operations is implicit commit! Rollback Data control Language (DCL) The DATA control language (DCL) is used to grant or revoke privileges to access a database, control the time and effect of database manipulation transactions, and monitor the database. I GRANT you permission. 2) ROLLBACK [WORK] TO [SAVEPOINT] : ROLLBACK TO a certain point. ROLLBACK – ROLLBACK; The rollback command returns the database state to the last committed state. The format is: SQL>ROLLBACK; 3) COMMIT [WORK] : COMMIT. Database insert, delete, and modify operations are not complete until the transaction is committed to the database. Until the transaction commits, only the person operating the database has the right to see what is being done; others can only see it after the final commit is completed. There are three types of commit data: explicit commit, implicit commit, and automatic commit. These three types are described below. (1) Explicit COMMIT An explicit COMMIT is performed by running the COMMIT command. The format is: SQL>COMMIT; (2) Implicit submission The submission completed indirectly by SQL command is implicit submission. These commands are: ALTER, AUDIT, COMMENT, CONNECT, CREATE, DISCONNECT, DROP, EXIT, GRANT, NOAUDIT, QUIT, REVOKE, RENAME. AUTOCOMMIT If AUTOCOMMIT is set to ON, the system will commit automatically after insert, modify, or delete statements have been executed. The format is: SQL>SET AUTOCOMMIT ON;

% % wildcard: indicates any number of occurrences of any character (can be 0). The underscore wildcard: matches only one character, no more or less than one character. The like operator: indicates that the mysql search mode uses wildcards instead of equal matches. SELECT * FROM products WHERE prod_name = ‘1000’; SELECT * FROM products WHERE prod_name = ‘1000’; Results that can only match are 1000, not results like JetPack 1000. SELECT * FROM products WHERE prod_name = ‘yves%’; SELECT * FROM products WHERE prod_name = ‘yves%’; SELECT * FROM products WHERE prod_name = ‘%yves%’; SELECT * FROM products WHERE prod_name = ‘%yves%’; SELECT * FROM products WHERE products.prod_name like SELECT * FROM products.prod_name like SELECT * FROM products WHERE products.prod_name like ‘%yves’; SELECT * FROM products WHERE prod_name = ‘yves’; SELECT * FROM products WHERE prod_name = ‘yves’; Match result: record as “yyves”. SELECT FROM products WHERE products.prodname like ‘yves’; Matching results are as follows: Note: Be case sensitive. When using fuzzy matching, that is, when matching text,mysql may be size sensitive or case insensitive, depending on how the user has configured mysql. If case sensitive, records like YvesHe cannot be matched by matching conditions like “yves__”. Note the trailing space,”%yves” will not match a record like “heyves “. SELECT * FROM products WHERE prod_name = ‘%; SELECT * FROM products WHERE prod_name = ‘%; < products.prod_name > < products.prod_name > Tips & Suggestions: As you can see, MySQL’s wildcards are useful. But this functionality comes at a cost: Wildcard searches generally take longer to process than the other searches discussed earlier. Here are some tips to keep in mind when using wildcards. Don’t overuse wildcards. Other operators should be used if they serve the same purpose. When you do need to use wildcards, don’t use them at the beginning of a search pattern unless absolutely necessary. Placing wildcards at the beginning of a search pattern is the slowest. Pay close attention to the wildcard positions. If misplaced, the desired number may not be returned.

Count () counts the number of rows, including NULLcount(column) counts the number of rows that a specific column value has. There is another way to use count(), and count(1) gives the same result as count(). 1. SELECT COUNT() FROM tablename is optimal in any case; SELECT COUNT() FROM tablename WHERE COL = ‘value’; 3. Eliminate SELECT COUNT(COL) FROM tablename WHERE COL2 = ‘value’. If the table has no primary key, count(1) is faster than count(). If there is a primary key, then count(primary key, union primary key) is faster than count(). If the table has only one field,count() is the fastest. Count (1) scans only primary keys, just like count(primary key). Like count(non-primary key), count() scans the entire table. Obviously the former is faster.

18. What is the left-most prefix rule? ALTER TABLE people ADD INDEX lname_fname_age (lame,fname,age); To improve search efficiency, we need to consider using multi-column indexes. Since the index files are stored in b-tree format, we can get the final results without scanning any records. Mysql > select * from lname; select * from age; select * from lname; select * from age; select * from lname; select * from age; select * from lname; Lname_fname_age (lname,fname,age); lname (lname,fname); lname (fname,age);

19. What is an index? What is index: Database index, is a sort of data structure in database management system, index implementation usually uses B tree and its variant B+ tree. In addition to the data, the database system maintains data structures that satisfy specific lookup algorithms, and that reference (point to) the data in a way that makes it possible to implement advanced lookup algorithms on these data structures. This data structure is called an index.

20. What does an index do? What are its advantages and disadvantages? Indexes help to query and update data in a database table. Indexing tables comes at a cost in terms of increased database storage space and the time it takes to insert and modify data (because indexes change accordingly).

21. What are the advantages and disadvantages of indexes? Creating an index can greatly improve the performance of the system (advantages) :(1) by creating a unique index, you can ensure the uniqueness of each row in the database table. (2) The speed of data retrieval can be greatly accelerated, which is the main reason for creating indexes. (3) It can speed up the connection between tables, especially in the realization of referential integrity of data. (4) When grouping and sorting clauses are used for data retrieval, the time of grouping and sorting in query can also be significantly reduced. (5) Through the use of index, can be in the process of query, use optimization hide, improve the performance of the system. There are many downsides to adding indexes :(1). Creating and maintaining indexes takes time, which increases with the volume of data. (2). Indexes need to occupy physical space. In addition to the data table occupies data space, each index also needs to occupy a certain physical space. (3). When the data in the table is added, deleted and modified, the index also needs to be maintained dynamically, which reduces the data maintenance speed. (4). Which columns are suitable for indexing and which are not? An index is built over certain columns in a database table. When creating an index, you should consider which columns you can and cannot create indexes on. In general, you should create indexes on these columns :(1) you can speed up searches on columns that need to be searched frequently; (2) on the column as the primary key, enforce the uniqueness of the column and organize the arrangement structure of the data in the table; (3) In the columns that are often used in connection, these columns are mainly some foreign keys, which can speed up the connection; (4) Create indexes on columns that often need to be searched by range because the indexes are sorted and specify a contiguous range; (5) select * from table where index (s) are sorted; (6) Create indexes on columns that are often used in the WHERE clause to speed up the judgment of the condition. You should not create indexes for some columns :(1) you should not create indexes for columns that are rarely used or referenced in queries. This is because, since these columns are rarely used, having an index or no index does not improve query speed. On the contrary, due to the increase of indexes, the maintenance speed of the system is reduced and the space requirements are increased. (2) Do not add indexes to columns that have very few data values. This is because, because the values of these columns are very small, such as the gender column of the personnel table, in the result of the query, the data rows of the result set account for a large proportion of the data rows in the table, that is, a large proportion of the data rows that need to be searched in the table. Adding indexes does not significantly speed up retrieval. (3) Columns defined as text, image, and bit data types should not be indexed. This is because the data volume of these columns is either quite large or has very few values. (4) Indexes should not be created when the modification performance is much greater than the retrieval performance. This is because modification performance and retrieval performance are incompatible. When indexes are added, retrieval performance is improved, but modification performance is reduced. When indexes are reduced, modification performance is improved and retrieval performance is reduced. Therefore, indexes should not be created when the modification performance is much greater than the retrieval performance. MySQL > select * from ‘MySQL’;

22. What fields are suitable for indexing? A unique, non-null, frequently queried field

MySQL > select * from B+Tree; Hash index and B+ tree index have the following characteristics: Hash index structure is special, the retrieval efficiency is very high, the index can be located at a time; The B+ tree index needs to go from the root node to the branch node, and finally to access the page node as many IO visits; Why not all Hash indexes instead of B+ tree indexes? Hash indexes can only be used for “=”,”IN”, and “” queries, but cannot be used for range queries, because the size relationship of Hash values processed by the corresponding Hash algorithm cannot be exactly the same as before the Hash operation. Hash indexes cannot be used to avoid sorting because the size of the Hash value is not necessarily the same as the key before the Hash operation. A Hash index cannot be queried using partial index keys. For a combined index, the combined index keys are used to compute the Hash value together, not separately. Therefore, the Hash index cannot be used when the first one or more index keys are used to query the Hash index. Hash indexes cannot avoid table scanning at any time. Because different index keys have the same Hash value, the query cannot be directly completed even if the number of records of data that meet a certain Hash key value. Hash indexes do not necessarily perform better than B+ tree indexes when a large number of Hash values are equal. In MySQL, only the HEAP/MEMORY engine supports Hash indexes. (2). Commonly used in the InnoDB engine used by default is B + tree index, it can real-time monitoring the usage of table index, if think build a hash index can improve the efficiency of query, automatically in memory “adaptive hash index buffer” to establish a hash index (the default in the InnoDB open adaptive hash index), by observing the search pattern, MySQL uses the index key prefix to create a hash index. If a table is mostly in the buffer pool, creating a hash index can speed up equivalent queries. The obvious difference between B+ tree index and hash index is: (3). If it is equivalent query, then hash index obviously has absolute advantage, because it only needs to go through the algorithm to find the corresponding key value; Of course, this assumes that the keys are unique. If the key value is not unique, you need to find the location of the key, and then scan through the linked list until the corresponding data is found. (4). If it is a range query search, at this time the hash index is useless, because the original is an ordered key value, after the hash algorithm, may become discontinuous, there is no way to use the index to complete the range query search; Similarly, hash indexes can’t be sorted using indexes, and partially fuzzy queries like ‘XXX %’ (partially fuzzy queries are essentially range queries); (5). Hash indexes also do not support the leftmost matching rule of multi-column joint indexes; (6). The keyword retrieval efficiency of B+ tree index is relatively average, unlike that of B tree, which fluctuates greatly. In the case of a large number of duplicate key values, the efficiency of hash index is also extremely low, because of the so-called hash collision problem. (7) In most scenarios, there will be range query, sorting, grouping and other query features, using B+ tree index can be.

B tree, each node stores key and data, all nodes make up the tree, and the leaf node pointer is NUL. The leaf node does not contain any keyword information. B + tree, all the leaf node contains all the key information, and pointer to contain these keywords records, and the leaf node itself depends on the size of the keywords from the big order link, all the terminal nodes can be regarded as index part, nodes contains only its roots in the largest (or smallest) key. (The non-end nodes of the B-tree also contain valid information to look up)

25. Why is B+ more suitable than B tree for file index and database index in practical operating system? 1. The disk read and write cost of B+ is lower. The internal node of B+ does not point to the specific information of the keyword. So the internal nodes are smaller than the b-tree. If all the keywords of the same internal node are stored in the same disk block, then the disk block can contain more keywords. Read into memory at a time to find more keywords. The number of IO reads and writes is relatively low. 2. The query efficiency of B+tree is more stable because the non-endpoint is not the node that ultimately points to the file content, but the index of the keyword in the leaf node. So any keyword lookup must take a path from root to leaf. The length of all keyword query paths is the same, resulting in the same query efficiency of each data.

26. What is the difference between a clustered index and a non-clustered index? Clustered index: Records in a clustered index table are arranged in the same order as those in the index table. Therefore, query efficiency is fast. As long as the first index value is found, other records are stored in a physical cluster. The disadvantage of clustered index correspondence is that it is slow to modify because the data pages are reordered when the records are inserted to ensure that the physical and index order of the records in the table is consistent. Clustered index similar xin-hua yu using pinyin dictionary to look up Chinese characters, pinyin is secretary of the retrieval table in order to line up with the a to z, like the same logical sequence in physical order, when you need to find a, ai two pronunciation of the word, or to a search for multiple silly (sha) homophone, may turn back a few pages, or get the result and then the next line. Nonclustered indexes specify the logical order of records in a table, but physical records and indexes are not necessarily the same. Both indexes use a B+ tree structure. Leaf layers of nonclustered indexes do not overlap actual data pages. Instead, the leaf layer contains a pointer to the records in the table in the data page. Non-clustered indexes have many levels and do not cause data rearrangement. The non-clustered index is similar to the query of Chinese characters in Xinhua Dictionary by partial radical. The retrieval table may be arranged in accordance with horizontal, vertical and apostrophe, but because the text is a and Z pinyin order, it is similar to the logical address and physical address does not correspond. It also applies to groups, large numbers with different values, and frequently updated columns that are not suitable for clustered indexes. Fundamental difference: The fundamental difference between a clustered index and a non-clustered index is whether the table records are sorted in the same order as the index. MySql > MySql > MySql > MySql > MySql > MySql What are the functions you use most often? Loweruppersubstrlengthtrim (leading and trailing Spaces, do not remove the middle of the space) str_to_date (Y – m – % d % %) date_formatformat (keep) roundrand () random ifnull (if is empty, The grouping function automatically ignores the null value countsumavgminmax 2. Conditions for group query? If order by is used, order by must be placed after group BY. In SQL statements, select statements can only be followed by grouping functions + participating fields. If you want to filter the grouped data, you need to use the HAVING clause. 3. Limit usage method? select * from emp limit m,n; 4. Mysql common data types? Char: fixed-length string, suitable for family or foreign key varchar: double/floatint/bigintdate 5 variable length strings. Alter TABLE name ADD field name Data type (length) — Add a field ALTER table name modify field name Data type (length) — Change field length ALTER table name change Alter TABLE alter table drop alter table drop drop alter table drop drop Service mysqld start

/init.d/mysqld start

Safe_mysql & Stop the mysqld service

/etc/init.d/mysqld stop

mysqladmin -uroot -p123456 shutdown 7. Check whether the port runs lsOF -I :3306

netstat -tunlp|grep 3306

Ss – tulnp | grep 3306 8. How to set password or change password for MySQL. Methods a

Mysqladmin -u root -p123456 password ‘abc123’ #

Method 2 (SQL statement modification)

update mysql.user set password=password(123456) where user=’root’ and host=’localhost’;

flush privileges;

Method 3 (SQL statement modification)

set password=password(‘abc123’);

9. How to log in to the MySQL database? Single instance login

mysql -uroot -p123456

Multi-instance login

mysql -uroot -p123456 -S /data/3306/mysql.sock

Mysql > show variables like “%charac%”;

11. How do I view the current database version

mysql -V

mysql> select version();

12. How do I view the current login user? mysql> select user();

Mysql > select * from T1; mysql> select * from T1;

mysql> show tables;

Mysql > create database oldboy default character set GBK mysql> create database oldboy default character set GBK mysql> show create database oldboy;

Create user oldboy; Make it can manage oldboy mysql database > grant select, update, insert, delete, alter on oldboy. * to oldboy @ ‘localhost’ identified by ‘123456’.

Mysql > select on oldboy@’localhost’; mysql> select on oldboy@’localhost’;

Mysql > select user,host from mysql.user;

Mysql > use oldboy();

19. Please write a script: Create an innoDB GBK table test, Mysql > create table test (id int(4),name varchar(16)) engine=InnoDB default charset= GBK;

Mysql > select * from table_name where table_name = 1; mysql> show create table test\G

Mysql > insert into test (id,name) values (1,’oldboy’); mysql> insert into test (id,name) values (1,’oldboy’);

Mysql > insert into test (id,name) values (2,’ oldboyedu’),(3,’oldboyedu’);

Mysql > select * from test where name=’oldboy’;

Mysql > update test set name=’oldgirl’ where id=1;

Mysql > alter table test add age tinyint(2) after id; alter table test add age tinyint(2) after id;

Mysql > system mysqldump -uroot -p123456 -b -x -f –events oldboy >/opt/bak.sql

Mysql > delete from test;

MySql database

MySQL > alter table lock table lock table lock MyISAM only supports table locks, InnoDB supports table locks and row locks, default row locks. Table lock: low overhead, fast lock, no deadlock. Large lock granularity has the highest probability of lock conflict and the lowest concurrency. Row-level locking: expensive, slow, and deadlocks. The lock force is small, the probability of lock conflict is small, and the concurrency is the highest.

2. What is the default transaction isolation level of MySQL? Read Uncommitted (RU): Changes made by a transaction can be seen by other transactions before it is committed. Read Commit (RC): After a transaction commits, its changes are seen by other transactions. Repeatable read (RR): The data seen during the execution of a transaction is always the same as the data seen when the transaction is started. Of course, at the repeatable read isolation level, uncommitted changes are also invisible to other transactions. Serialization (S): For the same row, both read and write are locked. When a read-write lock conflict occurs, a later-accessed transaction must wait for the previous transaction to complete before it can continue.

3. What are the types of Mysql tables? MyISAM, InnoDB, HEAP, BOB,ARCHIVE,CSV, etc. MyISAM: Mature, stable, easy to manage, fast to read. Some features do not support table level locking (transactions, etc.). InnoDB: support transactions, foreign keys and other features, data row locking. It takes up too much space and does not support full-text indexes.

4. How can MySQL restore data from half a month ago? Restore through whole database backup +binlog. The prerequisite is that the entire database is backed up periodically and binlog logs are saved.

MySQL > alter table id = 1; MySQL > alter table ID = 1; MySQL > alter table ID = 1 If the table type is MyISAM, then the id is 8. If the table type is InnoDB, the id is 6. InnoDB tables only record the maximum ID of the self-increment primary key in memory, so the maximum ID will be lost after restarting.

MySQL > select * from inner join; select * from left join; Inner join keywords: inner join; Left join: left join; Right join: right join. The inner link is to display the matched associated data; The left link shows all the tables on the left, and the table on the right shows the data that meets the conditions; The right join is just the opposite.

7. What are the methods used to troubleshoot MySQL problems? Run the show processList command to view all current connection information. Query SQL statement execution plans using the Explain command. Enable the slow query log function to view the SQL that is slowly queried.

8. How to optimize MySQL performance? Create an index for the search field. Avoid select * and list the fields you want to query. Vertical split table. Select the correct storage engine. Reading and writing separation

9.MySQL database is used as the storage of the release system, with increments of more than 50,000 pieces per day. It is expected to be operated and maintained for three years. (1) Well-designed database structure allows partial data redundancy, avoids join query as far as possible, and improves efficiency. (2) Select the appropriate table field data type and storage engine, and add indexes appropriately. (3) Separate read and write from primary and secondary mysql replication. (4) Divide the data table into different tables to reduce the amount of data in a single table and improve the query speed. (5) Add caching mechanisms such as Redis, memcached, etc. (6) Generate static pages (such as OB cache) for pages that are not frequently changed. (7) Write efficient SQL. SELECT field_1, field_2, fieldd_3 FROM TABLE.

10. What are the components of MySQL and what are they used for? (1) Server (2) connector: management connection, permission verification. (3) analyzer: lexical analysis, syntax analysis. (4) optimizer: execution plan generation, index selection. (5) executor: operation storage engine, return execution results. (6) storage engine: storage data storage, provide read and write interface.

11. How to check whether the MySQL index meets the requirements? Use Explain to see how SQL executes queries to see if your index meets your requirements. Explain syntax: explain select * from table where type=1

12. Would you please introduce the primary/secondary replication of mysql? MySQL master-slave replication is one of its most important functions. Master-slave replication means that one server acts as the master database server and one or more servers act as the slave database server, and the data in the master server is automatically copied to the slave server. For multilevel replication, the database server can act as either the host or slave. MySQL master-slave replication is based on the primary server recording binary logs of database changes, and the secondary server automatically performs updates through the binary logs of the primary server. There are two types of primary/secondary replication in MySQL: synchronous replication and asynchronous replication. Most of the actual replication architectures are asynchronous replication. The basic replication process is as follows: The IO process on the Slave connects to the Master and requests the log content from the specified location in the specified log file (or from the original log). After the Master receives the request from the Slave I/O process, the REPLICATION I/O process reads the specified log information and sends the log information to the Slave I/O process. In addition to the log information, the returned information includes the name of the bin-log file that has been sent to the Master and the location of the bin-log file. After receiving the log information, the Slave I/O process adds the received log content to the end of the Slave relay log file, and records the file name and location of the bin-log read from the Master to the master-info file. So that the next read can clearly tell the Master “I need to start from a certain bin-log in the next log content, please send me.” When the Sql process of the Slave detects the new content added to the relay log, it immediately parses the content of the relay log into the executable content that is actually executed on the Master end and executes it itself.

MVCC is a multi-version concurrency control mechanism. It is a specific way for mysql’s InnoDB storage engine to implement isolation levels for committed reads and repeatable reads. MVCC is by saving snapshots of the data at some point to implement the mechanism, its behind each row record save two hidden columns, respectively, save the line to create the version number and delete version number, and then Innodb MVCC use to a snapshot of the stored in the Undo log, the log by rollback Pointers connect a data line all the snapshots.

14. What are the common Mysql replication architectures? (1) In the scenario where the read request pressure of the master library is very high, the read and write separation can be realized by configuring the replication architecture of the master and multiple slaves. A large number of read requests that do not require high real-time performance can be distributed to multiple slave libraries through load balancing to reduce the read pressure of the master library. In the event of an abnormal outage of the primary library, a secondary library can be switched over to the primary library to continue providing services. (2) The architecture of multi-stage replication with one master and many slaves can solve the requirements of most scenarios with particularly heavy read request pressure. Given that MysQL replication is done by “pushing” Binlog logs from the master to the slave, I/0 stress and network stress on the master increases as the slave grows (each slave has a separate Binlog Dump thread on the master to send events), The multilevel replication architecture solves the extra I/0 and network pressure of the master library in the scenario of one master and multiple slaves. (3) Dual Master replication refers to Master library and Master2 as Master and slave library. Client clients can access Master library for write requests, and Master library or Master2 for read requests.

15. What is the difference between MyISam and InnoDB? (1) InnoDB supports transactions, MyISAM does not. (2) InnoDB supports row-level locking, MyISAM supports table-level locking. (3) InnoDB supports multi-version concurrency control (MVVC), MyISAM does not support. (4) InnoDB supports foreign keys, (5) MyISAM supports full text indexing, InnoDB does not (but Sphinx plug-in can be used)

MySQL > show global variables like ‘port’; The default port for mysql is 3306. Sqlserver default port number: 1433; The default oracle port number is 1521. The default DB2 port number is 5000. The default port number for PostgreSQL is 5432.) Modify the port number: Modify the port number: edit the /etc/my. CNF file.

17. How to add index to Mysql? ALTER TABLE table_name ADD PRIMARY KEY (column) ALTER TABLE table_name ADD PRIMARY KEY (column ALTER TABLE table_name ADD INDEX index_name (column) (4) ADD FULLTEXT(FULLTEXT INDEX) ALTER TABLE table_name ADD INDEX index_name (column ALTER TABLE table_name ADD INDEX index_name (column1, column2, column2) column3 )

18. What is your understanding of two common storage engines of MySQL: MyISAM and InnoDB? InnoDB engine: The InnoDB engine provides acid transaction support, row-level locking and foreign key constraints, and is designed for database systems with large data volumes. When MySQL is running, InnoDB creates a buffer pool in memory to buffer data and indexes. However, this engine does not support full-text search and is slow to start. It does not save the number of rows in a table, so when performing select count() from table, you need to scan the entire table. Because of the small granularity of locks, write operations do not lock the entire table. Therefore, using locks in scenarios with high concurrency can improve efficiency. MyIASM engine: the default engine for MySQL, but does not support transactions, row-level locks and foreign keys. Therefore, when inserting and updating statements, that is, when writing, the table needs to be locked, resulting in a loss of efficiency. Unlike InnoDB, MyIASM saves the number of rows in a table, so when you select count() from a table, you can read the saved values directly without scanning the entire table. Therefore, MyIASM can be used as the database engine of choice if the tables are being read far more than written, and transaction support is not required.