ppst 视频—— mysql 的语句的优化方法介绍

ppst 视频—— mysql 的语句的优化方法介绍 ：具体视频请点击 mysql的优化视频, 请关注ppst 技术微博视频分享平台

1.explain 指令的使用介绍

EXPLAIN  select  * from post_table where post_id='1385618847672105010101'; 

EXPLAIN列的解释：

table	显示这一行的数据是关于哪张表的
type	这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL
possible_keys	显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
key	实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引
key_len	使用的索引的长度。在不损失精确性的情况下，长度越短越好
ref	显示索引的哪一列被使用了，如果可能的话，是一个常数
rows	MYSQL认为必须检查的用来返回请求数据的行数
Extra	关于MYSQL如何解析查询的额外信息。将在表4.3中讨论，但这里可以看到的坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢

 

从上面的介绍可以看出 

sql的使用了主键post_id 他的type是最快的const,只扫描了rows 一条记录,

唯一一点 不好就是 key_len 有点长，

explain 是基本查看一条sql 语句是否合适分方法

2.mysqk profile 的使用

MySQL Query Profiler, 可以查询到此 SQL 语句会执行多少, 并看出 CPU/Memory 使用

量, 执行过程 System lock, Table lock 花多少时间等等. 
mysql> show variables like 'profiling%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| profiling              | OFF   | 
| profiling_history_size | 15    | 
+------------------------+-------+
开启此功能
mysql>set profiling=1; 
mysql> show variables like 'profiling%';
# 此命令会让mysql在 information_schema 的 database 建立一個 PROFILING 的 

table 来记录.
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| profiling              | ON    | 
| profiling_history_size | 15    | 
+------------------------+-------+
profiling_history_size记录多少次查询

mysql> show profiles;
+----------+------------+------------------------------------+
| Query_ID | Duration   | Query                              |
+----------+------------+------------------------------------+
|        1 | 0.00018100 | show variables like 'profiling%'   | 
|        2 | 0.00020400 | show variables like 'profiling%'   | 
|        3 | 0.00007800 | set profiling=1                    | 
|        4 | 0.00011000 | show variables like 'profiling%'   | 
|        5 | 0.00002400 | select count(1) from `mrhao_stats` | 
|        6 | 1.52181400 | select count(*) from `mrhao_stats` | 
|        7 | 0.00026900 | show variables like 'profiling%'   | 

mysql> show profile for query 6;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| (initialization)               | 0.000003 | 
| checking query cache for query | 0.000042 | 
| Opening tables                 | 0.00001 | 
| System lock                    | 0.000004 | 
| Table lock                     | 0.000025 | 
| init                           | 0.000009 | 
| optimizing                     | 0.000003 | 
| statistics                     | 0.000007 | 
| preparing                      | 0.000007 | 
| executing                      | 0.000004 | 
| Sending data                   | 1.521676 | 
| end                            | 0.000007 | 
| query end                      | 0.000003 | 
| storing result in query cache | 0.000002 | 
| freeing items                  | 0.000006 | 
| closing tables                 | 0.000004 | 
| logging slow query             | 0.000002 | 
+--------------------------------+----------+
17 rows in set (0.00 sec)

mysql> show profile cpu for query 6;
+--------------------------------+----------+----------+------------+
| Status                         | Duration | CPU_user | CPU_system |
+--------------------------------+----------+----------+------------+
| (initialization)               | 0.000003 | 0        | 0          | 
| checking query cache for query | 0.000042 | 0.001    | 0          | 
| Opening tables                 | 0.00001 | 0        | 0          | 
| System lock                    | 0.000004 | 0        | 0          | 
| Table lock                     | 0.000025 | 0        | 0          | 
| init                           | 0.000009 | 0        | 0          | 
| optimizing                     | 0.000003 | 0        | 0          | 
| statistics                     | 0.000007 | 0        | 0          | 
| preparing                      | 0.000007 | 0        | 0          | 
| executing                      | 0.000004 | 0        | 0          | 
| Sending data                   | 1.521676 | 1.631752 | 0.036995   | 
| end                            | 0.000007 | 0        | 0          | 
| query end                      | 0.000003 | 0        | 0          | 
| storing result in query cache | 0.000002 | 0        | 0          | 
| freeing items                  | 0.000006 | 0        | 0          | 
| closing tables                 | 0.000004 | 0        | 0          | 
| logging slow query             | 0.000002 | 0        | 0          | 
+--------------------------------+----------+----------+------------+

1、使用索引来更快地遍历表。 

　　缺省情况下建立的索引是非群集索引，但有时它并不是最佳的。在非群集索引下，数据在物理上随机存放在数据页上。合理的索引设计要建立在对各种查询的分析和预测上。一般来说： 
　　a.有大量重复值、且经常有范围查询（ > ,< ，> =,< =）和order by、group by发生的列，可考虑建立群集索引； 
　　b.经常同时存取多列，且每列都含有重复值可考虑建立组合索引； 
　　c.组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。索引虽有助于提高性能但不是索引越多越好，恰好相反过多的索引会导致 系统 低效。用户在表中每加进一个索引，维护索引集合就要做相应的更新工作。 

　　2、在海量查询时尽量少用格式转换。 
　　3、ORDER BY和GROPU BY使用ORDER BY和GROUP BY短语，任何一种索引都有助于SELECT的性能提高。 

　　4、任何对列的操作都将导致表扫描，它包括 数据库 函数 、计算表达式等等，查询时要尽可能将操作移至等号右边。 

　　5、IN、OR子句常会使用工作表，使索引失效。如果不产生大量重复值，可以考虑把子句拆开。拆开的子句中应该包含索引。 
　　6、只要能满足你的需求，应尽可能使用更小的数据类型：例如使用MEDIUMINT代替INT 

　　7、尽量把所有的列设置为NOT NULL，如果你要保存NULL，手动去设置它，而不是把它设为默认值。 
　　8、尽量少用VARCHAR、TEXT、BLOB类型 
　　9、如果你的数据只有你所知的少量的几个。最好使用ENUM类型 
　　10、正如graymice所讲的那样，建立索引。