Shulou(Shulou.com)06/01 Report--

This article mainly talks about what is the content of MySQL's basic statements. If you are interested, take a look at this article together. I believe that after reading what is MySQL's basic statement, it has some reference value for everyone. View current MYSQL supported storage engines

show engines; View the permissions of a user

show grants for UserName; How to find out the current Binary Log file and Position value

show master status; What command toggles Binary Log

flush logs; What command to use to repair MyISAM tables

repaire TabName; What command is used to defragment table data files

optimize TabName; How to get TabA table creation statements

show create table TabA; Create TabB table, copy TabA table structure and index completely, no data

create table TabB like TabA; add field ColA to TabA table, decimal type with precision without deviation, 10-digit integer, 2-digit decimal, plus index

alter TabA add ColA decimal(12,2), add key (ColA); How to empty TabB table data

truncate table TabB; how to not enter mysql client, execute a SQL command, account User, password Passwd, library name DBName, SQL is "select sysdate();"

mysql -uUser -pPasswd -D DBName -e "select sysdate();" How to parse all select statements from slow log, output the first 20 in reverse order, log name is slow_log.file

mysqldumpslow -g 'select' -s c -r slow_log.file How to parse the SQL statements of anjuke library from "2017-10-27 13:00:00" to "2017-10-27 18:00:00" from BinLog, the log name is bin_log.file

mysqlbinlog -d anjuke --start-datetime='2017-10-27 13:00:00' --stop-datetime='2017-10-27 18:00:00' bin_log.file Create user, user name is iamdba, password is mypasswd, access source 10.11.8.X, i.e. 8 IP segments are allowed, and have "Add, Delete, Modify and Query" permission on all tables in anjuke library

grant insert,delete,update,select on anjuke.* to iamdba@'10.10.8.% ' identified by 'mypasswd'; How to execute a SQL file, account User, password Passwd, library name DBName, SQL file name sql.file

mysql -uUser -pPasswd -D DBName Export all TabA data to/tmp/TabA.txt file by select

select * from TabA into outfile '/tmp/TabA.txt'; Import TabA.txt data into TabB table, such as primary key or unique key conflict, overwrite data in tabB table

load data [local] infile '/tmp/TabA.txt' replace into table TabB; export the TabB data in anjuke library that meets "ColA > 100" to TabB.sql file with mysqldump command, do not export the table creation statement, account User, password Passwd

mysqldump -uUser -pPasswd --no-create-info anjuke TabB -w "ColA > 100" >TabB.sql

PS: --no-create-info can also be written as-t Switch synchronization to master 10.11.8.11, port 3307, username repl, password passwd, starting BinLog db-master.000001, position 123456

change master to master_host='10.11.8.11', master_port=3307, master_user='repl', master_password='passwd', master_log_file='db-master. 00001', master_log_pos=123456; Start slave sql process and stop at db-master. 00003 file location 123456 synchronized to master

start slave sql_thread util master_log_file ='db-master. 00003', master_log_pos=123456; Create an optimal index that satisfies the following five conditions

where a=? and b=? and c=?

where a=? and b>? and c=?

where a=? and b in (?) and c=?

where a=? and c=? order by b

where a=? order by c,b

A composite index created in order (a,c,b) has two composite indexes (a,b) and (c,d). How do the following statements use indexes? What optimization can be done?

select from Tab where (a=? and b=?) or (c=? and d=?)

According to MYSQL's mechanism, only a composite index with good filtering effect will be used. You can do the following optimization.

select from Tab where a=? and b=?

union

select * from Tab where c=? and d=? How many non-empty lines does the file a.txt have?

grep -c '^..*$ ' a.txt

or

grep -v '^$' a.txt |wc-l file b.txt, each row is divided into 5 columns with ":" character, such as "1:apple:3:2017-10-25:very good", how to get the total value of the third column of all rows

awk 'BEGIN {FS=":"; s=0} {s+=$3} END {print s}' b.txt intercepts the records from lines 60 to 480 of the file c.txt, ignoring case, counting the record with the most repetitions, and the number of repetitions

sed -n '60,480'p c.txt | sort | uniq -i -c | sort -rn | head -n 1

What are the features of 5x5 MyISAM and InnoDB? In what scenarios do they apply?

MyISAM, table lock, does not support transactions, table corruption rate is high, divided into MYD data file and MYI index two files, read and write concurrency is not as good as InnoDB, suitable for scenes with many inserts, and supports direct copy of files, used to backup data

InnoDB, row lock, transaction support, recove mechanism after crash, only ibd file, divided into data area and index area, good read and write concurrency ability, but do count() operation consumes CPUMySQL What are the backup methods natively supported, and say their advantages and disadvantages?

(1)Copy data file directly, must be MyISAM table, and use flush tables with read lock; statement, the advantage is simple and convenient, the disadvantage is that it needs to be locked, and can only be restored on the same version of MySQL

(2)mysqldump, exported SQL statements, so you can recover across versions, but you need to import data and rebuild the index, recovery time will be long, if it is MyISAM table, also need to lock the table, if it is InnoDB table, you can use the--single-transaction parameter to avoid this problem When creating and using the index, what should be paid attention to, what rules?

Avoid too many indexes, which will affect write performance

Indexing fields with low filtering effect is almost invalid, such as gender, status flag, etc.

Only one index is used per query execution, and composite indexes should be created if necessary

When using composite index, follow the principle of "from left to right", and the left percent sign is strictly prohibited

Do not have arithmetic operations and use functions on index fields, you will not be able to use indexes How to analyze the execution performance of a SQL statement, what information to pay attention to?

Regardless of performance, avoid subqueries and nested SQL, and try not to have join queries

Use the explain command to observe the type column, you can know whether it is a full table scan, and the use of the index, observe the key to know which index is used, observe the key_len to know whether the index is used completely, observe the rows to know whether the number of rows scanned is too many, observe the extra to know whether temporary tables are used and additional sorting operations are performed. MySQL uses what mechanism to control access permissions?

mysql library, from the user table to the host table and db table, and then to the tables_priv table and columns_priv table above about what is MySQL basic statement details, to help you? If you want to know more about it, you can continue to pay attention to our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.