【MySQL】索引、锁、主从复制

配置文件

1. 二进制日志log-bin：用于主从复制

2. 错误日志log-error：默认关闭

3. 查询日志log：默认关闭，记录查询的sql语句

4. 数据文件：

   两系统：win linux

   frm：存放表结构

   myd：存放表数据

   myi：存放表索引

索引优化分析

性能下降SQL慢、执行时间长、等待时间长

• 查询语句写的不好
• 索引失效
• 关联查询太多join（设计缺陷或不得已的需求）
• 服务器调优及各个参数设置（缓冲、线程数）

常见join查询

1. select的执行顺序

手写顺序：

机读顺序：
总结：

2. join图

​ 由于FULL在数据库语法中不支持，故进行变通。

​ A的独有+B的独有+AB共有：

select * from tableA a left join tableB b on a.key = b.key
union
select * from tableA a right join tableB b on a.key = b.key

​ A的独有+B的独有：

select * from tableA a left join tableB b on a.key = b.key where b.key is null
union
select * from tableA a right join tableB b on a.key = b.key where a.key is null

索引简介

1. 定义：排好序的快速查找的数据结构。会影响where后面的查找和order by后面的排序。

​ 一般索引也很大，往往以索引文件的形式存储在磁盘。

2. 优点：

   • 提高数据检索效率，降低数据库IO成本
   • 通过索引对数据排序，降低排序成本，降低了CPU消耗

3. 缺点：

   • 实际也是一张表，保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占空间的
   • 虽然大大提高查询速度，却会降低更新速度。因为更新表时，MySQL不仅要保存数据，还要保存索引文件每次更新添加了索引列的字段，调整因为更新带来的键值变化后的索引信息。

4. 索引分类

   • 单列索引
   • 唯一索引：索引列值必须唯一，但允许有空值
   • 复合索引

5. 索引结构

   • BTree索引

     

   • Hash索引

   • full-text全文索引

   • R-Tree索引

6. 什么情况下建索引

   • 主键自动建立唯一索引
   • 频繁查找的字段应建立索引
   • 查询中与其他表关联的字段，外键关系建立索引
   • 频繁更新的字段不适合创建索引
   • where条件里用不到的字段不建立索引
   • 单值/复合索引选择（在并发情况下倾向创建复合索引）
   • 查询中排序的字段
   • 查询中统计或分组的字段

7. 什么情况下不建索引

   • 表记录太少
   • 经常增删改的表
   • 数据的值重复且平均分配

8. 性能分析

   • MySQL Query Optimizer查询优化器

   • MySQL常见瓶颈