SQL语句优化

最新推荐文章于 2024-03-04 10:45:54 发布

命运的左岸

最新推荐文章于 2024-03-04 10:45:54 发布

阅读量222

点赞数

分类专栏： MySQL 文章标签： SQL语句

本文链接：https://blog.csdn.net/mingyundezuoan/article/details/79058481

版权

MySQL 专栏收录该内容

37 篇文章 0 订阅

订阅专栏

SQL语句优化

建表

主键无符号自增，不使用字符串UUID做主键，因为辅助索引中存放的是主建索引的值，通过主键索引间接查询数据，主键索引过长，会增加辅助索引文件的大小
数据类型：行数据 65535 字节，在分配 varchar 字段类型的数据的长度时需要注意，不要超过最大上限

查询

union 与 union all ，前者合并两个查询结果并去重，后者只是做合并，优先使用后者，减轻服务器压力
like ，按照 InnoDB 存储引擎，B+Tree 数据结构存储，复合最左匹配原则，like “1%” 而不能是 like “%1”
时间匹配，匹配同一天的的数据记录 like “2018-01-14%” ，不在SQL中使用 date函数处理字符串

示例

EXPLAIN select COALESCE(sum(money),0.00)
from td_project_invest_record r, project p 
where r.C_PROJECT_ID=p.ID and p.C_STATUS='xx' and r.C_USER="xxxxx";

explain执行计划

COALESCE()：COALESCE函数会依次检查输入的参数，返回第一个不是NULL的参数，只有当传入 COALESCE函数的所有的参数都是NULL的时候，函数才会返回NULL；如：COALESCE(a,”“) 如果 a 不为空，则返回 a ; 如果 a 为空，则返回 “” ；作用与 IFNULL(a,0) 相似；使用场景：MyBatis 查询数量，若使用 int 接收返回值，当查询结果为空的时候会出现尝试为int赋值null 的异常，可通过此函数进行判断并给出默认值
索引长度：使用到的两个索引的字段均是varchar(32)，索引长度一个为 99 ，一个为 98 ；索引长度计算：若为varchar数据类型，n * （编码格式为UTF-8，则乘以3；GBK，则乘以2）+2 ；原因：project_invest_record 中 C_USER 为空，则再+1
索引基数：Explain 的运行结果中，possible_key 与 key 内容不同，一个SQL语句中只会选择一个最优的索引，C_PROJECT_ID 的索引基数大于C_USER，SHOW INDEX FROM project_invest_record ; 通过索引基数确认多个索引中使用的哪个索引；索引基数并不是选择索引的唯一约束条件
此处为何使用C_USER的索引？通过 C_USER 可过滤一部分数据，关联条件区分度不大
explain 执行计划

SELECT * FROM `agent_user`
LIMIT 1,1;
-- 查询数据时未检索到结果；SQL如上；本地SQL查询时未加LIMIT条件查询有结果；
-- 因为数据结果仅有一条，limit 1,1  实际查询的是第二条数据结果，所以无值 
-- LIMIT 数据偏移量，每页的数据量

SELECT COUNT(0)
FROM td_project
WHERE 1 = 1
    AND C_STATUS IN ('301')
ORDER BY C_STATUS + 0 ASC, CASE 
    WHEN C_STATUS = 110 THEN C_LOAN_BEGIN_DATE
END ASC, CASE 
    WHEN C_STATUS = 201 THEN C_CREATE_TIME
END DESC, CASE 
    WHEN C_STATUS = 209 THEN C_LOAN_ACTUAL_END_DATE
END DESC, CASE 
    WHEN C_STATUS = 301 THEN C_TRANSFER_DATE
END DESC ;

整体未使用到索引
但

explain SELECT COUNT(0)
FROM p2p_td_project
WHERE 1 = 1
    AND C_STATUS IN ('301');

使用到了组合索引，最左匹配，为何索引使用到 C_STATUS ?
C_STATUS 为组合索引的右侧，理论上使用不到索引，但此处有用到
key_len 为组合索引的长度 51 ，是组合索引的索引长度
原理：覆盖索引：
https://www.cnblogs.com/chenpingzhao/p/4776981.html
http://blog.csdn.net/garfielder007/article/details/54295577
Explain 使用解读
http://blog.csdn.net/xifeijian/article/details/19773795