Mysql高级 学习笔记分享
索引是一种数据结构 ---排好序的快速查找数据结构 、 某种满足特定查找算法的数据结构、以某种方式指向数据
两大功能：查找快、排好序
目的在于提高查询效率，类比字典
如果没有索引，那得从a找到z
mysql用order by来排序
比如图书馆有一百本书，你通过图书管理员来找，因为他记得每一本书的位置，那么图书管理员就是索引
可以叫这种索引叫 BTree B树索引
为什么逻辑删除数据，而不是物理删除？ 第一是为了保留数据，第二是为了保护索引
增删太多可能导致索引失效
索引的优势和劣势：
优势：类似大学图书馆建书目索引，提高数据检索效率，降低数据库IO成本、通过索引列队数据进行排序，降低数据排序成本，降低CPU消耗
劣势：索引其实也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列是需要占用空间的。因为更新表的时候，mysql不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
索引是需要不停地优化和调整的，根据用户的搜索习惯来修改优化
索引分类：
单值索引：一个索引只包含单个列，一个表可以有多个单例索引 意思就是用一个条件来找
唯一索引：索引列的值必须唯一，但允许为空值
复合索引：一个索引包括多个列
基本语法：创建、删除、查看
mysql索引结构
Btree索引 尽量让广度大 而不是让高度更高
Hash索引
full-text全文索引
R-tree索引
什么情况建立索引？
频繁查找的应该创建索引，外键关系建立索引
查询中 统计 或分组的字段要建立索引
什么情况不要建立？
频繁更换的字段不适合建立索引
where条件中用不到的字段不创建索引
高并发情况下倾向建立组合索引 而不是单键索引
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
经常增删改不建立
大量重复不建立
性能分析：
Mysql的常见瓶颈：CPU、 IO、 服务器性能瓶颈
expain + 自己写的sql语句
能干嘛： 表的读取顺序、数据读取操作的操作类型、那些索引可以使用、哪些索引被实际使用、表之间的引用、每张表有多少行被优化器查询
id:select查询的序列号，包含一组数字，表示查询中
id 相同的情况下，执行顺序从上到下
id不同：id值越大优先级越高，越先被执行
第三种情况id相同和id不同的情况同时存在 比如 1 1 2
首先 数字大的先走
在所有组中，id值越大，优先级越高，越先执行
其次id如果相同，从上往下按顺序执行
Derived代表衍生
select_type 查询类型 告诉我们是Simple简单查询还是 primary（只要包含子查询外层查询就会被标记为primary）
还是subquery（在select活where列表中包含了子查询）
Derived(表示是 临时表 衍生表)
UNION
UNION result
table显示这一行的数据是关于哪张表的
type
最好到最差依次是 system>const>eq_ref>range>index>ALL
All代表是全表扫描 只要是百万以上的 不要用ALL
一般来说得保证查询至少达到range级别，最好达到ref
eq_ref :唯一性索引扫描 ->公司只有一个ceo
ref 返回匹配某个单独值的所有行
range:只检索给定范围的行
index ： 全索引扫描 遍历索引树
ALL：全表扫描
possible_keys 显示可能应用在这张表中的索引（但不一定被查询实际使用）
key 实际上应用到在这张表中的索引 key才是我们应该关心的真正用到的索引
key_len 表示索引中使用的字节数，可通过该列计算查询中使用的索引长度，不损失精确性的情况下，索引越短越好
ref：显示索引的哪一列被使用了 库.表.字段.常量
rows:每张表有多少行被sql查询 rows用得越少越好
Extra: 十分重要的额外信息
{
Using filesort 对数据使用一个外部的索引排序，而不是按照表内的索引顺序排序，而产生 文件排序 九死一生
Using temporary 使用了临时表保存中间结果，常见于排序order by 和分组查询group by 十死无生 效率更低
Using index 索引被用来执行索引键值的查询 使用了索引来查询 效率高
Using where 使用了 where
其他的没这么重要
}
索引优化：
单表情况
两表情况：
如果两张表有外键，索引加在哪个表的字段上？
比如 left join on class.card = book.card
记住 左连接建立在右表上 右连接建立在左表上
三表的情况：如果是class left join on class.card = book.card left join phone on book.card =phone,card
那么索引应该建立在 book.card 和 phone.card上面
结论：
1、永远用小结果集 驱动大结果集 比如 用class来连接book 因为class大概是几十种，但是书可能有几千本几万本
2、优先优化内层循环
3、保证join语句中被驱动表上join条件字段已经被索引
当无法保证被驱动表的join字段被索引且内存资源充足的前提下，不要太吝啬joinBuffer的设置
如何避免索引失效
全值匹配我最爱
最佳左前缀法则：查询从索引的最左前列开始并且不跳过索引中的列（where条件的使用，where按索引次序来）
比如 有三个索引 name age pos
我可以用 name 因为他是火车头 但是直接用age 和pos 就等于直接用车厢跑
不在索引列上做任何操作 比如 计算，函数，自动或者手动类型转换，会导致索引失效而转向全表扫描
不能使用索引中范围条件右边的列
也就是说 比如 有 where 那么 = july and age >25 and pos = 'manage' 那么 age这个范围条件后面的pos=.... 会失效
尽量使用覆盖索引尽量减少使用select* 尽量 取什么 用什么
在使用不等于的时候无法使用索引 会导致全表扫描
is null 或者 is not null 也无法使用索引 导致全表扫描
like以通配符开头的sql 索引会失效变成全表扫描
解决like %字符串%（写在左边或者两边，会失效）时索引不能使用的方法？
使用复合索引 建的索引最好在查的时候跟该复合索引重合 一致
字符串 不写单引号会导致索引失效
少用or 用它来连接的时候也会导致索引失效
总结：
索引面试题分析：
定值、范围还是排序，一般order by是给个范围 也就是按照某个字段去排序
group by 基本上都需要进行排序，会有临时表产生 分组之前必排序
索引是查找和 排序
一般性建议
对于单键索引，尽量选择针对当前query过滤性更好的索引
在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好
在选择组合索引时，尽量选择可以包含当前query中的where子句中更多字段的索引
尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的
查询截取分析：1、观察至少一天，看看生产的慢sql情况 2、开启慢查询日志，设置阈值，比如超过5秒钟就是慢sql，并将其抓取出来3、explain+慢sql分析 4、show profile 5、运维经理或DBA进行sql数据库服务器参数调优
查询优化：
小表驱动大表，小的数据集驱动大的数据集
关于exist与in 假设有A in B 当B表数据集小于A表时， in 优于exist
而当B表数据大于A表时 用exist优于in
左大右小in 左小右大exist
exist：将主查询的数据，放到子查询中做条件验证，将验证结果用true或FALSE返回
如果用排序的时候 查找顺序跟索引一直 就不会产生filesort
ORder by 满足两种情况，会使用Index方式排序:Order BY语句使用索引最左前缀 2 使用where子句与Order By子句条件组合满足索引最左前列
如果不在索引列 那么filesort有两种算法 mysql就要启动双路排序和单路排序了
提高order by 的速度
1、不要用select *
慢查询 开启 设置 抓取
是Mysql提供的一种日志记录，他用来记录在Mysql中响应时间超过阈值的语句
默认不会开启慢查询日志 需要自己去开启
命令 省略..
慢SQL的属性信息，比如可以用命令返回得到记录集最多的10个sql，可以得到访问次数最多的10的sql 等等
插入1000w条数据
创建函数，保证每条数据都不同：随机产生字符串，随机产生部门编号
创建存储过程，创建往emp表中插入数据的存储过程
创建往dept表中插入数据的存储过程
调用存储过程
性能分析 show profile 用来分析当前会话中语句执行的资源消耗情况
默认是关闭 使用需要自己开启
开启后 使用show profile操作后 可以看到自己执行的sql的性能详情
诊断sql，show profile cpu,block,io for query
一般比较影响性能的结论 这四条都非常影响性能 convertint HEAP to MYISAM 查询结果太大，内存都不够用了往磁盘上搬了
Creating tmp table 创建历史表 拷贝数据到临时表 用完再删除
copying to tmp table on disk 把内存中临时表复制到磁盘
locked
全局查询日志： （只允许在测试环境用，不要在生产环境用）
set global general_log =1 开启后会将所有执行过的查询记录到 general_log表
可以用select * from mysql.general_log 命令查看
Mysql锁机制
锁是计算机控制线程资源的一种机制
比如 一人一单 就需要锁
锁的分类 ： 读锁与写锁 读锁就是共享锁 写锁就是排它锁
加锁 lock 解锁 unlock
加读锁
加了读锁后 能读自己加锁的表 不能写该表 也不能读未加锁的表
如果加了读锁 其他的session不能访问 处于阻塞状态 只有等我解锁 才能进行写操作
加写锁
自己加了写锁以后，可以读，可以写自己的表，但是不能读其他的表
其他会话可以查未加锁的表 但是查询加锁的表会被阻塞 写也会被阻塞
读锁会阻塞写，但是不会阻塞读，而写锁则会把读和写都阻塞
Myisam的读写调度是写优先，，不适合做写为主表的引擎，写锁后，其他线程不能做任何操作，大量的更新会使查询很难的到锁，从而造成永远阻塞
行锁 偏向innodb引擎，
行锁支持事务
他并不是锁整张表，而只是锁住session1正在操作的行1 session2此时是不能操作1的 会阻塞，只有当session1提交后 session2 才能操作
但是其他没有被锁的行 2 3 4 session2可以操作
索引失效行锁升级为表锁
间隙锁的危害
间隙锁：我修改1-6的数据 未提交时 2不能够插入 这就是间隙锁
当我们用范围条件而不是相等条件检索数据，并请求共享锁或者排他锁时，innoDb会给符合条件的已有数据条件的索引项加锁，对于键值在条件范围内并不存在的记录，叫做“间隙”
show status like'innodb_row_lock%'
可以查看锁的状态 比如 等待平均时长，等待总次数，等待总时长
优化建议：
尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
合理设计索引，尽量缩小锁的范围
尽可能减少检索条件，避免间隙锁
尽量控制事务大小，减少锁定资源量和时间长度
尽可能使用低隔离级别
页锁 了解即可
Mysql复制过程分为三步：
1.master将改变记录到二进制日志(binary log) 这些记录过程叫做二进制日志时间，binary log events；
2slave 将master的 binary log event 拷贝到它的中继日志(relay log)；
3.slave 重做中继日志中的时间，将改变应用到自己的数据库中，Mysql复制是异步串行化的。
每个slave只有一个master
每个slave只能有一个唯一的服务器ID
每个master可以有多个salve
一主一从常见配置：
版本一致， 双边网络要ping通