mysql数据库进阶
sql变慢查询步骤
1.开启慢查询日志,跑遍所有的sql语句
2.进入慢查询日志,提取慢sql语句
3.explain分析慢sql语句
4.show profile
5.查看全局日志
代码处理
记得导入驱动,前面加下划线
1.mysql的存储引擎有两种:inodb(三个文件存储表格 .frm (表结构).data(表数据).myi(表索引) )和myisam (.frm表结构 .idb表数据+表索引)
索引命令:
查看索引:show index from 表名
创建索引:
创建索引方法一:
mysql> create index ix_stuname on stuinfo(stuname);
创建索引方法二:
mysql> alter table stuinfo add index ix_address (stuaddress);
创建表时添加索引:
create table emp(
id int,
name varchar(10),
index ix_name (name) # 创建索引
);
删除索引:
语法:drop index 索引名 on 表名
mysql> drop index ix_stuname on stuinfo;
mysql索引优化口诀
全职匹配我最爱,最左前缀要遵守
带头大哥不能死,中间兄弟不能断
索引列上少计算,范围之后全失效
like百分号写最右,覆盖索引不写*
不等空值还有or,索引失效要少用
var引号不可丢,sql高级也不难
mysql优化步骤
1.慢查询开启并捕获 (观察一天查看生产慢sql的情况,开启慢查询日志)
2.explain+慢查询分析
3.show profile查询sql在服务器里的执行细节和生命周期
4.sql数据库服务的参数调优
优化实例
小表驱动大表
注意A表的字段和B表的字段应该建立索引
1.当B表的数据集小于A表的时候用IN由于exit
select * from A where id in(select id from B)
等价于: for select id from B
for select * from A where A.id=B.id
2.当A表的数据集小于B表时,用exist优于in (将主查询中的结果放到子查询中做验证,以验证查询结果是否保留,所以会先执行主查询)
(exists 只返回TRUE或者FALSE),因此子查询中的SELECT*也可以是select1或者其他,官方说法实际中执行会忽略select清单,所以没区别
select * from A where exits(select * from B where A.id=B.id)
等价于: for select * from A
for select * from B where A.id=B.id
order by 和group by(实质上是先排序后分组)
为排序使用索引:mysql两种排序方式,文件排序(不建议)或扫描有序索引排序
mysql能为查询和排序使用相同的索引 当无法使用索引列,增大max_length_for_sort_data 参数的设置+增大 sort
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 (mysql中的order by是默认增序的,想在order by中使用索引要么得全增序,要么全降序)
如果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,