mysql系列1-基本sql优化

A， 独立的列: 索引列不能是查询中表达式的一部分,也不能是函数参数的一部分,必须是独立的列,否则不使用索引。Oracle可以使用函数索引解决，mysql没有函数索引，可以通过冗余设计+触发器解决

B,如果需要索引很长的字符列,会让索引变得大而且慢,可以考虑使用字符列的前一部分

C,覆盖索引：查询数据列时只需要查询索引就可得到，不需要再继续查询数据行。

    Select max(a) from t;在a上建立索引就能使用到覆盖索引

select a  from table_name where b 这样的一个查询，都知道索引应该加在b上面，查询的处理过程：

        首先去检索b索引找到与其对应的索引，

        然后根据索引区检索正确的数据行。

   alter table_name add index(b,a);

添加一个这样的索引就能实现了,查看是否使用了覆盖索引;Explain查看执行计划extra:use index如果出现了红色的字体部分，就表示使用了覆盖索引

D,一般在where,groupby,order by,on从句中出现的列建立索引，索引字段越小越好，离散度大的列放在联合索引的前面。

E,pt-dumplicate-key-checker检查重复及冗余索引pt-index-usage工具

F,使用int存储时间类型，使用函数FROM_UNIXTIME和UNIX_TIMESTAMP，使用bigint存储IP，使用函数INET_ATON()  INET_NTOA()转换

G,表的垂直拆分原则：把一个有很多列的表拆分成多个表，解决表的宽度问题，需要把不常用的字段放在一个表中，常用的放在一个表中，大的字段放在一个表中。

H,Mysql分区表

I,Mysql配置文件的常用可优化的参数：innodb_buffer_pool_size如果数据库只有innodb表的话建议配置成内存的75%。其它参数：innodb_buffer_pool_instances,innodb_log_buffer_size,

Innodb_flush_log_at_trx_commit,innodb_read_io_threads,innodb_write_io_threads,innodb_file_per_table,innodb_stats_on_metadata.

J,mysql的sql执行与复制只能使用到单核的CPU，另外mysql5.5使用的服务器的cup核数不要超过32核，否则会影响性能。

K, 在一个SQL语句中，如果对返回的记录集没有顺序要求时，应该去除引起排序的语句；

引起排序的操作有：order by，distinct，union，group by.因为排序会在内存中对符合条件的所有数据进行排序，如果符合条件的数据多会在磁盘上建立临时排序区进行排序。

L,只select需要的列，减少服务器IO和网络流量。From列表中去除不需要的表，避免join。

M,避免隐式转换造成不使用索引的情况。

 

N, 分组语法中，在select列表中出现的非统计字段，都要在group by 后出现

O, mysql中的in语句是把外表和内表作hash连接，而exists语句是对外表作loop循环，每次loop循环再对内表进行查询。一直大家都认为exists比in语句的效率要高，这种说法其实是不准确的。这个是要区分环境的。如果查询的两个表大小相当，那么用in和exists差别不大。

如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：

例如：表A（小表），表B（大表）

select * from A where cc in (select cc fromB) 效率低，用到了A表上cc列的索引；

select * from A where exists(select cc fromB where cc=A.cc) 效率高，用到了B表上cc列的索引。

相反的

select * from B where cc in (select cc fromA) 效率高，用到了B表上cc列的索引；

select * from B where exists(select cc fromA where cc=B.cc) 效率低，用到了A表上cc列的索引。

not in 和not exists如果查询语句使用了notin 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快.

P, 作比较的串中，如果‘%’或‘_’放于最前面，oracle将不用索引；

Q, 表字段越少，一个块(block)中能存储表的记录数就更多，查询肯定就更快了