MySql 数据量达到一定数量时查询会变得越来越慢,这时候需要建立合理的索引。

MySql 索引操作

在数据达到一定量级时,光建立索引还是不够。根据业务需要可查询最新的1百万数据,在实际情况中 后面的数据用户一般是不需要的。

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

如explain select * from test1 where id=1;会出现:id selecttype table type possible_keys key key_len ref rows extra各列。

各个属性的含义
id
select查询的序列号
select_type
select查询的类型,主要是区别普通查询和联合查询、子查询之类的复杂查询。
table
输出的行所引用的表。
type
联合查询所使用的类型。
type显示的是访问类型,是较为重要的一个指标,结果值从好到坏依次是:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说,得保证查询至少达到range级别,最好能达到ref。
possible_keys
指出MySQL能使用哪个索引在该表中找到行。如果是空的,没有相关的索引。这时要提高性能,可通过检验WHERE子句,看是否引用某些字段,或者检查字段不是适合索引。
key
显示MySQL实际决定使用的键。如果没有索引被选择,键是NULL。
key_len
显示MySQL决定使用的键长度。如果键是NULL,长度就是NULL。文档提示特别注意这个值可以得出一个多重主键里mysql实际使用了哪一部分。
ref
显示哪个字段或常数与key一起被使用。
rows
这个数表示mysql要遍历多少数据才能找到,在innodb上是不准确的。
Extra
如果是Only index,这意味着信息只用索引树中的信息检索出的,这比扫描整个表要快。
如果是where used,就是使用上了where限制。
如果是impossible where 表示用不着where,一般就是没查出来啥。
如果此信息显示Using filesort或者Using temporary的话会很吃力,WHERE和ORDER BY的索引经常无法兼顾,如果按照WHERE来确定索引,那么在ORDER BY时,就必然会引起Using filesort,这就要看是先过滤再排序划算,还是先排序再过滤划算。

利用这个可以查看自己的查询是不是利用索引进行查询还是全文索引。

利用profiling分析慢sql语句

查看Mysql 是否开启profile

mysql> select @@profiling;

启用profiling。变量profiling是用户变量,每次都得重新启用

mysql> set profiling = 1;  

然后在执行Sql 语句查询

在用 mysql> show profiles;; 来查询最近查询的sql 运行情况

sql运行明细

如果想看某个具体的查询,可以 使用

mysql> show profile for query 2;来进行显示、

如果想看更加详细的信息 可以使用  show profile cpu,block io,memory,swaps,context switches,source for query 2; 

来进行

Waiting for query cache lock  是查询缓存 ,可以通过更改mysql 的配置文件来对其大小进行限制。

还有就是Sending data 所谓的“Sending data”并不是单纯的发送数据,而是包括“收集 + 发送 数据”。

sending data 是收集数据和发送数据的关键。

mysql使用“索引”完成查询结束后,mysql得到了一堆的行id,如果有的列并不在索引中,mysql需要重新到“数据行”上将需要返回的数据读取出来返回个客户端。

如果是send data 话费的时间太长 可以查看数据库的某个字段是不是text或者该字段太长。

nnodb每页16K,除去一些数据结构空间和保留空间,大概1/10的行需要采用溢出存储(即将数据存放在另外的页中),一旦采用了这种方式存储,返回数据的时候 本来是顺序读取的数据,就变成了随机读取 了,所以导致性能急剧下降。

1) 查询时去掉大字段的查询 ,但这受限于业务的实现,可能需要业务做较大调整

2) 增大Innodb buffer pool ,但由于Innodb buffer pool会根据查询进行自动调整,因此如果gm_platform_info不是热门表,作用也不是很明显

3) 表结构优化 ,将大字段拆分到另外的表,这个改动较大,需要已有业务配合修改