MySQL database face question

Recently in exchange for work, although the interview is not DBA, but some knowledge of the database will be asked, and then Baidu a bit, combined with my search and was asked to summarize.

1. Does MySQL support business?

In the default mode, is automatically committed, all database update operations are committed immediately, but when the table type using InnoDB or BDB can be transacted;

Set autocommit=0;

Start transaction;

Select @a:=count (ID) from table1 where name= ' AA ';

Update table2 set [email protected] where id= ' 123 '

2, MySQL and other database comparison characteristics

MySQL is a small relational database management system that supports linux,mac,windows and other operating systems, and is less powerful than ORACLE,SQLSERVER,DB2.

A, SQL Server can only run on windows, open enough, but the real C/s, graphical user interface, has a rich programming interface, fully integrated with windnt, processing speed, can be used across the platform, specific some of the commands are different, such as paging can be used top

B, Oracle stability, security mechanism, big data, the disadvantage of expensive, paging with rownum

C, MySQL support 50 million records of the data Warehouse, adapt to all platforms, open source, the default desktop format is MyISAM. The MyISAM database is very compatible with the disk without consuming too much CPU and memory. Disadvantage: Backup of hot backup system is not supported, and security system is complex rather than standard;

3. VarChar and char differences

Char is a fixed-length type, and varchar is a variable-length type

4. Types of database transactions:

Isolation, durability, consistency, atomicity

5. Isolation level of four transactions in InnoDB

Read uncommited: Reads uncommitted, reads uncommitted data (dirty read), less actual application

Read Committed: Reads the commit content, most of the database default isolation level

REPEATABLE read, repeatable reads (MySQL default transaction isolation level), multithreading is displayed, multiple

When the instance reads the data concurrently, it sees the same data row, but the other transaction has inserted new data (Phantom Read), plus the gap lock to solve the Phantom read.

Serializable: Serializable, forcing transaction sequencing (with shared locks on each read data, but may result in timeouts and lock contention)

6. The difference between MySQL database engine MyISAM and InnoDB

Modify the MySQL storage engine ALTER TABLE tablename type = InnoDB;

Storage structure:

MyISAM stored as three files on disk,. frm file store table definition,. myd data file,. myi index File

InnoDB: stored in the same data file, limited by the size of the operating system, typically 2GB

Storage space:

MyISAM: Can be compressed, storage space is small, innodb need more memory and storage

Transaction:

MyISAM emphasizes performance, performs faster, but does not support transactions, InnoDB support transactions, external keys, and other advanced database functions

Curd operation:

MyISAM query faster (because there is no support for row-level locks) adding or deleting (locking the entire table)

InnoDB in Delete does not re-create the table, but deletes one row at a

FOREIGN key: MyISAM: No support for external, INNODB support

7. mysql Data sheet repair and data recovery

The server suddenly loses power and the MySQL service is not turned off until the shutdown is forced.

Use Myisamchk to fix

1) Stop the MySQL service

2) cmd-into the MySQL bin directory

3) The path where the Myisamchk-recover database is executed/*.myi

Use repair table or optimize table

8. What field does the MySQL record currency use?

Numeric and decimal types are implemented by MySQL as the same type, you can specify the number of digits in the decimal point, rounded when the specified number of digits is exceeded

9. Drop, delete, truncate difference

Drop directly deletes the table, truncate delete the data, the occupied Space (table and Index) will also be deleted, into the auto-incremented ID after deletion starting from 1, not with where

Delete can be associated with where, but does not delete the occupied space

10. Index

Advantages: Effective use can increase the speed of query

Cons: Increase database storage space, when inserting and modifying is the time the index changes and takes longer

Normal index: Uncertain whether it is repeatable, can be used as a normal index, the only one cited, determined to be non-repeatable such as a social security number, combined index, multiple indexes are created together, note the leftmost principle

11. Database Optimization:

1) SQL optimization

A, fuzzy query, try to avoid left Blur

b, to avoid the calculation of the index field Operation;

Avoid using not,<>,!=; in indexed fields

Avoid using is null on an indexed field, is not null;

Avoid type conversions on indexed fields

Avoid using functions on indexed fields

C, try to use union all instead of union

D, where to avoid using in, not in,or or having

Select Car_no from a where EXISTS (select car_id from C where c.car_id=a.car_id)

E, do not do unnecessary operations on the table

2) Index optimization

3) Database optimization

A, paradigm optimization (eliminate redundancy)

B, sub-table (vertical, horizontal)

C, Server hardware optimization

MySQL database face question