第06章_索引的数据结构.pdf

基础

1.查询去重

select distinct username from employees;

2.常见函数

单行函数

传入一个值,返回一个值

分组函数

传入一组值,返回一个值image.png
和分组函数一同查询的字段要求是group by后面的字段

image.png

分组查询的筛选条件分两类
image.png

字符串函数

image.png

数学函数

image.png

日期函数

image.png
image.png
image.png

3.count(*)和count(1)

效率:
MYISAM存储引擎下 count(*)效率高,有内部计数器直接返回了个数
INNODB存储引擎下,效率差不过,但是要比count(字段)高,因为字段要判断是否为null

多表查询

image.png

image.png
image.png

内连接

用左边表的记录去匹配右边表的记录，如果符合条件的则显示。如：从表.外键=主表.主键

隐式内连接

SELECT 字段名 FROM 左表, 右表 WHERE 条件
select * from emp,dept where emp.dept_id = dept.id; (等值连接,where后面条件是>或<这种就非等值连接
比如查询工资的等级)

非等值连接

image.png

自连接

image.png
把e看成员工表,m看成领导表

显式内连接

SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件

外连接

image.png

总结

image.png
image.png

子查询

image.png
外键?

表复制

1.复制表结构
create table 新表名 like 旧表名;
2.复制表结构+数据
create table 新表明 select *from 旧表名
image.png

事务

image.png

隔离级别

image.png
image.png

视图

image.png
image.png
image.png

进阶

1.SQL执行流程

sql服务器处理客户端请求流程

image.png

2.存储引擎

image.png
image.png
总结
InnoDB : 索引即数据,占用内存较高,支持事务,支持行级锁,索引和数据一起存储,适用于并发量高,频繁做更新删除操作
MyISAM : 不支持事务,行级锁,外键,访问速度快,索引和数据分开存储,适用于频繁查询和插入操作,针对数据统计有额外的常数存储所以count(*)效率高

image.png

2.1 InnoDB行格式

2.1.1 compact行格式

image.png
变长字段长度列表
MySQL 支持一些变长的数据类型，比如 VARCHAR间、 BINARY(M)、 各种 TEXT 类型、各种 BLOB 类型.我们也可以把拥有这些数据类型的列称为变长字段.变 长字段中存储多少字节的数据是不固定的，所以我们在存储真实数据的时候需要顺便把这些数
据占用的字节数也存起来，这样才不至于把 MySQL 服务器销懵 也就是说这些变长字段占用 的存储空间分为两部分

  • 真正的数据内容.
  • 该数据占用的字节数.

COMPACT 行格式中，所有变长字段的真实数据占用 字节数都存放在记录的开头位
置，从而形成一个变长字段长度列表，各变长字段的真实数据占用的字节数按照列的顺序逆序存放.再次强调一遍，是逆序存放

NULL值列表
一条记录中的某些列可能存储 NULL 值，如果把这些 NULL 值都放到记录的
真实数据中存储会很占地方，所以 COMPACT 行格式把一条记录中值为 NULL 的列统一管理起来 ，存储到 NULL 值列表中

记录头信息
记录头 信息由固定的5字节组成，用于描述记录的一些属性. 字节也就是 40 个二进制位 不同的位代表不同的意思

记录的真实数据
MySQL 会为每个记录默认地添加一些列(也称为隐藏列)
image.png
这里需要提一下 InooDB 表的主键生成策略 优先使用用户自定义的主键作为主键，如果用户没有定义主键 则选取一个不允许存储 NULL 值的 UNIQUE 键键作为主键 如果表中连不允许存储 NULL值的 UNIQUE 键都没有定义，InooDB 会为表默认添加一个名为 row_id 隐藏列作为主键.

3.索引

3.1 为什么用索引(索引的优点)

  1. 索引是存储引擎用于快速查找数据的一种数据结构,可以减少磁盘I/O次数,加快查询速率
  2. 创建唯一索引,可以保证数据库中每一行数据的唯一性
  3. 在实现数据的参考完整性方面，可以加速表和表之间的连接。换句话说，对于有依赖关系的子表和父表联合查询时，可以提高查询速度。
  4. 在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间 ，降低了CPU的消耗。

索引缺点

  1. 创建和维护索引耗费时间,数据量增加,所耗费时间也会增加
  2. 索引需要占磁盘空间

  3. 索引提高查询速度的同时会降低更新表的速度,当表中数据进行增删改操作时,索引也要动态维护,这样就降低了数据的维护速度

    3.2 InnoDB索引的结构

    聚簇索引

    image.png
    image.png

    非聚簇索引（二级索引，辅助索引）

    image.png

    image.png

    联合索引（非聚簇索引中的一种）

    image.png
    image.png

    3.3 InnoDB的B+树索引的注意事项

    根页面位置不变

    image.png

    内节点中目录项记录的唯一性

    image.png
    image.png
    image.png

    3.4 MyISAM索引结构

    可以理解为myISAM没有聚簇索引，只有二级索引，myISAM分两个文件存储，一个存储数据，另一个存储索引，索引文件仅仅保存数据记录的地址，
    image.png

    3.5 对比

    image.png

    3.6 哪些情况下适合创建索引

  4. 字段的数值有唯一性的限制

  5. 频繁的为where查询条件的字段
  6. 经常group by 和order by的列
    1. 如果group by 和order by都有使用可以建立联合索引,注意group by 的列放在前面
  7. update,delete的where条件

image.png

  1. distinct字段需要创建索引
  2. 多表join连接操作时,创建索引注意事项

image.png 7. 使用列的类型小的创建索引

  1. 使用字符串前缀创建索引

image.png
image.png

  1. 区分度(散列性高)的列适合作为索引

image.png

  1. 使用最频繁的列放到联合索引的左侧

  2. 在多个字段都要创建索引时,联合索引优化单值索引

    3.7 限制索引的数目

    建议单标索引数量不超过6个
    原因

  3. 每个索引要占用磁盘空间

  4. 索引会影响insert,delete,update等语句的性能

  5. 优化器在选择如何优化查询时,会根据同一信息,对每一个可以用到索引来进行评估,以生成出一个最好的执行计划,如果同时有很多个索引都可以用于查询,会增加MySQL优化器生成执行计划时间,降低查询性能

    3.8 哪些情况下不适合创建索引

  6. 在where,group by,order by中使用不到的字段,不要设置索引

  7. 数据量小的表中不要使用索引
  8. 有大量重复数据的列上不要建立索引
  9. 避免对经常更新的表创建过多的索引
  10. 不建议用无需的值作为索引
  11. 删除不再使用或很少使用的索引
  12. 不要定义冗余或重复的索引

数据库调优

当我们遇到数据库调优问题的时候，该如何思考呢？这里把思考的流程整理成下面这张图。
整个流程划分成了 观察（Show status） 和 行动（Action） 两个部分。字母 S 的部分代表观察（会使
用相应的分析工具），字母 A 代表的部分是行动（对应分析可以采取的行动）。
image.png
image.png
第09章_性能分析工具的使用.pdf

explain使用

列名 描述
id 在一个大的查询语句中每个SELECT关键字都对应一个 唯一的id
select_type SELECT关键字对应的那个查询的类型
table 表名
partitions 匹配的分区信息
type 针对单表的访问方法
possible_keys 可能用到的索引
key 实际上使用的索引
key_len 实际使用到的索引长度
ref 当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows 预估的需要读取的记录条数
filtered 某个表经过搜索条件过滤后剩余记录条数的百分比
Extra 一些额外的信息


索引失效

当被索引字段与原字段类型不匹配的时候，索引就会失效

被索引字段使用了表达式计算,包括嵌套查询，索引页无法生效,比如如下sql

3.被索引字段使用了函数

在like关键字后使用了左模糊查询或者左右模糊查询

5.被使用的索引字段不是联合索引的最左字段