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

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