高性能MySQL(第三版)
来源:互联网 发布:淘宝知识产权怎么处罚 编辑:程序博客网 时间:2024/04/30 01:02
一、MySQL架构与历史
1.2.2 锁粒度
开始事务:START TRANSACTION 提交事务:COMMIT 回滚事务:ROLLBACK
事务必须具备的特性:原子性、一致性、隔离性、持久性;
1.3.1 隔离级别
未提交读(READ UNCOMMITED):事务可以读取未提交的数据,也称脏读,这种会导致很多问题。
提交读(READ COMMITED):大部分的数据库默认都是提交读,但MySQL并不是;一个事务从开始到提交之前,所做的任何修改对 其他事务来说都是不可见的。也称不可重复读。
可重复读(REPEATABLE READ):该级别解决了脏读的问题,保证了在一个事务中多次读取同样的记录结果是一致的;它是MySQL
默认的事务隔离级别。InnoDB存储引擎可通过并发控制解决幻读的问题。
可串行化(SERIALIZABLE):该级别会在读取的每一行数据上都加锁,所以可能导致大量的超时和锁争用问题。实际很少用该
级别。
1.3.2 死锁
死锁是指两个或多个事务在同一资源上相互占用,并请求锁定对方占用的资源,从而导致恶性循环的现象。
当多个事务视图以不同的顺序锁定资源时,会产生死锁;多个事务同时锁定一个资源时,也会导致死锁。
InnoDB目前处理死锁的方法:将持有最少行级排他锁的事务进行回滚;锁的顺序和行为是和存储引擎相关的。
1.3.4 MySQL中的事务
MySQL提供了2种事务型的存储引擎:InnoDB和NDB cluster.
MySQL默认采用自动提交(AUTOCOMMIT)模式。
MySQL>SHOW VARIABLES LIKE 'AUTOCOMMIT';
MySQL>SET AUTOCOMMIT=1;
1或ON表示启用,0或OFF表示禁用。
对于修改非事务型的表(MYISAM和内存表)的AUTOCOMMIT是不会有任何影响的;因为他们没有AUTOCOMMIT和ROLLBACK的概念,
就是说他们是一直启动AUTOCOMMIT的。
有一些命令会在执行之前强制COMMIT提交当前活动的事务,例如:ALTER TABLE、LOCK TABLES等。
MySQL设置事务隔离级别:SET TRANSACTION ISOLATION LEVEL,例如:
MySQL>SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
MySQL服务层不管理事务,事务是由存储引擎实现的;
【显式锁定】
常用语句:SELECT ... LOCK IN SHARE MODE
SELECT ... FOR UPDATE
说明:尽量少用LOCK TABLES。
1.5 MySQL的存储引擎
查询显示表的相关信息:SHOW TABLE STATUS
例如:查看user的信息,MySQL>SHOW TABLE STATUS like 'user'\G
1.5.1 InnoDB存储引擎
MySQL5.5版本后默认以InnoDB为默认存储引擎;
1.5.2 MYISAM存储引擎
MYISAM对表进行加锁,而不是针对行;
CHECK TABLE mytable 检查表是否有错误;
REPAIR TABLE mytable 修复表错误;
MYISAM支持全文索引;
延迟更新索引键(Delayed Key Write):设置该选项后, 在每次修改执行完成时,不会立即将修改的索引数据写入硬盘,而是
会写到内存中的键缓冲区。只有在清理键缓冲区或关闭表的时候才会将对应的索引块写入到硬盘中。
MYISAM压缩表:如果一张表在创建并导入数据以后就再也不需要更改了,这时这个表就能进行压缩。压缩表是不允许修改的,
除非在解压缩之后;支持索引,但索引是只读的;
myisampack:压缩表的命令
1.5.3其他的一些存储引擎
archive、Blackhole、CSV、Federated、Memory、Merge、NDB集群引擎
【Memory存储引擎】
Memory应用的场景:
(1)用于查找或者映射表,例如将邮编和州名映射的表;
(2)用于缓存周期性聚合数据的结果;
(3)用于保存数据分析中产生的中间数据;
Memory支持hash索引,索引查找操作非常快;
Memory表是表级锁,因此并发写入的性能较低。不支持BLOB或TEXT类型的列,并且每行的长度是固定的;即使指定了VARCHAR列,
但在存储时还会转换成CHAR,导致内存浪费。
1.5.6转换表的引擎
方法1:使用ALTER TABLE语句 例如: mysql>ALTER TABLE mytable ENGINE=InnoDB;
缺点:首先需要执行很长时间。mysql会按行将数据从原表复制到一张新表中,复制期间可能会消耗系统中所有的I/O能力,
同时原表上会被加上读锁。
说明:如果转换表的存储引擎,那么将失去原引擎相关的所有特性。
方法2:导入与导出
先使用mysqldump工具将数据导出到文件,然后修改文件中CREATE TABLE语句的存储引擎选项,注意修改表名,同一个数据库中
不能存在相同的表名。另外,mysqldump默认会自动在CREATE TABLE语句上加上DROP TABLE,需要特别注意。
方法3: 创建与查询
该方法综合了前两种方法,即先创建一个新的存储引擎的表,然后利用INSERT...SELECT语句来导数据。
mysql>CREATE TABLE innodb_table like myisam_table;
mysql>ALTER TABLE innodb_table ENGINE=InnoDB;
mysql>INSERT INTO innodb_table SELECT * FROM myisam_table;
如果数据量大的话,可以做分批处理,针对每一段数据执行事务提交操作,以避免大事务产生过多的undo.假设有主键id
字段, 重复执行以下语句将数据导入到新表:
mysql>START TRANSACTION;
mysql>INSERT INTO innodb_table SELECT * FROM myisam_table
->WHERE id BETWEEN x AND y;
mysql>COMMIT;
三、服务器性能剖析
3.3.1剖析服务器负载
捕获mysql的查询到日志文件中,可以通过设置long_query_time为0来捕获所有的查询,而且查询的响应时间单位可以做到微秒级。
建议使用慢查询捕获服务器上的所有查询,并进行分析。
从慢查询日志中生成剖析报告的工具:pt_query_digest.
3.3.2剖析单条查询
目前有三种方法:SHOW STAUTS、SHOW PROFILE、检查慢查询日志的条目。
【SHOW PROFILE】
该命令默认是禁用的,开启该命令:mysql>SET profiling=1;
【SHOW STATUS】
该命令返回一些计数器。
【SHOW GLOBAL STATUS】
该命令是每秒执行一次SHOW GLOBAL STATUS来捕获数据。
【SHOW PROCESSLIST】
该方法主要是通过不停的捕获SHOW PROCESSLIST的输出,来观察是否有大量线程处于不正常的状态或者有其他不正常的特征。
【使用慢查询日志】
如果不能通过设置慢查询日志记录所有的查询,可以通过tcpdump和pt_query_digest来替代。
3.5.2使用strace
strace可以调查系统调用的情况。
四、Schema与数据类型优化
1.2.2 锁粒度
- 表锁:写锁的优先级高于读锁;写锁的请求可以插入到读锁的前面,但读锁的请求却不能插入到写锁的前面;
- 行级锁:行级锁只在存储引擎层实现,在服务器层没有实现;
开始事务:START TRANSACTION 提交事务:COMMIT 回滚事务:ROLLBACK
事务必须具备的特性:原子性、一致性、隔离性、持久性;
1.3.1 隔离级别
未提交读(READ UNCOMMITED):事务可以读取未提交的数据,也称脏读,这种会导致很多问题。
提交读(READ COMMITED):大部分的数据库默认都是提交读,但MySQL并不是;一个事务从开始到提交之前,所做的任何修改对 其他事务来说都是不可见的。也称不可重复读。
可重复读(REPEATABLE READ):该级别解决了脏读的问题,保证了在一个事务中多次读取同样的记录结果是一致的;它是MySQL
默认的事务隔离级别。InnoDB存储引擎可通过并发控制解决幻读的问题。
可串行化(SERIALIZABLE):该级别会在读取的每一行数据上都加锁,所以可能导致大量的超时和锁争用问题。实际很少用该
级别。
1.3.2 死锁
死锁是指两个或多个事务在同一资源上相互占用,并请求锁定对方占用的资源,从而导致恶性循环的现象。
当多个事务视图以不同的顺序锁定资源时,会产生死锁;多个事务同时锁定一个资源时,也会导致死锁。
InnoDB目前处理死锁的方法:将持有最少行级排他锁的事务进行回滚;锁的顺序和行为是和存储引擎相关的。
1.3.4 MySQL中的事务
MySQL提供了2种事务型的存储引擎:InnoDB和NDB cluster.
MySQL默认采用自动提交(AUTOCOMMIT)模式。
MySQL>SHOW VARIABLES LIKE 'AUTOCOMMIT';
MySQL>SET AUTOCOMMIT=1;
1或ON表示启用,0或OFF表示禁用。
对于修改非事务型的表(MYISAM和内存表)的AUTOCOMMIT是不会有任何影响的;因为他们没有AUTOCOMMIT和ROLLBACK的概念,
就是说他们是一直启动AUTOCOMMIT的。
有一些命令会在执行之前强制COMMIT提交当前活动的事务,例如:ALTER TABLE、LOCK TABLES等。
MySQL设置事务隔离级别:SET TRANSACTION ISOLATION LEVEL,例如:
MySQL>SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
MySQL服务层不管理事务,事务是由存储引擎实现的;
【显式锁定】
常用语句:SELECT ... LOCK IN SHARE MODE
SELECT ... FOR UPDATE
说明:尽量少用LOCK TABLES。
1.5 MySQL的存储引擎
查询显示表的相关信息:SHOW TABLE STATUS
例如:查看user的信息,MySQL>SHOW TABLE STATUS like 'user'\G
1.5.1 InnoDB存储引擎
MySQL5.5版本后默认以InnoDB为默认存储引擎;
1.5.2 MYISAM存储引擎
MYISAM对表进行加锁,而不是针对行;
CHECK TABLE mytable 检查表是否有错误;
REPAIR TABLE mytable 修复表错误;
MYISAM支持全文索引;
延迟更新索引键(Delayed Key Write):设置该选项后, 在每次修改执行完成时,不会立即将修改的索引数据写入硬盘,而是
会写到内存中的键缓冲区。只有在清理键缓冲区或关闭表的时候才会将对应的索引块写入到硬盘中。
MYISAM压缩表:如果一张表在创建并导入数据以后就再也不需要更改了,这时这个表就能进行压缩。压缩表是不允许修改的,
除非在解压缩之后;支持索引,但索引是只读的;
myisampack:压缩表的命令
1.5.3其他的一些存储引擎
archive、Blackhole、CSV、Federated、Memory、Merge、NDB集群引擎
【Memory存储引擎】
Memory应用的场景:
(1)用于查找或者映射表,例如将邮编和州名映射的表;
(2)用于缓存周期性聚合数据的结果;
(3)用于保存数据分析中产生的中间数据;
Memory支持hash索引,索引查找操作非常快;
Memory表是表级锁,因此并发写入的性能较低。不支持BLOB或TEXT类型的列,并且每行的长度是固定的;即使指定了VARCHAR列,
但在存储时还会转换成CHAR,导致内存浪费。
1.5.6转换表的引擎
方法1:使用ALTER TABLE语句 例如: mysql>ALTER TABLE mytable ENGINE=InnoDB;
缺点:首先需要执行很长时间。mysql会按行将数据从原表复制到一张新表中,复制期间可能会消耗系统中所有的I/O能力,
同时原表上会被加上读锁。
说明:如果转换表的存储引擎,那么将失去原引擎相关的所有特性。
方法2:导入与导出
先使用mysqldump工具将数据导出到文件,然后修改文件中CREATE TABLE语句的存储引擎选项,注意修改表名,同一个数据库中
不能存在相同的表名。另外,mysqldump默认会自动在CREATE TABLE语句上加上DROP TABLE,需要特别注意。
方法3: 创建与查询
该方法综合了前两种方法,即先创建一个新的存储引擎的表,然后利用INSERT...SELECT语句来导数据。
mysql>CREATE TABLE innodb_table like myisam_table;
mysql>ALTER TABLE innodb_table ENGINE=InnoDB;
mysql>INSERT INTO innodb_table SELECT * FROM myisam_table;
如果数据量大的话,可以做分批处理,针对每一段数据执行事务提交操作,以避免大事务产生过多的undo.假设有主键id
字段, 重复执行以下语句将数据导入到新表:
mysql>START TRANSACTION;
mysql>INSERT INTO innodb_table SELECT * FROM myisam_table
->WHERE id BETWEEN x AND y;
mysql>COMMIT;
三、服务器性能剖析
3.3.1剖析服务器负载
捕获mysql的查询到日志文件中,可以通过设置long_query_time为0来捕获所有的查询,而且查询的响应时间单位可以做到微秒级。
建议使用慢查询捕获服务器上的所有查询,并进行分析。
从慢查询日志中生成剖析报告的工具:pt_query_digest.
3.3.2剖析单条查询
目前有三种方法:SHOW STAUTS、SHOW PROFILE、检查慢查询日志的条目。
【SHOW PROFILE】
该命令默认是禁用的,开启该命令:mysql>SET profiling=1;
【SHOW STATUS】
该命令返回一些计数器。
【SHOW GLOBAL STATUS】
该命令是每秒执行一次SHOW GLOBAL STATUS来捕获数据。
【SHOW PROCESSLIST】
该方法主要是通过不停的捕获SHOW PROCESSLIST的输出,来观察是否有大量线程处于不正常的状态或者有其他不正常的特征。
【使用慢查询日志】
如果不能通过设置慢查询日志记录所有的查询,可以通过tcpdump和pt_query_digest来替代。
3.5.2使用strace
strace可以调查系统调用的情况。
四、Schema与数据类型优化
0
上一篇:Mysql数据库优化总结
下一篇:Memcached全面剖析
相关热门文章
- python 多进程之管道实例(模...
- MySQL数据插入、修改、删除...
- 解决mysql“Access denied for...
- mysql启动的四种方式
- PostgreSQL安装详细步骤(wind...
- linux dhcp peizhi roc
- 关于Unix文件的软链接
- 求教这个命令什么意思,我是新...
- sed -e "/grep/d" 是什么意思...
- 谁能够帮我解决LINUX 2.6 10...
给主人留下些什么吧!~~
评论热议
0 0
- 高性能MySQL(第三版)
- 高性能mysql第三版读书笔记
- 高性能mysql第三版 免费下载
- 高性能Mysql第三版(第五章创建高性能索引)
- 高性能Mysql第三版(第六章查询性能优化)
- 【学习笔记】高性能MySQL(第三版)——第2章:MySQL基准测试
- 【笔记】高性能MySQL(第三版)——第1章:MySQL架构与历史
- 【笔记】高性能MySQL(第三版)——第2章:MySQL基准测试
- 【笔记】高性能MySQL(第三版)——第4章:MySQL基准测试
- 高性能mysql第三版基础知识和基本优化总结
- 【学习笔记】高性能MySQL(第三版)——第1章:MySQL架构与历史
- 《高性能mysql》之性能分析(第三章)
- 高性能MySQL(第二版)第三章 架构优化和索引(上)——读书笔记
- 高性能MySQL(第二版)第三章 架构优化和索引(上)——读书笔记
- 高性能MYSQL(复制)
- 创建高性能索引(高性能mysql读书笔记二)
- 【高性能MySQL】读书摘录2-第三章、服务器性能剖析
- 《高性能MySQL(第3版)》之切分查询
- MySQL优化
- Mysql数据库优化总结
- Android--合并2个Xml
- jenkins 配置安全策略admin没有Overall/Read权限
- linux目录结构详细介绍 【转】
- 高性能MySQL(第三版)
- Java线程:线程的同步-同步方法
- Memcached全面剖析
- hdu 1849 Rabbit and Grass(s-nim博弈)
- iptables防火墙简介
- android 友盟推送SDK和支付宝支付SDK冲突
- 8.旋转数组的最小数
- 深入浅出Oracle:DBA入门、进阶与诊断案例(读书笔记1)
- 通过WriteProcessMemory改写进程的内存
原创粉丝点击
热门IT博客
热门问题
老师的惩罚
人脸识别
我在镇武司摸鱼那些年
重生之率土为王
我在大康的咸鱼生活
盘龙之生命进化
天生仙种
凡人之先天五行
春回大明朝
姑娘不必设防,我是瞎子
山野春惰陈壮800
色惰片
欧美激惰
教不严师之惰
山野春惰陈壮雪梅免费阅读
欧美激惰免费观看
避其锐气击其惰归
波多野野结衣之办公室激惰
惰轮轴
惰轮是什么
惰轮的作用
隳惰
隳
想要
想的
想,,
想
方想龙城
徐锦江想退出一路成年
想的意思
想是什么意思
想的拼音
想我
小坏蛋你想弄死阿姨啊
小坏蛋你想弄死阿姨
叶非夜好想住你隔壁
好想住你隔壁叶非夜
想住你隔壁叶非夜
想你
我想和你好好
我想你
很想很想你
我想要你信息素
韩剧想你全集
你想
想你韩剧
韩剧想你
想你韩剧下载
想你 韩剧
想你的三百六十五天
全世界只想你来爱我