设为首页收藏本站
查看: 80|回复: 0

[PHP] Mysql的一条SQL优化(二)_MySQL-mysql教程

[复制链接]

论坛元老

Rank: 6Rank: 6

积分
34274
主题
17031
UID
1347
M币
67
贡献
17176

  • 发表于 2017-5-12 16:11:00 | 显示全部楼层 |阅读模式
    开发过来查看我做的调整,发现我创建的索引其实在另一个测试库上(测试库2)已经创建好了,但为什么测试库2上还是慢呢?
    于是上测试库2查看,发现之前我创建的2个列的索引确实已经有了,并且还多了一个索引i_msource_type:
    Indexes Columns Index_Type
    PRIMARY ext_id Unique
    i_mobile mobile
    i_msource_type msource_type
    i_msource_id msource_id
    而msource_type列的可选择性是非常低了,基本上到这里我大概知道是怎么回事了,剩下的事情就是验证我的猜测,explain:
    mysql> explain select ext_id, mid, msource_id, msource_type, referee, mobile, tel, mpassword,status from m_vip_ext where (msource_id='xx1391434680574433' and msource_type=1 ) or ( mobile='1391434680574433' and msource_type=1);
    +----+-------------+-----------+------+--------------------------------------+----------------+---------+-------+-------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+-----------+------+--------------------------------------+----------------+---------+-------+-------+-------------+
    | 1 | SIMPLE | m_vip_ext | ref | i_mobile,i_msource_type,i_msource_id | i_msource_type | 4 | const | 58383 | Using where |
    +----+-------------+-----------+------+--------------------------------------+----------------+---------+-------+-------+-------------+
    1 row in set (0.17 sec)
    果不其然,Mysql的优化器并没有选择之前的2个索引,而是选择了可选择性很低的i_msource_type,这无疑是比全表扫描效率更低的一种方式。
    果断drop掉索引:
    mysql> alter table m_vip_ext drop index i_msource_type;
    Query OK, 0 rows affected (0.02 sec)
    Records: 0 Duplicates: 0 Warnings: 0
    再次explain:
    mysql> explain select ext_id, mid, msource_id, msource_type, referee, mobile, tel, mpassword,status from m_vip_ext where (msource_id='xx1391434680574433' and msource_type=1 ) or ( mobile='1391434680574433' and msource_type=1);
    +----+-------------+-----------+-------------+-----------------------+-----------------------+---------+------+------+------------------------------------------------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+-----------+-------------+-----------------------+-----------------------+---------+------+------+------------------------------------------------------+
    | 1 | SIMPLE | m_vip_ext | index_merge | i_mobile,i_msource_id | i_msource_id,i_mobile | 98,99 | NULL | 2 | Using sort_union(i_msource_id,i_mobile); Using where |
    +----+-------------+-----------+-------------+-----------------------+-----------------------+---------+------+------+------------------------------------------------------+
    1 row in set (0.17 sec)
    mysql> explain select ext_id, mid, msource_id, msource_type, referee, mobile, tel, mpassword,status from m_vip_ext where (msource_id='xx1391434680574433' and msource_type=1 ) or ( mobile='1391434680574433' and msource_type=1) \G;
    *************************** 1. row ***************************
    id: 1
    select_type: SIMPLE
    table: m_vip_ext
    type: index_merge
    possible_keys: i_mobile,i_msource_id
    key: i_msource_id,i_mobile
    key_len: 98,99
    ref: NULL
    rows: 2
    Extra: Using sort_union(i_msource_id,i_mobile); Using where
    1 row in set (0.00 sec)


    ERROR:
    No query specified
    mysql> show status like '%cost%';
    +-----------------+----------+
    | Variable_name | Value |
    +-----------------+----------+
    | Last_query_cost | 7.328006 |
    +-----------------+----------+
    1 row in set (0.00 sec)
    OK了,开发人员用测试库2做的压测,反映并发也能上1000了。
    回复

    使用道具 举报

    您需要登录后才可以回帖 登录 | 立即注册

    本版积分规则

    在我站开通SVIP可同时获得17个站点VIP资源 立即登录 立即注册
    快速回复 返回顶部 返回列表