MySQL优化

  一、如何发现有问题的SQL?

  使用MySQL慢查日志对有效率问题的SQL进行监控。

  慢查询日志概念

  MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中。long_query_time的默认值为10,意思是运行10S以上的语句。默认情况下,Mysql数据库并不启动慢查询日志,需要我们手动来设置这个参数,当然,如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件,也支持将日志记录写入数据库表。

  慢查询日志相关参数

  MySQL 慢查询的相关参数解释:

  slow_query_log :是否开启慢查询日志,1表示开启,0表示关闭。

  log-slow-queries :旧版(5.6以下版本)MySQL数据库慢查询日志存储路径。可以不设置该参数,系统则会默认给一个缺省的文件host_name-slow.log

  slow-query-log-file:新版(5.6及以上版本)MySQL数据库慢查询日志存储路径。可以不设置该参数,系统则会默认给一个缺省的文件host_name-slow.log

  long_query_time :慢查询阈值,当查询时间多于设定的阈值时,记录日志。

  log_queries_not_using_indexes:未使用索引的查询也被记录到慢查询日志中(可选项)。

  log_output:日志存储方式。log_output='FILE'表示将日志存入文件,默认值是'FILE'。log_output='TABLE'表示将日志存入数据库,这样日志信息就会被写入到mysql.slow_log表中。MySQL数据库支持同时两种日志存储方式,配置的时候以逗号隔开即可,如:log_output='FILE,TABLE'。日志记录到系统的专用日志表中,要比记录到文件耗费更多的系统资源,因此对于需要启用慢查询日志,又需要能够获得更高的系统性能,那么建议优先记录到文件。

// 找到文件my.ini  C:\ProgramData\MySQL\MySQL Server 5.6\my.ini
explicit_defaults_for_timestamp
general-log=1  
general-log_file="D:/Projects/DATA/MySqlLog/mysql.log"
slow_query_log=1
slow_query_log_file="D:/Projects/DATA/MySqlLog/mysql_slow.log"
long_query_time=1
log_queries_not_using_indexes =1 

  慢查日志分析工具mysqldumpslow

  二、如何分析SQL查询

  使用explain语句去查看分析结果

  explain用法

  EXPLAIN tbl_name或:EXPLAIN [EXTENDED] SELECT select_options

  前者可以得出一个表的字段结构等等,后者主要是给出相关的一些索引信息,而今天要讲述的重点是后者。

  如:

  explain select * from test1 where id=1;

  会出现列:

  [id] [selecttype] [table] [type] [possible_keys] [key] [key_len] [ref rows] [extra]各列。

  explain列的解释:

  table:显示这一行的数据是关于哪张表的

  type:这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、 indexhe和ALL

  possible_keys:显示可能应用在这张表中的索引。如果为空,没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句

  key:实际使用的索引。如果为NULL,则没有使用索引。很少的情况下,MYSQL会选择优化不足的索引。这种情况下,可以在SELECT语句中使用USE INDEX(indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制MYSQL忽略索引

  key_len:使用的索引的长度。在不损失精确性的情况下,长度越短越好

  ref:显示索引的哪一列被使用了,如果可能的话,是一个常数

  rows:MYSQL认为必须检查的用来返回请求数据的行数

  Extra:关于MYSQL如何解析查询的额外信息。坏的例子是Using temporary和Using filesort,意思MYSQL根本不能使用索引,结果是检索会很慢

  三、如何选择合适的列建立索引?

  http://www.cnblogs.com/helloyb/p/4159929.html

  四、其他查询优化

  优化Limit查询

  举例:SELECT * FROM UserInfo ORDER BY id DESC LIMIT 100000,1;

  LIMIT 100000,1 需要排序TOP 100001行(再丢弃100000行,取1行)

  http://www.cnblogs.com/skylerjiang/p/6281997.html

  http://geek.csdn.net/news/detail/105891

  http://www.jb51.net/article/51934.htm

  选择合适的数据类型

  使用bigint来存储IP地址,利用INET_ATON(),INET_NTOA()两个函数来进行转换

  http://www.jb51.net/article/29962.htm

  http://www.cnblogs.com/drake-guo/p/6215554.html

相关阅读

吐槽几句

  "(必填)"

  "(选填)"

  "(必填,不公开)"

(必填,最多100个字符。)(大家的评论

先说这么多吧