Article source: bbs.51cto.com/thread-1470…
1, MySQL replication principle and process
Basic principle flow, 3 threads and the correlation between them;
\
2. The difference between MyISam and InnoDB in MySQL is at least 5 points
(1) Ask 5 different points;
(2) Four features of InnoDB engine
(2) selectcount(*) which is faster and why
\
Varchar (50) = 50; varchar(50) = 50
(1) Varchar vs. char
(2) the meaning of 50 in VARCHar (50)
(3) the meaning of 20 in int (20)
(4) Why is mysql designed this way
\
4. Innodb transaction and log implementation
(1) How many kinds of logs are there?
(2) Four isolation levels of things
(3) How transactions are implemented through logging, the more in-depth the better.
\
5, MySQL binlog can be entered in different formats
(1) Type and difference of the log format of binlog
(2) Applicable scenarios;
(3) Combine with the first question, the pros and cons of each log format in replication.
\
MySQL database CPU increases to 500%
(1) No experience, can not ask;
(2), experienced, ask their handling ideas.
\
7, SQL optimization
(1) Explain the meaning of various items;
(2) The meaning and usage scenarios of profile;
\
8. Implementation principle of backup plan, mysqlDump and XtranBackup
(1) Backup plan;
(2) Backup and recovery time;
(3) Implementation principle of Xtrabackup
\
Mysql > select * from mysqldump where id = ‘…. ‘ What if value()? What if the backup requires the master copy point information?
\
10, 500 DB, restart in the fastest time
\
11. Innodb read and write parameter optimization
(1) Read parameters
(2) Write parameters;
(3) IO related parameters;
(4) Cache parameters and applicable scenarios of cache.
\
12. How do you monitor your database? How do you search your slow logs?
\
13, Have you done master-slave consistency check? If so, how? If not, what are you going to do?
\
14. Does your database support emoji? If not, how to operate?
\
15, How do you maintain the data dictionary of the database?
16. Do you have development specifications, and if so, how do you implement them
\
17, there is a large column X in the table (e.g., text type), and the column X is not frequently updated
(1) do you choose to split into sub-tables, or continue to put together;
(2) Write the reasons for your choice.
\
18. On what is InnoDB engine row locking implemented? Why is that?
\
Select * from mysqldump; select * from mysqldump;
\
20. Open question: It is said to be Tencent’s
A table of 600 million a, a table of 300 million B, through the external TID association, how can you query the 200 data records meeting the conditions from 50000 to 50200 in the fastest way?
\
1, MySQL replication principle and process
Basic principle flow, 3 threads and the correlation between them;
(1) Master: binlog thread — record all statements that change database data, put in the master binlog;
(2) Slave: IO thread — after using the start slave, it is responsible for pulling binlog content from the master and putting it into its own relay log;
(3) from: SQL thread — execute statements in relay log;
\
2. The difference between MyISam and InnoDB in MySQL is at least 5 points
(1) Ask 5 different points;
InnoDB supports things, MyISAM does not
InnoDB supports row-level locking, while MyISAM supports table-level locking
3>.InnoDB supports MVCC, MyISAM does not
4>.InnoDB supports foreign keys, while MyISAM does not
InnoDB does not support full-text indexing, while MyISAM does.
(2) Four features of InnoDB engine
Insert Buffer, Double Write, Adaptive hash index (AHI), Read Ahead
(2) selectcount(*) which is faster and why
Myisam is faster because myISAM maintains a counter that can be called directly.
\
Varchar (50); varchar(50); char(50); Varchar (50) stores up to 50 characters. Varchar (50) takes up the same space as hello (200), but the latter consumes more memory when sorting. Order by col fixed_length order by COL fixed_length order by COL fixed_length (memory engine) It will display 00000000001 ~~00000000010. When the number of bits exceeds 11, it will display only 11 bits. If you don’t add 0 before it is less than 11 bits, it will not add 0 before it. (4) why mysql is designed this way is not meaningful for most applications, just specify some tools to display the number of characters; Int (1) and int(20) store and calculate the same;
Innodb transaction and log implementation (1), how many kinds of log; Error log: Records error information, warning information or correct information. Query logging: Records information about all requests to the database, whether or not they are executed correctly. Slow query log: Set a threshold. All SQL statements whose running time exceeds this threshold are recorded in the slow query log file. Binary log: Records all the changes made to the database. Relay log: Transaction log: (2) 4 levels of isolation for transactions Isolation Level Read Uncommitted (RU) Read Committed (RC) Repeatable read (RR) Serial (3) How transactions are implemented through logging, the more in-depth the better. Transaction logging is implemented through redo and InnoDB log buffer. When a transaction is started, the LSN (log sequence number) of the transaction is recorded. When a transaction is executed, the transaction log is inserted into the log cache of InnoDB’s log storage engine. When a transaction commits, the storage engine’s log buffer must be written to disk (controlled by innodb_flush_LOG_at_trx_COMMIT), that is, the log must be written before data is written. This approach is called “write-ahead logging”
(1) The format of the MySQL binlog is different from that of the MySQL binlog. (2) The format of the log is different from that of the MySQL binlog. (3) Combine with the first question, the pros and cons of each log format in replication. Statement: Each SQL Statement that modifies data is recorded in a binlog. Advantages: You do not need to record the changes of each row, reducing the amount of binlog logs, saving I/O, and improving performance. (How much performance and log volume can be saved compared to a ROW depends on the SQL application. Normally, the amount of log volume generated by modifying or inserting a row is less than that generated by a Statement. However, if you perform an update operation with a bar, delete a whole table, alter a table, etc., the number of log volume generated by modifying or inserting a row is less than that generated by a Statement. ROW generates a large number of logs, so you should consider whether to use ROW logs based on the actual situation of the application, how much more logs are generated, and the I/O performance issues. Disadvantages: Since only executing statements are recorded, in order for the statements to run correctly on the slave, some information must be recorded about the execution of each statement to ensure that all statements get the same results on the slave as they did on the master side. In addition, mysql replication, like some specific functions, can be consistent on slave and master (such as sleep(), last_insert_id(), and user-defined functions(UDF)). Statements using the following functions also cannot be copied: * LOAD_FILE() * UUID() * USER() * FOUND_ROWS() * SYSDATE() (unless the — sysdate-IS-now option is enabled at startup) at the same time as INSERT… SELECT generates more row-level locks than RBR. 2.Row: Does not record information about the context of the SQL statement. Advantage: Binlog does not record context-specific information about the SQL statement being executed, only record which record has been modified. So the rowLevel log content clearly records the details of each row of data modification. And there are no specific cases where stored procedures, or functions, and trigger calls and triggers cannot be copied correctly. The disadvantage is that all executed statements, when logged, are recorded as modifications to each line, which can result in a large amount of log content, such as an UPDATE statement, If multiple records are modified, each change in the binlog will be recorded. As a result, a large number of binlog logs will be generated. In particular, when statements such as ALTER TABLE are executed, each record in the table will be recorded in the log because the table structure is changed. 3.Mixedlevel: A combination of the above two levels is used. Common statement changes use the statment format to store binlogs. For example, statements cannot perform primary/secondary copy operations. MySQL stores binlogs in the ROW format. The MySQL database selects a log format based on each SQL Statement executed. That is, Statement and ROW are selected. The row level mode of MySQL has been optimized. Not all changes are recorded in the row level mode. For example, statement mode is used to record table structure changes. In the case of statements that modify data, such as UPDATE or DELETE, all row changes are recorded.
MySQL database CPU increases to 500% (1) No experience, can not ask; (2), experienced, ask their handling ideas. Show processList Displays the status of all processes in seconds (do several years of development, the general will be query and large number of inserts will lead to CPU and I/O increase,,,, of course does not exclude the network state suddenly down, resulting in a request server only to receive half, such as where clause or paging clause is not sent, of course a pit experience) \
7, SQL optimization (1), explain the meaning of various items; Possible_keys specifies which index MySQL can use to find a row in a table. Possible_keys specifies which index MySQL can use to find a row in a table. If no index is used, the value is NULL. Key_len indicates the number of bytes used in the index. You can use this column to calculate the length of the index used in the query. That is, which columns or constants are used to find the value on the index column Extra contains important additional information that is not suitable for display in other columns (2), the meaning of the profile and usage scenarios; Query how long the SQL will take to execute, and see how much CPU/Memory usage, Systemlock, Table lock, etc
Backup plan, mysqlDump and XtranBackup implementation principle Every company here is different, you do not say that 1 hour 1 full of what on the line (2), backup and recovery time; It has to do with the speed of the machine, especially the hard drive, 20GB 2-minute (mysqldump) 80GB 30-minute (mysqldump) 111G 30-minute (mysqldump) 288GB 3-hour (Xtra) 3TB 4-hour (Xtra) logical import takes more than 5 times the backup time InnoDB maintains a redo log file, or transaction log file. The transaction log stores recorded changes to each InnoDB table data. When InnoDB starts, InnoDB checks the data files and transaction logs and performs two steps: It applies (rolls forward) committed transaction logs to the data files and rolls back data that has been modified but not committed.
Mysql > select * from mysqldump where id = ‘…. ‘ What if value()? What if the backup requires the master copy point information? –skip-extended-insert [root@helei-zhuanshu ~]# mysqldump -uroot -p helei –skip-extended-insert Enter password: KEY `idx_c1` (`c1`), KEY `idx_c2` (`c2`) ) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=latin1; / *! 40101 SET character_set_client = @saved_cs_client */; — — Dumping data for table `helei` — LOCK TABLES `helei` WRITE; / *! 40000 ALTER TABLE `helei` DISABLE KEYS */; INSERT INTO ‘helei’ VALUES (1,32,37,38,’2016-10-18 06:19:24′,’sususususususususu ‘); INSERT INTO ‘helei’ VALUES (2,37, 60,21,’2016-10-18 06:19:24′,’sususususu ‘); INSERT INTO ‘helei’ VALUES (3,21,5,14,’2016-10-18 06:19:24′,’susu’); \
Mysql > restart Puppet as soon as possible, DSH \
Innodb read/write parameter optimization (1), read parameter \
Global buffer pool and Local buffer; (2) Write parameters; Innodb_flush_log_at_trx_commit Innodb_buffer_pool_size (3), I/O related parameters; Innodb_write_io_threads = 8 Innodb_read_IO_threads = 8 Innodb_thread_concurrency = 0 (4), cache parameters and the applicable scenarios for caching. Query Cache /query_cache_type Not all tables are suitable for query cache. The main cause of query cache failure is that the corresponding table is changed \
- The first one: read a lot of words to see the proportion, in simple terms, if it is a list of users, or the proportion of data is relatively fixed, such as the list of goods, it can be opened, the premise is that these libraries are more centralized, the practice of the database is relatively small.
- The second is that when we “cheat”, such as when we are bidding on the query cache, we can still get the QPS surge effect, but of course the connection pool configuration is the same. In most cases, if the amount of writing is in the majority, the number of visits is not much, so do not open, for example, social networking sites, 10% of people produce content, the rest 90% are in consumption, open or effect is very good, but if you are QQ messages, or chat, it is very deadly.
- It is not recommended to open query cache for a site with a high number of concurrent requests
\
12. How do you monitor your database? How do you search your slow logs? There are many monitoring tools, such as Zabbix, lePUS, I use lePUS \ here
13, Have you done master-slave consistency check? If so, how? If not, what are you going to do? For example, checksum, mysqlDIff, pt-table-checksum, etc
14. Does your database support emoji? If not, how to operate? For utF8 character set, you need to upgrade to UTF8_MB4 to support \
15, How do you maintain the data dictionary of the database? We all have different maintenance methods. I generally annotate it directly in the production library and export it into Excel for easy circulation by using tools. \
16, do you have a development specification, if so, how to implement there, there are a lot of development specifications online, you can have a look at the summary \
17, there is a large field X in the table (e.g., text type), and the field X is not often updated, mainly read, ask (1), do you choose to split into child tables, or continue to put together; (2) Write the reasons for your choice. A: Disassembly brings problems: connection consumption + storage disassembly space; Possible problems: query performance; If you can tolerate the space problems associated with splitting, it is best to physically place the primary key of the frequently queried table together (partitioned) order IO to reduce join consumption. Finally, this is a text column with a full-text index to minimize join consumption If you can tolerate the performance penalty of not splitting the query: the above scheme is bound to be problematic under extreme conditions, then leaving it alone is the best choice
18. On what is InnoDB engine row locking implemented? Why is that? InnoDB locks rows based on indexes: select * from tab_with_index where id = 1 for update; For UPDATE can perform a row lock based on conditions, and id is a column with an index key. If id is not an index key then InnoDB will perform a table lock, and concurrency is out of the question
Select * from mysqldump; select * from mysqldump; \
Answer: see suifu.blog.51cto.com/9167728/183… \
20. Open question: It is said that Tencent has a table A of 600 million yuan and a table B of 300 million yuan. Through external TID association, how can you query the 200 data records from 50000 to 50200 that meet the conditions as soon as possible? Select * from A,B where a.id = b.id and a.id >500000 LIMIT 200 select * from A where a.id = b.id and a.id >500000 limit 200 2. If the TID of table A is not continuous, then overwrite index is required. TID is either a primary key or secondary index. Table B ID also needs to have an index. Select * from b, (select tid from a limit 50000,200) a where b.id = a.tid;
Author: dbapower
Source: bbs.51cto.com/thread-1470…
\