关联查询时，关联字段加索引可以提高查询的速度，同时on后面只加关联条件，限制条件放到where中。
如： select a.ID,a.name,a.count,b.number from a left join b on a.id = b.a_id on b.number =11 ;
select a.ID,a.name,a.count,b.number from a left join b on a.id = b.a_id where b.number =11;
比较俩个sql，会发现第一个可能会查到数据比第二个多。
混合关联查询(必须是俩个表中都有数据，才能查到数据)：
SELECT a.ID,a.name,a.count,b.number from a， b WHERE a.id = b.a_id AND b.number =11 ;
查看mysql数据库中数据库下某个表的大小(切到 information_schema下执行)：查询test库的goods表的大小
select COUNT(ROUND(SUM(DATA_LENGTH/1024/1024/1024),2),'GB') AS DATA FROM TABLES
WHERE TABLE_SCHEMA='TEST' AND TABLE_NAME = 'GOODS';
查看表的大小排名前20的表数据：
select TABLE_NAME, TABLE_ROWS, AVG_ROW_LENGTH ,COUNT(ROUND(DATA_LENGTH/1024/1024/1024,2),'GB') AS DATA FROM TABLES WHERE TABLE_SCHEMA='TEST' ;
索引的误区：
-
新建表时不需要建索引,后续才添加索引。续创建索引的成本也相对高很多。实际项目中，在百万级的数据中添加索引需要半个小时，或者更长时间（坑的要死）。
-
where条件后的字段均建索引。（过多的索引,也会导致索引文件剧增,也还达不到期望中的效果。）
-
简单SQL不需要索引,联合查询才需要索引。(单表查询where中有索引字段则会使用索引)
-
联合索引的顺序是where条件后字段的先后顺序。（联合索引的顺序,是根据最左前缀原则,以及区分度来区分的，和where条件后字段的先后顺序无关。）
-
对于区分度小的字段上也新建索引,如状态,性别等字段等。（在区分度较小的字段上新建索引,基本无效,还会增加大量的索引文件。）
-
区分度：字段去重后的总数与全表总记录数的商。如性别字段只有 男 ，女 ，未知 三个值，说明区分度小，不适合建索引。
2.如何建索引
1. 单列索引
可以查看该字段的区分度,根据区分度的大小,也能大概知道在该字段上的新建索引是否有效，以及效果如何。区分度越大,索引效果越明显。
2.多列索引(联合索引)
多列索引中其实还有一个字段的先后顺序问题,一般是将区分度较高的放在前面,这样联合索引才更有效,例如:
select * from goods where sku='111111' and pool_id = '10'
建立联合索引：alter table goods add index idx_sku_pool(sku,pool_id ); -- sku的区分度更高
使用联合索引最好包含第一个字段，不然不走索引。
总是使用索引的第一个列：
如果索引是建立在多个列上，只有在它的第一个列(leading column)被where子句引用时，优化器才会选择使用该索引。这也是一条简单而重要的规则，当仅引用索引的第二个列时，优化器使用了全表扫描而忽略 了索引。
(二) 最左前缀匹配原则
MySQL会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如:
select * from goods where sku='111111' and pool_id < '10'
范围查询走全表扫描。
一般 in比or高效，EXISTS比in高效。
(三) 函数运算
不要在索引列上,进行函数运算,否则索引会失效。因为b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。在实际项目中的时候，做报表需要查询月初到月末的数据，一开始我们采用的是：DATE_FORMAT(o.create_date, "%Y-%m")=#{date}，查询很慢因为是全表扫描，函数的运算在“=”的左边，后面进行优化之后采用：o.create_date between #{startDate} and #{endDate}，速度明显快起来。
避免在索引列上使用计算。WHERE子句中，如果索引列是函数的一部分。优化器将不使用索引而使用全表扫描。
低效： SELECT … FROM DEPT WHERE SAL * 12 > 25000;
高效: SELECT … FROM DEPT WHERE SAL > 25000/12;
用>=替代>：
高效：SELECT * FROM EMP WHERE DEPTNO >=4
低效: SELECT * FROM EMP WHERE DEPTNO >3
避免在索引列上使用NOT通常，我们要避免在索引列上使用NOT, NOT会产生在和在索引列上使用函数相同的影响。当Oracle“遇到”NOT,他就会停止使用索引转而执行全表扫描。（索引列的is null is not null 走全表扫描）
避免在索引列上使用计算。WHERE子句中，如果索引列是函数的一部分。优化器将不使用索引而使用全表扫描。
低效： SELECT … FROM DEPT WHERE SAL * 12 > 25000;
高效: SELECT … FROM DEPT WHERE SAL > 25000/12;
避免改变索引列的类型:
假设索引列为字符型，SELECT … FROM EMP WHERE EMP_TYPE = 123 不会走索引，需要改为：
SELECT … FROM EMP WHERE EMP_TYPE = '123'
Oracle中最高效的删除重复记录方法 ( 因为使用了ROWID)例子：
DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);
用EXISTS替代IN、用NOT EXISTS替代NOT IN：
在许多基于基础表的查询中，为了满足一个条件,往往需要对另一个表进行联接。在这种情况下，使用EXISTS(或NOT EXISTS)通常将提高查询的效率。在子查询中，NOT IN子句将执行一个内部的排序和合并。无论在哪种情况下，NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历)。为了避免使用NOT IN ，我们可以把它改写成外连接(Outer Joins)或NOT EXISTS。
例子：
（高效）SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND EXISTS (SELECT ‘X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB')
用EXISTS替换DISTINCT：
当提交一个包含一对多表信息(比如部门表和雇员表)的查询时，避免在SELECT子句中使用DISTINCT。一般可以考虑用EXIST替换， EXISTS 使查询更为迅速，因为RDBMS核心模块将在子查询的条件一旦满足后，立刻返回结果。例子：
(低效): SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E WHERE D.DEPT_NO = E.DEPT_NO (高效): SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT ‘X' FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);(低效)SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB')
优化GROUP BY：
提高GROUP BY 语句的效率，可以通过将不需要的记录在GROUP BY 之前过滤掉。下面两个查询返回相同结果但第二个明显就快了许多。
低效: SELECT JOB , AVG(SAL) FROM EMP GROUP JOB HAVING JOB = ‘PRESIDENT' OR JOB = ‘MANAGER' 高效: SELECT JOB , AVG(SAL) FROM EMP WHERE JOB = ‘PRESIDENT' OR JOB = ‘MANAGER' GROUP JOB
能在where条件过滤掉的，尽量不用having过滤。
带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能。DISTINCT需要一次排序操作，而其他的至少需要执行两次排序。
数据库设计方面的建议：
把字段定义为NOT NULL并且提供默认值
a）null的列使索引/索引统计/值比较都更加复杂，对MySQL来说更难优化
b）null 这种类型MySQL内部需要进行特殊处理，增加数据库处理记录的复杂性；同等条件下，表中有较多空字段的时候，数据库的处理性能会降低很多
c）null值需要更多的存储空间，无论是表还是索引中每行中的null的列都需要额外的空间来标识
d）对null 的处理时候，只能采用is null或is not null，而不能采用=、in、<、<>、!=、not in这些操作符号。如：where name!=’zhangsan’，如果存在name为null值的记录，查询结果就不会包含name为null值的记录。
- 务必请使用“同类型”进行比较，否则可能全表扫面（上面提到的，会先进行函数转换，导致不走索引。不对索引字段做计算，函数运算）
SELECT name FROM t_user WHERE phone=1888888888 会导致全表扫描.
- 禁止在WHERE条件的上使用函数或者计算
解读：SELECT naem FROM tuser WHERE date(createdatatime)='2017-12-29' 会导致全表扫描
推荐的写法是：SELECT name FROM tuser WHERE createdatatime>= '2017-12-29' and create_datatime < '2017-12-30'
- 禁止负向查询，以及%开头的模糊查询
a）负向查询条件：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等，会导致全表扫描
b）%开头的模糊查询，会导致全表扫描
- 不要大表使用JOIN查询，禁止大表使用子查询
会产生临时表，消耗较多内存与CPU，极大影响数据库性能
- OR改写为IN()或者UNION
原因很简单or不会走索引
扫一扫
3866
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
