1.关于inner join，left join，right join和子查询

1）能用inner join就不要用left join或者right join ，inner join是等值连接。 left join，right join 是外连接，返回的行数比较少。
2）外连接性能比子查询快，尽量用外连接来代替子查询。
3）使用join的时候，应该用小的结果驱动大的结果（left join左边表尽量小，如果有条件应该放到左边先处理，right join同理反向）
优化前
  Select
       *
  from table_a    a
  left join table_b   b
      on a.id=b.id
  Left join table_c  c
  On  a.id = c.id
  where a.id>100
  and b.id<200
优化后
Select * from
(Select * from table_a where id >100) a
left join
(Select * from table_b where id<200) b
on a.id=b.id
Left join table_c  
On a.id =c.id

2.建立索引，加快查询

1）如果我们创建了(col1,col2,col3)的复合索引，那么其实相当于创建了(col1,col2,col3),(col1,col2),(col1）三个索引，这样称为最佳左前缀特性。

     因此我们在创建复合索引的应该将最常用作限制条件的列放在最左边，依次递减。

   使用:
    创建索引
    create index idx1 on table1(col1,col2,col3)
    查询
    select * from table1 where col1= A and col2= B and col3 = C
    这时候查询优化器,不在扫描表了,而是直接的从索引中拿数据,因为索引中有这些数据,这叫覆盖式查询,这样的查询速度非常快;

   注意事项:
    对于复合索引,在查询使用时,最好将条件顺序按找索引的顺序,这样效率最高;
    select * from table1 where col1=A AND col2=B AND col3=D
    如果使用 where col2=B AND col1=A 或者 where col2=B 将不会使用索引


2）select * from A name like ‘xxx%’ 这个sql会使用name索引，但是 select * from A name like ‘%XXX’不会走索引，

     因为‘%’代表任何字符，%xxx不知道怎么去索引的，所以使用不了索引。

3）索引不会包含有null值得列。只要列中包含有NULL值都将不会被包含在索引中（除非是唯一值的域，可以存在一个NULL），

     复合索引中只要有一列含有NULL值，那么这一列对于此复合索引是无效的。所以我们在数据库设计时不要让字段的默认值为NULL.
4）排序的索引问题   Mysql查询只是用一个索引，因此如果where字句中已经用了索引的话，那么order by中的列是不会使用索引的。

      因此数据库默认排序可以符合要求情况下不要使用排序操作，尽量不要包含多个列的排序，如果需要最好给这些列创建符合索引。

5）不要在列上进行运算

     如： select * from users where YEAR(adddate)<2007;将在每个行进行运算，将导致索引失效进行全盘扫描，因此我们可以改为

     select * from users where adddate<‘2007-01-01’;

6）尽量不要使用not in和<>操作

     Not in和<>操作都不会使用索引，而是将会进行全表扫描。

3.limit千万级分页时的优化
   

  1）在我们平时使用limit 如
       select * from A order by id limit 1,10;
       这样在表数据很少的时候，看不出什么性能问题，倘若达到千万级，如
       select * from A order by ID limit 10000000，10；
       虽然都是只查询10记录，但是这个性能让人受不了，
 2）可以这么优化，如
      select * from A where id>=(select id from a limit 10000000,1)limit 10;
     其实还可以这么写
     Select * from A where id between 10000000 and 10000010

4.  尽量避免select * 语句

5.如果只需要取1行，可以用limit 1取得1行

6.尽量用union all或者union 来代替or，尽量使用union all而不是union

7.使用批量插入节省交互

insert into system_user(username,passwd) values('test1','123456')
insert into system_user(username,passwd) values('test2','123456')
insert into system_user(username,passwd) values('test3','123456')
优化后：
insert into system_user(username,passwd) values('test1','123456'),('test2','123456'),('test3','123456')

 

待完善