mysql与kingbase适配 mysql和什么搭配

转载

daleiwang 2023-11-10 10:38:20

文章标签 mysql与kingbase适配 mysql 数据子查询 文章分类 MySQL 数据库

以联合索引(a,b,c)为例

建立这样的索引相当于建立了索引a、ab、abc三个索引。一个索引顶三个索引当然是好事，毕竟每多一个索引，都会增加写操作的开销和磁盘空间的开销。
覆盖(动词)索引。同样的有联合索引（a,b,c），如果有如下的sql: select a,b,c from table where a=xxx and b = xxx。那么MySQL可以直接通过遍历索引取得数据，而无需读表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一
索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select * from table where a = 1 and b =2 and c = 3,假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W*10%=100w 条数据，然后再回表从100w条数据中找到符合b=2 and c= 3的数据，然后再排序，再分页；如果是复合索引，通过索引筛选出1000w *10% *10% *10%=1w，然后再排序、分页，哪个更高效，一眼便知

1. 示例表结构

CREATE TABLE `test_user` (
  `name`     varchar(20) DEFAULT NULL,
  `province` int(11)     DEFAULT NULL,
  `sex`      varchar(20) DEFAULT NULL,
  `birthday` int(11)     DEFAULT NULL,
  `phone`    double      DEFAULT NULL
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

CREATE INDEX test_user_name_phone_province_index
  ON test_user (name, phone, province);

2. 执行计划说明

以下全部详细解析explain各个属性含义：

+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys                       | key                                 | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------+------+----------+-------+

各属性含义：

id: 查询的序列号
select_type: 查询的类型，主要是区别普通查询和联合查询、子查询之类的复杂查询

SIMPLE：查询中不包含子查询或者UNION
查询中若包含任何复杂的子部分，最外层查询则被标记为：PRIMARY
在SELECT或WHERE列表中包含了子查询，该子查询被标记为：SUBQUERY

table: 输出的行所引用的表
type: 访问类型

ALL: 扫描全表
index: 扫描全部索引树
range: 扫描部分索引，索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、<、>等的查询
ref: 使用非唯一索引或非唯一索引前缀进行的查找
（eq_ref和const的区别：）
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
const, system: 单表中最多有一个匹配行，查询起来非常迅速，例如根据主键或唯一索引查询。system是const类型的特例，当查询- 的表只有一行的情况下，使用system。
NULL: 不用访问表或者索引，直接就能得到结果，如select 1 from test where 1

key: 显示MySQL实际决定使用的索引。如果没有索引被选择，是NULL
key_len: 使用到索引字段的长度
注：key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。
ref: 显示哪个字段或常数与key一起被使用
rows: 这个数表示mysql要遍历多少数据才能找到，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数，在innodb上可能是不准确的
Extra: 执行情况的说明和描述。包含不适合在其他列中显示但十分重要的额外信息。
Using index：表示使用索引，如果只有 Using index，说明他没有查询到数据表，只用索引表就完成了这个查询，这个叫覆盖索引。
Using where：表示条件查询，如果不读取表的所有数据，或不是仅仅通过索引就可以获取所有需要的数据，则会出现 Using where。

3. 执行计划分析

mysql> explain select *
    ->         from test_user
    ->         where name = '张三';
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys                       | key                                 | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test_user | NULL       | ref  | test_user_name_phone_province_index | test_user_name_phone_province_index | 63      | const |   11 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

mysql> explain select *
    ->         from test_user
    ->         where phone = '13546294373';
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | test_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4753980 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select *
    ->         from test_user
    ->         where province = '532130';
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | test_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4753980 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select *
    ->         from test_user
    ->         where phone = '13546294373';
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | test_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4753980 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select *
    ->         from test_user
    ->         where province = '532130';
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | test_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4753980 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select *
    ->         from test_user
    ->         where name = '张三' and province = '532130';
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table     | partitions | type | possible_keys                       | key                                 | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | test_user | NULL       | ref  | test_user_name_phone_province_index | test_user_name_phone_province_index | 63      | const |   11 |    10.00 | Using index condition |
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select *
    ->         from test_user
    ->         where name = '张三' and phone = '13546294373';
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys                       | key                                 | key_len | ref         | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | test_user | NULL       | ref  | test_user_name_phone_province_index | test_user_name_phone_province_index | 72      | const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select *
    ->         from test_user
    ->         where  phone = '13546294373' and province = '532130';
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | test_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4753980 |     1.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select *
    ->         from test_user
    ->         where     province = '532130' and phone = '13546294373';
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | test_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4753980 |     1.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select *
    ->         from test_user
    ->         where name = '张三' and phone = '13546294373'  and province = '532130';
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys                       | key                                 | key_len | ref               | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | test_user | NULL       | ref  | test_user_name_phone_province_index | test_user_name_phone_province_index | 77      | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

4. 现象总结

对于联合索引

CREATE INDEX test_user_name_phone_province_index
  ON test_user (name, phone, province);

在查询中我们能用到的索引的是

name
name phone
name phone province

对于其他顺序,或者其他字段我们不能使用该联合索引,这个就是mysql联合索引原则

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：spark批处理一次多少g spark能处理500万条数据不

下一篇：vs修改python库位置 vs安装python库

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯

mysql与kingbase适配 mysql和什么搭配

mysql与kingbase适配 mysql和什么搭配

1. 示例表结构

2. 执行计划说明

3. 执行计划分析

4. 现象总结

51CTO博客