学习笔记分享：https://note.youdao.com/s/GSIKHiBD
1.mysql架构
linux安装
配置文件
MyISAM 和 InnoDB 的区别
2.索引优化分析
性能下降sql慢：
常见的join查询：
sql执行顺序：
七种join：
索引
优势：
劣势：
分类：
Btree索引原理
哪些情况适合建索引：
不适合创建：
执行计划 explain
id：表读取顺序
select_type：数据读取的操作类型
type：访问类型排序
possiblekey：理论上用到的索引
key：实际使用到的索引
key_len：索引中使用的字节数
ref：显示索引的那一列被使用了
rows：估算出找到所需的记录所需要读取的行数
extra：包含不适合在其他列显示但十分重要的信息
3.索引
分析
索引失效
建议：
查询截取分析
①小表驱动大表
② order by 关键字优化
③ group by优化
4.慢查询日志
循环：
批量插入数据
用show profile进行sql分析
全局查询日志
4.mysql锁机制
表锁
读锁：
写锁：
特点
行锁
无索引，行锁自动升级为表锁
间隙锁：update范围的，也会有锁
行锁分析
5.主从复制
1.mysql架构
linux安装
检查是否安装过：rpm -qa|grep -i mysql
安装服务端和客户端：rpm -ivh MySQL-xxx.rpm
查看是否安装成功：mysqladmin --version
启动：service mysql start
停止：service mysql stop
top 查看linux机器信息
设置mysql连接密码 /usr/bin/mysqladmin -u root password 123456
设置开机自启动：checkconfig mysql on
查看开机启动的服务：ntsysv
配置文件
二进制文件log-bin：主从复制
错误日志 log-error
查询日志log
数据文件：frm存放表结构，myd存放表数据，myi存放表索引
插件式的存储引擎架构，引擎可根据不同业务场景选择
1.连接层
2.服务层
3.引擎层
4.存储层
MyISAM 和 InnoDB 的区别
2.索引优化分析
性能下降sql慢：
查询语句烂。
索引失败。
关联查询太多join。
服务器调优及参数设置（缓冲，线程数等）
常见的join查询：
sql执行顺序：
七种join：
full outer 的语法mysql不支持，oracle才支持，可用union实现同样效果
索引
目的在于提高查询效率，可以类比字典。可以理解为“排好序的快速查找数据结构”
优势：
①类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本
②通过索引列对数据进行排序，降低数据排序成本，降低CPU的消耗
劣势：
①索引也是一张表，该表保存了主键和索引字段，并指向实体表的记录
②降低更新表的速度，如对表进行insert，update，不仅要保存数据，还要保存索引文件
③索引只是提高效率的一个因素，如果mysql有大数据量的表，需要花时间研究建立最优秀索引
分类：
单值索引：一个索引只包含单个列，一个表可以有多个单列索引
唯一索引：索引列的值必须唯一，但允许有空值
符合索引：一个索引包含多个列
基本语法：
创建 create 【unique】 index indexName On mytable(columnname(length))
alter mytable ADD 【unique】 INDEX indexName on columnname(length)
mysql索引结构：Btree索引、Hash索引，full-text索引、R-Tree索引
Btree索引原理
哪些情况适合建索引：
①主键自动建立唯一索引
②频繁作为查询条件的字段
③查询中与其他表关联的字段，外键关系建立索引
④频繁更细你的不适合创建
⑤where条件里用不到的不创建索引
⑥高并发下倾向于创建组合索引
⑦查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
⑧查询中统计或分组字段
不适合创建：
①表记录太少
②经常增删改的表
③数据重复且分布平均的表字段，建立索引没有太大效果
性能分析：
①mysql query optimizer
②mysql常见瓶颈：CPU，IO，服务器硬件（top，free，iostat和vmstat查看系统性能）
③explain
执行计划 explain
所有字段：id，select_type，table，type，possible_key，key，ken_len，ref，rows，extra
id：表读取顺序
①id相同，可以认为是一组，执行顺序由上至下。
②id不同 如果是子查询，id序号越大，越先执行
在所有组中，id越大，优先级越高，越先执行
（衍生=derived = DERIVED）
select_type：数据读取的操作类型
① SIMPLE :简单的查询，不包含子查询或是union
② PRIMARY: 查询中若包含任何复杂的子部分，最外层查询则被标记为
③ SUBQUERY：在select或where中包含子查询
④ DERIVED：在from中包含子查询被标记为衍生，mysql或执行并把结果放在临时表中
⑤ UNIOn:若第二个select出现在union之后，则被标记为union。若union包含在from的子查询中，外层select被标记为derived
⑥ UNIOn RESULT：从union表中获取的结果
type：访问类型排序
查询类型从最好到最差依次是:system>const>eq_ref>ref>range>index>All
一般情况下,得至少保证达到range级别,最好能达到ref
system：表只有一行记录（等于系统表）
const：表通过索引一次就找到了，用于比较primary或者union索引，因为只匹配一行数据（写死的常量）
eq_ref:唯一性索引扫描，表中只有一条记录与之匹配（主键或唯一索引）
ref：非唯一性索引扫描，返回匹配某个单独值的所有行（索引找到多个返回）
range：只检索给定范围的行，使用一个索引来选择行。（between、<、>、in）
index：full index scan与all的区别为index类型只遍历索引树（index和all都是扫描全表）
all：遍历全表找到对应的行
possiblekey：理论上用到的索引
显示可能应用在这个表中的索引，一个或多个，但实际中不被使用到
key：实际使用到的索引
查询中若使用了覆盖索引，则索引仅出现在key列表中
key_len：索引中使用的字节数
长度越短越好，显示的是索引字段的最大可能长度，并非实际使用长度
where条件越多，精度越高，但key_len越多
ref：显示索引的那一列被使用了
如果可能得话，是一个常数
rows：估算出找到所需的记录所需要读取的行数
越少越好
extra：包含不适合在其他列显示但十分重要的信息
① using filesort ： 说明mysql会对数据库使用一个外部的索引排序，而不是按照表中的索引顺序进行读取。（文件索引）
② using temporary ：使用了临时表保存了中间结果，常见于order by 和 group by（建议group by 和索引一致）
③ using index ：使用了覆盖索引，避免访问了表的数据行，效率不错。如果出现了using where ，表明索引被用来执行索引键值的查找；如果没有，表明索引用来读取数据而非执行查找动作。
覆盖索引：就是select的数据列只用从索引中能够获取，不必读取数据行。mysql可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，即：查询列要被所建索引覆盖
④ using where
⑤ using join buffer： 使用了连接缓存
⑥ impossible where ： where 字句的值总是false，不能用来获取任何元组
⑦ select table optimized away ： 在没有group by的情况下，基于索引优化min/max的操作
⑧ distinct ： 优化distinct 操作，在找到第一匹配的元组后即停止
3.索引
分析
左连接和右连接相反加，左连接加在右表，右连接加在左表
例如 from A left join B on xxx ，此时因为A表肯定全部都有，B表是关键点，所以需要建立索引
尽可能减少join语句中的循环总次数：用小结果集驱动大的结果集
保证join语句中被驱动表上join条件以及被索引
当无法保证被驱动表的join条件字段被索引且内存资源充足的前提下，不要吝啬joinbuffer的设置
索引失效
① 全值匹配
② 最佳左前缀法则：如果索引多个值，要从索引的最左列开始，且中间不能断（类似楼层）
③ 不在索引列上做任何操作（计算、函数、（自动or手动）类型转换，会导致失效而转全局索引
④ 存储引擎不能使用索引中范围条件右边的列， 例如 a>19 and b -xxx ，此时a索引生效，b不生效
⑤ 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *
⑥ mysql在使用不等于（！=或 <>）的时候无法使用索引会导致全表扫描
⑦ is null ， is not null 也无法使用索引
⑧ like 以通配符开头（’%abc…‘)mysql索引失效会变成全表扫描的操作。如果非要写，就用覆盖索引，例如 索引是a+b ，select a，b from xx where a like ‘%xx%’
⑨ 字符串不加单引号索引失效 （因为底层会自动隐式的转换）
⑩ 少用or，用它来连接时索引失效
若条件全是常量的时候，即使不按照索引，mysql底层也会自动应用索引（不建议）
索引本质是用在了查找和排序
group by 表面是分组，分组之前必排序，会有临时表产生
建议：
① 在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好
② 在选择组合索引的时候，尽量选择可以包含当前query中where字句更多字段的索引
③ 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的
查询截取分析
①小表驱动大表
② order by 关键字优化
③ group by优化
实质是先排序后分组，遵从最佳左前缀
当无法使用索引列的时候，增加max_length_for_sort_data参数，以及增大sort_buffer_size参数设置
where 高于 having ，能写在where限定的条件就不去用having
慢查询日志
查看是否开启： show variables like ‘%slow_query_log%’
日志分析工具：mysqldumpslow
循环：
批量插入数据
用show profile进行sql分析
危险的四种：
全局查询日志
配置启用：
编码启用：
4.mysql锁机制
表锁
偏向MyIsam存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生冲突的概率最高，并发度最低。
读锁
lock table read/write 加锁
unlock table 解锁
读锁：
加了读锁后，自己和别人都能读，但是都不能update，并且自己不能再去读别的表
写锁：
特点
行锁
更新丢失：
脏读：
不可重复读：读到修改数据
幻读：读到新增数据
事务隔离级别，默认是可重复读
行锁：修改中时，同一条数据，若有其他的session在修改，会被阻塞等待
无索引，行锁自动升级为表锁
varchar类型若是没有写
间隙锁：update范围的，也会有锁
如何锁一行
行锁分析
show status like ‘innodb_row_lock%’
建议：
5.主从复制
