锐客网

  1. 首页 > 睿知 > it技术 > >

mysql怎么做查询优化 mysql查询优化器原理

IT技术查询

MySql中LongText类型大字段查询优化1.mysql在操作数据的时候，以page为单位
??不管是更新，插入，删除一行数据，都需要将那行数据所在的page读到内存中，然后在进行操作 ， 这样就存在一个命中率的问题，如果一个page中能够相对的存放足够多的行，那么命中率就会相对高一些，性能就会有提升
2.innodb的page大小默认为16kb
??innodb存储引擎表为索引组织表，树底层的叶子节点为一双向链表，因此每个页中至少应该有两行记录，这就决定了innodb在存储一行数据的时候不能够超过8k，但事实上应该更小，有一些InnoDB内部数据结构要存储以及预留操作空间，
3.blob，text大字段
??innodb只会存放前768字节在数据页中，而剩余的数据则会存储在溢出段中（发生溢出情况的时候适用），最大768字节的作用是便于创建前缀索引/prefix index ， 其余更多的内容存储在额外的page里，哪怕只是多了一个字节 。因此，所有列长度越短越好
4.扩展存储禁用了自适应哈希
??因为需要完整的比较列的整个长度，才能发现是不是正确的数据（哈希帮助InnoDB非常快速的找到“猜测的位置”，但是必须检查“猜测的位置”是不是正确） 。因为自适应哈希是完全的内存结构，并且直接指向Buffer Pool中访问“最”频繁的页面，但对于扩展存储空间却无法使用Adaptive Hash
变长大字段类型包括blob,text,varchar ， 其中varchar列值长度大于某数N时也会存溢出页，在latin1字符集下N值可以这样计算：innodb的块大小默认为16kb ， 由于innodb存储引擎表为索引组织表，树底层的叶子节点为一双向链表 ， 因此每个页中至少应该有两行记录，这就决定了innodb在存储一行数据的时候不能够超过8k，减去其它列值所占字节数，约等于N 。对于InnoDB ， 内存是极为珍贵的，如果把768字节长度的blob都放在数据页，虽然可以节省部分IO，但是能缓存行数就变少，也就是能缓存的索引值变少了，降低了索引效率
Mysql把每个BLOB和TEXT值当作一个独立的对象处理 。存储引擎在存储时通常会做特殊处理 。当BLOB和TEXT值太大时，InnoDB会使用专门的“外部”储存区域来进行存储，此时每个值在行内需要1~4个字节存储一个指针 ， 然后在内部存储区域存储实际的值 。
Mysql不能将BLOB和TEXT列全部长度的字符串进行索引
mysql的 io 以page为单位，因此不必要的数据（大字段）也会随着需要操作的数据一同被读取到内存中来，这样带来的问题由于大字段会占用较大的内存（相比其他小字段），使得内存利用率较差，造成更多的随机读取 。从上面的分析来看，我们已经看到性能的瓶颈在于由于大字段存放在数据页中，造成了内存利用较差，带来过多的随机读，那怎么来优化掉这个大字段的影响
5.6版本以后，新增选项 innodb_page_size 可以修改innodb的page默认大小，但并不推荐修改这个配置
5.6版本之后mysql新增索引FULLTEXT可用来增加大文本搜索速度
mysql查询优化器应该怎么使用在开始演示之前mysql怎么做查询优化，我们先介绍下两个概念 。
概念一，数据mysql怎么做查询优化的可选择性基数，也就是常说的cardinality值 。
查询优化器在生成各种执行计划之前，得先从统计信息中取得相关数据，这样才能估算每步操作所涉及到的记录数，而这个相关数据就是cardinality 。简单来说 ， 就是每个值在每个字段中的唯一值分布状态 。
比如表t1有100行记录，其中一列为f1 。f1中唯一值的个数可以是100个，也可以是1个 ， 当然也可以是1到100之间的任何一个数字 。这里唯一值越的多少 ， 就是这个列的可选择基数 。
那看到这里我们就明白mysql怎么做查询优化了 ， 为什么要在基数高的字段上建立索引，而基数低的的字段建立索引反而没有全表扫描来的快 。当然这个只是一方面 ， 至于更深入的探讨就不在我这篇探讨的范围mysql怎么做查询优化了 。
概念二，关于HINT的使用 。
这里我来说下HINT是什么，在什么时候用 。
HINT简单来说就是在某些特定的场景下人工协助MySQL优化器的工作，使她生成最优的执行计划 。一般来说，优化器的执行计划都是最优化的，不过在某些特定场景下，执行计划可能不是最优化 。
比如：表t1经过大量的频繁更新操作，（UPDATE,DELETE,INSERT），cardinality已经很不准确了，这时候刚好执行了一条SQL，那么有可能这条SQL的执行计划就不是最优的 。为什么说有可能呢mysql怎么做查询优化？
来看下具体演示
譬如，以下两条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 =100and rank2 =100and rank3 =100;
表t1实际上在rank1,rank2,rank3三列上分别有一个二级索引 。
那我们来看SQL C的查询计划 。
显然 ， 没有用到任何索引，扫描的行数为32034，cost为3243.65 。
mysql explainformat=json select * from t1where 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_produced_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 explainformat=json select /*index_merge(t1) */ * from t1where 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_produced_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的计划：
【mysql怎么做查询优化 mysql查询优化器原理】不加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_produced_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_produced_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 。
mysql怎么优化，都要怎么做mysql优化是一个大方向，大的是要分布式、读写分离，小的是对sql语句进行优化 。不过大多问的也是对sql语句优化，网上很多资料 ， 我就大体说说 。
1、explain 索引 。
在你要查询的语句前加explain，看下有没有用到索引，如果出现type为all的，则说明有必要添加下索引 。（附多表查询速度比较：表关联existsin）慢查询优化是一大块 。
2、预统计 。
很经常需要对历史的数据进行过滤统计 。比如移动需要统计上个月电话小时数超过N小时的人 ， 那么如果直接取原始数据，那将很慢，此时如果每天晚上凌晨都对数据进行预统计，统计每个人每天电话时数，那再来过滤就很快 。
3、分表分区 。
分表分区也是为了提高搜索速度 。例如 ， 公交车的gps行驶记录，gps每隔15s报一次 ， 一辆车一天运行12小时，一天就要插入4*60*12条记录 ， N辆车就要再乘，其数量极大 ， 所以经常按月分表，分表里再按上报时间做日分区 ， 这样就达到很大的优化，想查询某段时间 ， mysql很快就可以定位到 。
4、表结构 。
表结构很重要，经常需要多表关联查询一些字段，有时可以冗余下放到同一张表 。
mysql优化很有意思，多去查阅些资料，多去尝试，对你有好处的 。
MySQL大数据量分页查询方法及其优化使用子查询优化大数据量分页查询
这种方式的做法是先定位偏移位置的id，然后再往后查询，适用于id递增的情况 。
使用id限定优化大数据量分页查询
使用这种方式需要先假设数据表的id是连续递增的，我们根据查询的页数和查询的记录数可以算出查询的id的范围，可以使用 id between and 来查询：
当然了，也可以使用in的方式来进行查询，这种方式经常用在多表关联的情况下，使用其他表查询的id集合来进行查询：
但是使用这种in查询方式的时候要注意的是，某些MySQL版本并不支持在in子句中使用limit子句 。
参考sql优化之大数据量分页查询（mysql） - yanggb - 博客园 (cnblogs.com)
MySQL 查询优化（六）： MySQL 的查询优化排序优化机制对结果进行排序操作的代价可能很高，因此可以通过避免排序或让参与排序的数据行更少来优化查询性能 。
当 MySQL 不能使用索引产生有序结果时，它必须对数据行进行排序 。这有可能是在内存中进行也可能是在磁盘进行 ， 但 MySQL 始终将这个过程称之为 filesort，即便实际上并没有使用一个文件 。
如果用于排序的值可以一次性放入排序缓存中 ， MySQL 可以在内存中使用快排算法进行排序 。如果 MySQL 不能在内存中进行排序，则会在磁盘中按块逐块排序 。它对每个块使用快排算法 ， 然后在将这些排序好的块合并到结果中 。
有两个文件排序（filesort）算法：
很难说哪种算法更有效，对每个算法来说都会有最优和最坏案例 。MySQL 在数据表全部列加上用于排序的列的大小不超过 max_length_for_sort_data 时会使用单次遍历算法 。可以通过修改这个参数影响排序算法的选择 。
需要注意的是，MySQL 的 filesort使用的临时存储空间可能会超出你的预期 ， 这是因为它对每个排序元素都分配了固定大小的存储空间 。这些存储空间要足够大以便容下存储最大的元素，而且 VARCHAR这类字段使用的是对应的最大长度 。而且，如果使用的是 UTF-8字符集，MuSQL 会对每个字符分配3个字节 。结果是，我们会发现那些没怎么优化的查询会导致磁盘上的临时存储空间是数据表自身存储空间的好几倍 。
而在对联合查询进行排序时，MySQL 可能会在查询执行过程中执行两次文件排序 。如果 ORDER BY 子句只是引用联合查询的第一张表，MySQL 可以先对这个表进行文件排序，然后再处理联合查询 。如果是这种情况，在 EXPLAIN 时会在 Extra 字段显示“Using filesort” 。而对于其他的排序情况——例如排序不是针对第一张表，或者是 ORDER BY 使用的列对应了不止一个数据表，MySQL 必须使用临时表缓存查询结果 ， 然而在联合查询完成后 ， 再对临时表进行文件排序 。在这种情况下，EXPLAIN 会在 Extra 字段显示“Using temorary; Using filesort” 。如果包含 LIMIT 约束的话，会发生在文件排序后 ， 因此临时表和文件排序的存储空间可能非常大 。
MySQL 5.6在只需要对数据行的子集（例如 LIMIT）进行排序时，引入了一个重大改进 。相对于对整个结果集进行排序再返回部分数据，MySQL 有时候会在排序的时候直接丢弃掉不需要的数据行来提高效率 。不管怎么样，排序也需要小心使用 ， 很可能会导致存储占用的飙升最终导致系统负荷过大 。
【Mysql】查询优化——减少回表操作 ??聚集索引：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引 。叶子结点存储索引和行记录，聚簇索引查询会很快，因为可以直接定位到行记录 。
??非聚集索引：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同 ， 一个表中可以拥有多个非聚集索引 。叶子节点存储聚簇索引值（主键id），需要扫码两遍索引树，先通过普通索引定位到主键值id，再通过聚集索引定位到行记录 。
??回表查询可以理解为普通索引的查询，先定位主键值，再定位行记录 ， 它的性能较扫一遍索引树更低 。
??索引覆盖，即将查询sql中的字段添加到联合索引里面 ， 只要保证查询语句里面的字段都在索引文件中，就无需进行回表查询；
??实际开发中 ， 不可能把所有字段建立到联合索引，可根据实际业务场景，把经常需要查询的字段建立到联合索引中 。
?? 在Mysql5.6的版本上推出 ， 用于优化查询 。在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数 。
?? 优化超多分页场景 。查询条件放到子查询中，子查询只查主键id，然后使用子查询中确定的主键关联查询其他的属性字段 。
mysql怎么做查询优化的介绍就聊到这里吧，感谢你花时间阅读本站内容，更多关于mysql查询优化器原理、mysql怎么做查询优化的信息别忘了在本站进行查找喔 。

    推荐阅读


    上一篇：java+代码+svn，java svn

    下一篇：直播色卡调色技巧，直播调色系统