一、mysql优化相关介绍
假设一个网站从最开始访问量很小做到日PV千万，下面来推测一下它的mysql服务器架构演变过程。
第一阶段：网站访问量日pv量级在1w以下。
单台机器跑web和db，不需要做架构层调优（比如不需要增加memcached缓存）。此时，数据是每日冷备份的，但是如果考虑数据安全性，会搭建一个mysql主从。
第二阶段：网站访问量日pv达到几万。
此时单台机器已经有点负载，需要把web和db分开，需要搭建memcached服务作为缓存。也就是说，在这个阶段，还可以使用单台机器跑mysql去承担整个网站的数据存储和查询。如果做mysql主从目的也是为了数据安全性。
第三阶段：网站访问量日pv达到几十万。
单台机器虽然也可以支撑，但是需要的机器配置要比之前的机器多很多。如果经费允许，可以购买配置很高的机器来跑mysql服务，但是到了一定阶段，配置增加已经不能带来性能的增加。所以此阶段，会想到做mysql服务的集群，也就是说可以拿多台机器跑mysql。
但mysql的集群和web集群是不一样的，我们需要考虑数据的一致性，所以不能简单套用做web集群的方式。可以做的架构是mysql主从，一主多从。为了保证架构的强壮和数据完整，主只能是一个，从可以是多个。还有一个问题，就是在前端web层，我们程序里面指定了mysql机器的ip，那么当mysql机器有多台时，程序里面如何去配置？我们可以拿多台机器跑mysql，其中一台写，其他多台是读，只需要把读写的ip分别配置到程序中，程序自动会去区分机器。
第四阶段：网站访问量日pv到几百万。
之前的一主多从模式已经遇到瓶颈，因为当网站访问量变大，读数据库的量也会越来越大，我们需要多加一些从进来，但是从的数量增加到数十台时，由于主需要把bin-log全部分到所有从上，那么这个过程本身就是一件很繁琐的读取，势必会造成从上同步过来的数据有很大延迟。所以，我们可以做一个优化，把mysql原来的一主多从改为一主一从，然后作为其他从的主，而前面的主，只负责网站业务的写入，而后面的从不负责网站任何业务，只负责给其他从同步bin-log。这样还可以继续多叠加几个从库。
第五阶段：网站访问量日pv到1千万。
网站的写入量非常大，之前架构中只有一个主，这里的主已经成为瓶颈了。所以，需要再进一步作出调整。
想要解决瓶颈，可以从以下几个方面进行优化。
1.查看数据库性能参数
查询一些MySQL数据库的性能参数用show status。
mysql> show status like 'connections'; #连接mysql服务器的次数
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections | 3 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show status like 'uptime'; #mysql服务器的上线时间
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime | 16956 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show status like 'com_select'; #查询操作的次数
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select | 0 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show status like 'com_insert'; #插入操作的次数
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_insert | 0 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show status like 'com_update'; #更新操作的次数
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_update | 0 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show status like 'com_delete'; #删除操作的次数
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_delete | 0 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show status like 'slow_queries'; #查询mysql服务器的慢查询次数
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries | 0 |
+---------------+-------+
1 row in set (0.00 sec)
二、分析查询语句
1.语法格式
Explain [extended] select select_option;
{EXPLAIN | DESCRIBE | DESC}
tbl_name [col_name | wild]
{EXPLAIN | DESCRIBE | DESC}
[explain_type]
{explainable_stmt | FOR CONNECTION connection_id}
explain_type: {
EXTENDED
| PARTITIONS
| FORMAT = format_name
}
format_name: {
TRADITIONAL
| JSON
}
explainable_stmt: {
SELECT statement
| DELETE statement
| INSERT statement
| REPLACE statement
| UPDATE statement
}
2.示例
创建fruits表
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> create table fruits
-> (
-> f_id char(10) not null,
-> s_id int not null,
-> f_name char(255) not null,
-> f_price decimal(8,2) not null,
-> primary key(f_id)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into fruits(f_id,s_id,f_name,f_price)
-> values('a1',101,'apple','5.2'),
-> ('b1',101,'blackberry','10.2'),
-> ('bs1',102,'orange','11.2'),
-> ('bs2',105,'melon','8.2'),
-> ('t1',102,'banana','10.3'),
-> ('t2',102,'grape','5.3'),
-> ('o2',103,'coconut','9.2'),
-> ('c0',101,'cherry','3.2'),
-> ('a2',103,'apricot','2.2'),
-> ('l2',104,'lemon','6.4'),
-> ('b2',104,'berry','7.6'),
-> ('m1',106,'mango','15.7'),
-> ('m2',105,'xbabay','2.6'),
-> ('t4',107,'xbababa','3.6'),
-> ('m3',105,'xxtt','11.6'),
-> ('b5',107,'xxxx','3.6');
Query OK, 16 rows affected (0.00 sec)
Records: 16 Duplicates: 0 Warnings: 0
(1)explain语法分析
mysql> explain select * from fruits\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: fruits
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL #使用哪个列或常数与索引一起使用来查询记录
rows: 16
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.01 sec)
参数解释
Select_type：表示select语句的类型
simple是简单查询(不包括连接查询和子查询)
Primary是主查询
Union是连接查询
Type：表的连接类型
System仅有系统表一行
Const数据表中最多只有一行匹配，将在查询开始时被读取，并在余下的查询优化中，作为常量
Eq_ref用于使用=操作符比较带索引的列
ref对于来自前面的表的任意行的组合，从该表中读取所有匹配的行
ref_or_null同上，添加可以专门搜索包含null值的行
index_merge将连接类型表示使用了索引并优化方法
range只检索给定范围的行
index与all的连接类型相同，除了只扫描索引树
all前面的表的任意行的组合，进行完整的表的扫描
possible_keys：NULL
指出mysql使用哪个索引在表中找到行NULL表示没有创建索引
(2)describe语句分析
mysql> desc select * from fruits\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: fruits
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 16
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
三、添加索引提高查询效率
1.语法格式
create index 索引名 on 表名(字段)
2.创建普通索引
mysql> explain select * from fruits where f_name='apple'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: fruits
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 16
filtered: 10.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
mysql> create index test1 on fruits(f_name);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from fruits where f_name='apple'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: fruits
partitions: NULL
type: ref
possible_keys: test1
key: test1
key_len: 765
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
注意：添加索引之后，用like ‘x%’查询。%不在第一位查询效率最高。
mysql> explain select * from fruits where f_name like '%x'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: fruits
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 16
filtered: 11.11
Extra: Using where
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from fruits where f_name like 'x%'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: fruits
partitions: NULL
type: range
possible_keys: test1
key: test1
key_len: 765
ref: NULL
rows: 4
filtered: 100.00
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
3.添加多字段索引
除了第一字段查询最快，其余不按索引来，索引不生效。(最左原则)
mysql> create index test2 on fruits(f_id,f_price);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from fruits where f_id=12\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: fruits
partitions: NULL
type: ALL
possible_keys: PRIMARY,test2
key: NULL
key_len: NULL
ref: NULL
rows: 16
filtered: 10.00
Extra: Using where
1 row in set, 5 warnings (0.00 sec)
mysql> explain select * from fruits where f_price=5.2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: fruits
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 16
filtered: 10.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from fruits where f_price=5.2 and f_id=12\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: fruits
partitions: NULL
type: ALL
possible_keys: PRIMARY,test2
key: NULL
key_len: NULL
ref: NULL
rows: 16
filtered: 6.25
Extra: Using where
1 row in set, 5 warnings (0.00 sec)
若创建索引所设置的字段，查询索引组合or左右边的值都是属于索引设置字段下的值。
mysql> explain select * from fruits where f_name='apple' or s_id=101\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: fruits
partitions: NULL
type: ALL
possible_keys: test1
key: NULL
key_len: NULL
ref: NULL
rows: 16
filtered: 19.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from fruits where f_name='apple' or f_id=12\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: fruits
partitions: NULL
type: ALL
possible_keys: PRIMARY,test1,test2
key: NULL
key_len: NULL
ref: NULL
rows: 16
filtered: 19.00
Extra: Using where
1 row in set, 6 warnings (0.00 sec)
四、profiling分析查询
通过慢查询日志可以知道哪些SQL语句执行效率低下，通过explain可以得知SQL语句的具体执行情况，索引使用等，还可以结合show命令查看执行状态。如果觉得explain的信息不够详细，可以通过profiling命令得到更准确的SQL执行消耗系统资源的信息。profiling默认是关闭的。
1.通过以下语句查看
mysql> show variables like 'profiling'; #off表示未开启
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling | OFF |
+---------------+-------+
1 row in set (0.00 sec)
或者
mysql> select @@profiling; #0表示未开启
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
2.打开profiling功能
mysql> set @@profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 1 |
+-------------+
1 row in set, 1 warning (0.00 sec)
3.执行要测试的sql语句
mysql> select * from fruits where f_name='apple';
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| a1 | 101 | apple | 5.20 |
+------+------+--------+---------+
1 row in set (0.00 sec)
mysql> show profiles;
+----------+------------+-------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------+
| 1 | 0.00011300 | set @@profiling=1 |
| 2 | 0.00013225 | select @@profiling |
| 3 | 0.00028375 | select * from fruits where f_name='apple' |
+----------+------------+-------------------------------------------+
3 rows in set, 1 warning (0.00 sec)
mysql> show profile for query 3;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000065 |
| checking permissions | 0.000007 |
| Opening tables | 0.000017 |
| init | 0.000021 |
| System lock | 0.000007 |
| optimizing | 0.000008 |
| statistics | 0.000064 |
| preparing | 0.000011 |
| executing | 0.000003 |
| Sending data | 0.000042 |
| end | 0.000004 |
| query end | 0.000006 |
| closing tables | 0.000006 |
| freeing items | 0.000012 |
| cleaning up | 0.000011 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)
4.参数解释
status：是profile里的状态。
duration：是status状态下的耗时。关注哪个状态最耗时，这些状态中哪些可以优化。
当然也可以查看更多的信息如CPU等等。
语法格式
SHOW PROFILE [type [, type] ... ]
[FOR QUERY n]
[LIMIT row_count [OFFSET offset]]
type: {
ALL
| BLOCK IO
| CONTEXT SWITCHES
| CPU
| IPC
| MEMORY
| PAGE FAULTS
| SOURCE
| SWAPS
}
type参数解释
ALL：显示所有的开销信息
BLOCK IO：显示块IO相关开销
CPU：显示用户CPU时间、系统CPU时间
IPC：显示发送和接收相关开销信息
PAGE FAULTS：显示页面错误相关开销信息
SWAPS：显示交换次数相关开销的信息
5.测试完成以后，记得要关闭调试功能，以免影响数据库的正常使用。
mysql> set @@profiling=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
1 row in set, 1 warning (0.00 sec)
五、优化数据库表结构
1.优化数据表结构方法
(1)将字段很多的表分解成多个表
(2)增加中间表
(3)合理增加冗余字段
(4)优化插入记录的速度
2.对于myisam来说
(1)禁用索引
在插入数据之前禁用索引，会让创建索引不会生效。
命令格式
alter table 表名 disable keys
注意表的创建，表后加引擎engine=myisam，可以禁用成功。
mysql> alter table fruits disable keys;
Query OK, 0 rows affected, 1 warning (0.00 sec)
(2)禁用唯一性检查
插入记录之前禁用唯一性检查。
mysql> set unique_checks=0;
Query OK, 0 rows affected (0.00 sec)
(3)使用批量插入(多条插入命令整合成一条命令)
mysql> INSERT INTO fruits values('x1','101','mongo2','5.5');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO fruits values('x2','101','mongo2','5.5');
Query OK, 1 row affected (0.08 sec)
mysql> INSERT INTO fruits values('x3','101','mongo2','5.5');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO fruits values('x4','101','mongo2','5.5');
Query OK, 1 row affected (0.01 sec)
mysql> insert into fruits values ('x8','101','mongo2','5.5'),
('x7','101','mongo2','5.5'), ('x6','101','mongo2','5.5'),
('x5','101','mongo2','5.5');
Query OK, 4 rows affected (0.07 sec)
Records: 4 Duplicates: 0 Warnings: 0
3.对于innnodb表来说，使用load data infile批量插入。
(1)禁用唯一性检查
mysql> set unique_checks=0;
Query OK, 0 rows affected (0.00 sec)
(2)禁用外键检查
mysql> set foreign_key_checks=0;
Query OK, 0 rows affected (0.00 sec)
(3)禁用自动提交
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
六、分析表、检查表和优化表
1.分析表
分析关键字的分布。
语法格式
ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
TABLE tbl_name [, tbl_name] ...
Local的关键字不写入二进制日志，后跟1个表或多个表。
在分析期间只能读，不能进行插入和更新的操作。
mysql> analyze table fruits;
+-------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------------+---------+----------+----------+
| test.fruits | analyze | status | OK |
+-------------+---------+----------+----------+
1 row in set (0.00 sec)
参数解释
Table：表名
op：执行的操作是什么
msg_type：信息级别(status是正常状态，info是信息，note注意，warning警告，error错误)
msg_text：显示信息
2.检查表
检查是否存在错误，关键字统计，检查视图是否有错误。
语法格式
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option: {
FOR UPGRADE
| QUICK
| FAST
| MEDIUM
| EXTENDED
| CHANGED
}
参数解释
Quick不扫描行，不检查错误连接
Fast只检查没有被正确关闭的表
Medium扫描行验证被删除的连接是有效的，也可以计算各行的关键字校验和。
Extended对每行所有关键字进行全面的关键字查找
Changed只检查上次检查后被更改的表和没有被正确关闭的表
注意：Option只对myisam有效，对innodb表无效。
在执行时会给表加上只读锁。
mysql> check table fruits;
+-------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------------+-------+----------+----------+
| test.fruits | check | status | OK |
+-------------+-------+----------+----------+
1 row in set (0.00 sec)
3.优化表
消除删除或更新造成的空间浪费。
语法格式
OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL]
TABLE tbl_name [, tbl_name] ...
只能优化myisam的表和innodb的表，但是只能优化表中的varchar\text\blob。
执行过程中上只读锁
mysql> optimize table fruits\G
*************************** 1. row ***************************
Table: test.fruits
Op: optimize
Msg_type: note
Msg_text: Table does not support optimize, doing recreate + analyze instead
*************************** 2. row ***************************
Table: test.fruits
Op: optimize
Msg_type: status
Msg_text: OK
2 rows in set (0.03 sec)
358
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
