mysql查询优化器应该怎么使用

发布网友 发布时间：2022-04-22 10:46

我来回答

共2个回答

热心网友 时间：2022-04-07 16:20

在开始演示之前，我们先介绍下两个概念。

概念一，数据的可选择性基数，也就是常说的cardinality值。

查询优化器在生成各种执行计划之前，得先从统计信息中取得相关数据，这样才能估算每步操作所涉及到的记录数，而这个相关数据就是cardinality。简单来说，就是每个值在每个字段中的唯一值分布状态。

比如表t1有100行记录，其中一列为f1。f1中唯一值的个数可以是100个，也可以是1个，当然也可以是1到100之间的任何一个数字。这里唯一值越的多少，就是这个列的可选择基数。

那看到这里我们就明白了，为什么要在基数高的字段上建立索引，而基数低的的字段建立索引反而没有全表扫描来的快。当然这个只是一方面，至于更深入的探讨就不在我这篇探讨的范围了。

概念二，关于HINT的使用。

这里我来说下HINT是什么，在什么时候用。

HINT简单来说就是在某些特定的场景下人工协助MySQL优化器的工作，使她生成最优的执行计划。一般来说，优化器的执行计划都是最优化的，不过在某些特定场景下，执行计划可能不是最优化。

比如：表t1经过大量的频繁更新操作，（UPDATE,DELETE,INSERT），cardinality已经很不准确了，这时候刚好执行了一条SQL，那么有可能这条SQL的执行计划就不是最优的。为什么说有可能呢？

来看下具体演示

譬如，以下两条SQL，

A：

select * from t1 where f1 = 20;


B：

select * from t1 where f1 = 30;


如果f1的值刚好频繁更新的值为30，并且没有达到MySQL自动更新cardinality值的临界值或者说用户设置了手动更新又或者用户减少了sample page等等，那么对这两条语句来说，可能不准确的就是B了。

这里顺带说下，MySQL提供了自动更新和手动更新表cardinality值的方法，因篇幅有限，需要的可以查阅手册。

那回到正题上，MySQL 8.0 带来了几个HINT，我今天就举个index_merge的例子。

示例表结构：

mysql> desc t1;+------------+--------------+------+-----+---------+----------------+| Field      | Type         | Null | Key | Default | Extra          |+------------+--------------+------+-----+---------+----------------+| id         | int(11)      | NO   | PRI | NULL    | auto_increment || rank1      | int(11)      | YES  | MUL | NULL    |                || rank2      | int(11)      | YES  | MUL | NULL    |                || log_time   | datetime     | YES  | MUL | NULL    |                || prefix_uid | varchar(100) | YES  |     | NULL    |                || desc1      | text         | YES  |     | NULL    |                || rank3      | int(11)      | YES  | MUL | NULL    |                |+------------+--------------+------+-----+---------+----------------+7 rows in set (0.00 sec)


表记录数：


mysql> select count(*) from t1;+----------+| count(*) |+----------+|    32768 |+----------+1 row in set (0.01 sec)


这里我们两条经典的SQL：

SQL C：

select * from t1 where rank1 = 1 or rank2 = 2 or rank3 = 2;


SQL D：

select * from t1 where rank1 =100  and rank2 =100  and rank3 =100;


表t1实际上在rank1,rank2,rank3三列上分别有一个二级索引。

那我们来看SQL C的查询计划。

显然，没有用到任何索引，扫描的行数为32034，cost为3243.65。

mysql> explain  format=json select * from t1  where rank1 =1 or rank2 = 2 or rank3 = 2\G*************************** 1. row ***************************EXPLAIN: {  "query_block": {    "select_id": 1,    "cost_info": {      "query_cost": "3243.65"    },    "table": {      "table_name": "t1",      "access_type": "ALL",      "possible_keys": [        "idx_rank1",        "idx_rank2",        "idx_rank3"      ],      "rows_examined_per_scan": 32034,      "rows_proced_per_join": 115,      "filtered": "0.36",      "cost_info": {        "read_cost": "3232.07",        "eval_cost": "11.58",        "prefix_cost": "3243.65",        "data_read_per_join": "49K"      },      "used_columns": [        "id",        "rank1",        "rank2",        "log_time",        "prefix_uid",        "desc1",        "rank3"      ],      "attached_condition": "((`ytt`.`t1`.`rank1` = 1) or (`ytt`.`t1`.`rank2` = 2) or (`ytt`.`t1`.`rank3` = 2))"    }  }}1 row in set, 1 warning (0.00 sec)


我们加上hint给相同的查询，再次看看查询计划。

这个时候用到了index_merge,union了三个列。扫描的行数为1103，cost为441.09，明显比之前的快了好几倍。

mysql> explain  format=json select /*+ index_merge(t1) */ * from t1  where rank1 =1 or rank2 = 2 or rank3 = 2\G*************************** 1. row ***************************EXPLAIN: {  "query_block": {    "select_id": 1,    "cost_info": {      "query_cost": "441.09"    },    "table": {      "table_name": "t1",      "access_type": "index_merge",      "possible_keys": [        "idx_rank1",        "idx_rank2",        "idx_rank3"      ],      "key": "union(idx_rank1,idx_rank2,idx_rank3)",      "key_length": "5,5,5",      "rows_examined_per_scan": 1103,      "rows_proced_per_join": 1103,      "filtered": "100.00",      "cost_info": {        "read_cost": "330.79",        "eval_cost": "110.30",        "prefix_cost": "441.09",        "data_read_per_join": "473K"      },      "used_columns": [        "id",        "rank1",        "rank2",        "log_time",        "prefix_uid",        "desc1",        "rank3"      ],      "attached_condition": "((`ytt`.`t1`.`rank1` = 1) or (`ytt`.`t1`.`rank2` = 2) or (`ytt`.`t1`.`rank3` = 2))"    }  }}1 row in set, 1 warning (0.00 sec)


我们再看下SQL D的计划：

不加HINT，

mysql> explain format=json select * from t1 where rank1 =100 and rank2 =100 and rank3 =100\G*************************** 1. row ***************************EXPLAIN: {  "query_block": {    "select_id": 1,    "cost_info": {      "query_cost": "534.34"    },    "table": {      "table_name": "t1",      "access_type": "ref",      "possible_keys": [        "idx_rank1",        "idx_rank2",        "idx_rank3"      ],      "key": "idx_rank1",      "used_key_parts": [        "rank1"      ],      "key_length": "5",      "ref": [        "const"      ],      "rows_examined_per_scan": 555,      "rows_proced_per_join": 0,      "filtered": "0.07",      "cost_info": {        "read_cost": "478.84",        "eval_cost": "0.04",        "prefix_cost": "534.34",        "data_read_per_join": "176"      },      "used_columns": [        "id",        "rank1",        "rank2",        "log_time",        "prefix_uid",        "desc1",        "rank3"      ],      "attached_condition": "((`ytt`.`t1`.`rank3` = 100) and (`ytt`.`t1`.`rank2` = 100))"    }  }}1 row in set, 1 warning (0.00 sec)


加了HINT，

mysql> explain format=json select /*+ index_merge(t1)*/ * from t1 where rank1 =100 and rank2 =100 and rank3 =100\G*************************** 1. row ***************************EXPLAIN: {  "query_block": {    "select_id": 1,    "cost_info": {      "query_cost": "5.23"    },    "table": {      "table_name": "t1",      "access_type": "index_merge",      "possible_keys": [        "idx_rank1",        "idx_rank2",        "idx_rank3"      ],      "key": "intersect(idx_rank1,idx_rank2,idx_rank3)",      "key_length": "5,5,5",      "rows_examined_per_scan": 1,      "rows_proced_per_join": 1,      "filtered": "100.00",      "cost_info": {        "read_cost": "5.13",        "eval_cost": "0.10",        "prefix_cost": "5.23",        "data_read_per_join": "440"      },      "used_columns": [        "id",        "rank1",        "rank2",        "log_time",        "prefix_uid",        "desc1",        "rank3"      ],      "attached_condition": "((`ytt`.`t1`.`rank3` = 100) and (`ytt`.`t1`.`rank2` = 100) and (`ytt`.`t1`.`rank1` = 100))"    }  }}1 row in set, 1 warning (0.00 sec)


对比下以上两个，加了HINT的比不加HINT的cost小了100倍。

总结下，就是说表的cardinality值影响这张的查询计划，如果这个值没有正常更新的话，就需要手工加HINT了。相信MySQL未来的版本会带来更多的HINT。

热心网友 时间：2022-04-07 17:38

、MySQL数据库几配置选项帮助我及捕获低效SQL语句 一slow_query_log 参数设置ON捕获执行间超定数值SQL语句 二long_query_time SQL语句执行间超数值记录志建议设置一或者更短 三slow_query_log_file 记录志文件名 四log_queries_not_using_indexes 参数设置ON捕获所未使用索引SQL语句尽管SQL语句能执行挺快 二、检测mysqlsql语句效率 一、通查询志 （一）、Windows启MySQL慢查询 MySQLWindows系统配置文件般my.ini找[mysqld]面加 代码 log-slow-queries = F:/MySQL/log/mysqlslowquerylog long_query_time = 二 （二）、Linux启用MySQL慢查询 MySQLWindows系统配置文件般my中国f找[mysqld]面加 代码 log-slow-queries=/data/mysqldata/slowquerylog long_query_time=二 说明 log-slow-queries = F:/MySQL/log/mysqlslowquery 慢查询志存放位置般目录要MySQL运行帐号写权限般都目录设置MySQL数据存放目录； long_query_time=二二表示查询超两秒才记录； 二.show processlist 命令 SHOW PROCESSLIST显示哪些线程运行您使用mysqladmin processlist语句信息 各列含义用途： ID列 标识要kill语句候用用命令杀掉查询 /*/mysqladmin kill 进程号 user列 显示单前用户root命令显示权限范围内sql语句 host列 显示语句哪ip哪端口发用于追踪问题语句用户 db列 显示进程目前连接哪数据库 command列 显示前连接执行命令般休眠（sleep）查询（query）连接（connect） time列 状态持续间单位秒 state列 显示使用前连接sql语句状态重要列续所状态描述请注意state语句执行某状态 sql语句查询例能需要经copying to tmp tableSorting resultSending data等状态才完 info列 显示sql语句度限所sql语句显示全判断问题语句重要依据 命令关键state列mysql列状态主要几种： Checking table 　检查数据表（自） Closing tables 　表修改数据刷新磁盘同关闭已经用完表快操作应该确认磁盘空间否已经满或者磁盘否处于重负 Connect Out 　复*务器连接主服务器 Copying to tmp table on disk 　由于临结集于tmp_table_size临表内存存储转磁盘存储节省内存 Creating tmp table 　创建临表存放部查询结 deleting from main table 　服务器执行表删除第部刚删除第表 deleting from reference tables 　服务器执行表删除第二部删除其表记录 Flushing tables 　执行FLUSH TABLES等待其线程关闭数据表 Killed 　发送kill请求给某线程线程检查kill标志位同放弃kill请求MySQL每主循环检查kill标志位些情况该线程能段才能死掉该线程程其线程锁住kill请求锁释放马效 Locked 　其查询锁住 Sending data 　处理SELECT查询记录同结发送给客户端 Sorting for group 　GROUP BY做排序 　Sorting for order 　ORDER BY做排序 Opening tables 　程应该快除非受其素干扰例执ALTER TABLE或LOCK TABLE语句行完前数据表其线程打尝试打表 Removing plicates 　执行SELECT DISTINCT式查询MySQL前阶段优化掉些重复记录MySQL需要再掉重复记录再结发送给客户端 Reopen table 　获表锁必须表结构修改才能获锁已经释放锁关闭数据表尝试重新打数据表 Repair by sorting 　修复指令排序创建索引 Repair with keycache 　修复指令利用索引缓存创建新索引比Repair by sorting慢些 Searching rows for update 　讲符合条件记录找备更新必须UPDATE要修改相关记录前完 Sleeping 　等待客户端发送新请求. System lock 　等待取外部系统锁前没运行mysqld服务器同请求同表通增加--skip-external-locking参数禁止外部系统锁 Upgrading lock 　INSERT DELAYED尝试取锁表插入新记录 Updating 　搜索匹配记录并且修改 User Lock 　等待GET_LOCK() Waiting for tables 　该线程通知数据表结构已经修改需要重新打数据表取新结构能重新打数据表必须等所其线程关闭表几种情况产通知：FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE,或OPTIMIZE TABLE waiting for handler insert 　INSERT DELAYED已经处理完所待处理插入操作等待新请求 　部状态应快操作要线程保持同状态几秒钟能问题发需要检查 　其状态没面列部查看服务器否存错误才用着 例图： 三、explain解SQL执行状态 explain显示mysql何使用索引处理select语句及连接表帮助选择更索引写更优化查询语句 使用select语句前加explain： 例： explain select surname,first_name form a,b where a.id=b.id 结图 EXPLAIN列解释 table 显示行数据关于哪张表 type 重要列显示连接使用何种类型差连接类型const、eq_reg、ref、range、indexheALL possible_keys 显示能应用张表索引空没能索引相关域WHERE语句选择合适语句 key 实际使用索引NULL则没使用索引少情况MYSQL选择优化足索引种情况SELECT语句 使用USE INDEX（indexname）强制使用索引或者用IGNORE INDEX（indexname）强制MYSQL忽略索引 key_len 使用索引度损失精确性情况度越短越 ref 显示索引哪列使用能数 rows MYSQL认必须检查用返请求数据行数 Extra 关于MYSQL何解析查询额外信息表四.三讨论看坏例Using temporaryUsing filesort意思MYSQL根本能使用索引结检索慢 extra列返描述意义 Distinct 旦MYSQL找与行相联合匹配行再搜索 Not exists MYSQL优化LEFT JOIN旦找匹配LEFT JOIN标准行再搜索 Range checked for each Record（index map:#） 没找理想索引于前面表每行组合MYSQL检查使用哪索引并用表返行使用索引慢连接 Using filesort 看候查询需要优化MYSQL需要进行额外步骤发现何返行排序根据连接类型及存储排序键值匹配条件全部行行指针排序全部行 Using index 列数据仅仅使用索引信息没读取实际行表返发表全部请求列都同索引部候 Using temporary 看候查询需要优化MYSQL需要创建临表存储结通发同列集进行ORDER BYGROUP BY Where used 使用WHERE句*哪些行与张表匹配或者返给用户想返表全部行并且连接类型ALL或index发或者查询问题同连接类型解释（按照效率高低顺序排序） const 表记录值能够匹配查询（索引主键或惟索引）行值实际数MYSQL先读值做数待 eq_ref 连接MYSQL查询前面表每记录联合都表读取记录查询使用索引主键或惟键全部使用 ref 连接类型查询使用惟或主键键或者些类型部（比利用左边前缀）发于前表每行联合全部记录都表读类型严重依赖于根据索引匹配记录少—越少越 range 连接类型使用索引返范围行比使用>或<查找东西发情况 index 连接类型前面表每记录联合进行完全扫描（比ALL更索引般于表数据） ALL 连接类型于前面每记录联合进行完全扫描般比较糟糕应该尽量避