#1、and与or的逻辑
条件1 and 条件2:所有条件都成立才算成立,但凡要有一个条件不成立则最终结果不成立
条件1 or 条件2:只要有一个条件成立则最终结果就成立
#2、and的工作原理
条件:
a = 10 and b = 'xxx' and c > 3 and d =4
索引:
制作联合索引(d,a,b,c)
工作原理:
对于连续多个and:mysql会按照联合索引,从左到右的顺序找一个区分度高的索引字段(这样便可以快速锁定很小的范围),加速查询,即按照d—>a->b->c的顺序
#3、or的工作原理
条件:
a = 10 or b = 'xxx' or c > 3 or d =4
索引:
制作联合索引(d,a,b,c)
工作原理:
对于连续多个or:mysql会按照条件的顺序,从左到右依次判断,即a->b->c->d
经过分析,在条件为name='egon' and gender='male' and id>333 and email='xxx'的情况下,我们完全没必要为前三个条件的字段加索引,因为只能用上email字段的索引,前三个字段的索引反而会降低我们的查询效率
6 最左前缀匹配原则(详见第八小节),非常重要的原则,对于组合索引mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配(指的是范围大了,有索引速度也慢),比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。
7 其他情况
- 使用函数
select * from tb1 where reverse(email) = 'egon';
- 类型不一致
如果列是字符串类型,传入条件是必须用引号引起来,不然...
select * from tb1 where email = 999;
#排序条件为索引,则select字段必须也是索引字段,否则无法命中
- order by
select name from s1 order by email desc;
当根据索引排序时候,select查询的字段如果不是索引,则速度仍然很慢
select email from s1 order by email desc;
特别的:如果对主键排序,则还是速度很快:
select * from tb1 order by nid desc;
- 组合索引最左前缀
如果组合索引为:(name,email)
name and email -- 命中索引
name -- 命中索引
email -- 未命中索引
- count(1)或count(列)代替count(*)在mysql中没有差别了
- create index xxxx on tb(title(19)) #text类型,必须制定长度
执行计划:让mysql预估执行操作(一般正确)
all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
id,email
慢:
select * from userinfo3 where name='alex'
explain select * from userinfo3 where name='alex'
type: ALL(全表扫描)
select * from userinfo3 limit 1;
快:
select * from userinfo3 where email='alex'
type: const(走索引)
十 慢查询优化的基本步骤
0.先运行看看是否真的很慢,注意设置SQL_NO_CACHE
1.where条件单表查,锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起,单表每个字段分别查询,看哪个字段的区分度最高
2.explain查看执行计划,是否与1预期一致(从锁定记录较少的表开始查询)
3.order by limit 形式的sql语句让排序的表优先查
4.了解业务方使用场景
5.加索引时参照建索引的几大原则
6.观察结果,不符合预期继续从0分析
十一 慢日志管理
慢日志
- 执行时间 > 10
- 未命中索引
- 日志文件路径
配置:
- 内存
show variables like '%query%';
show variables like '%queries%';
set global 变量名 = 值
- 配置文件
mysqld --defaults-file='E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\my-default.ini'
my.conf内容:
slow_query_log = ON
slow_query_log_file = D:/....
注意:修改配置文件之后,需要重启服务
日志管理
MySQL日志管理
========================================================
错误日志: 记录 MySQL 服务器启动、关闭及运行错误等信息
二进制日志: 又称binlog日志,以二进制文件的方式记录数据库中除 SELECT 以外的操作
查询日志: 记录查询的信息
慢查询日志: 记录执行时间超过指定时间的操作
中继日志: 备库将主库的二进制日志复制到自己的中继日志中,从而在本地进行重放
通用日志: 审计哪个账号、在哪个时段、做了哪些事件
事务日志或称redo日志: 记录Innodb事务相关的如事务执行时间、检查点等
========================================================
一、bin-log
1. 启用
# vim /etc/my.cnf
[mysqld]
log-bin[=dir\[filename]]
# service mysqld restart
2. 暂停
//仅当前会话
SET SQL_LOG_BIN=0;
SET SQL_LOG_BIN=1;
3. 查看
查看全部:
# mysqlbinlog mysql.000002
按时间:
# mysqlbinlog mysql.000002 --start-datetime="2012-12-05 10:02:56"
# mysqlbinlog mysql.000002 --stop-datetime="2012-12-05 11:02:54"
# mysqlbinlog mysql.000002 --start-datetime="2012-12-05 10:02:56" --stop-datetime="2012-12-05 11:02:54"
按字节数:
# mysqlbinlog mysql.000002 --start-position=260
# mysqlbinlog mysql.000002 --stop-position=260
# mysqlbinlog mysql.000002 --start-position=260 --stop-position=930
4. 截断bin-log(产生新的bin-log文件)
a. 重启mysql服务器
b. # mysql -uroot -p123 -e 'flush logs'
5. 删除bin-log文件
# mysql -uroot -p123 -e 'reset master'
二、查询日志
启用通用查询日志
# vim /etc/my.cnf
[mysqld]
log[=dir\[filename]]
# service mysqld restart
三、慢查询日志
启用慢查询日志
# vim /etc/my.cnf
[mysqld]
log-slow-queries[=dir\[filename]]
long_query_time=n
# service mysqld restart
MySQL 5.6:
slow-query-log=1
slow-query-log-file=slow.log
long_query_time=3
查看慢查询日志
测试:BENCHMARK(count,expr)
SELECT BENCHMARK(50000000,2*3);