- SQL优化
- SQL优化步骤
- 查看SQL执行频率
- 定位慢查询SQL
- 分析SQL执行计划
- 分析SQL执行耗费
- 分析SQL优化器执行计划
- SQL优化实例
- 批量插入大量数据优化
- 主键顺序插入
- 关闭唯一性校验
- 关闭自动提交事务
- INSERT语句优化
- ORDER BY 语句优化
- 两种排序方式
- ORDER BY优化方式
- filesort排序优化
- GROUP BY语句优化
- 子查询优化
- OR语句优化
- LIMIT语句优化
- SQL常用技巧
- SQL执行顺序
- SQL中的正则表达式
- MySQL常用函数
- MySQL项目优化
- 数据库连接池
- 减少数据库访问
- 避免重复查询数据库
- 增加缓存Cache层
- 负载均衡
- MySQL主从复制
- 分布式数据库架构
- MySQL查询缓存优化
- 查询缓存 *** 作流程
- 查询缓存配置参数
- 查询缓存的开启
- 查询缓存SELECT选项
- 查询缓存失效情况
- MySQL内存优化
- MySQL内存优化原则
- MyISAM存储引擎内存优化
- key_buffer_size
- read_buffer_size
- read_rnd_buffer_size
- InnoDB存储引擎内存优化
- innodb_buffer_pool_size
- innodb_log_buffer_size
- MySQL并发参数配置
- max_connections
- back_log
- thread_cache_size
- table_open_cache
- innodb_lock_wait_timeout
- MySQL中的锁
- 锁的基本概念
- 锁的分类
- MySQL中的锁
- MyISAM存储引擎的表锁
- InnoDB存储引擎的行锁
- 在应用开发初期,使用的数据库SQL语句注重的是功能上的实现.但是当应用系统正式上线之后,随着生产数据量的急剧增长 ,SQL语句就显示出性能问题,对生产的影响也越来越大,有可能成为整个系统性能的瓶颈,因此需要对SQL进行优化
- 对存在SQL性能问题的数据库进行优化时,需要尽快定位到问题SQL并解决问题
- 可以通过命令查看数据库服务器状态信息:
- 根据需要使用参数session或者global来显示当前连接会话session级或者全局数据库global级的统计结果
- 默认使用的参数是当前连接会话session级
show [session|global] status;
- 查看当前数据库中统计参数的值:
show global status like 'Com_______';
- 查看当前数据库中InnoDB存储引擎执行的数据记录统计结果:
show global status like 'Innodb_rows_%';
- Com_xxx表示 *** 作执行的次数,各个参数的含义如下:
| 参数 | 说明 |
|---|---|
| Com_select | 查询 *** 作SELECT执行的次数 一次查询增加1 |
| Com_insert | 插入 *** 作INSERT执行的次数 批量的INSERT *** 作也是增加1 |
| Com_update | 更新 *** 作UPDATE执行的次数 |
| Com_delete | 删除 *** 作DELETE执行的次数 |
| Innodb_rows_read | 查询 *** 作SELECT返回的行数 |
| Innodb_rows_inserted | 插入 *** 作INSERT执行的行数 |
| Innodb_rows_updated | 更新 *** 作UPDATE执行的行数 |
| Innodb_rows_deleted | 删除 *** 作DELETE执行的行数 |
| Connections | 尝试连接MySQL数据库服务器的次数 |
| Uptime | MySQL数据库服务器工作的时间 |
| Slow_queries | 慢查询的次数 |
- 慢查询日志:
- 可以通过慢查询日志定位到执行效率很低的SQL语句
- 使用以下命令选项启动时 ,mysqld会写出一个包含所有执行时间超过指定的long_query_time的SQL语句执行的日志文件:
--log-slow-queries[=file_name] - show processlist:
- 可以使用show processlist命令查看当前MySQL数据库正在执行的线程,包括执行的线程的状态,是否锁表等信息
- 可以实时查看SQL语句的执行情况,同时可以对锁表 *** 作进行优化
- 慢查询日志在查询的SQL语句执行结束后才会生成日志文件,所以在应用正在执行查询SQL语句出现查询效率问题时通过慢查询日志无法定位问题.此时需要使用到show processlist来实时查看查询SQL语句的执行情况
- show processlist中各个列表参数的含义如下:
| 参数 | 说明 |
|---|---|
| Id | 用户登录MySQL时,系统分配的connection_id 可以使用函数connection_id()查看当前用户的connection_id的值 |
| User | 当前用户名称 如果不是root用户,显示的SQL语句就是当前用户权限范围内的SQL语句 |
| Host | 当前语句来源的IP和端口 可以用来跟踪出现问题语句的登录用户 |
| db | 连接中的数据库 |
| Command | 当前连接执行的命令 通常值为休眠Sleep,查询Query,连接Connect |
| Time | 当前连接的SQL语句状态的持续时间 |
| State | 当前连接的SQL语句的状态,描述的是语句执行中的某一个状态 比如查询SQL语句的执行状态要经过Coping to tmp table, Sorting result, Sending data这几个语句执行状态才可以完成 |
| Info | 当前正在执行的SQL语句 判断问题SQL的重要依据 |
- explain:
- 定位到慢查询SQL后,可以通过EXPLAIN命令或DESC命令获取MySQL执行SQL语句的信息,包括SQL语句执行过程中表的连接方式和连接顺序
- 使用explain命令分析查询SQL语句中各个列表参数的含义如下:
| 参数 | 说明 |
|---|---|
| id | 查询SQL语句的序列号 一组数字,表示查询中执行查询SQL语句的子语句或者是 *** 作表的顺序 |
| select_type | 表示查询SQL的表的类型 通常值为简单表SIMPLE,不使用表连接或者子查询. 主查询PRIMARY,最外层的查询. 联合查询UNION,使用了UNION的第二个和后面的查询. 子查询SUBQUERY等 |
| table | 执行当前查询的表 |
| type | 连接的类型 性能由高到低的顺序为:system => const => eq_ref => ref => ref_or_null => index——merge => index_subquery => rang => index => ALL |
| possible_keys | 查询SQL可能使用的索引 |
| key | 查询SQL实际使用的索引 |
| key_len | 查询SQL使用的索引字段的长度 |
| ref | 查询SQL的引用 |
| rows | 扫描的行的数量 |
| Extra | 执行情况的补充说明和描述 |
- id: id字段是查询语句的序列号,是一组数字.表示查询中执行查询SQL语句的子语句或者是 *** 作表的顺序,有以下三种情况:
- id相同: 加载表的顺序是从上到下加载执行
- id不同: id的值越大,优先级越高,优先加载执行
- id有相同,也有不同: id相同的可以认为是一组,按照从上到下的顺序加载执行.在所有的组中,id的值越大,优先级越高,优先加载执行
- select_type: 查询的SQL的表的类型. 由高到低,性能逐渐降低
- SIMPLE: 简单的SELECT查询. 查询SQL中不包含子查询和UNION查询
- PRIMARY: 查询SQL中包含任何复杂的子查询和UNION查询时,最外层的查询就是PRIMARY类型
- SUBQUERY: 查询SQL中SELECT或者WHERE列表中包含的子查询
- DERIVED: 查询SQL中FROM列表中包含的子查询是DERIVED类型. MySQL数据库中会递归执行DERIVED类型的子查询,将查询结果放入临时表中
- UNION: 查询SQL中如果第二个SELECT出现在UNION后面,那么第二个SELECT的查询SQL就是UNION. 如果UNION包含在FROM子句的子查询中,外层的查询就是DERIVED
- UNION RESULT: 查询SQL的从UNION表中获取结果的SELECT查询语句
- table: 查询SQL执行的表
- type: 连接访问的类型. 由高到低,性能逐渐降低
- NULL: 查询SQL不访问任何表或者索引,直接返回结果
- system: 查询SQL的表中只有一行记录,通常是系统表,是const类型的一个特例.业务应用系统中一般不会出现
- const: 查询SQL通过索引一次就可以查找的结果记录,通常是使用主键PRIMARY KEY或者唯一索引UNIQUE INDEX. 因为只需要匹配一行数据,所以性能较高.比如将主键放在WHERE列表中,MySQL会将主键转换为一个常量,这时,const类型会将主键或者唯一索引的所有部分和常量值进行匹配,所以性能较高
- eq_ref: 使用唯一索引或者主键进行关联查询,查询出的结果记录只有一条. 通常用于主键或者唯一索引扫描
- ref: 非唯一性索引扫描,查询出的结果记录可能包含多条. 本质上也是一种索引访问
- range: 在给定的返回的行中查询结果记录,使用一个索引来选择行. 比如WHERE查询条件中的BETWEEN, < , > , IN等 *** 作
- index: 遍历索引树查询匹配的结果记录. index和ALL相比较在于index只是遍历索引树,通常比ALL性能较高,ALL遍历全表
- ALL: 遍历全表查询匹配的结果记录
- 完整的连接访问类型,性能由高到低 : NULL => system => const => eq_ref => ref => fulltext => ref_or_null => index_merge => unique_subquery => index_subquery => range => index => ALL
- SQL优化中需要保证查询SQL至少达到range级别,最好达到ref级别
- possible_keys: 查询SQL中可能使用到的索引,一个或多个
- key: 查询SQL中实际使用到的索引. 如果值为NULL,表示没有使用索引
- key_len: 查询SQL中使用的索引的字节数.这里表示的使用的索引的最大可能长度,不是实际使用长度.在不损失精确性的前提下,索引的长度越小查询效率越高
- rows: 查询SQL扫描数据库中表中数据的行数量
- Extra: 查询SQL中额外的执行计划的信息
- Using filesort: 文件排序.MySQL数据库对查询的数据使用一个外部的索引排序,不按照查询表内的索引的顺序进行排序. 查询效率较低,需要进行SQL优化
- Using temporary: MySQL数据库中使用临时表保存查询数据的中间结果,使用临时表对查询的数据进行排序,通常在order by和group by中使用. 查询效率较低,需要进行SQL优化
- Using index: 查询SQL语句中包含索引对应的字段,并且根据索引对应的字段对查询的数据进行排序.避免访问数据库表的行中的数据,查询效率较高
- show profile可以查询到SQL执行的耗费情况
- 系统变量have_profiling可以查看当前MySQL数据库服务器是否支持show profile *** 作:
select @@have_profiling;
mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES |
+------------------+
1 row in set, 1 warning (0.00 sec)
- 系统变量profiling可以查看当前MySQL数据库服务器是否开启show profile *** 作,默认是关闭的:
select @@profiling;
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
1 row in set, 1 warning (0.00 sec)
- 可以通过set语句开启用户会话session级别的profile:
set profiling=1;
- 使用show profiles命令可以查看SQL *** 作耗费的时间:
show profiles;
- 使用show profile for query query_id命令可以查看指定的SQL执行过程中各个线程的状态和耗费的时间:
show profile for query n;
- sending data: SQL的一个执行状态
- 表示MySQL线程开始访问数据行并且将结果返回给客户端的过程.不仅仅只是将数据发送给客户端的过程
- 在sending data状态下 ,MySQL线程通常需要做大量的磁盘读取IO *** 作,所以是整个查询耗费时间最长的
- 在获取到消耗时间的线程后 ,MySQL数据库服务器可以指定参数查看其余类型的耗费时间:
- all
- cpu
- block io
- context switch
- page faults
- 示例:
show profile cpu for query n;
- MySQL数据库服务器中提供对SQL的跟踪组件trace, 通过trace能够进一步分析SQL语句的执行计划
- trace跟踪组件的使用步骤:
- 启动trace: 打开trace,设置显示格式为json,并且指定trace使用的内存大小,避免使用trace分析SQL语句过程中由于默认内存过小导致分析内容无法完全展示
SET optimizer_trace="enable=on",end_markers_in_json=ON; SET optimizer_trace_max_mem_size=1024000;- 执行SQL
- 检查optimizer_trace文件:
SELECT * FROM information_schema.optimizer_trace\G;
- 使用load命令导入数据时,可以通过适当的设置提交数据导入的效率
load data local infile '/path/sql.log' into table 'table_name' fileds terminated by ',' lines terminated by '\n';
- InnoDB类型的表是按照主键进行顺序保存的
- 将导入的数据按照主键顺序排列,可以有效提高导入数据的效率
- 如果InnoDB的表没有主键,系统会自动默认创建一个内部列作为主键,如果可以给表创建一个主键,并且是有顺序的主键,就可以提高数据导入的效率
- 关闭唯一性校验,可以提高数据导入的效率
- 在导入数据前,关闭唯一性校验:
SET UNIQUE_CHECKS=0;- 在导入数据后,打开唯一性校验:
SET UNIQUE_CHECKS=1;
- 关闭事务的自动提交,可以提高数据导入的效率
- 在导入数据前,关闭事务的自动提交:
SET AUTOCOMMIT=0;- 在导入数据后,打开事务的自动提交:
SET AUTOCOMMIT=1;
- 如果需要同时对一张表插入很多行数据,应该采用多个值表的INSERT语句.这样可以大大减少客户端和数据库之间的连接,关闭的消耗,提高INSERT语句的插入效率
-- 原始方式
INSERT INTO oxford VALUES('1','Chova');
INSERT INTO oxford VALUES('2','Vea');
-- INSERT优化
INSERT INTO oxford VALUES('1','Chova'),('2','Vea');
- 手动开启事务,在事务中进行插入数据后提交
START TRANSACTION;
INSERT INTO oxford VALUES('1','Chova');
INSERT INTO oxford VALUES('2','Vea');
COMMIT;
- 数据按照主键顺序插入
-- 原始方式
INSERT INTO oxford VALUES('2','Vea');
INSERT INTO oxford VALUES('1','Chova');
-- INSERT优化
INSERT INTO oxford VALUES('1','Chova');
INSERT INTO oxford VALUES('2','Vea');
- Using filesort:
- 通过对返回数据进行排序,就是filesort排序
- 所有不是通过索引直接返回排序结果的排序都叫作filesort排序
- Using index:
- 通过有序索引顺序扫描直接返回有序数据,就是Using index排序
- 这种情况不需要额外的排序, *** 作效率高
- 尽量减少额外的排序,通过有序索引Using index直接返回有序结果
- WHERE条件和ORDER BY使用相同的索引,并且ORDER BY的字段的顺序和索引的字段顺序一致
- ORDER BY的字段要么都是升序,要么都是降序.否则会增加额外的排序 *** 作,出现filesort排序
- filesort排序优化思路:
- 通过创建合适的索引,减少filesort排序的出现
- 在某些一定会用到filesort排序的情况下,就要提高filesort排序的效率
- MySQL中filesort的两种排序算法:
- 两次扫描算法:
- 首先根据条件取出排序字段和行指针信息,然后在排序区sort buffer中进行排序,如果遇到sort buffer空间不够的情况,则在临时表temporary table中存储排序的结果.完成排序之后,再根据行指针回表读取记录
- 在MySQL 4.1之前,都是使用这种歌方式进行排序
- 两次扫描算法的排序 *** 作可能会导致大量的随机I/O *** 作
- 一次扫描算法:
- 一次性取出满足条件的所有字段,然后在排序区sort buffer中排序后输出结果集
- 一次扫描算法的排序 *** 作排序时内存开销较大,但是排序效率要比两次扫描算法的排序 *** 作的排序效率高
- 两次扫描算法:
- filesort排序优化:
- MySQL中会通过比较系统变量max_length_for_sort_data和QUERY语句取出字段的总大小,来判定使用哪一种排序算法:
- 如果max_length_for_sort_data的总大小比QUERY语句取出字段的总大小大,就使用一次扫描算法
- 如果QUERY语句取出字段的总大小比max_length_for_sort_data的总大小大,就使用两次扫描算法
- 通过适当增大系统变量max_length_for_sort_data和系统变量sort_buffer_size的值,来增大排序区的大小,提高排序的效率
-- 查询系统变量max_length_for_sort_data的值 show variable like 'max_length_for_sor_data'; -- 查询系统变量sort_buffer_size的值 show variable like 'sort_buffer_size'; - MySQL中会通过比较系统变量max_length_for_sort_data和QUERY语句取出字段的总大小,来判定使用哪一种排序算法:
- GROUP BY和ORDER BY语句一样,也会进行排序 *** 作 ,GROUP BY主要是多了排序之后的分组 *** 作
- 如果在分组的时候使用了聚合函数,那么还需要进行一些聚合函数的 *** 作
- GROUP BY语句的优化:
- 如果使用GROUP BY查询时,用户只是进行分组 *** 作,不需要对数据进行排序,可以在语句的最后使用ORDER BY NULL禁止排序来避免排序结果的性能消耗
- 使用GROUP BY语句查询时,可以和ORDER BY一样创建索引来提高性能
- MySQL 4.1之后,支持SQL的子查询:
- 可以使用SQL语句创建一个单列的查询结果,然后将这个结果作为过滤条件在另一个查询中使用
- 使用子查询可以一次性完成很多逻辑上需要很多步骤才能完成的SQL *** 作,同时可以避免事务或者表锁死的情况,而且很容易书写
- 子查询的优化: 为了提高查询性能,尽量使用连接JOIN查询代替子查询
-- 原始方式
SELECT * FROM user_info WHERE id IN (SELECT user_id FROM user_role);
-- 子查询优化
SELECT * FROM user_info u, user_role ur WHERE u.id= ur.user_id;
- OR语句中,只有OR关键词的每个条件都使用索引,整个语句才会利用到索引
- OR关键词多个条件组合成复合索引,整个语句不会利用到索引
- OR语句的优化: 为了提高查询性能,推荐使用UNION语句替换OR语句
-- 原始方式
SELECT * FROM user_info WHERE id = 66 OR id = 88;
-- OR语句优化
SELECT * FROM user_info WHERE id = 66 UNION SELECT * FROM user_info WHERE id = 88;
- 访问类型type效率由高到低:
system => const => eq_ref => ref => fulltext => ref_or_null => index_merge => unique_subquery => index_subquery => range => index => ALL
- 分页查询通常情况下,可以采用创建覆盖索引的方式来提高性能
- 但是对于大数据量时进行分页,需要首先获取前面排序的大量数据,却又只返回分页的数据,排序的大量数据丢弃,这种查询排序的性能损耗很大
- LIMIT语句的优化:
- 在索引的字段上完成分页 *** 作,最后根据主键关联从原表中查询其余的数据
SELECT * FROM user_info u, (SELECT id FROM user_info ORDER BY id LIMIT 200000,10) i WHERE u.id = i.id;- 对于主键自增且连续的表,可以使用对某个位置的查询替换LIMIT语句查询
SELECT * FROM user_info WHERE id > 200000 LIMIT 10;
- SQL语句的编写顺序 : SELECT DISTINCT -> FROM -> JOIN -> WHERE -> GROUP BY -> HAVING -> ORDER BY -> LIMIT
- SQL语句的执行顺序 : FROM -> ON -> JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT DISTINCT -> ORDER BY -> LIMIT
- 正则表达式: Regular Expression, 描述或者匹配一系列符合某个句法规则的字符串的单个字符串
- SQL语句可以和正则表达式联合使用:
SELECT * FROM table_name WHERE col_name REGEXP '正则表达式';
| 句法 | 说明 |
|---|---|
| ^ | 在字符串的开始处进行匹配 |
| $ | 在字符串的结尾处进行匹配 |
| . | 匹配任意单个字符串,包括换行符 |
| [ ] | 匹配括号中的任意字符 |
| x* | 匹配零个或者多个指定的字符,可以是空串 |
| x+ | 匹配零个或者多个指定的字符,不可以是空串 |
| x? | 匹配零个或者一个指定的字符 |
| x1|x2 | 匹配x1或x2 |
| x(m) | 匹配m个指定的字符 |
| x(m,) | 匹配至少m个字符 |
| x(m,n) | 匹配m个至n个字符 |
| x(,n) | 匹配0至n个字符 |
| ( ) | 将模式的元素组成单一元素 |
- 数字函数:
| 函数 | 说明 |
|---|---|
| ABS | 求绝对值 |
| SQRT | 求二次方根 |
| MOD | 求余数 |
| CEIL(CEILING) | 向上取整.返回不小于指定参数的最小整数 |
| FLOOR | 向下取整.返回不大于指定参数的最大整数,返回值转化为一个BIGINT |
| RAND | 生成一个0到1之间的随机数. 可以传入整数参数来产生重复序列 |
| ROUND | 对指定的整数四舍五入 |
| SIGN | 返回参数的符号 |
| POW(POWER) | 对指定的参数进行次方运算的结果值 |
| SIN | 求正弦值 |
| ASIN | 求反正弦值 |
| COS | 求余弦值 |
| ACOS | 求反余弦值 |
| TAN | 求正切值 |
| ATAN | 求反正切值 |
| COT | 求余切值 |
- 字符串函数:
| 函数 | 说明 |
|---|---|
| LENGTH | 求字符串的长度.返回字符串的字节长度 |
| CONCAT | 合并字符串.返回连续参数合并成的字符串,参数可以是一个或者多个 |
| INSERT | 替换字符串 |
| LOWER | 将字符串中字母转换成小写 |
| UPPER | 将字符串中的字母转换为大写 |
| LEFT | 从左侧截取字符串.返回字符串左边指定个数的字符 |
| RIGHT | 从右侧截取字符串.返回字符串右边指定个数的字符 |
| TRIM | 删除字符串中包含的空格 |
| REPLACE | 替换字符串.返回替换后的新字符串 |
| SUBSTRING | 截取字符串.返回指定位置开始的指定长度的字符串 |
| REVERSE | 字符串反转函数.返回和原字符串顺序相反的新字符串 |
- 日期函数:
| 函数 | 说明 |
|---|---|
| CURDATE(CURRENT_DATE) | 返回系统当前日期 |
| CURTIME(CURRTENT_TIME) | 返回系统当前时间 |
| NOW(SYSDATE) | 返回系统当前日期和时间 |
| MONTH | 获取指定日期中的月份 |
| MONTHNAME | 获取指定日期中的英文名称 |
| DAYNAME | 获取指定日期中对应的星期的英文名称 |
| DAYOFWEEK | 获取指定日期对应的一周的索引位置值 |
| WEEK | 获取指定日期在一年中的第几周.返回值范围为0到52或者1到53,取决于WEEK使用的模式 |
| DAYOFYEAR | 获取指定日期在一年中的第几天.返回值范围为1到366 |
| DAYOFMONTH | 获取指定日期在一个月中的第几天.返回值范围为1到31 |
| YEAR | 获取指定日期中的年份.返回值范围为1970到2069 |
| TIME_TO_SEC | 获取指定时间参数的秒数 |
| SEC_TO_TIME | 获取指定秒数的时间参数 |
| DATE_ADD(ADDDATE) | 向指定日期添加指定的日期间隔 |
| DATE_SUB(SUBDATE) | 向指定日期减去指定的日期间隔 |
| ADDTIME | 向指定的时间添加指定的时间间隔 |
| SUBTIME | 向指定的时间添加指定的时间间隔 |
| DATEDIFF | 获取参数1减去参数2的两个日期的时间间隔 |
| DATE_FORMAT | 格式化指定的日期.根据参数返回指定格式的值 |
| WEEKDAY | 获取指定日期在一周内对应的值 |
- 聚合函数:
| 函数 | 说明 |
|---|---|
| MAX | 获取查询列中的最大值 |
| MIN | 获取查询列中的最小值 |
| COUNT | 统计查询列的行数 |
| SUM | 获取查询列中指定列的总和 |
| AVG | 获取查询列中指定列的平均值 |
- 在对数据库进行了一系列优化后,在实际的生产环境中,由于数据库本身性能的局限性,必须还要对项目进行一些优化,来降低数据库的访问压力
- 对于访问数据库,建立连接的损耗很大,如果频繁的创建关闭数据库连接,会导致对数据库的访问性能很低
- 需要建立数据库连接池来提高数据库的访问性能
- 在实际业务中,要理清数据库的访问逻辑,如果一次连接能够获取到访问结果的,就要避免使用多次连接
- 这样可以大大减少对数据库无用的重复请求
- 可以在项目中增加缓存Cache层来减少对数据库的访问压力
- 示例:
- 将固定不变的基础数据从数据库中抽取出来以文本的方式存储到项目中
- 使用MyBatis框架或者Hibernate框架提供的一级缓存和二级缓存
- 使用缓存型数据库Redis等来缓存数据
- 负载均衡: 使用特定的均衡算法,将固定的负载量分布到不同的服务器上,降低单台服务器的负载,这样来达到优化的目的
- 通过MySQL主从复制,实现读写分离:
- 主节点进行增加,删除,修改的 *** 作
- 从节点进行查询的 *** 作
- 这样的MySQL主从复制可以降低单台服务器的读写压力
- 分布式数据库架构: 将数据分布在多台服务器之间,实现多台服务器之间的负载均衡,这样来提高访问效率
- 分布式数据库架构特点:
- 适合大数据量,负载高的情况
- 具有良好的拓展性和高可用性
- MySQL查询缓存:
- 开启MySQL的查询缓存后,如果后续执行完全相同的SQL语句,数据库服务器就直接从查询缓存中获取查询结果
- 开启MySQL的查询缓存后,如果数据发生修改,缓存就会失效,需要重新执行查询SQL后再缓存数据.所以MySQL查询缓存不适合修改频繁的数据库的表
- MySQL中SQL查询的 *** 作流程:
- 客户端发送一条查询SQL进入服务器
- 服务器首先检查缓存,如果命中缓存就直接返回存储在缓存中的结果.如果没有命中缓存就进入下一阶段的 *** 作
- 服务器端进行SQL解析,预处理,再由优化器生成对应的执行计划
- MySQL数据库服务器根据优化器生成的执行计划,调用存储引擎的API来执行SOL查询
- 最后将SQL查询结果缓存到查询缓存中并返回给客户端
- 下一次执行完全相同的SQL查询时,直接从查询缓存中获取查询结果返回给客户端
- 查看当前的MySQL数据库是否支持查询缓存:
show variables like 'have_query_cahce';
- 查询当前MySQL数据库是否开启了查询缓存:
show variables like 'query_cache_type';
- 查询当前MySQL数据库查询缓存的大小,以字节为单位:
show variables like 'query_cache_size';
# 查询缓存的大小为 1M
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| query_cache_size |1048576|
+------------------+-------+
- 查询当前MySQL数据库查询缓存的状态:
show status like 'Qcache%';
- Qcache_free_blocks : 查询缓存的可用内存块数
- Qcache_free_memory : 查询缓存的可用内存大小
- Qcache_hits : 查询缓存的命中数
- Qcache_inserts : 添加到查询缓存的查询数
- Qcache_lowmen_prunes : 内存不足时从查询缓存删除的查询数
- Qcache_not_cache : 没有设置查询缓存的查询数 .query_cache_type参数设置为OFF状态时进行的无法缓存或者没有缓存的查询的次数
- Qcache_queries_in_cahce : 注册到查询缓存的查询数
- Qcache_total_blocks : 查询缓存的内存块总数
- 数据库MySQL中的查询缓存默认是关闭的,需要手动配置query_cahce_type参数来开启查询缓存
- query_cache_type参数有以下三个取值:
- OFF, 0 : 查询缓存功能关闭
- ON, 1 : 查询缓存功能开启 .SELECT查询的结果符合查询缓存条件就会添加到查询缓存中,否则就不会添加到查询缓存.显式指定SQL_NO_CACHE时不会进行查询缓存
- DEMAND, 2 : 查询缓存按需执行.显式指定SQL_CACHE的SELECT查询语句才会添加到查询缓存,否则就不会添加到查询缓存
- 开启查询缓存: 在 /usr/my.cnf文件中,增加查询缓存配置.配置增加完成后,重启MySQL服务配置生效
# 开启MySQL查询缓存
query_cache_type=1
- SELECT选项: 可以在SELECT语句中指定查询缓存的选项
- SQL_CACHE: 查询结果是可缓存的,并且MySQL数据库的系统变量query_cache_type变量的值为ON或者DEMAND, 就缓存查询结果
- SQL_NO_CACHE: MySQL服务器不使用查询缓存,不检查缓存,也不检查结果是否缓存,也不缓存查询结果
SELECT SQL_CACHE id,name FROM user_table;
SELECT SQL_NO_CACHE id,name FROM user_table;
- 查询SQL语句不一致:
- SQL语句要想命中查询缓存,查询的SQL语句必须完全一致,包括大小写,都要完全一样
- 查询SQL语句使用了不确定值的函数:
- 查询SQL语句使用不确定值的函数后,就不会对查询结果进行缓存
- 不确定值函数包括:
- now()
- current_date()
- curdate()
- curtime()
- rand()
- uuid()
- user()
- database()
- 不使用任何表查询的语句,不会对查询结果进行缓存
- 查询数据库中mysql,information_schema和performance_schema表时,不会对查询结果进行缓存
- 存储函数,触发器和事件的主体中执行的查询语句,不会对查询结果进行缓存
- 如果数据库中的表发生更改变化时,那么所有使用这个表的查询缓存都会失效并且查询结果会从缓存中删除:
- 使用MERGE映射到已更改表的表的查询,不会对查询结果进行缓存
- 可以使数据库中的表发生更改的语句包括:
- INSERT
- UPDATE
- DELETE
- TRUNCATE TABLE
- ALTER TABLE
- DROP TABLE
- DROP DATABASE
- 在给 *** 作系统和其余的应用程序预留了足够内存的前提下,需要将尽量多的内存分配给MySQL做缓存
- 因为MyISAM存储引擎的数据文件读取依赖 *** 作系统自身的IO缓存,所以存在MyISAM类型的表,需要预留足够多的内存给 *** 作系统做IO缓存
- 排序区和连接区等缓存的默认值要根据最大连接数合理分配.因为排序区和连接区等缓存是专门分配给每个数据库会话Session连接使用的,如果设置太大,不仅会浪费资源,而且在并发连接较高时会导致物理内存完全耗尽
- MyISAM存储引擎使用key_buffer缓存索引块来加速MyISAM索引的读写速度
- MyISAM存储引擎对于表中的数据块没有特别的缓存机制,完全依靠 *** 作的系统的IO缓存对数据进行读写
- key_buffer_size:
- 配置MyISAM存储引擎中索引块缓存的大小
- 直接影响MyISAM表的存取效率
- 可以在MySQL数据库服务器的参数配置文件 /usr/my.cnf中配置key_buffer_size的值,推荐值的大小至少为 1 4 \frac{1}{4} 41可用内存
key_buffer_size=4048M
- read_buffer_size: 如果需要经常顺序扫描MyISAM存储引擎的表,可以通过增加read_buffer_size的值来提高性能
- 注意: read_buffer_size是每一个会话Session独占的,如果默认值设置过大,就会造成内存浪费
- read_rnd_buffer_size: 如果需要经常排序查询MyISAM存储引擎的表,比如存在ORDER BY子句的SQL, 可以通过增加read_rnd_buffer_size的值来提高性能
- 注意: read_rnd_buffer_size是每一个会话Session独占的,如果默认值设置过大,就会造成内存浪费
- InnoDB存储引擎使用内存区作为IO缓存池
- 这个IO缓存池不仅用来缓存InnoDB存储引擎的索引块,也用来缓存InnoDB存储引擎的数据块
- innodb_buffer_pool_size: 配置InnoDB存储引擎表数据和索引数据的最大缓存区大小
- 在保证 *** 作系统和其余的应用程序内存足够的情况下 ,innodb_buffer_pool_size的值越大,缓存命中率越高,这样访问InnoDB存储引擎的表的需要的磁盘IO就越少,性能越高
innodb_buffer_pool_size=4048M
- innodb_log_buffer_size: 配置InnoDB存储引擎重做日志缓存的大小
- 如果InnoDB存储引擎的表可能产生大量更新记录的大事务,可以通过增加innodb_log_buffer_size的大小,避免InnoDB存储引擎在事务提交前就执行不必要的日志写入磁盘的 *** 作
innodb_log_buffer_size=100M
- 数据库服务器MySQL是多线程结构实现的,包括:
- 后台线程
- 客户服务线程
- 数据库服务器MySQL的多线程可以有效利用服务器资源,提高数据库的并发性能
- 数据库服务器MySQL中控制并发连接和线程的主要参数包括:
- max_connections
- back_log
- thread_cache_size
- table_open_cache
- max_connections: 配置允许连接到MySQL数据库服务器的最大连接数量.默认值为151
- 增大max_connections值的情况:
- 状态变量connection_errors_max_connections的值不为0, 并且一直增长
- 这时说明不断有连接请求因为数据库连接数量已经达到MySQL数据库服务器允许的最大值导致连接请求失败.此时需要增大max_connections的值
- 配置max_connctions的值需要考虑以下因素:
- *** 作系统线程库的质量
- 系统内存大小
- 每个连接的负荷
- CPU的处理速度
- 期望的响应时间
- Linux中 ,MySQL数据库服务器最大连接数可以配置500至1000, 具体需要根据Linux服务器的性能决定
- back_log: 配置MySQL服务器监听TCP端口时积压的请求栈的大小.默认值在MySQL 5.6.6版本之前为50, 默认值在MySQL 5.6.6版本之后为最大连接数量max_connections的五分之一,最大不超过900
- 如果MySQL服务器的连接数达到最大连接数max_connections的时候,新来的连接请求就会保存到堆栈中,等待MySQL数据库服务器中的某一个连接释放资源,这个堆栈中允许的连接请求的最大数量就是back_log的值
- 如果等待的连接请求超过了back_log的值,就无法获取到MySQL数据库服务器的连接资源,将会报错
- 增大back_log的值的情况:
- MySQL数据库服务器需要在比较短的时间内处理大量的连接请求
- thread_cache_size: 配置MySQL数据库服务器缓存的客户服务线程的数量.默认值为9
- 为了提升数据库服务器的连接性能 ,MySQL数据库服务器会缓存一定数量的客户服务线程用于线程重用
- table_open_cache: 配置SQL语句执行线程可以打开的表缓存的数量.默认值为2000
- 配置table_open_cache的值需要根据以下情况来决定:
- 最大连接数max_connections的值
- 每个连接的SQL语句执行关联查询涉及的表的最大数量
- innodb_lock_wait_timeout: 配置InnoDB存储引擎中事务等待行锁的时间.默认值为50ms
- 配置innodb_lock_wait_timeout的值需要根据需要动态配置:
- 对于需要快速响应的业务系统,可以将值配置的小一点,这样可以避免事务长时间挂起
- 对于后台执行的批量处理程序,可以将值配置的大一点,这样可以避免发生比较大的事务回滚 *** 作
- 锁: 计算机协调多个进行或者线程并发访问某一资源的机制
- 在数据库中,除了传统的计算机资源,比如CPU,RAM,I/O等资源的争用以外,数据也是一种共享的资源
- 在数据库中,如何保证数据并发访问的一致性和有效性是数据库必须要解决的一个问题,锁冲突也成为影响数据库并发访问性能的一个重要因素
- 根据对数据库的 *** 作粒度:
- 表锁: 对数据库进行 *** 作时会锁定整个表
- 行锁: 对数据库进行 *** 作时会锁定当前 *** 作行
- 根据对数据库的数据的 *** 作类型:
- 读锁: 是一个共享锁.对于同一份数据,多个读 *** 作可以同时进行不会相互影响
- 写锁: 是一个排他锁.对于数据的写 *** 作没有完成时,会阻止其余的写锁和读锁
- MySQL中的锁机制简单,最大的特点就是不同的存储引擎支持不同的锁机制
| 存储引擎 | 表级锁 | 行级锁 | 页面锁 |
|---|---|---|---|
| MyISAM | 支持(默认) | 不支持 | 不支持 |
| InnoDB | 支持 | 支持(默认) | 不支持 |
| MEMORY | 支持 | 不支持 | 不支持 |
| BDB | 支持 | 不支持 | 支持 |
- MySQL中的锁的特点:
- 表级锁: 偏向于MyISAM存储引擎
- 优点:
- 开销小,加锁快
- 不会出现死锁
- 缺点:
- 锁的粒度大
- 锁冲突高
- 并发度低
- 优点:
- 行级锁: 偏向于InnoDB存储引擎
- 优点:
- 锁的粒度小
- 锁冲突低
- 并发度高
- 缺点:
- 开销大,加锁慢
- 会出现死锁
- 优点:
- 页面锁: 介于表级锁和行级锁之间
- 开销和加锁时间界于表级锁和行级锁之间
- 会出现死锁
- 锁的粒度界于表级锁和行级锁之间
- 并发度一般
- 表级锁: 偏向于MyISAM存储引擎
- MySQL中的各种锁有各自的特点,要根据具体的应用场景来选择需要使用的锁:
- 表级锁:
- 适用于以查询为主,只有少量按照索引条件更新数据的应用
- 比如Web应用等
- 行级锁:
- 适用于有大量按照索引条件并发更新少量不同数据,同时又有并发查询的应用
- 比如OLTP在线事务处理系统
- 表级锁:
- MySQL中的MyISAM存储引擎只支持表级锁
- MyISAM存储引擎加表锁的方式:
- MyISAM存储引擎在执行查询语句SELECT *** 作前,会自动给涉及的所有的表加上读锁的表级锁
- MyISAM存储引擎在执行更新语句UPDATE,DELETE,INSERT *** 作前,会自动给涉及的所有的表加上写锁的表级锁
- 一般情况下 ,MyISAM存储引擎中表级锁的加锁过程都是自动完成的,用户不需要直接使用LOCK TABLE命令为MyISAM存储引擎的表显式加锁
- 显式加锁的语法:
-- 读锁
LOCK TABLE table_name READ;
-- 写锁
LOCK TABLE table_name WRITE;
- 读锁不会阻塞读,但会阻塞写.写锁既会阻塞读,也会阻塞写:
- 对于MyISAM存储引擎的表的读 *** 作,不会阻塞其余用户对于同一张表的读请求,单数会阻塞对同一张表的写请求
- 对于MyISAM存储引擎的表的写 *** 作,会阻塞其余用户对于同一张表的读请求和写请求
- 在MyISAM存储引擎中的读写锁调度中是写锁优先:
- 这样导致写锁之后,其余线程无法进行任何 *** 作,这样大量的更新会使得查询很难获取到锁,造成永久阻塞
- 这个就是在以写为主的数据库中不能够使用MyISAM存储引擎的原因
- 可以使用命令查看MyISAM存储引擎中锁的争用情况:
SHOW OPEN TABLES;- In_use: 表示指定的表使用的次数.如果值为0就表示表是打开的,没有锁定处于使用中
- Name_locked: 表示指定的表的表名称是否被锁定.表的名称锁定用于取消或者对表进行重命名的相关 *** 作
SHOW STATUS LIKE 'Table_locks%';- Table_locks_immediate: 表示能够立即获得表级锁的次数.每次立即获得表级锁,值就加1
- Table_locks_waited: 表示不能立即获得表级锁时需要等待的次数,每等待一次,值就加1. 这个值越高说明锁争用的情况越严重
- InnoDB存储引擎的行锁的特点:
- 开销大,加锁慢
- 会出现死锁
- 锁的粒度小
- 锁冲突低
- 并发度高
- InnoDB存储引擎和MyISAM存储引擎比较两个最大的不同点:
- 支持事务
- 采用行级锁
- 事务: 事务是由一组SQL语句组成的逻辑处理单元
- 事务的4个ACID属性:
| 事务ACID属性 | 说明 |
|---|---|
| 原子性Atomicity | 事务是一个原子 *** 作单元.对数据的修改 *** 作,要么全部成功,要么全部失败 |
| 一致性Consistent | 事务开始和完成时,数据都保持一致状态 |
| 隔离性Isolation | 数据库提供一定的隔离机制,保证事务在不受外部并发 *** 作影响的独立环境中执行 |
| 持久性Durable | 事务完成后,对数据的修改是持久化的 |
- 并发事务造成的问题:
| 问题 | 说明 |
|---|---|
| 丢失更新Lost Update | 当两个或者多个事务同时选定同一行进行事务 *** 作时,后者事务修改的值会覆盖前者事务修改的值 |
| 脏读Dirty Reads | 当一个事务正在访问数据并对数据做出修改,在这个修改的事务没有提交到数据库中时,另一个事务也访问这个数据并且使用了这个数据 |
| 不可重复读Non-Repeatable Reads | 一个事务读取某些数据一段时间后,再次读取以前读过的数据,两次读取的数据不一致 |
| 幻读Phantom Reads | 一个事务按照相同的查询条件重新读取已经读取过的数据,发现其余的事务插入了满足相同查询条件的新数据 |
- 数据库提供一定的事务隔离机制来解决事务并发问题:
- 数据库的事务隔离越严格,并发产生的问题越少,但是会造成付出的代价越大
- 事务隔离实质上就是使用事务在一定程度上串行化执行,这种串行化和并发是存在矛盾的
- 事务隔离级别: 4个由低到高排序
- Read uncommitted : 脏写
- Read committed : 脏读
- Repeatable read : 不可重复读
- Serializable : 幻读
- MySQL中数据库的默认隔离级别是Repeatable read. 可以通过以下命令查看:
show variables like 'tx_isolation';
- InnoDB存储引擎中实现以下两种类型的行锁:
- 共享锁: 读锁,简称S锁.多个事务可以对同一数据共享一个锁,都能对数据进行访问,只能读不能修改
- 排他锁: 写锁,简称X锁.排他锁不能和其余的锁并存.一个事务获取一个数据行的排他锁,其余事务就无法获取这个数据行的锁,包括共享锁和排他锁.获取到排他锁的事务可以对数据进行读取和修改
- InnoDB存储引擎的默认加锁方式:
- 对于UPDATE,DELETE和INSERT语句 ,InnoDB存储引擎会为涉及到的数据集自动加上排他锁X
- 对于SELECT语句 ,InnoDB存储引擎不会进行加锁
- MySQL中可以通过命令为数据记录显式添加共享锁或者排他锁
-- 添加共享锁
SELECT * FROM table_name LOCK IN SHARE MODE;
-- 添加排他锁
SELECT * FROM table_name FOR UPDATE;
- 导致InnoDB存储引擎中的行锁升级为表锁的情况:
- 如果不使用索引对表进行 *** 作时,就会导致InnoDB存储引擎对表中的所有记录都加锁,实际效果和表锁一样
- 查看当前表的索引的命令:
SHOW INDEX FROM table_name;
- 间隙锁:
- 当 *** 作SQL语句不是使用相等条件,而是使用范围条件,并且请求共享锁或者排他锁时 ,InnoDB会对符合条件的已有数据进行加锁
- 对于键值在范围条件内但是并不存在的记录,叫作GAP间隙 ,InnoDB会对这个间隙进行加锁,这种锁机制就是Next-key间隙锁
- 可以使用更加精确的范围条件来避免间隙锁
- 使用以下命令可以查看InnoDB存储引擎中行锁的基本信息:
show status like 'innodb_row_lock%';
- Innodb_row_lock_current_waits : 当前正在等待锁的数量
- Innodb_row_lock_time : 系统启动到现在的总时间长度
- Innodb_row_lock_time_avg : 每次等待花费的平均时长
- Innodb_row_lock_time_max : 系统启动到现在等待最长的一次的时间长度
- Innodb_row_lock_waits : 系统启动到现在总共等待的次数
- 当InnoDB存储引擎中行锁等待的次数很高,并且每次等待的时长很长时,就需要分析系统中出现行锁等待多次的原因,根据分析结果设计优化
- 总结:
- InnoDB存储引擎使用行锁,虽然性能不如MyISAM存储引擎的表锁,但是在整体并发处理方面的能力要远远优于MyISAM的表锁.在系统并发量较高时 ,InnoDB存储引擎的整体性能比MyISAM存储引擎有明显的优势
- InnoDB存储引擎中的行锁如果使用不当,可能会导致InnoDB存储引擎的整体性能不如MyISAM存储引擎甚至更差
- InnoDB存储引擎行锁优化:
- 尽可能使用索引完成数据的查询 *** 作,避免无索引行锁升级为表锁
- 合理设计查询条件,尽可能缩小行锁的范围
- 尽可能缩小查询条件和查询范围,避免产生间隙锁
- 尽可能控制事务的大小,减少锁定的资源量和时间长度
- 尽可能使用低级别的事务隔离,需要保证业务满足需求
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）