Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ MySQL full-index scan bug     - MySQL rollback insert statement causes ibd file grows double (Database)

- Nginx + ownCloud + PHP + MySQL to build personal private cloud under CentOS7 (Server)

- N1QL bring SQL-like query NoSQL database experience (Database)

- C ++ 11 feature: auto keyword (Programming)

- Linux System Getting Started Tutorial: How to find the maximum memory your system supports (Linux)

- ORA-00845: MEMORY_TARGET not supported on this system Problem (Database)

- Spring-depth understanding of the various annotations (Programming)

- To install OwnCloud 7.0.4 under Ubuntu (Linux)

- CentOS install Java 1.8 (Linux)

- fcntl file locking function add (Programming)

- To help you easily protect the Linux System (Linux)

- Java Concurrency: synchronized (Programming)

- Partition contrast manifestations under Windows and Linux (Linux)

- SQL in the specific internal Oracle process (Database)

- To teach you how to safely send mail (Linux)

- Analysis: Little Notebook facing a major security threat secure online (Linux)

- Configure the ASM process on Red Hat Linux 6.5 (Database)

- Python several standard types of built-in functions (Programming)

- Arronax allows you to easily create desktop startup file (Linux)

- Java garbage collection and heap memory layout (Programming)

 
         
  MySQL full-index scan bug
     
  Add Date : 2017-08-31      
         
         
         
  First, Introduction

Find a slow query, the query time 1.57s, check the presence of the correct combination of the index table structure where field conditions, under the right circumstances optimizer should choose the combination of the index, rather than why can cause slow query when slowlog inspection of a business database yet ? Let us look at this article to slowly analysis.

Second, the analysis

Case in MySQL database version 5.6.16 will produce sql environment make the appropriate changes, where conditions remain unchanged. Readers can test other versions.

root @ rac1 10:48:11> explain select id,
 
    -> Gmt_create,

    -> Gmt_modified,

    -> Order_id,

    -> Service_id,

    -> Seller_id,

    -> Seller_nick,

    -> Sale_type

    -> From lol

    -> Where seller_id = 1501204

    -> And service_id = 1

    -> And sale_type in (3, 4)

    -> And use_status in (3, 4, 5, 6)

    -> And process_node_id = 6 order by id desc limit 0,20 \ G

*************************** 1. row ******************** *******

          id: 1

  select_type: SIMPLE

        table: lol

        type: index

possible_keys: idx_sellerid, idx_usestatus_saletype, idx_sellerid_saletype, idx_sidustsvidtype

          key: PRIMARY

      key_len: 8

          ref: NULL

        rows: 3076

        Extra: Using where

1 row in set (0.00 sec)

analysis

MySQL execution plan chosen by the main key to access the data. Note that the implementation plan is the access type index, while the index means that the SQL query when the secondary index, the index of the two full-index scan, there is no filtering

This behavior is unreasonable, because where conditions are contained in the query, a reasonable implementation of the plan should be access type range.

We forced the index to see the results

root @ rac1 10:48:07> explain select id,
 
    -> Gmt_create,

    -> Gmt_modified,

    -> Order_id,

    -> Service_id,

    -> Seller_id,

    -> Seller_nick,

    -> Sale_type

    -> From lol force index (idx_sidustsvidtype)

    -> Where seller_id = 1501204

    -> And service_id = 1

    -> And sale_type in (3, 4)

    -> And use_status in (3, 4, 5, 6)

    -> And process_node_id = 6 order by id desc limit 0,20 \ G

*************************** 1. row ******************** *******

          id: 1

  select_type: SIMPLE

        table: lol

        type: range

possible_keys: idx_sidustsvidtype

          key: idx_sidustsvidtype

      key_len: 19

          ref: NULL

        rows: 5178

        Extra: Using where; Using filesort

1 row in set (0.00 sec)

analysis

Plus mandatory implementation plan is in line with the index after the expected time to perform sql decreased from 1.57s to 0.01s. Therefore, we speculate is the optimizer to choose an index when there is a problem

We found that combination of Source and optimize_trace first stage optimization when the optimizer did choose the idx_sidustsvidtype and opted range accessed because sql statements contained in order by, the optimizer attempts to optimize order by limit the time

Clear the way of quick access to saved variables (the original is preserved range, but was asked to empty), eventually found the cost of using the sort index (here id) is higher than the composite index (here idx_sidustsvidtype) when, chose idx_sidustsvidtype

But the tragedy is that this time the right of access method has been emptied, can not be restored, and this is the root cause of this bug # 78993 of.

According to the analysis, we can also use another workaround ---- remove the order by. Of course, this all business development and communication invasion must confirm sql result set is unique, if not the only, or to use other methods.

root @ rac1 10:48:15> explain select id,
 
    -> Gmt_create,

    -> Gmt_modified,

    -> Order_id,

    -> Service_id,

    -> Seller_id,

    -> Seller_nick,

    -> Sale_type

    -> From lol

    -> Where seller_id = 1501204

    -> And service_id = 1

    -> And sale_type in (3, 4)

    -> And use_status in (3, 4, 5, 6)

    -> And process_node_id = 6 \ G

*************************** 1. row ******************** *******

          id: 1

  select_type: SIMPLE

        table: lol

        type: range

possible_keys: idx_sellerid, idx_uts_stp, idx_sid_stpe, idx_sidustsvidtype

          key: idx_sidustsvidtype

      key_len: 19

          ref: NULL

        rows: 5178

        Extra: Using where

1 row in set (0.00 sec)

Third, the summary
a modifying SQL, add the correct hint.
b's order by removing unnecessary needs and developing communication confirm whether the impact of the business logic.
c modify optimization bug, keep multiple access paths, do not clean up the quick access method saved variables found when the price is higher than the composite index orderby, you can choose the best access path.
     
         
         
         
  More:      
 
- How to build Memcached Docker container (Server)
- Linux 6 use UDEV binding shared storage (Linux)
- How apt-get limited use IPv4 or IPv6 protocol to download (Linux)
- The correct way to open Xcode - Debugging (Programming)
- Manager Docker browser (Server)
- How to Install Linux kernel 4.0 on Ubuntu / CentOS (Linux)
- Vagrant Getting Start (Linux)
- The most concise Systemd tutorial, just ten minutes (Linux)
- Through the source code to install MySQL 5.6.26 under CentOS6 (Database)
- Ubuntu 14.04 virtual machine switching desktop environments (Linux)
- Laravel 4 Expansion Pack (Server)
- Qt signals and slots mechanism (Programming)
- Python decorators to learn and practice the actual usage scenarios (Programming)
- Install the latest Eclipse IDE in Ubuntu (Linux)
- CentOS RedHat YUM Source Extensions Supplement (including 32-bit, 64-bit) (Linux)
- Java rewrite equals method (Programming)
- How to remove the files inside the privacy of data on Linux (Linux)
- MySQL 5.7 perfectly distributed transaction support (Database)
- CentOS7 installation GAMIT and GMT (Linux)
- HTML5 Application Cache (Programming)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.