|
但是，用直方图而不是索引有以下两个原因：
- 维护一个索引有代价。每一次的insert、update、delete都会需要更新索引，会对性能有一定的影响。而直方图一次创建永不更新，除非明确去更新它。所以不会影响insert、update、delete的性能。
- 如果有索引，优化器用使用index dives技术来估算符合条件范围的记录数量。这种方式也是有代价的，特别是查询语句条件中有很长的IN列表。直方图相对而言代价小，因此可能更合适。
检索统计直方图
统计直方图以JSON的形式存在数据字典中。可以用内建的JSON函数built-in JSON functions从直方图获取一些信息。举例来说，如果需要知道amount列的直方图的创建或者更新时间，可以用JSON unquoting extraction operator来获取信息：
- mysql> SELECT
- -> HISTOGRAM->>'$."last-updated"' AS last_updated
- -> FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
- -> WHERE
- -> SCHEMA_NAME = "sakila"
- -> AND TABLE_NAME = "payment"
- -> AND COLUMN_NAME = "amount";
- +----------------------------+
- | last_updated |
- +----------------------------+
- | 2017-09-15 11:54:25.000000 |
- +----------------------------+
如果要查找实际有多少个buckets，以及用analyze table时指定了多少个buckets，可以如下：
- mysql> SELECT
- -> TABLE_NAME,
- -> COLUMN_NAME,
- -> HISTOGRAM->>'$."number-of-buckets-specified"' AS num_buckets_specified,
- -> JSON_LENGTH(HISTOGRAM, '$.buckets') AS num_buckets_created
- -> FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
- -> WHERE
- -> SCHEMA_NAME = "sakila";
- +------------+--------------+-----------------------+---------------------+
- | TABLE_NAME | COLUMN_NAME | num_buckets_specified | num_buckets_created |
- +------------+--------------+-----------------------+---------------------+
- | payment | amount | 32 | 19 |
- | payment | payment_date | 32 | 32 |
- +------------+--------------+-----------------------+---------------------+
经测试，num_buckets_created与字段的distinct值很接近，近似相等；但是num_buckets_created不会大于num_buckets_specified。如果num_buckets_created与num_buckets_specified相等，那么存在可能，在创建直方图的时候指定的buckets不够多，那么此时可以通过增加buckets的数量，来提高直方图的准确性。
buckets可以设置为1到1024
优化器trace
如果你想要知道直方图做了什么，最简单的方式就是看一下执行计划：
- mysql> EXPLAIN SELECT * FROM customer WHERE c_birth_day BETWEEN 1 AND 10;
- +----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
- +----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
- | 1 | SIMPLE | customer | NULL | ALL | NULL | NULL | NULL | NULL | 98633 | 11.11 | Using where |
- +----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
- 1 row in set, 1 warning (0.00 sec)
- mysql> ANALYZE TABLE customer UPDATE HISTOGRAM ON c_birth_day WITH 32 BUCKETS;
- +----------------+-----------+----------+--------------------------------------------------------+
- | Table | Op | Msg_type | Msg_text |
- +----------------+-----------+----------+--------------------------------------------------------+
- | tpcds.customer | histogram | status | Histogram statistics created for column 'c_birth_day'. |
- +----------------+-----------+----------+--------------------------------------------------------+
- 1 row in set (0.10 sec)
- mysql> EXPLAIN SELECT * FROM customer WHERE c_birth_day BETWEEN 1 AND 10;
- +----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
- +----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
- | 1 | SIMPLE | customer | NULL | ALL | NULL | NULL | NULL | NULL | 98633 | 32.12 | Using where |
- +----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
- 1 row in set, 1 warning (0.00 sec)
（编辑：晋中站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
