MySQL高级
1.mysql数据库的层级
1、连接层 2、服务层 3、引擎层 4、存储层
2.join查询
# 机读的顺序 1 from 2 on 3 join 4 where 5 group by 6 having 7 select 8 distinct 9 order by 10 limit # 7中join查询的结果
3.索引
3.1.索引的简介
# 什么是索引 索引是帮助mysql高效获取数据的数据结构，即排好序的快速查找数据结构 索引功能：排序、查找 # 创建索引 select * from user where name="zs"; # create index 索引名 on 表名(字段) 索引名：idx_表名_字段名 create index idx_user_name on user(name) # 单值索引 select * from user where name="zs" and emal="qq.com" create index idx_user_nameEmail on user(name,email) # 复合索引 # alter 四中方式来添加数据表的索引 # 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为null ALTER TABLE 表名 ADD PRIMARY KEY(column_list); # 这条语句创建索引的值必须是唯一的（除了null外，null可能会出现多次） ALTER TABLE 表名 ADD UNIQUE index_name(column_list); # 添加普通索引，索引值可以出现多次 ALTER TABLE 表名 ADD INDEX index_name(column_list); # 该语句制定了索引为FULLTEXT，用于全文索引 ALTER TABLE 表名 ADD FULLTEXT index_name(column_list); # 删除索引 drop index [indexname] on 表名; # 查看 show index from table_name\G;
3.2.创建索引的情况
# 创建索引的情况 1、主键自动建立唯一索引 2、频繁作为查询条件的字段应该创建索引 3、查询中与其它表关联的字段，外键关系建立索引 4、频繁更新的字段不舍和创建索引，因为每次更新不单单更新了记录还会更新索引 5、where 条件里永不发哦的字段不创建索引 6、单键/组合索引的选择问题，who？（在高并发下倾向创建组合索引） 7、查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度 8、查询中统计或者分组字段 # 不创建索引的情况 1、表里记录太少 2、经常增删改的表，因为提高了查询速度，同时却会降低更新表的速度，如对表进行insert。update和delete。 因为更新表时，mysql不仅要保存数据，还要保存一下索引文件 3、数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。 注意：如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
3.3.性能优化
# MySql Query Optimizer 1、mysql中有专门负责优化SELECT语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的query提供他认为的执行计划（他认为最优的数据检索方式，但不见得是DBA认为是最优的，这部分最耗费时间） 2、当客户端想mysql请求一条query，命令解析器模块完成请求分类，区别出是SELECT并转发给mysql query optimizer时，mysql query optimizer首先会对整条query进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对query中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析query中的hint信息（如果有），看显示hint信息是否可以完全确定该query的执行计划。如果没有hint或hint信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据query进行写相应的计算分析，然后再得出最后的执行计划。 # MySQL常见瓶颈 CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候 IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候 服务器硬件的性能瓶颈：top，free，iostat和vmstat查看系统的性能状态
3.4.explain
# explain # 是什么？（查看执行计划） 使用explain关键字可以模拟优化器执行sql查询语句，从而知道mysql是如何处理你的sql语句的。 分析你的查询语句或是表结构的性能瓶颈 # 能干嘛？ 1、表的读取顺序 2、数据读取操作的操作类型 3、那些索引可以使用 4、那些索引被实际使用 5、表直接的引用 6、每张表有多少行被优化器查询 # 怎么玩？ explain + SQL语句 执行计划包含的信息 # 各字段解释 id： select查询的序列号，包含一组数字，表示查询中执行select字句或操作表的顺序 三种情况： id相同，执行顺序由上至下 id不同，如果子查询，id的序号会递增，id的值越大优先级越高，越先被执行 id相同不同，同时存在 select_type:查询的操作类型 table:表名 partitions: type:访问类型排序 显示查询使用了何种类型 从最好到最差依次是： system>const>eq_ref>ref>range>index>ALL system:表中只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个也可以忽略不计。 const：表示通过索引一次就找到了，const用于比较promary key或者unique索引，因为只匹配一行数据，所以很快，如将主键置于where列表中，mysql就能将查询转换为一个常量。 eq_ref：唯一性索引扫描：对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一性索引扫描。 ref:非唯一性索引扫描，返回匹配某个单独值得所有行； 本质上也是一种索引访问，它返回所有匹配某个单独值得行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。 range：只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引； 一般就是在你的where语句中出现了between、<、>、in等的查询 这种范围扫描索引扫描笔全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。 index：full index scan，index和ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小（也就是说虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘中读取）。 ALL：full table scan，将遍历全表以找到匹配的行。 备注：一般来说，得保证查询至少达到range级别，最好能达到ref。 possible_keys:显示可能应用在这张表中的索引，一个或多个。 查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用 key:实际使用的索引。如果为null，则没有使用索引 查询中若使用了覆盖索引，则该索引仅出现在key列表中 key_len:表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确的情况下，长度越短越好 key_len显示的值为索引字段的最大可能长度，并非实际使用的长度，即key_len是根据表定义计算而得，不是通过表检索出的。 ref:显示索引的那一列被使用了，如果可能的话，是一个常数。那些列或常量被使用于查找索引列上的值。 rows:根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数 filtered: extra:包含不适合在其他列中显示但不十分重要的额外信息 1、using filesort:说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引排序进行读取。 mysql中无法利用索引完成的排序操作称为“文件排序” 2、using temporary:使了临时表保存中间结果，mysql在对查询结果排序时使用临时表。常见于排序order by和分组查询 group by。 3、using index：表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率不错！ 如果同时出现using where，表明索引被用来执行索引键值得查找； 如果没有同时出现using where，表明索引用来读取数据而执行查找动作； 4、using where 5、using join buffer 6、impossible where 7、select tables optimized away 8、distinct # 热身case
3.5.join的优化
# left 建立索引应该建立在右表上 # right 建立索引应该建立在左表上 # join语句的优化 尽可能较少join语句中的NestedLoop的循环总次数：“永远用小结果集驱动大的结果集” 优先优化NestedLoop的内层循环； 保证join语句中被驱动表上join条件字段已经被索引 当无法保证被驱动表的join条件字段被索引且内层资源充足的前提下，不要太吝啬JoinBuffer的设置；
3.6.尝试设置索引
# 可以先把where后面用到的字段，组合起来设置一个复合索引，然后再用explain看看，如果extra还是显示using filesort或using temporary，再把原来的索引删了，再重新设置索引，优化的可不可以，还还可以看rows这里面的数值。
3.7.索引的使用
1、全值匹配日我最爱 2、最佳左前缀法则，如果索引了多列（复合索引），要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列 3、不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描 4、存储引擎不能使用索引中范围条件右边的列 5、尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select * 6、mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描 7、is null，is not null也无法使用索引 8、like以通配符开头（'%abc...'）mysql索引失效会变成全表扫描的操作 问题：解决like'%字符串%'时索引不被使用的方法？？ 解决：建立复合索引，查询的字段与复合索引一致 create index idx_user_NameAge on user(name,age); select name,age from user where name like "%aa%"; # 这个时候也会用到索引 select name,age，email from user where name like "%aa%"; # 这个时候索引失效，多了一个email 9、字符串不加单引号索引失效 10、少用or，用它来连接时会索引失效
3.8.建立索引的建议
# group by 基本上都需要进行排序，会有临时表产生 一般性建议： 1、对于单键索引，尽量选择针对当前query过滤性更好的索引 2、在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好 3、在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引 4、尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的
3.9.优化总结口诀
全值匹配我最爱，最左前缀要遵守； 带头大哥不能死，中间兄弟不能断； 索引列上少计算，范围之后全失效； LIKE百分写最右，覆盖索引不写星； 不等空值还有or，索引失效要少用； VAR引号不可丢，SQL高级也不难！
4.查询截取分析
4.1.分析
# 分析 1、观察，至少跑一天，看看生产的慢SQL情况 2、开启慢查询日志，设置阙值，比如超过5秒钟的就是慢sql，并将它抓取出来 3、explain+慢sql分析 4、show profile 5、运维经理 or DBA，进行sql数据库服务器的参数调优 # 总结 1、慢查询的开启并捕获 2、explain+慢sql分析 3、show profile查询sql在mysql服务器里面的执行细节和生命周期情况 4、sql数据库服务器的参数调优
4.2.优化原则
# 优化原则：小表驱动达标，即小的数据集驱动大的数据集 # 当B表的数据集必须小于A表的数据集时，用in优于exists select * from A where id in (select id from B) 等价于： for select id from B for select * from A where A.id = B.id # 当A表的数据集小于B表的数据集时，用exists优于in select * from A where exists (select 1 from B where B.id = A.id) 等价于： for select * from A for select * from B where B.id = A.id # 注意：A表和B表的ID字段应建立索引 # exists select ... from table where exists(subquery) 该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE或FALSE）来决定主查询的数据结果是否得意保留。 # 提示 1、exists(subquery)只返回TRUE或FALSE，因此子查询中的select * 也可以是select 1或其他，官方说法是实际执行时会忽略select清单，因此没有区别 2、exists子查询的实际执行过程可能经过了诱惑而不是我们理解上的逐条对比，如果担忧效率问题，可进行实际检验以确定是否有效率问题。 3、exists子查询往往也可以用条件表达式、其他子查询或者join来替代，何种最优需要具体问题具体分析。
4.3.order by优化
# order by子句，尽量使用index方式排序，避免使用fileSort方式排序: mysql支持两种方式的排序，filesort和index，index效率高 它指mysql扫描索引本身完成排序，filesort方式效率较低 order by 满足两情况，会使用index方式排序： 1、order by 语句使用索引最左前列 2、使用where子句与order by子句条件列组合满足索引最左前列 # 尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀 # 如果不在索引列上，filesort有两种算法： mysql就要启动双路排序和单路排序 # 优化策略： 增大sort_buffer_size参数的设置 增大max_length_for_sort_data参数的设置 why # 提高order by的速度 1、order by时select * 是一个大忌只query需要的字段，这点非常重要，在这里的影响是： 1.1.当query的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型，会用改进后的算法——单路排序，否则用老算法——多路排序； 1.2.两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些，所以提高sort_buffer_size。 2、尝试提高sort_buffer_size 不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的 3、尝试提高max_length_for_sort_data 提高这个参数，会增加用改进算法的概率，但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率
4.4.为排序使用索引
# mysql两种排序方式：文件排序或扫描有序索引排序 # mysql能为排序于查询使用相同的索引 KEY a_b_c(a,b,c) # 简写的创建索引 order by 能使用索引最左前缀 -ORDER BY a -ORDER BY a,b -ORDER BY a,b,c -ORDER BY a DESC,b DESC,c DESC 如果where使用索引的最左前缀定义为常量，则order by能使用索引 -WHERE a = const ORDER BY b,c -WHERE a = const AND b = const ORDER BY c -WHERE a = const AND b > const ORDER BY b,c 不能使用索引进行排序 -ORDER BY a ASC,b DESC,c DESC # 排序不一致 -WHERE g = const ORDER BY b,c # 丢失a索引 -WHERE a = const ORDER BY c # 丢失b索引 -WHERE a = const ORDER BY a,d # d不是索引的一部分 -where a in(...) ORDER BY b,c # 对于排序来说，多个相等条件也是范围查询
4.5.group by优化
# group by优化 和 order by优化原则一样 group by实质是先排序后进行分组，遵照索引建的最佳左前缀 当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置 where高于having，能写在where现代的条件就不要去having限定
4.6.慢查询日志
# 是什么 1、mysql的慢查询日志是mysql提供的一种日志记记录，它用来记录在mysql中响应时间超过阀值得语句，具体指运行时间超过long_query_time指的sql，则会被记录到慢查询日志中。 2、具体指运行时间超过long_query_time值的sql，则会被记录到慢查询日志中，long_query_time的默认值为10，意思是运行10秒以上的语句。 3、由他来查看那些sql超出了我们的最大忍耐时间值，比如一条sql执行超过5分钟，我们就算慢sql，希望能收集超过5秒的sql，结合之前explain进行全面分析。 # 怎么玩 1、说明： 默认情况下，mysql数据库没有开启慢查询日志，需要我们手动来设置这个参数； 当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件； 2、查看是否开启以及如何开启： 2.1.默认：SHOW VARIABLES LIKE '%slow_query_log%'; 默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的 可以通过设置slow_query_log的值来开启 2.2.开启：set global slow_query_log=1; 使用set global slow_query_log=1开启了慢查询日志只对当前数据库生效，如果mysql重启后则会失效 如果要永久生效，就必须修改皮遏制文件my.cnf（其它系统变量也是如此） 修改my.cnf文件，【mysqld】下增加或修改参数 slow_query_log和slow_query_log_file后，然后重启mysql服务器，也即将如下两行配置进my.cnf文件 slow_query_log=1 slow_query_log_file=/var/lib/mysql/atguigu-slow.log 关于慢查询的参数slow_query_log_file,它指定慢查询日志文件的存放路径，系统默认会给一个缺省的文件 host_name-slow.log（如果没有指定参数slow_query_log_file的话） 3、那么开启了慢查询日志后，什么样的sql才会记录到慢查询日志里面呢 这个是由参数long_query_time控制，默认情况下long_query_time为10秒 命令：SHOW VARIABLES LIKE '%long_query_time%'; 可以使用命令修改，也可以在my.cnf参数里面修改 假如运行时间正好等于long_query_time的情况，并不会被记录下来，也就是说，在mysql源码里是判断大于long_query_time，而非大于等于 4、case 4.1.查看当前多少秒算慢：SHOW VARIALBES LIKE 'long_query_time%' 4.2.设置慢的阀值时间：set global long_query_time=3; 修改为阀值到3秒钟的就是慢sql 4.3.为什么设置后看不出变化 需要重新连接或新开一个回话才能看到修改值。 SHOW VARIABLES LIKE '%long_query_time%'; show global variables like '%long_query_time%'; 4.4.记录慢sql并后续分析 查看/var/lib/mysql/atguigu-slow.log里的内容，可以看到超过3秒以上的sql 4.5.查询当前系统中有多少条慢查询记录 SHOW GLOBAL STATUS LIKE '%Slow_queries%'; 5、配置版 [mysqld]下配置： slow_query_log=1; slow_query_log_file=/var/lib/sql/atguigu-slow.log long_query_time=3; log_output=FILE # 日志分析工具mysqldumpslow 查看mysqldumpslow的帮助信息： 在生产环境中，如果要手工分析日志，查找、分析sql，显然是个体力活，mysql提供了日志分析工具mysqldumpslow s：是表示按照何种方式排序； c：访问次数 l：锁定时间 r：返回记录 t：查询时间 al：平均锁定时间 ar：平均返回记录数 at：平均查询时间 t：即为返回前面多少条的数据 g：后边搭配一个正则匹配模式，大小写不敏感的 # 得到返回记录集最多的10个sql musqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log # 得到访问次数最多的10gesql mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log # 得到按照时间排序的前10条里面含有左连接的查询语句 mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log # 另外建议在使用这些命令时结合 | 和more使用，否则有可能出现爆屏的情况 mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more
4.7.批量数据脚本
# 往表里插入1000w数据 1.建表 2.设置参数log_bin_trust_function_creators 创建函数，假如报错：This function has none of DETERMINISTIC.... # 优于开启过慢查询日志，因为我们开启了bin-log，我们就必须为我们的function指定一个参数。 show variables like 'log_bin_trust_function_creators'; set global log_bin_trust_function_creators=1; # 这样添加了参数以后，如果mysqld重启，上述参数又会消失，永久方法： Windows下my.inf【mysqld】加上log_bin_trust_function_creators=1 linux下/etc/my.cnf下my.cnf【mysqld】加上log_bin_trust_function_creators=1 3.创建函数，保证每条数据都不同： 3.1.随机产生字符串: DELIMITER $$ # 定义结束符号 CREATE FUNCTION rand_string(n,INT) RETURNS VARCHAR(255) # 创建函数 BEGIN # 声明参数 DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwsyzABCDEFGHIJKLMNOPQRSTUVWXYZ'; DECLARE return_str VARCHAR(255) DEFAULT ''; DECLARE i INT DEFAULT 0; WHILE i < n DO SET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1)); SET i = i + 1; END WHILE; RETURN return_str; END $$ 3.2.随机产生部门编号: DELIMITER $$ # 定义结束符号 CREATE FUNCTION rand_num() RETURN INT(5) BEGIN DECLARE i INT DEFAULT 0; SET i = FLOOR(100+RAND()*10); RETURN i; END $$ 4.创建存储过程 # 执行存储过程，往emp表添加随机数据 DELIMITER $$ # 定义结束符号 CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10)) BEGIN # set autocommit = 0 把autocommit设置成0 SET autocommit = 0; REPEAT SET i = i + 1; INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES((START + i),rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num()); UNTIL i = max_num END REPEAT; COMMIT; END $$ # 执行存储过程，往dept表添加随机数据 DELIMITER $$ # 定义结束符号 CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10)) BEGIN DECLARE i INT DEFAULT 0; SET autocommit = 0; REPEAT SET i = i + 1; INSERT INTO dept(deptno,dname,loc) VALUES((START+i),rand_string(10),rand_string(8)); UNTIL i = max_num; END REPEAT; COMMIT; END $$ 5.调用存储过程 DELIMITER ; CALL insert_dept(100,10);
4.8.show profile
# 是什么 是mysql提供可以用来分析当前会话中语句执行的资源消情况，可以用于sql的调优的测量 官网：http://dev.mysql.com/doc/refman/5.5/en/show-profile.html 默认情况下，参数处于关闭状态，并保存最近15次的运行结果 分析步骤： 1、是否支持，看看当前的mysql版本是否支持 show variables like 'profiling'; # 默认是关闭，使用前需要开启 2、开启功能，默认是关闭，使用前需要开启 set profiling=on; 3、运行sql # 测试，为了记录sql，便于查看那些sql慢了 4、查看结果，show profiles； # 显示执行的sql 5、诊断sql，show profile cpu,block io for query 上一步前面的问题sql数字号码，例如： show profile cpu,block io for query 1; # 上一步id为1的sql type: all：显示所有的开销信息 block io：显示块io相关开销 # 主要 context switches：上下文切换相关开销 cpu：显示cpu相关开销信息 # 主要 ipc：显示发送和接受相关开销信息 memory：显示内存相关开销信息 page faults：显示页面错误相关开销信息 source：显示和source_function,source_file,source_line相关的开销信息 swaps：显示交换次数相关开销的信息 6、日常开发需要注意的结论 converting HEAP to MyISAM 查询结果太大，内存都不够用了往磁盘上搬了 creating tmp table 创建临时表:a.拷贝数据到临时表，b.用完再删除 copying to tmp table on disk 爸内存中临时表复制到磁盘，危险！！ locked
4.9.全局查询日志
# 配置启用 在mysql的my.cnf中，设置如下： # 开启 general_log=1 # 记录日志文件的路径 general_log_file=/path/logfile # 输出格式 log_output=FILE # 编码启用 命令： set global general_log=1; set global log_output='TABLE'; 此后，你所编写的sql语句，将会记录到mysql库里的general_log表，可以用下面的命令查看 select * from mysql.general_log; # 永远不要在生产环境开启这个功能
5.mysql锁机制
# 锁 锁是计算机协调多个进程或线程并发访问某一资源的机制； 在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种提供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。 # 锁的分类 从对数据操作的类型（读\写）分： 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。 写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。 从对数据操作的粒度分：表锁和行锁 # 手动增加表锁 lock table 表名字 read(write),表名字2 read(write),其他; 例如：lock table mylock read,book write; # 查看表上加过的锁 show open tables; # 消除锁 unlock tables;
5.1.表锁(偏读)
# 特点 偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁重读的概率最高，并发度最低 # 表锁的总结 MyISAM在执行查询语句（SELECT）前，会自动给涉及的欧所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。 MySQL的标记锁有两种模式： 表共享读锁（Table Read Lock） 表独占写锁（Table Wtire Lock） 锁类型 可否兼容 读锁 写锁 读锁 是 是 否 写锁 是 否 否 结论： 结合上表，所以对MyISAM表进行操作，会有以下情况： 1、对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其他进程的写操作 2、对MyISAM表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。 简而言之，就是读锁会阻塞写，但是不会堵塞读。而写锁则会吧读和写都堵塞。
5.2.行锁(偏写)
# 特点： 偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发读也最高 InnoDB与MyISAM的最大不同有两点：一是支持事务(TRANSACTION)；二是采用了行级锁 # 行锁注意点： 无索引行锁升级为表锁 间隙锁危害 # 如何锁定某一行 begin; select * from books where a = 8 for update; commit; # select xxx... for update锁定某一行后，其它的操作会被阻塞，直到锁定行的会话提交commit # 总结 Inodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远由于MyISAM的表级锁定的。当系统并发量较高的时候，Innodb的整体性能和MyISAM相比就会有比较明显的优势了。 但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让Innodb的整体性能表现不仅不能比MyISAM高，甚至可能会更差。
5.3.分析行锁定
# 通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况 show status like 'innodb_row_lock%'; # 对各个状态量的说明如下： Innodb_row_lock_current_waits; 当前正在等待锁定的数量； Innodb_row_lock_time; 从系统启动到现在锁定总时间长度； Innodb_row_lock_time_avg; 每次等待说话平均时间； Innodb_row_lock_time_max; 从系统启动到现在等待最常的一次所花的时间； Innodb_row_lock_waits; 系统启动后到现在总共等待的次数； # 对于这5个状态变量，比较重要的主要是： Innodb_row_lock_time_avg（等待平均时长） Innodb_row_lock_waits（等待总次数） Innodb_row_lock_time（等待总时长）这三项 尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划。
5.5.优化建议——行锁
# 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。 # 合理设计索引，尽量缩小锁的方位 # 尽可能较少检索条件，避免间隙锁 # 尽量控制事务大小，减少锁定资源量和时间长度 # 尽可能低级别事务隔离
6.主从复制
# 复制的基本原理 slave会从master读取binlog来进行数据同步 # 三步骤： 1、master将改变记录到二进制日志(binary log)。这些记录过程叫做二进制日志事件，binary log events； 2、slave将master的binary log events拷贝到它的中继日志（relay log）； 3、slave重做中继日志中的事件，将改变应用到自己的数据库中。MySQL复制是异步的且串行化的 # 复制的基本原则 每个slave只有一个master 每个slave只能有一个唯一的服务器ID 每个master可以有多个slave # 复制的最大问题 延时 # 一主一从常见配置 mysql版本一致且后台以服务运行 主从都配置在[mysqld]节点下，都是小写 # 主机修改my.ini配置文件 1.【必须】主服务器唯一ID： server-id=1 2.【必须】启用二进制日志：log-bin=自己本地的路径/mysqlbin log-bin=D:/devSoft/MySQLServer5.5/data/mysqlbin 3.【可选】启用错误日志：log-err=自己本地的路径/mysqlerr log-err=D:/devSoft/MySQLServer5.5/data/mysqlerr 4.【可选】跟目录：basedir="自己本地路径" basedir="D:/devSoft/MySQLServer5.5" 5.【可选】临时目录：tmpdir="自己本地路径" tmpdir="D:/devSoft/MySQLServer5.5" 6.【可选】数据目录 datadir="自己本地路径/Data" datadir="D:/devSoft/MySQLServer5.5/Data" 7.主机，读写都可以：read-only=0 8.【可选】设置不要复制的数据库：binlog-ignore-db=mysql 9.【可选】设置需要复制的数据库：binlog-do-db=需要复制的主数据库名字 # 从机修改my.cnf配置文件 【必须】从服务器唯一ID 【可选】启用二进制日志 因修改过配置文件，请主机+从机都重启后台mysql服务； 主机从机都关闭防火墙； # 在Windows主机上建立账户并授权slave： GRANT PEPLICATION SLAVE ON *.* TO 'zhangsan'@'从机器数据库IP' IDENTIFIED BY "123456"; flush privileges; 查询master的状态： show master status; 记录下File和Positition的值 执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化 # 在Linux从机上配置需要复制的主机： CHANGE MASTER TO MASTER_HOST='主机IP', MASTER_USER='zhangsan', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysqlbin.具体数字', MASTER_LOG_POS=具体指; 启动从服务器复制功能: start slave show slave status\G: 下面两个参数都是yes，则说明主从配置成功！ Slave_IO_Running:Yes Slave_SQL_Running:Yes 主机建新库。新建表。insert记录，从机复制； 如何停止从服务复制功能: stop slave;
7.优化limit
# 优化前
SELECT customer_id,title,content
FROM product_comment
WHERE audit_status=1
AND product_id=199727
LIMIT 0,5;
# 优化后,利用表里的主键和自关联
SELECT t.customer_id,t.title,t.content
FROM (
SELECT `comment_id` # 主键,先用limit来查询主键
FROM product_comment
WHERE product_id=199727 AND audit_status=1 LIMIT 0,15
) a INNER JOIN product_comment t # 自关联
ON a.comment_id = t.comment_id;
8.删除表里的重复数据
# 删除重复数据 例如，删除评论表中对同一订单同一商品的重复评论，只保留最早的一条 步骤一：查看是否存在对于一订单同一商品的重复评论 步骤二：备份product_comment表 步骤三：删除同一订单的重复评论
9.数据库备份
# 对于任何数据库来说，备份都是非常重要的 数据库复制不能取代备份的作用 # 逻辑备份和物理备份 逻辑备份的结果为SQL语句，适合于所有存储引擎。 物理备份是对数据库目录的拷贝，对于内存表只备份结构。 # 全量备份和增量备份 全量备份是对整个数据库的一个完整备份 增量备份是在上次全量或增量备份基础上，对于更改数据进行的备份 Mysqldump 不支持增量备份 # 使用mysqldump来备份 mysqldump [OPTIONS] database [tables] # 备份多个表，表直接用空格隔开 mysqldump [OTITONS] --databases [OPTIONS] DB1 [db2...] mysqldump [OPTIONS] --all-databases [OPTIONS]
10.恢复mysqldump备份数据库
mysql -u -p dbname < backup.sql mysql>source /tmp/backup.sql
11.xtrabackup
# xtrabackup xtrabackup用于在线备份innodb存储引擎的表 innobackupex是对xtrabackup的封装并提供MyISAM表的备份功能 innobackupex是xtrabackup的插件，支持MyISAM备份，但也锁表 # 安装xtrabackup https://www.percona.com/downloads/XtraBackup/LATEST # 下载最新的版本 percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm # 安装依赖环境 yum install -y perl-DBD-MySQL.X86_64 perl-DBI.x86 perl-Time-HiRes.x86_64 perl-IO-Socket_SSL.noarch perl-TermReadKey.x86_64 # 安装下载好的压缩包 rpm -ivh percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm # 查看安装结果 cd /usr/bin # 利用xtrabackup全量备份 innobackupex --user=root --password=pws --parallel=2 /home/db_backup # 查看依赖库的名字 yum search 依赖包 yum search libnuma yum install numactl.x86_64 # innobackupex恢复备份 innobackupex --apply-log /home/db_backup/20190131/
6835
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
