SQL索引与join语句

SQL性能下降，执行时间长，等待时间长原因:
1.查询语句写的烂
2.关联查询(Join)太多：设计缺陷或者不得已的需求
3.索引失效(单值索引和复合索引)
4.服务器调优及各个参数设置(缓冲、线程数等)

比如有一张user表，有四个字段：id name age number.
索引就是将杂乱无序的表按照一定规律排列，排列之后的表查询起来要比没排序的表更快。
单值索引：

select * from user where name='';
create index idx_user_name on user(name)

复合索引

select * from user where name='' and age='';
create index idx_user_nameAge on user(name,age)

七种join
1.LEFT JOIN：SELECT (select list) FROM TABLE A A LEFT JOIN TABLE B ON A.KEY=B.KEY
SELECT (select list) FROM TABLE A A LEFT JOIN TABLE B ON A.KEY=B.KEY WHERE B.KEYIS NULL

2.RIGHT JOIN:SELECT (select list) FROM TABLE A A RIGHT JOIN TABLE B ON A.KEY=B.KEY
SELECT (select list) FROM TABLE A A RIGHT JOIN TABLE B ON A.KEY=B.KEY WHERE A.KEY IS NULL

3.INNER JOIN:SELECT (select list) FROM TABLE A A INNER JOIN TABLE B ON A.KEY=B.KEY
4.OUTER JOIN:SELECT (select list) FROM TABLE A A FULL OUTER JOIN TABLE B ON A.KEY=B.KEY
SELECT (select list) FROM TABLE A A FULL OUTER JOIN TABLE B ON A.KEY IS NULL OR B.KEY IS NULL