深入浅出MySQL

<a href="#0-%e6%9c%ac%e6%96%87%e9%93%be%e6%8e%a5">本文链接</a>

<a href="#1-%e8%af%b4%e6%98%8e">说明</a>

<a href="#2-%e7%b4%a2%e5%bc%95%e7%9a%84%e8%ae%be%e8%ae%a1%e5%92%8c%e4%bd%bf%e7%94%a8">索引的设计和使用</a>

<a href="#1-%e8%ae%be%e8%ae%a1%e7%b4%a2%e5%bc%95%e5%8e%9f%e5%88%99">设计索引原则</a>

<a href="#2-%e5%b0%8f%e5%b8%b8%e8%af%86">小常识</a>

<a href="#3-btree%e7%b4%a2%e5%bc%95">btree索引</a>

<a href="#3-sql%e4%b8%ad%e7%9a%84%e5%ae%89%e5%85%a8%e9%97%ae%e9%a2%98">sql中的安全问题</a>

<a href="#4-%e5%b8%b8%e7%94%a8sql%e6%8a%80%e5%b7%a7">常用sql技巧</a>

<a href="#5-sql%e4%bc%98%e5%8c%96%e8%bf%87%e7%a8%8b">sql优化过程</a>

<a href="#1-%e4%ba%86%e8%a7%a3sql%e6%89%a7%e8%a1%8c%e9%a2%91%e7%8e%87">了解sql执行频率</a>

<a href="#2-explain%e5%88%86%e6%9e%90">explain分析</a>

<a href="#3-%e6%9f%a5%e7%9c%8b%e7%b4%a2%e5%bc%95%e4%bd%bf%e7%94%a8%e6%83%85%e5%86%b5">查看索引使用情况</a>

<a href="#4-%e5%ae%9a%e6%9c%9f%e5%88%86%e6%9e%90%e8%a1%a8%e5%92%8c%e6%a3%80%e6%9f%a5%e8%a1%a8">定期分析表和检查表</a>

<a href="#5-%e5%ae%9a%e6%9c%9f%e4%bc%98%e5%8c%96%e8%a1%a8">定期优化表</a>

<a href="#6-%e5%b8%b8%e7%94%a8sql%e4%bc%98%e5%8c%96">常用sql优化</a>

<a href="#6-%e4%bc%98%e5%8c%96%e6%95%b0%e6%8d%ae%e5%ba%93%e5%af%b9%e8%b1%a1">优化数据库对象</a>

<a href="#1-%e4%bc%98%e5%8c%96%e8%a1%a8%e7%9a%84%e6%95%b0%e6%8d%ae%e7%b1%bb%e5%9e%8b">优化表的数据类型</a>

<a href="#2-%e9%80%9a%e8%bf%87%e6%8b%86%e5%88%86%e8%a1%a8%e6%8f%90%e9%ab%98%e8%a1%a8%e7%9a%84%e8%ae%bf%e9%97%ae%e6%95%88%e7%8e%87">通过拆分表提高表的访问效率</a>

<a href="#3-%e4%bd%bf%e7%94%a8%e4%b8%ad%e9%97%b4%e8%a1%a8%e6%8f%90%e4%be%9b%e7%bb%9f%e8%ae%a1%e6%9f%a5%e8%af%a2%e9%80%9f%e5%ba%a6">使用中间表提供统计查询速度</a>

<a href="#7-%e9%94%81%e9%97%ae%e9%a2%98">锁问题</a>

<a href="#1-%e9%94%81">锁</a>

<a href="#2-myisam%e8%a1%a8%e9%94%81">myisam表锁</a>

<a href="#3-innodb%e9%94%81">innodb锁</a>

本站:

<a href="http://blog.csdn.net/alex_my/article/details/72626925">深入浅出mysql</a>

个人博客:

<a href="http://alex-my.xyz/books/database/%e6%b7%b1%e5%85%a5%e6%b5%85%e5%87%bamysql">深入浅出mysql</a>

之前先看了《mysql必知必会》一书，再来看这本。二者很多内容相同，所以本书只节选了部分内容来看。

最适合索引的列是出现在<code>where/join/order by/group by/distinct</code>中的列。而不是出现在select中的列。

使用唯一索引。索引的基数越大，效果越好。比如存放身份号码的列具有不同的值，很容易区分各行。而用来记录性别的列，只含有’m’和’f’，对这样的列进行索引，没多大用处。

使用短索引。如果索引的值很长，那么查询的速度会受到影响。

利用最左索引。假设建立了user_id, user_name, status(按该顺序建立)复合索引， 实际上是创建了三个mysql可利用的索引。

只要在查询中指定了user_id的值，无论是否有user_name或者status，mysql都可以使用这个索引。但是，如果不包含user_id，只包含了user_name或status，那么，mysql不能利用这个索引。

限制索引的数目。并非索引越多越好。除了要占用额外的磁盘空间外，还会降低写操作的性能。在修改表的时候，索引必须进行更新，索引越多，所花的时间也越多。

删除不再使用或者很少使用的索引。从而减少对表更新操作的影响。

系统自动创建primary key的索引。

系统自动创建unique key的索引。

myisam和innodb默认创建的都是btree索引。

当使用<code>&gt;,&lt;,&gt;=,&lt;=,between,!=,&lt;&gt;或者like 'pattern'(pattern不以通配符开始)</code>操作符时，都可以使用相关列上的索引。

sql注入攻击

但如果没有任何过滤，传入的<code>user_name=admin'#</code>，就会出现大问题。

因为<code>#</code>会将后面的句子注释。因此密码验证功能就丢失了。

又如传入<code>user_name=admin' or 1=1</code>

防范

使用preparestatement，预编译sql后，通过绑定参数来执行。

对特殊字符进行转换

定义函数对输入进行校验。

使用rand()获取随随即行

使用show [session | global] statusa来获得服务器状态信息。

session表示当前连接，如果直接写show status, 则默认是session。

global表示从数据库上次启动至今的统计结果。

在结果中可以看下

通过以上信息，就可以了解到是以更新为主还是查询为主了。

假设有一张account表存在id(主键), phone字段,且未对phone建立索引。

通过id查找:

输出:

我们可以看到，rows=1, 表示扫描了1行接得到了结果。

通过phone查找:

可以看到，扫描了48130行才得到想要的结果。

如果这个查询很常用，就有必要对phone建立索引，特别是account表很庞大的时候，速度优势很明显。

explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的引擎和写出更佳的查询语句。

select_type, select的类型，有以下几种值:

simple: 表示简单的select，没有union和子查询。

primary: 查询中包含任何复杂的子查询, 最外层被标记为primary。

<code>示例a</code>

subquery: 在<code>示例a</code>中, 子句就被标记为subquery。

derived: 在from列表中的子查询被标记为derived。mysql会将这个子查询的结果放到一个临时表中，相当于该临时表是从子查询中派生出来的。

union: 若第二个select出现在union之后,就会被标记为union。

<code>示例b</code>

union result: 从union获取结果被标记为union result。见<code>示例b</code>的结果。

subquery和union还可以被标记为dependent和uncacheable

dependent: 意味着子句依赖于外层发现的结果，见<code>示例a</code>。

uncacheable: 意味着select某些特性阻止结果缓存于一个item_cache中(<code>todo:需要更多的资料</code>)。

type, 表示在表中找到所需行的方式，也称访问类型。

常见有<code>all, index, range, ref, eq_ref, const, system, null</code>, 从左到右，性能从最差到最好。

null: 执行时甚至不用访问表或使用索引，比如找出找出最小用户id（主键）

const: 表中最多只有一个匹配行，用于primary key或者unique索引。因为只匹配一行，所以速度快。

system: 是const的特殊类型，为表中只有一行的时候。

eq_ref: 简单的说就是在多表连接中使用primary key和unique key做为关联条件。

ref: 搜索时使用的索引不是primary key或unique，但可以是复合索引的第一个值。

<code>以上都是很理想的索引使用情况</code>。

range: 用索引来检索一定范围的行, between, &lt;, &gt;。除此之外, in, or也会显示range，但性能有差异。

index: 只查找索引，不能包含非索引值。

all: 将遍历全表以查找匹配的行。

possible_keys: 提示使用了哪些索引可以找到该行。

keys: 使用的索引。

key_len: 索引使用的长度。

ref: 哪些列或常量被用于查找索引列上的值。

子句中使用a.role_id来查找匹配。

rows: 估算的找到所需的记录所需要读取的行数。

filtered: 显示了通过条件过滤出的行数的百分比估计值。

extra: 比较重要的额外信息。

handler_read_key: 这个值表示一个行被索引值读的次数，很低的值表示增加的索引对性能改善不高，因为索引并不经常使用。

handler_read_rnd_next: 表示在数据文件中读下一行的请求书。如果值很高，表示进行了大量的扫描。通常说明索引不正确或写入的查询没有利用索引。

按照搜索出的数据，这个库的索引情况并不理想。

分析表 analyze table account;

检查表 check table account;

输出: <code>1 client is using or hasn't closed the table properly</code>

修复该错误:

优化表 optimize table account, user, …

该命令可以将表中的空间碎片进行合并，并且可以消除由于删除或者更新造成的更新浪费。

如果已经删除了表中的一大部分，或者对含有可变长度行的表进行了很多更改，可以使用该命令进行优化。

该命令支队myisam,bdb,innodb起作用。

优化insert语句

插入多行时,尽量使用多个值表的insert语句，减少客户端与服务端的链接，关闭消耗。

使用insert delayed into替代insert into。仅限于isam和myisam表。需要插入的数据会在内存中排队，直到有空闲。而客户端会立即得到ok响应。好处是极高的插入速度，客户端不需要等待太长的时间。坏处是如果没有来得及插入数据，在内存队列中的数据将会丢失，而且不能反悔自动递增id。

批量插入时，可以增加bulk_insert_buffer_size变量值来提高速度，只有myisam有效。这个参数是批量插入缓存大小，默认8m。

使用load data infile载入(未使用过)

优化group by语句

在包含了group by语句，会有一个默认的排序，如果没有必要对结果进行排序，可以用order by null取消排序。

优化order by语句

当以下情况时,order by也可以借助索引来排序

索引满足where和order by

order by的顺序和索引顺序相同

order by都是升序或者降序的

<code>todo 需要更多的资料</code>

优化嵌套查询

使用join来替代子查询，速度会快很多，尤其是对子查询中的列建有索引的情况下，性能会更好。

mysql不需要再内存中创建临时表来完成这个逻辑上需要两个步骤的工作。

使用sql提示

use index: 添加希望mysql去参考的索引列表

ignore index: 忽略指定的索引

假设就这一个索引，忽略之后，mysql会使用全表扫描

force index: 强制使用指定索引

大利器procedure analyse()

用法 <code>select ... from ... where ... procedure analyse([max_elements,[max_memory]])</code>

max_elements 默认值256, 查找每一列不同值时所需关注的最大不同值的数量。还用这个值来检查给予建议的值是否是enum。

max_memory 查找每一列所有不同值时可能分配的最大的内存数量。

示例 <code>select * from user procedure analyse(16, 256);</code>,注意看optimal_fieldtype中给予的建议。如果表中数据量小，要注意区分建议的局限性。

对于myisam类型的表:

* 垂直拆分: 把主键和一些列放在一张表，把主键和另一些列放在另一张表。特别在一张表中，有的列常用，而有些列不常用的时候，可以这么拆。好处是使得数据行变小，查询的时候会减少i/o次数。缺点是查询所有数据的时候要join操作。

* 水平拆分: 把很大的表拆成好几张，比如最近3个月的数据一张，3个月以前的表一张。

如果表很大，要在上面对一定范围内的数据做查询统计。可以把这部分数据导出到另一张一模一样的表中，然后在做处理。

这边省略了导出的时间。

在新表上处理，不会对应用产生负面影响。

可以在新表上增加索引，临时字段等，提供性能。

表级锁: 开销小，加锁快，不会出现死锁，锁定粒度大，引起冲突概率最高，并发度最低。

行级锁：开销大，加锁慢，会出现思索，锁定粒度最小，引起冲突概率最低，并发度最高。

页面所: 会出现死锁，介于表级锁和行级锁中间。

myisam支持表级锁。

innodb支持表级锁和行级锁，默认采用行级锁。

仅从锁的角度来说:

表级锁更适合以查询为主，只有少量按索引条件更新数据的应用。

行级锁适合有大量按索引条件并发更新数据的应用。

可以通过<code>show status like 'table_locks_%';</code>来查看表锁定争夺。

如果table_locks_waited值比较高，说明存在着较严重的表级锁竞争。

加读锁: 不会阻塞其它用户对表的读操作，但是，会阻塞对该表的写操作，包括加锁的这个用户。

加写锁: 只有加锁的这个用户可以对表进行读写操作，其它用户读写操作都会等待。

示例:

myisam会在select前给锁设计的表添加读锁。在update,insert,delete时，加写锁。

myisam会一次获得所需要的锁，不会变更。比如获得了读锁，则不能执行写操作。

并发插入

concurrent_insert 用于控制并发插入行为。<code>show variables like 'concurrent_insert';</code>

当concurrent_insert=0的时候，不允许并发插入。

当concurrent_insert=1的时候，表中间没有被删除的行，加读锁的时候，允许另一个用户从表尾插入数据，这个也是默认设置。

当concurrent_insert=2的时候，无论表中间有没有删除的行，都允许另一个用户从表尾插入数据。

我这边显示的是concurrent_insert=auto, 行为和concurrent_insert=1一样。

可以在空闲的时候使用optimize table来整理空间碎片，收回因删除记录而产生的中间空洞。

锁调度

myisam的读写是互斥的，串行的。

假设同时有读和写请求到来，写会获得锁。

即使读请求先在获取锁的等待队列中，写请求后到，写会获得锁。

因为mysql认为写操作比读操作重要。

这也是myisam不适合有大量更新操作同时又有很多读操作的原因。

大量的写操作会造成读操作难以获得锁。

可以使用一些设置来调节这些行为:

启动时指定参数low-priority-updates，使myisam默认给予读请求以优先的权利。

set low_priority_updates=1,使得该连接发出的写操作优先级降低。

通过指定insert,update,delete语句的low_priority属性,降低该语句的优先级。

给系统参数max_write_lock_count指定一定的值，当读请求达到这个值后，就将写请求的优先级降低。

<code>todo</code>