尧图网页设计 
1.cmd登陆mysqlD:\phpstudy_pro\Extensions\MySQL5.7.26\binmysql -uroot -p2.查看数据库引擎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 distinctselect_listfrom left_tablejoin_typejoin right_tableon join_conditionwherewhere_conditiongroup bygroup_by_listhavinghaving_conditionorder byorder_by_conditionlimit limit_number机读顺序:from left_tableon join_conditionjoin_typejoin right_tablewhere where_conditiongroup by group_by_listhaving having_conditionselectdistinct select_listorder by order_by_conditionlimit limit_number7种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.deptIdb.id;//左联 全aselect * from tbl_emp a left join tbl_dept b on a.deptIdb.id;//右联 全bselect * from tbl_emp a right join tbl_dept b on a.deptIdb.id;//a表独有select * from tbl_emp a left join tbl_dept b on a.deptIdb.id whereb.id is null;//b表独有select * from tbl_emp a right join tbl_dept b on a.deptIdb.id wherea.deptId is null;//全a全b //union自带去重select * from tbl_emp a left join tbl_dept b on a.deptIdb.idunionselect * from tbl_emp a right join tbl_dept b on a.deptIdb.id;//a表独有b表独有select * from tbl_emp a left join tbl_dept b on a.deptIdb.id whereb.id is nullunionselect * from tbl_emp a right join tbl_dept b on a.deptIdb.id wherea.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.表记录太少300W2.经常增删改的表3.数据列包含许多重复的内容如国籍索引作用不大五、性能分析explain能干嘛:表的读取顺序数据读取操作的操作类型哪些索引可以使用哪些索引被实际使用表之间的引用每张表有多少行被优化器查询explain sql语句结果字段解释idid相同执行顺序由上至下id不同如果是子查询id的序号会递增id值越大优先级越高越先被执行id相同不同同时存在select_type值:simple简单的select查询不包含子查询或unionprimary:最外层查询subquery:子查询derived:衍生临时表union:联合union result:从union表获取结果的selecttype:最好-最差ALLsystemconsteq_refrefrangeindexALLextra:包含以下需要优化using filesort,using temporary最好的状态using index, using where优化加索引单表创建索引时范围条件不加到索引列中两表优化左联时在右表加索引右联时在左表加索引三表优化和两表相同索引失效案例1.全值匹配我最爱。创建索引的列和查询的列全对应2.最佳左前缀法则. 如果索引多列指查询从索引的最左前列开始并且不跳过索引中的列 nameAgePos name列在则使用了索引带头大哥不能少中间兄弟不能断.3.不在索引列上做任何操作(计算函数类型转换)如select *from staffs left(name,4)july; //left4.存储引擎不能使用索引中范围条件右边的列.age10posmanager 范围之后索引失效.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 c1ai and c4a4 group by c3;索引优化一般建议:对于单键索引尽量选择针对当前query过滤性更好的索引在选择组合索引的时候当前query中过滤性最好的字段在索引字段顺序中位置越靠前越好。在选择组合索引的时候尽量选择可以能够包含当前query中的where子句中更多字段的索引。尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的案例假设index(a,b,c)where语句 索引是否使用where a3 Y,使用到awhere a3 and b5 Y,使用到a,bwhere a3 and b5 and c4 Y,使用到a,b,cwhere b3 或者 b3 and c4 或者 where c4 Nwhere a3 and c5 Y,使用到a,但是c不可以b中间断了where a3 and b4 and c5 Y,使用到a,b. c不能用在范围之后b中间断了where a3 and b like kk% and c5 Y,使用到a,b,cwhere a3 and b like %kk and c5 Y,使用到awhere a3 and b like %kk% and c5 Y,使用到awhere a3 and b like k%kk% and c5 Y,使用到a,b,c优化总结口诀全值匹配我最爱最左前缀要遵守带头大哥不能死中间兄弟不能断索引列上少计算范围之后全失效Like百分写最右覆盖索引不写星不等空值还有or索引失效要少用VAR引号不可丢SQL高级也不难方法一、使用慢查询日志分析1.观察至少跑1天看看生产的慢sql情况2.开启慢查询日志设置阙值比如超过5秒的就是慢sql,并将它抓取出来3.explain慢sql分析4.show profile5.运维经理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 whered.ide.deptid)查看慢查询日志。默认是禁用的show variables like %slow_query_log%;开启set global slow_query_log1;如果要永久生效必须修改my.cnf文件mysqld下增加或修改slow_query_log1slow_query_log_file/var/lib/mysql/atguigu-slow.log //主要-slow.log慢查询时间阙值 默认10sshow variables like long_query_time%;set global long_query_time3; //大于3秒的是慢看效果要重连select sleep(4) //用于模拟查询用时4秒show global status like %slow_queries% //查看慢sql条数方法二、使用show profiles;1,查看支持show variables like profiling2开启功能默认关闭set profilingon3.运行sql4.查看结果show profiles;show profile cpu,block io for query 2; //2对应show profiles id5.诊断sql,6.日常开发注意的结论convertion heap to myisam 查询结果太大creating tmp table 创建临时表copying to tmp table on disk 把内存中临时表locked 锁表方法三、全局查询日志set global general_log1;set global log_outputTABLE;此后所有的sql语句将会记录到mysql库的general_log表可以使用命令查看select * from mysql.general_log;清空表TRUNCATE TABLE cmf_expert_realtime_info;参数视频尚硅谷MySQL数据库高级mysql优化数据库优化_哔哩哔哩_bilibili 学习路之mysql--mysql优化,数据库优化
1.cmd登陆mysqlD:\phpstudy_pro\Extensions\MySQL5.7.26\binmysql -uroot -p2.查看数据库引擎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 distinctselect_listfrom left_tablejoin_typejoin right_tableon join_conditionwherewhere_conditiongroup bygroup_by_listhavinghaving_conditionorder byorder_by_conditionlimit limit_number机读顺序:from left_tableon join_conditionjoin_typejoin right_tablewhere where_conditiongroup by group_by_listhaving having_conditionselectdistinct select_listorder by order_by_conditionlimit limit_number7种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.deptIdb.id;//左联 全aselect * from tbl_emp a left join tbl_dept b on a.deptIdb.id;//右联 全bselect * from tbl_emp a right join tbl_dept b on a.deptIdb.id;//a表独有select * from tbl_emp a left join tbl_dept b on a.deptIdb.id whereb.id is null;//b表独有select * from tbl_emp a right join tbl_dept b on a.deptIdb.id wherea.deptId is null;//全a全b //union自带去重select * from tbl_emp a left join tbl_dept b on a.deptIdb.idunionselect * from tbl_emp a right join tbl_dept b on a.deptIdb.id;//a表独有b表独有select * from tbl_emp a left join tbl_dept b on a.deptIdb.id whereb.id is nullunionselect * from tbl_emp a right join tbl_dept b on a.deptIdb.id wherea.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.表记录太少300W2.经常增删改的表3.数据列包含许多重复的内容如国籍索引作用不大五、性能分析explain能干嘛:表的读取顺序数据读取操作的操作类型哪些索引可以使用哪些索引被实际使用表之间的引用每张表有多少行被优化器查询explain sql语句结果字段解释idid相同执行顺序由上至下id不同如果是子查询id的序号会递增id值越大优先级越高越先被执行id相同不同同时存在select_type值:simple简单的select查询不包含子查询或unionprimary:最外层查询subquery:子查询derived:衍生临时表union:联合union result:从union表获取结果的selecttype:最好-最差ALLsystemconsteq_refrefrangeindexALLextra:包含以下需要优化using filesort,using temporary最好的状态using index, using where优化加索引单表创建索引时范围条件不加到索引列中两表优化左联时在右表加索引右联时在左表加索引三表优化和两表相同索引失效案例1.全值匹配我最爱。创建索引的列和查询的列全对应2.最佳左前缀法则. 如果索引多列指查询从索引的最左前列开始并且不跳过索引中的列 nameAgePos name列在则使用了索引带头大哥不能少中间兄弟不能断.3.不在索引列上做任何操作(计算函数类型转换)如select *from staffs left(name,4)july; //left4.存储引擎不能使用索引中范围条件右边的列.age10posmanager 范围之后索引失效.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 c1ai and c4a4 group by c3;索引优化一般建议:对于单键索引尽量选择针对当前query过滤性更好的索引在选择组合索引的时候当前query中过滤性最好的字段在索引字段顺序中位置越靠前越好。在选择组合索引的时候尽量选择可以能够包含当前query中的where子句中更多字段的索引。尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的案例假设index(a,b,c)where语句 索引是否使用where a3 Y,使用到awhere a3 and b5 Y,使用到a,bwhere a3 and b5 and c4 Y,使用到a,b,cwhere b3 或者 b3 and c4 或者 where c4 Nwhere a3 and c5 Y,使用到a,但是c不可以b中间断了where a3 and b4 and c5 Y,使用到a,b. c不能用在范围之后b中间断了where a3 and b like kk% and c5 Y,使用到a,b,cwhere a3 and b like %kk and c5 Y,使用到awhere a3 and b like %kk% and c5 Y,使用到awhere a3 and b like k%kk% and c5 Y,使用到a,b,c优化总结口诀全值匹配我最爱最左前缀要遵守带头大哥不能死中间兄弟不能断索引列上少计算范围之后全失效Like百分写最右覆盖索引不写星不等空值还有or索引失效要少用VAR引号不可丢SQL高级也不难方法一、使用慢查询日志分析1.观察至少跑1天看看生产的慢sql情况2.开启慢查询日志设置阙值比如超过5秒的就是慢sql,并将它抓取出来3.explain慢sql分析4.show profile5.运维经理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 whered.ide.deptid)查看慢查询日志。默认是禁用的show variables like %slow_query_log%;开启set global slow_query_log1;如果要永久生效必须修改my.cnf文件mysqld下增加或修改slow_query_log1slow_query_log_file/var/lib/mysql/atguigu-slow.log //主要-slow.log慢查询时间阙值 默认10sshow variables like long_query_time%;set global long_query_time3; //大于3秒的是慢看效果要重连select sleep(4) //用于模拟查询用时4秒show global status like %slow_queries% //查看慢sql条数方法二、使用show profiles;1,查看支持show variables like profiling2开启功能默认关闭set profilingon3.运行sql4.查看结果show profiles;show profile cpu,block io for query 2; //2对应show profiles id5.诊断sql,6.日常开发注意的结论convertion heap to myisam 查询结果太大creating tmp table 创建临时表copying to tmp table on disk 把内存中临时表locked 锁表方法三、全局查询日志set global general_log1;set global log_outputTABLE;此后所有的sql语句将会记录到mysql库的general_log表可以使用命令查看select * from mysql.general_log;清空表TRUNCATE TABLE cmf_expert_realtime_info;参数视频尚硅谷MySQL数据库高级mysql优化数据库优化_哔哩哔哩_bilibili