mysql数据库千万级别数据查询优化

最近因工作需要，客户需要把一些表中数据全部导出，由于表中数据量巨大，开始的时候，查询特别慢，我想应该是进行全表扫描了，于是对SQL进行了调优，废话不多说，先来看看我是如何调优的吧，

1：首先我们需要选择正确的存储引擎，先来简单介绍下存储引擎吧。

MySQL有两个存储引擎MySAM和InnoDB, 每个引擎都有利有弊。MyISAM 适合于一些需要大量查询的应用，但其对于有大量写操作并不是很好。甚至你只是需要update一个字段，整个表都会被锁起来，而别的进程，就算是读进程都无法操作直到读操作完成。另外，MyISAM 对于SELECT COUNT(*) 这类的计算是超快无比的。

InnoDB 的趋势会是一个非常复杂的存储引擎，对于一些小的应用，它会比MyISAM 还慢。他是它支持“行锁” ，于是在写操作比较多的时候，会更优秀。并且，他还支持更多的高级应用，比如：事务。

Mysql在V5.1之前默认存储引擎是MyISAM；在此之后默认存储引擎是InnoDB，因为我需要查询表平时主要是写入的比较多，查询相对来说要少些，所以就默认的InnoDB。

查看当前mysql默认引擎:show variables like '%engine%';

2：给查询条件上的字段建立索引，但是索引字段不能太多，太多索引文件就会很大那样搜索只能变慢,查询速度和索引有很大关系也就是索引的大小直接影响你的查询效果。

Mysql中可以使用alter table语句来为表中的字段添加索引的基本语法是：

ALTER TABLE ADD INDEX ();

例：mysql>alter table test add index(t_name);

当然也可以使用可视化工具进行添加索引，当某个字段要经常用来做搜索，也为其建立索引

这是当时给表字段建立索引时的长度，大概花了25s左右的时间，然后花了3178s查出全部数据16243855条，(相关联的表所需的条件字段也都建立过索引了)，如果是第二次在查询，就会很快返回结果，这是因为使用了缓存

之前没有建立索引查询的时候，超过10000s还没有查询出来，应该是导致全表扫描了，相比建立索引后，查询效率确实提升了不少，但是索引并不是越多越好，一个表索引最好不要超过6个。索引固然可以提高select效率，但是也降低了insert效率和update效率，因为insert和update会使索引重建，所以怎么建索引需要慎重考虑。其次是内存问题，如果查询这种千万甚至上亿级别的数据时，内存会疯狂飙升，直到耗尽内存资源，内存溢出，然后你得电脑或者数据库服务器就蓝屏死机了，哈哈不要问我怎么知道，说多了都是泪；要避免这种情况，可以使用分页查询，在最后加上limit，

LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)

例：select * from table limit 10; //检索前10个记录行

select * from table limit 10,20; //检索11-30个记录行，简单理解就是去掉前面10行，从第11行开始往后面检索20行

3：对操作符的优化，我们尽量不采用不利用索引的操作符

如：in ，not in ，is nulMySQL 查询数据，is not null，等

4：用Not Exists 代替Not In

Not Exists允许用户使用相关子查询已排除一个表中能够与另一个表成功连接的所有记录。Not Exists用到了连接，能够发挥已经建好的索引的作用，而Not In不能使用索引。Not In是最慢的方式，要同每条记录比较，在数据量比较大的查询中不建议使用这种方式。

Select a.mobileidfrom Log_user awhere not exists(select b.mobileid from magazineitem b where b.mobileid=a.mobileid);

5：除了SQL上的优化外，我们还可以进行分库分表，但是目前我还没有做到分库分表，抱歉，哈哈，等我以后做了分库分表后在和大家分享

（编辑：晋中站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!