1、MYSQL高级概述
【1】架构概述 【2】索引优化 【3】查询截取 【4】mysql锁机制 【5】主从复制
2、MYSQL概述
【1】mysql内核 【2】sql优化工程师 【3】mysql服务器的优化 【4】各种参数常量设定 【5】查询语句优化 【6】主从复制 【7】软硬件升级 【8】容灾百分 【9】sql编程
3、MYSQL Linux版的安装
【1】到Linux章节查看具体的安装步骤 【2】查看mysql是否安装 rpm -qa|grep -i mysql 【3】mysqladmin --version 查看mysql版本 【4】service mysql start 【5】service mysql stop 【6】ps -ef|grep mysql 查看后台服务 【7】chkconfig mysql on 设置开机自启动 【8】chkconfig --list | grep mysql
5、MYSQL的安装位置
【1】建立的库在机器上都能看的到。/var/lib/mysql 这个就是存放位置 【2】/usr/share/mysql 【3】/usr/bin 【4】/etc/init.d/mysqld
6、修改字符集
【1】修改默认的配置文件/usr/share/mysql —> my-default.cnf 【2】具体修改参数 [client] default-character-set=utf8 [mysql] default-character-set=utf8 [mysqld] init_connect='SET collation_connection = utf8_unicode_ci' init_connect='SET NAMES utf8' character-set-server=utf8 collation-server=utf8_unicode_ci skip-character-set-client-handshake skip-name-resolve 【3】重启mysql 重新建立库 【4】查看编码show variables like '%char%';
7、MySQL高级配置文件
【1】二进制日志log-bin 主从复制 【2】错误日志log-error 默认关闭 【3】查询日志log 【4】数据文件 rfm 表结构 ; myd 存放博爱数据 ; myi 存放表索引 【5】如何配置
8、MYSQL逻辑架构
【1】Connection Pool 连接池 【2】Interface Parser Optimizer Caches&Buffers 【3】Pluggable Storage Enginess 可拔插存储引擎 【4】文件存储层 File system Files & Logs 【5】MYSQL与众不同,它的架构可以在多种不同的场景中应用并发挥良好作用。插件式处处引擎架构查询处理和数据存储 提取相分离。 【6】连接层、服务层、引擎层、存储层—>四层结构
9、存储引擎概述
【1】show engines; 【2】show variables like '%storage_engine%'; 【3】MyISAM和InnoDB 阿里巴巴、淘宝用的哪个
10、SQL性能下降原因
【1】性能下降：执行时间长、等待时间长 【2】索引：select * from user where name='' and email=''; create index idx_user_name on user(name); # 建立索引 create index idx_user_nameEmail on user(name,email);# 多重索引 【3】关联查询太多join(设计缺陷或不得已的需求) 【4】服务器调优及各个参数设置(缓冲、线程数等)
11、SQL执行加载顺序
【1】手写：select distinct from 表 left join 表 on 条件 where group by having order by limit 【2】机读顺序：from on where group by having select distinct order by limit
12、七种JOIN理论
【1】select 表1.id,表2.id from 表1 left join 表2 on 表1.id=表2.id
13、七种JOIN的SQL编写
【1】建立部门表
create table department(
id int(11) not null auto_increment,
name varchar(30) default null,
ioc_add varchar(40) default null,
primary key(id)
)engine=innodb auto_increment=1 default charset=utf8;
【2】建立员工表
create table employee(
id int(11) not null auto_increment,
name varchar(20) default null,
department_id int(11) default null,
primary key(id),
key fk_department_id(department_id)
)engine=innodb auto_increment=1 default charset=utf8;
【3】插入部门数据
insert into department(department.name,department.ioc_add)
values('RD',11);
insert into department(department.name,department.ioc_add)
values('HR',12);
insert into department(department.name,department.ioc_add)
values('MK',13);
insert into department(department.name,department.ioc_add)
values('MIS',14);
insert into department(department.name,department.ioc_add)
values('FD',15);
【4】插入员工数据
insert into employee(employee.name,employee.department_id)
values('z3',1);
insert into employee(employee.name,employee.department_id)
values('z4',1);
insert into employee(employee.name,employee.department_id)
values('z5',1);
insert into employee(employee.name,employee.department_id)
values('w5',2);
insert into employee(employee.name,employee.department_id)
values('w6',2);
【5】笛卡尔集
select * from department;
select * from employee;
select * from department,employee; # 前两个乘积 25
【6】inner join
select *
from employee
inner join department
on employee.department_id=department.id
【7】left join
select *
from employee
left join department
on employee.department_id=department.id
【8】right join
select *
from employee
right join department
on employee.department_id=department.id
【9】增加where条件
select *
from employee
right join department
on employee.department_id=department.id
where department.id=null
【10】union 合并加去重
select *
from employee
left join department
on employee.department_id=department.id
union
select *
from employee
right join department
on employee.department_id=department.id
14、索引的概述
【1】Index,是帮助MySQL高效获取数据的数据结构。本质：索引是数据结构 【2】索引的目的在于提高查询效率，可以类比字典 【3】可以理解为排好序的快速查找数据结构 【4】在数据之外，数据库系统还维护着满足特定查找算法的数据结构 【5】折中比大小的思路，二叉树查找 【6】尽量使用逻辑删除，而不是使用逻辑删除 【7】索引本身也很大，所以不可能全部存在内存，因此往往以索引文件的形式存储在磁盘上 【8】我们所说的索引，如果没有特别指明，都是指B树（多路搜索树）结构组织的索引。
15、索引的优缺点
【1】优点：提升数据索引效率，降低数据的IO成本。通过索引排序，降低排序成本，降低了CPU消耗 【2】缺点：索引也是需要占空间的。（1）提升了查询表速度，但是降低了更新表的速度（2）需要花时间研究优秀的索引字段
16、索引分类与索引建立语句
【1】单值索引：一个索引只包含单个列，一个表可以有多个单列索引 【2】唯一索引：索引值必须唯一，单允许空值 【3】符合索引：即一个索引包含了多个列 【4】语法 （1）create [unique] index indexName on my_table(column_name(length));// 建立1 （2）alter my_table add [unique] index [indexName] on (column_name(length)); //建立2 （3）drop index [indexName] on my_table; （4）show index from table_name 例如show index from department; （5）四种方式添加数据表的索引: alter table table_name add primary key(column_list); 添加一个主键，意味着索引值必须是唯一的，且不为null alter table table_name unique index_name(column_list);索引值必须唯一，null除外 alter table table_name add index index_name(column_list); 普通索引，索引值可出现多次 alter table table_name add fulltext index_name(column_list);改语句指定了索引为fulltext，用于全文索引
18、建立索引情况概述
【1】主键自动建立唯一索引 【2】频繁作为查询条件的字段应该创建索引 【3】查询汇总与其他表关联的字段，外键关系建立索引 【4】频繁更新的字段，不适合创建索引 【5】where条件里用不到的字段不创建索引 【6】单键/组合索引的选择问题？who？（在高并发倾向创建组合索引） 【7】查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度 【8】查询中统计或分组字段
19、不建立索引情况概述
【1】表记录太少不建 【2】经常增删改的表不建 【3】如果某个数据列包含许多重复的内容，不建索引
20、性能分析
【1】MySQL查询优化分析器。他认为最优的，不见得是DBA认为是最优的 【2】MySQL常见瓶颈：CPU、IO、服务器硬件的性能瓶颈 【3】explain
21、Explain概述
【1】就是查看执行计划 【2】explain + 您的sql语句 【3】读取顺序、操作类型、哪些索引可以使用、哪些索引实际被使用、表之间的引用、每张表有多少行被优化器查询
22、Explain之id概述
【1】id相同，执行顺序由上而下 【2】id不同，id值越大，越先被执行
23、Explain之select_type与table概述
【1】select_type 查询类型 （1）simple 简单的select查询 （2）primary （3）subquery （4）derived 临时表 （5）union 联合查询 （6）union result 两种union结果的合并 【2】table 就是数据关联那张表
24、Explain之type概述
【1】从最好到最差依次是system->const—>eq_ref—>ref—>range—>index—>ALL 【2】能达到range级别，最好达到ref级别 【3】system 表只有一行记录，平时基本不会出现，可以忽略不计 【4】const 表示通过索引一次就找到了。只匹配一行数据 explain select * from (select * from employee where employee.id=1) as e; 【5】eq_ref，唯一性索引扫描，表中只有一条记录与之匹配。 explain select * from department,employee where employee.id=department.id; 【6】ref 返回匹配某个单独值的所有行，需要配合着使用的。 alter table department add index idx_col(name,ioc_add); --------------------------------------------------------- explain select * from department where department.name='RD' //这里的区别是非唯一性、就达到ref级别了 【7】range explain select * from employee where id BETWEEN 1 and 20 ----------------------------------- explain select * from employee where id in (1,2,6) 【8】index 全索引扫描 explain select id from employee 【9】ALL explain select * from employee
25、Explain之possible_keys与key概述
【1】到range和ref就很好了（百万数据以上） 【2】possible_keys预估可能用到的索引 【3】key是实际用到的索引 EXPLAIN select * from employee left join department on employee.department_id=department.id union select * from employee right join department on employee.department_id=department.id ---------------------------------------- EXPLAIN select department.name,department.ioc_add from department 【4】覆盖索引：就是select 字段.... 刚好和索引列一致。 EXPLAIN select department.name,department.ioc_add # 这两个字段刚好在前面建立了一个索引 from department
26、Explain之key_len概述
【1】key_len表示索引中使用的字节数，在不损失精确性的情况下，长度越短越好 【2】key_len显示值为索引子弹的最大可能长度，并非实际长度。即key_len是根据表定义计算而得，不是通过表内检索出的
27、Explain之ref概述
【1】显示索引的那一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
28、Explain之rows概述
【1】表的读取顺序 【2】rows 根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数 EXPLAIN select * from department,employee where department.id=employee.id and department.ioc_add='11'
29、Explain之Extra
【1】包含不适合在其他列中显示但十分重要的额外信息 【2】Using where; Using index 用了条件、用了索引 Using where; Using index; Using temporary; Using filesort -------------------------------------------------------- EXPLAIN select * from department,employee where department.id=employee.id and department.ioc_add='11' order by employee.id 【3】而Using filesort是不好的，如果可以尽快优化 【4】Using temporary这个更难受、产生了内部的临时表;order by group by往往会产生，会拖慢SQL 【5】所以order by、group by的字段，一定要建立索引，这样才能提高速度 【6】Using index比较牛批了，表示响应的操作使用了覆盖索引，避免访问了表的数据行，效率不错。 （1）如果同时出现Using where 表名索引被用来执行索引键值的查找; （2）如果没有出现Using where，表明索引用来读取数据而非执行查找动作; 【7】实际案例 （1）create index idx_user_nameEmail on user(name,email); （2）explain select email from user where user.email='cx@163.com' （3）explain select name,email from user 【8】覆盖索引Covering index select 数据列只从索引中就能够取得，不必读取数据行 【9】Using where 条件 【10】Using join buffer 使用了连接缓存 【11】impossible where : where的值总是false where a=1 and a=2 【12】select tables optimized away 在没有group by字句的情况下,基于索引优化操作或存储引擎优化 【13】distinct 找到第一个匹配后就不找了。
31、单表优化案例
【1】设计表 SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0;-- ---------------------------- -- Table structure for article -- ---------------------------- DROP TABLE IF EXISTS `article`; CREATE TABLE `article` (`id` int(6) NOT NULL AUTO_INCREMENT,`author_id` int(6) NULL DEFAULT NULL,`category_id` int(6) NULL DEFAULT NULL,`views` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,`comments` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,`title` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,`content` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Compact;-- ---------------------------- -- Records of article -- ---------------------------- INSERT INTO `article` VALUES (1, 1, 1, '1', '1', '1', '1'); INSERT INTO `article` VALUES (2, 2, 2, '2', '2', '2', '2'); INSERT INTO `article` VALUES (3, 1, 1, '3', '3', '3', '3');SET FOREIGN_KEY_CHECKS = 1; 【2】SQL select article.id,article.author_id from article where article.category_id=1 and article.comments > 1 order by article.views desc LIMIT 1 【3】详解 EXPLAIN select article.id,article.author_id from article where article.category_id=1 and article.comments > 1 order by article.views desc LIMIT 1 【4】查看索引情况 show index from article 【5】新建索引，发现在type上与优化了 create index idx_article_ccv on article(category_id,comments,views) ---------- EXPLAIN select article.id,article.author_id from article where article.category_id=1 and article.comments > 1 order by article.views desc LIMIT 1 【6】因为有个范围索引不太合适，所以用重新建立索引,再次查询可以了，Extra也优化了，牛批 drop index idx_article_ccv on article -------------------- create index idx_article_cv on article(category_id,views) -------------------- EXPLAIN select article.id,article.author_id from article where article.category_id=1 and article.comments > 1 order by article.views desc LIMIT 1
32、两个表优化案例
【1】建立两个表 （1） SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0;-- ---------------------------- -- Table structure for book -- ---------------------------- DROP TABLE IF EXISTS `book`; CREATE TABLE `book` (`id` int(6) NOT NULL AUTO_INCREMENT,`card` int(6) NULL DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Compact;-- ---------------------------- -- Records of book -- ---------------------------- INSERT INTO `book` VALUES (1, 20); INSERT INTO `book` VALUES (2, 13); INSERT INTO `book` VALUES (3, 7);SET FOREIGN_KEY_CHECKS = 1; （2） SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0;-- ---------------------------- -- Table structure for class -- ---------------------------- DROP TABLE IF EXISTS `class`; CREATE TABLE `class` (`id` int(20) NOT NULL AUTO_INCREMENT,`card` int(20) NULL DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 21 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Compact;-- ---------------------------- -- Records of class -- ---------------------------- INSERT INTO `class` VALUES (1, 10); INSERT INTO `class` VALUES (2, 7); INSERT INTO `class` VALUES (3, 3); INSERT INTO `class` VALUES (4, 13); INSERT INTO `class` VALUES (5, 17); INSERT INTO `class` VALUES (6, 4); INSERT INTO `class` VALUES (7, 9); INSERT INTO `class` VALUES (8, 13); INSERT INTO `class` VALUES (9, 16); INSERT INTO `class` VALUES (10, 16); INSERT INTO `class` VALUES (11, 20); INSERT INTO `class` VALUES (12, 13); INSERT INTO `class` VALUES (13, 3); INSERT INTO `class` VALUES (14, 15); INSERT INTO `class` VALUES (15, 5); INSERT INTO `class` VALUES (16, 20); INSERT INTO `class` VALUES (17, 6); INSERT INTO `class` VALUES (18, 9); INSERT INTO `class` VALUES (19, 6); INSERT INTO `class` VALUES (20, 4);SET FOREIGN_KEY_CHECKS = 1; 【2】查看SQL性能 EXPLAIN select * from class left join book on class.card=book.card where book.id is not null 【3】添加索引 alter table book add index Y (card) ------- alter table class add index Z (card) --------- EXPLAIN select * from class left join book on class.card=book.card where book.id is not null 【4】如果删除你再看下性能 drop index Y on book; 【5】结论：左连接就加载右表即可（反向加） 【6】如果发现DBA建的顺序和预期不符，连接查询的时候换一下位置
33、索引三表优化案例
【1】show index from class 【2】drop index Z on class 【3】查看和清除表的索引 show index from book drop index Y on book 【4】三表连接查询 EXPLAIN select * from class left join book on class.card=book.card left join phone on book.card=phone.card where book.card is not null and phone.card is not null 【5】建立索引 alter table book add index A (card) ------- alter table class add index B (card) -------- alter table phone add index C (card) -------- EXPLAIN select * from class left join book on class.card=book.card left join phone on book.card=phone.card where book.card is not null and phone.card is not null 【6】结论：索引最好设置在经常查询的字段中。 （1）尽量减少join语句中的循环总次数 （2）永远用小结果集驱动大的结果集 （3）如果无法保证驱动表join条件字段被索引，如果内存充足，可以把joinBuffer设置大点
34、索引优化
【1】索引失效（应该避免） 【2】建立数据库表 SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0;-- ---------------------------- -- Table structure for staffs -- ---------------------------- DROP TABLE IF EXISTS `staffs`; CREATE TABLE `staffs` (`id` int(6) NOT NULL AUTO_INCREMENT,`name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,`age` int(6) NOT NULL DEFAULT 0,`position` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,`time` datetime NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY (`id`) USING BTREE,INDEX `idx_staffs_name_age_position`(`name`, `age`, `position`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Compact;-- ---------------------------- -- Records of staffs -- ---------------------------- INSERT INTO `staffs` VALUES (1, '陈翔', 20, '经理', '2022-06-19 21:28:37'); INSERT INTO `staffs` VALUES (2, '蘑菇头', 19, '演员', '2022-06-19 21:28:56'); INSERT INTO `staffs` VALUES (3, '润土', 24, '主管', '2022-06-19 21:29:12');SET FOREIGN_KEY_CHECKS = 1; 【3】建立索引 alter table staffs add index idx_staffs_name_age_position(name,age,position) 【4】索引失效的原因总结 （1）全值匹配我最爱 EXPLAIN select * from staffs where staffs.name='陈翔' --------- EXPLAIN select * from staffs where staffs.name='陈翔' and age=20 ------------- EXPLAIN select * from staffs where staffs.name='陈翔' and staffs.age=20 and staffs.position='经理' --------------- 【5】索引失效了 EXPLAIN select * from staffs where staffs.age=19 and staffs.position='演员' ------------- EXPLAIN select * from staffs where staffs.position='演员' 【6】结论：alter table staffs add index idx_staffs_name_age_position(name,age,position) （1）最佳左前缀法则，如果索引了多列，查询要从索引最左列开始，并且不能跳过索引列 （2）----就是带头大哥不能死。----死了索引就失效 （3）----中间兄弟不能断。-------（断了就是使用了部分索引，只能使用到断之前的索引了）
35、索引优化-二
【1】不在索引列上做任操作（计算、函数、类型转换），会导致索引失效而转向全表扫描 EXPLAIN select * from staffs where staffs.name='陈翔' ---------------------- 失效的情况: EXPLAIN select * from staffs where left(staffs.name,1)='陈' 【2】结论：索引内容少计算
36、引擎优化-三
【1】常量查询 EXPLAIN select * from staffs where staffs.name='陈翔' and staffs.age=20 and staffs.position='经理' 【2】不能使用索引的范围判断，不然后面的索引就失效了 EXPLAIN select * from staffs where staffs.name='陈翔' and staffs.age>19 and staffs.position='经理'
37、索引优化-四
【1】尽量使用覆盖索引，减少使用select *;下面的语句就比36的多用了Using index EXPLAIN select staffs.`name`,staffs.age,staffs.position from staffs where staffs.name='陈翔' and staffs.age=20 and staffs.position='经理' 【2】原则:谁建立了索引，尽量就查谁
38、索引优化-五
【1】MYSQL不用使用！=或者<>，使用了会导致索引失效 【2】但是如果确实用到，也可以写
39、索引优化-六
【1】如果对非空字段进行空判断 EXPLAIN select * from staffs where name is null
40、索引优化-七
【1】两边有百分号，索引就失效了 EXPLAIN select * from staffs where name like '%陈翔%' 【2】但是如果只使用右边%，索引还是起作用的 EXPLAIN select * from staffs where name like '陈翔%' 【3】如果必须两边都用%%号，则使用覆盖索引(建的索引和查的字段、顺序、数量完全一致) create index idx_user_name_age on user(name,age) ------------------- EXPLAIN select user.name from user where user.name like '陈翔%'
41、索引优化-八
【1】varchar不加单引号导致索引失效，这个是开发中的重罪 EXPLAIN select * from staffs where staffs.name='陈翔' ----------------------- EXPLAIN select * from staffs where staffs.name=2000 # mysql 会使用隐式的类型转换 【2】少用or
44、索引面试题分析
【1】复合索引 create index idx_user_c1_c2_c3_c4 on user(c1,c2,c3,c4) 【2】全值匹配我最爱 【3】建立的是1、2、3、4，用的时候是4、3、2、1 ，索引仍不会失效，这是因为mysql聪明 【4】但是我们推荐的还是怎么建立，怎么使用索引 【5】范围之后的索引会全部失效，但是如果范围是最高的楼那就无所谓了 【6】Using filesort 九死一生
46、查询截取详解-小表驱动大表
【1】Explain 分析 【2】观察，至少跑1天，看看生产慢SQL情况。 【3】开启慢查询日志，是指阈值，超过5S的SQL都抓取出来 【4】Explain+慢SQL分析 【5】show profile：查询mysql服务器里的执行细节和声明周期情况 【6】运维经理 OR DBA，进行SQL数据库服务器的参数调优 【7】永远用“小表驱动大表” 【8】当B表数据小于A表的数据集时，用in优于exists select * from A where id in(select id from b) 【9】当A表数据集小于B表，用exists优于in 【10】将主查询的数据，放到子查询中做条件验证，根据验证结果（True或False）来决定查询的结数据结果是否保留
47、in与exists
【1】查询用in select * from staffs where staffs.id in (select book.id from book) ------------ select * from staffs where staffs.id in (select 1 from book) --------------- 【2】查询用exists select * from staffs where EXISTS (select book.id from book where book.id=staffs.id)
48、排序使用索引order by优化
【1】会不会产生filesort 【2】show index from staffs 让age成为带头大哥 【3】drop index idx_staffs_name_age_position on staffs 【4】create index idx_staffs_age_postion on staffs(age,position) 【5】带头大哥查询、排序,不会使用filesort EXPLAIN select staffs.age from staffs where staffs.age>20 order by staffs.age 【6】会使用filesort，效果差 EXPLAIN select staffs.age from staffs where staffs.age>20 order by staffs.position 【7】不会使用filesort，尽量使用Index方式排序 EXPLAIN select staffs.age,staffs.position from staffs where staffs.age>20 order by staffs.age,staffs.position 【8】order by （1）语句使用索引最做前列 （2）使用Where字句与Order by字句提哦啊见组合满足索引最左前列 【9】双路排序与单路排序 （1）MySQL4.1之前使用的是双路排序，扫描两次磁盘，最终得到数据 （2）在MySQL4.1之后，推出了单路排序 （3）由于单路是后出的，总体来说是好过双路的。但是用单路有问题，一次抓不完，性能反而不如双路。 【10】优化策略 （1）增大sort_buffer_size参数的设置 （2）增大max_length_for_sort_data参数的设置 （3）只要有order by就不用select * 【11】小结： （1）尽量不要用* （2）增加sort_buffer_size与max_length_for_sort_data长度 （3）扫描有序索引排序、mysql能为查询与排序使用相同的索引 （4）如果order by是常量，则可以排序，反正不产生filesort就对了 （5）跳楼机不要用、带头大哥不能死、中间兄弟不能断
49、慢日志查询
【1】group by实质是先排序后分组 【2】能用where搞定的，就不要写having 【3】慢查询日志，是MySQL提供的一种日志，超过long_query_time值的SQL，会被记录到日志中 【4】show VARIABLES like '%slow_query_log%' 查询是否开启 【5】开启与其他操作 （1）set global slow_query_log=1; # mysql重启后会失效，一般用不到永久的 （2）查询定义慢查询的时间show VARIABLES like '%long_query_time%' （3）set global long_query_time=3 修改后需要重新打开sql链接 （4）select sleep(4) （5）cd /var/lib/mysql 有主机名-slow.log 【6】mysqldumpslow 查询执行慢的sql （1）mysqldumpslow --help （2）得到返回记录最多的10个sql mysqldumpslow -s r -t 10 /var/lib/mysql/xxx-slow.log （3）得到访问次数最多的10个sql mysqldumpslow -s c -t 10 /var/lib/mysql/xxx-slow.log （4）得到按时间排序的前10条里面含有左连接的查询语句 mysqldumpslow -s t -t 10 -g “left join”/var/lib/mysql/xxx-slow.log （5）另外在使用只写命令时，建议加上 | more mysqldumpslow -s r -t 10 /var/lib/mysql/xxx-slow.log | more
50、批量插入数据脚本
【1】mysql写函数 【2】新建库 create database bigData; use bigData 【3】创建表 create table department( id int UNSIGNED PRIMARY key auto_increment, no MEDIUMINT UNSIGNED not null DEFAULT 0, name varchar(20) not null DEFAULT "", ico varchar(20) not null DEFAULT "" )ENGINE=INNODB DEFAULT CHARSET=GBK; ---------------------------------- create table employee( id int UNSIGNED PRIMARY key auto_increment, no MEDIUMINT UNSIGNED not null DEFAULT 0, name varchar(20) not null DEFAULT "", job varchar(20) not null DEFAULT "", manage_no MEDIUMINT UNSIGNED not null DEFAULT 0, hiredate date not null, salary DECIMAL(7,2) not null, package DECIMAL(7,2) not null, department_no MEDIUMINT UNSIGNED not null DEFAULT 0 )ENGINE=INNODB DEFAULT CHARSET=GBK; 【4】创建函数如果报错this function has none of deterministic... show variables like 'log_bin_trust_function_creators' --------- set global log_bin_trust_function_creators=1; 也是临时的设置，但是临时设置也足够了 【5】随机产生字符串的函数 DELIMITER $$ create function rand_string(n int) returns varchar(255) beginDECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';DECLARE return_str varchar(255) DEFAULT '';DECLARE i int DEFAULT 0;while i < n DOset return_str=concat(return_str,substring(chars_str,floor(1+rand()*52),1));set i= i+1;end while;return return_str; end $$ 【6】随机产生部门编号 DELIMITER $$ create function rand_num() returns int(5) beginDECLARE i int DEFAULT 0;set i=floor(100+rand()*10); return i; end $$ 【7】创建存储过程 DELIMITER $$ create procedure insert_employee(in start int(10),in max_num int(10)) begin DECLARE i int DEFAULT 0;set autocommit=0;repeatset i=i+1;insert into employee(employee.`no`,employee.name,employee.job,employee.manage_no,employee.hiredate,employee.salary,employee.package,employee.department_no) values ((start+i),rand_string(6),'salesman',0001,CURDATE(),2000,400,rand_num());until i=max_numend repeat;COMMIT;end $$ ----------- DELIMITER $$ create procedure insert_department(in start int(10),in max_num int(10)) begin DECLARE i int DEFAULT 0;set autocommit=0;repeatset i=i+1;insert into department(department.`no`,department.name,department.ico) values ((start+i),rand_string(10),rand_string(8));until i=max_numend repeat;COMMIT;end $$ ------- drop procedure insert_department 这是删除的方法 【8】调用存储过程 select * from department; ----------------- delimiter; call insert_department(100,10); --------------- 插入50万 delimiter; call insert_department(1000000,500000);
51、用show profile进行sql分析
【1】查询优化-慢查询日志-批量数据脚本-show profile-全局日志查询 【2】mysql提供可以用来分析当前会话中语句执行的资源消耗情况，可以用于SQL调优测量 【3】默认情况下默认是关闭的，并保存最近15此的运行结果 【4】show VARIABLES like 'profiling' （1）set profiling=on （2）show VARIABLES like 'profiling' 【5】show profiles; （1）show profile cpu,block io for query 3 #找个耗时比较久的查看替换3 （2）执行后可以看到每一步的执行耗时； （3）converting heap to MyISAM 查询结果太大，内存不够，开始用磁盘了 （4）creating temp table 创建了临时表 （5）Copying to temp table on disk 把内存中的临时表复制到磁盘，非常危险！！！ （6）locked 死锁
52、全局查询日志
【1】只能在测试环境使用 【2】开启查询 （1）set global general_log=1; （2）set global log_output='TABLE'; （3）此后所写的SQL将会记录到mysql库里的general_log表 （4）select * from mysql.general_log;
53、数据库锁理论概述
【1】锁是计算机协调多个进程或线程并发访问某一资源的机制 【2】商品只有一件，谁能购买到，需要用到锁的隔离和并发的矛盾 【3】锁的分类 （1）从对数据操作的类型分：读锁（共享）、写锁（排他） （2）从对数据操作的粒度分，分为表锁和行锁
54、读锁案例讲解
【1】表锁、行锁
【2】建立表演示
create table mylock(
id int not null primary key auto_increment,
name varchar(20)
)engine myisam;
---------------
insert into mylock(name) values('a');
insert into mylock(name) values('b');
insert into mylock(name) values('c');
insert into mylock(name) values('d');
insert into mylock(name) values('e');
【3】锁表
（1）show open tables #查看表的锁情况 In_use 为0 代表没有锁
------------
show open tables from study;
（2）lock table mylock read,book write; mylook book的In_use都是1 带包加锁
（3）unlock tables;
【4】加锁后lock table mylock read;
（1）select * from mylock; session 1可以读
（2）update mylock set mylock.name='a2' where id=1; session 1不能修改
（3）session 1不能读别的表，因为锁着在 select * from book;
（4）但是另个一连接session 2，可以查锁，也可以查别的表
（5）session 2如果修改session 1的表，则需要等待，只有session 1执行unlock tables;才能修改
55、锁的案例讲解
【1】lock table mylock write; ---------------------------------- 【2】读写情况 （1）select * from mylock; 可读 （2）update mylock set mylock.name='a3' where mylock.id=1;可写 （3）但是锁着，不update的话，select * from book;会报错Table 'book' was not locked with LOCK TABLES （4）session2 被阻塞，需要等待锁被释放 ---------------------------------- 【3】查看被加锁的表 show open tables; 有1的就是锁着的 ---------------------------------- 【4】myisam要偏读，不要偏写。
56、行锁理论
【1】行锁粒度最小，发生锁冲突的概率最低，并发度也最高。 【2】innodb与myisam的最大不同点：一是事务支持，而是采用了行级锁 【3】行锁支持事务 （1）事务的ACID属性：原子性、一致性、隔离性、持久性 （2）并发事务处理带来的问题：丢失、脏读、不可重复度、幻读 （3）事务隔离级别：未提交度、已提交读、可重复读、可序列化 （4）show VARIABLES like 'tx_isolation'; mysql出厂的时候默认是可重复读
57、行锁案例讲解
【1】数据准备 create table test_innodb_lock( a int(11), b varchar(20) )engine=INNODB 【2】插入数据 insert into test_innodb_lock values(1,"A"); insert into test_innodb_lock values(2,"B"); insert into test_innodb_lock values(3,"C"); insert into test_innodb_lock values(4,"D"); insert into test_innodb_lock values(5,"E"); insert into test_innodb_lock values(6,"F"); 【3】建立两个单值索引 create index test_innodb_lock_a on test_innodb_lock(a); create index test_innodb_lock_b on test_innodb_lock(b); 【4】查询数据 select * from test_innodb_lock; （1）set autocommit=0; 不让自动提交 （2）update test_innodb_lock set b='4001' where test_innodb_lock.a=4; （3）COMMIT
59、索引失效行锁变表锁
【1】varchar必须加单引号，否则是重罪 【2】update test_innodb_lock set a=41 where b=4000; 假如b varchar没加引号 【3】这个session1不写单引号，session2规范执行就回被阻塞 【4】这样就会导致行锁变表锁
60、间隙锁危害
【1】就是session1用范围条件、session2用相等检索 【2】mysql会根据范围错杀，而不会放过，seesion2会阻塞
61、如何锁一行
【1】select xxx for update 锁定某一行后，其他的操作会被阻塞 【2】直到上面的语句执行提交commit，其他的操作才会被执行
62、行锁总结
【1】行锁针对innodb，性能相比myisam有比较明显的优势 【2】也有缺点，当使用不当，innodb会行锁变表锁，性能可能比myisam更差 【3】当前正在等待锁定的数量 show STATUS like 'innodb_row_lock%' 【4】系统启动后到现在总共等待的次数 show STATUS like 'innodb_row_lock_waits%' 【5】页锁，在行锁和表锁之前，会触发死锁，用的相对少很多
63、主从复制
【1】复制的基本原理 （1）slave会从master读取binlog来进行数据同步 （2）主机改变记录到二进制日志—>slave将master日志拷贝到它的中继日志—>slave将自己日志改变到数据库 （3）每一个slave只有个master、每个slave只能有且仅有一个服务器ID （4）每个master可以有多个slave 【2】要求 （1）mysql版本要求一致，且后台已经启动服务 （2）必须同一网段，就是“互相”可以访问 （3）主机修改my.cnf文件 server-id=1、log-bin=/var/.../data/mysqlbin （4）log-err/var/...../data/mysqlerr （5）basedir="mysql的安装路径" （6）tmpdir="/var/.../MySQLServer5.6" （7）datadir="/var/devSoft/data/" （8）read-only=0 （9）binlog-do-db=ww2 【3】从机修改 my.cnf （1）log-bin （2）server-id=2 【4】修改后要重启数据库 （1）grant replication slave on *.* to 'zs'@'从机器数据库IP' indentified by '123456' （2）show master status;查询主机状态 【5】从机的配置 （1）change master to master_host='主机IP',MASTER_USER='zs',MASTER_PASSWORD='123456',MASTER_LOG_FILE='File名字',MASTER_LOG_POS=Position数字; （2）start slave （3）show slave statusG 启动后必须看到Slave_IO_Running Yes、Slave_SQL_Running Yes 【6】最后在主机插入一条数据，从机自动就会复制这条数据，两个数据库都能查的到。 【7】stop slave 停止从机。从机的配置一定要根据show master status主机的状态决定
