Article From:https://www.cnblogs.com/chuijingjing/p/9967912.html

Contents

Mysql The difference between storage engine, MyISAM and innodb.

Answer:

1.MyISAM Non-transactional storage engine, suitable for frequent query applications. Table lock, no deadlock, suitable for small data, small concurrency.

2.innodbIt is a storage engine supporting transactions. It is suitable for applications with more insertion and update operations. If it is designed reasonably, it is line lock (the biggest difference is at the lock level). It is suitable for large data and large concurrency.

What are the data table types?

       Answer: MyISAM, InnoDB, HEAP, BOB, ARCHIVE, CSV, etc.
       MyISAM:Mature, stable, easy to manage, fast to read. Some functions do not support table level locks (transactions, etc.).
       InnoDB:Support transaction, foreign key and other features, data row locking. It takes up a lot of space and does not support full-text indexing.

MySQLHow to optimize the storage of database as a publishing system, with an increment of more than 50,000 items per day, which is expected to be operated and maintained for three years?

a. Designing a good database structure, allowing some data redundancy, avoiding join query as far as possible, and improving efficiency.
b. Choose the appropriate table field data type and storage engine, and add the index appropriately.
c. mysqlThe library master and slave read and write separately.
d. Find regular sub-tables, reduce the amount of data in a single table and improve the query speed.
e。Add caching mechanisms, such as memcached, apc, etc.
f. Pages that don’t change frequently generate static pages.
g. Write efficient SQL. For example, SELECT * FROM TABEL is changed to SELECT field_1, field_2, field_3 FROM TABLE.

For large traffic websites, what methods do you use to solve the statistical problem of page visits?

Answer: a. Verify whether the server can support the current traffic.
b. Optimize database access.
c. External access to links (piracy chains), such as image piracy chains, is prohibited.
d. Control file download.
e. Use different hosts for shunting.
f. Use browsing statistics software to understand the amount of visits and optimize them pertinently.

How to optimize SQL?

Answer:
(1)Choose the right storage engine
Take MySQL as an example, including two storage engines MyISAM and InnoDB, each of which has advantages and disadvantages.
MyISAM
It is suitable for some applications that require a lot of queries, but it is not very good for a lot of write operations. Even if you just need to update a field, the entire table will be locked, while other processes, even the read process, will not be able to operate until the read operation is completed. In addition, MyISAMFor SEThe calculation of LECT COUNT (*) is extremely fast.

InnoDB The trend will be a very complex storage engine, which will be slower than MyISAM for some small applications. But it supports “line lock”, so it will be better when there are more write operations. In addition, he also supports more advanced applications, such as transactions.

(2)Optimize the data type of the field


Keep in mind that the smaller the column, the faster it will be. If a table has only a few columns (such as dictionary tables, configuration tables), then there is no reason to use INT as the primary key.MEDIUMINT, SMALLINT or smaller TINYINTIt will be more economical. If you don’t need to record time, using DATE is better than using DATETIME.Much better. Of course, you also need to leave enough room for expansion.

(3)Adding an index to the search field

Indexes do not necessarily give primary keys or unique fields. If you have a field in your table that you always use to search for, it’s better to index it, unless the field you want to search for is a large text field, then you should create a full-text index.

(4)Avoid Select* The more data you read from the database, the slower the query will be. Moreover, if your database server and WEB server are two separate servers, this will also increase the load of network transmission. Even if you want to query all fields of the data table, try toDon’t use * wildcards. Making good use of the built-in field exclusion definitions may bring more convenience.

(5)Use ENUM instead of VARCHAR

ENUM Types are very fast and compact. In practice, what it keeps isTINYINT, but it appears as a string. In this way, using this field to make a list of options becomes quite perfect. For example, the values of fields such as gender, nationality, department and status are limited and fixed.Well, then, you should use it.ENUM, not VARCHAR.

(6)Use NOT NULL whenever possible

Unless you have a particular reason to use NULL values, you should always keep your fields NOTNULL. NULL actually needs extra space, and when you compare, your program will be more complex.Of course, this doesn’t mean you can’t use it.NULL, the reality is very complex, there will still be some cases, you need to use NULL values.

(7)Fixed-length tables are faster

If all fields in a table are “fixed length”, the whole table will be considered “static” or “static”.”Fixed-length”. For example, there are no fields of the following types in the table:VARCHAR, TEXT, BLOB. As long as you include oneWith these fields, the table is not a “fixed length static table”, so MySQLThe engine will deal with it in another way.

Fixed-length tables improve performance because MySQL searches faster, because these fixed-length tables are easy to calculate the offset of the next data, so naturally they read quickly. And if the field is not fixed length, then every time you want to find the next one, you need the program to find the primary key.

Also, fixed-length tables are more easily cached and reconstructed. However, the only side effect is that a fixed-length field wastes some space, because a fixed-length field allocates so much space whether you don’t use it or not.

How to design a highly concurrent system

① Database optimization, including reasonable transaction isolation level, SQL statement optimization, index optimization

② Use caching to minimize database IO

③ Distributed database, distributed cache

④ Server load balancing

Lock optimization strategy

① Read / write separation

② Sectional locking

③ Reduce lock holding time

④ Multiple threads try to get resources in the same order

And so on, these are not absolute principles, we have to depend on the situation, such as the size of the lock can not be too fine, or there may be too many times of thread locking and release, but the efficiency is not as good as adding a big lock at a time. This part talked to the interviewer for a long time.

The underlying implementation principle and optimization of index

B+Tree, optimized B + tree

Since pointers to the next leaf node are added to all leaf nodes, InnoDB recommends that default self-increasing primary keys be used as primary indexes for most tables.

 In which case the index is set but cannot be used

① LIKE statement beginning with “%”, fuzzy matching

② ORNo index is used before and after the statement

③ Implicit conversion of data types (e.g. varchar without single quotation marks may be automatically converted to int type)

SQLStatement optimization

order byHow to deal with it

alterTry to merge multiple times into one

insertAnd delete also need to be merged

Wait

How to Optimize MySQL in Practice

I answered them in turn according to the following four items, the first of which had the greatest effect, and the latter was getting smaller and smaller.

① SQLOptimizing Statements and Indexes

② Optimization of database table structure

③ Optimizing System Configuration

④ Hardware optimization

sqlMain characteristics of injection

Varieties are numerous, attacks are simple and harmful.

sqlMain hazards of injection

Unauthorized operation of database data

Malicious editing of web pages

Adding System Account or Database User Account Privately

Trojan Horse on Web Page

Method of optimizing database

  1. Choose the most suitable field attributes, minimize the width of the defined field, and set the field as NOTNULL as far as possible. For example,’provinces’,’gender’, ENUM is the best choice.
  2. Use Connection (JOIN) instead of Subquery
  3. Apply UNION instead of manually created temporary tables
  4. transaction processing
  5. Locking tables, optimizing transaction processing
  6. Optimize locking table with foreign key
  7. Index building
  8. Optimizing Query Statements

Describe briefly the differences between index, primary key, unique index and joint index in mysql, and how they affect the performance of database (from both reading and writing)

Index is a special kind of file (the index on InnoDB data table is a part of table space), which contains reference pointers to all records in the data table.

The only task of a normal index (an index defined by keyword KEY or INDEX) is to speed up access to data.

Ordinary indexes allow indexed data columns to contain duplicate values. If it can be determined that a data column will contain only different values from each other, the keyword UNIQUE should be used to define it as a unique index when creating an index for the data column. That is to say, a unique index ensures that the data is recorded.Uniqueness.

Primary key is a special unique index. In a table, only one primary key index can be defined. Primary key is used to uniquely identify a record. Primary key is created by using the keyword PRIMARY KEY.

Indexes can cover multiple data columns, such as INDEX (columnA, columnB) indexes, which are joint indexes.

Indexing can greatly improve the query speed of data, but it will reduce the speed of inserting, deleting and updating tables, because when these write operations are performed, the index files are also operated.

What are the transactions in the database?

Transaction is an orderly set of database operations as a unit. If all operations in a group succeed, the transaction is considered successful, even if only one operation fails, the transaction will not succeed. If all operations are completed, the transaction is committed, and its modifications will affect all other numbersDatabase process. If an operation fails, the transaction will be rolled back and the impact of the operation on the transaction will be cancelled. ACIDFour characteristics: atomicity, isolation, consistency and persistence.

Know about XSS attacks? How to prevent?

XSSIt is a cross-site script attack. First, it uses cross-site script vulnerabilities to execute scripts constructed by attackers in a privileged mode, and then uses unsafe Activex controls to execute malicious actions.
The htmlspecialchars () function is used to filter the submitted content and materialize the special symbols in the string.

SQLCauses of injection vulnerabilities? How to prevent?

SQLReasons for injection: In the process of program development, SQL statements are not written properly and special characters are filtered, which results in clients submitting some SQL statements for normal execution through global variables POST and GET.

How to prevent SQL injection:
Open the magic_quotes_gpc and magic_quotes_runtime settings in the configuration file

Use addslashes to convert SQL statements when executing SQL statements

SqlDo not omit double and single quotation marks in sentence writing.

Filter out some key words in SQL statements: update, insert, delete, select,*.

Improving the naming skills of database tables and fields, naming some important fields according to the characteristics of the program, which is not easy to guess.

PhpSet register_globals to off in configuration file to close global variable registration

Control error information, do not output error information on the browser, write error information to the log file.

Select the appropriate data type for the fields in the table (physical design)

 Field type priority: shaping & gt; date, time & gt; enum, char & gt; varchar & gt; blob, text
 Priority should be given to the number type, followed by the date or binary type, and finally to the string type. For the data type of the same level, priority should be given to the data type that occupies less space.

Storage period

Datatime:Storage time in YYYY-MM-DDDHH:MM:SS format, accurate to seconds, occupies 8 bytes of storage space, and data time type is independent of time zone.
Timestamp:Stored in timestamp format, it takes 4 bytes, ranging from 1970-1-1 to 2038-1-19. The display depends on the specified time zone. By default, the value of timestamp column can be automatically modified when the data of the first column is modified.
Date:(Birthday) takes up fewer bytes than using string. datatime. int to store. Date only takes 3 bytes to store date and month. Date-time function can also be used to calculate date and time.
Time:Storage time partially acquired data
Note: Do not use string types to store date and time data (usually less storage space than strings, and you can use date function for lookup filtering)
Using int to store date and time is not as good as using timestamp type

For relational databases, index is a very important concept. Please answer some questions about index.

a)、What is the purpose of the index?
Quick access to specific information in data tables to improve retrieval speed

Create a unique index to ensure the uniqueness of each row of data in the database table.

Connection between accelerometers and tables

Using grouping and ranking clauses in data retrieval can significantly reduce the time of grouping and ranking in queries.

b)、What are the negative effects of indexing on database systems?
Negative effects:
It takes time to create and maintain indexes, which increases with the increase of data volume; indexes need to occupy physical space, not only tables, but also each index needs to occupy physical space; when tables are added, deleted, changed, indexes need to be maintained dynamically, which reduces the number of tables.According to the maintenance speed.

c)、What are the principles for indexing data tables?
Index the most frequently used fields to narrow the query scope.

Index frequently used fields that need to be sorted

d)、 Under what circumstances is it inappropriate to establish an index?
For columns rarely involved in the query or columns with more duplicate values, it is not appropriate to establish an index.

For some special data types, it is not appropriate to establish an index, such as text, etc.

 Brief description of the difference between MyISAM and InnoDB in MySQL database

The most important feature that distinguishes it from other databases is its plug-in table storage engine. Keep in mind: The storage engine is based on tables, not databases.

InnoDBDifferentiation from MyISAM:

InnoDBStorage Engine: Mainly for OLTP (Online Transaction Processing, Online Transaction Processing) applications, is the first complete ACID transaction support storage engine (BDB, the first transaction support storage engine, has stopped development.)

Characteristic:

· Line locks design, support foreign keys, support transactions, support concurrency, lock granularity is to support mvcc line-level locks;

 MyISAMStorage Engine: MySQL Official Storage Engine, mainly for OLAP (Online Analytical Processing, Online Analytical Processing) applications.
Characteristic:

Transaction is not supported. Lock granularity supports concurrent insertion of table-level locks, table-level locks and full-text indexing. The operation speed is fast and can not read and write too frequently.

 Explain the differences between MySQL external connection, internal connection and self-connection

First, what is cross join: cross join is also called Cartesian product. It means that all records in one table are matched directly with all records in another table without any conditions.

Internal connection is a cross connection with only conditions. According to a certain condition, qualified records are screened out. Non-qualified records will not appear in the result set, that is, internal connection only connects matched rows.
The result set of an external join contains not only rows that meet the join criteria, but also left, right, or two tables.
All data rows, in turn, are called left outer connection, right outer connection, and external connection.

Left outer join, also known as left join, left table as the main table, all records in the left table will appear in the result set, for those records that do not match in the right table, still need to show that the corresponding field values on the right are filled with NULL. Right outer connection, also known as right connection, the right table is the main table, all of the right tableRecords will appear in the result set. Left and right connections are interchangeable, and MySQL does not currently support full external connections.

Write out the names of more than three MySQL database storage engines (hint: case-insensitive)

MyISAM、InnoDB、BDB(BerkeleyDB)、Merge、Memory(Heap)、Example、Federated、
Archive、CSV、Blackhole、MaxDB Wait a dozen engines.

MyqlOverview of transaction rollback mechanism in

Transaction is a user-defined sequence of database operations, which are either done or not done at all. Transaction rollback refers to the revocation of the update operation of the database that the transaction has completed.

To modify two different tables in the database at the same time, if they are not a transaction, when the first table is modified, there may be an exception in the process of modifying the second table that can not be modified. At this time, only the second table is still the state before the modification, and the first table has been modified. And when you put them inWhen set as a transaction, when the first table is modified, the second table is modified abnormally and can not be modified. The first table and the second table are both returned to the state of unchanged, which is called transaction rollback.

SQLWhat are the parts of language? What operation keywords do each part have?

Answer: The SQL language includes four parts: Data Definition (DDL), Data Manipulation (DML), Data Control (DCL) and Data Query (DQL).

Data definitions: Create Table, Alter Table, Drop Table, Craete/Drop Index, etc.

Data manipulation: Select, insert, update, delete,

Data control: grant, revoke

Data query: select

What are integrity constraints?


Answer: Data Integrity refers to the Accuracy and Reliability of data.

It can be divided into the following four categories:

1) Entity integrity: specifies that each row of the table is the only entity in the table.

2) Domain Integrity: A column in a table must satisfy certain data type constraints, which include the range and accuracy of values.

3) Reference Integrity: It means that the data of the primary and foreign keywords of two tables should be consistent, which ensures the consistency of data between tables and prevents data loss or meaningless data from spreading in the database.

4) User-defined integrity: Different relational database systems often need some special constraints according to their application environments. User-defined integrity refers to the constraints of a specific relational database, which reflects the semantic requirements that a specific application must meet.

Table-related constraints: including column constraints (NOT NULL (non-null constraints) and table constraints (PRIMARY KEY, foreign key, check, UNIQUE).

What is a transaction? And its characteristics?


Answer: Transaction: is a series of database operations, is the basic logical unit of database application.

Transaction characteristics:

(1)Atomicity: That is, indivisibility. Transactions are either all executed or none executed.

(2)Consistency or serialization. Transaction execution causes the database to change from one correct state to another

(3)Isolation. No changes to the data made by the transaction are allowed to be made to any other transaction until the transaction is submitted correctly.

(4) Persistence. After the transaction is committed correctly, the result will be stored in the database permanently. Even if there are other failures after the transaction is committed, the result of the transaction will be saved.

Or so to understand:

Transactions are groups of SQL statements bound together as a logical unit of work. If any statement operation fails, the whole operation will fail. Later, the operation will roll back to the pre-operation state or have a node. To ensure either execution or non-execution, transactions can be used. wantConsidering a group of statements as transactions, you need to pass ACID tests, namely atomicity, consistency, isolation and persistence.

What is a lock?


  Answer: The database is a shared resource used by multiple users. When multiple users access data concurrently, there will be multiple transactions accessing the same data at the same time in the database. If concurrent operations are not controlled, incorrect data may be read and stored, and the consistency of the database may be destroyed.

Locking is a very important technology to realize database concurrency control. When a transaction operates on a data object, it first makes a request to the system and locks it. After the lock, the transaction has some control over the data object. Before the transaction releases the lock, other transactions can’t do more about the data object.New operation.

Basic Lock Types: Locks include row-level locks and table-level locks

What is a view? What is the cursor?


Answer: Views are virtual tables with the same functions as physical tables. Views can be added, changed, checked and manipulated. Views usually have a table or a subset of rows or columns of multiple tables. Modifications to views do not affect base tables. It makes it easier for us to get data than multi-table queries.

  Cursor: The query result set is treated as a unit effectively. A cursor can specify a particular row in the cell and retrieve one or more rows from the current row of the result set. You can modify the current row of the result set. In general, cursors are not used, but cursors are very important when data needs to be processed one by one.。

What is a stored procedure? With what to call?


Answer: Stored procedure is a precompiled SQL statement. Its advantage is that it allows modular design. That is to say, it only needs to be created once, and then it can be called many times in the program. If an operation needs to execute multiple times of SQL, it is faster to execute with stored procedures than with simple SQL statements. You can use a command objectTo call stored procedures.

What is the role of indexing? And what are its advantages and disadvantages?


Answer: Index is a special query table, which can be used by database search engines to speed up data retrieval. It’s very similar to the catalogue of books in real life. You don’t need to query the whole book to find the data you want. Indexes can be unique, and creating an index allows you to specify a single column or multiple columns. lackThe point is that it slows down the speed of data entry, but also increases the size of the database.

How to understand the three paradigms in a popular way?  


Answer: The first paradigm: 1NF is an atomic constraint on attributes, requiring attributes to be atomic and not to be decomposed again.

The second paradigm: 2NF is a constraint on the uniqueness of records, requiring that records have a unique identity, that is, the uniqueness of entities;

The third paradigm: 3NF is a constraint on field redundancy, that is, no field can be derived from other fields, it requires no field redundancy.

Advantages and disadvantages of paradigm design:

excellentPoints:

Data redundancy can be minimized to make updates faster and smaller.

Disadvantage: For queries, multiple tables are needed for association, which reduces the efficiency of writing, increases the efficiency of reading, and makes index optimization more difficult.

Anti-paradigm:

Advantages: It can reduce table Association and optimize index better.

Disadvantages: Data redundancy and data anomalies require more cost to modify data

What is the basic table? What is a view?


Answer: A basic table is a table that exists independently of itself. In SQL, a relationship corresponds to a table. Views are tables derived from one or more basic tables. The view itself is not stored independently in the database, it is a virtual table.

Explain the advantages of views?


Answer: (1) View can simplify user’s operation; (2) View enables users to view the same data from multiple perspectives; (3) View provides a certain degree of logical independence for the database; (4) View can provide security protection for confidential data.

 NULLWhat do you mean?


Answer: The NULL value denotes UNKNOWN (unknown): it does not denote “(empty string). Any comparison of NULL values produces a NULL value. You can’t compare any value with a NULL value and logically hope to get an answer.

Use IS NULL for NULL judgment

What are the differences between primary keys, foreign keys and indexes?


Differences between primary keys, foreign keys and indexes

Definition:

 Primary key – uniquely identifies a record, cannot be duplicated, not allowed to be empty

 Foreign Key – The foreign key of a table is the primary key of another table, and the foreign key can be duplicated or null.

 Index – This field has no duplicate values, but can have a null value

Effect:

 Primary key – used to ensure data integrity

 Foreign Key – Used to connect with other tables

 Indexing – Improves the speed of query sorting

Number:

 Primary key – Primary key can only have one

 Foreign keys – A table can have multiple foreign keys

 Index – A table can have multiple unique indexes

What can you use to ensure that the fields in the table only accept values in a specific range?


Answer: Check restriction, which is defined in the database table, is used to restrict the input value of the column.

Triggers can also be used to limit the acceptable values of fields in database tables, but this approach requires that triggers be defined in tables, which may affect performance in some cases.

What are the ways to optimize SQL statements? (Choose a few)


(1)WhereIn the clause: The connections between where tables must be written before other Where conditions, and those conditions that filter out the largest number of records must be written at the end of Where clause. HAVING finally.

(2)Use EXISTS instead of IN and NOT EXISTS instead of NOT IN.

(3) Avoid computing on index columns

(4)Avoid using IS NULL and IS NOT NULL on index columns

(5)In order to optimize queries, we should avoid scanning the whole table as much as possible. First, we should consider indexing the columns involved in where and order by.

(6)Null value judgment of fields in where clause should be avoided as far as possible. Otherwise, it will cause engine to abandon indexing and scan the whole table.

(7)As far as possible, avoid expressing fields in the where clause, which will cause the engine to abandon indexing and scan the whole table.

SQLWhat’s the difference between’related sub-query’and’non-related sub-query’ in sentences?


Answer: Subqueries: Queries nested in other queries are called queries.

Subqueries are also called internal queries, while statements containing subqueries are called external queries (also known as main queries).

All sub-queries can be divided into two categories: related sub-queries and unrelated sub-queries.

(1)Non-correlated sub-queries are independent of external queries. The sub-queries are executed once and the values are passed to external queries after execution.

(2)The execution of related sub-queries depends on the data of external queries, and the sub-queries are executed once when an external query executes a row.

Therefore, non-correlated sub-queries are more efficient than correlated sub-queries.

charWhat’s the difference between varchar and varchar?


Answer: It’s a fixed-length type, while varchar is a variable-length type.

char(M)In a data column of type M, each value occupies M bytes. If a length is less than M, MySQL will fill it with space characters on the right side. (The space characters filled in in the retrieval operation will be removed.) In a data column of type varchar (M), each value occupies just enough.The byte used is added with a byte used to record its length (that is, the total length is L + 1 byte).

varcharApplicable scenarios:

The maximum length of a string column is much longer than the average length. 2. Strings are rarely updated, resulting in storage fragments. 3. Storage of strings using multi-byte character sets

CharScene:

    Storage has approximate length (md5 value, ID card, mobile phone number), shorter length than string (because varchar needs extra space to record string length), more suitable for frequent updates of string, update will not occur when page splitting, avoid storage fragments, get better IO performance

 

 Please reserve the page address for reprinting: http://youzhixueyuan.com/mysql-answers-to-interview-questions.html

Leave a Reply

Your email address will not be published. Required fields are marked *