1.PHP语言级的性能优化
1.1多用php自身能力
1.2php内置函数优化
1.3少用魔法函数
1.4合理使用内存
1.5避免在循环内做运算
1.6减少大规模计算
2.mysql优化
1)查看mysql是否开启慢查询日志
show variables like 'slow_query_log';
2)设置没有索引的记录到慢查询日志
set global log_queries_not_using_indexes=on;
3)查看超过多长时间的sql进行记录到慢查询日志
show variables like 'long_query_time'
4)开启慢查询日志
set global slow_query_log=on
1.MYSQL分析工具--mysqldumpslow
2.如何通过日志发现有问题的SQL
3.explain返回含义
4.group by优化
group by可能会出现临时表(Using temporary),文件排序(Using filesort)等,影响效率。
可以通过关联的子查询,来避免产生临时表和文件排序,可以节省io
改写前
select actor.first_name,actor.last_name,count(*)
from sakila.film_actor
5.如何建立合适的列索引
如何判断列的离散程度
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.17 MySQL Community Server (GPL)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| chat |
| dede_kxdata |
| dedecms |
| dedecmsv57utf8sp1 |
| ecshop |
| fikcdn |
| hotel |
| jpgraph |
| kxdata |
| myshop |
| mysql |
| newsreport |
| notebook |
| performance_schema |
| shop |
| test |
| testdb |
| votedb |
| #mysql50# |
+--------------------+
20 rows in set, 1 warning (0.34 sec)
mysql> use information_schema;
Database changed
mysql> select a.table_schema as 'database_name'
-> ,a.table_name as 'table_name'
-> ,a.index_name as 'index1',
-> b.index_name as 'index2',
-> a.column_name as 'repeat_col'
-> from statistics a join statistics b on
-> a.table_schema=b.table_schema and a.table_name=b.table_name
-> and a.seq_in_index=b.seq_in_index and a.column_name=b.column_name where
-> a.seq_in_index=1 AND a.index_name<>b.index_name;
+-------------------+-------------------+---------+---------+------------+
| database_name | table_name | index1 | index2 | repeat_col |
+-------------------+-------------------+---------+---------+------------+
| dedecms | dede_member_group | id | PRIMARY | id |
| dedecms | dede_member_group | PRIMARY | id | id |
| dedecmsv57utf8sp1 | dede_member_group | id | PRIMARY | id |
| dedecmsv57utf8sp1 | dede_member_group | PRIMARY | id | id |
| kxdata | dede_member_group | id | PRIMARY | id |
| kxdata | dede_member_group | PRIMARY | id | id |
+-------------------+-------------------+---------+---------+------------+
6 rows in set (2.41 sec)
6.选择合适的数据类型
7.反范式化