关于MySQL优化的详细教程

首先我们可以根据数据库表的级别把它分成两部分：

第一部分百万级数据库使用那些优化，

1.对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null

最好不要给数据库留NULL，尽可能的使用NOT NULL填充数据库.

备注、描述、评论之类的可以设置为NULL，其他的，最好不要使用NULL。

不要以为NULL不需要空间，比如：char(100)型，在字段建立时，空间就固定了，不管是否插入值(NULL也包含在内)，都是占用100个字符的空间的，如果是varchar这样的变长字段，null不占用空间。

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num = 0

3.应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。

4.应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num=10 or Name = 'admin'

可以这样查询：

select id from t where num = 10

union all

select id from t where Name = 'admin'

5.in 和 not in 也要慎用，否则会导致全表扫描，如：

select id from t where num in(1,2,3)

对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

很多时候用exists 代替 in是一个好的选择：

select num from a where num in(select num from b)

用下面的语句替换：

select num from a where exists(select 1 from b where num=a.num)

6.下面的查询也将导致全表扫描：

select id from t where name like ‘%abc%’

若要提高效率，可以考虑全文检索。

7.如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

select id from t where num = @num

可以改为强制查询使用索引：

select id from t with(index(索引名)) where num = @num

.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2 = 100

应改为:

select id from t where num = 100*2

9.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where substring(name,1,3) = ’abc’ -–name以abc开头的id

select id from t where datediff(day,createdate,’2005-11-30′) = 0 -–‘2005-11-30’ --生成的id

应改为:

select id from t where name like 'abc%'

select id from t where createdate >= '2005-11-30' and createdate < '2005-12-1'

10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

11.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

12.不要写一些没有意义的查询，如需要生成一个空表结构：

select col1,col2 into #t from t where 1=0

这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：

create table #t(…)

13.Update 语句，如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志。

14.对于多张大数据量(这里几百条就算大了)的表JOIN，要先分页再JOIN，否则逻辑读会很高，性能很差。

15.select count(*) from table;这样不带任何条件的count会引起全表扫描，并且没有任何业务意义，是一定要杜绝的。

16.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。

17.应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。

18.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连 接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

19.尽可能的使用 varchar/nvarchar 代替 char/nchar，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

20.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

21.尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限(只有主键索引)。

第二部分千万级数据怎么优化

1，单库表别太多，一般保持在200以下为宜

2，尽量避免SQL中出现运算，例如select a+5 from A，让DB功能单一化

3，表设计尽量小而精，能用5个字段就不要用6个(不绝对，取决于业务，该冗余时坚决不要手软)

4，SQL事务不能设计太大，比如一次性提交10W条insert，当然这个不仅仅是性能问题了，可能直接内存溢出了

一般来说insert事务的话，5K-1W来做批处理就可以了(字段不能太大)

5，设计表的时候尽量用”小数据类型”，比如尽量避免text，blob等这些大家伙，优先使用ENUM和SET(小而美，范围有限，百益无一害)

6，设计表字段能用数字类型就千万别用字符类型，比如存IP地址，用int，别用varchar(方法自己百度一下吧)

7，尽量避免null字段，定义时尽量使用 not null.原因是允许null时不方便查询优化，复合索引也会失效，而且如果列有索引时会额外占用空间： a int(10) NOT NULL DEFAULT 0

8，图片等大家伙不要存DB，用fastdfs等中间件或者直接使用七牛等云存储都可以搞，也不贵

A，大SQL尽量拆分，多核CPU每个CPU只能执行一个SQL，所以并发时，一堆小的可能效率更高一些，并且容易命中缓存，而且不容易长时间锁表(无论什么锁都是时间越短越好)，当然这个要结合实际情况分析了，一大堆小的万一增加IO负担呢。

B，事务尽可能的小，代码别偷懒，全加到一个transaction中，道理不多说了

C，存储过程，触发器之类的能避就全避免了吧，维护不方便，人员变动时，很多时候就忘了，时间一长全是定时炸弹

D，禁止select *，不用问为啥了，禁止就是禁止!需要啥就取啥是王道

E，update时，where语句尽量要走索引，不然会全表扫描，一般情况下，1G的数据至少10S(想想这可是update啊，锁住10S意味着啥)

F，or尽量不用，改为in(),当然in的范围太多也不行，尽量别超100

G，还是or，如果：select a from A where b=1 or c=1这种where里面不同字段进行or，这种尽量改为union。

select a from A where b=1

union

select a from A where c=1

H，避免 “% 前缀”模糊查询 。因为会导致索引失效，大数据量下是灾难

I，分页时：Select a from A limit 10000,10; 这种大偏移量下效率非常低。可以考虑如下几个方案：

select a from A WHERE id>=xxxx limit 11;(将上一页的最大值通过where id> 进行预处理，然后分页)

select a from A WHERE id >= ( select a from A limit 10000,1 ) limit 10;

select a from A inner join (select a from A limit 10000,10) using (id) ;

J，避免使用count(*)，不知道为什么mysql优化这么个东西有那么难么，但是实际上大数量下这个东西真心慢，1000W以上至少几秒，作为替代方案，考虑使用nosql例如redis，memcached存下来，但是要定时校对。还有一个办法，直接做一个表存下来，每次增加或者减少都在这个表做update增减

K，UNION ALL 而非 UNION ，看需要啦，一般不用去重的业务的话去重压力不小，能省则省

L，尽量不用 INSERT SELECT，数据量大有延迟，同步完了可能有错误