mysql优化 【5xx服务器内部】
避免页面错误
由于数据库连接timeout 连接超时
由于慢查询造成页面错误无法加载
由于数据无法提交
数据库的优化
硬件设备 贵 -》低
系统配置 CPU
数据库表结构优化
sql即索引的优化 低---》高
优化
优化硬件IO
系统配置的cpu
数据表结构
Sql语句及索引
查看mysql的版本
select @@version;
慢查日志
慢查询日志设置
当语句执行时间较长时，通过日志的方式进行记录，这种方式就是慢查询的日志。
1、临时开启慢查询日志（如果需要长时间开启，则需要更改mysql配置文件，第6点有介绍）
set global slow_query_log = on;
注：如果想关闭慢查询日志，只需要执行 set global slow_query_log = off; 即可
2、临时设置慢查询时间临界点 查询时间高于这个临界点的都会被记录到慢查询日志中（如果需要长时间开启，则需要更改mysql配置文件）。
set long_query_time = 1;
现在起，所有执行时间超过1秒的sql都将被记录到慢查询文件中（我这里就是 /data/mysql/mysql-slow.log）。
3、设置路径 set global slow_query_log_file='D:\\kpdata\\DataBase\\Data\\mysql.log';
4、设置慢查询存储的方式
set global log_output = file;
说明: 可以看到,我这里设置为了file,就是说我的慢查询日志是通过file体现的,默认是none,我们可以设置为table或者file,如果是table则慢查询信息会保存到mysql库下的slow_log表中
5、查询慢查询日志的开启状态和慢查询日志储存的位置
show variables like '%quer%';
参数说明:
slow_query_log : 是否已经开启慢查询
slow_query_log_file : 慢查询日志文件路径
long_query_time : 超过多少秒的查询就写入日志
log_queries_not_using_indexes 如果值设置为ON，则会记录所有没有利用索引的查询(性能优化时开启此项,平时不要开启)
6、使用慢查询日志示例
# Time: 190715 14:30:37 查询时间
# User@Host: root[root] @ localhost [::1] Id: 1 查询sql的主机信息
# Query_time: 0.108015 Lock_time: 0.096039 Rows_sent: 1 Rows_examined: 1
Sql执行信息
SET timestamp=1563172237; 执行的时间戳
select * from user; 查询的sql语句
cat -n /data/mysql/mysql-slow.log
从慢查询日志中，我们可以看到每一条查询时间高于1s钟的sql语句，并可以看到执行的时间是多少。
比如上面，就表示 sql语句 select * from comic where comic_id < 1952000; 执行时间为3.902864秒，超出了我们设置的慢查询时间临界点1s，所以被记录下来了。
7、永久设置慢查询日志开启，以及设置慢查询日志时间临界点
linux中，mysql配置文件一般默认在 /etc/my.cnf和window基本相同
1.修改配置文件开启慢查日志
添加如下几句
long_query_time = 0
slow-query-log = on
slow-query-log-file = E:/wamp/log/long.log
slow_query_log ：是否开启慢查询日志，1表示开启，0表示关闭。
slow-query-log-file：慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
long_query_time ：慢查询阈值【yù引申为界限或范围】，当查询时间多于设定的阈值时就记录日志。
log_queries_not_using_indexes：未使用索引的查询也被记录到慢查询日志中。
log_output：日志存储方式。'FILE'表示将日志存入文件。'TABLE'表示将日志存入数据库mysql.slow_log表中。也可以同时指定两种存储方式。
慢日志数据
Time:执行查询的时间。
User：查询的用户和IP。
Id:进程ID。
Query_time:查询所执行的时间，查询实际上在MySQL上所执行的时间。
Lock_time:查询对这个记录锁定的时间
Rows_sent:查询的行数
Rows_examined:查询这个记录所读取的行数 没有正确使用索引
三、对慢查询日志进行分析
我们通过查看慢查询日志可以发现，很乱，数据量大的时候，可能一天会产生几个G的日志，根本没有办法去清晰明了的分析。所以，这里，我们采用工具进行分析。
EXPLAIN列的解释：
table：显示这一行的数据是关于哪张表的
type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL
type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说，得保证查询至少达到range级别，最好能达到ref。
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根本不能使用索引，结果是检索会很慢
extra列返回的描述的意义
Distinct:一旦MYSQL找到了与行相联合匹配的行，就不再搜索了
Not exists: MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了
Range checked for each Record（index map:#）:没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一
Using filesort: 看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行
Using index: 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候
Using temporary 看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上
Where used 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有+问题不同连接类型的解释（按照效率高低的顺序排序）
system 表只有一行：system表。这是const连接类型的特殊情况
const:表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待
eq_ref:在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用
ref:这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好
range:这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况
index: 这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）
ALL:这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免
|
id |
SELECT识别符。这是SELECT的查询序列号 |
|
select_type |
SELECT类型,可以为以下任何一种: SIMPLE:简单SELECT(不使用UNION或子查询) PRIMARY:最外面的SELECT UNION:UNION中的第二个或后面的SELECT语句 DEPENDENT UNION:UNION中的第二个或后面的SELECT语句,取决于外面的查询 UNION RESULT:UNION 的结果 SUBQUERY:子查询中的第一个SELECT DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询 DERIVED:导出表的SELECT(FROM子句的子查询) |
|
table |
输出的行所引用的表 |
|
type |
联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序: system:表仅有一行(=系统表)。这是const联接类型的一个特例。 const:表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次! eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。 ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。 ref_or_null:该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。 index_merge:该联接类型表示使用了索引合并优化方法。 unique_subquery:该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。 index_subquery:该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr) range:只检索给定范围的行,使用一个索引来选择行。 index:该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。 ALL:对于每个来自于先前的表的行组合,进行完整的表扫描。 |
|
possible_keys |
指出MySQL能使用哪个索引在该表中找到行 |
|
key |
显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。 |
|
key_len |
显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。 |
|
ref |
显示使用哪个列或常数与key一起从表中选择行。 |
|
rows |
显示MySQL认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数。 |
|
filtered |
显示了通过条件过滤出的行数的百分比估计值。 |
|
Extra |
该列包含MySQL解决查询的详细信息 Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。 Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。 range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。 Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。 Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。 Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果。 Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。 Using sort_union(...), Using union(...), Using intersect(...):这些函数说明如何为index_merge联接类型合并索引扫描。 Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。 |
一.select_type的说明
1.UNION:
当通过union来连接多个查询结果时，第二个之后的select其select_type为UNION。
mysql> explain select * from t_order where order_id=100 union select * from t_order where order_id=200;
2.DEPENDENT UNION与DEPENDENT SUBQUERY:
当union作为子查询时，其中第二个union的select_type就是DEPENDENT UNION。
第一个子查询的select_type则是DEPENDENT SUBQUERY。
mysql> explain select * from t_order where order_id in (select order_id from t_order where order_id=100 union select order_id from t_order where order_id=200);
3.SUBQUERY:
子查询中的第一个select其select_type为SUBQUERY。
mysql> explain select * from t_order where order_id in (select order_id from t_order where order_id=100 union select order_id from t_order where order_id=200);
4.DERIVED:
当子查询是from子句时，其select_type为DERIVED。
mysql> explain select * from (select order_id from t_order where order_id=100) a;
二.type的说明
1.system，const
其中第一行的type就是为system，第二行是const，这两种联接类型是最快的。
mysql> explain select * from (select order_id from t_order where order_id=100) a;
2.eq_ref
在t_order表中的order_id是主键，t_order_ext表中的order_id也是主键，该表可以认为是订单表的补充信息表，他们的关系是1对1，在下面的例子中可以看到b表的连接类型是eq_ref，这是极快的联接类型。
mysql> explain select * from t_order a,t_order_ext b where a.order_id=b.order_id;
3.ref
下面的例子在上面的例子上略作了修改，加上了条件。此时b表的联接类型变成了ref。因为所有与a表中order_id=100的匹配记录都将会从b表获取。这是比较常见的联接类型。
mysql> explain select * from t_order a,t_order_ext b where a.order_id=b.order_id and a.order_id=100;
4.ref_or_null
user_id字段是一个可以为空的字段，并对该字段创建了一个索引。在下面的查询中可以看到联接类型为ref_or_null，这是mysql为含有null的字段专门做的处理。在我们的表设计中应当尽量避免索引字段为NULL，因为这会额外的耗费mysql的处理时间来做优化。
mysql> explain select * from t_order where user_id=100 or user_id is null;
5.index_merge
经常出现在使用一张表中的多个索引时。mysql会将多个索引合并在一起
mysql> explain select * from t_order where order_id=100 or user_id=10;
6.unique_subquery
该联接类型用于替换value IN (SELECT primary_key FROM single_table WHERE some_expr)这样的子查询的ref。注意ref列，其中第二行显示的是func，表明unique_subquery是一个函数，而不是一个普通的ref。
mysql> explain select * from t_order where order_id in (select order_id from t_order where user_id=10);
7.index_subquery
该联接类型与上面的太像了，唯一的差别就是子查询查的不是主键而是非唯一索引。
mysql> explain select * from t_order where user_id in (select user_id from t_order where order_id>10);
8.range
按指定的范围进行检索，很常见。
explain select * from t_order where user_id in (100,200,300);
9.index
在进行统计时非常常见，此联接类型实际上会扫描索引树，仅比ALL快些。
mysql> explain select count(*) from t_order;
10.ALL
完整的扫描全表，最慢的联接类型，尽可能的避免
mysql> explain select * from t_order;
三.extra的说明
1.Distinct
MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。对于此项没有找到合适的例子，求指点。
2.Not exists
因为b表中的order_id是主键，不可能为NULL，所以mysql在用a表的order_id扫描t_order表，并查找b表的行时，如果在b表发现一个匹配的行就不再继续扫描b了，因为b表中的order_id字段不可能为NULL。这样避免了对b表的多次扫描。
3.Range checked for each record
这种情况是mysql没有发现好的索引可用，速度比没有索引要快得多。
4.Using filesort
在有排序子句的情况下很常见的一种情况。此时mysql会根据联接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行。
5.Using index
这是性能很高的一种情况。当查询所需的数据可以直接从索引树中检索到时，就会出现。上面的例子中有很多这样的例子，不再多举例了。
6.Using temporary
发生这种情况一般都是需要进行优化的。mysql需要创建一张临时表用来处理此类查询。
7.Using where
当有where子句时，extra都会有说明。
8.Using sort_union(...)/Using union(...)/Using intersect(...)
下面的例子中user_id是一个检索范围，此时mysql会使用sort_union函数来进行索引的合并。而当user_id是一个固定值时，请参看上面type说明5.index_merge的例子，此时会使用union函数进行索引合并。
9.Using index for group-by
表明可以在索引中找到分组所需的所有数据，不需要查询实际的表。
