mysql 大数据量优化集合

-------------------当表记录超过30万的时候，以前写的代码报错：

“select * from gll_pro_ru WHERE 1 = 1 ORDER BY id DESC
select * from gll_pro_ru WHERE 1 = 1 ORDER BY id DESC limit 0,10000

Fatal error : Allowed memory size of 16777216 bytes exhausted (tried to allocate 39 bytes) in /var/www/web-sale/class/db.class.php on line 405 ”

405： while ($array_tmp[$i] = mysql_fetch_array($query,MYSQL_ASSOC))

 

 

解决方法：单个PHP运行程序最大可以用16M的内存，而实际需要的要大于它，而抱错。解决方法就是改php.ini的memory_limit的值，允许程序占于更大的内存,也可以在php代码里用ini_set("memory_limit","256M");设置.

想测试这个错也很简单，比如include一个大于2M甚至更大的文件。

 

 

 

 

 

 

 

 

 

 

－－－－－－－－－－－－－－－－－－－－－－－limit 对索引的影响

mysql> EXPLAIN select * from gll_pro_ru where sn_class =7 ORDER BY id DESC ;    +----+-------------+------------+------+----------------+------+---------+------+--------+-----------------------------+
| id | select_type | table      | type | possible_keys  | key   | key_len | ref  | rows   | Extra                       |
+----+-------------+------------+------+----------------+------+---------+------+--------+-----------------------------+
|  1 | SIMPLE      | gll_pro_ru | ALL  | class_ctime_no | NULL | NULL     | NULL | 269667 | Using where; Using filesort |
+----+-------------+------------+------+----------------+------+---------+------+--------+-----------------------------+
1 row in set (0.00 sec)

 

 

 

 

mysql> EXPLAIN select * from gll_pro_ru where sn_class =7 ORDER BY id DESC limit 0,10000;
+----+-------------+------------+-------+----------------+----------------+---------+------+--------+-----------------------------+
| id | select_type | table      | type  | possible_keys  | key             | key_len | ref  | rows   | Extra                       |
+----+-------------+------------+-------+----------------+----------------+---------+------+--------+-----------------------------+
|  1 | SIMPLE      | gll_pro_ru | range | class_ctime_no | class_ctime_no | 4        | NULL | 357645 | Using where; Using filesort |
+----+-------------+------------+-------+----------------+----------------+---------+------+--------+-----------------------------+
1 row in set (0.02 sec)