0 本文链接
本站:
深入浅出MySQL
个人博客:
深入浅出MySQL
1 说明
- 之前先看了《MySQL必知必会》一书，再来看这本。二者很多内容相同，所以本书只节选了部分内容来看。
- 链接: MySQL必知必会
2 索引的设计和使用
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不以通配符开始)操作符时，都可以使用相关列上的索引。
3 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=1SELECT 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() - 定义函数对输入进行校验。
4 常用SQL技巧
-
使用RAND()获取随随即行
SELECT * FROM user ORDER BY RAND() LIMIT 10;
5 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。
示例aEXPLAIN 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。
示例bEXPLAIN 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:需要更多的资料)。
- DEPENDENT: 意味着子句依赖于外层发现的结果，见
-
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;
-
6 优化数据库对象
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 使用中间表提供统计查询速度
- 如果表很大，要在上面对一定范围内的数据做查询统计。可以把这部分数据导出到另一张一模一样的表中，然后在做处理。
- 这边省略了导出的时间。
- 在新表上处理，不会对应用产生负面影响。
- 可以在新表上增加索引，临时字段等，提供性能。
7 锁问题
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来整理空间碎片，收回因删除记录而产生的中间空洞。
- concurrent_insert 用于控制并发插入行为。
- 锁调度
- MyISAM的读写是互斥的，串行的。
- 假设同时有读和写请求到来，写会获得锁。
- 即使读请求先在获取锁的等待队列中，写请求后到，写会获得锁。
- 因为MySQL认为写操作比读操作重要。
- 这也是MyISAM不适合有大量更新操作同时又有很多读操作的原因。
- 大量的写操作会造成读操作难以获得锁。
- 可以使用一些设置来调节这些行为:
- 启动时指定参数low-priority-updates，使MyISAM默认给予读请求以优先的权利。
- SET LOW_PRIORITY_UPDATES=1,使得该连接发出的写操作优先级降低。
- 通过指定INSERT,UPDATE,DELETE语句的LOW_PRIORITY属性,降低该语句的优先级。
- 给系统参数max_write_lock_count指定一定的值，当读请求达到这个值后，就将写请求的优先级降低。
3 InnoDB锁
TODO
