锐客网

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

mysql查询优化怎么做 mysql查询调优

IT技术查询

【Mysql】查询优化——减少回表操作 ??聚集索引mysql查询优化怎么做：数据行mysql查询优化怎么做的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引 。叶子结点存储索引和行记录，聚簇索引查询会很快 ， 因为可以直接定位到行记录 。
??非聚集索引mysql查询优化怎么做：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引 。叶子节点存储聚簇索引值（主键id），需要扫码两遍索引树，先通过普通索引定位到主键值id，再通过聚集索引定位到行记录 。
??回表查询可以理解为普通索引的查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低 。
??索引覆盖 ， 即将查询sql中的字段添加到联合索引里面，只要保证查询语句里面的字段都在索引文件中，就无需进行回表查询；
??实际开发中 ， 不可能把所有字段建立到联合索引，可根据实际业务场景，把经常需要查询的字段建立到联合索引中 。
?? 在Mysql5.6的版本上推出，用于优化查询 。在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数 。
?? 优化超多分页场景 。查询条件放到子查询中 ， 子查询只查主键id，然后使用子查询中确定的主键关联查询其mysql查询优化怎么做他的属性字段 。
mysql中怎样对大批量级的数据查询进行优化在我们使用MySQL数据库时mysql查询优化怎么做，比较常用也是查询，包括基本查询，关联查询，条件查询等等，对于同一个操作，SQL语句mysql查询优化怎么做的实现有很多种写法，但是不同mysql查询优化怎么做的写法查询的性能可能会有很大的差异 。这里主要介绍下select查询优化的要点 。
1. 使用慢查询日志去发现慢查询 。
2. 使用执行计划去判断查询是否正常运行 。
3. 总是去测试你的查询看看是否mysql查询优化怎么做他们运行在最佳状态下 –久而久之性能总会变化 。
4. 避免在整个表上使用count(*),它可能锁住整张表 。
5. 使查询保持一致以便后续相似的查询可以使用查询缓存 。
6. 在适当的情形下使用GROUP BY而不是DISTINCT 。
7. 在WHERE, GROUP BY和ORDER BY子句中使用有索引的列 。
8. 保持索引简单,不在多个索引中包含同一个列 。
9. 有时候MySQL会使用错误的索引,对于这种情况使用USE INDEX 。
10. 检查使用SQL_MODE=STRICT的问题 。
11.对于记录数小于5的索引字段，在UNION的时候使用LIMIT不是是用OR.
12. 为了 避免在更新前SELECT，使用INSERT ON DUPLICATE KEY或者INSERT IGNORE ,不要用UPDATE去实现 。
3. 不要使用 MAX,使用索引字段和ORDER BY子句 。
14. 避免使用ORDER BY RAND().
15. LIMIT M，N实际上可以减缓查询在某些情况下 ， 有节制地使用 。
16. 在WHERE子句中使用UNION代替子查询 。
17. 对于UPDATES（更新），使用 SHARE MODE（共享模式），以防止独占锁 。
18. 在重新启动的MySQL ， 记得来温暖你的数据库，以确保您的数据在内存和查询速度快 。
19. 使用DROP TABLE，CREATE TABLE DELETE FROM从表中删除所有数据 。
20. 最小化的数据在查询你需要的数据，使用*消耗大量的时间 。
21. 考虑持久连接，而不是多个连接 ， 以减少开销 。
22. 基准查询，包括使用服务器上的负载，有时一个简单的查询可以影响其他查询 。
23. 当负载增加您的服务器上，使用SHOW PROCESSLIST查看慢的和有问题的查询 。
24. 在开发环境中产生的镜像数据中 测试的所有可疑的查询 。
来源：PHP程序员雷雪松的博客
mysql怎么优化，都要怎么做mysql优化是一个大方向，大的是要分布式、读写分离 ， 小的是对sql语句进行优化 。不过大多问的也是对sql语句优化 ， 网上很多资料，mysql查询优化怎么做我就大体说说 。
1、explain 索引 。
在你要查询的语句前加explain ， 看下有没有用到索引，如果出现type为all的，则说明有必要添加下索引 。（附多表查询速度比较mysql查询优化怎么做：表关联existsin）慢查询优化是一大块 。
2、预统计 。
很经常需要对历史的数据进行过滤统计 。比如移动需要统计上个月电话小时数超过N小时的人，那么如果直接取原始数据，那将很慢，此时如果每天晚上凌晨都对数据进行预统计，统计每个人每天电话时数，那再来过滤就很快 。
3、分表分区 。
分表分区也是为了提高搜索速度 。例如，公交车的gps行驶记录 ， gps每隔15s报一次，一辆车一天运行12小时，一天就要插入4*60*12条记录，N辆车就要再乘，其数量极大，所以经常按月分表，分表里再按上报时间做日分区，这样就达到很大的优化 ， 想查询某段时间，mysql很快就可以定位到 。
4、表结构 。
表结构很重要，经常需要多表关联查询一些字段，有时可以冗余下放到同一张表 。
mysql优化很有意思，多去查阅些资料 ， 多去尝试，对你有好处的 。
MySQL大数据量分页查询方法及其优化使用子查询优化大数据量分页查询
这种方式的做法是先定位偏移位置的idmysql查询优化怎么做，然后再往后查询，适用于id递增的情况 。
使用id限定优化大数据量分页查询
使用这种方式需要先假设数据表的id是连续递增的，我们根据查询的页数和查询的记录数可以算出查询的id的范围，可以使用 id between and 来查询mysql查询优化怎么做：
当然了，也可以使用in的方式来进行查询，这种方式经常用在多表关联的情况下，使用其他表查询的id集合来进行查询：
但是使用这种in查询方式的时候要注意的是，某些MySQL版本并不支持在in子句中使用limit子句 。
参考sql优化之大数据量分页查询（mysql） - yanggb - 博客园 (cnblogs.com)
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查询优化器应该怎么使用在开始演示之前，我们先介绍下两个概念 。
概念一，数据的可选择性基数 ， 也就是常说的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 =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的计划：
不加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查询调优】mysql查询优化怎么做的介绍就聊到这里吧，感谢你花时间阅读本站内容，更多关于mysql查询调优、mysql查询优化怎么做的信息别忘了在本站进行查找喔 。

    推荐阅读


    上一篇：学习电商如何在快手直播，快手电商直播基地

    下一篇：如何防范肉鸡攻击服务器？ 肉鸡怎么攻击服务器的