MySQL数据库笔记

1. 查看sql是否走索引

在查询sql前面加一个explain

explain select ..........

1.1 explain查询出数据的列的含义

1. id:选择标识符

2. select_type:表示查询的类型。

3. table:输出结果集的表 , 显示这一行的数据是关于哪张表的

4. partitions:匹配的分区

5. type:表示表的连接类型, 索引没有失效,就会显示索引连接类型

   • 从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL

   • type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：

   • system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

   • 一般来说，得保证查询至少达到range级别，最好能达到ref。

6. possible_keys:表示查询时，可能使用的索引

   • 如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句

7. key:表示实际使用的索引,

   • 如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。
   • 这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引

8. key_len:索引字段的长度

   • 在不损失精确性的情况下，长度越短越好

9. ref:显示索引的哪一列被使用了，如果可能的话，是一个常数

10. rows:扫描出的行数(估算的行数)

11. filtered:按表条件过滤的行百分比

12. Extra:执行情况的描述和说明

2. 索引-避免不走索引的场景

1. 减少数据访问:加索引,避免全表扫描,

   • 在where和order by涉及的字段上建立索引,要安装什么内容检索,就在哪儿加索引
   • like查询,不要在字段开头进行模糊
   • 避免使用in(t1,t2),优化使用 字段 between 2 and 3 代替
   • 子查询用 exisit 代替 in
   • 避免使用or 优化,查询两次,使用union连接
   • 避免null的判断,给字段添加默认值0 where score is null 改为 where score= 0
   • 数据量大,避免使用where 1=1 (where 1=1 是为了方便拼接)
   • 不能使用 > < != 避免在此字段上建立索引改查询条件,
   • 使用order by 条件的要和和where条件一致

2. 减少数据的返回

   • 只返回需要的数据
   • 避免使用 select *
   • 多表查询, 小表在前
   • 使用表的别名

3. 减少交互-数据库连接的次数

   • 批量的DML,减少数据库连接的次数
   • 插入大量数据,不要单独插入,而是使用多值插入 Insert into T values(1,2),(1,3),(1,4);

4. 降低数据库cpu的使用:

   • 尽量减少排序操作和全表查询,减少cpu占用率
   • 使用truncate代替delete删除表

5. 使用表分区,增加并行数,更大限度的利用cpu资源

   • 合理分页,提高分页效率

3. SQL语法执行顺序

select 
distinct <字段,函数>
from <table1>
<连接类型> join <table2>
on <筛选条件>
where <查询条件>
group by <字段> # 以什么分组,
having <分组后的查询条件>
order by <排序条件>
limit <分页参数>

1. distinct 作用于单列,排除相同的值—查出结果是单列的

   • 作用于多列,排除这几列完全相同的值–查出结果的多列,没有相同的

   • 例如 3列 name age sex ,不会出现三者同时相同的数据

2. join 连接类型,内连接inner join,左外left join,右外right join,

   • 全连接

     select * from dept left join emp on emp.dept_id=dept.id
     union  # 表示两条sql查询条件列数相同,不存在的用null
     select * from dept right join emp on emp.dept_id=dept.id;
     

4. mysql三范式

1. 第一范式: 保证数据库表中的所有字段值都是不可分解的原子值

2. 第二范式: 一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

3. 第二范式: 每一列数据都和主键直接相关，而不能间接相关。

5. 隔离级别

• RU: 读未提交, 可以读到未提交的数据, 会发生脏读,幻读,不可重复读问题
• RC: 读已提交, 解决了脏读问题, 会发生幻读, 不可重复读问题
• RR: 可重复读, 阻止了脏读幻读, 未解决可重复读
• 串行化: 所有语句依次执行, 能解决脏读, 幻读, 可重复读问题

6. 主从复制

• 首次全量+后续增量的方式