企业官网建设流程全解析

1.cmd登陆mysql
D:\phpstudy_pro\Extensions\MySQL5.7.26\bin>mysql -uroot -p

2.查看数据库引擎：show engines;

3.sql慢执行时间长，等待时间长
3.1查询语句写的烂:select不要使用*,
3.2索引失效
1创建索引--单值索引
select * from user where name='';
create index idx_user_name on user(name)

2创建索引--复合索引
select * from user where name='' and email='';
create index idx_user_nameEmail on user(name,email)

3.3关联查询太多join,不要使用子查询
3.4服务器调优及各个参数设置(缓冲，线程数等)

4.sql执行顺序：
手写顺序：
select distinct
<select_list>
from <left_table><join_type>
join <right_table>on <join_condition>
where
<where_condition>
group by
<group_by_list>
having
<having_condition>
order by
<order_by_condition>
limit <limit_number>
机读顺序:
from <left_table>
on <join_condition>
<join_type>join <right_table>
where <where_condition>
group by <group_by_list>
having <having_condition>
select
distinct <select_list>
order by <order_by_condition>
limit <limit_number>

7种join图
例子：
CREATE TABLE `tbl_emp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`deptId` int(11) DEFAULT NULL,
PRIMARY KEY (`id`) ,
KEY `fk_dept_id`(`deptId`)
)ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8;

CREATE TABLE `tbl_dept` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`deptName` varchar(30) DEFAULT NULL,
`locAdd` varchar(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8;

insert into tbl_dept(deptName,locAdd) values('RD',11);
insert into tbl_dept(deptName,locAdd) values('HR',12);
insert into tbl_dept(deptName,locAdd) values('MK',13);
insert into tbl_dept(deptName,locAdd) values('MIS',14);
insert into tbl_dept(deptName,locAdd) values('FD',15);

insert into tbl_emp(NAME,deptId) values('z3',1);
insert into tbl_emp(NAME,deptId) values('z4',1);
insert into tbl_emp(NAME,deptId) values('z5',1);
insert into tbl_emp(NAME,deptId) values('w5',2);
insert into tbl_emp(NAME,deptId) values('w6',2);
insert into tbl_emp(NAME,deptId) values('s7',3);
insert into tbl_emp(NAME,deptId) values('s8',4);
insert into tbl_emp(NAME,deptId) values('s9',51);

//迪卡尔积
select * from tbl_emp,tbl_dept;

//内联 ab共有
select * from tbl_emp a inner join tbl_dept b on a.deptId=b.id;

//左联 全a
select * from tbl_emp a left join tbl_dept b on a.deptId=b.id;

//右联 全b
select * from tbl_emp a right join tbl_dept b on a.deptId=b.id;

//a表独有
select * from tbl_emp a left join tbl_dept b on a.deptId=b.id where
b.id is null;

//b表独有
select * from tbl_emp a right join tbl_dept b on a.deptId=b.id where
a.deptId is null;

//全a+全b //union自带去重
select * from tbl_emp a left join tbl_dept b on a.deptId=b.id
union
select * from tbl_emp a right join tbl_dept b on a.deptId=b.id;

//a表独有+b表独有
select * from tbl_emp a left join tbl_dept b on a.deptId=b.id where
b.id is null
union
select * from tbl_emp a right join tbl_dept b on a.deptId=b.id where
a.deptId is null

一、索引。

创建：
create [unique] index indexName on mytable(columenname(length))
alter mytable add [unique] index [indexName] on (columenname(length))
删除：drop index [indexName] on mytable
查看：show index from table_name

二、索引结构。

BTree索引
Hash索引
full-text全文索引
R-Tree索引

三、需要创建索引。

1.主键自动建立唯一索引
2.频繁作为查询条件的字段应该创建索引
3.查询中与其它表关联的字段，外键关系建立索引
4.频繁更新的字段不适合创建索引：因为每次更新，更新记录，还要更新索引
5.where条件里用不到字段不创建索引
6.高并发下倾向使用组合索引
7.查询中排序的字段，
8.查询中统计或分组字段

四、不要创建索引

1.表记录太少，300W
2.经常增删改的表
3.数据列包含许多重复的内容，如国籍，索引作用不大

五、性能分析explain

能干嘛:表的读取顺序，数据读取操作的操作类型，哪些索引可以使用，哪些索引
被实际使用，表之间的引用，每张表有多少行被优化器查询
explain + sql语句
结果字段解释
id：id相同，执行顺序由上至下
id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
id相同不同，同时存在
select_type值:
simple：简单的select查询，不包含子查询或union
primary:最外层查询
subquery:子查询
derived:衍生，临时表
union:联合
union result:从union表获取结果的select
type:
最好->最差ALL
system>const>eq_ref>ref>range>index>ALL
extra:包含以下需要优化using filesort,using temporary
最好的状态：using index, using where

优化：加索引
单表创建索引时，范围条件不加到索引列中
两表优化，左联时，在右表加索引
右联时，在左表加索引
三表优化，和两表相同

索引失效案例

1.全值匹配我最爱。（创建索引的列，和查询的列全对应）
2.最佳左前缀法则. 如果索引多列，指查询从索引的最左前列开始并且不跳过索
引中的列， nameAgePos name列在则使用了索引
带头大哥不能少，中间兄弟不能断.
3.不在索引列上做任何操作(计算，函数，类型转换)
如：select *from staffs left(name,4)='july'; //left
4.存储引擎不能使用索引中范围条件右边的列.
age>10&&pos='manager' 范围之后索引失效.pos索引失效
5.尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少
select *
6.mysql在使用不等于(!=或<>)的时候无法使用索引会导致全表扫描
如：select name from user where name!='zlk';
7.is null,is not null 也无法使用索引
8.like以通配符开头('%abc..')mysql索引失效会变成全表扫描的操作
create index idx_nameAge on user(name,age)
如：select * from user where name like '%zlk%';
避免失效 select name from user where name like '%zlk%';
9.字符串不加单引号,索引失效
10.少用or,用它来连接时会索引失效

注意：group by如果和索引顺序不同，也产生file排序 filesort
如：where c1='ai' and c4='a4' group by c3;

索引优化一般建议:
对于单键索引，尽量选择针对当前query过滤性更好的索引
在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置
越靠前越好。
在选择组合索引的时候，尽量选择可以能够包含当前query中的where子句中更多
字段的索引。
尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

案例：
假设index(a,b,c)
where语句 索引是否使用
where a=3 Y,使用到a
where a=3 and b=5 Y,使用到a,b
where a=3 and b=5 and c=4 Y,使用到a,b,c
where b=3 或者 b=3 and c=4 或者 where c=4 N
where a=3 and c=5 Y,使用到a,但是c不可以，b中间断了
where a=3 and b>4 and c=5 Y,使用到a,b. c不能用在范围之后，b中间
断了
where a=3 and b like 'kk%' and c=5 Y,使用到a,b,c
where a=3 and b like '%kk' and c=5 Y,使用到a
where a=3 and b like '%kk%' and c=5 Y,使用到a
where a=3 and b like 'k%kk%' and c=5 Y,使用到a,b,c

优化总结口诀
全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
Like百分写最右，覆盖索引不写星；
不等空值还有or，索引失效要少用；
VAR引号不可丢，SQL高级也不难！

方法一、使用慢查询日志

分析
1.观察，至少跑1天，看看生产的慢sql情况
2.开启慢查询日志，设置阙值，比如超过5秒的就是慢sql,并将它抓取出来
3.explain+慢sql分析
4.show profile
5.运维经理or dba，进行sql数据库服务器的参数调优
--总结
1 慢查询的开启并捕获
2 xplain+慢sql分析
3.show profile 查询sql在mysql服务器里面的执行细节和生命周期情况
4.sql数据库服务器的参数调优

小表驱动大表，
in与exists 相互转化
select * from emp e where e.deptid in(select id form dept )
select * from emp e where exists(select 1 form dept d where
d.id=e.deptid)

查看慢查询日志。默认是禁用的
show variables like '%slow_query_log%';
开启
set global slow_query_log=1;
如果要永久生效，必须修改my.cnf文件
mysqld下增加或修改
slow_query_log=1
slow_query_log_file=/var/lib/mysql/atguigu-slow.log //主要-slow.log

慢查询时间阙值 默认10s
show variables like 'long_query_time%';

set global long_query_time=3; //大于3秒的是慢
看效果要重连

select sleep(4) //用于模拟查询用时4秒

show global status like '%slow_queries%' //查看慢sql条数

方法二、使用show profiles;

1,查看支持
show variables like 'profiling'
2，开启功能，默认关闭
set profiling=on
3.运行sql
4.查看结果，show profiles;
show profile cpu,block io for query 2; //2对应show profiles id
5.诊断sql,

6.日常开发注意的结论
convertion heap to myisam 查询结果太大，
creating tmp table 创建临时表
copying to tmp table on disk 把内存中临时表
locked 锁表

方法三、全局查询日志

set global general_log=1;
set global log_output='TABLE';
此后，所有的sql语句，将会记录到mysql库的general_log表，可以使用命令查看
select * from mysql.general_log;

清空表：

TRUNCATE TABLE cmf_expert_realtime_info;

参数视频：尚硅谷MySQL数据库高级，mysql优化，数据库优化_哔哩哔哩_bilibili