mysql 高级进阶（优化）

mysql 优化

• mysql 机构介绍
• 索引优化分析
• 查询截取分析
• 主从复制
• mysql锁机制

mysql 逻辑架构：

　　mysql 的架构可以在不同场景下应用并发挥良好作用，主要体现在存储引擎的架构上  mysql5.0+默认存储引擎未InnoDB

　　插件式的存储引擎架构将查询处理和其他的系统任务以及数据的存储提取相分离，这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

　　

1. 连接层
2. 服务层
3. 引擎层
   1. show engines；查看 ==》常用MYISAM,INNODB
   2. 查看默认存储引擎 show variables like '%storage_engine%' 默认InnoDB
   3. MYISAM 与INNODB 的区别 引用自csdn ：  https://blog.csdn.net/qq_35642036/article/details/82820178

   4. 对比	MyISAM	InnoDB
      主外键	×	√
      事务	×	√
      行表锁	表锁，即使操作一条记录也会锁住整个表， 不适合高并发操作	行锁，操作时只锁一行， 不对其他行有有影响 适合高并发操作
      缓存	只缓存索引，不缓存真实数据	不仅仅缓存索引还缓存真实数据， 对内存要求较高， 而且内存大小对性能有决定性影响
      表空间	小	大
      关注点	性能	事务
      默认安装	Y	Y

 

 

 

 

 

 

 

 

　　　

 

 

 

 

　　　　4.存储层

索引优化分析

• 性能下降sql慢
• 执行时间长
• 等待时间长
  • 原因：
    • 查詢語句写的慢
    • 索引失效
    • 关联查询太多join（设计上的缺陷）
    • 服务器调优以及各个参数设置（缓冲，线程等）
• 常见通用join查询
  • sql执行顺序
  • join图
  • 建表sql
  • 7种join
• 索引简介
  • mysql中索引index时帮助mysql高效获取数据的数据结构。可以得到索引得到本质：索引是数据结构（即排好序的快速查找的数据结构） 
  •  一般来说索引本身很强大，不可能全部存储到内存中因此索引往往以索引文件形式存储到磁盘上
  • 我们平常说的索引如果没有特别声明，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引，其中聚集索引，次要索引，覆盖索引
  • 在数据之外，数据库系统还在维护着满足特定查找算法的数据，这些数据结构以某种方式引用指向数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引
  • 复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引，当然，除了b+树这种类型的索引之外，还有哈希索引hash index等
  •  请查看这个博客--》https://blog.csdn.net/qq_33419463/article/details/9051508
  • 优势：
    • 提高数据检索的效率，降低数据库的io成本
    • 通过索引列对数据进行排序，降低数据排序成本，降低cpu消耗
  • 劣势：
    • 实际上索引也是一张表，该表保存了主键和索引字段，并指向实体表的记录，所以索引列也是要占用空间的
    • 虽然索引大大提高查询效率但是会降低更新表的速度，如对标进行insert，update，和delete，更新表时mysql不仅要保存数据，
    • 还要保存一下索引文件更新添加了索引列的字段
    • 都会调整因为更新所带来的键值变化后的索引信息。
  • mysql索引分类：
    • 单值索引：一个索引只包含单个列，一个表可以有多个单列索引
    • 唯一索引：索引列的值必须唯一，但是允许有空值
    • 复合索引：一个索引包含多个列
    • 基本语法：
      • 创建
        • create [unique] index indexname on mytable(columnname(length))
        • alter mytable add [unique]  index [indexname] on (columnname(length))
      • 删除:
        • drop index[indexname] on mytable
      • 查看：show index from table_name\G
      • 使用alter命令

      • ALTER TABLE tbl_name add primary key (column_list);  #该语句添加一个主键，这意味着索引值必须是唯一的，且不能为null
        ALTER TABLE tbl_name add unique index_name(column_list);   #这条语句创建索引的值必须是唯一的（除了null外，）
        ALTER TABLE tbl_name add index index_name(column_list);#添加普通索引，索引值可出现多次
        ALTER TABLE tbl_name add fulltext index_name(column_list);#该语句指定了索引为fulltext ，用于全文索引。

  • mysql索引结构：
    • Btree索引：
      • 检索原理：根据条件进行io查询，查找到指针指到第一层树的io然后判断其对应指针地址的值的条件，准确判断，然后一次往下，知道找到其对应的值，返回其指针指向的地址。
    • Hash索引
    • full-text全文索引
    • R-Tree索引
  • 哪些情况需要创建索引：
    • 主键自动建立唯一索引
    • 频繁作为查询条件的字段应该创建索引
    • 查询中与其它表关联的字段，外键关系建立索引
    • 频繁更新的字段不适合创建索引（因为每次更新不单单那更新记录，还要更新索引树）
    • where条件里用不到的字段，不创建索引
    • 单值/复合索引的选择？（在高并发下倾向创建组合索引）
    • 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
    • 查询中统计或者分组的字段
  • 哪些情况不要建索引：
    • 表记录太少
    • 经常增删改的：（提高了查询速度，同时却会降低更新表的速度，如对表进行增删改，因为更新表的时候，mysql不仅仅会保存更新数据，还要保存一下索引文件）
    • 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引，（注意：如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果） 索引列中不同值的数目与表中记录数的比，如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引选择性就是1980/2000 = 0.99
    • 一个索引的选择越接近与1，这个索引的效率
  • 单值
    • create index idx_user_name on user(name)
  • 复合
    • create index idx_user_nameEmail on user(name,email)
• 性能分析;
  • MYsql Query Optimizer
  • mysql常见瓶颈：
    • 1. cpu在饱和的时候一般在数据装入内存或者从磁盘上读取数据的时候
      2. io磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
      3. 服务器硬件的性能瓶颈：top，free，iostat和vmstat来查看系统的性能状态
  • Explain：
    • 执行计划：使用explain关键字可以模拟优化器执行sql查询语句，从而知道mysql是如何处理你的sql语句的，分析查询语句或是表结构的性能瓶颈
    • 操作：Explain + sql 语句，执行计划包含的信息
    • • id：
        • id 相同的时候：执行简单查询的时候一次输入三张表，这三张表的id相同，，table不同，查询类型也相同。
        • id不同时：即查询级别数越往里嵌套子查询他们的id值会被优先执行，所以会变大（primary为最外查询，id小，subquery为子查询，id越来越大，优先级越高）
        • id相同不同，同时存在：（DERIVED：衍生）虚表，table 名为derived‘2’  这个2为衍生虚表里面子查询的id
      • select_type:
        • SIMPLE
          • 简单的select查询，查询不包含子查询或者union
        • PRIMARY
          • 查询若包含任何的子部分，最外层查询则被标记为PRIMARY,
          • id的序号会递增，id值越大优先级越高，越先被执行
        • SUBQUERY
          • 在select或where列表种包含子查询
        • DERIVED
          • 在from列表包含的子查询标记为DERIVED（衍生）mysql会递归执行这些子查询，把结果放在临时表里
          • （DERIVED：衍生）虚表，table 名为derived‘2’  这个2为衍生虚表里面子查询的id
        • UNION
          • 第二个select出现在union之后，则被标记为union，若union包含from子句的子查询中，外层select将被标记为：DERIVED
        • UNION RESULT
          • 从union表获取结果的select
      • table ：表名
      • partitions：
      type（访问类型）：从最好到最差依次是：system》const》eq_ref>ref>range>index>all
      • all：
      • index：full index scan index与all的区别在于index类型只遍历索引树，这通常比all快，因为索引文件通常比数据文件小，也就是all和index都是读全表，单index是从索引中读取的，而all是从硬盘中读的（比all稍微好一点）
      • range：只检索给定范围的行，使用一个索引来选择行，key显示使用了那个索引，一般就是在你的where语句中出现了between，《》in等查询，这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一个点，而结束语另一个点，不用扫描全部索引。
      • ref：非唯一性扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体
      • eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描
      • const：表示通过索引一次就找到了，const用于比较primary key或者unique索引，因为只匹配一行数据，所以很快如将主键置于where列表中，mysql就能将该查询转换为一个常量
      • system：表只有一行记录，等于系统表，这是const类型的特列，平时不会出现，可以忽略不记
      • null：
      • possible_keys:显示可能用到这张表中的索引，一个或多个，查询涉及到的字段上若存在索引，则该索引将被列出，单不一定被查询实际使用
      • key:被使用到的索引，实际使用的索引，如果为null，则没有使用索引，查询中若使用了
      • 覆盖索引，则该索引仅出现在key列表中
      • key_len:表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，不损失精度情况下，越短越好，表示索引字段最大可能长度，并非实际使用长度，不是通过表内检索出的
      • ref：显示索引的哪一列被使用，如果可能的话，是一个常数（const），哪些列或常量被用于查找索引列上的值
      • rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要的读取行数
      • Extra：包含重要信息
        • Using filesort：说明mysql会对数据进行使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，mysql中无法利用索引完成的排序操作称为“文件排序”
        • Using  temporary: 使用了临时表保存中间结果，mysql在对查询结果排序时使用临时表，常见于排序order by和分组查询group by
          • Using Index ： 表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率不错，如果同时出现using where ，表明索引被用来执行索引键值的查找如果没有同时出现using where ，表明索引用来读取数据而非执行查询动作
            
            • 索引覆盖:就是select的数据列只能用从索引中就能够取得，不必读取数据行，mysql可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说，查询列要被所建的索引覆盖。
            • 注意 ：尽量注意select列表中只取出需要的列，不可select *，因为如果将所有字段一起做索引会导致suoyin文件过大，查询性能下降
          • Using where
          • using join buffer：使用连接缓存
          • impossible where ：where 子句总是false，不能用来获取任何元组
          • select tables optimized away：
          • distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

mysql> explain select * from tbl_emp;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | tbl_emp | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    8 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+

 

• • • 能干啥：
      • 表的读取顺序
      • 读取曹祖的操作类型
      • 哪些索引被引用
      • 哪些索引被实际使用
      • 表之间的引用
      • 每张表有多少行被优化器查询
• 索引优化

 

新建两张表

CREATE TABLE `tbl_emp` (
       `id` int(11) NOT NULL AUTO_INCREMENT,
       `name` varchar(20) DEFAULT NULL,
       `deptId` int(11) DEFAULT NULL,
       PRIMARY KEY (`id`) ,
       KEY `fk_dept_id`(`deptId`)
)ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8;

CREATE TABLE `tbl_dept` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `deptName` varchar(30) DEFAULT NULL,
        `locAdd` varchar(40) DEFAULT NULL,
        PRIMARY KEY (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8;


insert into tbl_dept(deptName, locAdd) values ('RD',1);
insert into tbl_dept(deptName, locAdd) values ('HR',1);
insert into tbl_dept(deptName, locAdd) values ('MK',1);
insert into tbl_dept(deptName, locAdd) values ('MIS',1);
insert into tbl_dept(deptName, locAdd) values ('FD',1);

INSERT INTO tbl_emp(name, deptId) VALUES ('z3',1);
INSERT INTO tbl_emp(name, deptId) VALUES ('z4',1);
INSERT INTO tbl_emp(name, deptId) VALUES ('w5',1);
INSERT INTO tbl_emp(name, deptId) VALUES ('w5',2);
INSERT INTO tbl_emp(name, deptId) VALUES ('w6',2);
INSERT INTO tbl_emp(name, deptId) VALUES ('s7',3);
INSERT INTO tbl_emp(name, deptId) VALUES ('s9',51);
INSERT INTO tbl_emp(name, deptId) VALUES ('s8',4);

select * from tbl_emp;
select * from tbl_dept;
# ab同  
select *
from tbl_emp a
inner join tbl_dept b on a.deptId = b.id;
# a独有+ab同    
select *
from tbl_emp a
left join tbl_dept b on a.deptId = b.id;
# a独有       
select *
from tbl_emp a
left join tbl_dept b on a.deptId = b.id
where b.id is null;

# b独有+ab同
select *
from tbl_emp a
right join tbl_dept b on a.deptId = b.id;

# b独有
select *
from tbl_emp a
right join tbl_dept b on a.deptId = b.id
where a.deptId is null;

# 全连接 a独有+b独有+ab同
select * from tbl_emp a left join tbl_dept b on a.deptId = b.id
union
select * from tbl_emp a right join tbl_dept b on a.deptId = b.id;

# a独有+b独有
select * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null
union
select * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.id is null;