站长技术精华帖:MySQL索引无效实例分析

一朋友最近新上线一个项目，本地测试环境跑得好好的，部署到线上却慢得像蜗牛一样。后来查询了一下发现一个sql执行了16秒，有些长的甚至80秒。本地运行都是毫秒级别的查询。下面记录一下困扰了两天的，其中一条sql的优化。

表结构及现象描述：

表wp_goods数据量10094，sys_users数据量14044， jd_jianding数据量9142。

执行sql：

耗时16秒，而本地数据库执行耗时0.02毫秒。

原因分析：

1、explain/desc 发现left join索引不起作用。

分析结果：

索引无效，Using join buffer (Block Nested Loop)相当于遍历表查询。

2、profile分析了下，发现几乎所有耗时都在sending data且缓存sending cached result to clien没开启。

show variables like '%cache%';

query_cache_type为off，在配置文件/etc/my.cf中添加“query_cache_type = 1”配置项并重启。

执行后耗时10s，如果将order by去掉后耗时3秒。即使是耗时3秒也是无法接受的。

通过profile分析下具体耗时

发现几乎所有耗时都在sending data部分。

3、查看jd_jianding表索引，show index from jd_jianding发现cardinality的值为1。

4、优化表jd_jianding，analyze table jd_jianding，再次执行仍然如此。

然而mysql的文档时这么说的。The higher the cardinality, the greater the chance that MySQL uses the index when doing joins.

An estimate of the number of unique values in the index. This is updated by running ANALYZE TABLE or myisamchk -a. Cardinality is counted based on statistics stored as integers, so the value is not necessarily exact even for small tables. The higher the cardinality, the greater the chance that MySQL uses the index when doing

大意如下：

1)、它代表的是索引中唯一值的数目的估计值。如果是myisam引擎，这个值是一个准确的值。如果是innodb引擎，这个值是一个估算的值，每次执行show index 时，可能会不一样

2)、创建Index时(primary key除外)，MyISAM的表Cardinality的值为null，InnoDB的表Cardinality的值大概为行数；

3)、值的大小会影响到索引的选择

4)、创建Index时，MyISAM的表Cardinality的值为null，InnoDB的表Cardinality的值大概为行数。

5)、可以通过Analyze table来更新一张表或者mysqlcheck -Aa来进行更新整个数据库

6)、可以通过 show index 查看其值

5、查看表jd_jianding字段wid的值全为默认值0，于是将其中一条记录的wid字段值update为非0；再次analyze table jd_jianding。

再次执行，效果杠杠的，耗时只有0.02毫秒。困扰两天的问题终于得到了解决。

6、把步骤4修改的字段值还原回来。

后记，原因大致如下：

1、mysql没有开启查询缓存。

2、新添加字段默认值都一样，导致索引不可用。