concept

Do you understand the database index?

Generally speaking, the underlying data structure
        There are two common mysql structures: Hash index, B+ Tree index and full text index. We use innoDB engine,
        The default is B + tree.

        Differences between the two structures:
        The bottom layer of hash index is hash table. Hash table is an organization that stores data in the form of key value, so multiple data have no storage relationship at all
        Because of any order relationship, interval queries cannot be queried directly through the index, so full table scanning is required. Hash tables are only applicable to the scenario of equivalent queries.
        B+ Tree is a multi-channel balanced query tree, so its nodes are naturally ordered. There is no need to scan the whole table for range query.

What are the classifications of indexes?

Classification by data structure:
        B+ Tree index
        Hash index
        Full text index
        Classification by physical storage:
        Cluster index
        Secondary index (secondary index)
        Classification by field properties:
        primary key 
        General index
        Prefix index
        Classification by number of fields:
        single column 
        Joint index (composite index, composite index)

  Scan VX for Java data, front-end, test, python and so on

Can gender and time be indexed?

Gender: indexing is not recommended
  Fields with high value repetition rate are not suitable for indexing. for example**Gender status,Low value dictionary**
  Accessing the index requires additional costs IO Overhead, all you get from the index is the address. If you want to really access the data, you still have to edit the table once IO. 
    If you want to get several data from the 1 million rows of data in the table, use the index to quickly locate and access this part of the index IO It's worth it.
    However, if you take 500000 rows of data from 1 million rows of data, such as the gender field, you need to access the index 500000 times and then access the table 500000 times. The combined cost is not less than a complete scan of the table directly.
Time: Yes
  Time columns are suitable for using clustered indexes because they have few duplicate values and are often used for sorting and range filtering.

reference material:

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

Cross connect definition: 
  Cross connection is also called Cartesian product. It means that all records in one table are directly matched with all records in another table without using any conditions.

1. Inner connection 
  It is a cross connection with only conditions, and the qualified records are filtered according to a certain condition,
  Records that do not meet the criteria will not appear in the result set, that is, inner joins only connect the matching rows.
2. External connection 
  The result set contains not only the rows that meet the join criteria, but also the rows in the left table, the right table, or both tables
  For all data rows, these three cases are called left outer connection, right outer connection, and all outer connection.

  Left outer connection
    It is also called left join. The left table is the main table, and all records in the left table will appear in the result set,
    For those records that do not match in the right table, they still need to be displayed, and the corresponding field values on the right are displayed in NULL To fill in.
  Right outer connection
    It is also called right join. The right table is the main table, and all records in the right table will appear in the result set.
    The left and right connections are interchangeable, MySQL At present, all external connections are not supported.

What kinds of locks are there in MySQL?

1,Table level lock: low overhead and fast locking; No deadlock; The locking granularity is large, the probability of lock conflict is the highest, and the concurrency is the lowest.
2,Row level lock: high overhead and slow locking; Deadlock will occur; The locking granularity is the smallest, the probability of lock conflict is the lowest, and the concurrency is the highest.
3,Page lock: the overhead and locking time are bounded between table lock and row lock; Deadlock will occur; The locking granularity is between table lock and row lock, and the concurrency is general.

What are the different tables in MySQL?

There are 5 types of tables:
1,MyISAM
2,Heap
3,Merge
4,INNODB
5,ISAM

Briefly describe the difference between MyISAM and InnoDB in MySQL database?

MyISAM: 
1. Transaction is not supported, but each query is atomic;
2. Table level locks are supported, that is, the whole table is locked for each operation;
3. Total rows of the storage table;
One MYISAM The table has three files: index file, table structure file and data file; Using a clustered index,
  The data field of the index file stores a pointer to the data file. The secondary index is basically the same as the primary index, but the uniqueness of the secondary index is not guaranteed.

InnoDb: 
1. support ACID Four isolation levels of transactions are supported;
2. Support row level locks and foreign key constraints: therefore, write concurrency can be supported;
3. Total rows not stored:
One InnoDb The engine is stored in a file space (shared table space), and the table size is not controlled by the operating system,
  A table may be distributed in multiple files, or it may be multiple (set as an independent table empty. The table size is limited by the file size of the operating system, generally 2 G)
  ，Limited by the file size of the operating system; the primary key index adopts the clustered index (the data field of the index stores the data file itself)
  ，The data field of the secondary index stores the value of the primary key. Therefore, to find data from the secondary index, you need to find the primary key value through the secondary index before accessing the secondary index;
  It is best to use self incrementing primary keys to prevent data insertion from maintaining B+Tree structure, large adjustment of files.

The names of the four transaction isolation levels supported by InnoDB in MySQL and the differences between them?

SQL The four isolation levels defined in the standard are:
1,read uncommited : Uncommitted data read
2,read committed: Dirty reading, non repeatable reading
3,repeatable read: Rereadable
4,serializable : Serial things

What is the difference between CHAR and VARCHAR?

1,CHAR and VARCHAR Types differ in storage and retrieval
2,CHAR The column length is fixed to the length declared when the table is created, and the length value range is 1 to 255 CHAR
 When values are stored, they are filled with spaces to a specific length and retrieved CHAR The trailing space needs to be removed when the value is.

What is the usage of ENUM in MySQL?

ENUM is a string object that specifies a predefined set of values and can be used when creating a table.
        The SQL syntax is as follows:
        Create table size(name ENUM('Smail,'Medium','Large');

What is the difference between a primary key and a candidate key?

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

  Scan VX for Java data, front-end, test, python and so on

What is myisamchk used for?

It's used to compress MyISAM Table, which reduces disk or memory usage

What is the difference between MyISAM Static and MyISAM Dynamic?

stay MyISAM Static All fields on the have a fixed width.
dynamic MyISAM The table will have an image TEXT，BLOB And other fields to adapt to data types of different lengths.
MyISAM Static Easier to recover in case of damage.

Specific use

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

Whenever a row is changed, the timestamp field gets the current timestamp.

When the column is set to AUTO INCREMENT, what happens if the maximum value is reached in the table?

It will stop incrementing and any further insertion will produce an error because the key has been used.

How can I find out which automatic increment was allocated at the last insertion?

LAST_INSERT_ID Will be returned by Auto_increment The last value assigned, and there is no need to specify a table name.

% and% in LIKE statement_ What do you mean?

％Corresponding to 0 or more characters,_just LIKE A character in a statement.

How to convert between Unix and MySQL timestamps?

UNIX_TIMESTAMP Is from MySQL Timestamp conversion to Unix Timestamp command
FROM_UNIXTIME Is from Unix Timestamp conversion to MySQL Timestamp command

What's the difference between BLOB and TEXT?

BLOB Is a binary object that can hold a variable amount of data. TEXT Is a case insensitive
 of BLOB. 
BLOB and TEXT The only difference between types is that BLOB Values are case sensitive when sorted and compared
 Write, yes TEXT Values are not case sensitive.

MySQL_fetch_array and MySQL_ fetch_ What is the difference between objects?

Here is MySQL_fetch_array and MySQL_fetch_object Differences between:
MySQL_fetch_array() – Returns the result row as an associative array or as a regular array from the database.
MySQL_fetch_object – Returns a result row from the database as an object.

How many columns can be used to create an index?

Any standard table can create up to 16 index columns.

Does MySQL support transactions?

In the default mode, MySQL yes autocommit Mode, all database update operations will be performed immediately
 Submit, so by default, MySQL Transaction is not supported.

But if your MySQL Table type is used InnoDB Tables or BDB tables If so, yours
MySQL You can use transactions,use SET AUTOCOMMIT=0 Can make MySQL Allowed in non autocommit Mode, in non
autocommit In mode, you must use COMMIT To submit your changes, or use ROLLBACK To roll back your changes.

In a table, there is an ID self incrementing primary key. After inserting 17 records, delete records 15, 16 and 17, restart Mysql, and insert a record. Is the ID of this record 18 or 15?

1. If the table type is MyISMA，Then it's 18
    because MYISAM The table will increase the maximum value of the self incrementing primary key ID Record it in the data file and restart mysql Maximum value of self incrementing primary key ID It won't change
2. If the table type is InnoDB，So it's 15
    because InnoDB The table only increases the maximum value of the self incrementing primary key ID Record to memory,
    After restarting the database or modifying the table OPTIMIZE Operation will result in maximum ID lose.

Underlying principle

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

each MyISAM Tables are stored on disk in three formats:
·".frm"File storage table definition
·Data file has“.MYD"(MYData)Extension
 Index file has“.MYI"(MYIndex)Extension

performance analysis

What are the methods and commands for MySQL database server performance analysis?

Show status; view some variable values worth monitoring
        The traffic between Bytes_received and Bytes_sent and the server.
        Com_ * the command being executed by the server.
        Created_ * temporary tables and files created during the query execution period.
        Handler (* store engine operation.
        Select * different types of join execution plans.
        Sort * several sort information.
        Show profiles analyzes the resource consumption of SQL statement execution in the current session

  Scan VX for Java data, front-end, test, python and so on