Mysql 应用程序优化 管理 锁 使用技巧

Mysql高级

1.应用优化

1.1使用连接池

对于访问数据库来说，建立链接的代价是比较昂贵的，因为我们频繁的创建关闭链接时比较耗费资源的，我们有必要简历数据库连接池，以提高访问的性能

1.2减少对MySQL的访问

1.2.1 避免对数据进行重复检索

在编写应用代码时，需要能够理清对数据库的访问逻辑。能够一次链接就获取到结果，就不用两次链接，这样可以大大减少对数据无用的重复请求

1.2.2增加cache层

在应用中，我们可以在应用中增加缓存层来达到减少数据库负担的目的。缓存层又很多种，也有很多实现方式，只要能够达到降低数据库的负担又能满足应用需求就可以
因为可以部分数据从数据库中抽取出来放到应用端以文本方式存储，或者使用框架（Mybatis，Hibernate）提供的一级，耳机缓存，或者使用redis数据库来缓存数据

1.3负载均衡

负载均衡时应用中使用非常普遍的一种优化方法，它的机制就是利用某种均衡算法，将固定的负载量分布到不同的服务器上，以此来降低单台服务器的负载，达到优化的效果

1.3.1利用MySQL复制分流查询

通过MySQL的主从复制，实现读写分离，使增删改操作走主节点，查询走从节点，从而可以降低单台服务器的读写压力。

1.3.2采用分布式数据库架构

分布式数据库架构适合大数据量，负载高的情况，它有良好的拓展性和高可用性。通过多台服务器之间分布数据，可以实现在多台服务器之间的负载均衡，提高访问效率

2.Mysql中查询缓存优化

2.1概述

开启Mysql的查询缓存，当执行完全相同的SQL语句的时候，服务器就会直接从缓存中读取结果，当数据被修改，之前的缓存会失效，修改比较频繁的表不适合做查询缓存

2.2操作流程

1.客户端发送一条查询给服务器
2.服务器先会检查缓存，如果命中了缓存，则立即返回存储在缓存中的结果。否则进入下一阶段
3.服务器端进行SQL解析，预处理，再由优化器生成对应的执行计划
4.MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询
5.将结果返回给客户端

2.3查询缓存配置

1.查看当前的MySQL数据库是否支持查询缓存

SHOW VARIABLES LIKE 'have_query_cache';

2.查看当前MySQL是否开启了查询缓存

SHOW VARIABLES LIKE 'query_cache_type';

3.查看查询缓存占用大小

SHOW VARIABLES LIKE 'query_cache_size';

4.查看缓存的状态变量

SHOW STATUS like 'Qcache%';

2.4开启查询缓存

2.5查询缓存SELECT选项

可以在SELECT语句中指定两个与查询缓存相关的选项
SQL_CACHE 如果查询结果是可缓存，并且query_cache_type系统变量为ON或者DEMAND，则缓存查询结果。
SQL_NO_CACHE 服务器不使用查询缓存。它既不检查查询缓存，也不检查结果是否缓存，也不缓存查询结果

SELECT SQL_CACHE id,name FROM customer;
SELECT SQL_NO_CACHE id,name FROM customer;

2.6查询缓存失效的情况

1）SQL查询语句不一致的情况，要想命中查询缓存，查询的SQL语句必须一致

select count(*)from table_name

Select count(*)from table_name

2）当查询语句中有一些不确定的时候，则不会走缓存。如 now(),current_date(),curdate(),curtime(),rand(),uuid(),user(),database()

3)不使用任何表查询语句

4)查询mysql,information_schema或 performance_schema数据库中的表时，不会走查询缓存

5)在存储的函数，触发器或事件的主体内执行的查询

6）如果表更改，则使用该表的所有告诉缓存查询都将变为无效并从告诉缓存中删除。这包括使用MERGE映射到已更改表的表的查询。一个表可以被许多类型的与，如被改变INSET,UPDATE,DELETE,TRUNCATE TABLE,ALTER TABLE,DROP TABLE,或DROP DATABASE

3.Mysql内存管理及优化

3.1内存优化原则

1)将尽量多的内存分配给MySQL做缓存,但要给操作系统和其他程序预留足够内存
2)MyISAM存储引起的数据问读取依赖于操作系统自身的IO缓存,因此如果有MyISAM表,就要预留更多的内存给操作系统做IO缓存
3)排序区,链接区等缓存是分配给每个数据库会话专用的,其默认的设置要根据最大连接数合理分配,如果设置太大,不但浪费资源,而且在并发连接较高时会导致物理内存耗尽

3.2MyISAM内存优化

myisam存储引起使用key_buffer缓存索引块,加速myisam索引的读写速度.对于muisam表的数据库,mysql没有特别的缓存机制,完全依赖于操作系统的IO缓存

key_buffer_size
key_buffer_size决定MyISAM索引块缓存区的大小,直接影响到M有ISAM表的存取效率.可以在MySQL参数文件中设置key_buffer_size的值,对于一般MyISAM数据库,建议至少将1/4可用内存分配至key_buffer_szie
在/usr/my.cnf中做配置
默认是

key_buffer_size=512M

read_buffer_size
如果需要经常扫描myisam表,可以通过增大read_buffer_size的值来改善性能.但需要注意的是read_buffer_size是每个session独占的,如果默认值设置太大,就会造成内存浪费

read_rnd_buffer_size
对于需要做排序的myisam表的查询,入带有order by子句的sql,适当增加read_rnd_buffer_size的值,可以改善此类的sql性能.但需要注意的是read_rnd_buffer_size是每个session独占的,如果默认值设置太大,就会造成内存浪费

3.3InnoDB内存优化

用一块内存区做IO缓存池,该缓存池不仅用来缓存innodb的索引块,而且也用来缓存innodb的数据块.

innodb_buffer_pool_size
该变量决定了Innodb存储索引表数据和索引数据的最大缓存区大小.在保证操作系统及其其他程序有足够内存可用的情况下,innodb_buffer_pool_size的值越大,缓存命中率越高,访问InnoDB表需要的磁盘I/O就越少,性能也就越高

innodb_buffer_pool_size=512M

innodb_log_buffer_size
决定了innodb重做日志缓存的大小,对于可能产生大量更新记录的大事务,增加innodb_log_buffer_size的大小,可以避免innodb在事务提交前就执行不必要的日志写入磁盘操作
默认

/usr/my.cnf

innodb_log_buffer_size=10M

4.Mysql并发参数调整

从实现上来说,MySQL Server是多线程结构,包括后台线程和客户端线程.多线程可以有效利用服务器资源,提高数据库的并发性能.在Mysql,龙之并发连接和线程的主要参数包括max_connections.back_log,thread_cache_size,table_open_cache

4.1 max_connections

采用max_connections控制允许连接到MYSQL数据库的最大连接数,默认151/如果状态遍历connection_errors_max_connections部位零,并且一直增长,则说明不断有连接请求因数据库连接数已达到允许最大值而失败,这是可以考虑增大max_connections的值.
mysql最大可支持的连接数,取决于很多因素,包括给定操作系统平台的数据库的质量,内存大小,每个连接的负荷,CPU的处理速度,期望的响应时间等.在Linux平台下,性能好的服务器,支持500-1000个连接不是难事,需要根据服务器性能进行评估设定

show VARIABLES like 'max_connections';

4.2 back_log

back_log参数控制MYSQL监听TCP端口时设置的积压请求栈大小.如果Mysql的链接数达到max_connections时,新来的请求将会被存放在堆栈中,以等待某一个链接释放资源,该堆栈的输了即back_log,如果等待链接的输了超过bakc_log,将不被授予链接资源,将会报错.5.6.6版本之前默认50,之后是50+(max_connections/5) ,但最大不超过900
如果需要数据库在较短的时间内处理大量链接请求,可以考虑适当增大back_log的值

show VARIABLES like 'back_log'

4.3 table_open_cache

该参数用来控制所有SQL语句执行线程可以打开表缓存的数量,而在执行SQL语句时,每一个SQL执行线程至少要打开一个表缓存.该参数的值应该根据设置的最大连接数max_connections以及每个连接执行关联查询中涉及的表的最大数量来设定

show VARIABLES like 'table_open_cache'

4.4 thread_cache_size

为了加快连接数据库的速度,MYSQL会缓存一定数量的客户服务线程以备重用,通过参数thread_cache_size可控制MYSQL缓存客户端服务线程的数量(线程池)

show VARIABLES like 'thread_cache_size'

4.5 innodb_lock_wait_timeout

用来设置InnoDB事务等待行锁的时间,默认值是50ms,可以根据需要进行动态设置.对于需要快速反馈的业务系统来说,可以将行锁的等待时间调小,以避免事务长时间挂起;对于后台运行的批量处理程序来说,可以将行锁的等待时间调大,以避免发生大的回滚操作

show VARIABLES like 'innodb_lock_wait_timeout'

5.Mysql锁问题

5.1锁概述

锁是计算机协调多个进行或现场并发访问某一资源的机制(避免资源的争抢)
在数据库中,除传统的计算资源(如CPU,RAM,I/O等)的争用以外,书库也是一种供需要用户共享的资源.如果保证书库并发访问的一致性,有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素.从这个角度来说.锁对数据库而言显得尤其重要,也更加复杂

5.2锁分类

从对数据操作的力度分
1)表锁:操作时,会锁定整个表
2)行锁:操作时,会锁定当前操作行
从对数据操作的类型分
1)读锁(共享锁):针对同一份数据,多个读操作可以同时进行而不会相互影响
2)写锁(排它锁):当前操作没有完成之前,它会阻断其他写锁和读锁

5.3Mysql锁

相对其他数据库而言,MYSQL的锁机制比较简单,其最显著的特点是不同的存储引起支持不同的锁机制.下表中罗列出了各存储引擎对锁的支持情况

MYSQL这3种锁的腾讯可大致归纳如下

5.4MyISAM表锁

只支持表锁

如何加锁

MYISAM在执行查询语句(select)前,会自动给涉及的所有表加读锁,在执行更新操作(update,delete,insert等)前,会自动给涉及的表加写锁.因此用户一般不需要直接使用LOCK TABLE 命令给MYISAM表显式加锁

显式加表锁语法

加读锁 lock table table_name read;

加写锁 lock table table_name write;

查看锁的证用情况

show open tables;

In_use :表当前被查询使用的次数.如果该数为零,则表示打开的,但是当前没有被使用
Name_locked:表名称是否被锁定.名称锁定用于取消表或对表进行重命名等操作

show status like 'Table_locks%'

table_locks_immediate:指的是能够立即获得表级锁的次数,每立即获取锁,值加1
table_locks_waited:指的是不能立即获取表级锁而需要等待的次数,每等待一次,该值加1,此值高说明存在着较为严重的表锁争用情况

5.5InnoDB锁问题

5.3.1行锁介绍

行锁特点:偏向InnoDB存储引擎,开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高.
InnoDB与MyISAM的最大不同有两点:一是支持事务;二是采用了行级锁

5.3.2背景知识

事务及其ACID属性
事务是一组SQL语句组成的逻辑处理单元
事务具有以下4个腾讯,简称为事务ACID属性

并发事务处理带来的问题

事务隔离级别

MYSQL的数据库的默认隔离级别为Repeatable read,查看方式

show variables like 'tx_isolation';

5.3.3InnoDB的行锁模式

• 共享锁:又称读锁,简称S锁,共享锁就是多个事务对于同一数据可以共享一把锁,都能访问到数据,但是只能读不能修改
• 排他锁:又称写锁,简称X锁,排他锁就是不能与掐他锁并存,如一个事务获取了一个数据行的排他锁,其他事务就不能再获取该行的其他锁,包括共享锁和排他锁,但是获取排他锁的事务是可以对数据进行读取和修改
  对于UPDATE,DELETE,INSERT语句,InnoDB会自动给涉及数据集加排他锁
  对于普通SELECT语句,InnoDB不会加任何锁
  可以通过以下语句显示给记录集加共享锁和排他锁

共享锁 SELECT * FROM table_name WHERE ...  LOCK IN SHARE MODE
排他锁 SELECT * FROM table_name Where ... for UPDATE

无索引行锁等级为表锁
如果不通过索引条件检索数据,那么InnoDB将对表中的所有记录加锁,实际效果跟表锁一样

间隙锁危害
当我们用范围条件,而不是使用相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据进行加锁;对于键值在条件范围内但不存在的记录,叫做"*间隙(GAP)*",InooDB也会对这个"间隙"加锁,这种锁机制就是所谓的间隙锁(Next_Key锁)

InnoDB行锁争用情况

show status like 'innodb_row_lock%';

5.3.4总结

InnoDB存储引起由于事先了行级锁定,虽然在锁定机制的实现方面带来了性能损耗可能比表锁会更高一些,但是在整体并发处理能力方面要远远高于MUISAM的表锁的.当系统并发量较高的时候,InnoDB的整体性能和MyISAM相比就会有比较明显的优势
优化建议

• 尽可能让所有数据检索都能通过索引来完成,避免无索引行锁升级为表锁
• 合理设计索引,尽量缩小锁的范围
• 尽可能减少索引条件,及索引范围,避免间隙锁
• 尽量控制事务大小,减少锁定资源量和时间长度
• 尽可使用低级别事务隔离(但是需要业务层面满足需求)

6.常用SQL技巧

6.1 SQL执行顺序

编写顺序

执行顺序

6.2正则表达式

正则表达式是指一个用来描述或者匹配一系列符合某个句法规则的字符串的单个字符串

select * from table_name where name regex '^T';

6.3MYSQL常用函数

数字函数

字符串函数

日期函数

聚合函数

7.MySql中常用工具

7.1mysql(执行脚本)

mysql -u root -p
mysql -h 127.0.0.1 -p 3306 -u root -p

-u --user=name 指定用户名
-p --password 指定密码
-h --host 指定服务器IP或域名
-p --port 指定连接客户端

-e --execute=name 执行SQL语句并退出
此选项可以在MYSQL客户端执行SQL语句,而不用连接到MYSQL数据库再执行,对于一些批处理脚本,这种方式尤其方便

mysql -uroot -p2143 db01 -e ""select * from table_name;

7.2mysqladmin(管理操作)

mysqladmin是一个执行管理操作的客户端程序.可以它来检查服务器的配置和当前状态,创建并删除数据库等
可以通过mysqladmin --help 指令查看帮助文档

mysqladmin -uroot -p2143 create 'test01';

7.3mysqlbinlog(日志)

由于服务器生成的二进制文件以二进制格式保存,所以如果想要检查这些文本的文本格式,就会使用到mysqlbinlog日志管理工具

mysqlbinlog [options] log-files1 log-files2 ...
选项
   -d   --database=name  指定数据库名称,只列出指定的数据库相关操作
   -o   --offset=#  忽略掉日志中的前n行命令
   -r   --result-file=name  将输出的文本格式日志输出到指定文件
   -s   --short-form  显示简单格式,忽略掉一些信息
   --start-datatime=date1  --stop-datetime=date2  指定日志剑阁内的所有日志
   --start-position=pos1 --stop-poistion=pos2  指定位置间隔内的所有日志

7.4mysqldump(备份和数据迁移)

mysqldump客户端工具用来备份数据库或在不同数据库之间进行数据迁移.备份内容包含创建表,及插入表的SQL语句

mysqldump [options] db_name [tables]
mysqldump [options] --database/-B db1[db2 db3...]
mysqldump [options] --all-databases/-A

连接选项
-u --user=name 指定用户名
-p --password[=name] 指定密码
-h --host=name 指定服务器IP或者域名
-p --port=# 指定连接端口
输出内容选项

//不指定表就是所有
mysqldump -uroot -pxx database_name table_name >xxx.sql
//创建表之前先删除
mysqldump -uroot -pxx database_name --add-drop-table >xxx.sql
//导出
mysqldump -uroot -pxx -T /temp database_name table_name

7.5mysqllimport(客户端数据导入)

客户端数据导入工具,用来导入mysqldump加 -T参数后导入的文本文件

mysqlimport [options] db_name textfile1 [textfile2]

mysqlimport -uroot -pxx test /temp/table_name.txt

导入的是sql文件

source /root/tb_name.sql

7.6mysqlshow(查看mysql概要信息)

客户端对象查找工具,用来很快的查找存在哪些数据库,数据库中的表,表中的列或者索引

mysqlshow [options] [db_name[table_name[col_name]]]

–count 显示数据库及表的统计信心(数据库,表 均可以不指定)
–i 显示指定数据库或者指定表的状态信息

//查询每个数据库的表的输了及记录的数量
mysqlshow -uroot -pxx --count
//查询test库中每个表中的字段数,及行数
mysqlshow -uroot -pxx test --count
//查询test库中Book表的详细情况
mysqlshow -uroot -pxx test book --count

8.MySql日志

8.1错误日志

记录了当Mysql启动和停止时,以及服务器在运行过程中发生任何严重错误时的相关日志.当数据库出现任何故障导致无法正常使用时,可以先手查看此日志
该日志是默认开启的,默认存放的目录为mysql的数据目录(/var/lib/mysql),默认的日志文件名为hostname.err(hostname是主机名)
查看日志位置指令

show variables like 'log_error%'

查看日志内容
tail -f /var/libmysql/xaxh-server.err

8.2二进制日志

二进制日志记录了所有DDL(数据定于语言)语句和DML(数据操纵语言)语句,但不包括数据查询语句.此日志对于灾难时的数据恢复起着极其重要的作用,MySQL的主从复制,就是通过该binlog实现的
二进制日志,默认情况下是没有开启的,需要到MySQL的配置文件中开启,并配置mysql日志的格式
配置文件位置/usr/my.cnf
日志存放位置:配置时,给定了文件名但没有指定路径,日志默认写入Mysql的数据目录

#配置开启binlog日志,日志的文件前缀为Mysqlbin----->生成的文件名入:mysqlbin.00001
log_bin=mysqlbin
#配置二进制日志的格式
binlog_format=STATEMENT

//根据需要格式配置
binlog_format=ROW

日志格式
Statement
每一条会修改数据的sql都会记录在binlog中。
优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。
缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同 的结果。另外mysql 的复制,像一些特定函数功能，slave可与master上要保持一致会有很多相关问题。
ps：相比row能节约多少性能与日志量，这个取决于应用的SQL情况，正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量，但是考虑到如果带条件的update操作，以及整表删除，alter表等操作，ROW格式会产生大量日志，因此在考虑是否使用ROW格式日志时应该跟据应用的实际情况，其所产生的日志量会增加多少，以及带来的IO性能问题。
Row
5.1.5版本的MySQL才开始支持row level的复制,它不记录sql语句上下文相关信息，仅保存哪条记录被修改。
优点： binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题.
缺点:所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容。
ps:新版本的MySQL中对row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录，如果sql语句确实就是update或者delete等修改数据的语句，那么还是会记录所有行的变更。
Mixed
从5.1.8版本开始，MySQL提供了Mixed格式，实际上就是Statement与Row的结合。
在Mixed模式下，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。

查看
mysqlbinlog log-file
mysqlbinlog mysqlbing.000001; //查看statement
mysqlbinlog -vv mysqlbing.000002;//查看row

日志删除
方式一:
通过Reset Master执行删除全部binlog日志,删除之后,日志编号,将会从xx.000001重新开始
方式二:
purge master logs to ‘mysqlbin.’,该命令将删除编号之前的所有日志
方式三
purge master logs before ‘yyyy-mm-dd hh24:mi:ss’ ,该命令将删除日志为’yyyy-mm-dd hh24:mi:ss’ 之前产生的所有日志
方式四
设置参数–expire_logs_days=# ,此参数的含义是设置日志的过期天数,过了指定天数后日志将会被自动删除

8.3查询日志

cat query_log.log

8.2慢查询日志

慢查询日志记录了所有执行时间超过参数long_query_time设置值并且扫描记录数不小于min_examined_row_limit的所有的SQL语句的日志,llong_query_time默认为10秒,最小为0,精度可以到微妙

默认关闭的,可以通过两个参数来控制慢查询日志

#该参数用来控制慢查询日志是否开启,可取值1和0,1代表开启,0代表关闭
show_query_log=1
#该参数用来指定慢查询日志的名称
show_query_log_file=slow_query.log
#该选项用来配置查询的时间限制,超过这个额时间将认为值为慢查询,需要进行日志记录,默认10s
long_query_time=10

读取
和错误日志,查询日志一样,慢查询日志记录的格式也是纯文本,可以被直接读取
1)查询long_query_time的值

show variables like 'long%';

cat slow_query.log
mysqldumpslow slow_query.log

8.MySql复制

复制是指将主数据DDL和DML操作通过二进制日志传到从库服务器中,然后在从库上对这些日志重新执行,从而使得从库和主库的数据保持同步
MYSQL支持一台主库同时向多台从库进行复制,从库同时也可以作为其他从服务器的主库,实现链状复制

优势

• 主库出现问题,可以快速切换到从库提供服务
• 可以在从库执行查询操作,从主库中更新,实现读写分离,降低主库的访问压力
• 可以在从库中执行备份,以避免备份期间影响主库的服务

搭建主从复制

1)先关闭防火墙
service iptables stop
2)配置
master /usr/my.cnf

#mysql 服务ID,爆炸整个集群环境中唯一
server-id=1

##mysql binlog 日志的存储路径和文件名
log-bin=/var/lib/mysql/mysqlbin


# 是否只读,1代表只读,0代表读写
read-only=0

#忽略的数据,指不需要同步的数据库
binlog-ignore-db=mysql

#指定同步的数据库
#binlog-do-db=db01

3)执行完后,重启mysql

service mysql restart;

4)创建同步数据的账户,并进行授权操作
前一个xx是用户名 后一个是密码

grant replication slave on *.* to 'xx'@192.168.xx.xx'identfied by 'xx';
flush privileges;

5)查看master状态

show master status;

slave
1)配置

#mysql服务器ID,唯一
server-id=2

#指定binlog日志
log-bin=/var/lib/mysql/mysqlbin

2)重启

service mysql restart

3)执行一下以下命令
指定当前从库对于的主库的IP地址,用户名,密码,从哪个日志文件开始的那个位置开始同步推送日志

change master to master_host='192.168.xx.xx',master_user='xx',master_password='xx',
master_log_file='mysqlbin.000001',master_log_pos=413;

4)开启同步操作

start slave;
show slave status;