day13：数据库高级部分

数据库高级

• mysql架构介绍

• 索引优化分析

• 查询截取分析

• 主从复制

• mysql锁机制

1、查看存储引擎

show engines；

2、查看当前默认的存储引擎

show variables like ’%storage_engine%‘；

3、myisam和innodb区别

• myisam：不支持事务、不支持主外键，有表锁，只缓存索引，不缓存真实数据，表空间小

• innodb：支持事务、支持主外键，有行锁，不仅缓存索引，还缓存真实数据，表空间大

索引优化

性能下降，长记性时间长的原因：

• 查询语句写的不好

• 索引失效（单值、多值索引）

• 3关联查询太多join

• 服务器调优及各个参数的设置

1、sql执行顺序

from -> join -> on -> where -> group by -> having -> select -> order by -> limit

2、七种join的语法

• 内连接(交集)

select 查询列表 from 表1 inner join 表2 on 连接条件

• 左连接

select 查询列表 from 表1 left join 表2 on 连接条件

• 右连接

select 查询列表 from 表1 right join 表2 on 连接条件

• 全连接

select 查询列表 from 表1 full join 表2 on 连接条件

• 表1独有

select 查询列表 from 表1 left join 表2 on 连接条件 where 表2.列 is null

• 表2独有

select 查询列表 from 表1 right join 表2 on 连接条件 where表1.列 is null

• 并集

select 查询列表 from 表1 full join 表2 on 连接条件 where表1.列 is null or 表2.列 is null

3、索引

含义：索引是一种排好序的快速查找数据结构

索引分类：

• 单值索引：一个索引只包含单个列，一个表可以有多个单列索引

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

• 复合索引：一个索引包含多个列

4、创建索引

单值索引：create 【unique】 index 索引名 on 表名(字段名)；

​ alter 表名 add 【unique】index 【索引名】 on (字段名)；

多值索引：create index 索引名 on 表名(字段名，字段名，。。。)；

5、删除索引

drop index 【索引名】 on 表名；

6、查看索引

show index from 表名；

7、添加数据库索引的方式

• alter table 表名 add primary key(字段列表)；#添加主键索引，不能为空且唯一

• alter table 表名 add unique 索引名(字段列表)；#添加唯一索引，允许为空

• alter table 表名 add index 索引名(字段列表)；#添加普通索引

• alter table 表名 add fulltext 索引名(字段列表)；#添加全文索引

8、索引结构分类

• BTree索引

• Hash索引

• full-text全文索引

• R-Tree索引

9、需要创建索引的情况

• 主键自动建立唯一索引

• 频繁作为查询条件的字段应该建立索引

• 查询中与其他表关联的字段、外键关系建立索引

• 频繁更新的字段不适合建立索引

• where条件里用不到的字段不创建索引

• 查询中统计或分组字段

10、不需要创建索引的情况

• 表记录数较少

• 经常增删改的表

• 数据重复且分布平均的表字段

性能分析

1、explain介绍

语法：explain+sql语句(执行计划包含的信息)

执行计划包含的信息：

• id(用于查看加载顺序)

  • id相同，执行顺序由上到下

  • id不同时，如果是子查询，id的序号会递增，id的值越大越先被执行

  • id相同不同，同时存在

• select_type常见的值：

  • simple：简单的查询，查询中不包含子查询或union

  • primary：查询中若包含任何复杂的子查询，最外层查询会被标记

  • subquery：在select或where列表中包含子查询

  • derived：在from列表中包含的子查询会被标记为derived，mysql会递归执行这些子查询，把结果放在临时表里

  • union：若第二个select出现在union后，则被标记为union；若union包含在from子句的子查询中，外层select将被标记为dervide

  • union result：从union表获取结果的select

• table：显示这一行的数据时关于哪张表的

• type类型

  • all：全表扫描

  • index：只遍历索引树

  • range：只检索给定范围的值，使用一个索引来选择行

  • ref：非唯一性索引扫描，返回匹配某个单独值得所有行

  • eq_ref：唯一性索引扫描，对于每个索引建，表示只有一条记录与之匹配，常见于主键或唯一索引扫描

  • const：表示通过索引一次就找到了

  • system：表只有一行记录（等于系统表）

  • null

  • 排序从好到差：system>const>eq_ref>ref>range>index>all

• possible_keys：显示可能应用在这张表上的索引，一个或多个

• key：实际使用的索引

• key_len：表示索引字段可能最大使用的字节数，并非实际使用长度

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

• rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

• extra：包含不适合在其他列中显示但十分重要的额外信息

优化