mysql基础部分
中间补充
mysql获取系统时间
1.获取单个年月日
2.获取当前时间,格式为YYYY-MM-DD HH:mm:ss
3.获取当前年月日
4.获取时分秒
5、获取当前时间的秒数
格林尼治时间为:1970-01-01 00:00:00,此时位为东八区的北京时间为:1970-01-01 08:00:00
6.获取指定时间的秒数
7.获取指定秒数的时间
8.获取两时间之间制订单位的时长
等值连接与自然连接
外连接:
Mysql高级
1.概念:MySQL官方对索引的定义为:索引(Index)是帮助MySQL高校获取数据的数据结构。
可以得到索引的本质:索引是数据结构 底层是B+树
2.优缺点:
3.索引分类
1.主键索引(聚集)
2.普通(单列索引、单值索引)索引
3.唯一索引
4.复合索引
聚集(clustered)索引与非聚集索引
聚集索引
注:第一列的地址表示该行数据在磁盘中的物理地址,后面三列才是我们SQL里面用的表里的列,其中id是主键,建立了聚集索引。
ps:基本语法
非聚集索引
非聚集索引的二次查询问题
非聚集索引叶节点仍然是索引节点,只是有一个指针指向对应的数据块,此如果使用非聚集索引查询,而查询列中包含了其他该索引没有覆盖的列,那么他还要进行第二次的查询,查询节点上对应的数据行的数据。
如有以下表t1:
以及聚集索引clustered index(id), 非聚集索引index(username)。
使用以下语句进行查询,不需要进行二次查询,直接就可以从非聚集索引的节点里面就可以获取到查询列的数据。
但是使用以下语句进行查询,就需要二次的查询去获取原数据行的score:
如何解决非聚集索引的二次查询问题
复合索引(覆盖索引)
建立两列以上的索引,即可查询复合索引里的列的数据而不需要进行回表二次查询,如index(col1, col2),执行下面的语句:
要注意使用复合索引需要满足最左侧索引的原则,也就是查询的时候如果where条件里面没有最左边的一到多列,索引就不会起作用。
总结与使用心得
4.最左原则(针对复合索引)
1.简单说下什么是最左匹配原则
2.最左匹配原则的原理
5.索引使用场景
适合建索引:
并不需要索引:
6.索引优化
EXPLAIN关键字
可以模拟优化器执行SQL语句,从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是结构的性能瓶颈
语法:EXPLAIN+sql语句
字段包含信息
id:
select_type:
分类
table:
显示这一行的数据是关于哪张表的
type:
分类
possible_key:
key:
key_len:
ref:
rows:
Extra:
索引分析
单表 多表 连接查询 要把索引建立在相反的一边 比如 left join 就把索引建立在右表中
索引失效
上面倒数第四个是以常量开头,索引用到了b索引,但是type 是range
上面倒数第三、第二以%开头 无法确定 索引直接没被使用 只使用了a索引 所以type显示ref
注意:
建议:
7.查询优化
小表驱动大表
Order by 分析:
8.慢查询日志
9.show profiles
mysql锁
1.表锁
特点:
读锁:
写锁:
结论:
表锁分析:
2.行锁
更新丢失:
脏读:
不可重复读:
幻读:
事务隔离级别:
演示:
无索引行锁升级为表锁:varchar 不用 ' ' 导致系统自动转换类型, 行锁变表锁
间隙锁:
常见面试题:如何锁定一行:
结论:
行锁分析:
优化建议
3.页锁