mysql 整理之mysql sql语句相关

6.1     sql优化步骤

1、通过show status 了解各种SQL的执行频率

   show status like 'Com_%'; 显示当前session中所有统计参数的值

    1）适用所有存储引擎

   Com_select 执行select的次数，一次执行累加一

   Com_insert 执行insert的次数

   Com_update 执行update的次数

   Com_delete 执行delete的次数

    2）innodb引擎

   show status like 'Innodb_rows_%';

   Innodb_rows_deleted  | 139           执行delete删除的行数

   Innodb_rows_inserted | 5067714      执行insert插入的行数  

   Innodb_rows_read     |1164419790796 执行select 查询返回的行数

   Innodb_rows_updated  |2841812       执行update更新的行数

    3）show status like 'Slow_queries%'; 慢查询次数

      show status like 'Uptime%';       服务器工作时间

      show status like 'Connections%';  视图连接mysql服务器的次数

 

2、定位执行效率较低的SQL

    1）通过慢查询日志定位那些执行效率较低的 SQL 语句

      用--log-slow-queries[=file_name] 选项启动时， mysqld 会 写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志文件，

      通过查看这个日志文件定位效率较低的 SQL 。慢查询日志在查询结束以后才纪录，所以在应用反映执行效率出现问题的时候查询慢查询日志

      并不能定位问题

    2）使用 show processlist 命令查看当前 MySQL 在进行的线程，

      包括线程的状态、是否锁表等，可以实时地查看 SQL 的 执行情况，同时对一些锁表操作进行优化。

   

3、使用EXPLAIN分析低效SQL的执行计划，确定问题并采取优化措施

 

（1）explain出来的各种item的意义

（2）profile的意义以及使用场景。

（3）explain中的索引问题。

 

（1） explain出来的各种item的意义

id:每个被独立执行的操作的标志，表示对象被操作的顺序。一般来说，id值大，先被执行；如果id值相同，则顺序从上到下。

select_type：查询中每个select子句的类型。具体待明天补充。

table:名字，被操作的对象名称，通常的表名(或者别名)，但是也有其他格式。

partitions:匹配的分区信息。

type:join类型。具体指待明天补充。

possible_keys：列出可能会用到的索引。

key:实际用到的索引。

key_len:用到的索引键的平均长度，单位为字节。

ref:表示本行被操作的对象的参照对象，可能是一个常量用const表示，也可能是其他表的key指向的对象，比如说驱动表的连接列。

rows:估计每次需要扫描的行数。

filtered:rows*filtered/100表示该步骤最后得到的行数(估计值)。

extra:重要的补充信息。具体待明天补充。

（2） profile的意义以及使用场景。

 

Profile用来分析sql性能的消耗分布情况。当用explain无法解决慢SQL的时候，需要用profile来对sql进行更细致的分析，找出sql所花的时间大部分消耗在哪个部分，确认sql的性能瓶颈。Profile（查询到 SQL 会执行多少时间,并看出 CPU/Memory使用量,执行过程中Systemlock, Table lock花多少时间等等.）

（3） explain中的索引问题。

 

Explain结果中，一般来说，要看到尽量用index(type为const、ref等，key列有值)，避免使用全表扫描(type显式为ALL)。比如说有where条件且选择性不错的列，需要建立索引。被驱动表的连接列，也需要建立索引。被驱动表的连接列也可能会跟where条件列一起建立资

 

联合索引。当有排序或者group by的需求时，也可以考虑建立索引来达到直接排序和汇总的需求。

6.2     cpu飙升500%的优化步骤

当cpu飙升到500%时，先用操作系统命令top命令观察是不是mysqld占用导致的，如果不是，找出占用高的进程，并进行相关处理。如果是mysqld造成的，show processlist，看看里面跑的session情况，是不是有消耗资源的sql在运行。找出消耗高的sql，看看执行计划是否准确，index是否缺失，或者实在是数据量太大造成。一般来说，肯定要kill掉这些线程(同时观察cpu使用率是否下降)，等进行相应的调整(比如说加索引、改sql、改内存参数)之后，再重新跑这些SQL。也有可能是每个sql消耗资源并不多，但是突然之间，有大量的session连进来导致cpu飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等。

6.3     mysql如何实现插入时如果不存在则插入如果存在则更新的操作？

1. replace intotbl_name(col_name, …) values(…)
2. replace into tbl_name(col_name, …) select …
3. replace into tbl_name set col_name=value, …

6.4     mysql如何实现高效分页

先看一下分页的基本原理（我拿的是CSDN那个百万级数据库来测试！）：

SELECT* FROM `csdn` ORDER BY id DESC LIMIT 100000,2000;
耗时： 0.813ms

分析：对上面的mysql语句说明：limit 100000,2000的意思扫描满足条件的102000行，扔掉前面的100000行，返回最后的2000行。

问题就在这里，如果是limit 100000,20000，需要扫描120000行，在一个高并发的应用里，每次查询需要扫描超过100000行，性能肯定大打折扣。

在《efficient pagination using mysql》中提出的clue方式。

利用clue方法，给翻页提供一些线索，比如还是SELECT * FROM `csdn`order by id desc，按id降序分页，每页2000条，当前是第50页，当前页条目id最大的是102000，最小的是100000。如果我们只提供上一页、下一页这样的跳转（不提供到第N页的跳转）。

那么在处理上一页的时候SQL语句可以是：
SELECT * FROM `csdn` WHERE id
耗时：0.015ms

处理下一页的时候SQL语句可以是：
SELECT * FROM `csdn` WHERE id>102000 ORDER BY id ASC LIMIT 2000; #下一页
耗时：0.015ms

这样，不管翻多少页，每次查询只扫描20行。效率大大提高了！

但是，这样分页的缺点是只能提供上一页、下一页的链接形式。

6.5     一张表,里面有ID自增主键,当insert了17条记录之后,删除了第15,16,17条记录,再把Mysql重启,再insert一条记录,这条记录的ID是18还是15 ？

（1）如果表的类型是MyISAM，那么是18。 
因为MyISAM表会把自增主键的最大ID记录到数据文件里，重启MySQL自增主键的最大ID也不会丢失。 
（2）如果表的类型是InnoDB，那么是15。 
InnoDB表只是把自增主键的最大ID记录到内存中，所以重启数据库或者是对表进行OPTIMIZE操作，都会导致最大ID丢失。

6.6     优化sql语句执行效率的方法，从哪些方面。sql语句性能如何分析？

（1）尽量选择较小的列 
（2）将where中用的比较频繁的字段建立索引 
（3）select子句中避免使用‘*’ 
（4）避免在索引列上使用计算，not，in和<>等操作 
（5）当只需要一行数据的时候使用limit 1 
（6）保证表单数据不超过200w，适时分割表 
（7）针对查询较慢的语句，可以使用explain来分析该语句具体的执行情况

6.7           select Count (*)和Select Count(1)以及Select Count(column)区别

一般情况下，Select Count(*)和Select Count(1)两着返回结果是一样的

假如表沒有主键(Primary key),那么count(1)比count(*)快，    如果有主键的話，那主键作为count的条件时候count(主键)最快
    如果你的表只有一个字段的话那count(*)就是最快的   count(*)跟 count(1)的结果一样，都包括对NULL的统计，而count(column)是不包括NULL的统计

6.8 对于mysql查询，都有哪些情况导致存储引擎放弃索引进行全表扫描？

（1）where子句中使用!=或<>操作符

（2）在 where子句中对字段进行 null值判断，可以将null值设置成0 或者 “”

（3）在 where子句中使用 or来连接条件

select id from t where num=10 or num=20
　　可以这样查询：
select id from t wherenum=10
union all
select id from t wherenum=20

（4）in和 not in也要慎用，否则会导致全表扫描

（5）在 where子句中使用参数，也会导致全表扫描

       因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

select id from t where num=@num
　　可以改为强制查询使用索引：
select id from twith(index(索引名)) wherenum=@num

       （6）在 where子句中对字段进行表达式操作

       （7）在where子句中对字段进行函数操作

       （8）在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用

6.9 sql语句中 exists和in哪个的效率更高？为什么

mysql中的in语句是把外表和内表作hash连接，而exists语句是对外表作loop循环，每次loop循环再对内表进行查询。一直大家都认为exists比in语句的效率要高，这种说法其实是不准确的。这个是要区分环境的。

如果查询的两个表大小相当，那么用in和exists差别不大。

如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：

例如：表A（小表），表B（大表）

 

1：select * from A where cc in (select cc from B)效率低，用到了A表上cc列的索引；

select * from A where exists(select cc from Bwhere cc=A.cc)效率高，用到了B表上cc列的索引。

相反的

 2：select * from B where cc in (select cc from A)效率高，用到了B表上cc列的索引；

 select *from B where exists(select cc from A where cc=B.cc)效率低，用到了A表上cc列的索引。

 not in 和not exists如果查询语句使用了not in那么内外表都进行全表扫描，没有用到索引；而not extsts的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

in 与 =的区别

select name from student where name in('zhang','wang','li','zhao');

与

select name from student where name='zhang' orname='li' or name='wang' or name='zhao'

的结果是相同的。