魏长东

weichangdong

东邪

mysql高级命令

1. 查看当前MYSQL支持的存储引擎

show engines;

 

2. 查看某一用户的权限

show grants for UserName;

 

3. 如何得知当前Binary Log文件和Position

show master status;

 

4. 用什么命令切换Binary Log

flush logs;

 

5. 用什么命令修复MyISAM

repaire table TabName;

 

6. 用什么命令整理表数据文件的碎片

optimize table TabName;

 

7. 如何得到TabA表的建表语句

show create table TabA;

 

8. 创建TabB表,完整拷贝TabA表的结构和索引,不要数据

create table TabB like TabA;

 

9. TabA表新增字段ColA,精度无偏差的小数型,10位整数,2位小数,加索引

alter table TabA add ColA decimal(12,2), add key (ColA);

 

10. 如何清空TabB表的数据

truncate table TabB;

 

11. 如何不进入mysql客户端,执行一条SQL命令,帐号User,密码Passwd,库名DBNameSQL"select sysdate();"

mysql -uUser -pPasswd -D DBName -e "select sysdate();"

 

12. 如何从slow log中分析出所有的select语句,按次数以倒序输出前20条,日志名为slow_log.file

mysqldumpslow -g 'select' -s c -r slow_log.file

 

13. 如何从BinLog中分析出时间从"2012-10-27 13:00:00""2012-10-27 18:00:00"anjuke库的SQL语句,日志名为bin_log.file

mysqlbinlog -d anjuke --start-datetime='2012-10-27 13:00:00' --stop-datetime='2012-10-27 18:00:00' bin_log.file

 

14. 创建用户,用户名为iamdba,密码为mypasswd,访问来源10.11.8.X,即8IP都允许,对anjuke库所有表拥有增删改查权限

grant insert,delete,update,select on anjuke.* to iamdba@'10.10.8.%' identified by 'mypasswd';

 

15. 如何执行一个SQL文件,帐号User,密码Passwd,库名DBNameSQL文件名为sql.file

mysql -uUser -pPasswd -D DBName

 

16. select方式导出TabA的所有数据到/tmp/TabA.txt文件

select * from TabA into outfile '/tmp/TabA.txt';

 

17. 导入TabA.txt数据到TabB表,如主键或唯一键冲突,覆盖tabB表中的数据

load data [local] infile '/tmp/TabA.txt' replace into table TabB;

 

18. mysqldump命令导出anjuke库里的TabB表中满足“ColA > 100”的数据到TabB.sql文件,不要导出建表语句,帐号User,密码Passwd

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

PS: --no-create-info 也可以写为 -t

 

19. 切换同步到master 10.11.8.11,端口为3307,用户名repl,密码为passwd,起点BinLogdb-master.000001,位置123456

change master to master_host='10.11.8.11', master_port=3307, master_user='repl', master_password='passwd', master_log_file='db-master.000001', master_log_pos=123456;

 

20. 启动slavesql进程,并在同步到masterdb-master.000003文件123456位置上停止

start slave sql_thread util master_log_file='db-master.000003', master_log_pos=123456;

 

21. 建一个满足以下五种条件的最优索引

  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,c,b)的复合索引

 

22. 有二个复合索引(a,b)(c,d),以下语句会怎样使用索引?可以做怎样的优化?

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

根据MYSQL的机制,只会使用到一个筛选效果好的复合索引,可以做如下优化

select * from Tab where a=? and b=?

union

select * from Tab where c=? and d=?

 

23. 如何统计文件a.txt有多少非空行?

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

grep -v '^$' a.txt | wc -l

 

24. 文件b.txt,每行以“:”符分成5列,如“1:apple:3:2012-10-25:very good”,如何得到所有行第三列的总合值

awk 'BEGIN {FS=":"; s=0} {s+=$3} END {print s}' b.txt

 

25. 截取文件c.txt的第60480行记录,忽略大小写,统计出重复次数最多的那条记录,及重复次数

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

 

 

二、简答题 5x5

 

1. MyISAMInnoDB各有哪些特性?分别适用在怎样的场景下?

MyISAM,表锁,不支持事务,表损坏率较高,分为MYD数据文件和MYI索引二个文件,读写并发不如InnoDB,适用于insert较多的场景,且支持直接复制文件,用以备份数据

InnoDB,行锁,支持事务,crash后具有recove机制,只有ibd文件,分为数据区和索引区,有较好的读写并发能力,但做count()运算时相当消耗CPU

 

2. MySQL原生支持的备份方式有哪些,并说出其优缺点?

(1) 直接拷贝数据文件,必须是MyISAM表,且使用flush tables with read lock;语句,优点是简单方便,缺点是须要锁写,且只能在同版本的MySQL上恢复使用

(2) mysqldump,导出的是SQL语句,所以可以跨版本恢复,但是需要导入数据和重建索引,恢复用时会较长,如果是MyISAM表,同样需要锁表,如果是InnoDB表,可以使用--single-transaction参数避免此问题

 

3. 在创建和使用索引时,有哪些要注意的地方,有什么规则?

避免索引过多,会影响写性能

给筛选效果低的字段加索引,几乎无效,如性别、状态标志等

每条查询执行时,只会使用一个索引,有需要时应该创建复合索引

复合索引使用时遵守从左到右原则,严禁左百分号

不要在索引字段上有运算操作和使用函数,将无法使用索引

 

4. 如何分析一条SQL语句的执行性能,关注哪些信息?

不论性能如何,不要有子查询和嵌套SQL,尽量不要有join查询

使用explain命令,观察type列,可以知道是否是全表扫描,和索引的使用形式,观察key可以知道使用了哪个索引,观察key_len可以知道索引是否使用完成,观察rows可以知道扫描的行数是否过多,观察extra可以知道是否使用了临时表和进行了额外的排序操作

 

5. MySQL通过什么机制来控制访问权限?

mysql库中,从user表到host表和db表,再到tables_priv表和colums_priv