nginx 出现413 Request Entity Too Large问题的解决方法

nginx 出现413 Request Entity Too Large问题的解决方法

使用PHP上传图片(大小1.9M),出现 nginx: 413 Request Entity Too Large 错误。
根据经验是服务器限制了上传文件的大小,但php默认的文件上传是2M,应该不会出现问题。

打开php.ini,把 upload_max_filesize 和 post_max_size 修改为20M,然后重启。
再次上传,问题依旧,可以排除php方面的问题。

原来nginx默认上传文件的大小是1M,可nginx的设置中修改。

解决方法如下:
1.打开nginx配置文件 nginx.conf
2.在http{}段中加入 client_max_body_size 20m; 20m为允许最大上传的大小。
3.保存后重启nginx,问题解决。

mysql 严格模式 Strict Mode

mysql 严格模式 Strict Mode

找到MySQL安装目录下的my.cnf(windows系统则是my.ini)文件

在sql_mode中加入STRICT_TRANS_TABLES则表示开启严格模式,如没有加入则表示非严格模式,修改后重启mysql即可

例如这就表示开启了严格模式:
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

Strict Mode功能说明
不支持对not null字段插入null值
不支持对自增长字段插入”值
不支持text字段有默认值

mysql分页查询优化

mysql分页查询优化
当limit基数大的时候 查询速度会变得很慢 这个时候一般

mysql> select a.* from member as a inner join (select id from member where gender=1 limit 300000,1) as b on a.id=b.id;
+--------+------------+--------+
| id     | name       | gender |
+--------+------------+--------+
| 599465 | f48375bdb8 |      1 |
+--------+------------+--------+
1 row in set (0.08 sec)

mysql explain中key_len的作用

mysql explain中key_len的作用
key_len越小 索引效果越好

name的字段类型是varchar(20),字符编码是utf8,一个字符占用3个字节,那么key_len应该是 20*3=60。

mysql> explain select * from `member` where name='fdipzone';
+----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref   | rows | Extra                 |
+----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | member | ref  | name          | name | 63      | const |    1 | Using index condition |

explain的key_len为63,多出了3。

所以需要索引 设置字段长度尽量小 2 用定长char (key_len会少1) 3 用not null(key_len会少1)

如果用int key_len就是4 了 typeint就会更小1

mysql order by rand() 优化方法

mysql order by rand() 优化方法 适用于领取奖品等项目

mysql> select * from user order by rand() limit 1;
+-------+------------+----------------------------------+----------+--------------+-----------+
| id    | phone      | password                         | salt     | country_code | ip        |
+-------+------------+----------------------------------+----------+--------------+-----------+
| 15160 | 6549721306 | e4f302120c006880a247b652ad0e42f2 | 40343586 | 86           | 127.0.0.1 |
+-------+------------+----------------------------------+----------+--------------+-----------+
1 row in set (0.25 sec)
mysql> explain select * from user order by rand() limit 1;
+----+-------------+-------+------+---------------+------+---------+------+--------+---------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra                           |
+----+-------------+-------+------+---------------+------+---------+------+--------+---------------------------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 200303 | Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+--------+---------------------------------+
1 row in set (0.00 sec)

根据分析结果,运行需要0.25秒,order by rand() 需要使用临时表(Using temporary),需要使用文件排序(Using filesort),效率低下。

改进方法

<pre>
<?php
// 获取总记录数
$sqlstr = 'select count(*) as recount from user';
$query = mysql_query($sqlstr) or die(mysql_error());
$stat = mysql_fetch_assoc($query);
$total = $stat['recount'];

// 随机偏移
$offset = mt_rand(0, $total-1);

// 偏移后查询
$sqlstr = 'select * from user limit '.$offset.',1';
$query = mysql_query($sqlstr) or die(mysql_error());
$result = mysql_fetch_assoc($query);

print_r($result);
?>
mysql> select * from user limit 23541,1;
+-------+------------+----------------------------------+----------+--------------+-----------+
| id    | phone      | password                         | salt     | country_code | ip        |
+-------+------------+----------------------------------+----------+--------------+-----------+
| 23542 | 3740507464 | c8bc1890de179538d8a49cc211859a46 | 93863419 | 86           | 127.0.0.1 |
+-------+------------+----------------------------------+----------+--------------+-----------+
1 row in set (0.01 sec)

mysql> explain select * from user limit 23541,1;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 200303 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
1 row in set (0.00 sec)

mysql子查询用法

mysql子查询用法

1 可以当值来用

select id from hcyuyin_share where id=(select id from hcyuyin_share limit 200000,1)

2 可以当做表来用

select o.nickName,o.sex,o.province,o.city,from_unixtime(m.time,'%Y-%m-%d %H:%i:%s') as starttime,from_unixtime(z.time,'%Y-%m-%d %H:%i:%s') as endtime,ROUND((z.time-m.time)/60) as haoshifenzhong,from_unixtime(z1.time,'%Y-%m-%d') as choujiangtime from (select openid,time from xintjiashen_zhongjiang2   union select openid,time from xintjiashen_meizhong2) as z left join xintjiashen_my as m on z.openid=m.openid left join xintjiashen_zhongjiang1 as z1 on m.openid=z1.openid left join xintjiashen_oauthinfo as o on m.openid=o.wechatId where z1.prize_id !=8

PHP去重的简单写法

PHP去重的简单写法
用array_flip实现去重效果

<?php
$arr =array("a"=>"a1","b"=>'b1',"c"=>"a2","d"=>"a1");
$arr1 = array_flip($arr);
print_r($arr1);//先反转一次,去掉重复值,输出Array ( [a1] => d[b1] => b [a2] => c )  后者优先替代前面的
$arr2 = array_flip($arr1);
print_r($arr2);//再反转回来,得到去重后的数组,输出Array([d] => a1 [b] => b1 [c] => a2)


?>

此去重效果比array_unique要快