Writing in the front

Arrange some interview questions with Internet companies, these interview questions are often asked, also as a Java engineer need to master the knowledge, after all, the combination of theory and practice, is king, fragmentation, eat some knowledge every day, happy every day, if you have any help to you, remember the point attention and point oh.

Related articles

MyBatis (MyBatis) (ZooKeeper) (Dubbo) (Elasticsearch) (Redis) (MySQL) (Dubbo) Java concurrent programming interview questions (1)

What types of locks are available in MySQL?

  • Table lock: low overhead, fast lock; No deadlocks occur; The lock granularity is large, and the probability of lock conflict is high and the concurrency is low.
  • Row-level lock: expensive, slow lock; Deadlocks occur; The lock granularity is the lowest, the probability of lock conflict is the lowest, and the concurrency is the highest.
  • Page lock: the overhead and lock time are between table lock and row lock. Deadlocks occur; The locking granularity is between table locks and row locks, and the concurrency is average.

What are the different tables in MySQL?

There are five types of tables:

  • MyISAM
  • Heap
  • Merge
  • INNODB
  • ISAM

Describe the difference between MyISAM and InnoDB in MySQL database

MyISAM:

  • Transactions are not supported, but each query is atomic;
  • Supports table-level locking, that is, each operation locks the entire table.
  • The total number of rows stored in the table;
  • A MYISAM table has three files: index file, table structure file, data file;
  • With a non-clustered index, the data domain of the index file stores Pointers to the data file. Secondary indexes are basically the same as primary indexes, but secondary indexes are not guaranteed to be unique.

InnoDb:

  • Acid-supported transactions, which support four isolation levels of transactions;
  • Row-level locking and foreign key constraints are supported, so write concurrency is supported.
  • Total number of rows not stored:
  • An InnoDb engine is stored in one file space (shared table space, table size is not controlled by the operating system, a table may be distributed in multiple files), may be multiple (set to independent table empty, table size is limited by the operating system file size, generally 2G), by the operating system file size limit.
  • A primary key index uses a clustered index (the data field of the index stores the data file itself), and a secondary index’s data field stores the value of the primary key. Therefore, to search data from the secondary index, you need to find the primary key through the secondary index and then access the secondary index. It is best to use auto-increment primary keys to prevent large file adjustments when inserting data to maintain the B+ tree structure.

What are the four transaction isolation levels InnoDB supports in MySQL?

The four isolation levels defined by the SQL standard are:

  • Read uncommited: Uncommitted data is read
  • Read COMMITTED: Indicates dirty reads and cannot be committed
  • Repeatable read: repeatable
  • Serializable: Serial things

The difference between CHAR and VARCHAR?

  • The CHAR and VARCHAR types differ in storage and retrieval
  • When CHAR values are stored, they are filled with Spaces to a specific length. Trailing Spaces are removed when retrieving CHAR values.

What’s the difference between primary keys and candidate keys?

Each row of a table is uniquely identified by a primary key, and a table has only one primary key. Primary keys are also candidates. By convention, candidate keys can be specified as primary keys and can be used for any foreign key reference.

What is Myisamchk used for?

It is used to compress MyISAM tables, which reduces disk or memory usage.

What is the difference between MyISAM Static and MyISAM Dynamic?

All fields on MyISAM Static have fixed widths. Dynamic MyISAM tables will have fields like TEXT, BLOB, and so on to accommodate data types of different lengths. MyISAM Static is easier to recover from damage.

What happens if a table has a column defined as TIMESTAMP?

The timestamp field gets the current timestamp whenever the row is changed.

What happens if the maximum value in the table is reached when the column is set to AUTO INCREMENT? It stops incrementing, and any further inserts will generate an error because the key has already been used.

How do I find out which automatic increment was allocated on the last insert? LAST_INSERT_ID returns the last value assigned by Auto_increment and does not need to refer to the table name.

How do you see all the indexes defined for the table?

Indexes are defined for tables in the following way:

SHOW INDEX FROM <tablename>;
Copy the code

What do % and _ mean in the LIKE declaration?

% corresponds to zero or more characters, and _ is just one character in a LIKE statement.

How to convert between Unix and MySQL timestamps? FROM_UNIXTIME converts a Unix timestamp from a MySQL timestamp to a MySQL timestamp

What is the column comparison operator?

Use the =, <>, <=, <, > =, >, <<, >, <=>, AND, OR, OR LIKE operators in column comparisons in SELECT statements.

What’s the difference between BLOB and TEXT?

A BLOB is a binary object that can hold a variable amount of data. TEXT is a case insensitive BLOB.

The only difference between BLOB and TEXT types is that BLOB values are size sensitive when sorting and comparing BLOB values, and TEXT values are case insensitive.

What is the difference between MySQL_fetch_array and MySQL_fetch_object?

MySQL_fetch_array differs from MySQL_fetch_object:

  • MySQL_fetch_array – Returns the result row as an associative array or as a regular array from the database.
  • MySQL_fetch_object – Returns the result row as an object from the database.

Where will the MyISAM table be stored and its storage format provided?

Each MyISAM table is stored on disk in three formats:

  • "FRM".File storage table definition
  • Data files have”.MYD“(MYData) extension
  • Index files have”.MYI“(MYIndex) extension

How does MySQL optimize DISTINCT?

DISTINCT is converted to GROUP BY on all columns and is used in combination with the ORDER BY clause.

SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;
Copy the code

How do I display the first 50 lines?

In MySQL, query to display the first 50 lines using the following code:

SELECT*FROM LIMIT 0.50;
Copy the code

How many columns can be used to create an index?

Up to 16 index columns can be created for any standard table.

What’s the difference between NOW () and CURRENT_DATE ()?

  • NOW ()This command is used to display the current year, month, date, hour, minute, and second.
  • CURRENT_DATE ()Displays only the current year, month, and date.

What is a nonstandard string type?

  • TINYTEXT
  • TEXT
  • MEDIUMTEXT
  • LONGTEXT

What are generic SQL functions?

  • CONCAT(A, B)– Concatenate two string values to create a single string output. Usually used to combine two or more fields into a single field.
  • FORMAT(X, D)– Format the valid numbers from X to D.
  • CURRDATE(), CURRTIME()– Returns the current date or time.
  • NOW ()– Returns the current date and time as a value.
  • MONTH (), DAY (), YEAR (), WEEK (), WEEKDAY ()– Extracts the given data from the date value.
  • HOUR (), MINUTE (), SECOND ()– Extracts the given data from the time value.
  • DATEDIFF (A, B)— Determine the difference between two dates, usually used to calculate age
  • SUBTIMES (A, B)— Determine the differences between the two.
  • FROMDAYS (INT)– Converts integer days to date values.

Does MySQL support transactions?

By default, MySQL is in autoCOMMIT mode. All database updates are committed immediately, so MySQL does not support transactions by default. If your MySQL table type is InnoDB Tables or BDB Tables, your MySQL can use transactions. If your MySQL table type is InnoDB Tables or BDB Tables, your MySQL can use transactions. If your MySQL table type is InnoDB Tables, your MySQL can use transactions. In non-autocommit mode, you must COMMIT your changes using COMMIT, or ROLLBACK your changes with ROLLBACK.

What is the best field type in MySQL to record currency

The NUMERIC and DECIMAL types are implemented by MySQL as the same type, as permitted by the SQL92 standard. They are used to hold values whose precise accuracy is extremely important, such as data related to money. Precision and size can be (and usually are) specified when declaring a class to be one of these types. Such as:

salary DECIMAL(9.2)
Copy the code

In this example, 9(precision) represents the total number of decimal places that will be used to store values, while the 2(scale) surrogate table will be used to store the number of places after the decimal point. So, in this case, the range of values that can be stored in the SALARY column is from -9999999.99 to 9999999.99.

MySQL > select * from ‘MySQL’ where ‘privileges’ are stored.

The MySQL server controls user access to the database through the permission table, which is stored in the MySQL database and initialized by the MySQL_install_db script. These permission tables are user, DB, table_priv, columns_priv, and host.

What can be the string type of a column?

The string type is:

  • SET
  • BLOB
  • ENUM
  • CHAR
  • TEXT

MySQL database is used as the storage of the release system, with more than 50,000 increments per day. It is expected to be operated and maintained for three years. How to optimize?

  • Well-designed database structure allows partial data redundancy, avoids join query as far as possible, and improves efficiency.
  • Select the appropriate table field data type and storage engine, and add indexes as appropriate.
  • MySQL library master separated from read and write.
  • Find regular table, reduce the amount of data in a single table to improve the query speed.
  • Add caching mechanisms such as memcached, APC, etc.
  • Pages that do not change often, generate static pages.
  • Write efficient SQL. Such asSELECT * FROM TABEL SELECT field_1, field_2, fieldd_3 FROM TABLE.

Optimization strategies for locking

  • Reading and writing separation
  • Subsection locking
  • Reduces the duration of lock holding
  • Multiple threads should acquire resources in the same order as far as possible. The granularity of locks should not be too refined, otherwise threads may lock and release too many times, but the efficiency is not as good as adding a large lock at a time.

The underlying implementation principles and optimizations of indexes

B+ trees are optimized to add a pointer to the next leaf node in each leaf node, so InnoDB recommends that most tables use the default primary key increment as the primary index.

When is an index set but not available

  • LIKE statements beginning with “%”, fuzzy match
  • The index is not used before OR after the OR statement
  • Implicit conversion of data type (vARCHar may be automatically converted to int if not quoted)

How to optimize MySQL in practice

It is best to optimize in the following order:

  • SQL statement and index optimization
  • Optimization of database table structure
  • Optimization of system configuration
  • Hardware optimization

MySQL slow query optimization, index optimization, and table optimization summary

Methods to optimize the database

  • Select the field attributes that are most applicable, define the field width as little as possible, and set the field as much as possibleNOTNULLFor example, ‘province’ and ‘gender’ are bestENUM
  • Use joins instead of subqueries
  • Instead of manually created temporary tables, use unions
  • Transaction processing
  • Lock tables and optimize transaction processing
  • Apply foreign key, optimize lock table
  • indexing
  • Optimized query statement

MySQL > select key (s), primary key (s), unique index (s), and joint index (s)

Indexes are special files (indexes on InnoDB tables are part of the table space) that contain Pointers to all the records in the table.

The only job of a plain INDEX (an INDEX defined by the KEY or INDEX keyword) is to speed up access to data.

Normal indexes allow indexed data columns to contain duplicate values. If it is certain that a data column will contain only those values that differ from each other, you should define it as a UNIQUE index when creating an index for that data column using the keyword UNIQUE. In other words, a unique index guarantees the uniqueness of the data record.

A PRIMARY KEY is a special unique index. Only one PRIMARY KEY can be defined in a table. A PRIMARY KEY is used to uniquely identify a record.

An INDEX can cover multiple data columns, such as the INDEX(columnA, columnB) INDEX, which is a federated indexing.

Indexes can greatly improve the speed of querying data, but slow down the speed of inserting, deleting, and updating tables because of the need to manipulate index files while performing these writes.

What are transactions in a database?

A transaction is an ordered set of database operations as a unit. The transaction is considered successful if all operations in the group succeed, even if only one operation fails. If all operations are complete, the transaction commits and its changes apply to all other database processes. If an operation fails, the transaction is rolled back and the effects of all operations on the transaction are cancelled.

Transaction features:

  • Atomicity: Indivisibility, where all transactions are executed or none are executed.
  • Consistency or seriability. The execution of the transaction causes the database to transition from one correct state to another
  • Isolation. Any changes made to data by that transaction are not allowed to be made available to any other transaction until the transaction has committed correctly,
  • Persistence. Once a transaction commits correctly, its results are permanently stored in the database, even if other failures occur after the transaction commits.

Alternatively, a transaction is a group of SQL statements bound together as a logical unit of work. If any of the statements fails, the entire operation fails, and then the operation is rolled back to its previous state or has a node on it. Transactions can be used to ensure that either they are executed or they are not. To consider groups of statements as transactions, you need to pass the ACID test, which is atomicity, consistency, isolation, and persistence.

What is the cause of SQL injection vulnerability? How to prevent it?

The cause of SQL injection: During the process of program development, the standard WRITING of SQL statements and the line filtering of special characters are not paid attention to. As a result, the client can submit some SQL statements through the global variables POST and GET for normal execution.

Ways to prevent SQL injection: Enable magic_QUOtes_GPC and magic_QUOtes_Runtime in the configuration file. Use addslashes to convert SQL statements. Do not omit double quotation marks and single quotation marks when writing SQL statements.

Filter out some keywords in SQL statements: UPDATE, INSERT, delete, select, *.

Improve the database table and field naming skills, for some important fields according to the characteristics of the program named, not easy to guess.

Select the appropriate data type for the fields in the table

Field type priority: Integer > Date,time>enum,char> VARCHar >blob,text takes precedence over numeric type, date or binary type, and string type

Storage period

  • DatatimeTo:YYYY-MM-DD HH:MM:SSFormat Storage period time, accurate to the second, takes up 8 bytes of storage space. Datatime type is independent of time zone
  • Timestamp: Stored in Timestamp format, occupying 4 bytes and 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) number of bytes occupied than usedString. Datatime. IntSave less, use date only need 3 bytes, store the date month, also can use the date time function to calculate the day period
  • Time: stores Time data. Note: Do not use strings to store date and time data (usually takes up less storage space than strings, you can use the date function for lookup filtering). Using int to store dates and times is not as good as using timestamp

Indexes are an important concept for relational databases. Please answer some questions about indexes:

  • What is the purpose of the index?

Fast access to specific information in a data table to speed up retrieval create unique indexes to ensure the uniqueness of each row of data in a database table. Accelerated tables and joins between tables can significantly reduce grouping and sorting time in queries when grouping and sorting clauses are used for data retrieval

  • What are the negative effects of indexes on database systems?

Negative: Creating and maintaining indexes takes time, which increases with the volume of data; Indexes need to occupy physical space. Not only tables need to occupy data space, but also each index needs to occupy physical space. Indexes also need to be maintained dynamically when tables are added, deleted, or modified, which reduces the maintenance speed of data.

  • What are the principles for indexing data tables?

Build indexes on the most frequently used fields to narrow the query. Build indexes on frequently used fields that need to be sorted

  • When is it inappropriate to build an index?

Indexes are not appropriate for columns that are rarely involved in a query or that have a lot of duplicate values. For some special data types, such as text fields, indexes are not appropriate

Explain the difference between external join, inner join and self-join in MySQL

Cross join: A cross join, also called a Cartesian product, matches all the records in one table with all the records in another table without using any criteria.

The inner join is a cross-join with only conditions. Records that meet the conditions are screened out according to a certain condition. Records that do not meet the conditions will not appear in the result set, that is, the inner join only connects the matched rows. The result set of an outer join contains not only the rows that meet the join conditions, but also all data rows in the left table, right table or two tables. These three cases are called left outer join, right outer join, and full outer join in turn.

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

Overview of the transaction rollback mechanism in Myql

A transaction is a sequence of database operations defined by users. These operations either do all or do not do all, which is an inseparable unit of work. Transaction rollback refers to the cancellation of the update operation to the database that has been completed by the transaction.

To modify two different tables in the database at the same time, if they are not a transaction, when the first table is modified, the second table may be modified in the process of exception and failed to modify, at this time only the second table according to the old is not modified before the state, and the first table has been modified. When you set them as a transaction, when the first table is modified and the second table fails to be modified, both the first table and the second table return to the unmodified state. This is called transaction rollback

What are the parts of SQL? What are the operation keys for each section?

SQL language includes data definition (DDL), data manipulation (DML), data control (DCL) and data query (DQL).

  • Data Definition:Create Table,Alter Table,Drop Table, Craete/Drop Index 等
  • Data manipulation:Select ,insert,update,delete.
  • Data control:grant,revoke
  • Data query:select

What are integrity constraints?

Data Integrity refers to the Accuracy and Reliability of Data. Divided into the following four categories:

  • Entity integrity: Specifies that each row of a table is a unique entity in the table.
  • Field integrity: Indicates that columns in a table must meet certain data type constraints, including value range and precision.
  • Referential integrity: the data of the primary and external keywords of two tables should be consistent to ensure data consistency between tables and prevent data loss or meaningless data from spreading in the database.
  • User-defined integrity: Different relational database systems require special constraints depending on the context in which they are used. User-defined integrity is a constraint for a specific relational database, which reflects the semantic requirements that a specific application must meet.

    Table related constraints: including column constraints (NOT NULL,Non-empty constraints) and table constraints (PRIMARY KEY, Foreign KEY, Check, UNIQUE).

What is a lock?

A: A database is a shared resource that is used by multiple users. When multiple users concurrently access data, there will be multiple transactions accessing the same data simultaneously in the database. Uncontrolled concurrent operations can read and store incorrect data, breaking the consistency of the database.

Locking is a very important technology to realize database concurrency control. A transaction makes a request to the system to lock a data object before performing an operation on it. After the lock is locked, the transaction has some control over the data object, and no other transaction can update the data object until the transaction releases the lock.

Basic lock types: Locks include row-level locks and table-level locks

What is a view? What is a cursor?

A view is a virtual table that has the same functions as a physical table. You can add, modify, query, and manipulate views, which are usually rows or subsets of columns in one or more tables. Changes to the view do not affect the base table. It makes it easier for us to get data compared to multi-table queries.

Cursors are the efficient processing of a query 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.

What is a stored procedure? With what?

A: 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. A stored procedure can be invoked with a command object.

How to understand the three paradigms popularly?

  • The first normal form: 1NF is the atomicity constraint on attributes, which requires attributes to have atomicity and cannot be decomposed again.
  • Second normal form: 2NF is a constraint on the uniqueness of records, requiring records to have a unique identity, that is, the uniqueness of entities;
  • Third normal form: 3NF is a constraint on field redundancy, that is, no field can be derived from another field, it requires that the field have no redundancy.

Advantages and disadvantages of formal design:

Advantages: Reduce data redundancy as far as possible, fast update, and small size. Disadvantages: Multiple tables are required for query association, which reduces write efficiency and increases read efficiency, and makes index optimization more difficult

Disadvantages: Data redundancy and data abnormality, data modification needs more cost

What is a basic table? What is a view?

A: A base table is a table that stands on its own. In SQL, a relationship corresponds to a table. A view is a table exported from one or more base tables. The view itself is not stored independently in the database and is a virtual table

What are the advantages of trial views?

  • Views simplify user operations
  • Views enable users to view the same data from multiple perspectives;
  • Views provide a degree of logical independence for the database;
  • Views provide security for confidential data.

What does NULL mean

Answer: NULL this value stands for UNKNOWN: it does not stand for “” (empty string). Any comparison of the NULL value produces a NULL value. You cannot compare any value to a NULL value and logically expect an answer. Use IS NULL for NULL determination

What is the difference between a primary key, a foreign key, and an index?

Differences between primary keys, foreign keys, and indexes

  • Definition:

– THE foreign key of a table is the primary key of another table. The foreign key can have duplicate or null values. – This field has no duplicate value, but it can have a null value

  • Function:

Primary keys – foreign keys used to ensure data integrity – indexes used to relate to other tables – are used to speed query sorting

  • The number:

Primary key – A primary key can have only one foreign key – A table can have multiple foreign key indexes – a table can have multiple unique indexes

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

A: Check limits, which are defined in the database table to limit the values entered for that column. Triggers can also be used to limit the values that fields in a database table can accept, but this approach requires triggers to be defined in the table, which can affect performance in some cases.

What are the methods for SQL statement optimization? (Select a few)

  • Joins between Where tables must precede other Where conditions, and conditions that filter out the maximum number of records must precede the end of the Where clause.HAVINGAt last.
  • withEXISTSalternativeIN, withNOT EXISTSalternativeNOT IN.
  • Avoid calculations on indexed columns
  • Avoid using it on indexed columnsIS NULL and IS NOT NULL
  • To optimize the query, avoid full table scan as far as possible, first consider inwhere 及 order byCreate indexes on the columns involved.
  • Try to avoid null values for fields in the WHERE clause, as this will cause the engine to abandon the index for a full table scan
  • Expression operations on fields in the WHERE clause should be avoided as much as possible, which can cause the engine to abandon indexes for a full table scan