[刘阳Java]_MySQL数据优化总结_查询备忘录

数据库优化是在后端开发中必备技能，今天写一篇MySQL数据优化的总结，供大家看看

一、MySQL数据库优化分类

我们通过一个图片形式来看看数据优化一些策略问题

不难看出，优化有两条路可以选择：硬件与技术。如果是投入硬件方式来优化成本相当高，如果是通过技术方式来优化效果好

二、测试数据样例：大家可以参考mysql官方的sakina数据库

三、提高mysql千万级大数据SQL查询优化几条经验

1. 对查询进行优化，应尽量避免全表扫描，首先应考虑在where及order by 涉及的列上创建索引。因为：索引对查询的速度有着至关重要的影响

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

例如：select id from user where num is null 。可以将num是这个字段设置默认值0.确保表中没有null值，然后在进行查询。修正后的SQL如下：select id from user where num=0

为什么需要尽量去除null，而将这个字段设置默认值0，大家可以看看下面的分析

考虑如下情况，假设数据库中一个表有10^6条记录，DBMS的页面大小为4K，并存储100条记录。如果没有索引，查询将对整个表进行扫描，最坏的情况下，如果所有数据页都不在内存，需要读取10^4个页面，如果这10^4个页面在磁盘上随机分布，需要进行10^4次I/O，假设磁盘每次I/O时间为10ms(忽略数据传输时间)，则总共需要100s(但实际上要好很多很多)。如果对之建立B-Tree索引，则只需要进行log100(10^6)=3次页面读取，最坏情况下耗时30ms。这就是索引带来的效果，很多时候，当你的应用程序进行SQL查询速度很慢时，应该想想是否可以建索引

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

4. 应尽量避免在where子句中使用or来连接条件，否则导致放弃使用索引而进行全表扫描。可以使用 union 或者是 union all代替

例如： select id from user where num =10 or num =20 这个语句景导致引擎放弃num索引，而要全表扫描来进行处理的

解决方式：使用union 或者是 union all来代替

select id from user where num = 10;
union all
select id from user where num =20;

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

如果 in 对于连续的数组，可以使用between ...and.来代替

例如：select id from user where num in (1,2,3); 像这样连续的就可以使用between ...and...来代替了 select id from user where num between 1 and 3;

6. like使用需注意

下面这个查询也将导致全表查询：select id from user where name like '%三'；

如果想提高效率，可以考虑到全文检索。比如solr或是luncene。而solr和luncenc是Java中最常用的全文检索的技术

而下面这个查询却使用到了索引：select id from user where name like '张%'；

7. where子句参数使用时候需注意

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

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

例如：select id from user where num/2=100;   应修改为：  select id from user where num = 100*2;

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

例如：  select id from user substring(name,1,3) = 'abc' ，这句sql的含义其实就是，查询name以abc开头的用户id

应修改为：  select id from user where name like 'abc%';

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

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

比如：  select num from user where num in(select num from newTable);

可以使用下面语句代替：  select num from user a where exists(select num from newTable b where b.num = a.num );

12. 索引创建需注意

并非索引创建越多越好。索引固然可以提高相应的查询效率，但是同样会降低insert以及update的效率。因为在insert或是update的时候有可能会重建索引或是修改索引。所以索引怎样创建需要慎重考虑，视情况而定。一个表中所以数量最好不要超过6个。若太多，则需要考虑一些不常用的列上创建索引是否有必要

13. 并不是所有索引对查询都有效，sql是根据表中数据进行查询优化的，当索引lie(索引字段)有大量重复数据的时候，sql查询可能不会去利用索引。如一表中字段 sex、male、female 几乎各一半。那么即使在sex上创建了索引对查询效率也起不了多大作用