2018-06-16 16:00:05

a little about query of mysql

There some theory about query on mysql we should know so that we can use it more efficiently.

some important theories

  • avoid reduncdant data access such as appliction retrieving more data than nedd and server level get reduncdant data
  • sometimes a fewer queries would be better than a single query
  • a scan was used based on whether the best index spanned more than 30% of the table, but a fixed percentage no longer determines the choice between using an index or a scan. This indicate whether use the index to get data you want is detemined by optimizer and the data on the table

query execution flow

  1. client/server procotol, half duplex, client need to recieve data as soon as possible
  2. query cache, must check privileges and then case-sensitive hash lookup
  3. parser
  4. preprocessor
  5. query optimizer, find best option of query and use a cost-based optimizer
  6. query execution engine
  7. storage engine
  8. data

join strategy

  • MySQL considers every query a join
  • MySQL runs a loop to find a row from a table(maybe some where condition), then runs a nested loop to find a matching row in the next table.
  • the join dermined by optimizer
  • we can use the hint STRAIGHT JOIN to specify the order of join, but it maybe the better idea to let optimizer decide the join order in most of the time

union strategy

  • MySQL executes a UNION as a series of single queries whose results are spooled into a temporary table, then read out again.

index merge

  • although mysql can use index merge on some query, but it more optimal to use UNION because the index merge may need more memory and CPU and the strategy of index merge is limited
  • In EXPLAIN output, the Index Merge method appears as index_merge in the type column. In this case, the key column contains a list of indexes used, and key_len contains a list of the longest key parts for those indexes.
  • only used on seperate single index
  • see other articles on my blog

    icp(index condition push down)

  • only used on multicolumn index to push where condition which could make use of the index to filter out data on at storage engine level
  • see other articles on my blog

order by

  • there are so many rules list about what type of query can use index on the book of High Performance SQL which should be conclude as a single rule, that is if data which server level retrieved from storage engine are ordered, the query include order by clause could use index to sort
  • if the sort result can not make use of index, it'll sort on memory or disk, and worst the user might not know where it execute the sort operation.
  • for join which use order by clause, which can make use of index only when it's columns used on order by clause are all from the first table display in EXPLAIN or the driver table may be more exactly

group by

  • the strategy of group by clause whether can make use of index is same as order by clause
  • sometimes it is more efficient to do superaggregation in you application instead use the clause ROLLUP

the optimzation LIMIT and offset

  • if table is very large, could use defer join to use covering index.
    mysql> explain select * from largeb inner join (select id from largeb order by uid desc limit 50, 5) as a using(id);
    +----+-------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-------------+
    | id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
    +----+-------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-------------+
    |  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL          | NULL    | NULL    | NULL |   55 |   100.00 | NULL        |
    |  1 | PRIMARY     | largeb     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | a.id |    1 |   100.00 | NULL        |
    |  2 | DERIVED     | largeb     | NULL       | index  | NULL          | idx_uid | 4       | NULL |   55 |   100.00 | Using index |
    +----+-------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-------------+
    3 rows in set, 1 warning (0.00 sec)
    
  • get prev page's last data index and then use it to get this page's data, and maybe return page size number plus one per page
  • limit the maximum page number
  • use cache to cache some more data

on storage or server level?

storage engine just access data use the where condition which have index which optimizer decide to use, the other where condition would be filter out at server lavel, and same as the row lock stratege which means the storage level would lock rows it even shouldn't lock

COUNT()

  • it counts values(not NULL) and rows
  • if you want to know the number of rows in the result, you should always use COUNT(*)

a few subtles about unequatity

it's very strange that always someone tell me that unequatity can not make use index, what I want to say is it depends on the data on the table and your query syntx which optimizer depends on

mysql> show index from lowcardinality;
+---------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table         | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| lowcardinaliy |          0 | PRIMARY        |            1 | id          | A         |     4837166 |     NULL | NULL   |      | BTREE      |         |               |
| lowcardinaliy |          1 | idx_uid        |            1 | uid         | A         |        1001 |     NULL | NULL   |      | BTREE      |         |               |
| lowcardinaliy |          1 | idx_char25     |            1 | char25      | A         |        8436 |     NULL | NULL   |      | BTREE      |         |               |
| lowcardinaliy |          1 | idx_uid_char25 |            1 | uid         | A         |        1001 |     NULL | NULL   |      | BTREE      |         |               |
| lowcardinaliy |          1 | idx_uid_char25 |            2 | char25      | A         |        1001 |     NULL | NULL   |      | BTREE      |         |               |
+---------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

mysql> explain select * from lowcardinaliy where uid=2142140633 and char25!='zfqQXGkVLYnNTsHihoFgRwPa';
+----+-------------+---------------+------------+-------+-----------------------------------+----------------+---------+------+------+----------+-----------------------+
| id | select_type | table         | partitions | type  | possible_keys                     | key            | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------------+------------+-------+-----------------------------------+----------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | lowcardinaliy | NULL       | range | idx_uid,idx_char25,idx_uid_char25 | idx_uid_char25 | 31      | NULL |    2 |   100.00 | Using index condition |
+----+-------------+---------------+------------+-------+-----------------------------------+----------------+---------+------+------+----------+-----------------------+

mysql> explain select * from lowcardinaliy where uid=2142140633 and char25!='zfqQXGkVLYnNTa';
+----+-------------+---------------+------------+------+-----------------------------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table         | partitions | type | possible_keys                     | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------------+------------+------+-----------------------------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | lowcardinaliy | NULL       | ref  | idx_uid,idx_char25,idx_uid_char25 | idx_uid | 4       | const | 4970 |    54.13 | Using where |
+----+-------------+---------------+------------+------+-----------------------------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

Permanent link of this article:http://nulls.cc/post/query_about_mysql

-- EOF --