Mysql 部分优化
使用范式会有哪些优缺点：
优点：
- 避免数据冗余
- 减少数据的空间
- 减轻维护数据完整性的麻烦
- 范式设计的表通常比较小，可以更好的利用内存的优势，提高我们的检索速度
缺点：
- 经过范式设计出来的表，会很多，越严格来遵循，表就越多
- 多表关联会慢，可能会导致索引失效
- 范式越高，对操作性能可能就越低
反范式设计：
不符合3NF的设计就是范式
在NOSQL中大量运用
运用场景：
检索性能要求高
对冗余字段很少做更新操作
Int(?):
? 代表宽度，表示没有设定的宽度的时候，用0来补充，
需要配合zerofill来使用create table bbb(id int(11) zerofill,id2 int(1));
varchar
变长的字符串
优势：节省空间
缺点：在update的时候，如果数据长度变长了，就会去申请空间，就会有额外的工作，对性能有影响
什么情况下使用varchar:
1. 字符串的最大长度比平均真实值大的比较多，这个时候就用Varchar(100) , 平均用的只有20字
但是他只也有80,90的长度的值
2. 更新频率比较低的字符串字段
存储空间：
非空：65533字节
可空：65532字节
Char
存储长度：create table t (char(10) not null)
非空： 存储255字节
可以为空：存储254字节
定长的字符串
什么情况使用：
1. 适合存储比较短的字符串
2. 存储固定长度或者存储长度比较接近的字符串 例如：手机号，MD5加密的密码
3. 经常变更的数据，不容易产生碎片
4. 存储比较短的字符串值，例如 Y/N
注意点：如果插入的值末尾有空格，会自动截取掉
Text
非二进制字符串，变长的
Blob
用于存储二进制数据，大二进制数据
Datetime
8字节
YYYY-MM-DD HH:MM:ss
Timestamp
4字节
秒数
区别：
存储空间
Datetime 存储到9999年， 查看比较直观
Timestamp 存储到2038年
查询linux服务器性能参数
top
htop
free –m
df –hl
A join B on…
mysql执行顺序：
from: 左表和右表的笛卡尔积 ，产生虚拟表v1
on： 对v1 进行筛选，根据join-condition过滤， 产生v2
join: 如果是left join 就把左表在v2 的结果通过on 过滤，通过右表的外部行过滤，产生v3
where: 过滤条件 产生v4
GROUP BY ：分组，产生v5
Having: 过滤条件，产生 v6
Select : load 出指定的列，产生v7
Distinct: 排重，产生v8
Order by: 排序，产生v9
Limit: 取出指定的行，并返回结果
常用的join
1. 笛卡尔积：
2. 左连接 left join left outer join
左表全部保留，右表关联不上的用null表示
select * from t1 left join t2 on t1.id=t2.id;
3. 右连接 right join
4. 内连接 inner join
5. 查询左边独有部分数据
6. 查询又表边独有部分数据
7 全连接
在mysql中没有full join 正确的做法
索引
索引是排好序的快速查找数据结构
BTREE： balance tree 平衡树
索引类型：
1. 主键索引
create table t1(id int primary key);
alter table t2 add primary key(id);
2. 普通索引
create index 索引名 on 表（列名）;
create index idx_id on t3(id);
3. 唯一索引（unique）
create table t1(id int unique);
create unique index索引名 on 表（列名）;
注意：unique可以为null，也可以重复， “”不能重复出现
4. 全文索引
支持中文全文索引：
sphinx 斯芬克斯, coreseek
删除索引：
alter table 表名 drop index 索引名称
查询索引：
show keys from t1
show index from t1;
多列索引创建：
create index idx_id_name on t3(id,name);
explain:
语法：explain select …
执行计划包含的信息：
id: 标识符，表示执行顺序
select_type: 查询类型
table: 查询的表
partitions: 使用的哪个分区，需要结合表分区才能看到
type:联接的类型，例如index索引
possible_keys:可能用到的索引，保存的是索引名称，如果是多个索引的话，用逗号隔开
key:实际用到的索引，保存的是索引名称，如果是多个索引的话，用逗号隔开
key_len: 使用到是索引的长度
ref: 引用索引对应的表中哪些行
rows:显示mysql认为执行查询时必须要返回的行数
filtered:通过过滤条件之后对比总数的百分比
extra: 额外的信息, using file sort, using where
id：
表示select标识符，同时表名执行顺序，也就是说他是一个序列号
id的值：
1.id相同
2.id全不同
3.id部分相同
id相同：按顺序执行
id全不同：数字越大越先执行
案例：
explain select * from student stu where stu.classid=(select id from classes cls where stu.age=20 and cls.schoolid = (select id from school where name=‘xxx'));
id部分相同：先按照数字大的先执行，然后数字相同的按照从上往下的顺序执行
案例：
explain select * from student stu where stu.classid in (select id from classes cls where cls.schoolid=(select id from school where name='xx'));
select_type: 查询类型
1. simple 简单的查询
2. primary 主查询，或者说是最外层查询
3. subquery 子查询
4. union union中的第二个或者后面的那一个select
5. union result: union之后的结果
6. dependent union : union中第二个或者后面的select,取决我们查询
7. dependent subquery 子查询中的第一个select
8. derived 衍生表
1.primary/subquery:
2.union / union result 说明：<union1,2> :数字代表id
1. dependent union、dependent subquery
explain select * from student s where s.classid in (select id from classes where classno='2017001' union select id from classes where classno='2017002');
2. derived
在mysql5.5、mysql5.6.x中：
mysql5.7.x
explain详细讲解
1.partitions
explain select * from test_partition where id > 7;
在mysql5.7之前：
2.type
表示按照某种类型来查询，例如按照索引类型查找，按照范围查找
2.1 const
表示 表中最多有一个匹配行
2.2 eq_ref
对于每个来自于前面的表的记录，从该表中读取唯一一行
2.3 ref
对于每个来自于前面的表的记录，所有匹配的行从这张表中取出
2.4 ref_or_null
类似于ref，但是可以搜索包含null值的行
2.5. index_merge
出现在使用一张表中的多个索引时，mysql会讲这多个索引合并到一起
2.6 range
按指定的范围来检索，很常见
例如> < between … and 等等
2.7 index
从索取树中查找
2.8 ALL
全表扫描
3.possible_keys、key
4.key_len
key_len越小越好，当然不能为null
5. ref
6.rows
0
分析：因为我们这里没有用上索引，所以是全表扫描，从type=ALL ， key=NULL 可以看出来
rows的值越小越好，说明检索的数据越少
7.Extra
7.1 using where
使用了where查询
7.2 using index
使用了覆盖索引， 出现这个值是比较好的现象
2. using where 和using index结合：表明索引被用来条件查找
3. using join buffer
表示使用了连接缓存
explain select * from student where classid in(select id from classes);
4.using filesort
使用了文件内排序，必须要优化，严重影响性能
5.using temporary
使用了中间表或者是临时表
案例1：
业务需求
查询类目为1（java）并且浏览次数(browse_times)大于1000，
购买人数（buy_times）最多的课程 的信息
1. 写出符合业务的SQL:
select * from course where category_id=1 and browse_times>1000 order by buy_times desc limit 1;
2.初步分析执行计划
2. 根据之前讲到的复合索引的概念， 以及在where条件和order by 排序中，需要建立复合索引
create index idx_cate_browse_buy on course(category_id,browse_times,buy_times);
3. 再次执行explain
此时：type=range(范围查找)， 使用上了索引idx_cate_browse_buy
rows=2
但是：using filesort 还是存在？
4. 因为 索引字段 browse_times 使用了范围查找，会使索引后面的字段失效
那我们把范围改成=，来查看结果：
此时，using filesort 没有了，同时出现了type=ref, ref=const, 这是好的现象
当然，此时问题也出现了？查询的数据不符合需求了
5. 此时，我们需要跳过browse_times索引字段，重新来建立只有category_id,buy_id的复合索引：
drop index idx_cate_browse_buy on course;
create index idx_cate_buy on course(category_id,buy_id);
此时就是比较适合的优化方法
案例2：
业务需求
查询所有手机颜色，并把与之相同颜色的电脑也查询出来
1.写出sql:
select * from phone p left join computer c on p. color = c. color;
2.首先来分析一把
发现出现了全表扫描
3.分析需要建立索引，那到底是建在左表还是右表
4.首先在左表建立索引：
create index idx_color on phone(color);
结果：
5.在右表再来建立索引
drop index idx_color on phone;
create index idx_color on computer(color);
6,在左表和右表同时建立索引
create index idx_color on phone(color);
案例3：
业务需求
查询手机，电脑，小车，属于同一颜色的记录
1. 写sql:
select * from phone p inner join computer cp on p.color=cp.color inner join car c on c.color=cp.color;
2. 执行explain结果：
先删除全部索引
3. 在phone中color字段上面建立索引
4. 在computer中color字段建立索引：
5. 在car中建立索引
create index idx_color on car(color);
以下情况会索引失效
1.选择列尽量少用*号
2. 尽量遵循复合索引的字段顺序(最左前缀)
刚刚我们建了索引 idx(name,age,phone)
2.1 name上面加条件
2.2 name和age上面加条件
2.3 name和age,phone上面加条件
2.4 去掉最左边的列name
2.5 where中使用name,phone作为查找条件
3. 复合索引字段不要使用> <查找，会是索引后的字段失效
4. 不要在索引列上做计算，自动类型转换操作，及函数计算
4.1在索引列上做计算
4.2自动类型转换
4.3 函数计算
5. 不要使用不等于!= <> ，会使索引失效
6. 尽量不要出现 is null， is not null, 可能也会导致索引失效
7. like中字符串左边值不固定，会使索引失效
8.where中要尽量少用or
in和exists
分析：
in执行顺序：先执行in中的子查询，作为我们最外层循环，主查询作为内层循环
exists: 主查询作为最外层循环，子查询作为最内层循环
而根据我们的时间复杂度，最外层循环小于内层循环的时候，使用的时间相对较少
结论：永远小表驱动大表是最优的选择方式
慢查询：
show status 显示系统状态参数
1. 查询慢查询是否开启：
show global variables like '%slow_query%';
slow_query_log 为OFF 表示没有开启，mysql默认是没有开启慢查询日志记录功能的
set slow_query_log=1 开启慢查询日志记录
2. 查看慢查询时间
show variables like 'long_query_time'; 默认为10秒钟，意识是大于10s才算慢查询
3. 修改慢查询记录的时间
set long_query_time=1; 设置时间为1s
4. 记录慢查询日志的次数
show status like 'slow_queries';
慢查询日志分析工具Mysqldumpslow
mysqldumpslow --help 查看帮助信息 或者man mysqldumpslow
whereis mysqldumpslow 查看linux命令安装在哪个目录
-s:排序
t:查询时间
c:访问次数
l:锁定定时
r：返回的记录
-g:后边可以跟正则表达式，用于过滤
-t NUM :显示的条数
使用案例：
1. 取出耗时最长的前2条sql
mysqldumpslow -s t -t 2 慢日志文件
2. –g从结果中过滤-g后面的正则表达式的内容
mysqldumpslow -s t -t 2 -g 'exists' test2-slow.log
show profile ：用于分析当前会话中语句执行的资源消耗情况
1.查看是否开启profile
show variables like ' profiling';
2.开启profile
set profiling=1; 或者 set profiling=ON;
3.关闭profile
set profiling=0; 或者 set profiling=OFF;
4.显示当前执行的语句和时间
show profiles;
5. 显示当前查询语句执行的时间和系统资源消耗
show profile cpu,block io for query 4;
锁
1. 锁分类
a) 按照对数据操作的类型分：读锁，写锁
读锁：也称为共享锁。 针对同一资源，多个并发读操作可以并行执行，并且互不影响,不能写
写锁：也称排它锁。当前线程写数据的时候，会阻塞其它线程来读取或者写数据
b) 按照数据操作的粒度：表锁，行锁，页锁
表锁：就是锁住整个表，主要在myisam表存储引擎中出现
行锁：锁定单独的某个表中的某一行记录，主要用于innodb存储引擎
2. 表锁
特点：锁住整个表，所以开销小，加锁比较快，无死锁情况， 锁的粒度大，在并发情况下，
产生锁等待的概率比较高，所以说，支持的并发数比较低，一般用于查找
手动增加表锁：
lock table 表名 [read|write]，表名 [read|write]…
94
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
