深入浅出MySQL

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

0 前述

• 个人博客来源: 深入浅出MySQL

• Keylala在线工具

• 程序员导航

1 索引的设计和使用

1 设计索引原则

• 最适合索引的列是出现在WHERE/JOIN/ORDER BY/GROUP BY/DISTINCT中的列。而不是出现在SELECT中的列。

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

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

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

  user_id, user_name, status
  user_id, user_name
  user_id
  

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

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

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

2 小常识

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

3 BTRee索引

• MyISAM和InnoDB默认创建的都是BTRee索引。
• 当使用>,<,>=,<=,BETWEEN,!=,<>或者LIKE 'pattern'(pattern不以通配符开始)操作符时，都可以使用相关列上的索引。

2 SQL中的安全问题

• SQL注入攻击

  -- 通过账号密码获取账号id, 如果存在，则登陆成功
  SELECT id FROM account WHERE user_name='admin' AND password='opds123456';
  

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

  SELECT id FROM account WHERE user_name='admin '# ' AND password='123456';
  

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

  又如传入user_name=admin' OR 1=1

  SELECT id FROM account WHERE user_name='admin' OR 1=1 AND password='123456';
  

• 防范

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

  • 对特殊字符进行转换

    // MySQL C API
    mysql_real_escape_string() 
    // PHP
    mysql_real_escape_string()
    

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

3 常用SQL技巧

• 使用RAND()获取随随即行

  SELECT * FROM user ORDER BY RAND() LIMIT 10;
  

4 SQL优化过程

1 了解SQL执行频率

使用SHOW [SESSION | GLOBAL] STATUSA来获得服务器状态信息。
SESSION表示当前连接，如果直接写SHOW STATUS, 则默认是SESSION。
GLOBAL表示从数据库上次启动至今的统计结果。

SHOW GLOBAL STATUS LIKE 'Com_%';

在结果中可以看下

Com_select: 执行select操作次数
Com_insert: 执行insert操作次数，批量插入只累加1
Com_update: 执行update操作次数
Com_delete: 执行delete操作次数

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

2 EXPLAIN分析

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

通过id查找:

EXPLAIN SELECT * FROM account WHERE id = 10839792;

输出:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	    account	const	PRIMARY	        PRIMARY	4	const	1	NULL

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

通过phone查找:

EXPLAIN SELECT * FROM account WHERE phone = 1899713xxxx;

输出:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	account	    ALL	    NULL	NULL	NULL	NULL	48130	Using where

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

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

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

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

  • SIMPLE: 表示简单的SELECT，没有UNION和子查询。

  • PRIMARY: 查询中包含任何复杂的子查询, 最外层被标记为PRIMARY。
    示例a

    EXPLAIN 
    SELECT a.role_id, a.role_name, (SELECT SUM(money) AS total_recharge 
    FROM user_pay b WHERE a.role_id = b.role_id) FROM user a;
    

  • SUBQUERY: 在示例a中, 子句就被标记为SUBQUERY。

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

    EXPLAIN 
    SELECT t.role_id, t.role_name FROM (SELECT role_id, role_name FROM user LIMIT 10) t;
    

  • UNION: 若第二个SELECT出现在UNION之后,就会被标记为UNION。
    示例b

    EXPLAIN
    SELECT role_id FROM user_pay WHERE money=6 
    UNION
    SELECT role_id FROM user_pay WHERE money=30;
    

  • UNION RESULT: 从UNION获取结果被标记为UNION RESULT。见示例b的结果。

  • SUBQUERY和UNION还可以被标记为DEPENDENT和UNCACHEABLE

    • DEPENDENT: 意味着子句依赖于外层发现的结果，见示例a。
    • UNCACHEABLE: 意味着SELECT某些特性阻止结果缓存于一个item_cache中(TODO:需要更多的资料)。

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

  • 常见有ALL, index, range, ref, eq_ref, const, system, NULL, 从左到右，性能从最差到最好。

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

    EXPLAIN 
    SELECT MIN(role_id) FROM user;
    

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

    EXPLAIN 
    SELECT * FROM user WHERE role_id=123;
    

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

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

    -- role_id是user和user_activity的主键
    EXPLAIN 
    SELECT * FROM user, user_activity WHERE user.role_id=user_activity.role_id;
    

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

    -- 存在复合索引 (channel_id, xx, xx)
    EXPLAIN 
    SELECT * FROM user WHERE channel_id=1;
    

  • 以上都是很理想的索引使用情况。

  • range: 用索引来检索一定范围的行, BETWEEN, <, >。除此之外, IN, OR也会显示range，但性能有差异。

    -- level是索引，channel_id不是索引。
    -- type=range
    EXPLAIN SELECT role_id, name FROM user WHERE level BETWEEN 10 AND 30 AND channel_id < 10;
    -- type=all
    EXPLAIN SELECT role_id, name FROM user WHERE channel_id < 10;
    

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

    -- level是索引，channel_id不是索引
    -- type=index
    EXPLAIN SELECT level FROM user;
    -- type=all
    EXPLAIN SELECT level, channel_id FROM user;
    

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

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

• keys: 使用的索引。

• key_len: 索引使用的长度。

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

  EXPLAIN 
  SELECT a.role_id, a.role_name, (SELECT SUM(money) AS total_recharge 
  FROM user_pay b WHERE a.role_id = b.role_id) FROM user a;
  

  输出:

  id	table	type	possible_keys	key	    key_len	ref	    rows
  1	a	    ALL	    NULL	        NULL	NULL	NULL	40	    
  2	b		ref	    role_id	        role_id	8	    test.a.role_id	3
  

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

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

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

• Extra: 比较重要的额外信息。

3 查看索引使用情况

SHOW STATUS LIKE 'Handler_read%';

输出:

Variable_name	        Value
Handler_read_first	    38
Handler_read_key	    2044
Handler_read_last	    0
Handler_read_next	    988904
Handler_read_prev	    0
Handler_read_rnd	    2154
Handler_read_rnd_next	1022038

• Handler_read_key: 这个值表示一个行被索引值读的次数，很低的值表示增加的索引对性能改善不高，因为索引并不经常使用。
• Handler_read_rnd_next: 表示在数据文件中读下一行的请求书。如果值很高，表示进行了大量的扫描。通常说明索引不正确或写入的查询没有利用索引。
• 按照搜索出的数据，这个库的索引情况并不理想。

4 定期分析表和检查表

• 分析表 ANALYZE TABLE account;

• 检查表 CHECK TABLE account;
  输出: 1 client is using or hasn't closed the table properly
  修复该错误:

  REPAIR TABLE account;
  

5 定期优化表

• 优化表 OPTIMIZE TABLE account, user, …
• 该命令可以将表中的空间碎片进行合并，并且可以消除由于删除或者更新造成的更新浪费。
• 如果已经删除了表中的一大部分，或者对含有可变长度行的表进行了很多更改，可以使用该命令进行优化。
• 该命令支队MyISAM,BDB,InnoDB起作用。

6 常用sql优化

• 优化INSERT语句

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

    INSERT INTO account VALUES(1, 2), (3, 4)...
    

  • 使用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取消排序。

  SELECT role_id, SUM(money) AS total_recharge FROM user_pay GROUP BY role_id ORDER BY NULL;
  

• 优化ORDER BY语句
  当以下情况时,ORDER BY也可以借助索引来排序

  • 索引满足WHERE和ORDER BY
  • ORDER BY的顺序和索引顺序相同
  • ORDER BY都是升序或者降序的
    TODO 需要更多的资料

• 优化嵌套查询

  • 使用JOIN来替代子查询，速度会快很多，尤其是对子查询中的列建有索引的情况下，性能会更好。
  • MySQL不需要再内存中创建临时表来完成这个逻辑上需要两个步骤的工作。

• 使用SQL提示

  • USE INDEX: 添加希望MySQL去参考的索引列表

    SELECT * FROM user USE INDEX(user_id_phone) WHERE user_id=1 AND phone=123456;
    

  • IGNORE INDEX: 忽略指定的索引

    SELECT * FROM user IGNORE INDEX(user_id_phone) WHERE user_id=1 AND phone=123456;
    

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

  • FORCE INDEX: 强制使用指定索引

    SELECT * FROM user FORCE INDEX(user_id_phone) WHERE user_id=1 AND phone=12356;
    

5 优化数据库对象

1 优化表的数据类型

• 大利器PROCEDURE ANALYSE()
• 用法 SELECT ... FROM ... WHERE ... PROCEDURE ANALYSE([max_elements,[max_memory]])
  • max_elements 默认值256, 查找每一列不同值时所需关注的最大不同值的数量。还用这个值来检查给予建议的值是否是ENUM。
  • max_memory 查找每一列所有不同值时可能分配的最大的内存数量。
• 示例 SELECT * FROM user PROCEDURE ANALYSE(16, 256);,注意看Optimal_fieldtype中给予的建议。如果表中数据量小，要注意区分建议的局限性。

2 通过拆分表提高表的访问效率

对于MyISAM类型的表:

• 垂直拆分: 把主键和一些列放在一张表，把主键和另一些列放在另一张表。特别在一张表中，有的列常用，而有些列不常用的时候，可以这么拆。好处是使得数据行变小，查询的时候会减少I/O次数。缺点是查询所有数据的时候要JOIN操作。
• 水平拆分: 把很大的表拆成好几张，比如最近3个月的数据一张，3个月以前的表一张。

3 使用中间表提供统计查询速度

• 如果表很大，要在上面对一定范围内的数据做查询统计。可以把这部分数据导出到另一张一模一样的表中，然后在做处理。
• 这边省略了导出的时间。
• 在新表上处理，不会对应用产生负面影响。
• 可以在新表上增加索引，临时字段等，提供性能。

6 锁问题

1 锁

• 表级锁: 开销小，加锁快，不会出现死锁，锁定粒度大，引起冲突概率最高，并发度最低。
• 行级锁：开销大，加锁慢，会出现思索，锁定粒度最小，引起冲突概率最低，并发度最高。
• 页面所: 会出现死锁，介于表级锁和行级锁中间。
• MyISAM支持表级锁。
• InnoDB支持表级锁和行级锁，默认采用行级锁。
• 仅从锁的角度来说:
  • 表级锁更适合以查询为主，只有少量按索引条件更新数据的应用。
  • 行级锁适合有大量按索引条件并发更新数据的应用。

2 MyISAM表锁

• 可以通过SHOW STATUS LIKE 'table_locks_%';来查看表锁定争夺。

  Variable_name	        Value
  Table_locks_immediate	29029735
  Table_locks_waited	    30
  

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

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

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

• 示例:

  -- 加锁
  LOCK TABLE user WRITE;
  -- 可以进行读写操作
  -- 但其它用户则需要等待，比如另起终端执行读操作。
  SELECT * FROM user LIMIT 10;
  -- 直到释放了锁
  UNLOCK TABLES;
  

• MyISAM会在SELECT前给锁设计的表添加读锁。在UPDATE,INSERT,DELETE时，加写锁。

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

• 并发插入

  • concurrent_insert 用于控制并发插入行为。SHOW VARIABLES LIKE 'concurrent_insert';
  • 当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指定一定的值，当读请求达到这个值后，就将写请求的优先级降低。

3 InnoDB锁

TODO