mysql简单应用

1.慢查询

• 查询慢查询的次数：show status like 'slow_queries':不仅查的select,还包括其他操作。
• 查询慢查询的时间设置：show variables like 'long_query_time'
• 修改慢查询时间：set long_query_time =?
• 慢查询日志记录:bin\mySqld.exe --safe-mode --slow-query-log  mysql 5.5及以上 
•                          bin\muysqld.exe -log-slow-queries =d:\abc.log 低版本
•      先关闭mysql,再重新启动，如果启用慢查询日志默认文件把文件放在my.ini里面记录的位置。

2.sql分析

 可以通过Explain语句分析sql.

eg:explain select * from temp;

 

分析结果形式如下： 

table |  type | possible_keys | key | key_len  | ref | rows | Extra 

EXPLAIN列的解释： 

  www.2cto.com  

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根本不能使用索引，结果是检索会很慢 

 

extra列返回的描述的意义 

  www.2cto.com  

Distinct 

一旦MYSQL找到了与行相联合匹配的行，就不再搜索了 

 

Not exists 

MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行， 

 

就不再搜索了 

 

Range checked for each 

 

Record（index map:#） 

没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一 

 

Using filesort 

看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行 

 

Using index 

列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候 

 

Using temporary 

看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上   www.2cto.com  

 

Where used 

使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题 

 

不同连接类型的解释（按照效率高低的顺序排序） 

 

system 

表只有一行：system表。这是const连接类型的特殊情况 

 

const 

表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待 

 

eq_ref 

在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用 

 

ref 

这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好   www.2cto.com  

 

range 

这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况 

 

index 

这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据） 

 

ALL 

这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免

 

3.查询数据库设置属性

 A.show global variables  

 B.show variables like 'query_cache%' 

4.建索引的几大原则

1>.最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2>.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

3>.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录

4>.索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

5>.尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可