一、SQL相关
MyISAM和InnoDB
SQL性能下降的原因
SQL执行加载顺序
手写:
机读:
7种JOIN
二、索引
1、索引是什么
可以得到索引的本质:索引是数据结构。
你可以简单理解为 “排好序的快速查找数据结构” 。
结论:
数据本身之外,数据库还维护着一个满足特定查找算法的数据结构,这些数据结构以某种方式指向数据,这样就可以在这些数据结构的基础上实现高级查找算法,这种数据结构就是索引。
2、索引优势
3、索引劣势
4、索引的分类及基本语法
5、检索原理
【初始化介绍】
一颗b+树,浅蓝色的块我们称之为一个磁盘块,可以看到每个磁盘块包含几个数据项(深蓝色所示)和指针(黄色所示),如磁盘块1包含数据项17和35,包含指针P1、P2、P3, P1表示小于17的磁盘块,P2表示在17和35之间的磁盘块,P3表示大于35的磁盘块。 真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。 非叶子节点只不存储真实的数据,只存储指引搜索方向的数据项,如17、35并不真实存在于数据表中。
【查找过程】
如果要查找数据项29,那么首先会把磁盘块1由磁盘加载到内存,此时发生一次IO,在内存中用二分查找确定29在17和35之间,锁定磁 盘块1的P2指针,内存时间因为非常短(相比磁盘的IO)可以忽略不计,通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存 ,发生第二次IO,29在26和30之间,锁定磁盘块3的P2指针,通过指针加载磁盘块8到内存,发生第三次IO,同时内存中做二分查找找到29.结市香询。点计三次16~~, 真实的情况是,3层的b+树可以表示上百万的数据,如果上百万的数据查找只需要三次IO,性能提高将是巨大的,如果没有索引,每个 数据项都要发生一次IO,那么总共需要百万次的lO,显然成本非常非常高
6、需要建立索引的情况
7、不需要建立索引的情况
8、Explain关键字介绍
9、Explain之id(表的读取顺序)
10、Explain之select_type(数据读取操作的操作类型)、table
table
11、Explain之type
12、Explain之possible_keys、key
13、Explain之key_len
14、Explain之 ref 介绍
15、Explain之rows介绍
16、Explain之Extra介绍
17、Explain之热身Case
三、索引优化
1、索引单表优化案例
建表
2、索引两表优化案例
左连接加右索引,右连接加左索引
建表:
3、索引三表优化案例
建表:
左连接,在右边两个表加索引:
4、索引优化
口诀:
- 1.带头大哥不能死
- 2.中间兄弟不能断,永远符合最左前缀原则
- 3.索引列上不计算
- 4.范围之后全失效
- 5.百分 like 加右边
- 6.两边都百分号时 可建 覆盖索引 解决索引失效
- 7.字符串引号不能缺
建表:
两边都百分号时 可建 覆盖索引 解决索引失效
小总结:
5、例题分析及建议
6、小结
口诀:
一般性建议:
四、其他优化
1、小表驱动大表
2、order by 关键字优化
小总结:
3、group by 关键字优化
4、慢查询日志
查看与临时开启
永久开启:
阈值:
设置慢查询的值:
查找慢SQL语句:
开启慢查询日志后,执行sql后,即可在 /var/lib/mysql 找到日志:
查看即可发现慢的SQL:
查看当前系统有多少条慢查询记录:
若要配置永久生效,则如下:
使用【mysqldumpslow --help】查看帮助信息:
帮助信息:
常用查询:
五、批量插入及showfile
1、批量插入数据脚本
【1 建表】
【2 设置参数】
【3 创建函数,保证每条数据都不同】
随机产生字符串
【4 创建存储过程】
【5 调用存储过程】
调用部门表的存储过程,插入从 100 开始 的 10条数据:
执行员工表的存储过程,添加50万条数据:
2、Show Profile
【第一步 开启功能】
【第二步 执行SQL】
【第三步 show profile】
【第四步 查看分析】
六、表锁行锁
1、读锁案例分析
2、写锁案例分析
3、案例总结
表锁分析
4、行锁定案例分析
5、间隙锁
6、如何锁定一行
7、行锁总结
8、行锁分析
优化建议:
9、页锁
七、MySQL主从复制
1、复制的基本原理
三步骤+原理图: