MySQL性能优化

性能优化是通过某些有效的方法提高MySQL数据库的性能。性能优化的目的是为了使MySQL数据库运行速度更快、占用的磁盘空间更小。主要包括优化查询速度、优化更新速度和优化MySQL服务器等。

数据库管理员可以使用'show status like 'value';'来查看数据库的性能:

mysql> show status like 'Connections';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 6     |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show status like 'uptime';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime        | 19052 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show status like 'com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 71    |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show status like 'com_insert';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_insert    | 1     |
+---------------+-------+
1 row in set (0.01 sec)

优化查询

通过对查询语句的分析,可以了解查询语句的执行情况。MySQL中,可使用explain和edscribe来分析语句。

mysql> explain select * from employee\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employee
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)
mysql> create index index_name on employee(name);
Query OK, 0 rows affected (0.02 sec)
mysql> explain select name from employee where name='tom'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employee
   partitions: NULL
         type: ref
possible_keys: index_name
          key: index_name
      key_len: 62
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

使用索引查询时,一定要注意索引的使用情况。例如,like关键字配置的字符创不能以’%‘开头;使用多列索引时,查询条件必须要使用这个索引的第一个字段;使用or关键字时,or关键字所链接的所有条件都必须使用索引。

优化数据库结构

  1. 将字段分解为多个表;
  2. 增加中间表;
  3. 增加冗余字段;
  4. 优化插入记录的速度;
  5. 分析表、检查表和优化表

优化服务器

  1. 优化服务器硬件;
  2. 优化MySQL的参数。

存储过程与函数

存储过程和函数实在数据库中年定义一些SQL语句的集合,然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。存储过程和函数可以避免开发人员重复的编写相同的SQL语句。而且,存储过程和函数实在MySQL服务器中存储和执行的,可以减少客户端和服务器端的数据传输。

创建存储过程和函数

创建存储过程和函数是指将经常使用的一组SQL语句组合在一起,并将这些SQL语句当做一个整体存储在MySQL服务器中。

创建示例表

idnamecompanypriceproduce_timevalidity_timeaddress
1AA饼干AA饼干厂2.520083北京
2CC牛奶CC牛奶厂3.520091河北
3EE果冻EE果冻厂1.520072北京
4FF咖啡FF咖啡厂2020025天津
5GG奶糖GG奶糖厂1420033广东
#创建表
mysql> create table food
(id int(10) not null unique primary key auto_increment,
name varchar(20) not null, 
company varchar(30) not null, 
price float, 
produce_time YEAR, 
validity_time int(4), 
address varchar(50)) 
default charset=utf8;
#插入数据
mysql> insert into food values
(1,'AA饼干','AA饼干厂',2.5,'2008',3,'北京'),
(2,'CC牛奶','CC牛奶厂',3.5,'2009',1,'河北'),  
(3,'EE果冻','EE果冻厂',1.5,'2007',2,'北京'),  
(4,'FF咖啡','FF咖啡厂',20,'2002',5,'天津'),  
(5,'GG奶糖','GG奶糖厂',14,'2003',3,'广东');
#创建存储过程
mysql> delimiter &&
mysql> create procedure food_price_count(in price_info1 float,in price_info2 float,out  count int)
    -> reads sql data
    -> begin
     -> declare temp float;
    -> declare match_price cursor for select price from food;
    -> declare exit handler for not found close match_price;
    -> set @sum=0;
    -> select count(*) into count from food
    -> where price>price_info1 and price_info2;
    -> open match_price;
    -> repeat
    -> fetch match_price into temp;
    -> if temp>price_info1 and price_info2
    -> then set @sum=@sum+temp;
    -> end if;
    -> until 0 end repeat;
    -> close match_price;
    -> end&&
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call food_price_count(2,18,@count);
Query OK, 0 rows affected (0.00 sec)
mysql> select @count,@sum;
+--------+------+
| @count | @sum |
+--------+------+
|      4 |   40 |
+--------+------+
1 row in set (0.00 sec)
#查看存储过程状态
mysql> show create procedure food_price_count\G
#删除存储过程
mysql> drop procedure food_price_count;

MySQL函数

MySQL数据库中提供了丰富的函数。通过这些函数可以简化用户的操作。

数学函数

数学函数是MySQL中常用的一类函数,主要用欧冠与处理数字,包括整型、浮点数等。数学好书包括绝对值函数、正弦函数、余弦函数和获取随机数的函数等。

字符串函数

字符串函数主要用于处理表中的字符串。包括求字符串长度、合并字符串、在字符串中插入子串和大小写字母切换等函数。

时间和日期函数

日期和时间函数主要用于处理表中的日期和时间数据。日期和时间函数包括获取当日日期的函数、获取当前时间的函数、计算日期的函数、计算时间的函数等。

条件判断函数

条件判断函数用来在SQL语句中进行条件判断。根据是否满足判断条件,SQL语句执行不同的分支。

系统信息函数

系统信息函数用来查询MySQL数据库的系统信息,例如数据库版本、当前用户等。

mysql> select database(),schema(),version(),session_user();
+------------+----------+-------------------------+----------------+
| database() | schema() | version()               | session_user() |
+------------+----------+-------------------------+----------------+
| sujx       | sujx     | 5.7.19-0ubuntu0.16.04.1 | sujx@localhost |
+------------+----------+-------------------------+----------------+
1 row in set (0.00 sec)

加密函数

因为数据库总有些敏感信息不希望被其他人看到,就应该通过加密的方式来使得这些数据变成看似乱码的数据。例如用户密码。

其他函数

MySQL查询习题

建立连个数据表,department表和employee表。


| d_id | d_name | function | d_address |
| ------------ | ------------ | ------------ | ------------ |
| 1001 | 人事部 | 人事管理 | 北京 |
| 1002 | 科研部 | 研发产品 | 成都 |
| 1003 | 生产部 | 产品生产 | 天津 |
| 1004 | 销售部 | 产品销售 | 上海 |


| id | name | sex | age | d_id | salary | address |
| :------------: |:------------: |: ------------:| :------------ | :------------: | :------------:| :------------ 😐
| 9001 | Aric | 男 | 25 | 1002 | 4000 | 北京市海淀区 |
| 9002 | Jim | 男 | 26 | 1001 | 2500 | 北京市昌平区 |
| 9003 | Tom | 男 | 20 | 1003 | 1500 | 湖南省永州市 |
| 9004 | Eric | 男 | 30 | 1001 | 3500 | 北京市顺义区 |
| 9005 | Lily | 女 | 21 | 1002 | 3000 | 北京市平谷县 |
| 9006 | Lucy | 女 | 27 | 1004 | 8000 | 上海市静安区 |
| 9007 | Jack | 男 | 23 | 1004 | 4000 | 天津市南开区 |


CREATE TABLE `department` (
  `d_id` int(10) NOT NULL,
  `d_name` varchar(20) NOT NULL,
  `function` varchar(20) DEFAULT NULL,
  `d_address` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`d_id`),
  UNIQUE KEY `d_id` (`d_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `employee` (
  `id` int(10) NOT NULL,
  `name` varchar(20) NOT NULL,
  `sex` varchar(4) DEFAULT NULL,
  `age` int(5) DEFAULT NULL,
  `d_id` varchar(20) DEFAULT NULL,
  `salary` float DEFAULT NULL,
  `address` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

然后在department表和employee表查询记录。
查询的要求如下:
1. 计算所有员工的出生年份,并显示结果中的字段别名为birth_year;

mysql> select name,2017-age as birty_year from employee ;
+------+------------+
| name | birty_year |
+------+------------+
| Aric |       1992 |
| Jim  |       1991 |
| Tom  |       1997 |
| Eric |       1987 |
| Lily |       1996 |
| Lucy |       1990 |
| Jack |       1994 |
+------+------------+
7 rows in set (0.00 sec)
  1. 用limit关键字来查询工资最低的员工的信息;
mysql> select * from employee order by salary limit 0,1;
+------+------+------+------+------+--------+--------------------+
| id   | name | sex  | age  | d_id | salary | address            |
+------+------+------+------+------+--------+--------------------+
| 9003 | Tom  | 男   |   20 | 1003 |   1500 | 湖南省永州市       |
+------+------+------+------+------+--------+--------------------+
  1. 计算男性与女性员工的平均工资;
mysql> select sex,avg(salary) from employee group by sex;                                                       +------+-------------+
| sex  | avg(salary) |
+------+-------------+
| 女   |        5500 |
| 男   |        3100 |
+------+-------------+
2 rows in set (0.01 sec)
  1. 查询在上海工作的员工姓名、性别、年龄和部门名称;
select name,sex,age,department.d_name,d_address from employee,department where employee.d_id=(select d_id from department where d_address='上海') and employee.d_id=department.d_id;
+------+------+------+-----------+-----------+
| name | sex  | age  | d_name    | d_address |
+------+------+------+-----------+-----------+
| Lucy | 女   |   27 | 销售部    | 上海      |
| Jack | 男   |   23 | 销售部    | 上海      |
+------+------+------+-----------+-----------+
2 rows in set (0.00 sec)
  1. 用右连接的方式查询department表和employee表;
mysql> select name,sex,age,department.d_name,d_address,function from employee right join department on employee.d_id=department.d_id;
+------+------+------+-----------+-----------+--------------+
| name | sex  | age  | d_name    | d_address | function     |
+------+------+------+-----------+-----------+--------------+
| Aric | 男   |   25 | 科研部    | 成都      | 研发产品     |
| Jim  | 男   |   26 | 人事部    | 北京      | 人事管理     |
| Tom  | 男   |   20 | 生产部    | 天津      | 产品生产     |
| Eric | 男   |   30 | 人事部    | 北京      | 人事管理     |
| Lily | 女   |   21 | 科研部    | 成都      | 研发产品     |
| Lucy | 女   |   27 | 销售部    | 上海      | 产品销售     |
| Jack | 男   |   23 | 销售部    | 上海      | 产品销售     |
+------+------+------+-----------+-----------+--------------+
7 rows in set (0.00 sec)
  1. 查询名字以字母T为开头的员工姓名、性别、年龄、部门和工作地点;
mysql> select name,sex,age,department.d_name,d_address from employee,department where name like "^T" and employee.d_id=department.d_id;
+------+------+------+-----------+-----------+
| name | sex  | age  | d_name    | d_address |
+------+------+------+-----------+-----------+
| Tom  | 男   |   20 | 生产部    | 天津      |
+------+------+------+-----------+-----------+
1 row in set (0.00 sec)
  1. 查询年龄小于25或者大于30岁的员工信息;
mysql> select * from employee where age>30 or age<25;
+------+------+------+------+------+--------+--------------------+
| id   | name | sex  | age  | d_id | salary | address            |
+------+------+------+------+------+--------+--------------------+
| 9003 | Tom  | 男   |   20 | 1003 |   1500 | 湖南省永州市       |
| 9005 | Lily | 女   |   21 | 1002 |   3000 | 北京市平谷县       |
| 9007 | Jack | 男   |   23 | 1004 |   4000 | 天津市南开区       |
+------+------+------+------+------+--------+--------------------+
  1. 查询家住永州市的员工的信息。
mysql> select * from employee where address like "%永州%";
+------+------+------+------+------+--------+--------------------+
| id   | name | sex  | age  | d_id | salary | address            |
+------+------+------+------+------+--------+--------------------+
| 9003 | Tom  | 男   |   20 | 1003 |   1500 | 湖南省永州市       |
+------+------+------+------+------+--------+--------------------+
1 row in set (0.00 sec)

MySQL的事务

事务(Transaction)是数据库区别于文件系统的重要特性之一。事务会把数据库从一种一致状态转换为另一种一致状态。在数据库提交工作时,可以确保要么所有修改都已经保存了,要么所有修改都不保存。


InnoDB存储引擎中的事务完全符和ACID的特性:
1. A原子性(Atomicity)
原子性指整个数据库事务是不可分割的工作单元。只有使事务中所有的数据库操作都执行成功,才算整个事务的成功。事务中任何一个SQL语句执行失败,已经执行成功的SQL语句也必须撤销,数据库状态应该退回到执行事务前的状态。
2. C一致性(Consistency)
一致性指事务将数据库从过一种状态转变为下一种一致性的状态。事务是一致性的单位,如果事务中某个动作失败了,系统可以自动撤销事务——返回初始化的状态。
3. I隔离性(Isolation)
隔离性还有其他称呼,如并发控制、可串行化、锁等。事务的理性要求每个读写事务的对象对其他事务的操作对象能相互分离,即该事务提交前对其他事务都不可见,通常这是用锁来实现。
4. D持久性(Durability)
事务一旦提交,其结果就是永久性的。及时发生宕机等故障,数据库也能将数据恢复。注意,只能从事务本身的角度来保证结果的永久性。如果因为外部原因,比如raid卡损坏、不可抗力等原因导致数据库损坏,所提交数据仍然有可能丢失。因此,持久性保证事物系统的高可靠性,而不是高可用性。

认识事务

事务是访问并更新数据库中各种数据项的一个程序执行单元。在事务中的操作,要么都做修改,要么都不做。这就是事务的目的,也是事务模型区别与文件系统的重要特性之一。

分类

  • 扁平事务
    扁平事务中,所有操作都处于同一层次,由begin work开始,由commit work或者rollback work结束,期间的操作是原子的,要么执行,要么回滚。
  • 带有保存点的扁平事务
    除了支持扁平事务支持的操作外,允许在事务执行过程中回滚到同一事务中较早的一个状态。保存点用来通知系统应该记住事务当前的一个状态。
  • 链事务
    带有保存点的扁平事务,当发生系统崩溃时,所有的保存点都将小时,因为其保存点是易失的,而非持久的。链事务的思想是:在提交一个事务时,释放不需要的数据对象,将必要的处理上下文隐式的传给下一个要开始的事务。提交事务操作和开始下一个事务操作将合并为一个原子操作。
  • 嵌套事务
    嵌套事务是一个层次结构框架,由一个顶层事务控制各个层次的事务。顶层事务之下嵌套的事务被成为子事务。
  • 分布式事务
    在分布式环境下运行的扁平事务,需要根据数据所在位置访问网络的不同节点。

实现

事务的隔离性由锁来实现。原子性、一致性和持久性通过数据库的redo log和undo log来完成。redo log成为重做日志,用来保证事务的原子性和持久性。undo log用来保证事务的一致性。
redo和undo的作用都可以视为一种恢复操作,redo恢复提交事务修改的页操作,而undo回滚行记录到某个特定版本。redo通常是物理日志,记录的是页的物理修改操作。undo是逻辑日志,根据每行记录进行记录。

redo

重做日志用来实现事务的持久性。其由两部分组成:一是内存中的重做日志缓冲(redo log buffer),其实易失的;二是重做日志文件(redo log file),其是持久的。
InnoDB是事务的存储引擎,当事务提交时,必须先将该事务的所有日志写入到重做日志文件进行持久化,待事务的commit操作完成才算完成。
为了确保每次日志都写入redo log,在每次将redo log buffer写入redo log之后,InnoDB都需要调用fsync操作。因此,磁盘性能决定了事务提交的性能,也就是数据库的性能。
参数innodb_flush_log_at_trx_commit用来控制重做日志刷新到磁盘的策略。该参数的默认值为1,表示事务提交时必须调用一次fsync操作。开可以设置该参数的值为0和2.0表示事务提交时不进行写入redo log操作,这个操作仅在master thread中完成,而在master thread中每一秒会进行重做日志文件的fsync操作。2表示事务提交时将redo log buffer写入redo log,不进行fsync操作。在这个设置下,当MySQL数据宕机而操作系统未宕机时,并不会丢失事务。

undo

事务的回滚操作需要由undo来实现。与redo不同,undo存放在数据库内部一个特殊的字段中,这个段被成为undo段,它位于共享表空间内。可以通过py_innodb_page_info.py来查看。

事务的隔离级别

SQL标准定义的四个隔离级别由低到高为:
* read uncommitted——浏览访问
* read commited——游标稳定
* repeatable read——隔离,没有幻读的保护,为InnoDB默认级别
* serializable——隔离,串行化