SegmentFault 落棋不悔最新的文章
2017-11-29T14:18:11+08:00
https://segmentfault.com/feeds/blogs
https://creativecommons.org/licenses/by-nc-nd/4.0/
Redis 数据类型及应用场景
https://segmentfault.com/a/1190000012212663
2017-11-29T14:18:11+08:00
2017-11-29T14:18:11+08:00
waterandair
https://segmentfault.com/u/waterandair
53
<h3>一、 redis 特点</h3>
<ul>
<li>所有数据存储在内存中,高速读写</li>
<li>提供丰富多样的数据类型:string、 hash、 set、 sorted set、bitmap、hyperloglog</li>
<li>提供了 AOF 和 RDB 两种数据的持久化保存方式,保证了 Redis 重启后数据不丢失</li>
<li>Redis 的所有操作都是原子性的,还支持对几个操作合并后的原子性操作,支持事务</li>
</ul>
<p> 通常我们都把数据存到关系型数据库中,但为了提升应用的性能,我们应该把访频率高且不会经常变动的数据缓存到内存中。。Redis 没有像 MySQL 这类关系型数据库那样强大的查询功能,需要考虑如何把关系型数据库中的数据,合理的对应到缓存的 key-value 数据结构中。</p>
<h3>二、 设计 Redis Key</h3>
<h4>分段设计法</h4>
<p> 使用冒号把 key 中要表达的多种含义分开表示,步骤如下:</p>
<ol>
<li>把表名转化为 key 前缀</li>
<li>主键名(或其他常用于搜索的字段)</li>
<li>主键值</li>
<li>要存储的字段。</li>
</ol>
<p>eg. 用户表(user)</p>
<table>
<thead><tr>
<th>id</th>
<th>name</th>
<th>email</th>
</tr></thead>
<tbody>
<tr>
<td>1</td>
<td>zj</td>
<td>156577812@qq.com</td>
</tr>
<tr>
<td>2</td>
<td>ai</td>
<td>156577813@qq.com</td>
</tr>
</tbody>
</table>
<p>这个简单的表可能经常会有这个的需求:>根据用户 id 查询用户邮箱地址,可以选择把邮箱地址这个数据存到 redis 中:</p>
<pre><code>set user:id:1:email 156577812@qq.com;
set user:id:2:email 156577812@qq.com;</code></pre>
<h3>三、 String数据类型的应用场景</h3>
<h4>1. 简介</h4>
<p> string 类型是 Redis 中最基本的数据类型,最常用的数据类型,甚至被很多玩家当成 redis 唯一的数据类型去使用。string 类型在 redis 中是二进制安全(binary safe)的,这意味着 string 值关心二进制的字符串,不关心具体格式,你可以用它存储 json 格式或 JPEG 图片格式的字符串。 <br> </p>
<h4>2. 数据模型</h4>
<p> string 类型是基本的 Key-Value 结构,Key 是某个数据在 Redis 中的唯一标识,Value 是具体的数据。</p>
<table>
<thead><tr>
<th>Key</th>
<th>Value</th>
</tr></thead>
<tbody>
<tr>
<td>‘name’</td>
<td>‘redis’</td>
</tr>
<tr>
<td>‘type’</td>
<td>‘string’</td>
</tr>
</tbody>
</table>
<h4>3. 应用场景</h4>
<h5>(1) 存储 MySQL 中某个字段的值</h5>
<p>把 key 设计为 表名:主键名:主键值:字段名 <br>eg.</p>
<pre><code>set user:id:1:email 156577812@qq.com</code></pre>
<h5>(2) 存储对象</h5>
<p>string 类型支持任何格式的字符串,应用最多的就是存储 json 或其他对象格式化的字符串。(这种场景下推荐使用 hash 数据类型)</p>
<pre><code>set user:id:1 '[{"id":1,"name":"zj","email":"156577812@qq.com"},{"id":1,"name":"zj","email":"156577812@qq.com"}]'</code></pre>
<h5>(3) 生成自增 id</h5>
<p>当 redis 的 string 类型的值为整数形式时,redis 可以把它当做是整数一样进行自增(incr)自减(decr)操作。由于 redis 所有的操作都是原子性的,所以<code>不必担心多客户端连接时可能出现的事务</code>问题。</p>
<h3>四、hash 数据类型的应用场景</h3>
<h4>1. 简介</h4>
<p> hash 类型很像一个关系型数据库的数据表,hash 的 Key 是一个唯一值,Value 部分是一个 hashmap 的结构。<br> </p>
<h4>2. 数据模型</h4>
<p> 假设有一张数据库表如下:</p>
<table>
<thead><tr>
<th>id</th>
<th>name</th>
<th>type</th>
</tr></thead>
<tbody><tr>
<td>1</td>
<td>redis</td>
<td>hash</td>
</tr></tbody>
</table>
<p> 如果要用 redis 的 hash 结构存储,数据模型如下: </p>
<p><img src="/img/bVVQ6K?w=403&h=191" alt="clipboard.png" title="clipboard.png"></p>
<p> hash数据类型在存储上述类型的数据时具有比 string 类型更灵活、更快的优势,具体的说,使用 string 类型存储,必然需要转换和解析 json 格式的字符串,即便不需要转换,在内存开销方面,还是 hash 占优势。 <br> </p>
<h4>3. 应用场景</h4>
<p>hash 类型十分适合存储对象类数据,相对于在 string 中介绍的把对象转化为 json 字符串存储,hash 的结构可以任意添加或删除‘字段名’,更加高效灵活。</p>
<pre><code>hmset user:1 name zj email 156577812@qq.com</code></pre>
<h3>五、list 数据类型的应用场景</h3>
<h4>1. 简介</h4>
<p> list 是按照插入顺序排序的字符串链表,可以在头部和尾部插入新的元素(双向链表实现,两端添加元素的时间复杂度为 O(1))。插入元素时,如果 key 不存在,redis 会为该 key 创建一个新的链表,如果链表中所有的元素都被移除,该 key 也会从 redis 中移除。</p>
<h4>2. 数据模型</h4>
<p><img src="/img/bVVRaU?w=483&h=159" alt="clipboard.png" title="clipboard.png"></p>
<p> 常见操作时用 lpush 命令在 list 头部插入元素, 用 rpop 命令在 list 尾取出数据。 <br> </p>
<h4>3. 应用场景</h4>
<h5>(1) 消息队列</h5>
<p> redis 的 list 数据类型对于大部分使用者来说,是实现队列服务的最经济,最简单的方式。<br> </p>
<h5>(2) “最新内容”</h5>
<p>因为 list 结构的数据查询两端附近的数据性能非常好,所以适合一些需要获取最新数据的场景,比如新闻类应用的 “最近新闻”。<br> </p>
<h4>4.优化建议</h4>
<h5>(1) list 是链表结构,所有如果在头部和尾部插入数据,性能会非常高,不受链表长度的影响;但如果在链表中插入数据,性能就会越来越差。</h5>
<h3>六、set 数据类型的应用场景</h3>
<h4>1. 简介</h4>
<p> set 数据类型是一个集合(没有排序,不重复),可以对 set 类型的数据进行添加、删除、判断是否存在等操作(时间复杂度是 O(1) ) <br> set 集合不允许数据重复,如果添加的数据在 set 中已经存在,将只保留一份。 <br> set 类型提供了多个 set 之间的聚合运算,如求交集、并集、补集,这些操作在 redis 内部完成,效率很高。</p>
<h4>2. 数据模型</h4>
<p><img src="/img/bVVRdw?w=385&h=274" alt="clipboard.png" title="clipboard.png"></p>
<h4>3. 应用场景</h4>
<p> set 类型的特点是——不重复且无序的一组数据,并且具有丰富的计算功能,在一些特定的场景中可以高效的解决一般关系型数据库不方便做的工作。</p>
<h5>1. “共同好友列表”</h5>
<p> 社交类应用中,获取两个人或多个人的共同好友,两个人或多个人共同关注的微博这样类似的功能,用 MySQL 的话操作很复杂,可以把每个人的好友 id 存到集合中,获取共同好友的操作就可以简单到一个取交集的命令就搞定。</p>
<pre><code>// 这里为了方便阅读,把 id 替换成姓名
sadd user:wade james melo paul kobe
sadd user:james wade melo paul kobe
sadd user:paul wade james melo kobe
sadd user:melo wade james paul kobe
// 获取 wade 和 james 的共同好友
sinter user:wade user:james
/* 输出:
* 1) "kobe"
* 2) "paul"
* 3) "melo"
*/
// 获取香蕉四兄弟的共同好友
sinter user:wade user:james user:paul user:melo
/* 输出:
* 1) "kobe"
*/
/*
类似的需求还有很多 , 必须把每个标签下的文章 id 存到集合中,可以很容易的求出几个不同标签下的共同文章;
把每个人的爱好存到集合中,可以很容易的求出几个人的共同爱好。
*/</code></pre>
<h3>七、sorted set 数据类型的应用场景</h3>
<h4>1.简介</h4>
<p> 在 set 的基础上给集合中每个元素关联了一个分数,往有序集合中插入数据时会自动根据这个分数排序。</p>
<h4>2.应用场景</h4>
<p> 在集合类型的场景上加入排序就是有序集合的应用场景了。比如根据好友的“亲密度”排序显示好友列表。</p>
<pre><code>// 用元素的分数(score)表示与好友的亲密度
zadd user:kobe 80 james 90 wade 85 melo 90 paul
// 根据“亲密度”给好友排序
zrevrange user:kobe 0 -1
/**
* 输出:
* 1) "wade"
* 2) "paul"
* 3) "melo"
* 4) "james"
*/
// 增加好友的亲密度
zincrby user:kobe 15 james
// 再次根据“亲密度”给好友排序
zrevrange user:kobe 0 -1
/**
* 输出:
* 1) "james"
* 2) "wade"
* 3) "paul"
* 2) "melo"
*/
//类似的需求还出现在根据文章的阅读量或点赞量对文章列表排序</code></pre>
MySQL 常见问题和应用技巧
https://segmentfault.com/a/1190000012079413
2017-11-20T14:16:16+08:00
2017-11-20T14:16:16+08:00
waterandair
https://segmentfault.com/u/waterandair
2
<h3>一、 忘记 MySQL 的 root 密码</h3>
<h4>1. 登录到数据库所在的服务器,手工 kill 掉 mysql 进程。</h4>
<h5>(1) 登录到数据库所在的服务器,手工 kill 掉 MySQL 进程:</h5>
<pre><code>root@bogon:/data/mysql# kill `cat ./mysql.pid`</code></pre>
<p>其中,mysql.pid 指的是 MySQL 数据目录下的 pid 文件,它记录了 MySQL 服务的进程号。</p>
<h5>(2) 使用 --skip-grant-tables 选项重启 MySQL 服务:</h5>
<pre><code>zj@bogon:/data/mysql$ sudo /usr/local/mysql/bin/mysqld --skip-grant-tables --user=root &</code></pre>
<p>--skip-grant-tables 选项意思是启动 MySQL 服务时跳过权限表认证。启动后,连接到 MySQL 的 root 将不需要口令。</p>
<h5>(3) 用空密码的 root 用户连接到 mysql ,并且更改 root 口令:</h5>
<pre><code>zj@bogon:/usr/local/mysql/bin$ mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.18-log Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> set password = password('123456');
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
MySQL [(none)]> use mysql
Database changed
MySQL [mysql]> update user set authentication_string=password('123456') where user="root" and host="localhost";
Query OK, 1 row affected, 1 warning (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 1
MySQL [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MySQL [mysql]> exit;
Bye
****************************************************************
zj@bogon:/usr/local/mysql/bin$ mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.18-log Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]>
</code></pre>
<p>由于使用了 --skip-grant-tables 选项启动,使用 “set password” 命令更改密码失败,直接更新 user 表的 authentication_string(测试版本为5.7.18,有的版本密码字段是 ‘password’) 字段后,更改密码成功。刷新权限表,使权限认证重新生效。重新用 root 登录时,就可以使用刚刚修改后的口令了。</p>
<h3>二、如何处理 myisam 存储引擎的表损坏</h3>
<p>有的时候可能会遇到 myisam 表损坏的情况。一张损坏的表的症状通常是查询意外中断,并且能看到下述错误:</p>
<ul>
<li><p>'table_name.frm' 被锁定不能更改</p></li>
<li><p>不能找到文件 'tbl_name.MYYI' (errcode:nnn)</p></li>
<li><p>文件意外结束</p></li>
<li><p>记录文件被毁坏</p></li>
<li><p>从表处理器得到错误 nnn。</p></li>
</ul>
<p>通常有以下两种解决方法:</p>
<h4>1. 使用 myisamchk 工具</h4>
<p>使用 MySQL 自带的 myisamchk 工具进行修复:</p>
<pre><code>shell> myisamchk -r tablename</code></pre>
<p>其中 -r 参数的含义是 recover,上面的方法几乎能解决所有问题,如果不行,则使用命令:</p>
<pre><code>shell> mysiamchk -o tablename</code></pre>
<p>其中 -o 参数的含义是 --safe-recover,可以进行更安全的修复。</p>
<h4>2. 使用 sql 命令</h4>
<p>使用 MySQL 的 check table 和 repair table 命令一起进行修复,check table 用来检查表是否有损坏;repair table 用来对坏表进行修复。</p>
<h3>三、 数据目录磁盘空间不足的问题</h3>
<p>系统上线后,随着数据量的不断增加,会发现数据目录下的可用空间越来越小,从而给应用造成了安全隐患。</p>
<h4>1. 对于 myisam 存储引擎的表</h4>
<p>对于 myisam 存储引擎的表,在建表时可以用如下选项分别制定数据目录和索引目录存储到不同的磁盘空间,而默认会同时放在数据目录下:</p>
<pre><code>data directory = 'absolute path to directory'
index directory = 'absolute path to directory'</code></pre>
<p>如果表已经创建,只能先停机或者将表锁定,防止表的更改,然后将表的数据文件和索引文件 mv 到磁盘充足的分区上,然后在原文件处创建符号链接即可。</p>
<h4>2. 对于 innodb 存储引擎的表</h4>
<p>因为数据文件和索引文件是存放在一起的,所以无法将它们分离。当磁盘空间出现不足时,可以增加一个新的数据文件,这个文件放在充足空间的磁盘上。 <br>具体实现方法是在参数 innodb_data_file_path 中增加此文件,路径写为新磁盘的绝对路径。 <br>例如,如果 /home 下空间不足,希望在 /home1 下新增加一个可自动扩充数据的文件,那么参数可以这么写:</p>
<pre><code>innodb_data_file_path = /home/ibdata1:2000M;/home1/ibdata2:2000M:autoextend</code></pre>
<p>参数修改后,必须重启数据库才可以生效。</p>
<h3>四、DNS反向解析的问题 (5.0 以后的版本默认跳过域名逆向解析)</h3>
<p>在客户端执行 show processlist 命令,有时会出现很多进程,类似于:</p>
<pre><code>unauthenticated user | 192.168.10.10:55644 | null | connect | null | login | null </code></pre>
<p>这些进程会累计的越来越多,并且不会消失,应用无法正常相应,导致系统瘫痪。 </p>
<p>MySQL 在默认情况下对于远程连接过来的 IP 地址会进行域名的逆向解析,如果系统的 hosts 文件中没有与之对应的域名,MySQL 就会将此连接认为是无效用户,所以下进程中出现 unauthenticated user 并导致进程阻塞。 </p>
<p>解决的方法很简单,在启动时加上 --skip-name-resolve 选项,则 MySQL 就可以跳过域名解析过程,避免上述问题。</p>
<h3>五、mysql.sock 丢失后如何连接数据库</h3>
<p>在 MySQL 服务器本机上连接数据库时,经常会出现 mysql.sock 不存在,导致无法连接的问题。这是因为如果指定 localhost 作为一个主机名,则 mysqladmin 默认使用 Unix 套接字文件连接,而不是 tcp/ip。而这个套接字文件(一般命名为 mysql.sock)经常会因为各种原因而被删除。通过 --protocol=TCP|SOCKET|PIPE|MEMORY 选项,用户可以显式地指定连接协议,下面演示使用了 Unix 套接字失败后使用 tcp 协议连接成功的例子。</p>
<h4>1. Unix 套接字连接:</h4>
<pre><code>zj@bogon:~$ mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
</code></pre>
<h4>2. tcp 连接</h4>
<pre><code>zj@bogon:~$ mysql --protocol=TCP</code></pre>
MySQL 备份和恢复机制
https://segmentfault.com/a/1190000011980376
2017-11-13T11:04:48+08:00
2017-11-13T11:04:48+08:00
waterandair
https://segmentfault.com/u/waterandair
17
<h3>一、 备份恢复策略</h3>
<p>进行备份或恢复操作时需要考虑一些因素:</p>
<ul>
<li><p>确定要备份的表的存储引擎是事务型还是非事务型,两种不同的存储引擎备份方式在处理数据一致性方面是不太一样的。</p></li>
<li><p>确定使用全备份还是增量备份。全备份的优点是备份保持最新备份,恢复的时候可以花费更少的时间;缺点是如果数据量大,将会花费很多的时间,并对系统造成较长时间的压力。增量备份相反,只需要备份每天的增量日志,备份时间少,对负载压力也小;缺点就是恢复的时候需要全备份加上次备份到故障前的所有日志,恢复时间长一些。</p></li>
<li><p>可以考虑采用复制的方法来做异地备份,但不能代替备份,它对数据库的误操作也无能为力。</p></li>
<li><p>要定期做备份,备份的周期要充分考虑系统可以承受的恢复时间。备份要在系统负载较小的时候进行</p></li>
<li><p>确保 MySQL 打开 log-bin 选项,有了 binlog,MySQL 才可以在必要的时候做完整恢复,或基于时间点的恢复,或基于位置的恢复。</p></li>
<li><p>经常做备份恢复测试,确保备份时有效的,是可以恢复的。</p></li>
</ul>
<h3>二、 逻辑备份和恢复</h3>
<p>在 MySQL 中,逻辑备份的最大优点是对于各种存储引擎都可以用同样的方法来备份;而物理备份则不同,不同的存储引擎有着不同的备份方法,因此,对于不同存储引擎混合的数据库,逻辑备份会简单一点。</p>
<h4>1. 备份</h4>
<p>MySQL 中的逻辑备份是将数据库中的数据备份为一个文本文件,备份的文件可以被查看和编辑。在 MySQL 中,可以使用 mysqldump 工具来完成逻辑备份:</p>
<pre><code>// 备份指定的数据库或者数据库中的某些表
shell> mysqldump [options] db_name [tables]
// 备份指定的一个或多个数据库
shell> mysqldump [options] --database DB1 [DB2,DB3...]
// 备份所有数据库
shell> mysqldump [options] --all-database</code></pre>
<p>如果没有指定数据库中的任何表,默认导出所有数据库中的所有表。</p>
<h5>示例:</h5>
<h5>1. 备份所有数据库:</h5>
<pre><code>shell>mysqldump -uroot -p --all-database > all.sql</code></pre>
<h5>2. 备份数据库 test</h5>
<pre><code>shell>mysqldump -uroot -p test > test.sql</code></pre>
<h5>3. 备份数据库 test 下的表 emp</h5>
<pre><code>shell> mysqldump -uroot -p test emp > emp.sql</code></pre>
<h5>4. 备份数据库 test 下的表 emp 和 dept</h5>
<pre><code>shell> mysqldump -uroot -p test emp dept > emp_dept.sql </code></pre>
<h5>5. 备份数据库test 下的所有表为逗号分割的文本,备份到 /tmp:</h5>
<pre><code>shell> mysqlddump -uroot -p -T /tmp test emp --fields-terminated-by ','
shell> more emp.txt
1,z1
2,z2
3,z3
4,z4</code></pre>
<p><strong> 注意: </strong> 为了保证数据备份的一致性,<code>myisam 存储引擎在备份时需要加上 -l 参数</code>,表示将所有表加上读锁,在备份期间,所有表将只能读而不能进行数据更新。但是对于事务存储引擎来说,可以采用更好的选项 --single-transaction,此选项使得 innodb 存储引擎得到一个快照(snapshot),使得备份的数据能够保证一致性。</p>
<h4>2. 完全恢复</h4>
<p>mysqldump 的恢复也很简单,将备份作为输入执行即可:</p>
<pre><code>mysql -uroot -p db_name < backfile</code></pre>
<p><strong>注意</strong>,将备份恢复后数据并不完整,还需要将备份后执行的日志进行重做:</p>
<pre><code>mysqlbinlog binlog-file | mysql -uroot -p</code></pre>
<h5>完整的 mysqldump 备份与恢复示例:</h5>
<h5>1. 凌晨 2:00,备份数据库:</h5>
<pre><code>root@bogon:/usr/local/mysql/bin$ ./mysqldump -uroot -p -l -F t2 > t2.dmp
Enter password: </code></pre>
<p>其中 <code>-l 参数表示给所有表加读锁,-F 表示生成一个新的日志文件</code>,此时,t2 中 emp 表的数据如下:</p>
<pre><code># 为了便于测试,执行 reset master 删除所有 binlog。
MySQL [(none)]> reset master;
Query OK, 0 rows affected (0.00 sec)
# 此时只有一个 binlog 日志文件 mysql-bin.000001
MySQL [t2]> select * from test;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | b |
+------+------+
2 rows in set (0.00 sec)</code></pre>
<h5>2. 备份完毕后,插入新的数据:</h5>
<pre><code># 因为上一步执行是加入了 -F 选项, 所以接下来的操作会被记录到新的二进制文件,即名为 mysql-bin.000002 的文件
MySQL [t2]> insert into test values (3,'c');
Query OK, 1 row affected (0.00 sec)
MySQL [t2]> insert into test values (4,'d');
Query OK, 1 row affected (0.00 sec)</code></pre>
<h5>3. 数据库突然故障(其实是小伙伴没事儿删库练手玩儿),数据无法访问。需要恢复备份:</h5>
<p>删库跑路:</p>
<pre><code># 这里为了便于测试,不把删库操作记入日志,当前 session 设置 sql_log_bin 为 off。
# 删库后,执行 flush logs,让后续的 binlog 到新的文件中,即名为 mysql-bin.000003中
MySQL [t2]> set sql_log_bin = 0;
Query OK, 0 rows affected (0.00 sec)
MySQL [t2]> show variables like "%sql_log_bin%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin | OFF |
+---------------+-------+
1 row in set (0.00 sec)
MySQL [t2]> drop database t2;
Query OK, 1 row affected (0.01 sec)
MySQL [(none)]> flush logs;
Query OK, 0 rows affected (0.22 sec)
MySQL [t2]> drop database t2;
Query OK, 3 rows affected (0.23 sec)
MySQL [(none)]> exit;
Bye</code></pre>
<p>数据恢复:</p>
<pre><code>root@bogon:/usr/local/mysql/bin# ./mysql -e "create database t2"
root@bogon:/usr/local/mysql/bin# ./mysql t2 < t2.dmp
*******************************************************************
MySQL [t2]> select * from test;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | b |
+------+------+
2 rows in set (0.00 sec)
</code></pre>
<h5>4. 使用 mysqlbinlog 恢复自 mysqldump 备份以来的 binglog</h5>
<p>根据前面操作的内容,可知从备份的时间点到删库的时间点之间的操作被记录到了 mysql-bin.000002 文件中</p>
<pre><code>root@bogon:/usr/local/mysql/bin# ./mysqlbinlog --no-defaults /data/mysql/mysql-bin.000002 | ./mysql t2
*******************************************************
MySQL [t2]> select * from test;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
+------+------+
4 rows in set (0.00 sec)
</code></pre>
<p>至此,数据恢复成功。</p>
<h4>3. 基于时间点恢复</h4>
<p>由于误操作,比如误删除了一张表,这时使用完全恢复时没有用的,因为日志里面还存在误操作的语句,我们需要的是恢复到误操作之前的状态,然后跳过误操作语句,再恢复后面执行的语句,完成恢复。这种恢复叫不完全恢复,在 MySQL 中,不完全恢复分为 基于时间点的恢复和基于位置的恢复。 <br>基于时间点恢复的操作步骤:</p>
<h5>(1) 如果是上午 10 点发生了误操作,可以用以下语句用备份和 binlog 将数据恢复到故障前:</h5>
<pre><code>shell>mysqlbinlog --stop-date="2017-09-30 9:59:59" /data/mysql/mysql-bin.123456 | mysql -uroot -ppassword</code></pre>
<h5>(2) 跳过故障时的时间点,继续执行后面的 binlog,完成恢复。</h5>
<pre><code>shell>mysqlbinlog --start-date="2017-09-30 10:01:00" /data/mysql/mysql-bin.123456 | mysql -uroot -ppassword</code></pre>
<h4>4. 基于位置恢复</h4>
<p>和基于时间点的恢复类似,但是更精确,因为同一个时间点可能有很多条 sql 语句同时执行。恢复的操作步骤如下:</p>
<h5>(1) 在 shell 下执行命令:</h5>
<pre><code>shell>mysqlbinlog --start-date="2017-09-30 9:59:59" --stop-date="2017-09-30 10:01:00" /data/mysql/mysql-bin.123456 > /tmp/mysql_restore.sql</code></pre>
<p>该命令将在 /tmp 目录创建小的文本文件,编辑此文件,知道出错语句前后的位置号,例如前后位置号分别为 368312 和 368315。</p>
<h5>(2) 恢复了以前的备份文件后,应从命令行输入下面的内容:</h5>
<pre><code>shell>mysqlbinlog --stop-position="368312" /data/mysql/mysql-bin.123456 | mysql -uroot -ppassword
shell>mysqlbinlog --start-position="368315" /data/mysql/mysql-bin.123456 | mysql -uroot -ppassword </code></pre>
<p>上面的第一行将恢复到停止位置为止的所有事务。下一行将恢复从给定的起始位置直到二进制日志结束的所有事务。因为 mysqlbinlog 的输出包括每个 sql 语句记录之前的 set timestamp 语句,因此恢复的数据和相关的 mysql 日志将反应事务执行的<code>原时间。</code></p>
<h3>三、物理备份和恢复</h3>
<p>物理备份又分为冷备份和热备份两种,和逻辑备份相比,它的最大优点是备份和恢复的速度更快,因为物理备份的原理都是基于文件的 cp。</p>
<h4>1. 冷备份</h4>
<p>冷备份其实就是停掉数据库服务,cp 数据文件的方法。(基本不考虑这种方法)</p>
<h4>2. 热备份</h4>
<p>在 MySQL 中,对于不同的存储引擎热备份的方法也有所不同。</p>
<h5>(1) myisam 存储引擎</h5>
<p>myisam 存储引擎的热备份有很多方法,本质其实就是将要备份的表加读锁,然后再 cp 数据文件到备份目录。常用的有以下两种方法:</p>
<ul><li><p>使用 mysqlhotcopy 工具</p></li></ul>
<pre><code>// mysqlhotcopy 是 MySQL 的一个自带的热备份工具
shell> mysqlhotcopy db_name [/path/to/new_directory]</code></pre>
<ul><li><p>手工锁表 copy</p></li></ul>
<pre><code>// 在 mysqlhotcopy 使用不正常的情况下,可以用手工来做热备份
mysql>flush tables for read;
cp 数据文件到备份目录即可,</code></pre>
<h5>(2) innodb 存储引擎(另写)</h5>
<p>使用第三方工具 ibbackup、xtrabackup、innobacupex</p>
<h3>四、 表的导入导出</h3>
<p>在数据库的日常维护中,表的导入导出时很频繁的一类操作。</p>
<h4>1. 导出</h4>
<p>在某些情况下,为了一些特定的目的,经常需要将表里的数据导出为某些符号分割的纯数据文本,而不是 sql 语句:</p>
<ul>
<li><p>用来作为 Excel 显示;</p></li>
<li><p>单纯为了节省备份空间;</p></li>
<li><p>为了快速的加载数据,load data 的加载速度比普通 sql 加载要快 20 倍以上。</p></li>
</ul>
<h5>使用 select ...into outfile ... 命令来导出数据,具体语法如下:</h5>
<pre><code>mysql> select * from tablename into outfile 'target_file' [option];</code></pre>
<p>其中 option 参数可以是以下选项:</p>
<pre><code>fields terminated by 'string' // 字段分隔符,默认为制表符'\t'
fields [optionally] enclosed by 'char' // 字段引用符,如果加 optionally 选项则只用在 char、varchar 和 text 等字符型字段上,默认不使用引用符
fields escaped by ‘char’ // 转移字符、默认为 '\'
lines starting by 'string' // 每行前都加此字符串,默认''
lines terminated by 'string' // 行结束符,默认为'\n'
# char 表示此符号只能是单个字符,string表示可以是字符串。</code></pre>
<p>例如,将 test 表中数据导出为数据文本,其中,字段分隔符为“,”,字段引用符为“"”,记录结束符为回车符:</p>
<pre><code>MySQL [t2]> select * from test into outfile '/data/mysql/outfile.txt' fields terminated by "," enclosed by '"';
Query OK, 4 rows affected (0.02 sec)
</code></pre>
<pre><code>zj@bogon:/data/mysql$ more outfile.txt
"1","a","helloworld"
"2","b","helloworld"
"3","c","helloworld"
"4","d","helloworld"</code></pre>
<p>发现第一列是数值型,如果不希望字段两边用引号引起,则语句改为:</p>
<pre><code>MySQL [t2]> select * from test into outfile '/data/mysql/outfile2.txt' fields terminated by "," optionally enclosed by '"';
Query OK, 4 rows affected (0.03 sec)
zj@bogon:/data/mysql$ more outfile2.txt
1,"a","helloworld"
2,"b","helloworld"
3,"c","helloworld"
4,"d","helloworld"</code></pre>
<p>测试转义字符,MySQL 导出数据中需要转义的字符主要包括以下 3 类:</p>
<ul>
<li><p>转义字符本身</p></li>
<li><p>字段分隔符</p></li>
<li><p>记录分隔符</p></li>
</ul>
<pre><code>MySQL [t2]> update test set content = '\\"##!aa' where id=1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MySQL [t2]> select * from test into outfile '/data/mysql/outfile3.txt' fields terminated by "," optionally enclosed by '"';
Query OK, 4 rows affected (0.03 sec)
*******************************************
zj@bogon:/data/mysql$ more outfile3.txt
1,"a","\\\"##!aa"
2,"b","helloworld"
3,"c","helloworld"
4,"d","helloworld"</code></pre>
<ul>
<li><p>当导出命令中包含字段引用符时,数据中含有转义字符本身和字段引用符的字符需要被转义;</p></li>
<li><p>当导出命令中不包含字段引用符时,数据中含有转义字符本身和字段分割符的字符需要被转义。</p></li>
</ul>
<p><strong> 注意: </strong> select ... into outfile ... 产生的输出文件如果在目标目录下有重名文件,将不会被创建成功,源文件不会被自动覆盖。</p>
<h5>使用 mysqldump 导出数据为文本的具体语法如下:</h5>
<p>mysqldump -u username -T target_dir dbname tablename [option] </p>
<p>其中,option 参数可以是以下选项:</p>
<ul>
<li><p>--fields-terminated-by=name (字段分隔符);</p></li>
<li><p>--fields-enclosed-by=name (字段引用符);</p></li>
<li><p>--fields-optionally-enclosed-by=name (字段引用符,只用在 char、varchar 和 test 等字符型字段上);</p></li>
<li><p>--fields-escaped-by=name (转义字符);</p></li>
<li><p>--lines-terminated-by=name (记录结束符);</p></li>
</ul>
<p>例子:</p>
<pre><code>root@bogon:/usr/local/mysql/bin# ./mysqldump -uroot -p -T /data/mysql/dump t2 test --fields-terminated-by ',' --fields-optionally-enclosed-by '"'
**************** test.txt **********************
zj@bogon:/data/mysql/dump$ more test.txt
1,"a","\\\"##!aa"
2,"b","helloworld"
3,"c","helloworld"
4,"d","helloworld"
***************** test.sql *********************
zj@bogon:/data/mysql/dump$ more test.sql
-- MySQL dump 10.13 Distrib 5.7.18, for Linux (x86_64)
--
-- Host: localhost Database: t2
-- ------------------------------------------------------
-- Server version 5.7.18-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `test`
--
DROP TABLE IF EXISTS `test`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test` (
`id` int(11) DEFAULT NULL,
`name` varchar(10) DEFAULT NULL,
`content` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2017-09-25 11:14:06</code></pre>
<p>可以发现,除多了一个表的创建脚本文件,mysqldump 和 select ... into outfile ... 的选项和语法非常相似。其实 mysqldump 实际调用的就是后者提供的接口,并在其上面添加了一些新的功能而已。</p>
<h4>2. 导入 (导入用 select ... into outfile 或者 mysqldump 导出的纯数据文本)</h4>
<p>和导出类似,导入也有两种不同的方法,分别是 load data infile... 和 mysqlimport,它们的本质是一样的,区别只是在于一个在 MySQL 内部执行,另一个在 MySQL 外部执行。</p>
<h5>使用 “load data infile...” 命令,具体语法如下</h5>
<pre><code>mysql> load data [local]infile 'filename' into table tablename [option]
</code></pre>
<p>option 可以是以下选项:</p>
<ul>
<li><p>fields terminated by 'string' (字段分割符,默认为制表符't');</p></li>
<li><p>fields [optionally] enclosed by 'char' (字段引用符,如果加 optionally 选项则只用在 char varchar text 等字符型字段上。默认不使用引用符);</p></li>
<li><p>fields escaped by 'char' (转义字符,默认为'')</p></li>
<li><p>lines starting by 'string' (每行前都加此字符串,默认为'')</p></li>
<li><p>lines terminated by 'string' (行结束符,默认为'n')</p></li>
<li><p>ignore number lines (忽略输入文件中的前几行数据)</p></li>
<li><p>(col_name_or_user_var,...) (按照列出的字段顺序和字段数量加载数据);</p></li>
<li><p>set col_name = expr,...将列做一定的数值转换后再加载。</p></li>
</ul>
<p>fields 、lines 和前面 select...into outfile...的含义完全相同,不同的是多了几个不同的选项,下面的例子将文件'test.txt'中的数据加载到表 test 中:</p>
<pre><code>// 清空表 test
MySQL [t2]> truncate table test;
Query OK, 0 rows affected (0.07 sec)
MySQL [t2]> load data infile '/data/mysql/outfile.txt' into table test fields terminated by ',' enclosed by '"';
Query OK, 4 rows affected (0.10 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0
MySQL [t2]> select * from test;
+------+------+------------+
| id | name | content |
+------+------+------------+
| 1 | a | helloworld |
| 2 | b | helloworld |
| 3 | c | helloworld |
| 4 | d | helloworld |
+------+------+------------+
4 rows in set (0.00 sec)</code></pre>
<p>如果不希望加载文件中的前两行,可以进行如下操作:</p>
<pre><code>MySQL [t2]> truncate table test;
Query OK, 0 rows affected (0.02 sec)
MySQL [t2]> load data infile '/data/mysql/outfile.txt' into table test fields terminated by ',' enclosed by '"' ignore 2 lines;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
MySQL [t2]> select * from test;
+------+------+------------+
| id | name | content |
+------+------+------------+
| 3 | c | helloworld |
| 4 | d | helloworld |
+------+------+------------+
2 rows in set (0.02 sec)</code></pre>
<h4>使用 mysqldump 实现</h4>
<p>语法:</p>
<pre><code>shell> mysqlimport -uroot -p [--local] dbname order_tab.txt [option] </code></pre>
<p>其中,option 参数可以是以下选项:</p>
<ul>
<li><p>--fields-terminated-by=name (字段分隔符)</p></li>
<li><p>--fields-enclosed-by=name (字段引用符)</p></li>
<li><p>--fields-optionally-enclosed-by=name (字段引用符,只用在 char、varchar、text等字符型字段上)</p></li>
<li><p>--fields-escaped-by=name (转义字符)</p></li>
<li><p>--lines-terminated-by=name (记录结束符)</p></li>
<li><p>--ignore-lines=number (忽略前几行)</p></li>
</ul>
<p><strong> 注意: </strong> <br>如果导入和导出时跨平台操作的(windows 和 linux),那么要注意设置参数 line-terminated-by,windows 上设置为 line-terminated-by='rn', linux 上设置为 line-terminated-by='n'。</p>
MySQL 权限管理
https://segmentfault.com/a/1190000011780451
2017-10-30T16:53:54+08:00
2017-10-30T16:53:54+08:00
waterandair
https://segmentfault.com/u/waterandair
6
<p>MySQL 的权限表在数据库启动的时候就载入内存,当用户通过身份认证后,就在内存中进行相应权限的存取,这样,此用户就可以在数据库中做权限范围内的各种操作了。</p>
<h3>一、权限表的存取</h3>
<p>在权限存取的两个过程中,系统会用到 “mysql” 数据库(安装 MySQL 时被创建,数据库名称叫“mysql”) 中 user、host 和 db 这3个最重要的权限表。 </p>
<p>在这 3 个表中,最重要的表示 user 表,其次是 db 表,host 表在大多数情况下并不使用。 </p>
<p>user 中的列主要分为 4 个部分:用户列、权限列、安全列和资源控制列。 </p>
<p>通常用的最多的是用户列和权限列,其中权限列又分为普通权限和管理权限。普通权限用于数据库的操作,比如 <code>select_priv</code>、<code>super_priv</code> 等。 </p>
<p>当用户进行连接时,权限表的存取过程有以下两个过程:</p>
<ul>
<li>先从 user 表中的 host、user 和 password 这 3 个字段中判断连接的 IP、用户名、和密码是否存在于表中,如果存在,则通过身份验证,否则拒绝连接。</li>
<li>如果通过身份验证、则按照以下权限表的顺序得到数据库权限:user -> db -> tables_priv -> columns_priv。</li>
</ul>
<p>在这几个权限表中,权限范围依次递减,全局权限覆盖局部权限。上面的第一阶段好理解,下面以一个例子来详细解释一下第二阶段。 <br><strong>为了方便测试,需要修改变量 sql_mode</strong></p>
<pre><code>// sql_mode 默认值中有 NO_AUTO_CREATE_USER (防止GRANT自动创建新用户,除非还指定了密码)
SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';</code></pre>
<h4>1. 创建用户 zj@localhost,并赋予所有数据库上的所有表的 select 权限</h4>
<pre><code>MySQL [mysql]> grant select on *.* to zj@localhost;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
MySQL [mysql]> select * from user where user="zj" and host='localhost' \G;
*************************** 1. row ***************************
Host: localhost
User: zj
Select_priv: Y
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
...</code></pre>
<h4>2. 查看 db 表</h4>
<pre><code>MySQL [mysql]> select * from db where user='zj' \G ;
Empty set (0.00 sec)</code></pre>
<p>可以发现,user 表的 select_priv 列是 “Y”,而 db 表中并没有记录,也就是说,对所有数据库都具有相同的权限的用户并不需要记录到 db 表,而仅仅需要将 user 表中的 select_priv 改为 “Y” 即可。换句话说,user 表中的每个权限都代表了对所有数据库都有权限。</p>
<h4>3. 将 zj@localhost 上的权限改为只对 t2 数据库上所有表的 select 权限。</h4>
<pre><code>MySQL [mysql]> revoke select on *.* from zj@localhost;
Query OK, 0 rows affected, 1 warning (0.02 sec)
MySQL [mysql]> grant select on t2.* to zj@localhost;
Query OK, 0 rows affected, 1 warning (0.04 sec)
MySQL [mysql]> select * from user where user='zj' \G;
*************************** 1. row ***************************
Host: localhost
User: zj
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
...
MySQL [mysql]> select * from db where user='zj' \G;
*************************** 1. row ***************************
Host: localhost
Db: t2
User: zj
Select_priv: Y
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Grant_priv: N
</code></pre>
<p>这时候发现,user 表中的 select_priv 变为 “N” ,而 db 表中增加了 db 为 t2 的一条记录。也就是说,当只授予部分数据库某些权限时,user 表中的相应权限列保持 “N”,而将具体的数据库权限写入 db 表。table 和 column 的权限机制和 db 类似。 </p>
<p>从上例可以看出,当用户通过权限认证,进行权限分配时,将按照 user -> db -> tables_priv -> columns_priv 的顺序进行权限分配,即先检查全局权限表 user,如果 user 中对应 权限为 “Y”,则此用户对所有数据库的权限都为“Y”,将不再检查 db、tables_priv 和 columns_priv;如果为“N”,则到 db 表中检查此用户对应的具体数据库,并得到 db 中为 “Y”的权限;如果 db 中相应权限为 “N”,则再依次检查tables_priv 和 columns_priv 中的权限,如果所有的都为“N”,则判断为不具备权限。</p>
<h3>二、账号管理</h3>
<p>主要包括账号的创建,权限的更改和账号的删除。</p>
<h4>1. 创建账号</h4>
<p>使用 grant 语法创建,示例:</p>
<h5>(1) 创建用户 zj ,权限为可以在所有数据库上执行所有权限,只能从本地进行连接。</h5>
<pre><code>MySQL [mysql]> grant all privileges on *.* to zj@localhost;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
MySQL [mysql]> select * from user where user="zj" and host="localhost" \G;
*************************** 1. row ***************************
Host: localhost
User: zj
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
</code></pre>
<p>可以发现,除了 grant_priv 权限外,所有权限在 user 表里面都是 “Y”。</p>
<h5>(2) 在 (1) 基础上,增加对 zj 的 grant 权限</h5>
<pre><code>MySQL [(none)]> grant all privileges on *.* to zj@localhost with grant option;
Query OK, 0 rows affected, 1 warning (0.01 sec)
MySQL [mysql]> select * from user where user="zj" and host='localhost' \G ;
*************************** 1. row ***************************
Host: localhost
User: zj
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
...</code></pre>
<h5>(3) 在 (2) 基础上,设置密码为 “123”</h5>
<pre><code>MySQL [mysql]> grant all privileges on *.* to zj@localhost identified by '123' with grant option;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
MySQL [mysql]> select * from user where user="zj" and host="localhost" \G ;
*************************** 1. row ***************************
Host: localhost
User: zj
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
......
authentication_string: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257
password_expired: N
password_last_changed: 2017-09-25 20:29:42
password_lifetime: NULL</code></pre>
<p>可以发现,密码变成了一堆加密后的字符串。</p>
<h5>(4) 创建新用户 zj2,可以从任何 IP 连接,权限为对 t2 数据库里的所有表进行 select 、update、insert 和 delete 操作,初始密码为“123”</h5>
<pre><code>MySQL [mysql]> grant select ,insert, update,delete on t2.* to 'zj2'@'%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
MySQL [mysql]> select * from user where user='zj2' and host="%" \G;
*************************** 1. row ***************************
Host: %
User: zj2
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
......
authentication_string: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257
password_expired: N
password_last_changed: 2017-09-25 20:37:49
password_lifetime: NULL
MySQL [mysql]> select * from db where user="zj2" and host='%' \G;
*************************** 1. row ***************************
Host: %
Db: t2
User: zj2
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: N
Drop_priv: N
......</code></pre>
<p>user 表中的权限都是“N”,db 表中增加的记录权限则都是“Y”。一般的,只授予用户适当的权限,而不会授予过多的权限。 </p>
<p>本例中的 IP 限制为所有 IP 都可以连接,因此设置为 “*”,mysql 数据库中是通过 user 表的 host 字段来进行控制,host 可以是以下类型的赋值。</p>
<ul>
<li>Host 值可以是主机名或IP号,或 “localhost” 指出本地主机。</li>
<li>可以在 Host 列值使用通配符字符 “%” 和 “_”</li>
<li>Host 值 “%” 匹配任何主机名,空 Host 值等价于 “%”,它们的含义与 like 操作符的模式匹配操作相同。</li>
</ul>
<p><strong>注意:</strong> mysql 数据库的 user 表中 host 的值为 “*” 或者空,表示所有外部 IP 都可以连接,但是不包括本地服务器 localhost,因此,如果要包括本地服务器,必须单独为 localhost 赋予权限。</p>
<h5>(5) 授予 super、process、file 权限给用户 zj3@%</h5>
<pre><code>MySQL [mysql]> grant super,process,file on *.* to 'zj3'@'%';
Query OK, 0 rows affected, 1 warning (0.00 sec)</code></pre>
<p>因为这几个权限都是属于管理权限,因此不能够指定某个数据库,on 后面必须跟 “<em>.</em>”,下面语法将提示错误</p>
<pre><code>MySQL [mysql]> grant super,process,file on t2.* to 'zj3'@'%';
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES</code></pre>
<h5>(6) 只授予登录权限给 zj4@localhost</h5>
<pre><code>MySQL [mysql]> grant usage on *.* to 'zj4'@'localhost';
Query OK, 0 rows affected, 2 warnings (0.01 sec)
MySQL [mysql]> exit
Bye
zj@bogon:~$ mysql -uzj4 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 78
Server version: 5.7.18-log Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.02 sec)</code></pre>
<p>usage 权限只能用于数据库登录,不能执行任何操作</p>
<h4>2. 查看账号权限</h4>
<p>账号创建好后,可以通过如下命令查看权限:</p>
<pre><code>show grants for user@host;</code></pre>
<p>示例:</p>
<pre><code>MySQL [(none)]> show grants for zj@localhost;
+-------------------------------------------------------------------+
| Grants for zj@localhost |
+-------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'zj'@'localhost' WITH GRANT OPTION |
+-------------------------------------------------------------------+
1 row in set (0.01 sec)
</code></pre>
<h4>3. 更改账号权限</h4>
<p>可以进行权限的新增和回收。和创建账号一样,权限变更也有两种方法:使用 grant(新增) 和 revoke (回收) 语句,或者更改权限表。</p>
<h5>示例:</h5>
<h5>(1) zj4@localhost 目前只有登录权限</h5>
<pre><code>MySQL [(none)]> show grants for zj4@localhost;
+-----------------------------------------+
| Grants for zj4@localhost |
+-----------------------------------------+
| GRANT USAGE ON *.* TO 'zj4'@'localhost' |
+-----------------------------------------+
1 row in set (0.00 sec)</code></pre>
<h5>(2) 赋予 zj4@localhost 所有数据库上的所有表的 select 权限</h5>
<pre><code>MySQL [(none)]> grant select on *.* to 'zj4'@'localhost';
Query OK, 0 rows affected, 1 warning (0.00 sec)
MySQL [(none)]> show grants for zj4@localhost;
+------------------------------------------+
| Grants for zj4@localhost |
+------------------------------------------+
| GRANT SELECT ON *.* TO 'zj4'@'localhost' |
+------------------------------------------+
1 row in set (0.00 sec)</code></pre>
<h5>(3) 继续给 zj4@localhost 赋予 select 和 insert 权限,和已有的 select 权限进行合并</h5>
<pre><code>MySQL [(none)]> show grants for 'zj4'@'localhost';
+--------------------------------------------------+
| Grants for zj4@localhost |
+--------------------------------------------------+
| GRANT SELECT, INSERT ON *.* TO 'zj4'@'localhost' |
+--------------------------------------------------+
1 row in set (0.00 sec)</code></pre>
<p>revoke 语句可以回收已经赋予的权限,对于上面的例子,这里决定要收回 zj4@localhost 上的 insert 和 select 权限:</p>
<pre><code>MySQL [(none)]> revoke select,insert on *.* from zj4@localhost;
Query OK, 0 rows affected, 1 warning (0.00 sec)
MySQL [(none)]> show grants for zj4@localhost;
+-----------------------------------------+
| Grants for zj4@localhost |
+-----------------------------------------+
| GRANT USAGE ON *.* TO 'zj4'@'localhost' |
+-----------------------------------------+
1 row in set (0.00 sec)</code></pre>
<p>usage 权限不能被回收,也就是说,revoke 用户并不能删除用户。</p>
<h4>4. 修改账号密码</h4>
<h5>(1) 可以用 mysqladmin 命令在命令行指定密码。</h5>
<pre><code>shell> mysqladmin -u user_name -h host_name password "123456"</code></pre>
<h5>(2) 执行 set password 语句。</h5>
<pre><code>mysql> set password for 'username'@'%' = password('pwd');</code></pre>
<p>如果是更改自己的密码,可以省略 for 语句</p>
<pre><code>mysql> set password=password('pwd');</code></pre>
<h5>(3) 可以在全局级别使用 grant usage 语句(在“<em>.</em>”)来指定某个账户的密码而不影响账户当前的权限。</h5>
<pre><code>mysql> grant usage on *.* to 'username'@'%' identified by 'pwd';</code></pre>
<h4>5. 删除账号</h4>
<p>要彻底的删除账号,可以使用 drop user :</p>
<pre><code>drop user zj@localhost;</code></pre>
<h4>6. 账号资源限制</h4>
<p>创建 MySQL 账号时,还有一类选项称为<code>账号资源限制</code>,这类选项的作用是限制每个账号实际具有的资源限制,这里的“资源”主要包括:</p>
<ul>
<li>max_queries_per_hour count : 单个账号每小时执行的查询次数</li>
<li>max_upodates_per_hour count : 单个账号每小时执行的更新次数</li>
<li>max_connections_per_hour count : 单个账号每小时连接服务器的次数</li>
<li>max_user_connections count : 单个账号并发连接服务器的次数</li>
</ul>
MySQL 日志(错误日志、二进制日志、查询日志、慢查询日志)
https://segmentfault.com/a/1190000011575836
2017-10-16T16:48:57+08:00
2017-10-16T16:48:57+08:00
waterandair
https://segmentfault.com/u/waterandair
2
<p>MySQL 中有 4 中不同的日志,分别是错误日志、二进制日志(binlog 日志)、查询日志和,慢查询日志,应该充分利用这些日志对数据库进行各种维护和调优。</p>
<h3>一、错误日志</h3>
<p>错误日志时 MySQL 中最重要的日志之一,它记录了当 mysqld 启动和停止时,以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时,可以首先查看此日志。</p>
<h3>二、二进制日志</h3>
<p>二进制日志(binlog) 记录了所有 DDL 语句和 DML 语句,但是不包括数据查询语句。语句以“事件”的形式保存,它描述了数据的更改过程。此日志对于灾难时的数据恢复起着极其重要的作用。</p>
<h4>1. 日志的位置和格式</h4>
<p>当用 --log-bin[=file_name] 选项启动时,mysqld 开始将数据变更情况写入日志文件。如果灭有给出 file_name 值,默认名为主机名后面跟“-bin”。如果给出了文件名,但没有包含路径,则文件默认被写入参数 datadir(数据目录)指定的目录。 </p>
<p>二进制日志的格式分为 3 种:statement、row、mixed,可以在启动时通过参数 --binlog_format 进行设置,这 3 种格式的区别如下:</p>
<h5>STATEMENT</h5>
<p>mysql 5.1 之前的版本都采用这种方式,日志中记录的都是语句(statement),每一条对数据造成修改的sql语句都会记录到日志中,通过 mysqlbinlog 工具,可以清晰的看到每条语句的文本。主从复制时,从库(slave)会将日志解析为原文本,并在从库中重新执行一次。这种格式的优点的日志记录清晰易读,日志量少,对 I/O 影响较小。缺点是在某些情况下 slave 的日志复制会出错。</p>
<h5>ROW</h5>
<p>mysql 5.1.11 之后,出现了这种新的日志格式,它将每一行的变更记录到日志中,而不是记录sql语句,比如一个简单的跟新sql: <br>update emp set name='abc' <br>如果是 statement 格式,日志中会记录一行 sql 文本; <br>如果是 row 格式,由于是对全表进行更新,也就是每一行记录都会发生改变,如果是一个 100 万行的大表,则日志中会记录 100万条记录的变化情况。日志量大大增加。 </p>
<p>这种格式的优点是会记录每一行数据变化的细节,不会出现某些情况下无法复制的情况,缺点是日志量大,对 I/O 影响较大。</p>
<h5>MIXED</h5>
<p>这是目前 MySQL 默认的日志格式,即混合了 statement 和 row 两种日志。默认情况下采用 statement,但在一些特殊情况下采用 row 来进行记录,比如 <br>采用 NDB 存储引擎,此时对表的 DML 语句全部采用 row; <br>客户端使用了临时表; <br>客户端采用了不确定函数,比如 current_user() 等; </p>
<p>因为这种不确定函数在主从中得到的值可能不同,导致主从数据产生不一致。mixed 格式能尽量利用两种模式的优点,而避开他们的缺点。 </p>
<p><strong>注意:</strong>可以在 global 和 session 级别对 binlog_format 进行日志格式设置,但一定要谨慎操作,确保从库的复制能够正常进行。</p>
<h4>2. 日志的读取</h4>
<p>由于日志以二进制方式存储,不能直接读取,需要用 mysqlbinlog 工具来查看,对于 statement 格式的文件可以用工具直接查看,对于 row 格式的要加 -v 或 -vv 参数进行读取。</p>
<h4>3. 日志的删除</h4>
<p>对于比较繁忙的系统,每天产生大量日志,这些日志如果长时间不清除,将会对磁盘空间带来极大的浪费,因此,需要定期删除日志。</p>
<h5>(1) 删除所有日志</h5>
<p>执行 “reset master;” 命令将删除所有 binlog 日志,新日志编号从“000001”开始</p>
<h5>(2) 删除指定序号之前的日志文件</h5>
<p>执行 “purge master logs to 'mysql-bin.<strong><em></em></strong>'”命令,将删除编号之前的所有日志(不删除命令中指定的文件)</p>
<h5>(3) 删除指定日期前的日志</h5>
<p>执行 “purge master log before 'yyyy-mm-dd hh24:mi:ss'” 命令将删除指定日期前的所有日志</p>
<h5>(4) 修改配置文件,自动删除</h5>
<p>在配置文件的[mysqld]中设置参数 <code>expire_logs_days=#</code>, 此参数的含义是设置日志的过期天数,过了指定的天数后日志将会被自动删除。</p>
<h4>4. 其他选项</h4>
<p>二进制日志记录了数据的变化过程,对于数据的完整性和安全性起着非常重要的作用。因此,MySQL 还提供了一些其他参数选项来进行更小粒度的管理</p>
<h5>--binlog-do-db=db_name</h5>
<p>该选项告诉主服务器,如果当前的数据库(即 use 选定的数据库)是 db_name, 应将更新记录到二进制文件中,其他所有么有显式指定的数据库更新将被忽略,不记录在日志中。</p>
<h5>--binlog-ignore-db=db_name</h5>
<p>该选项告诉主服务器,如果当前的数据库(即 use 选定的数据库) 是 db_name,不应将更新保存到二进制日志中,其他没有显式忽略的数据库都将进行记录。 <br>如果想记录或忽略多个数据库,可以对上面两个选项分别使用多次。</p>
<h5>--innodb-safe-binlog</h5>
<p>此选项经常和 --sync-binlog = N (每写 N 次日志同步磁盘)一起配合使用,使得事务在日志中的记录更加安全。</p>
<h3>三、查询日志</h3>
<p>查询日志记录了客户端的所有语句,而二进制日志不包含只查询数据的语句。</p>
<h4>1. 日志的位置和格式</h4>
<p>查询日志和慢查询日志都可以选择保存在文件或者表中,使用参数 --log-output[=value]来进行控制,value 值可以是table、file、none 的一个或者多个组合,中间用逗号进行分割,分别表示日志保存在表、文件、不保存在表和文件中,这里的表指的是 mysql 库中的 general_log (慢查询日志是 slow_log) 表。 <br>其中 none 的优先级最高,比如: <br>--log-output = table, file 表示日志可以同时输出到表和文件中 <br>--log-output = table, none 由于 none 的优先级高,表示日志不保存在表和文件中。 <br>如果不显示设置此参数,则默认输出到文件。 </p>
<p>可以通过参数 --general_log[={0|1}] 和 --general_log_file=file_name 来进行控制。 <br>--general_log 设置为1或者不带值都可以启用查询日志;设置为0表示关闭查询日志,如果不指定此参数也不会启用。 <br>如果没有指定 --general_log_file=file_name 的值,且没有显式设置 --log-output 参数,那么日志将写入参数 datadir(数据目录)指定的路径下,默认文件名为 host_name.log。 </p>
<p>这两个参数都是 global 类型,可以在系统启动时或者系统运行时进行动态修改,如果想在 session 级别控制,则通过在 session 中设置参数 sql_log_off 为 on 或者 off。</p>
<h4>2. 日志的读取</h4>
<p>查询日志记录的格式是纯文本,所以可以直接进行读取。 </p>
<p><strong> 注意:</strong> log 日志中记录了所有数据库的操作,对于访问频繁的系统,此日志对系统性能的影响较大,建议一般情况下关闭。</p>
<h3>四、 慢查询日志</h3>
<p>慢查询日志记录了所有执行时间超过参数 long_query_time(单位:秒)设置值并且扫描记录数不小于 min_examined_row_limit 的所有 sql 语句的日志(注意:<code>获得表锁定的时间不算作执行时间</code>)。long_query_time 默认为 10 秒,最小为 0,精度可以到微秒。 </p>
<p>在默认情况下,有两类常见语句不会记录到慢查询日志:<code>管理语句和不使用索引进行查询的语句</code>。管理语句包括 alter table、analyze table、check table、create index、drop index、optimize table、repair table。如果要监控这两类 sql语句,可以分别通过设置参数 <br>--log-slow-admin-statements 和 log_queries_not_using_indexes 进行控制。</p>
<h4>1. 文件位置和格式</h4>
<p>通过以下两个参数开启慢查询日志:</p>
<h5>(1) --slow_query_log[={0|1}]</h5>
<p>如果不指定值或者指定值为 1 都会打开慢查询;</p>
<h5>(2) --slow_query_log_file[=file_name]</h5>
<p>指定慢查询日志的路径 </p>
<p><strong> 注意: </strong> 可以使用 --log-output 参数来指定日志的输出方式,默认会输出到文件,当然也可以选择输出到表,需要注意的是,如果选择输出到表,则表中的记录只能精确到秒,而日志文件中可以精确到微秒。</p>
<h4>2. 日志的读取</h4>
<p>和错误日志、查询日志一样,慢查询日志记录的格式也是纯文本,可以被直接读取。 <br>如果要设置微秒级的慢查询,可以 <code>set global long_query_time=0.01</code> </p>
<p>如果慢查询日志中记录内容很多,可以使用 mysqldumpslow 工具对慢查询日志进行分类汇总。对于 sql 文本完全一致,只是变量不同的语句,mysqldumpslow 将会自动视为同一个语句进行统计,变量值用 N 来代替。这个统计结果将大大增加用户阅读慢查询日志的效率,迅速定位系统的 sql 瓶颈。 </p>
<p><strong> 注意: </strong> 慢查询日志对于发现应用中有性能问题的 sql 很有帮助,正常情况下,打开此日志并经常查看分析。</p>
调整 MySQL 并发相关的参数
https://segmentfault.com/a/1190000011474043
2017-10-09T12:06:13+08:00
2017-10-09T12:06:13+08:00
waterandair
https://segmentfault.com/u/waterandair
1
<p>MySQL server 是多线程结构,包括后台线程和客户服务线程。多线程可以有效利用服务器资源,提高数据库的并发性能。在 MySQL 中,控制并发连接和线程的主要参数包括 max_connections、back_log、thread_cache_size 以及 table_open_cache 等。</p>
<h3>一、调整 max_connections,提高并发连接</h3>
<p>参数 max_connections 控制允许连接到 MySQL 数据库的最大数量。如果状态变量 connection_errors_max_connections 不为零,并且一直增长,就说明不断有连接请求因数据库连接数已达到最大允许的值而失败,应考虑增大max_connections 的值。 </p>
<p>MySQL 最大可支持的数据库连接取决于很多因素,包括给定操作系统平台线程库的质量、内存大小、每个连接的符合以及期望的响应时间等。在 Linux 平台下,MySQL 支持 500~1000 个连接不是难事,如果内存足够,不考虑响应时间,甚至能达到上万个连接。而在 windows 平台下,受其所用线程库的影响,最大连接数有以下限制: </p>
<p>(open tables * 2 + open connections) < 2048 </p>
<p>每个session 操作 MySQL 数据库表都需要占用文件描述符,数据库连接本身也要占用文件描述符,因此,在增大 max_connections 时,也要注意评估 open-files-limit <a href="https://segmentfault.com/a/1190000009724931">(文件描述符)</a>的设置是否够用。</p>
<h3>二、调整 back_log</h3>
<p>back_log 参数控制 MySQL 监听 tcp 端口时设置的积压请求栈大小,5.6.6版本以前的默认值是 50,5.6.6 版本以后的默认值是 50 + (max_connections/5),但最大不能超过 900。 </p>
<p>如果需要数据库在较短时间内处理大量连接请求,可以考虑适当增大 back_log 的值。</p>
<h3>三、调整 table_open_cache</h3>
<p>每一个 sql 执行线程至少都要打开一个表缓存,参数 table_open_cache 控制所有 sql 执行线程可打开表缓存的数量。这个参数的值应根据最大连接数 max_connections 以及每个连接执行关联查询中所涉及表的最大个数(用 N 表示)来设定: </p>
<p>max_connection * N </p>
<p>在未执行 flush tables 命令的情况下,如果 MySQL 状态值 opened_tables 的值较大,就说明 table_open_cache 设置的太小,应适当增大。增大 table_open_cache 的值,会增加 MySQL 对文件描述符的使用量,因此,也要注意评估 open-files-limit 的设置是或否够用。</p>
<h3>四、调整 thread_cache_size</h3>
<p>为加快连接数据库的速度,MySQL 会缓存一定数量的客户服务线程以备重用,通过参数 thread_cache_size 可控制 MySQL 缓存客户端线程的数量。 <br>可以通过计算线程 cache 的失效率 threads_created / connections 来衡量 thread_cahce_size 的设置是否合适,该值越接近 1,说明线程 cache 命中率越低,应考虑适当增加 thread_cahce_size 的值。</p>
<h3>五、innodb_lock_wait_timeout 的设置</h3>
<p>参数 innodb_lock_wait_timeout 可以控制 innodb 事务等待行锁的时间,默认值是 50ms,可以根据需要动态设置。对于需要快速反馈的交互式应用,可以将行锁等待超时时间调大,以避免发生大的回滚操作。</p>
MySQL 中的常用工具
https://segmentfault.com/a/1190000011338402
2017-09-25T07:47:18+08:00
2017-09-25T07:47:18+08:00
waterandair
https://segmentfault.com/u/waterandair
1
<h3>一、mysql (客户端连接工具)</h3>
<p>使用最频繁的连接数据库的客户端工具,使用语法如下:</p>
<pre><code>mysql [options] [database] </code></pre>
<p>这里的 options 表示 mysql 的可用选项,可以一次写一个或者多个,甚至可以不写;database 表示连接的数据库,一次只能写一个或者不写,如果不写,连接成功后需要用 “use database”命令来进入要操作的数据库。</p>
<h4>1. 连接选项</h4>
<p>选项的表达方式有多种,例如:</p>
<pre><code># 这三种方式都是可以的
shell> mysql -u root
shell> mysql -uroot
shell> mysql -user=root</code></pre>
<h4>1. 连接选项</h4>
<pre><code>-u, --user=name 指定用户名
-p, --password[=name] 指定密码
-h, --host=name 指定服务器 IP 或者域名
-P, --port=# 指定连接端口</code></pre>
<p>一般在在本地环境,为了方便,可以在 配置文件 my.cnf 中配置当前用户和密码,配置好后,直接执行 mysql 就可以连接到数据库:</p>
<pre><code>[client]
user=root
password=000000
port = 3306
socket = /tmp/mysql.sock
default-character-set = utf8mb4</code></pre>
<p>配置好后,直接执行 mysql 即可:</p>
<pre><code>zj@bogon:~$ mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 19
...</code></pre>
<p>登录远程服务器,需要指定地址和端口:</p>
<pre><code>shell> mysql -h 192.168.10.10 -P 3306 -uroot -p</code></pre>
<p><strong> 注意: </strong>在正式的生产环境中,为了安全起见,一般需要创建应用账号并赋予适当权限,而不会用 root 直接操作数据库;默认端口(3306)一般不要使用,可以改为任意操作系统未占用的端口。</p>
<h4>2. 客户端字符集选项</h4>
<pre><code>--default-character-set=charset-name</code></pre>
<p>作为服务器的字符集选项,这个选项也可以配置在 my.cnf 的 [mysqld] 组中。同样,作为客户端字符集选项,也可以配置在 my.cnf 的 [mysql]组中,这样每次用 mysql 工具连接数据库的时候就会自动使用此客户端字符集。当然,也可以在 mysql 的命令行中手工指定客户端字符集:</p>
<pre><code>shell> mysql -u user -default-character-set=charset</code></pre>
<p>相当于在 mysql 客户端连接成功后执行:</p>
<pre><code>set names charset;</code></pre>
<h4>3. 执行选项</h4>
<pre><code>-e, --execute=name // 执行 sql 语句并退出</code></pre>
<p>此选项可以直接在 MySQL 客户端执行 sql 语句,对于一些批处理脚本,这是方式尤其方便:</p>
<pre><code>zj@bogon:~$ mysql mysql -e "select user,host from user"
+-----------+-----------+
| user | host |
+-----------+-----------+
| root | 127.0.0.1 |
| mysql.sys | localhost |
| root | localhost |
+-----------+-----------+</code></pre>
<p>可以按这种方式连续执行多个 sql 语句,用英文分号(;)隔开。</p>
<h4>4. 格式化选项</h4>
<pre><code>-E, --vertical 将输出方式按照字段顺序竖着显示
-s, --silent 去掉 mysql 中的线条框显示 </code></pre>
<p>“-E” 选项类似于 mysql 里面执行 sql 语句后加 “G”, 经常和 -e 一起使用。</p>
<h3>二、myisampack (myisam 表压缩工具)</h3>
<p>myisampack 是一个表压缩工具,可以使用很高的压缩率来对 myisam 存储引擎的表进行压缩,使得压缩后的表占用比压缩前小得多的空间。但是压缩后的表将成为一个只读表,不能进行 DML 操作。</p>
<h3>三、mysqladmin(MySQL 管理工具)</h3>
<p>mysqladmin 是一个执行管理操作的客户端程序。可以用它来检查服务器的配置和当前状态、创建并删除数据库等。它的功能与 mysql 客户端非常类似,主要区别在于它更侧重于一些管理方面的功能。 </p>
<p>使用语法:</p>
<pre><code>shell> mysqladmin [options] command [command-options]... </code></pre>
<p>可以执行的命令如下:</p>
<pre><code> create databasename Create a new database 新建数据库
debug Instruct server to write debug information to log 把 debug 日志记录到日志文件中
drop databasename Delete a database and all its tables 删除数据库
extended-status Gives an extended status message from the server 查看 MySQL 服务器的状态信息
flush-hosts Flush all cached hosts
flush-logs Flush all logs
flush-status Clear status variables
flush-tables Flush all tables
flush-threads Flush the thread cache
flush-privileges Reload grant tables (same as reload)
kill id,id,... Kill mysql threads
password [new-password] Change old password to new-password in current format
ping Check if mysqld is alive
processlist Show list of active threads in server
reload Reload grant tables
refresh Flush all tables and close and open logfiles
shutdown Take server down
status Gives a short status message from the server
start-slave Start slave
stop-slave Stop slave
variables Prints variables available
version Get version info from server</code></pre>
<p>举例:</p>
<pre><code>zj@bogon:/usr/local/mysql/bin$ mysqladmin -uroot -p shutdown
Enter password: </code></pre>
<h3>四、日志管理工具</h3>
<p>由于服务器生成的二进制文件以二进制格式保存,所以如果想要检查这些文件的文本格式,就会用到 mysqlbinlog 日志管理工具。 <br>用法如下:</p>
<pre><code>shell> mysqlbinlog [option] log-file1 log-file2... </code></pre>
<p>option 有很多选项:</p>
<ul>
<li>-d,--database=name: 指定数据库名称,只列出指定的数据库相关操作。</li>
<li>-o, --offset=#: 忽略日志中的前 n 行命令。 <br>-r, --result-file=name: 将输出的文本格式日志输出到指定文件</li>
</ul>
<p>-s, --short-form: 显示简单格式,省略掉一些信息。 <br>--start-datetime=name --stop-datetime=name: 指定日期间隔内的所有日志。 <br>--start-position=# --stop-position=#: 指定位置间隔内的所有日志</p>
<h4>1. 示例准备:创建新日志,新建库 t1 和 t2, 以及分别新建表 test1 和 test2</h4>
<pre><code>MySQL [(none)]> reset master;
Query OK, 0 rows affected (0.01 sec)
MySQL [(none)]> create table t1(id int,name varchar);
ERROR 1046 (3D000): No database selected
MySQL [(none)]> reset master;
Query OK, 0 rows affected (0.01 sec)
MySQL [(none)]> create database t1;
Query OK, 1 row affected (0.04 sec)
MySQL [(none)]> create database t2;
Query OK, 1 row affected (0.02 sec)
MySQL [(none)]> use t1;
Database changed
MySQL [t1]> create table test1(id int, name varchar(30));
Query OK, 0 rows affected (0.11 sec)
MySQL [t1]> insert into test1 value (1,'zj');
Query OK, 1 row affected (0.14 sec)
MySQL [t1]> insert into test1 value (2,'zj2');
Query OK, 1 row affected (0.02 sec)
MySQL [t1]> use t2;
Database changed
MySQL [t2]> create table test2(id int,name varchar(30));
Query OK, 0 rows affected (0.02 sec)
MySQL [t2]> insert into test2 select * from t1.test1;
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
MySQL [t2]> select * from t1.test1;
+------+------+
| id | name |
+------+------+
| 1 | zj |
| 2 | zj2 |
+------+------+
2 rows in set (0.02 sec)
MySQL [t2]> select * from test2;
+------+------+
| id | name |
+------+------+
| 1 | zj |
| 2 | zj2 |
+------+------+
2 rows in set (0.00 sec)
</code></pre>
<h4>2. 不加任何参数,显示所有日志</h4>
<p>注意:必须拥有访问目标文件的权限</p>
<pre><code>zj@bogon:/usr/local/mysql/bin$ sudo ./mysqlbinlog --no-defaults /data/mysql/mysql-bin.000001
[sudo] password for zj:
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#170920 20:44:49 server id 1 end_log_pos 123 CRC32 0x42fd5a4d Start: binlog v 4, server v 5.7.18-log created 170920 20:44:49 at startup
......
create table test2(id int,name varchar(30))
/*!*/;
# at 1366
#170920 20:50:29 server id 1 end_log_pos 1431 CRC32 0x18a95938 Anonymous_GTID last_committed=6 sequence_number=7
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1431
#170920 20:50:29 server id 1 end_log_pos 1509 CRC32 0x2fa8bd6c Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1505911829/*!*/;
BEGIN
/*!*/;
# at 1509
#170920 20:50:29 server id 1 end_log_pos 1622 CRC32 0x77ce6f3b Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1505911829/*!*/;
insert into test2 select * from t1.test1
/*!*/;
# at 1622
#170920 20:50:29 server id 1 end_log_pos 1653 CRC32 0x41b7a45b Xid = 29
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
</code></pre>
<h4>3. 加 -d 选项,将只显示 t2 数据库的操作日志</h4>
<pre><code>zj@bogon:/usr/local/mysql/bin$ sudo ./mysqlbinlog --no-defaults /data/mysql/mysql-bin.000001 -d t2
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
......
SET TIMESTAMP=1505911829/*!*/;
insert into test2 select * from t1.test1
/*!*/;
# at 1622
#170920 20:50:29 server id 1 end_log_pos 1653 CRC32 0x41b7a45b Xid = 29
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
</code></pre>
<h4>4. 加 -o 选项, 忽略掉前 20 行命令</h4>
<pre><code>zj@bogon:/usr/local/mysql/bin$ sudo ./mysqlbinlog --no-defaults /data/mysql/mysql-bin.000001 -o 20
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#170920 20:44:49 server id 1 end_log_pos 123 CRC32 0x42fd5a4d Start: binlog v 4, server v 5.7.18-log created 170920 20:44:49 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
wWLCWQ8BAAAAdwAAAHsAAAABAAQANS43LjE4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADBYsJZEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AU1a/UI=
'/*!*/;
# at 1509
#170920 20:50:29 server id 1 end_log_pos 1622 CRC32 0x77ce6f3b Query thread_id=4 exec_time=0 error_code=0
use `t2`/*!*/;
SET TIMESTAMP=1505911829/*!*/;
SET @@session.pseudo_thread_id=4/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=45/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
insert into test2 select * from t1.test1
/*!*/;
# at 1622
#170920 20:50:29 server id 1 end_log_pos 1653 CRC32 0x41b7a45b Xid = 29
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
</code></pre>
<h4>5. 加 -r 选项,将上面的结果输出到文件 resultfile 中。</h4>
<pre><code>zj@bogon:/usr/local/mysql/bin$ sudo ./mysqlbinlog --no-defaults /data/mysql/mysql-bin.000001 -o 20 -r ./logfile
zj@bogon:/usr/local/mysql/bin$ sudo more ./logfile
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
...</code></pre>
<h4>6. 结果显示的内容较多,显得比较乱,加 -s 选项将上面的内容进行简单显示</h4>
<pre><code>zj@bogon:/usr/local/mysql/bin$ sudo ./mysqlbinlog --no-defaults /data/mysql/mysql-bin.000001 -o 20 -s
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
ROLLBACK/*!*/;
use `t2`/*!*/;
SET TIMESTAMP=1505911829/*!*/;
SET @@session.pseudo_thread_id=999999999/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=45/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
insert into test2 select * from t1.test1
/*!*/;
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
</code></pre>
<h4>7. 加 “--start-datetime --stop-datetime” 选项显示 5:00:00 ~ 5:01:00 之间的日志</h4>
<pre><code>zj@bogon:/usr/local/mysql/bin$ sudo ./mysqlbinlog --no-defaults /data/mysql/mysql-bin.000001 --start-datetime="2017/09/30 05:00:00" --stop-datetime='2017/09/30 05:01:00'</code></pre>
<p>开始日期和结束日期可以只写一个。如果只写开始日期,表示范围开始日期到日志结束;如果只写结束日期,表示日志开始到指定的结束日期。</p>
<h4>8. --start-position=# 和 --stop-position=#, 与日期范围类似,不过可以更精确的表示范围。</h4>
<pre><code>sudo ./mysqlbinlog --no-defaults /data/mysql/mysql-bin.000001 --start-position=4 --stop-datetime=100
</code></pre>
<h3>五、mysqlcheck (myisam 表维护工具)</h3>
<p>mysqlcheck 工具可以检查和修复 myisam 表,还可以优化和分析表。实际上,它集成了 mysql 工具中的 check、repair、analyze、optimize </p>
<p>有 3 种方式可以来调用 mysqlcheck:</p>
<pre><code>shell> mysqlcheck [options] db_name [tables]
shell> mysqlcheck [options] --database DB1 [DB2 DB3...]
shell> mysqlcheck [options] --all-databse</code></pre>
<p>option 中有以下常用选项:</p>
<ul>
<li>-c, --check (检查表)</li>
<li>-r, --repair (修复表)</li>
<li>-a, --analyze (分析表)</li>
<li>-o, --optimize (优化表)</li>
</ul>
<p>其中,默认选项是 -c (检查表) <br>示例:</p>
<h4>1. 检查表</h4>
<pre><code>zj@bogon:/data/mysql$ mysqlcheck -c t2
t2.test1 OK
t2.test2 OK
</code></pre>
<h4>2. 修复表</h4>
<pre><code>zj@bogon:/data/mysql$ mysqlcheck -r t2
t2.test1
note : The storage engine for the table doesn't support repair
t2.test2 OK</code></pre>
<p>test1 表的存储引擎为 innodb,不支持 repair。</p>
<h4>3. 分析表</h4>
<pre><code>zj@bogon:/data/mysql$ mysqlcheck -a t2
t2.test1 OK
t2.test2 OK</code></pre>
<h4>4. 优化表</h4>
<pre><code>zj@bogon:/data/mysql$ mysqlcheck -o t2
t2.test1
note : Table does not support optimize, doing recreate + analyze instead
status : OK
t2.test2 </code></pre>
<h3>六、mysqldump (数据导出工具)</h3>
<p>mysqldump 客户端工具用来备份数据库或在不同数据库之间进行数据迁移。备份内容包含创建表或装载表的 sql 语句。 </p>
<p>有三中方式来调用 mysqldump:</p>
<pre><code>mysqldump [OPTIONS] database [tables] // 备份单个数据库或者库中部分数据表
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] //备份指定的一个或者多个数据库
mysqldump [OPTIONS] --all-databases [OPTIONS] // 备份所有数据库</code></pre>
<h4>1. 连接选项</h4>
<pre><code>-u, --user=name // 指定用户名
-p, --password[=name] // 指定密码
-h, --host=name // 指定服务器 IP 或者域名
-p, --port=# // 指定连接端口</code></pre>
<p>示例:</p>
<pre><code>shell> mysqldump -h192.18.10.10 -p3306 -uroot -p test > test.sql</code></pre>
<h4>2. 输出内容选项</h4>
<pre><code>--add-drop-database 每个数据库创建语句前加上 drop database 语句
--add-drop-table 在每个表创建语句前加上 drop table 语句</code></pre>
<p>在默认情况下,这两个参数都自动加上。</p>
<pre><code>-n, --no-create-db 不包含数据库的创建语句
-t, --no-create-info 不包含数据表的创建语句
-d, --no-data 不包含数据</code></pre>
<h4>3. 输出格式选项</h4>
<ul><li>--compact 选项使得输出结果简洁,不包括默认选项中的各种注释。</li></ul>
<pre><code>root@bogon:/usr/local/mysql/bin# ./mysqldump --compact t2 emp > emp.sql
root@bogon:/usr/local/mysql/bin# more emp.sql
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `emp` (
`id` int(11) NOT NULL DEFAULT '0',
`name` varchar(10) DEFAULT NULL,
`context` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `emp` VALUES (1,'a','a'),(2,'b','b');
</code></pre>
<ul><li>-c 或者 --complete-insert 选项使得输出文件中的 insert 语句包括字段名称,默认是不包括字段名称的。</li></ul>
<pre><code>root@bogon:/usr/local/mysql/bin# ./mysqldump -c --compact t2 emp > emp.sql
root@bogon:/usr/local/mysql/bin# more emp.sql
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `emp` (
`id` int(11) NOT NULL DEFAULT '0',
`name` varchar(10) DEFAULT NULL,
`context` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `emp` (`id`, `name`, `context`) VALUES (1,'a','a'),(2,'b','b');
</code></pre>
<ul><li>-T 选项将指定数据表中的数据备份为单纯的数据文本和建表 sql 两个文件,经常和下面几个选项一起配合使用,将数据导出为指定格式显示。</li></ul>
<pre><code>-T, --tab=name 备份数据和建表语句
--fileds-terminated-by=name 域分隔符
--fileds-enclosed-by=name 域引用符
--fileds-optionally-enclosed-by=name 域可选引用符
--fileds-escaped-by=name 转义字符</code></pre>
<p>示例:将 t2 数据库中的表 emp 导出为单纯的数据文本和建表 sql 两个文件,并存放在当前路径下的 bak 目录下。</p>
<h5>1.创建备份目录</h5>
<pre><code>root@bogon:/usr/local/mysql/bin# mkdir bak</code></pre>
<h5>2. 将 t2 数据库下的表 emp 备份到 bak 目录下</h5>
<pre><code>root@bogon:/usr/local/mysql/bin# ./mysqldump t2 emp -T ./bak</code></pre>
<h4>3. 查看 bak 目录,发现两个文件</h4>
<pre><code>root@bogon:/usr/local/mysql/bin# ls ./bak
emp.sql emp.txt</code></pre>
<h4>4. 查看两个文件的内容, .sql 结尾的是建表及插入数据的sql,.txt 结尾的是表数据</h4>
<pre><code>root@bogon:/usr/local/mysql/bin# more ./bak/emp.sql
-- MySQL dump 10.13 Distrib 5.7.18, for Linux (x86_64)
--
-- Host: localhost Database: t2
-- ------------------------------------------------------
-- Server version 5.7.18-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `emp`
--
DROP TABLE IF EXISTS `emp`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `emp` (
`id` int(11) NOT NULL DEFAULT '0',
`name` varchar(10) DEFAULT NULL,
`context` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2017-09-21 12:07:38</code></pre>
<pre><code>root@bogon:/usr/local/mysql/bin# more ./bak/emp.txt
1 a a
2 b b</code></pre>
<h4>4. 字符集选项</h4>
<p>mysqldump 导出的数据的字符集使用的是 mysqld 启动时的默认字符集,如果表的字符集用的不是默认字符集,导出的数据就有可能出现乱码。所以在导出时,应该先确定表的字符集,在导出时指定该字符集即可。</p>
<pre><code>shell> mysqldump -uroot --compact --default-character-set=utf8 t2 emp > emp.sql</code></pre>
<h4>5. 其他常用选项</h4>
<ul><li>-F --flush-logs (备份前刷新日志)</li></ul>
<p>加上此选项后,备份前将关闭就日志,生成新日志。使得进行恢复的时候直接从新日志开始进行重做,大大方便了恢复过程。</p>
<ul><li>-l --lock-tables (给所有表加读锁)</li></ul>
<p>可以在备份期间使用,使得数据无法被更新,从而使备份的数据保持一致性,可以配合 -F 选项一起使用。</p>
<h3>七、mysqlimport (数据导入工具)</h3>
<p>mysqlimport 是客户端数据导入工具,用来导入 mysqldump 加 -T 选项后导出的文本文件。 </p>
<p>基本用法:</p>
<pre><code>shell> mysqlimport [options] db_name textfile1</code></pre>
<h3>八、mysqlshow (数据库对象查看工具)</h3>
<p>mysqlshow 客户端对象查找工具,用来很快的查找存在哪些数据库,数据库中的表、表中的列或索引,和 mysql 客户端工具很类似,不过有些特性是 mysql 客户端工具所不具备的。 </p>
<p>使用方法:</p>
<pre><code>shell> mysqlshow [option] [db_name [tbl_name [col_name]]]</code></pre>
<p>如果不加任何选项,默认情况下会显示所有数据库。 </p>
<p>常用选项:</p>
<h4>1. --count (显示数据库和表的统计信息)</h4>
<p>如果不指定数据库,则显示每个数据库的名称、表数量、记录数量; <br>如果指定数据库,则显示指定数据库的每个表名、字段数量,记录数量; <br>如果指定具体数据库中的具体表,则显示表的字段信息。</p>
<h4>2. -k 或者 --keys (显示指定表中的所有索引)</h4>
<p>此选项显示了两部分内容,一部分是指定表的表结构,另一部分中是指定表的当前索引信息</p>
<h4>3. -i 或者 --status (显示表的一些状态信息)</h4>
<h3>九、perror (错误代码查看工具)</h3>
<p>在 MySQL 的使用过程中,可能会出现各种各样的 error。这些 error 有些是由于操作系统引起的,比如文件或者目录不存在;有些则是由于存储引擎使用不当引起的。这些 error 一般都有一个代码,类似于 “error:#” 或者 “Errcode:#”,“#” 代表具体的错误号。perror 的作用就是解释这些错误代码的详细含义:</p>
<pre><code>perror [options] [errorcode [errorcode]]
zj@bogon:/usr/local/mysql/bin$ perror 30
OS error code 30: Read-only file system
zj@bogon:/usr/local/mysql/bin$ perror 60
OS error code 60: Device not a stream
zj@bogon:/usr/local/mysql/bin$ perror 30 60
OS error code 30: Read-only file system
OS error code 60: Device not a stream
</code></pre>
innodb 日志机制及优化
https://segmentfault.com/a/1190000011322006
2017-09-23T10:28:33+08:00
2017-09-23T10:28:33+08:00
waterandair
https://segmentfault.com/u/waterandair
1
<h3>一、innodb 重做日志</h3>
<p>当更新数据时,innodb 内部的操作流程大致是:</p>
<ol>
<li>将数据读入 innodb buffer pool,并对相关记录加独占锁;</li>
<li>将 undo 信息写入 undo 表空间的回滚段中;</li>
<li>更改缓存页中的数据,并将更新记录写入 redo buffer中;</li>
<li>提交时,根据 innodb_flush_log_at_trx_commit 的设置,用不同的方式将 redo buffer 中的更新记录刷新到 innodb redo log file 中,然后释放独占锁;</li>
<li>最后,后台 IO 线程根据需要择机将缓存中更新过的数据刷新到磁盘文件中。</li>
</ol>
<pre><code>---
LOG
---
Log sequence number 1708635750 // 上次数据页的修改,还没有刷新到日志文件的lsn号
Log flushed up to 1708635750 // 上次成功操作,已经刷新到日志文件中的lsn号
Pages flushed up to 1708635750
Last checkpoint at 1708635741 // 上次检查点成功完成时的lsn号,以为着恢复的起点
</code></pre>
<p>其中 lsn(log sequence number) 称为<code>日志序列号</code>,它实际上对应日志文件的偏移量,其生成公式是: <br>新的 lsn = 旧的 lsn + 写入的日志大小 </p>
<p>例如,日志文件大小为 600 MB,目前的 lsn 是 1GB,现在要将 512 字节的更新记录写入 redo log,则实际写入过程如下:</p>
<ol>
<li>求出偏移量:由于 lsn 数值远大于日志文件大小,因此通过取余方式,得到偏移量为 400MB</li>
<li>写入日志:找到偏移 400MB 的位置,写入 512 字节日志内容,下一个事务的 lsn 就是 1000000512。</li>
</ol>
<p>除 innodb buffer pool,innodb log buffer 的大小,redo 日志文件的大小以及 innodb_flush_log_at_trx_commit 参数的设置等,都会影响 innodb 的性能。</p>
<h3>二、innodb_flush_log_at_trx_commit 的设置</h3>
<p>innodb_flush_log_at_trx_commit 参数可以控制将 redo buffer 中的更新记录写入到日志文件以及将日志文件数据刷新到磁盘的操作时机。通过调整这个参数,可以在性能和数据安全之间做取舍。</p>
<ul>
<li>0:在事务提交时,innodb 不会立即触发将缓存日志写到磁盘文件的操作,而是每秒触发一次缓存日志回写磁盘操作,并调用系统函数 fsync 刷新 IO 缓存。这种方式效率最高,也最不安全。</li>
<li>1:在每个事务提交时,innodb 立即将缓存中的 redo 日志回写到日志文件,并调用 fsync 刷新 IO 缓存。</li>
<li>2:在每个事务提交时,innodb 立即将缓存中的 redo 日志回写到日志文件,但并不马上调用 fsync 来刷新 IO 缓存,而是每秒只做一次磁盘IO 缓存刷新操作。只要操作系统不发生崩溃,数据就不会丢失,这种方式是对性能和数据安全的折中,其性能和数据安全性介于其他两种方式之间。</li>
</ul>
<p>innodb_flush_log_at_trx_commit 参数的默认值是 1,即每个事务提交时都会从 log buffer 写更新记录到日志文件,而且会实际刷新磁盘缓存,显然,这完全能满足事务的持久化要求,是最安全的,但这样会有较大的性能损失。 </p>
<p>在某些需要尽量提高性能,并且可以容忍在数据库崩溃时丢失小部分数据,那么通过将参数 innodb_flush_log_at_trx_commit 设置成 0 或 2 都能明显减少日志同步 IO,加快事务提交,从而改善性能。</p>
<h3>三、设置 log file size ,控制检查点</h3>
<p>当一个日志文件写满后,innodb 会自动切换到另一个日志文件,但切换时会触发数据库<code>检查点(checkpoint)</code>,这将导致 innodb 缓存脏页的小批量刷新,会明显降低 innodb 的性能。 </p>
<p>可以通过增大 log file size 避免一个日志文件过快的被写满,但如果日志文件设置的过大,恢复时将需要更长的时间,同时也不便于管理,一般来说,<code>平均每半个小时写满一个日志文件比较合适</code>。 </p>
<p>可以通过下面的方式来计算 innodb 每小时产生的日志量并估算合适的 innodb_log_file_size 的值:</p>
<pre><code>// 1. 计算 innodb 每分钟产生的日志量
MySQL [(none)]> pager grep -i "log sequence number"
PAGER set to 'grep -i "log sequence number"'
MySQL [(none)]> show engine innodb status\G select sleep(60);show engine innodb status\G
Log sequence number 1706853570
1 row in set (0.00 sec)
1 row in set (1 min 0.00 sec)
Log sequence number 1708635750
1 row in set (0.00 sec)
MySQL [(none)]> nopager
PAGER set to stdout
MySQL [(none)]> select round ((1708635750 - 1706853570) /1024/1024) as MB;
+------+
| MB |
+------+
| 2 |
+------+
1 row in set (0.00 sec)
</code></pre>
<p>通过上述操作得到 innodb 每分钟产生的日志量是 2 MB。然后计算没半小时的日志量: </p>
<p>半小时日志量 = 30 * 2MB = 60MB </p>
<p>这样,就可以得出 innodb_log_file_size 的大小至少应该是 60MB。</p>
<h3>四、调整 innodb_log_buffer_size</h3>
<p>innodb_log_buffer_size 决定 innodb 重做日志缓存池的大小,默认是 8MB。对于可能产生大量更新记录的大事务,增加 innodb_log_buffer_size 的大小,可以避免 innodb 在事务提交前就执行不必要的日志写入磁盘操作。因此,对于会在一个事务中更新,插入或删除大量记录的应用,可以通过增大 innodb_log_buffer_size 来减少日志写磁盘操作,提高事务处理性能。</p>
MySQL 内存优化
https://segmentfault.com/a/1190000011225200
2017-09-18T17:09:07+08:00
2017-09-18T17:09:07+08:00
waterandair
https://segmentfault.com/u/waterandair
6
<h3>一、内存优化原则</h3>
<ul>
<li>将尽量多的内存分配给 MySQL 做缓存,但要给操作系统和其他程序的运行预留足够的内存。</li>
<li>myisam 的数据文件读取依赖于操作系统自身的 I/O 缓存,因此,如果有 myisam 表,要预留更多的内存给操作系统做 IO 缓存。</li>
<li>排序区,连接区等缓存是分配给每个数据库会话(session)专用的,其默认值的设置要根据最大连接数合理分配,如果设置过大,不但浪费内存资源,而且在并发连接较高时会导致物理内存耗尽。</li>
</ul>
<h3>二、myisam 内存优化</h3>
<p>myisam 存储引擎使用 key buffer 缓存索引块,以加速 myisam 索引的读写速度,对于 myisam 表的数据块,MySQL 没有特别的患处机制,完全依赖操作系统的 IO 缓存。</p>
<h4>1. key_buffer_size 设置</h4>
<p>key_buffer_size 决定 myisam <code>索引块</code>缓存区的大小,它直接影响 myisam 表的存取效率。可以在 MySQL 的参数文件中设置该值。 <br>对弈一般数据库服务器,建议至少将 1/4 可用内存分配给 key_buffer_size。 </p>
<p>可以通过检查 key_read_requests、key_write_requests 和 key_writes 等 MySQL 状态变量来评估索引缓存的效率。 <br>一般来说,索引块物理读比率:<br><code>key_reads / key_read_requests</code> 应小于0.01 <br>索引块写比率也应尽可能小,但这与应用特点有关,对于更新和删除操作特别多的应用,key_writes / key_write_requests 可能会接近 1,而对于每次更新很多行记录的应用,key_writes / key_write_requests 就会比较小。 </p>
<p>除通过索引块的物理读写比率衡量 key buffer 外,也可以通过评估 key buffer 的使用率来判断索引缓存设置是否合理。 <br>key buffer 使用率的计算公式如下: <br><code>1 - ((key_blocks_unused * key_cache_block_size) / key_buffer_size)</code> <br>一般来说,使用率在 80% 左右比较合适,大于 80% 可能因索引缓存不足而导致性能下降;小于 80% 会导致内存浪费。</p>
<h4>2. 使用多个索引缓存</h4>
<p>MySQL 通过各 session 共享的 key buffer 提高了 myisam 索引存取的性能,但它并不能消除 session 间对 key buffer 的竞争。比如,一个 session 如果对某个很大的索引进行扫描,就可能将其他的索引数据块挤出索引缓存区,而这些索引块可能是其他 session 要用的热数据。 <br>为减少 session 间对 key buffer 的竞争,MySQL 从 5.1 版本开始引入了多缓存的机制,从而可以将不同表的索引缓存到不同的key buffer 中:</p>
<pre><code># hot_cache 是新建索引缓存的命名,global 关键字表示新建缓存对每一个新的连接都有效。
mysql> set global hot_cache.key_buffer_size = 128*1024;
# 删除刚刚创建的索引缓存
mysql> set global hot_cache.key_buffer_size = 0;</code></pre>
<p>默认情况下,MySQL 将使用默认的key buffer 缓存 myisam 表的索引,可以用 cache index 命令指定表的索引缓存:</p>
<pre><code>MySQL [sakila]> create table t2 (id int , name varchar(30)) engine myisam;
Query OK, 0 rows affected (0.03 sec)
MySQL [sakila]> create table t1 (id int , name varchar(30)) engine myisam;
Query OK, 0 rows affected (0.00 sec)
MySQL [sakila]> cache index t1,t2 in hot_cache;
+-----------+--------------------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------+--------------------+----------+----------+
| sakila.t1 | assign_to_keycache | status | OK |
| sakila.t2 | assign_to_keycache | status | OK |
+-----------+--------------------+----------+----------+
2 rows in set (0.00 sec)
</code></pre>
<p>更常见的做法是通过配置文件在 MySQL 启动时自动创建并加在索引缓存:</p>
<pre><code>key_buffer_size = 4G
hot_cache.key_buffer_size = 2G
cold_cache.key_buffer_size = 1G
init_file=/path/mysqld_init.sql </code></pre>
<p>在 mysql_init.sql 文件中,可以通过 cache index 命令分配索引缓存,并用 load index into cache 命令来进行索引预加载:</p>
<pre><code>cache index sales in hot_cache;
cache index sales2 in cold_cache;
load index into cache sales,sales2
</code></pre>
<h4>3. 调整“中点插入策略”</h4>
<p><a href="https://link.segmentfault.com/?enc=eWbyaQ3%2Fupofymx5JohLDg%3D%3D.euEExTfJH7l90ktxXH3f25fYAjQLP0Tba4v%2BBIbpNyDbny6Y%2FVjE%2BxjYJEt4gTroTLJj%2BUttbvd9N4xvXf%2FeQw%3D%3D" rel="nofollow">中点插入策略</a><br>中点插入策略(midpoint insertion strategy)是对简单 lru 淘汰算法的改进,它将 lru 链分成两部分:hot 子表和 warm 子表,当一个索引块读入内存时,先放到 lru 链表的“中点”,即 warm 子表的尾部,当达到一定的命中次数后,该索引块会被晋升到 hot 子表的尾部;此后,该数据块在 hot 子表流转,如果其到达 hot 子表的头部并超过一定时间,它将由 hot 子表的头部降级到 warm 子表的头部;当需要淘汰索引块时,缓存管理程序会优先淘汰 warm 表头部的内存块。这种算法能够避免偶尔被访问的索引块将访问频繁的热块淘汰。 </p>
<p>可以通过调节 key_cache_division_limit 来控制多大比例的缓存用做 warm 子表,其默认值是 100,意思是全部缓存块都放在 warm 子表,其实也就是不启用“中点插入策略”。如果我们希望将大致 30% 的缓存用来 cache 最热的索引块,可以对做如下设置</p>
<pre><code>set global key_cache_division_limit = 70
set global hot_cache key_cache_division_limit = 70</code></pre>
<p>除了调节 warm 子表的比例外,还可以通过 key_cache_age_threshold 控制数据块由 hot 子表向 warm 子表降级的时间,值越小,数据块就越快被降级。对于有 N 个块的索引缓存来说,如果一个在 hot 子表头部的索引块,在最后 N * key_cache_age_threshold / 100 次缓存命中内未被访问过,就会被降级到 warm 子表。</p>
<h4>4. 调整 read_buffer_size 和 read_rnd_buffer_size</h4>
<p>如果需要经常顺序扫描 myisam 表,可以通过增大 read_buffer_size 的值来改善性能,但需要注意的是:read_buffer_size 是每个 session 独占的,如果默认值太大,就会造成内存浪费,甚至导致物理内存耗尽。 <br>对于需要做排序的 myisam 表查询,如带有 order by 子句的 sql, 适当增大 read_rnd_buffer_size 的值,也可以改善此类 sql 的性能。read_rnd_buffer_size 也是按 session 分配的,默认值不能太大。</p>
<h3>三、 innodb 内存优化</h3>
<h4>1. innodb 缓存机制</h4>
<p>innodb 用一块内存区做 io 缓存池,该缓存池不仅用来缓存 innodb 的索引块,也用来缓存 innodb 的数据块,这一点与 myisam 不同。 </p>
<p>在内部,innodb 缓存池逻辑上由 free list、flush list 和 lru list 组成:</p>
<ul>
<li>free list : 空闲缓存块列表</li>
<li>flush list : 是需要刷新到此磁盘的缓存块列表</li>
<li>lru list : 是 innodb 正在使用的缓存块,它是 innodb buffer pool 的核心。</li>
</ul>
<p>innodb 使用的 lru 算法与 myisam 的“中点插入策略”lru算法很类似,大致原理是:将 lru list 分为 young sublist 和 old sublist,数据从磁盘读入时,会将该缓存块插入到 lru list 的“中点”,即 old sublist 的头部;经过一定时间的访问(由 innodb_old_blocks_time 系统参数决定),该数据块将会由 old sublist 转移到 young sublist 的头部,也就是整个lru list 的头部;随着时间推移,young sublist 和 old sublist 中较少被访问的缓存块将从各自链表的头部逐渐向尾部移动;需要淘汰数据块时,优先从链表尾部淘汰。这种设计同样是为了防止偶尔被访问的索引块将访问频繁的热块淘汰。</p>
<h4>2. innodb_buffer_pool_size 的设置</h4>
<p>innodb_buffer_size 决定 innodb 存储引擎表数据和索引数据的最大缓存区大小。在保证操作系统及其他程序有足够内存可用的情况下,innodb_buffer_pool_size 的值越大,缓存命中率越高,访问 innodb 表需要的磁盘 io 就越少,性能也就越高。在一个专用的数据库服务器上,可以将 80% 的物理内存分配给 innodb buffer pool。 </p>
<p>通过以下命令查看 buffer pool 的使用情况:</p>
<pre><code>zj@bogon:/usr/local/mysql$ mysqladmin -u root -p -S /tmp/mysql.sock ext | grep -i innodb_buffer_pool
Enter password:
| Innodb_buffer_pool_dump_status | Dumping of buffer pool not started |
| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 170918 15:07:09 |
| Innodb_buffer_pool_resize_status | |
| Innodb_buffer_pool_pages_data | 456 |
| Innodb_buffer_pool_bytes_data | 7471104 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_bytes_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 39 |
| Innodb_buffer_pool_pages_free | 32312 |
| Innodb_buffer_pool_pages_misc | 0 |
| Innodb_buffer_pool_pages_total | 32768 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 0 |
| Innodb_buffer_pool_read_ahead_evicted | 0 |
| Innodb_buffer_pool_read_requests | 3329 |
| Innodb_buffer_pool_reads | 422 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 515 |
zj@bogon:/usr/local/mysql$
</code></pre>
<p>可用以下公式计算 innodb 缓存池的命中率: </p>
<p>(1 - innodb_buffer_pool_read / innodb_buffer_pool_read_request) * 100 </p>
<p>如果命中率太低,则应考虑扩充内存,增加 innodb_buffer_pool_size 的值。</p>
<h4>3. 调整 old sublist 大小</h4>
<p>调整 old_sublist 的比例由系统参数 innodb_old_blocks_pct 决定,其取值范围是 5 ~ 95, 默认值是 37。 <br>通过以下命令可以查看其当前设置:</p>
<pre><code>MySQL [(none)]> show global variables like '%innodb_old_blocks_pct%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_old_blocks_pct | 37 |
+-----------------------+-------+
1 row in set (0.00 sec)
</code></pre>
<h4>4. 调整 innodb_old_blocks_time 的设置</h4>
<p>innodb_old_blocks_time 参数决定了缓存数据块由 old sublist 转移到 young sublist 的快慢,当一个缓存数据块被插入到 midpoint(old sublist)后,至少要在 old sublist 停留超过 innodb_old_blocks_time(ms)后,才有可能被转移到 young list。</p>
<h4>5. 调整缓存池数量,减少内部对缓存池数据源结构的争用</h4>
<p>MySQL 内部不同线程对 innodb 缓存池的访问在某些阶段是互斥的,<code>这种内部竞争也会产生性能问题</code>, 尤其在高并发和 buffer pool 较大的情况下。为解决这个问题,innodb 的缓存系统引入了 innodb_buffer_poolinstances 配置参数,对于较大的缓存池,适当增大此参数的值,可以降低并发导致的内部缓存访问冲突,改善性能。innodb 缓存系统会将参数 innodb_buffer_pool_size 指定大小的缓存平分为 innodb_buffer_pool_instances 个 buffer pool。</p>
<h4>6. 控制 innodb buffer 刷新,延长数据缓存事件,减缓磁盘 I/O</h4>
<p>在 innodb 找不到干净可用缓存页或检查点被触发等情况下,innodb 的后台线程就会开始把“脏的缓存页”回写到磁盘文件中,这个过程叫<code>缓存刷新</code>。 <br>·<br>通常都希望 buffer pool 中的数据在缓存中停留的时间尽可能长,以备重用,从而减少磁盘 IO 的次数。<code>磁盘 IO 慢,是数据库系统最主要的性能瓶颈</code>,可以通过延迟缓存刷新来减轻 IO 压力。 </p>
<p>innodb buffer pool 的刷新快慢主要取决于两个参数。</p>
<h5>innodb_max_dirty_pages_pct</h5>
<p>它控制缓存池中脏页的最大比例,默认是 75% ,如果脏页的数量达到或超过该值,innodb 的后台线程将开始缓存刷新。</p>
<h5>innodb_io_capacity</h5>
<p>它代表磁盘系统的 IO 能力,其值在一定程度上代表磁盘每秒可完成 IO 的次数。innodb_io_capacity 默认值是 200,对于低转速的磁盘,如 7200RPM 的磁盘,可将该值降低到 100,而对于固态硬盘和由多个磁盘组成的盘阵,它的值可以适当增大。 </p>
<p>innodb_io_capacity 决定一批刷新脏页的数量,当缓存池脏页的比例达到 innodb_max_dirty_pages_pct 时,innodb 大约将 innodb_io_capacity 个已改变的缓存页刷新到磁盘。在合并插入缓存时,innodb 每次合并的页数是 0.05 * innodb_io_capacity。 <br>若 innodb_buffer_pool_wait_free 的值增长较快,则说明 innodb 经常在等待空闲缓存页,如果无法增大缓存池,那么应将 innodb_max_dirty_pages_pct 的值调小,或将innodb_io_capacity 的值提高,以加快脏页的刷新。</p>
<h4>7. innodb doublewrite</h4>
<p>当进行脏页刷新时,innodb 采用了双写(double write)策略,这么做的原因是:MySQL 的数据页大小(一般是 16KB)与操作系统的 IO 数据页大小(一般是 4KB)不一致,无法保证 innodb 缓存页被完整、一致的刷新到磁盘,而innodb 的 redo 日志只记录了数据页改变的部分,并未记录数据页的完整前像,当发生部分写或断裂写时(比如讲缓存页的第一个 4KB 写入磁盘后,服务器断电),就会出现页面无法恢复的问题,为解决这个问题,innodb 引入了 doublewrite 技术。 </p>
<p>doublewrite 机制的实现原理: </p>
<p>用系统表空间的一块连续磁盘空间(100个连续数据页,大小为 2MB)作为 doublewrite buffer,当进行脏页刷新时,首先将脏页的副本写到系统表空间的 doublewrite buffer 中,然后调用 fsync() 刷新操作系统 IO 缓存,确保副本被真正写入磁盘,最后 innodb 后台 IO 线程将脏页刷新到磁盘数据文件。 </p>
<p>在做恢复时,若㘝发现不一致的页,innodb 会用系统表空间 doublewrite buffer 区的相应副本来恢复数据页。</p>
<h3>四、 调整用户服务线程排序缓冲区</h3>
<p>如果通过 show global status 看到 sort_merge_passes 的值很大,可以考虑通过调整参数 sort_buffer_size 的值来增大排序缓存区,以改善带有 order by 子句或 group 子句 sql 的性能。 </p>
<p>对于无法通过索引进行连接操作的查询,可以尝试通过增大 join_buffer_size 的值来改善性能。 </p>
<p>不过需要注意的是,sort buffer 和 join buffer 都是面向客户线程分配的,如果设置过大可能造成内存浪费。 </p>
<p>最好的策略是:设置较小的全局 join_buffer_size,而对需要做复杂连接操作的 session 单独设置较大的 join_buffer_size。</p>
MySQL 锁机制
https://segmentfault.com/a/1190000011164489
2017-09-14T11:53:01+08:00
2017-09-14T11:53:01+08:00
waterandair
https://segmentfault.com/u/waterandair
28
<blockquote><p>锁是计算机协调多个进程或线程并发访问某一资源的机制。</p></blockquote>
<h3>一、MySQL 锁概述</h3>
<p>MySQL 两种锁特性归纳 :</p>
<ul>
<li>表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。</li>
<li>行级锁:开销大,加锁慢;会出现死锁;锁定粒度小,发生锁冲突的概率最低,并发度也最高。</li>
</ul>
<p>MySQL 不同的存储引擎支持不同的锁机制。 <br>myisam 和 memory 存储引擎采用的是 表级锁; <br>innodb 存储引擎既支持行级锁,也支持表级锁,但默认情况下采用行级锁。 </p>
<p>表级锁更适合于以查询为主,只有少量按索引条件更新数据的应用,如 web 应用;<br>而行级锁则更适合于有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用。</p>
<h3>二、 myisam 表锁</h3>
<h4>1. 查询表级锁争用情况</h4>
<p>可以通过检查 table_locks_waited 和 table_locks_immediate 状态变量来分析系统上的表锁定争夺:</p>
<pre><code>MySQL [sakila]> show status like 'table_locks%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_locks_immediate | 100 |
| Table_locks_waited | 0 |
+-----------------------+-------+
</code></pre>
<p>如果 table_locks_waited 的值比较高,则说明存在着较严重的表级锁争用情况。</p>
<h4>2. MySQL 表级锁的锁模式</h4>
<p>MySQL 的表级锁有两种模式,表共享读锁(table read lock)和表独占写锁(table write lock)。 </p>
<p><code>对 myisam 表的读操作,不会阻塞其他用户对同一表的读请求,但会阻塞对同一表的写请求;对 myisam 表的写操作,则会阻塞其他用户对同一表的读和写操作;myisam 表的读操作和写操作之间,以及写操作之间时串行的。</code> </p>
<p>当一个线程获得对一个表的写锁户,只有持有锁的线程可以对表进行更新操作,其他线程的读、写操作都会等待,直到锁被释放。</p>
<h4>3. 加锁</h4>
<p>myisam 在执行查询语句(select)前,会自动给涉及的所有表加读锁,在执行更新操作(update、delete、insert等)前,会自动给涉及的表加写锁,这个过程并不需要直接用 lock table 命令给 myisam 表显示加锁。<code>给 myisam 表显式加锁,一般是为了在一定程度模拟事务操作</code> <br>myisam 在自动加锁的情况下,总是一次获得 sql 语句所需要的全部锁,所以显示锁表的时候,必须同时取得所有涉及表的锁,这也正是 myisam 表不会出现死锁(deadlock)的原因。 </p>
<p>注意:在使用 lock tables 时,不仅需要一次锁定用到的所有表,而且,同一个表在 sql 语句中出现多少次,就要通过与 sql 语句中相同的别名锁定多少次,否则会报错。</p>
<h4>4. 并发插入(concurrent inserts)</h4>
<p>myisam 表的读和写是串行的,但这是就总体而言的。在一定条件下,myisam 表也支持查询和插入操作的并发进行。 <br>myisam 存储引擎有一个系统变量 concurrent_insert , 专门用以控制其并发插入的行为,其值分别可以为0,1,2。</p>
<ul>
<li>当 concurrent_insert 设置为 0 时,不允许并发插入。</li>
<li>当 concurrent_insert 设置为 1 时,如果 myisam 表中没有空洞(即表的中间没有被删除的行),myisam 允许在一个进程读表的同时,另一个进程从表尾插入记录。这也是 MySQL 的默认设置。</li>
<li>当 concurrent_insert 设置为 2 时,无论 myisam 表中有没有空洞,都允许在表尾并发插入记录。</li>
</ul>
<h4>5. myisam 的锁调度</h4>
<p>myisam 存储引擎的读锁和写锁是互斥的,读写操作时串行的。当一个进程请求某个 myisam 表的读锁,同时另一个进程也请求同一表的写锁时,写进程会先获得锁。不仅如此,即使读请求先到锁等待队列,写请求后到,写锁也会插到读锁请求之前,这是因为 mysql 认为写请求一般比读请求重要。这也正是<code> myisam 表不太适合有大量更新操作和查询操作应用</code>的原因,因为,大量的更新操作会造成查询操作很难获得读锁,从而可能永远阻塞。 </p>
<p>通过一些参数设置可以调节 MySQL 的默认调度行为:</p>
<ul>
<li>通过指定启动参数 low-priority-updates, 使 myisam 引擎默认给予读请求以优先的权利。</li>
<li>通过执行命令 set low_priority_updates = 1, 使该连接发出的更新请求优先级降低。</li>
<li>通过指定 insert、update、delete 语句的 low_priority 属性,降低该语句的优先级。</li>
</ul>
<p>上述方式都是要么更新优先,要么查询优先,MySQL 也提供了一种折中的办法调节读写冲突: </p>
<p>给系统参数 max_write_lock_count 设置一个合适的值,当一个表的读锁达到这个值后,MySQL 就暂时将写请求的优先级降低,给读进程一定获得锁的机会。</p>
<h3>三、 InnoDB 锁</h3>
<p>innodb 与 myisam 的最大不同有两点,一是支持事务(transaction),二十采用了行级锁。</p>
<h4>1. 并发事务处理存在的问题</h4>
<p>相对于串行处理来说,并发事务处理能力大大增加数据库资源的利用率,提高数据库系统事务吞吐量,从而可以支持更多的用户,但并发事务处理也会带来一些问题:</p>
<h5>更新丢失(lost update)</h5>
<p>当两个或多个事务选择同一行,然后基于最初选定的值更新该行时,由于每个事务都不知道其他事务的存在,就会发生丢失更新问题,最后的更新覆盖了由其他事务所做的更新。</p>
<h5>脏读(dirty reads)</h5>
<p>一个事务正在对一条记录做修改,在这个事务完成并提交前,这条记录的数据就处于不一致状态;这时,另一个事务也来读取同一条记录,如果不加控制,第二个事务读取了这些“脏”数据,并据此作进一步的处理,就会产生未提交的数据依赖关系。</p>
<h5>不可重复读(non-repeatable reads)</h5>
<p>一个事务在读取某些数据后的某个时间,再次读取以前读过的数据,却发现其读出的数据已经发生了改变或某些记录已经被删除了!这种现象就是“不可重复读”。</p>
<h5>幻读(phantom reads)</h5>
<p>一个事务按相同的查询条件重新读取以前检索过的数据,却发现其他事务插入了满足其查询条件的新数据,这种现象称为“幻读”。</p>
<h4>2. 事务隔离级别</h4>
<p>并发事务处理带来的问题中,“更新丢失”,通常是可以避免的,需要应用程序对要更新的数据加必要的锁来解决。 <br>“脏读”,“不可重复读”和“幻读”, 其实都是数据库读一致性问题,必须由数据库提供一定的事务隔离机制来解决。 <br>数据库实现事务隔离的方式,基本可以分为两种:</p>
<ul>
<li>在读取数据前,对其加锁,阻止其他事务对数据进行修改</li>
<li>不加任何锁,通过一定机制生成一个数据请求时间点的一致性数据快照,这种方式叫做数据多版本并发控制。</li>
</ul>
<p>数据库的事务隔离越严格,并发副作用越小,但付出的代价也就越大,因为事务隔离实质上就是使事务在一定程度上“串行化”进行,这显然与“并发”是矛盾的。为了解决“隔离”与“并发”的矛盾,ISO/ANSI SQL92 定义了 4 个事务隔离级别,MySQL 实现了这四种级别,应用可以根据自己的业务逻辑要求,选择合适的隔离级别来平衡“隔离”与“并发”的矛盾。 </p>
<p><img src="/img/bVU0hE?w=808&h=229" alt="图片描述" title="图片描述"></p>
<h4>3. 查看 Innodb 行锁争用情况</h4>
<p>可以通过检查 innodb_row_lock 状态变量来分析系统上的行锁的争夺情况:</p>
<pre><code>MySQL [sakila]> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
+-------------------------------+-------+
5 rows in set (0.00 sec)
</code></pre>
<p>如果发现锁争用比较严重,如 Innodb_row_lock_waits 和 Innodb_row_lock_time_avg 的值比较高,可以通过查询 information_schema 数据库中相关的表来查看锁情况,或者通过设置 innodb monitors 来进一步观察。</p>
<h5>(1)查询 information_schema 数据库中的表了解锁等待</h5>
<pre><code>MySQL [sakila]> use information_schema
Database changed
MySQL [information_schema]> select * from innodb_locks \G;
</code></pre>
<h5>(2) 通过设置 innodb monitors 观察锁冲突情况</h5>
<pre><code>MySQL [sakila]> create table innodb_monitor (a int) engine=innodb;
Query OK, 0 rows affected (0.05 sec)
show engine innodb status \G;
</code></pre>
<h4>4. innodb 的行锁模式及加锁方法</h4>
<p>Innodb 实现了两种类型的行锁:</p>
<ul>
<li>共享锁(S):允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁。</li>
<li>排他锁(X):允许获得排他锁的事务更新数据,阻止其他事务获取相同数据集的共享读锁和排他写锁。</li>
</ul>
<p>另外,为了允许行锁和表锁共存,事项多粒度锁机制,innodb 还有两种内部使用的意向锁,这两种意向锁都是表锁:</p>
<ul>
<li>意向共享锁(IS):事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。</li>
<li>意向排它锁(IX): 事务打算给数据行加行排它锁,事务在给一个数据行加排它锁前必须先取得该表的 IX 锁。</li>
</ul>
<p><img src="/img/bVU0iI?w=658&h=275" alt="图片描述" title="图片描述"><br>如果一个事务请求的锁模式与当前的锁兼容,innodb 就将请求的锁授予该事务;反之,如果两者不兼容,该事务就要等待锁释放。 <br>意向锁是 innodb 自动加的,不需要用户干预。对于 update、delete 和 insert 语句,innodb 会自动给涉及数据集加排它锁(X);对于普通 select 语句,innodb 不会加任何锁。 </p>
<p>事务可以通过以下语句显式给记录集加共享锁或排它锁。</p>
<ul>
<li>共享锁(S):select * from table_name where ... lock in share mode.</li>
<li>排它锁(X): select * from table_name where ... for update.</li>
</ul>
<p>用 select... in share mode 获得共享锁,主要用在需要数据依存关系时来确认某行记录是否存在,并确保没有人对这个记录进行 update 或者 delete 操作。但是如果<code>当前事务也需要对该记录进行更新操作</code>,则有可能造成死锁,对于锁定行记录后需要进行更新操作的应用,应该使用 select... for update 方式获得排他锁。</p>
<h5>innodb 存储引擎<code>共享锁</code>例子(更新时死锁)</h5>
<p><img src="/img/bVU0l8?w=1694&h=1540" alt="图片描述" title="图片描述"></p>
<h5>innodb 存储引擎<code>排它锁</code>例子</h5>
<p><img src="/img/bVU0vh?w=1688&h=1579" alt="图片描述" title="图片描述"></p>
<h4>5. innodb 行锁实现方式</h4>
<p>innodb 行锁是通过给索引项加锁来实现的,如果么有索引,innodb 将通过隐藏的聚簇索引来对记录加锁。innodb 行锁分为 3 种情形:</p>
<ul>
<li>record lock: 对索引项加锁</li>
<li>gap lock: 对索引项之间的“间隙”、第一条记录前的“间隙”或最后一条记录的“间隙”加锁。</li>
<li>next-key lock: 前两种的结合,对记录及其前面的间隙加锁。</li>
</ul>
<p><code>innodb 这种行锁实现特点意味着:如果不通过索引条件检索数据,那么 innodb 将对表中的所有记录加锁,实际效果和表锁一样!</code> </p>
<p>在实际应用中,要特别注意 innodb 行锁的这一特性,否则可能导致大量的锁冲突,从而影响并发性能。</p>
<h5>(1) 在不通过索引条件查询时,innodb 会锁定表中的所有记录。</h5>
<p>创建测试表:</p>
<pre><code>MySQL [sakila]> create table tab_no_index (id int, name varchar(10)) engine=innodb;
Query OK, 0 rows affected (0.04 sec)
MySQL [sakila]> insert into tab_no_index values (1,'1'),(2,'2'),(3,'3'),(4,'4');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
</code></pre>
<p><img src="/img/bVU0vR?w=1690&h=811" alt="图片描述" title="图片描述"></p>
<p>看起来 session_1 只给一行加了排他锁,但 session_2 在请求其他行的排他锁时,却出现了锁等待!原因就是在没有索引的情况下,Innodb 会对所有记录都加锁。当给其增加一个索引后,innodb 就只锁定了符合条件的行 </p>
<p>创建测试表:</p>
<pre><code>MySQL [sakila]> create table tab_with_index (id int , name varchar(10)) engine = innodb;
Query OK, 0 rows affected (0.01 sec)
MySQL [sakila]> alter table tab_with_index add index id(id);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
MySQL [sakila]> insert into tab_with_index values (1,'1'),(2,'2'),(3,'3'),(4,'4');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0</code></pre>
<p><img src="/img/bVU0wj?w=1688&h=1053" alt="图片描述" title="图片描述"></p>
<h5>(2) 由于 MySQL 的行锁是针对索引加的锁,不是针对记录加的锁,所以虽然是访问不同行的记录,但是如果是使用相同的索引键,是会出现锁冲突的。</h5>
<p>创建测试表,id字段有索引,name字段没有索引:</p>
<pre><code>MySQL [sakila]> create table tab_with_index (id int , name varchar(10)) engine = innodb;
Query OK, 0 rows affected (0.01 sec)
MySQL [sakila]> alter table tab_with_index add index id(id);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
MySQL [sakila]> insert into tab_with_index values (1,'1'),(1,'4');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
</code></pre>
<p><img src="/img/bVU0wW?w=1689&h=696" alt="图片描述" title="图片描述"></p>
<h5>(3) 当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,不论是使用主键索引、唯一索引或普通索引,innodb 都会使用行锁对数据加锁。</h5>
<p>创建测试表,id 字段和 name 字段都有索引:</p>
<pre><code>MySQL [sakila]> create table tab_with_index (id int , name varchar(10),index id (id),index name (name)) engine = innodb;
Query OK, 0 rows affected (0.00 sec)
MySQL [sakila]> insert into tab_with_index values (1,'1'),(1,'4'),(2,'2');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
</code></pre>
<p><img src="/img/bVU0w8?w=1688&h=978" alt="图片描述" title="图片描述"></p>
<h5>(4) 即便在条件中使用了索引字段,但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的,<code>如果 MySQL 认为全表扫描效率更高,比如对一些很小的表,它就不会使用索引,这种情况下 innodb 也会对所有记录加锁。</code>因此,在分析锁冲突时,别忘了检查 sql 的执行计划,以确认是否真正使用了索引。</h5>
<h4>6. next-key 锁</h4>
<p>当我们用<code>范围条件</code>而不是相等条件检索数据,并请求共享或排他锁时,innodb 会给符合条件的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在的记录,叫做“间隙(gap)”,innodb 也会对这个“间隙”加锁,这种锁机制就是所谓的 next-key 锁。 </p>
<p>举例来说,假如 emp 表中只有 101 条记录,其 id 的值分别是1、2、...、100、101,下面的 sql:</p>
<pre><code># 这是一个范围条件的检索,innodb 不仅会对符合条件的 id 值为 101 的记录加锁,也会对 id 大于 101(这些记录并不存在)的“间隙”加锁。
select * from emp where id > 100 for update;</code></pre>
<h5>为什么使用 next-key 锁</h5>
<p>innodb 使用 next-key 锁的目的,一方面是为了防止幻读,以满足相关隔离级别的要求,对于上面的例子,要是不使用间隙锁,如果其他事务插入了 id 大于 100 的任何记录,那么本事务如果再次执行上述语句,就会发生幻读;另一方面,是为了满足其恢复和复制的需要。 </p>
<p>在使用范围条件检索并锁定记录时,innodb 这种加锁机制会阻塞符合条件范围内键值的并发插入,这往往会造成严重的锁等待。因此,<code>在实际开发中,尤其是并发插入比较多的应用,应该尽量优化业务逻辑,尽量使用相等条件来访问更新数据,避免使用范围条件。</code> </p>
<p>innodb 除了通过范围条件加锁时使用 next-key 锁外,如果使用相等条件请求给一个不存在的记录加锁,innodb 也会使用 next-key 锁!</p>
<h4>7. 恢复和复制的需要,对 innodb 锁机制的影响</h4>
<p>MySQL 通过 binlog 记录执行成功的 insert、update 、delete 等更新数据的 sql 语句,并由此实现 MySQL 数据库的恢复和主从复制。 </p>
<p>MySQL 5.6 支持 3 种 日志格式,即基于语句的日志格式 sbl,基于行的日志格式 rbl 和混合格式。它还支持 4 种复制模式:</p>
<ul>
<li>基于 sql 语句的复制 sbr:这也是 MySQL 最早支持的复制模式。</li>
<li>基于 行数据的复制 rbr: 这是 MySQL5.1 以后喀什支持的复制模式,主要优点是支持对非安全 sql 的复制模式。</li>
<li>混合复制模式:对安全的 sql 语句采用基于 sql 语句的复制模式,对于非安全的 sql 语句采用局于行的复制模式。</li>
<li>使用全局事务id(gtids)的复制:主要是解决主从自动同步一致的问题。</li>
</ul>
<p>对基于语句日志格式(sbl)的恢复和复制而言,由于 MySQL 的 binlog 是按照事务提交的先后顺序记录的,因此要正确恢复或复制数据,就必须满足: <br>在一个事务未提交前,其他并发事务不能插入满足其锁定条件的任何记录,也就是不允许出现幻读。这已经超过了“可重复读”隔离级别的要求,实际上是要求事务要串行化。这也是许多情况下,innodb 要用 next-key 锁的原因。</p>
<h4>8. 什么时候使用表锁</h4>
<p>对于 innodb 表,在绝大部分情况下都应该使用行级锁,因为事务和行锁往往是我们选择 innodb 表的理由,但在个别特殊任务中,也可以考虑使用表级锁:</p>
<ol>
<li>事务需要更新大部分或全部数据,表又比较大,如果使用默认的行锁,不仅这个事务执行效率低,而且可能造成其他事务长时间锁等待和锁冲突,这种情况下可以考虑使用表锁</li>
<li>事务涉及多个表,比较复杂,很可能引起死锁,造成大量事务回滚。这种情况也可以考虑一次性锁定多个表,从而避免死锁,减少数据库因事务回滚带来的开销。</li>
</ol>
<p>当然,应用中这两种事务不能太多,否则,就应该考虑使用 myisam 表了。 <br>在 innodb 下,使用表锁要注意以下两点:</p>
<ol>
<li>使用 lock tables 虽然可以给 innodb 加表级锁,但必须说明的是,表锁不是由 innodb 存储引擎管理的,而是由其上一层———— MySQL server 负责的,<code>仅当 autocommit=0、innodb_table_locks=1(默认设置)时,innodb 层才知道 MySQL 加的表锁,MySQL server 也才能够感知 innodb 加的行锁,这种情况下,innodb 才能自动识别涉及到的锁</code>。</li>
<li>在用 lock_tables 对 innodb 表加锁时要注意,要将 autocommit 设为 0,否则 MySQL 不会给表加锁;事务结束前,不要用 unlock tables 释放表锁,因为 unlock tables 会隐含的提交事务;commit 或 rollback 并不能释放用 lock tables 加的表锁,必须用 unlock tables 释放表锁</li>
</ol>
<pre><code>set autocommit = 0;
lock tables ti write, t2 read, ...;
[do something with tables t1 and t2 here];
commit;
unlock tables;</code></pre>
<h4>9. 关于死锁</h4>
<p>myisam 表锁是 deadlock free 的,这是因为 myisam 总是一次获取所需的全部锁,要么全部满足,要么等待,因此不会出现死锁。但在 innodb 中,除单个 sql 组成的事务外,锁是逐步获得的,这就决定了在 innodb 中发生死锁是可能的。 </p>
<p><img src="/img/bVU0xm?w=1690&h=479" alt="图片描述" title="图片描述"></p>
<p>上面的例子中,<code>两个事务都需要获得对方持有的排他锁才能继续完成事务,这种循环锁等待就是典型的死锁。</code> </p>
<p>发生死锁后,innodb 一般都能自动检测到,并使一个事务释放锁回退,另一个事务获得锁,继续完成事务。但在涉及外部锁或表锁的情况下,innodb 并不能完全自动检测到死锁,只需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决。需要说明的是,这个参数并不是只用来解决死锁问题,在并发访问比较高的情况下,如果大量事务因无法立即获得所需的锁而挂起,会占用大量计算机资源,造成严重的性能问题,甚至拖垮数据库。 </p>
<p><code>通常来说,死锁都是应用设计的问题,通过调整业务流程,数据库对象设计、事务大小、以及访问数据库的 sql 语句,绝大部分死锁都可以避免。 </code> </p>
<p>几种避免死锁的方法:</p>
<h5>(1) 在应用中,如果不同的程序会并发存取多个表,应尽量约定以相同的顺序来访问表,这样可以大大降低产生死锁的机会。</h5>
<p>下面的例子中,由于两个 session 访问两个表的顺序不同,发生死锁的机会就非常高!但如果以相同的顺序来访问,死锁就可以避免。 <br><img src="/img/bVU0ys?w=1688&h=1111" alt="图片描述" title="图片描述"></p>
<p>如果 session_2 以相同的顺序执行 sql 语句,会造成锁等待,但不会死锁。</p>
<h5>(2) 在程序以批量方式处理数据的时候,如果事先对数据排序,保证每个线程按固定的顺序来处理记录,也可以大大降低出现死锁的可能。</h5>
<p><img src="/img/bVU0xZ?w=1688&h=1236" alt="图片描述" title="图片描述"></p>
<h5>(3) 在事务中,如果要更新记录,应该申请足够级别的锁,即排他锁,而不应先申请共享锁,更新时再申请排他锁,因为当用户申请排他锁时,其他事务可能又已经获得了相同记录的共享锁,从而造成锁冲突,甚至死锁。</h5>
<h5>(4) 在 repeatable-read 隔离级别下,如果两个线程同时对相同条件记录用 select ... for update 加排他锁,在没有符合该条件记录情况下,两个线程过会加锁成功。程序发现记录尚不存在,就试图插入一条新记录,如果两个线程都这么做,就会出现死锁。这种情况下,将隔离级别改成 read committed ,就可避免问题。</h5>
<h5>(5) 当隔离级别为 read committed 时,如果两个线程都先执行 select ... for update, 判断是否存在符合条件的记录,如果没有,就插入记录。此时,只有一个线程能插入成功,另一个线程会出现锁等待,当第 1 个线程提交后,第 2 个线程会因主键重出错,但虽然这个线程出错了,却会获得一个排他锁,如果有第 3 个线程又来申请排他锁,也会出现死锁。<code>对于这种情况,可以直接做插入操作,然后再捕获主键重异常,或者在遇到主键重错误时,总是执行 rollback 释放获得的排他锁</code>
</h5>
<p><strong> 尽管通过上面介绍的设计和 sql 优化等措施,可以大大减少死锁,但死锁很难完全避免。因此,在程序设计中总是捕获并处理死锁异常是一个很好的编程习惯 </strong> </p>
<p>如果出现死锁,可以用 show innodb status 命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息,如引发死锁的 sql 语句,事务已经获得的锁,正在等待什么锁,以及被回滚的事务等。可以据此分析产生死锁的原因。</p>
优化 sql 语句的一般步骤
https://segmentfault.com/a/1190000010941790
2017-08-31T10:43:05+08:00
2017-08-31T10:43:05+08:00
waterandair
https://segmentfault.com/u/waterandair
10
<h4>一、通过 show status 命令了解各种 sql 的执行<code>频率</code>
</h4>
<p>mysql 客户端连接成功后,通过 show [session|global] status 命令可以提供服务器状态信息,也可以在操作系统上使用 mysqladmin extend-status 命令获取这些消息。 <br>show status 命令中间可以加入选项 session(默认) 或 global:</p>
<ul>
<li>session (当前连接)</li>
<li>global (自数据上次启动至今)</li>
</ul>
<pre><code># Com_xxx 表示每个 xxx 语句执行的次数。
mysql> show status like 'Com_%';</code></pre>
<h5>我们通常比较关心的是以下几个统计参数:</h5>
<ul>
<li>Com_select : 执行 select 操作的次数,一次查询只累加 1。</li>
<li>Com_insert : 执行 insert 操作的次数,对于批量插入的 insert 操作,只累加一次。</li>
<li>Com_update : 执行 update 操作的次数。</li>
<li>Com_delete : 执行 delete 操作的次数。</li>
</ul>
<p>上面这些参数对于所有存储引擎的表操作都会进行累计。下面这几个参数只是针对 innodb 的,累加的算法也略有不同:</p>
<ul>
<li>Innodb_rows_read : select 查询返回的行数。</li>
<li>Innodb_rows_inserted : 执行 insert 操作插入的行数。</li>
<li>Innodb_rows_updated : 执行 update 操作更新的行数。</li>
<li>Innodb_rows_deleted : 执行 delete 操作删除的行数。</li>
</ul>
<p>通过以上几个参数,可以很容易地了解当前数据库的应用是以插入更新为主还是以查询操作为主,以及各种类型的 sql 大致的执行比例是多少。对于更新操作的计数,是对执行次数的计数,不论提交还是回滚都会进行累加。 <br>对于事务型的应用,通过 Com_commit 和 Com_rollback 可以了解事务提交和回滚的情况,对于回滚操作非常频繁的数据库,可能意味着应用编写存在问题。 <br>此外,以下几个参数便于用户了解数据库的基本情况:</p>
<ul>
<li>Connections : 试图连接 mysql 服务器的次数。</li>
<li>Uptime : 服务器工作时间。</li>
<li>Slow_queries : 慢查询次数。</li>
</ul>
<h4>二、定义执行效率较低的 sql 语句</h4>
<h5>1. 通过慢查询日志定位那些执行效率较低的 sql 语句,用 --log-slow-queries[=file_name] 选项启动时,mysqld 写一个包含所有执行时间超过 long_query_time 秒的 sql 语句的日志文件。</h5>
<h5>2. 慢查询日志在查询结束以后才记录,所以在应用反映执行效率出现问题的时候慢查询日志并不能定位问题,可以使用 show processlist 命令查看当前 mysql 在进行的线程,包括线程的状态、是否锁表等,可以实时的查看 sql 的执行情况,同时对一些锁表操作进行优化。</h5>
<h4>三、通过 explain 分析低效 sql 的执行计划</h4>
<blockquote><p>测试数据库地址:<a href="https://link.segmentfault.com/?enc=P2rdMUCLaAmWxQGfUSJr5A%3D%3D.g3yCHjQq1R9HrUjihGCa4J25yyYyODr%2FZqwdF54AECe63HH5e7c5tAEIAPwNwUYQ" rel="nofollow">https://downloads.mysql.com/d...</a></p></blockquote>
<p>统计某个 email 为租赁电影拷贝所支付的总金额,需要关联客户表 customer 和 付款表 payment , 并且对付款金额 amount 字段做求和(sum) 操作,相应的执行计划如下:</p>
<pre><code>mysql> explain select sum(amount) from customer a , payment b where a.customer_id= b.customer_id and a.email='JANE.BENNETT@sakilacustomer.org'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
partitions: NULL
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 599
filtered: 10.00
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
partitions: NULL
type: ref
possible_keys: idx_fk_customer_id
key: idx_fk_customer_id
key_len: 2
ref: sakila.a.customer_id
rows: 26
filtered: 100.00
Extra: NULL
2 rows in set, 1 warning (0.00 sec)
</code></pre>
<ul>
<li>
<p>select_type: 表示 select 类型,常见的取值有:</p>
<ul>
<li>simple:简单表,及不使用表连接或者子查询</li>
<li>primary:主查询,即外层的查询</li>
<li>union:union 中的第二个或后面的查询语句</li>
<li>subquery: 子查询中的第一个 select</li>
</ul>
</li>
<li>table : 输出结果集的表</li>
<li>type : 表示 mysql 在表中找到所需行的方式,或者叫访问类型,常见类型性能由差到最好依次是:all、index、range、ref、eq_ref、const,system、null:</li>
</ul>
<ol>
<li>
<p>type=ALL,全表扫描,mysql 遍历全表来找到匹配的行:</p>
<pre><code>mysql> explain select * from film where rating > 9 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
filtered: 33.33
Extra: Using where
1 row in set, 1 warning (0.01 sec)
</code></pre>
</li>
<li>
<p>type=index, 索引全扫描,mysql 遍历整个索引来查询匹配的行</p>
<pre><code>mysql> explain select title form film\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
partitions: NULL
type: index
possible_keys: NULL
key: idx_title
key_len: 767
ref: NULL
rows: 1000
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)
</code></pre>
</li>
<li>
<p>type=range,索引范围扫描,常见于<、<=、>、>=、between等操作:</p>
<pre><code>mysql> explain select * from payment where customer_id >= 300 and customer_id <= 350 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: payment
partitions: NULL
type: range
possible_keys: idx_fk_customer_id
key: idx_fk_customer_id
key_len: 2
ref: NULL
rows: 1350
filtered: 100.00
Extra: Using index condition
1 row in set, 1 warning (0.07 sec)
</code></pre>
</li>
<li>
<p>type=ref, 使用非唯一索引扫描或唯一索引的前缀扫描,返回匹配某个单独值的记录行,例如:</p>
<pre><code>mysql> explain select * from payment where customer_id = 350 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: payment
partitions: NULL
type: ref
possible_keys: idx_fk_customer_id
key: idx_fk_customer_id
key_len: 2
ref: const
rows: 23
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.01 sec)
</code></pre>
<p>索引 idx_fk_customer_id 是非唯一索引,查询条件为等值查询条件 customer_id = 350, 所以扫描索引的类型为 ref。ref 还经常出现在 join 操作中:</p>
<pre><code>mysql> explain select b.*, a.* from payment a,customer b where a.customer_id = b.customer_id \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: b
partitions: NULL
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 599
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: a
partitions: NULL
type: ref
possible_keys: idx_fk_customer_id
key: idx_fk_customer_id
key_len: 2
ref: sakila.b.customer_id
rows: 26
filtered: 100.00
Extra: NULL
2 rows in set, 1 warning (0.00 sec)
</code></pre>
</li>
<li>
<p>type=eq_ref,类似 ref,区别就在使用的索引时唯一索引,对于每个索引的键值,表中只要一条记录匹配;简单的说,就是多表连接中使用 primary key 或者 unique index 作为关联条件。</p>
<pre><code>mysql> explain select * from film a , film_text b where a.film_id = b.film_id \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: b
partitions: NULL
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 1000
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: a
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: sakila.b.film_id
rows: 1
filtered: 100.00
Extra: Using where
2 rows in set, 1 warning (0.03 sec)
</code></pre>
</li>
<li>
<p>type=const/system,单表中最多有一个匹配行,查起来非常迅速,所以这个匹配行中的其他列的值可以被优化器在当前查询中当作常量来处理,例如,根据主键 primary key 或者唯一索引 unique index 进行查询。</p>
<pre><code>mysql> create table test_const (
-> test_id int,
-> test_context varchar(10),
-> primary key (`test_id`),
-> );
insert into test_const values(1,'hello');
explain select * from ( select * from test_const where test_id=1 ) a \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test_const
partitions: NULL
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)</code></pre>
</li>
<li>
<p>type=null, mysql 不用访问表或者索引,直接就能够得到结果:</p>
<pre><code>mysql> explain select 1 from dual where 1 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
partitions: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: No tables used
1 row in set, 1 warning (0.00 sec)
</code></pre>
</li>
</ol>
<p> 类型 type 还有其他值,如 ref_or_null (与 ref 类似,区别在于条件中包含对 null 的查询)、index_merge(索引合并优化)、unique_subquery (in 的后面是一个查询主键字段的子查询)、index_subquery(与 unique_subquery 类似,区别在于 in 的后面是查询非唯一索引字段的子查询)等。</p>
<ul>
<li>possible_keys : 表示查询时可能使用的索引。</li>
<li>key :表示实际使用索引</li>
<li>key-len : 使用到索引字段的长度。</li>
<li>rows : 扫描行的数量</li>
<li>extra:执行情况的说明和描述,包含不适合在其他列中显示但是对执行计划非常重要的额外信息。</li>
</ul>
<h5>show warnings 命令</h5>
<p>执行explain 后再执行 show warnings,可以看到sql 真正被执行之前优化器做了哪些 sql 改写:</p>
<pre><code>MySQL [sakila]> explain select sum(amount) from customer a , payment b where 1=1 and a.customer_id = b.customer_id and email = 'JANE.BENNETT@sakilacustomer.org'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
partitions: NULL
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 599
filtered: 10.00
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
partitions: NULL
type: ref
possible_keys: idx_fk_customer_id
key: idx_fk_customer_id
key_len: 2
ref: sakila.a.customer_id
rows: 26
filtered: 100.00
Extra: NULL
2 rows in set, 1 warning (0.00 sec)
MySQL [sakila]> show warnings;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select sum(`sakila`.`b`.`amount`) AS `sum(amount)` from `sakila`.`customer` `a` join `sakila`.`payment` `b` where ((`sakila`.`b`.`customer_id` = `sakila`.`a`.`customer_id`) and (`sakila`.`a`.`email` = 'JANE.BENNETT@sakilacustomer.org')) |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
</code></pre>
<p>从 warning 的 message 字段中能够看到优化器自动去除了 1=1 恒成立的条件,也就是说优化器在改写 sql 时会自动去掉恒成立的条件。</p>
<h5>explain 命令也有对分区的支持.</h5>
<pre><code>MySQL [sakila]> CREATE TABLE `customer_part` (
-> `customer_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
-> `store_id` tinyint(3) unsigned NOT NULL,
-> `first_name` varchar(45) NOT NULL,
-> `last_name` varchar(45) NOT NULL,
-> `email` varchar(50) DEFAULT NULL,
-> `address_id` smallint(5) unsigned NOT NULL,
-> `active` tinyint(1) NOT NULL DEFAULT '1',
-> `create_date` datetime NOT NULL,
-> `last_update` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> PRIMARY KEY (`customer_id`)
->
-> ) partition by hash (customer_id) partitions 8;
Query OK, 0 rows affected (0.06 sec)
MySQL [sakila]> insert into customer_part select * from customer;
Query OK, 599 rows affected (0.06 sec)
Records: 599 Duplicates: 0 Warnings: 0
MySQL [sakila]> explain select * from customer_part where customer_id=130\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: customer_part
partitions: p2
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warnings (0.00 sec)
</code></pre>
<p>可以看到 sql 访问的分区是 p2。</p>
<h4>四、通过 performance_schema 分析 sql 性能</h4>
<p>旧版本的 mysql 可以使用 profiles 分析 sql 性能,我用的是5.7.18的版本,已经不允许使用 profiles 了,推荐用<br> performance_schema 分析sql。</p>
<h4>五、通过 trace 分析优化器如何选择执行计划。</h4>
<p>mysql5.6 提供了对 sql 的跟踪 trace,可以进一步了解为什么优化器选择 A 执行计划而不是 B 执行计划,帮助我们更好的理解优化器的行为。 </p>
<p>使用方式:首先打开 trace ,设置格式为 json,设置 trace 最大能够使用的内存大小,避免解析过程中因为默认内存过小而不能够完整显示。</p>
<pre><code>MySQL [sakila]> set optimizer_trace="enabled=on",end_markers_in_json=on;
Query OK, 0 rows affected (0.00 sec)
MySQL [sakila]> set optimizer_trace_max_mem_size=1000000;
Query OK, 0 rows affected (0.00 sec)
</code></pre>
<p>接下来执行想做 trace 的 sql 语句,例如像了解租赁表 rental 中库存编号 inventory_id 为 4466 的电影拷贝在出租日期 rental_date 为 2005-05-25 4:00:00 ~ 5:00:00 之间出租的记录:</p>
<pre><code>mysql> select rental_id from rental where 1=1 and rental_date >= '2005-05-25 04:00:00' and rental_date <= '2005-05-25 05:00:00' and inventory_id=4466;
+-----------+
| rental_id |
+-----------+
| 39 |
+-----------+
1 row in set (0.06 sec)
MySQL [sakila]> select * from information_schema.optimizer_trace\G
*************************** 1. row ***************************
QUERY: select * from infomation_schema.optimizer_trace
TRACE: {
"steps": [
] /* steps */
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)
</code></pre>
<h4>六、 确定问题并采取相应的优化措施</h4>
<p>经过以上步骤,基本就可以确认问题出现的原因。此时可以根据情况采取相应的措施,进行优化以提高执行的效率。</p>
两种高效的事件处理模式
https://segmentfault.com/a/1190000010648274
2017-08-14T12:35:16+08:00
2017-08-14T12:35:16+08:00
waterandair
https://segmentfault.com/u/waterandair
1
<p>服务器程序通常需要处理三类事件:I/O 事件、信号及定时事件。随着网络设计模式的兴起,Reactor 和 Proactor 事件处理模式应运而生。同步 I/O 模型通常用于实现 Reactor 模式,异步 I/O 模型则用于实现 Rroactor 模式。</p>
<h3>一、 Reactor 模式</h3>
<p> Reactor 模式要求主线程(I/O 处理单元) 只负责监听文件描述符上是否有事件发生,有的话就立即将该事件通知工作线程(逻辑单元)。除此之外,主线程不做任何其他实质性的工作。读写数据,接受新的连接,以及处理客户请求均在工作线程中完成。 <br> 使用同步 I/O 模型(以 epoll_wait 为例)实现的 Reactor 模式的工作流程是:</p>
<ol>
<li>主线程往 epoll 内核事件表中注册 socket 上的读就绪事件。</li>
<li>主线程调用 epoll_wait 等待 socket 上有数据可读。</li>
<li>当 socket 上有数据可读时, epoll_wait 通知主线程。主线程则将 socket 可读事件放入请求队列。</li>
<li>睡眠在请求队列上的某个工作线程被唤醒,它从 socket 读取数据,并处理客户请求,然后往 epoll 内核事件表中注册该 socket 上的写就绪事件。</li>
<li>主线程调用 epoll_wait 等待 socket 可写。</li>
<li>当 socket 可写时,epoll_wait 通知主线程。主线程将 socket 可写事件放入请求队列。</li>
<li>睡眠在请求队列上的某个工作进程被唤醒,它往 socket 上写入服务器处理客户请求的结果。</li>
</ol>
<p><img src="/img/bVSQaz?w=820&h=203" alt="Reactor 模型" title="Reactor 模型"><br> 工作线程从请求队列中取出事件后,将根据事件类型来决定如何处理它:对于可读事件,执行读数据和处理请求的操作;对于可写事件,执行写数据的操作。因此,Reactor 模式中没必要区分所谓的 “读工作线程” 和 “写工作线程”。</p>
<h3>二、 Proactor 模式</h3>
<p> 与 Reactor 模式不同,Proactor 模式将所有 I/O 操作都交给<code>主线程和内核</code>来处理,工作线程仅仅负责业务逻辑。 <br> 使用异步 I/O 模型(以 aio_read 和 aio_write为例)实现的 Proactor 模式的工作流程是:</p>
<ol>
<li>主线程调用 aio_read 函数向内核注册 socket 上的<code>读完成</code>事件,并告诉内核用户读缓冲区的位置,以及读操作完成时如何通知应用程序(这里以信号为例)</li>
<li>主线程继续处理其他逻辑。</li>
<li>当 socket 上的数据被读入用户缓冲区后,内核将向应用程序发送一个信号,以通知应用程序数据已经可用。</li>
<li>应用程序预先定义好的信号处理函数选择一个工作线程来处理客户请求。工作线程处理完客户请求之后,调用 aio_write 函数向内核注册 socket 上的写完成事件,并告诉内核用户写缓冲区的位置,以及写操作完成时如何通知应用程序(仍然以信号为例)</li>
<li>主线程继续处理其他逻辑</li>
<li>当用户缓冲区的数据被写入 socket 之后,内核将向应用程序发送一个信号,以通知应用程序数据已经发送完毕。</li>
<li>应用程序预先定义好的信号处理函数选择一个工作线程来做善后处理,比如决定是否关闭 socket。</li>
</ol>
<p><img src="/img/bVSQeI?w=769&h=210" alt="proactor 模型" title="proactor 模型"></p>
<p> 连接 socket 上的读写事件是通过 aio_read/aio_write 向内核注册的,因此内核将通过信号来向应用程序报告连接 socket 上的读写事件。所以,主线程中的 epoll_wait 调用仅能用来检测监听 socket 上的连接请求事件,而不能用来检测 socket 上的读写事件。</p>
Linux I/O 模型
https://segmentfault.com/a/1190000010591900
2017-08-10T12:17:34+08:00
2017-08-10T12:17:34+08:00
waterandair
https://segmentfault.com/u/waterandair
0
<h3>一、I/O 模型对比</h3>
<p><img src="/img/bVSByf?w=754&h=175" alt="图片描述" title="图片描述"></p>
<h3>二、理解阻塞I/O 和 非阻塞I/O</h3>
<p> socket 在创建的时候默认是阻塞的,也可以通过命令将其设置为非阻塞的。阻塞和非阻塞的概念能应用于所有文件描述符,而不仅仅是 socket。我们称阻塞的文件描述符为阻塞 I/O,称非阻塞的文件描述符为非阻塞I/O。</p>
<h4>1. 阻塞 I/O</h4>
<p> 针对阻塞 I/O 执行的系统调用可能因为无法立即完成而被操作系统挂起,直到等待的事件发生为止。比如,tcp 客户端通过 connect 向服务器发起连接时, connect 将首先发送同步报文段给服务器,然后等待服务器返回确认报文段。如果服务器的确认报文段没有立即到达客户端,则 connect 调用将被挂起,直到客户端收到确认报文段并唤醒 connect 调用。socket 的基础 API 中,可能被阻塞的系统调用包括 accept、send、recv 和 connect。</p>
<h4>2. 非阻塞 I/O</h4>
<p> 针对非阻塞 I/O执行的系统调用则总是立即返回,而不管事件是否已经发生。如果事件没有立即发生,这么系统调用就返回 -1,和出错的情况一样。此时我们必须根据 errno 来区分这两种情况。对 acccept、send 和 recv 而言,事件未发生时 errno 通常被设置成 EAGAIN (意为“再来一次”) 或者 EWOULDBLOCK (意为“期望阻塞”);对 connect 而言,errno 则被设置成 EINROGRESS (意为“在处理中”)。 <br> 很显然,只有在事件已经发生的情况下操作非阻塞 I/O (读、写等),才能提高程序的效率。因此,非阻塞 I/O 通常要和其他 I/O 通知机制一起使用,比如 I/O 复用和 SIGIO 信号。</p>
<h3>三、理解 I/O 复用机制和 SIGIO 信号机制</h3>
<h4>1. I/O 复用</h4>
<p> I/O 复用是最常用的 I/O 通知机制。它指的是,应用程序通过 I/O 复用函数向内核注册一组事件,内核通过 I/O 复用函数把其中就绪的事件通知给应用程序。Linux 上常用的 I/O 复用函数是 select、poll 和 epoll_wait。需要指出的是,I/O 复用函数本身是阻塞的,它们能提高程序效率的原因在于它们具有同时监听多个 I/O 事件的能力。</p>
<h4>2. SIGIO 信号</h4>
<p> SIGIO 信号也可以用来报告 I/O 事件。我们可以为一个目标文件描述符指定宿主进程,那么被指定的宿主进程将捕获到 SIGIO 信号 。这样,当目标文件描述符上有事件发生时,SIGIO 信号的信号处理函数将被触发,我们也就可以在该信号处理函数中对目标文件描述符执行非阻塞 I/O 操作了。</p>
<h3>四、 理解同步I/O 和异步 I/O</h3>
<p> 阻塞 I/O、I/O复用 和 信号驱动 I/O 都是<code>同步 I/O 模型</code>。因为在这三种 I/O 模型中,I/O 的读写操作,都是在 I/O 事件发生之后,由<code>应用程序</code>来完成的。而 POSIX 规范所定义的异步模型不同。对异步I/O 而言,用户可以直接对 I/O 执行读写操作,这些操作告诉内核用户读写缓存区的位置,以及 I/O 操作完成之后内核通知应用程序的方式。也就是说,同步 I/O 模型要求用户代码自行执行 I/O 操作(将数据从内核缓冲区读入用户缓冲区,或将数据从用户缓冲区写入内核缓冲区),而异步 I/O 机制则由<code>内核</code>来执行 I/O 操作(数据在内核缓冲区和用户缓冲区之间的移动是由<code>内核在“后台”</code>完成的)。你可以这样认为,同步 I/O 向应用程序通知的是 <code>I/O 就绪</code>事件,而异步 I/O 向应用程序通知的是 <code>I/O 完成</code>事件。</p>
文件描述符(File Descriptor)简介
https://segmentfault.com/a/1190000009724931
2017-07-03T06:00:00+08:00
2017-07-03T06:00:00+08:00
waterandair
https://segmentfault.com/u/waterandair
37
<blockquote><p><a href="https://link.segmentfault.com/?enc=Xfv4CK1Vt76cPo8HT3Ag2Q%3D%3D.8E7R7PKA3NApG0Wpm%2B6j%2FOFOmJ%2Bsry4Pz7hDguXz2qrwYLx740YyIXQ5B6Q9TSc%2FX69o80J4bm1aDBwJo3lY1r1stKVuIgJQkWNKC43yNrM%3D" rel="nofollow">维基百科</a>:文件描述符在形式上是一个非负整数。实际上,它是一个索引值,指向内核为每一个进程所维护的该进程打开文件的记录表。当程序打开一个现有文件或者创建一个新文件时,内核向进程返回一个文件描述符。在程序设计中,一些涉及底层的程序编写往往会围绕着文件描述符展开。</p></blockquote>
<h3>一、文件描述符概念</h3>
<p> Linux 系统中,把一切都看做是文件,当进程打开现有文件或创建新文件时,内核向进程返回一个文件描述符,文件描述符就是内核为了高效管理已被打开的文件所创建的索引,用来指向被打开的文件,所有执行I/O操作的系统调用都会通过文件描述符。</p>
<h3>二、文件描述符、文件、进程间的关系</h3>
<h4>1.描述:</h4>
<ul>
<li><p>每个文件描述符会与一个打开的文件相对应</p></li>
<li><p>不同的文件描述符也可能指向同一个文件</p></li>
<li><p>相同的文件可以被不同的进程打开,也可以在同一个进程被多次打开</p></li>
</ul>
<h4>2.系统为维护文件描述符,建立了三个表</h4>
<ul>
<li><p>进程级的文件描述符表</p></li>
<li><p>系统级的文件描述符表</p></li>
<li><p>文件系统的i-node表 (<a href="https://link.segmentfault.com/?enc=GFhZasCKdBCTiIqOclUYBQ%3D%3D.J8rDajYTfPWGUJXR%2FbQv6td6T9s3M%2F4sSp5fIJlIbdpjXiIp6sP4m9vgC%2FiobnvxW%2BqrGqU5ngbte0ox5m03fg%3D%3D" rel="nofollow">转到:阮一峰——理解inode</a>)</p></li>
</ul>
<p><img src="/img/bVOX3l?w=619&h=312" alt="" title=""></p>
<h4>3.通过这三个表,认识文件描述符</h4>
<p><img src="/img/bVOX3m?w=617&h=392" alt="图片描述" title="图片描述"></p>
<ul>
<li><p>在进程A中,文件描述符1和30都指向了同一个打开的文件句柄(#23),这可能是该进程多次对执行<code>打开</code>操作</p></li>
<li><p>进程A中的文件描述符2和进程B的文件描述符2都指向了同一个打开的文件句柄(#73),这种情况有几种可能,1.进程A和进程B可能是父子进程关系;2.进程A和进程B打开了同一个文件,且文件描述符相同(低概率事件=_=);3.A、B中某个进程通过UNIX域套接字将一个打开的文件描述符传递给另一个进程。</p></li>
<li><p>进程A的描述符0和进程B的描述符3分别指向不同的打开文件句柄,但这些句柄均指向i-node表的相同条目(#1936),换言之,指向同一个文件。发生这种情况是因为每个进程各自对同一个文件发起了打开请求。同一个进程两次打开同一个文件,也会发生类似情况。</p></li>
</ul>
<p>前人的思考,我们的阶梯,这部分参考自网络:<a href="https://link.segmentfault.com/?enc=Xc3oYoWOaGR38%2BQanDeWEA%3D%3D.t6vVHrLjQWI%2FTnDlNPXW%2FqKi%2F7FBJvgtohb0gxypeJWvFd8jX3z0BXFR3OklGO62WyyLlX543UAzVVkwNM%2BkwA%3D%3D" rel="nofollow">链接</a></p>
<h3>三、文件描述符限制</h3>
<p> 有资源的地方就有战争,“文件描述符”也是一种资源,系统中的每个进程都需要有“文件描述符”才能进行改变世界的宏图霸业。世界需要秩序,于是就有了“文件描述符限制”的规定。</p>
<h4>如下表:</h4>
<p><img src="/img/bVOX3o?w=651&h=289" alt="图片描述" title="图片描述"></p>
<p>永久修改用户级限制时有三种设置类型:</p>
<ol>
<li><p><code>soft</code> 指的是当前系统生效的设置值</p></li>
<li><p><code>hard</code> 指的是系统中所能设定的最大值</p></li>
<li><p><code>-</code> 指的是同时设置了 soft 和 hard 的值</p></li>
</ol>
<p>命令讲解:</p>
<ul>
<li><p><a href="https://link.segmentfault.com/?enc=0irNuh%2Fcjoh%2BwfnO2Ecedw%3D%3D.KHXCl2Fj8HG4w4NZK3m9K3dlFce8B3McZsTinZyR%2Bns%3D" rel="nofollow">ulimit</a></p></li>
<li><p><a href="https://link.segmentfault.com/?enc=pTjQhJ44PAX1l33SAixOww%3D%3D.MW3sH9Aln0NsWYzhE3CY5ZtbNid7V%2BsD5TWm4XhN9qg%3D" rel="nofollow">sysctl</a></p></li>
</ul>
<h3>四、检查某个进程的文件描述符相关内容</h3>
<p>步骤(以nginx为例,*注意权限问题,此示例是在本地环境):</p>
<ol><li><p>找到需要检查的进程id</p></li></ol>
<p><img src="/img/bVOX3F?w=801&h=130" alt="" title=""></p>
<p>如图,找到的进程id为 1367</p>
<ol><li><p>查看该进程的限制</p></li></ol>
<p><img src="/img/bVOX3r?w=709&h=309" alt="图片描述" title="图片描述"></p>
<p>如图,在 Max open files 那一行,可以看到当前设置中最大文件描述符的数量为1024</p>
<ol><li><p>查看该进程占用了多少个文件描述符</p></li></ol>
<p><img src="/img/bVOX3S?w=766&h=341" alt="图片描述" title="图片描述"></p>
<p>如图所示,使用了17个文件描述符</p>
<h3>总结</h3>
<p> 实际应用过程中,如果出现“Too many open files” , 可以通过增大进程可用的文件描述符数量来解决,但往往故事不会这样结束,很多时候,并不是因为进程可用的文件描述符过少,而是因为程序bug,打开了大量的文件连接(web连接也会占用文件描述符)而没有释放。程序申请的资源在用完后及时释放,才是解决“Too many open files”的根本之道。</p>
mysql查询优化小技巧
https://segmentfault.com/a/1190000009724131
2017-06-17T06:00:00+08:00
2017-06-17T06:00:00+08:00
waterandair
https://segmentfault.com/u/waterandair
7
<h5>1. 开启缓存</h5>
<pre><code>mysql> show variables like 'query_cache%';
mysql> set global query_cache_type=1;
mysql> set global query_cache_size=1024*1024*32</code></pre>
<p>注意:</p>
<ol>
<li>查询缓存存在判断是严格依据select语句本身的:严格保证sql一致。</li>
<li>如果查询时包含动态数据,则不能被缓存。</li>
<li>如果不想使用缓存,可以使用 SQL_NO_CACHE 语法提示。</li>
</ol>
<h5>2. in型子查询</h5>
<pre><code>select goods_id,cat_id,goods_name from good where cat_id in(select cat_id form category where parent_id=6);
这条语句执行会非常慢,因为它会扫描goods全表,逐行与category表对照
原因:mysql的查询优化器,针对in型做了优化,优化成了exists的执行效果。
改进:用连接查询代替子查询
select goods_id,g.cat_id,g.goods_name from goods as g inner join (select cat_id from category where parent_id=6) as t;</code></pre>
<h5>3. from 子查询</h5>
<p>内层 from 语句查到的临时表,没有索引,所以from返回的内容要尽量少</p>
<h5>4. count()优化</h5>
<p>没有查询条件时count(*)非常快,不需要查表。但当有查询条件时,速度将减慢。 <br>可以使用缩小范围的方法优化查询。 <br>eg.</p>
<pre><code>需要统计good_id>100的总数时一般会写为:
select count(*) form goods where good_id>100;
优化为:
slect (select count(*) from goods)-(select count(*) from goods where id<100);
这样,就把范围由无限大缩小到了100</code></pre>
<h5>5. group by 优化</h5>
<ul>
<li>分组用于统计,而不用于筛选数据。</li>
<li>用索引避免产生临时表和文件排序</li>
<li>A,B表连接查询,group by和order by 的列尽量相同,而且列应该为A的列</li>
</ul>
<p>默认情况下,MySQL 对所有 group by col1, col2, …… 的字段进行排序。这与查询中指定 order by col1,col2,…… 类似。因此,如果显示包括一个包含相同列的 order by 子句,则对 MySQL 的实际执行性能没有什么影响。 </p>
<p>如果查询包括 group by 但用户想要避免排序结果的消耗,则可以指定 order by null 禁止排序。</p>
<pre><code>select col1 from table group by col2 order by null;</code></pre>
<h5>6. union 优化</h5>
<p>union all 不过滤 效率提高,如非必须,请用union all <br>因为 union去重的代价非常高, 放在程序里去重.</p>
<h4>7. limit & 分页优化</h4>
<p>limit offset,n 当offset非常大时,效率极低。mysql并不是跳过offset行,然后单取n行,而是取offset+n行,返回放弃前offset行,返回n行。 <br>优化:</p>
<ol>
<li>从业务上解决 <br>不允许翻过100页(百度也是如此)</li>
<li>利用索引</li>
<li>id,name from goods inner join (select id from goods limit 5000000,10) as tmp using(id);</li>
<li>记录上一次取出的最后一条数据,把 limit m, n 语句转化为 limit n。</li>
</ol>
<h4>8. 消除msyql内部临时表</h4>
<p>在一些sql请求中,mysql会创建临时表,可能创建到内存中,也可能由内存中转存到磁盘。 <br>会创建临时表的查询:</p>
<ol>
<li>group by 的列没有索引,必创建临时表</li>
<li>order by 与 group by 为不同列时,或多表联查时order by,group by 包含的列不是第一张表的列,必产生临时表。</li>
<li>distinct 与 order by 一起使用可能会产生临时表</li>
<li>union合并查询时会用到临时表</li>
</ol>
<h4>9. 大批量插入数据</h4>
<h5>对于 myisam 引擎</h5>
<p>如果是空的 myisam 表,默认就是先导入数据才创建索引的,不存在优化问题。 <br>对于非空的 myisam 表,在一次性插入大量数据时,可以通过设置 disable keys 和 enable keys 来提高导入的效率。</p>
<pre><code># 假设给 test 表一次性插入大量数据
alert table test disable keys;
loading the data ……
alert table test enable keys;
</code></pre>
<h5>对于 innodb 引擎</h5>
<p>disable keys 的方式适用于 myisam 引擎,但不适用于 innodb 引擎。</p>
<ol>
<li>因为 innodb 类型的表是按照主键的顺序保存的,所以将导入的数据按照主键的顺序排列,可以有效的提高导入数据的效率。</li>
<li>在导入数据前执行 set unique_checks=0 , 关闭唯一性校验,在导入结束后执行 set unique_checks=1,恢复唯一性校验,可以提高导入的效率。</li>
<li>如果应用使用自动提交的方式,建议在导入前执行 set autocommit=0,关闭自动提交,导入结束后再执行 set autocommit=1,打开自动提交,也可以提高导入的效率。</li>
</ol>
<h4>10. 优化 insert 语句</h4>
<h5>同一客户端一次插入多行</h5>
<p>使用多个值表的 insert 语句,可以减少客户端与数据库之间的连接、关闭等资源消耗</p>
<pre><code>insert into test values (1,1),(2,2),(3,3)…… </code></pre>
<h5>从不同客户插入很多行,可以使用 insert delayed 语句得到更高的素的。</h5>
<p>delayed 的含义是让 insert 语句马上执行,其实数据都被放在内存的队列中,并没有真正的写入磁盘,这比每条土局分别插入要快的多; <br>low_priority 刚好相反,在所有其他用户对表的读写完成后才进行插入(比如记录日志的场景)</p>
<h5>将索引文件和数据文件分在不同的磁盘上存放(利用建表中的选项)</h5>
<h5>如果进行批量插入,可以通过增加 bulk_insert_buffer_size 变量值的方法来提高速度,但是,这只能对 myisam 表使用。</h5>
<h5>当从一个文本文件装载一个表时,使用 load data infile 。这通常比使用很多 insert 语句快 20 倍。</h5>
<h4>11. 优化 order by 语句</h4>
<h6>mysql 的两种排序方式</h6>
<ol>
<li>通过有序索引顺序扫描直接返回有序数据,这种方式在使用 explain 分析查询时显示为 using index ,不需要额外的排序,操作效率极高。</li>
<li>通过对返回的数据行进行排序,也就是通常说的 filesort 排序,所有不是通过索引直接返回排序结果的排序都叫做 filesort 排序。filefort 并不代表通过磁盘文件进行排序,而只是说明进行了一个排序操作,至于排序操作是否使用了磁盘文件或临时表等,则取决于 MySQL 服务器对排序参数的设置和需要排序数据的大小。</li>
</ol>
<ul><li>filesort 是通过相应的排序算法,将取得的数据在 sort_buffer_size 系统变量设置的内存排序区中进行排序,如果内存装载不下,它就会将磁盘上的数据进行分块,再对各个数据块进行排序,然后将各个块合并成有序的结果集。sort_buffer_size 设置的排序区是每个线程独占的,所以同一个时刻,mysql 中存在多个sort buffer 排序区。</li></ul>
<h5>优化思路</h5>
<p>尽量减少额外的排序,通过索引直接返回有序数据。 <br>where 条件和 order_by 使用相同的索引,并且 order_by 的顺序和索引的顺序相同,并且 order by 的字段都是升序或者降序。否则肯定需要额外的排序操作,这样就会出现 filesort 。</p>
<h6>filesort 的优化</h6>
<p>在某些不得不使用 filesort 的场景中,需要想办法加快 filesort 的操作。对于 filesort ,MySQL 有两种排序算法。</p>
<ul><li>两次扫描算法:</li></ul>
<p>首先根据条件取出排序字段和行指针信息,之后在排序区 sort_buffer 中排序。如果排序区 sort buffer 不够,则在临时表 temporary table 汇总存储排序结果,完成排序后根据行指针回表读取记录。这种算法需要两次访问数据,第一次获取排序字段和行指针信息,第二次根据行指针获取记录,尤其是第二次读取操作可能导致大量随机 I/O 操作;优点是排序的时候<code>内存开销较少</code>。</p>
<ul><li>一次扫描算法:</li></ul>
<p>一次性取出满足条件的行的所有字段,然后在排序区 sort buffer 中排序后直接输出结果集,排序的时候内存开销较大,但是排序效率比两次扫描算法要高。 </p>
<p>mysql 通过比较系统变量 max_length_for_sort_data 的大小和 query 语句取出的字段总大小来判断使用哪种排序算法。如果 max_length_for_sort_data 更大,那么使用第二种优化之后的算法,否则使用第一种算法。 <br>适当加大系统变量 max_length_for_sort_data 的值,能够让 MySQL 选择更优化的 filesort 的排序算法,当然,设置过大,会造成cpu利用率过低和磁盘 I/O 过高 </p>
<p>适当加大 sort_buffer_size 排序区,尽量让排序在内存中完成,而不是通过创建临时表放在文件中进行;当然也不能不限制加大 sort_buffer_size 排序区,因为 sort_buffer_size 参数时每个线程独占的,所以要考虑数据库活动连接数和服务器内存的大小来适当设置排序区。 </p>
<p>尽量只使用必要的字段,select 具体的字段名称,而不是 select * 选择所有字段,这样可以减少排序区使用,提高 sql 性能。</p>
<h4>12. 使用 sql 提示</h4>
<p>sql 提示(sql hint) 是优化数据库的一个重要手段,简单来说就是在 sql 语句中加入一些人为的提示来达到优化操作的目的。</p>
<pre><code>select sql_buffer_results * from ……</code></pre>
<p>这个语句将强制 MySQL 生成一个临时结果集。只要临时结果集生成后,所有表上的锁定均被释放。 <br><code>这能再遇到表锁定问题时或要花很长时间将结果传给客户端时有所帮助,因为可以尽快释放锁资源。</code> </p>
<p>常用的 sql 提示:</p>
<ul>
<li>use index 提供希望 MySQL 去参考的索引列表,就可以让 MySQL 不再考虑其他可用的索引。</li>
<li>ignore index 忽略一个或者多个索引</li>
<li>force index 强制 MySQL 使用一个特定的索引。</li>
</ul>
MySql表分区——partition
https://segmentfault.com/a/1190000009724052
2017-06-13T06:00:00+08:00
2017-06-13T06:00:00+08:00
waterandair
https://segmentfault.com/u/waterandair
7
<h4>一、分区的优点</h4>
<ul>
<li>和单个磁盘或文件系统分区相比,可以存储更多的数据。</li>
<li>
<p>优化查询。</p>
<ul>
<li>where 子句中包含分区条件时,可以只扫描必要的分区。</li>
<li>涉及聚合函数的查询时,可以容易的在每个分区上并行处理,最终只需汇总得到结果。</li>
</ul>
</li>
<li>对于已经过期或者不需要保存的数据,可以通过删除与这些数据有关的分区来快速删除数据。</li>
<li>跨多个磁盘来分散数据查询,以获得更大的查询吞吐量。</li>
</ul>
<h4>二、分区方法</h4>
<p>将某张表的数据,分别存储到不同的区域中。每个分区都是独立的表,都要存储该分区数据的数据、索引等信息。 <br>使用mysql的分区功能,可以把一个大的数据表分成多个小份,用户不需要区分不同的表名。 <br>表中有主键的时候,分区只能使用主键</p>
<h5>1. KEY 分区,按照某个字段取余</h5>
<pre><code>create table post (
id int unsigned not null AUTO_INCREMENT,
title varchar(255),
PRIMARY KEY (id)
) engine = innodb
partition by key (id) partitions 5;</code></pre>
<h5>2. HASH 分区,基于给定的分区个数,把数据分配到不同的分区。</h5>
<pre><code>create table student_hash(
id int unsigned not null auto_increment,
birthday date,
PRIMARY KEY(id,birthday);
) engine=myisam
partition by hash (month(birthday)) patitions 12;</code></pre>
<p>key 和 hash 分区方法可以有效的分散热点数据。</p>
<h5>3. RANGE 分区,基于一个给定连续区间范围,把数据分配到不同的分区</h5>
<pre><code>create table goods (
id int,
uname char(10)
)engine myisam
partition by range(id) (
partition p1 values less than (10),
partition p2 values less than (20),
partition p3 values less than MAXVALUE
);
</code></pre>
<h5>4.LIST 分区,类似 RANGE 分区,区别在 LIST 分区是基于枚举出的值列表分区,RANGE 是局域给定的连续区间范围分区。</h5>
<pre><code>create table user (
uid int,
pid int,
uname
)engine myisam
partition by list(pid) (
partition bj values in (1),
partition ah values in (2),
partition xb values in (4,5,6)
);
# 如果试图插入的列值不包含分区值列表中时,那么 insert 操作会失败并报错,要重点注意的是,list 分区不存在类似 values less than maxvalue 这样包含其他值在内的定义方式,将要匹配的任何值都必须在值列表中找得到。
</code></pre>
<h5>5. Clumns 分区</h5>
<p>是在mysql5.5引入的分区类型,解决了之前版本 range 和 list 分区只支持整数分区,从而导致需要额外的函数计算得到整数或通过额外的转换表来转换为整数再分区的问题。 <br>Columns 分区可以细分为 range columns 分区和 list columns 分区,这两种分区都支持整数,日期和字符串三大数据类型。 <br>columns 分区的另一个亮点是支持多列分区:</p>
<pre><code>mysql> create table rc3(
a int,
b int
)
partition by range columns(a,b)(
partition p01 values less than (0,10),
partition p02 values less than (10,10),
partition p03 values less than (10,20),
partition p04 values less than (10,35),
partition p05 values less than (10,maxvalue),
partition p06 values less than (maxvalue,maxvalue)
);</code></pre>
<h5>6. 子分区</h5>
<p>子分区(subpartitioning)是分区表中对每个分区的再次分割,又被称为符合分区(composite partitioning)。mysql 从 mysql 5.1 开始支持对已经通过 range 或者 list 分区了的表再进行子分区,子分区既可以使用 hash 分区,也可以使用 key 分区。</p>
<pre><code>mysql> create table ts (id int, purchased date)
partition by range(year(purchased))
subpartition by hash (to_days(purchased))
subpartitions 2
(
partition p0 values less than (1990),
partition p1 values less than (2000),
partition p2 values less than maxvalue,
);</code></pre>
<p>表 ts 有3个 range 分区,这 3 个分区中的每个分区又进一步分成 2 个子分区,实际上,整个表被分成了 3*2=6 个分区,由于 partition by range 子句的作用,第一和第二个分区只保存 purchased 列中值小于 1990 的记录。 <br>复合分区适用于保存非常大量的数据记录。</p>
<h4>三、分区管理</h4>
<h5>1. 取余算法 key hash</h5>
<p><strong>采用取余算法的分区数量的修改,不会导致已有分区数据的丢失,需要重新分配数据到新的分区</strong> <br>增加分区: add partition N;<br>减少分区:coalesce partition N;</p>
<h5>2. 条件算法 list range</h5>
<p>添加分区</p>
<pre><code>alert table goods add partition(
partition p4 values less than 40);</code></pre>
<p>删除分区</p>
<pre><code>alert table goods drop partition p1;
注意:删除条件算法的分区,会导致分区数据的丢失</code></pre>
<h4>四、mysql 分区处理 null 值的方式</h4>
<p>mysql 不禁止在分区键值上使用 null , 分区键可能是一个字段或者一个用户定义的额表达式。一般情况下,mysql 的分区把 null 当作零值,或者一个最小值进行处理。 <br>range 分区中,null 值会被当作最小值来处理;<br>list 分区中,null 值必须出现在枚举列表中,否则不被接受; <br>hash/key 分区中,null 值会被当作零值来处理。</p>
Mysql索引优化
https://segmentfault.com/a/1190000009717352
2017-06-09T12:02:35+08:00
2017-06-09T12:02:35+08:00
waterandair
https://segmentfault.com/u/waterandair
24
<h4>一、索引的数据结构 B-Tree(mysql主要使用 B-tree 平衡树)</h4>
<h5>聚簇索引与非聚簇索引</h5>
<blockquote><p>聚簇索引:索引的叶节点指向数据 <br>非聚簇索引:索引的叶节点指向数据的引用</p></blockquote>
<table>
<thead><tr>
<th>索引类型</th>
<th>优</th>
<th>劣</th>
</tr></thead>
<tbody><tr>
<td>聚簇索引</td>
<td>查询数据少时,无须回行</td>
<td> 不规则插入数据,频繁的页分裂</td>
</tr></tbody>
</table>
<blockquote><p>myisam使用非聚簇索引,innodb使用聚簇索引</p></blockquote>
<p>对于innodb引擎:</p>
<ol>
<li>主键索引既存储索引值,又在叶中存储行数据</li>
<li>如果没有主键,则会使用 unique key 做主键</li>
<li>如果没有unique,则mysql会生成一个rowid做主键 </li>
</ol>
<h4>二、索引类型</h4>
<h5>1. 主键索引</h5>
<p>primary key() 要求关键字不能重复,也不能为null,同时增加主键约束 <br>主键索引定义时,不能命名</p>
<h5>2. 唯一索引</h5>
<p>unique index() 要求关键字不能重复,同时增加唯一约束</p>
<h5>3. 普通索引</h5>
<p>index() 对关键字没有要求</p>
<h5>4. 全文索引</h5>
<p>fulltext key() 关键字的来源不是所有字段的数据,而是字段中提取的特别关键字</p>
<p><strong>关键字:可以是某个字段或多个字段,多个字段称为复合索引</strong></p>
<pre><code>建表:
creat table student(
stu_id int unsigned not null auto_increment,
name varchar(32) not null default '',
phone char(11) not null default '',
stu_code varchar(32) not null default '',
stu_desc text,
primary key ('stu_id'), //主键索引
unique index 'stu_code' ('stu_code'), //唯一索引
index 'name_phone' ('name','phone'), //普通索引,复合索引
fulltext index 'stu_desc' ('stu_desc'), //全文索引
) engine=myisam charset=utf8;
更新:
alert table student
add primary key ('stu_id'), //主键索引
add unique index 'stu_code' ('stu_code'), //唯一索引
add index 'name_phone' ('name','phone'), //普通索引,复合索引
add fulltext index 'stu_desc' ('stu_desc'); //全文索引
删除:
alert table sutdent
drop primary key,
drop index 'stu_code',
drop index 'name_phone',
drop index 'stu_desc';</code></pre>
<h4>三、索引使用原则</h4>
<h5>1. 列独立</h5>
<p>保证索引包含的字段独立在查询语句中,不能是在表达式中</p>
<h5>2. 左前缀</h5>
<p>like:匹配模式左边不能以通配符开始,才能使用索引 <br>注意:前缀索引在排序 order by 和分组 group by 操作的时候无法使用。</p>
<h5>3. 复合索引由左到右生效</h5>
<p>建立联合索引,要同时考虑列查询的频率和列的区分度。</p>
<ol><li>index(a,b,c)</li></ol>
<table>
<thead><tr>
<th>语句</th>
<th>索引是否发挥作用</th>
</tr></thead>
<tbody>
<tr>
<td>where a=3</td>
<td>是,只使用了a</td>
</tr>
<tr>
<td>where a=3 and b=5</td>
<td>是,使用了a,b</td>
</tr>
<tr>
<td>where a=3 and b=5 and c=4</td>
<td>是,使用了a,b,c</td>
</tr>
<tr>
<td>where b=3 or where c=4</td>
<td>否</td>
</tr>
<tr>
<td>where a=3 and c=4</td>
<td>是,仅使用了a</td>
</tr>
<tr>
<td>where a=3 and b>10 and c=7</td>
<td>是,使用了a,b</td>
</tr>
<tr>
<td>where a=3 and b like '%xx%' and c=7</td>
<td>使用了a,b</td>
</tr>
</tbody>
</table>
<p>or的两边都有存在可用的索引,该语句才能用索引。</p>
<h5>4. 不要滥用索引,多余的索引会降低读写性能</h5>
<p><strong>即使满足了上述原则,mysql还是可能会弃用索引,因为有些查询即使使用索引,也会出现大量的随机io,相对于从数据记录中的顺序io开销更大。</strong></p>
<h4>四、mysql 中能够使用索引的典型应用</h4>
<blockquote><p>测试库下载地址:<a href="https://link.segmentfault.com/?enc=2Q1kA2ypobf2qkVq%2FuC1AQ%3D%3D.jlYeLzUxJbzJfuH1pGhMR6r3wn1Ds3Hg%2FV%2Bf47ZFXp18%2BxvTNzRYu0nC54EZ8nYr" rel="nofollow">https://downloads.mysql.com/d...</a></p></blockquote>
<h5>1. 匹配全值(match the full value)</h5>
<p>对索引中所有列都指定具体值,即是对索引中的所有列都有等值匹配的条件。 <br>例如,租赁表 rental 中通过指定出租日期 rental_date + 库存编号 inventory_id + 客户编号 customer_id 的组合条件进行查询,熊执行计划的 key he extra 两字段的值看到优化器选择了复合索引 idx_rental_date:</p>
<pre><code>MySQL [sakila]> explain select * from rental where rental_date='2005-05-25 17:22:10' and inventory_id=373 and customer_id=343 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rental
partitions: NULL
type: const
possible_keys: rental_date,idx_fk_inventory_id,idx_fk_customer_id
key: rental_date
key_len: 10
ref: const,const,const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
</code></pre>
<p>explain 输出结果中字段 type 的值为 const,表示是常量;字段 key 的值为 rental_date, 表示优化器选择索引 rental_date 进行扫描。</p>
<h5>2. 匹配值的范围查询(match a range of values)</h5>
<p>对索引的值能够进行范围查找。 <br>例如,检索租赁表 rental 中客户编号 customer_id 在指定范围内的记录:</p>
<pre><code>MySQL [sakila]> explain select * from rental where customer_id >= 373 and customer_id < 400 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rental
partitions: NULL
type: range
possible_keys: idx_fk_customer_id
key: idx_fk_customer_id
key_len: 2
ref: NULL
rows: 718
filtered: 100.00
Extra: Using index condition
1 row in set, 1 warning (0.05 sec)
</code></pre>
<p>类型 type 为 range 说明优化器选择范围查询,索引 key 为 idx_fk_customer_id 说明优化器选择索引 idx_fk_customer_id 来加速访问,注意到这个列子中 extra 列为 using index codition ,表示 mysql 使用了 ICP(using index condition) 来进一步优化查询。</p>
<h5>3. 匹配最左前缀(match a leftmost prefix)</h5>
<p>仅仅使用索引中的最左边列进行查询,比如在 col1 + col2 + col3 字段上的联合索引能够被包含 col1、(col1 + col2)、(col1 + col2 + col3)的等值查询利用到,可是不能够被 col2、(col2、col3)的等值查询利用到。 <br>最左匹配原则可以算是 MySQL 中 B-Tree 索引使用的首要原则。</p>
<h5>4. 仅仅对索引进行查询(index only query)</h5>
<p>当查询的列都在索引的字段中时,查询的效率更高,所以应该尽量避免使用 select *,需要哪些字段,就只查哪些字段。</p>
<h5>5. 匹配列前缀(match a column prefix)</h5>
<p>仅仅使用索引中的第一列,并且只包含索引第一列的开头一部分进行查找。 <br>例如,现在需要查询出标题 title 是以 AFRICAN 开头的电影信息,从执行计划能够清楚看到,idx_title_desc_part 索引被利用上了:</p>
<pre><code>MySQL [sakila]> create index idx_title_desc_part on film_text(title (10), description(20));
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
MySQL [sakila]> explain select title from film_text where title like 'AFRICAN%'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film_text
partitions: NULL
type: range
possible_keys: idx_title_desc_part,idx_title_description
key: idx_title_desc_part
key_len: 32
ref: NULL
rows: 1
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
</code></pre>
<p>extra 值为 using where 表示优化器需要通过索引回表查询数据。</p>
<h6>6. 能够实现索引匹配部分精确而其他部分进行范围匹配(match one part exactly and match a range on another part)</h6>
<p>例如,需要查询出租日期 rental_date 为指定日期且客户编号 customer_id 为指定范围的库存:</p>
<pre><code>MySQL [sakila]> MySQL [sakila]> explain select inventory_id from rental where rental_date='2006-02-14 15:16:03' and customer_id >= 300 and customer_id <=400\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rental
partitions: NULL
type: ref
possible_keys: rental_date,idx_fk_customer_id
key: rental_date
key_len: 5
ref: const
rows: 182
filtered: 16.85
Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)
</code></pre>
<h5>7. 如果列名是索引,那么使用 column_name is null 就会使用索引。</h5>
<p>例如,查询支付表 payment 的租赁编号 rental_id 字段为空的记录就用到了索引:</p>
<pre><code>MySQL [sakila]> explain select * from payment where rental_id is null \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: payment
partitions: NULL
type: ref
possible_keys: fk_payment_rental
key: fk_payment_rental
key_len: 5
ref: const
rows: 5
filtered: 100.00
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
</code></pre>
<h4>五、存在索引但不能使用索引的典型场景</h4>
<p>有些时候虽然有索引,但是并不被优化器选择使用,下面举例几个不能使用索引的场景。</p>
<h5>1.以%开头的 like 查询不能利用 B-Tree 索引,执行计划中 key 的值为 null 表示没有使用索引</h5>
<pre><code>MySQL [sakila]> explain select * from actor where last_name like "%NI%"\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: actor
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 200
filtered: 11.11
Extra: Using where
1 row in set, 1 warning (0.00 sec)
</code></pre>
<p>因为 B-Tree 索引的结构,所以以%开头的插叙很自然就没法利用索引了。一般推荐使用全文索引(Fulltext)来解决类似的全文检索的问题。或者考虑利用 innodb 的表都是聚簇表的特点,采取一种轻量级别的解决方式:一般情况下,索引都会比表小,扫描索引要比扫描表更快,而Innodb 表上二级索引 idx_last_name 实际上存储字段 last_name 还有主键 actot_id,那么理想的访问应该是<code>首先扫描二级索引 idx_last_name 获得满足条件的last_name like '%NI%' 的主键 actor_id 列表,之后根据主键回表去检索记录,这样访问避开了全表扫描演员表 actor 产生的大量 IO 请求。</code></p>
<pre><code>ySQL [sakila]> explain select * from (select actor_id from actor where last_name like '%NI%') a , actor b where a.actor_id = b.actor_id \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: actor
partitions: NULL
type: index
possible_keys: PRIMARY
key: idx_actor_last_name
key_len: 137
ref: NULL
rows: 200
filtered: 11.11
Extra: Using where; Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: sakila.actor.actor_id
rows: 1
filtered: 100.00
Extra: NULL
</code></pre>
<p>从执行计划中能够看出,extra 字段 using wehre;using index。理论上比全表扫描更快一下。</p>
<h5>2. 数据类型出现隐式转换的时候也不会使用索引</h5>
<p>当列的类型是字符串,那么一定记得在 where 条件中<code>把字符常量值用引号引起来</code>,否则即便这个列上有索引,mysql 也不会用到,因为 MySQL 默认把输入的常量值进行转换以后才进行检索。 </p>
<p>例如,演员表 actor 中的姓氏字段 last_name 是字符型的,但是 sql 语句中的条件值 1 是一个数值型值,因此即便存在索引 idx_last_name, mysql 也不能正确的用上索引,而是继续进行全表扫描:</p>
<pre><code>MySQL [sakila]> explain select * from actor where last_name = 1 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: actor
partitions: NULL
type: ALL
possible_keys: idx_actor_last_name
key: NULL
key_len: NULL
ref: NULL
rows: 200
filtered: 10.00
Extra: Using where
1 row in set, 3 warnings (0.00 sec)
MySQL [sakila]> explain select * from actor where last_name = '1'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: actor
partitions: NULL
type: ref
possible_keys: idx_actor_last_name
key: idx_actor_last_name
key_len: 137
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
</code></pre>
<h5>3. 复合索引的情况下,假如查询条件不包含索引列最左边部分,即不满足最左原则 leftmost,是不会使用复合索引的。</h5>
<h5>4. 如果 MySQL 估计使用索引比全表扫描更慢,则不使用索引。</h5>
<h5>5. 用 or 分割开的条件,如果 or 前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到。</h5>
<h4>六、查看索引使用情况</h4>
<p>如果索引正在工作, Handler_read_key 的值将很高,这个值代表了一个行被索引值读的次数,很低的值表名增加索引得到的性能改善不高,因为索引并不经常使用。 <br>Handler_read_rnd_next 的值高则意味着查询运行低效,并且应该建立索引补救。这个值的含义是在数据文件中读下一行的请求数。如果正在进行大量的表扫描,Handler_read_rnd_next 的值较高,则通常说明表索引不正确或写入的查询没有利用索引,具体如下。</p>
<pre><code>MySQL [sakila]> show status like 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 1 |
| Handler_read_key | 5 |
| Handler_read_last | 0 |
| Handler_read_next | 200 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
</code></pre>
<h4>七、使用索引的小技巧</h4>
<h5>1. 字符串字段权衡区分度与长度的技巧</h5>
<p>截取不同长度,测试区分度</p>
<pre><code># 这里假设截取6个字符长度计算区别度,直到区别度达到0.1,就可以把这个字段的这个长度作为索引了
mysql> select count(distinct left([varchar]],6))/count(*) from table;
#注意:设置前缀索引时指定的长度表示字节数,而对于非二进制类型(CHAR, VARCHAR, TEXT)字段而言的字段长度表示字符数,所
# 以,在设置前缀索引前需要把计算好的字符数转化为字节数,常用字符集与字节的关系如下:
# latin 单字节:1B
# GBK 双字节:2B
# UTF8 三字节:3B
# UTF8mb4 四字节:4B
# myisam 表的索引大小默认为 1000字节,innodb 表的索引大小默认为 767 字节,可以在配置文件中修改 innodb_large_prefix
# 项的值增大 innodb 索引的大小,最大 3072 字节。</code></pre>
<p>区别度能达到0.1,就可以。</p>
<h5>2. 左前缀不易区分的字段索引建立方法</h5>
<p>这样的字段,左边有大量重复字符,比如url字段汇总的<a>http://</a></p>
<ol>
<li>倒过来存储并建立索引</li>
<li>新增伪hash字段 把字符串转化为整型</li>
</ol>
<h5>3. 索引覆盖</h5>
<p>概念:如果查询的列恰好是索引的一部分,那么查询只需要在索引文件上进行,不需要回行到磁盘,这种查询,速度极快,江湖人称——索引覆盖</p>
<h5>4. 延迟关联</h5>
<p>在根据条件查询数据时,如果查询条件不能用的索引,可以先查出数据行的id,再根据id去取数据行。 <br>eg.</p>
<pre><code>//普通查询 没有用到索引
select * from post where content like "%新闻%";
//延迟关联优化后 内层查询走content索引,取出id,在用join查所有行
select a.* from post as a inner join (select id from post where content like "%新闻%") as b on a.id=b.id; </code></pre>
<h5>5. 索引排序 </h5>
<p>排序的字段上加入索引,可以提高速度。</p>
<h5>6. 重复索引和冗余索引</h5>
<p>重复索引:在同一列或者相同顺序的几个列建立了多个索引,成为重复索引,没有任何意义,删掉 <br>冗余索引:两个或多个索引所覆盖的列有重叠,比如对于列m,n ,加索引index m(m),indexmn(m,n),称为冗余索引。</p>
<h5>7. 索引碎片与维护</h5>
<p>在数据表长期的更改过程中,索引文件和数据文件都会产生空洞,形成碎片。修复表的过程十分耗费资源,可以用比较长的周期修复表。</p>
<pre><code>//清理方法
alert table xxx engine innodb;
//或
optimize table xxx;</code></pre>
<h5>8. innodb引擎的索引注意事项</h5>
<p>Innodb 表要尽量自己指定主键,如果有几个列都是唯一的,要选择最常作为访问条件的列作为主键,另外,Innodb 表的普通索引都会保存主键的键值,所以主键要尽可能选择较短的数据类型,可以有效的减少索引的磁盘占用,提高索引的缓存效果。</p>
Linux 命令 top 拆解
https://segmentfault.com/a/1190000009713245
2017-06-09T07:25:06+08:00
2017-06-09T07:25:06+08:00
waterandair
https://segmentfault.com/u/waterandair
7
<blockquote><p>top命令是Linux下常用的性能分析工具,能够<em>实时</em>显示系统中各个进程的资源占用状况。</p></blockquote>
<h3>一、内容介绍</h3>
<p>top 命令运行图:</p>
<p><img src="/img/bVOU16?w=864&h=389" alt="top 命令运行图" title="top 命令运行图"></p>
<h4>1. 第一行——基本信息</h4>
<p><img src="/img/bVOVJn?w=606&h=175" alt="第一行——基本信息" title="第一行——基本信息"></p>
<h5>load average:</h5>
<p> load average 表示系统负载均值,使用 top 或 uptime 可以查看到负载均值的信息,三个数值分表表示 1分钟内 、5分钟内 、 15分钟内的系统负载均值,要理解这三个数值的含义,首先要了解系统的“核数” </p>
<p><code>系统的核数 = CPU1 x CPU1的核数 + CPU2 x CPU2的核数 + CPUn x CPUn的核数 + ……</code> </p>
<p> 更清楚的讲,在Linux系统中输入命令<code>grep -c 'model name' /proc/cpuinfo</code>,即可得到核数。 </p>
<p> 回到负载均值,<code>负载均值的饱和值等于系统的核数</code>, 所以,根据load average观察系统负载首先要看系统中共有多少"核",单处理器单核的饱和值为 1,单处理器双核的饱和值为2,双处理器单核的饱和值也为2.<br> 理解负载均值的最经典的例子是把一个CPU的核当做一座单行单向桥,多核即为多行路单向桥.如图: </p>
<p><img src="/img/bVOVUy?w=641&h=256" alt="图片描述" title="图片描述"><br> <br>假定,目前系统是单核系统,根据上面的描述,它的负载饱和值为1.这种条件下,各种数值的含义如下:</p>
<ul>
<li><p>0.00 表示桥上没有任何车流,非常畅通</p></li>
<li><p>0.50 表示桥上有最高承载量一半的车流,也比较流畅.</p></li>
<li><p>1.00 表示桥上已经达到了最大承载量,如果再有车来,可能就要稍等才能上桥了,这种情况下,车速都会很慢,往往都会造成负载均值继续上升.</p></li>
<li><p>1.70 表示桥已经达到最大负载,且还有相对于桥最大负载70%的车辆等待上桥,这个时候的系统,已经要不堪重负了.</p></li>
</ul>
<p><strong>在实际应用中,重点关注5分钟,15分钟的负载均值,当达到0.7时,就需要调查原因了。</strong></p>
<h4>2. 任务信息</h4>
<p><img src="/img/bVOU1I?w=602&h=199" alt="任务信息" title="任务信息"></p>
<blockquote><p>僵尸进程:表示已经终止,但仍然保留一些信息的进程。其等待父进程调用wait(),就可以从内存中完全移除。 将是进程无法使用 <code>kill</code> 清理。如果要手动清理僵尸进程,需要找到其父进程,kill掉父进程后,LInux的 <code>init</code> 进程将接管该僵尸进程(linux中所有的子进程都需要有父进程,当父进程被kill后,其所有子进程将过继给init进程),init进程隔一段时间去调用wait(),来清除僵尸进程。</p></blockquote>
<h4>3. CPU使用情况</h4>
<p><img src="/img/bVOU2a?w=609&h=301" alt="CPU使用情况" title="CPU使用情况"></p>
<h4>4. 物理内存使用情况</h4>
<p><img src="/img/bVOVWa?w=602&h=169" alt="物理内存使用情况" title="物理内存使用情况"></p>
<h5>buff/cache:</h5>
<p>buffers 和 cache 都是内存中存放的数据,不同的是,buffers 存放的是准备写入磁盘的数据,而 cache 存放的是从磁盘中读取的数据 <br>在Linux系统中,有一个守护进程(daemon)会定期把buffers中的数据写入的磁盘,也可以使用 sync 命令手动把buffers中的数据写入磁盘。使用buffers可以把分散的 I/O 操作集中起来,减少了磁盘寻道的时间和磁盘碎片。 <br>cache是Linux把读取频率高的数据,放到内存中,减少I/O。Linux中cache没有固定大小,根据使用情况自动增加或删除。</p>
<pre><code># 手动把buffers写入硬盘并清空cache
sync && echo 3 > /proc/sys/vm/drop_caches
</code></pre>
<h4>5. 交换区使用情况</h4>
<p><img src="/img/bVOVWF?w=604&h=170" alt="交换区使用情况" title="交换区使用情况"></p>
<h5>Swap(内存交换区):</h5>
<p> 是硬盘上的一块空间。在内存不足的情况下,操作系统把内存中不用的数据存到硬盘的交换区,腾出内存来让别的程序运行。因此,开启swap会一定程度的引起 I/O 性能下降(阿里服务器默认不开)。</p>
<h4>6. 进程详细信息</h4>
<p><img src="/img/bVOU19?w=606&h=452" alt="进程详细信息" title="进程详细信息"></p>
<h3>二、灵活使用top</h3>
<h4>1. 命令行式使用</h4>
<p><code>-b</code>:以批处理模式操作 这种方式可以把top输出的内容以可读的形式写入文件<code>top -b >> top.txt</code> <br><code>-c</code>:显示完整的命令行(COMMAND),想查看进程执行的具体位置时,非常有用 <br><code>-d</code>:屏幕刷新间隔时间 <code>top -d 1</code>:表示每隔一秒刷新一次 <br><code>-s</code>:使用保密模式 <br><code>-S</code>:指定累积模式 <br><code>-i</code>:不显示任何闲置或者僵死进程 <br><code>-u<用户名</code>>:指定用户名 <br><code>-p<进程号</code>>:指定进程 <br><code>-n<次数></code>:指定循环显示的次数,到了次数自己退出。</p>
<h4>2. 交互式使用</h4>
<p>top命令显示系统实时状态,支持交互操作。执行top命令,显示系统状态界面(同时也是交互界面),输入交互命令:</p>
<p><code>1</code>:查看CPU每个核的使用情况 <br><code>h</code>:显示帮助画面,给出一些简短的命令总结说明 <br><code>k</code>:终止一个进程 <br><code>i</code>:忽略闲置和僵死进程,这是一个开关式命令 <br><code>q</code>:退出程序 <br><code>r</code>:重新安排一个进程的优先级别 <br><code>S</code>:切换到累计模式 <br><code>s</code>:改变两次刷新之间的延迟时间(单位为s),如果有小数,就换算成ms。输入0值则系统将不断刷新,默认值是5s <br><code>l</code>:切换显示平均负载和启动时间信息 <br><code>m</code>:切换显示内存信息 <br><code>t</code>:切换显示进程和CPU状态信息 <br><code>c</code>:切换显示命令名称和完整命令行 <br><code>M</code>:根据驻留内存大小进行排序 <br><code>P</code>:根据CPU使用百分比大小进行排序 <br><code>T</code>:根据时间/累计时间进行排序 <br><code>w</code>:将当前设置写入~/.toprc文件中。</p>
<h5>持续更新中……</h5>