MySQL强化篇指优化思路总结

标签：数据 查询 sys 索引 MySQL 思路 优化 id select

基础--

连接 退出数据库

连接 ：mysql -h 地址 -P 接口 -u 用户名 -p 密码

退出：exit或者 /q

数据库操作

关键字create 创建数据库

create database 数据库名

如：create database test default charset utf8

关键字 show 查看当前有哪些数据库

show database；

查看创建数据库的语句

show create database 数据库名；

关键字alter 修改数据库信息

alter database 数据库名 [新的数据库选项]

关键字drop 删除数据库

drop database 数据库名；

关键字use 进入指定的数据库

use 数据库名；

表的操作

关键字create 创建新的数据表

create table 表名{

字段1 字段类型[字段选项]

字段2 字段类型[字段选项]

...

}表选项信息；

如

create table test{

id int(10) not null auto_increment comment'id',

conment varchar(100) not null default coment '内容'

}engine= innodb

关键字 show 查询当前数据库下有哪些表

show table；

关键字like 模糊查询

show table like '%迷糊查询表名%'；

查看表的创建语句

show create table 表名；

查看表结构：

desc 表名；

关键字drop 删除数据表

drop table [if exist] 表名；

修改表名alter

alter table 旧表名 rename to 新表名；

修改列定义：

关键字add 增加一列

alter table 表名 add 新列名 字段类型[字段选项]；

alter table test add name char(10) not null ；

关键字 drop 删除一列

alter table 表名 drop 字段名；

关键字 modify 修改字段类型

alter table 表名 modify 字段名 新的字段类型

关键字 first 修改字段排序，把某个字段放在最前面

alter table 表名 modify 字段名 字段类型 first；

关键字 after 修改字段排序，把字段1放在字段2 后面

alter table 表名 modify 字段1 字段类型 after 字段2；

关键字：change 重命名字段

alter table 表名 change 原字段名 新字段名 新字段类型；

数据操作

关键字 insert 插入数据

insert into 表名（字段列表）values（值列表）；

表中字段比较多的时候，也可以这样写：

insert into 表名 set  字段1 = 值1，字段2=值2，...；

关键字 select 查询数据

select *[字段列表]

from 表名

where 查询条件

group by 字段名

order by 字段名 desc（默认）（asc升序）

关键字 delect 删除数据

delect from 表名 where 删除条件；

关键字 update 修改数据

update 表名 set 字段1 = 新值1，字段n = 新值n where 修改条件；

关键字 limit 分页

select * from 表名 where 查询条件 limit 每页数据*（页数-1），要查询的数据量；

select * from 表名 where 查询条件 limit 要查询的数据量；

存储过程：

MySQL存储过程是一组为了完成特定功能的SQL语句集，它存储在数据库中，可以被重复调用（运行在数据库的主机上甚至是数据库的进程里）。优点：基于运行在数据库主机上的特点，存储过程完成数据交互不需要进行网络间的传输，可以提高数据库操作的效率、保证数据的完整性和安全性，同时也简化了应用程序的代码。在企业级软件和数仓开发中比较常见。缺点：可以认为存储过程的代码和应用程序的代码是分开的，涉及到的代码的打包发布以及版本管理会增加很多工作量，MySQL5.0之后才支持存储过程，还不太完备，对于分库分表以及分布式架构，存储过程就完全不可用了，因为数据已经分散在不同的主机上，存储过程不需要网络交互这种优势也就不存在了。

创建存储过程的基本语法如下：

sql

DELIMITER //

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
    -- 这里是存储过程的SQL语句
END //

DELIMITER ;

在存储过程中，可以使用局部变量、流程控制语句（如IF-ELSE、CASE、WHILE等）、游标和异常处理。存储过程的参数可以是输入（IN）、输出（OUT）或输入输出（INOUT）类型。

调用存储过程使用CALL语句，例如：

sql

CALL 存储过程名(参数列表);

查看存储过程可以使用以下命令：

sql

SHOW PROCEDURE STATUS WHERE db='数据库名';

或者查询information_schema数据库：

sql

SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='数据库名';

删除存储过程使用DROP PROCEDURE语句：

sql

DROP PROCEDURE IF EXISTS 存储过程名;

修改存储过程可以使用ALTER PROCEDURE语句。

存储过程的优点包括代码复用、安全性、减少网络传输和预编译带来的性能提升。但它们也有缺点，如可移植性差、维护困难和与业务逻辑的耦合。

索引

主键索引：primary key

唯一索引：unique key

联合索引：

全文索引：fulltext key

1. DISTINCT 其实是对后面所有列名的组合进行去重，你能看到最后的结果是 74 条，因为这 74 个部
   门id不同，都有 salary 这个属性值。如果你想要看都有哪些不同的部门（department_id），只需
   要写 DISTINCT department_id 即可，后面不需要再加其他的列名了。

高级--mysql的优化和原理分析

一、索引

索引的原理

索引的原理大致概括为以空间换时间，数据库在未添加索引的时候进行查询默认的是进行全量搜索，也就是进行全局扫描，有多少条数据就要进行多少次查询，然后找到相匹配的数据就把他放到结果集中，直到全表扫描完。

这个过程中是直接查询磁盘，需要进行多次io，io会造成大量的时间开销，影响查询性能。

而建立索引之后，会将建立索引的KEY值放在一个n叉树上（BTree）。因为B树的特点就是适合在磁盘等直接存储设备上组织动态查找表，每次以索引进行条件查询时，会去树上根据key值直接进行搜索。整个过程相当于是为磁盘上的数据创建了一个目录，在查询操作的时候就会先查询这个目录，根据目录查到所需查询数据所在是位置，这样就可以只进行一次io操作，节省大量的时间，提高了查询性能。

索引之所以可以提高查询性能，本质上跟索引使用的数据结构有关。

索引的数据结构

数据结构介绍

1. 1. 红黑树：平衡二叉树的一种体现。平衡二叉树为了维护树的平衡，在不满足平衡的情况就要自旋，但是自旋会导致一定的开销。红黑树在不平衡状态下对自旋造成的开销和减少查询次数之间做了权衡，因此红黑树并不总是平衡二叉树。 红黑树已经在查询性能上做了优化，但是索引 并没有使用红黑树来存储数据，因为红黑树在每一层上存放的数据是有限的，导致一旦数据量太大，树的深度就会变大，影响查询性能。
   2. B树，B+树

1. 1. 1. B树（mysql默认一个节点可以存放16K的数据，每个索引的大小1032B-->每个节点可以存放15个索引）索引是以键值对的形式存放的， key：8B value：1K

1. 1. 1. 1. 一个节点中可以存放多个数据，树的深度由一个节点最多可以存放多少数据决定
         2. B树一个节点只能存放15个索引数据，依然不满足存放海量数据的查询性能优化
         3. B树的根节点、非叶子节点、叶子结点都在存放数据，只支持随机检索

1. 1. 1. B+树（平衡多路查找树）

1. 1. 1. 1. 非叶子节点冗余到叶子结点，非叶子节点相当于叶子结点的索引
         2. 叶子结点从左到右是依次增大的
         3. 叶子结点之间提供了指针， 提高了区间访问的性能

#举例
select * from employees where id > 1024;

1. 1. 1. 1. 只有叶子结点存放键值对，非叶子节点只存放key（8B）、外加指针域（6B） --使非叶子节点存放更多的数据
         2. B+树一个节点可以存放1170个索引键

1. 1. hash表 性能最好，但是索引没有用这种数据结构是因为hash表存储的数据不连续，hash表不支持范围查找（区间访问）

总结：mysql索引使用的数据结构是B+树，1、B+树的特点 2、B+树非叶子节点只存放key和指针域，可以存放更多的数据 3、叶子结点之间有指针相连，提高了区间访问性能

索引存放位置

win系统：C://programdata/mysql（隐藏文件夹）

• Innodb存储引擎的表：索引和数据会存放在同一个文件夹 *.idb
• MyISAM存储引擎的表：索引和数据存放在不同的文件夹

• • 索引存放在：*.MYI
  • 数据存放在：*.MYD

索引的分类和创建

• 分类

• • 主键索引：主键自带索引功能，根据主键查询性能很好
  • 普通索引：为普通列创建的索引

#格式
create index 索引名称 on 表名（字段名）;
#举例
create index idx_name on employees(name);

• • 唯一索引：就是唯一列，列中的数据时唯一的，比普通索引的性能要好

#格式
create unique index 索引名称 on 表名（字段名）;
#举例
create unique index idx_unique_name on employees(name);

• • 联合索引（组合索引）：一次性为表中的多个字段一起创建索引，最左前缀法则 注意：建议不超过5列

#格式
create index 索引名称 on 表名（字段名）；
#举例
create index idx_name_age_posotion on employees(name,age,position);

• • 全文索引：进行查询的时候，数据源可能来自不同的字段或者不同的表

Innodb和MyISAM的区别（存储引擎是在表级别）

• Innodb : 索引和数据放在同一个文件中（.idb），通过找到索引后就可以直接在索引树叶子结点找到完整数据，性能较好 -- 聚簇索引

• • 可以支持行锁和表锁

• MyISAM: 索引和数据放在不同的文件中，找到索引后还需要在另一个文件中找完整的数据。 -- 非聚簇索引

• • 天然支持表锁和全文检索
  • 对于一条sql语句，MYISAM存储引擎是怎么找数据的？

• • • 如 select * from table1 where Col1 = 30；
    • 根据where条件Col1 = 30，先到.MYI文件中定位到索引元素，再根据这个索引元素保存的数据位置信息（磁盘文件地址），根据地址信息到.MYD文件中定位数据

      

联合索引和最左前缀法则

1、联合索引的特点

使用一个索引实现表中多个字段的索引效果。

比如给三个字段创建索引，就会有三棵索引树，而使用联合索引就只有一棵索引树了。

2、联合索引如何存储

跟普通索引一样，叶子结点存放的是主键值

3、最左前缀法则

最左前缀法则表示 一条sql语句在联合索引中是否命中索引

• 最左前缀法则为什么一定要用a作为查询条件才能命中索引？

• • 因为索引的结构是B+树，索引树的创建是根据联合索引的多个字段信息进行排序的，先根据第一列进行排序，然后在第一列的基础上对第二列进行排序，如果不用a作为查询条件，针对索引树而言，b是无序的，也就是没有走索引。换句话说，就是在a相等的情况下b才是排好序的，对于整张表而言，b是无序的，也就不符合索引的原理，自然就不会走索引了。

#创建联合索引
create index idx_a_b_c on table1(a,b,c);
#sql语句 是否命中索引
select * from table1 where a = 10;#命中
select * from table1 where a = 10 and b = 20;#命中
select * from table1 where a = 10 and b = 20 and c = 30;#命中
select * from table1 where b = 20;#未命中
select * from table1 where b = 20 and c = 30;#未命中
select * from table1 where a = 10 and c = 30;#命中
select * from table1 where c = 30;#未命中
select * from table1 where a = 10 and c = 30 and b = 20;#命中,走abc，mysql有内部优化器，会进行一次内部优化

常见索引面试题

1. 为什么非主键索引的叶子结点存放的数据是主键值

如果普通索引中不存放主键值，而是存放完整数据，会造成：

1. 1. 数据冗余，虽然提升了查询效率，但是需要浪费更多的空间存放冗余的数据
   2. 维护麻烦：一个地方修改，需要对多棵索引树进行同步修改

2. 为什么创建Innodb表必须创建 主键

如果没有创建主键，mysql优化器会给一个虚拟主键，在使用普通索引查找的时候会使用这个虚拟主键，造成性能开销，索引在数据表创建的时候就创建主键

3. 为什么使用主键时，推荐使用整型的自增主键

1. 1. 为什么使用整型，主键索引树是一个二叉排序树，数据的存放是有序的

1. 1. 1. 整型的数据大小顺序好比较
      2. 字符串类型的数据在比较之前需要进行一次编码，浪费性能

1. 1. 为什么要自增

1. 1. 1. 如果不自增，使用不规律的整数作为主键，主键索引树就需要进行多次自旋操作来保证索引树叶子结点中的数据是从左到右-从小到大的顺序存放，性能会比使用自增差很多。

MySQL表关联常见的算法

驱动表：表关联中的小表，小表指的是要查询的结果集中数据量小的表

被驱动表：表关联中的大表。

小表驱动大表

嵌套循环连接 Nested-Loop join

explain select * from t1 inner join t2 on t1.a = t2.a; t1 、t2表中的a字段都是索引字段

表关联的条件字段是索引字段的时候，选择NLJ算法

基于块的嵌套循环连接算法 Block-Nested-Loop join

explain select * from t1 inner join t2 on t1.b = t2.b; t1 、t2表中的b字段都不是索引字段，选择BNL

二、SQL优化

explain的各列细节

• select_type列： 该列描述了查询的类型

• • simple：简单类型，不包含子查询的简单查询
  • primary：最外部的select
  • derived：执行的SQL的子查询在from后面，就会生成一张衍生表，他的类型就是derived
  • subquery：select之后 from之前的子查询
  • union：使用union进行联合查询的类型

关闭mysql对衍生表的合并优化

set session optimizer_switch='derived_merge = off';

• table列：当前查询的是哪张表
• type列：type列可以直观的判断出当前SQL的性能，type列取值和性能优劣如下

null > system > const > eq_ref > ref > range > index > all

• • 对于sql优化来说，要尽量保证type列 的值是range及以上级别
  • null：性能最好，一般是使用了聚合函数操作索引列，结果直接从索引树获取

select min(id) from order;
#相当于直接查到索引树的第一个节点的第一个id,直接返回

• • system：很少见，直接跟一条记录进行匹配

select * from (select * from order where id  = 1);

• • const：使用主键索引或者唯一索引直接跟常量进行比较，性能很好

select * from order where id = 1；

• • eq_ref：多表联查的时候，查询条件是用主键进行比较

select * from order_item left join order where order_item.order_id = order.id;

• • ref：（从根节点通过折半查找去找数据）

• • • 简单查询：使用普通列进行查询
    • 复制查询：在进行连接查询时，连接查询条件使用了本表的联合索引列

• • range：在索引上使用了范围查询

select * from order where id > 1;

• • index：查询表中的所有记录，但是记录都可以从索引树上查到（遍历整个索引树）

  • 

  • all：没有走索引，进行了全表扫描

    

• id列：id越大越先执行，id相同，上面的先执行
• possible_keys: 这次查询可能会走的索引。mysql的内部优化器会进行判断，如果此次查询走索引的性能比全表查询更差，就不走索引。--可以根据trace工具查询判断依据
• key列：实际使用了哪个索引
• rows列：该语句可能查询的数据条数
• key_len列：键的长度，该字段可以看出命中了聚簇索引中的哪几列
• extra列：extra列提供了一些额外的信息，能够帮助我们判断当前的sql是否使用覆盖索引、文件排序、使用索引作为查询条件等信息

• • Using index：使用了覆盖索引

• • • 所谓的覆盖索引就是：当前查询的所有字段都是索引列，这样就可以直接在索引树获取数据，而不需要查表。这种优化手段很常用。

EXPLAIN SELECT role_name, role_code FROM sys_role WHERE role_code = 'system';

• • Using where：使用索引列进行范围查找

EXPLAIN SELECT  * FROM sys_role WHERE id > 1;

• • Using index condition：查询的列没有被索引列完全覆盖，建议使用覆盖索引来优化
  • Using temporary：在非索引列上进行去重操作就需要创建一张临时表来实现，性能很差

EXPLAIN SELECT DISTINCT role_code FROM sys_role

• • Using filesort：使用文件排序：指数据存放在磁盘中，排序的时候需要从磁盘中读取数据到内存，会涉及到两个概念，单路排序、双路排序

• • • 单路排序：把select查询的结果集放到内存中去排序，排序好之后的结果集就是我们想要的结果，占用的内存比较大
    • 双路排序：（又叫回表排序）只把id和排序的字段放到内存中去排序，排序之后再根据id到磁盘中读取完整的数据，也就是需要回表。所以它占用的内存小一点
    • MySQL根据表中字段的总大小判断走单路排序还是双路排序，>1024走双路,<1024走单路

EXPLAIN SELECT * FROM sys_role ORDER BY role_name;

• • Select tables optimized away：直接在索引列上进行聚合函数的操作，没有进行任何表的操作。

EXPLAIN SELECT MIN(id) FROM sys_role;

trace工具

在sql执行过程中，在明确表明使用索引的时候，有的sql走索引，有的不会走，因为mysql优化器会判断走索引的性能比不走索引还要差，判断依据就是trace工具给出的结论。

SET SESSION optimizer_trace="enabled=on",end_markers_in_json=ON;--开启trace
SELECT * FROM sys_user WHERE NAME > 'a' ORDER BY id;--执行查询
SELECT * FROM information_schema.OPTIMIZER_TRACE;--获得trace的分析结果

{
  "steps": [
    {
      "join_preparation": {--阶段·：进入准备阶段
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `sys_user`.`id` AS `id`,`sys_user`.`username` AS `username`,`sys_user`.`password` AS `password`,`sys_user`.`name` AS `name`,`sys_user`.`phone` AS `phone`,`sys_user`.`head_url` AS `head_url`,`sys_user`.`dept_id` AS `dept_id`,`sys_user`.`post_id` AS `post_id`,`sys_user`.`description` AS `description`,`sys_user`.`status` AS `status`,`sys_user`.`create_time` AS `create_time`,`sys_user`.`update_time` AS `update_time`,`sys_user`.`is_deleted` AS `is_deleted` from `sys_user` where (`sys_user`.`name` > 'a') order by `sys_user`.`id` limit 0,1000"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {--阶段2：开始优化处理
        "select#": 1,
        "steps": [
          {
            "condition_processing": {--条件判断
              "condition": "WHERE",
              "original_condition": "(`sys_user`.`name` > 'a')",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(`sys_user`.`name` > 'a')"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(`sys_user`.`name` > 'a')"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(`sys_user`.`name` > 'a')"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [--表依赖详情
              {
                "table": "`sys_user`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [
              {
                "table": "`sys_user`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 3,
                    "cost": 2.65
                  } /* table_scan */,
                  "potential_range_indexes": [--可能使用到的索引
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_username",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_name_phone_status",
                      "usable": true,
                      "key_parts": [
                        "name",
                        "phone",
                        "status",
                        "id"
                      ] /* key_parts */
                    }
                  ] /* potential_range_indexes */,
                  "setup_range_conditions": [
                  ] /* setup_range_conditions */,
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  } /* group_index_range */,
                  "skip_scan_range": {
                    "potential_skip_scan_indexes": [
                      {
                        "index": "idx_name_phone_status",
                        "usable": false,
                        "cause": "query_references_nonkey_column"
                      }
                    ] /* potential_skip_scan_indexes */
                  } /* skip_scan_range */,
                  "analyzing_range_alternatives": {--分析各个索引使用的成本
                    "range_scan_alternatives": [
                      {
                        "index": "idx_name_phone_status",
                        "ranges": [
                          "'a' < name"
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,--是否使用了覆盖索引
                        "in_memory": 1,
                        "rows": 3,--索引需要查询的行数
                        "cost": 1.31,--索引的许花费的时间
                        "chosen": true--是否选择这个索引
                      }
                    ] /* range_scan_alternatives */,
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    } /* analyzing_roworder_intersect */
                  } /* analyzing_range_alternatives */,
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "idx_name_phone_status",
                      "rows": 3,
                      "ranges": [
                        "'a' < name"
                      ] /* ranges */
                    } /* range_access_plan */,
                    "rows_for_plan": 3,
                    "cost_for_plan": 1.31,
                    "chosen": true
                  } /* chosen_range_access_summary */
                } /* range_analysis */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [--可以考虑的执行计划
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`sys_user`",
                "best_access_path": {--最优访问路径
                  "considered_access_paths": [--最后选择的执行路径
                    {
                      "rows_to_scan": 3,
                      "filtering_effect": [
                      ] /* filtering_effect */,
                      "final_filtering_effect": 1,
                      "access_type": "range",
                      "range_details": {
                        "used_index": "idx_name_phone_status"
                      } /* range_details */,
                      "resulting_rows": 3,
                      "cost": 1.61,
                      "chosen": true,
                      "use_tmp_table": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 3,
                "cost_for_plan": 1.61,
                "sort_cost": 3,
                "new_cost_for_plan": 4.61,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "(`sys_user`.`name` > 'a')",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`sys_user`",
                  "attached": "(`sys_user`.`name` > 'a')"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "optimizing_distinct_group_by_order_by": {
              "simplifying_order_by": {
                "original_clause": "`sys_user`.`id`",
                "items": [
                  {
                    "item": "`sys_user`.`id`"
                  }
                ] /* items */,
                "resulting_clause_is_simple": true,
                "resulting_clause": "`sys_user`.`id`"
              } /* simplifying_order_by */
            } /* optimizing_distinct_group_by_order_by */
          },
          {
            "reconsidering_access_paths_for_index_ordering": {
              "clause": "ORDER BY",
              "steps": [
              ] /* steps */,
              "index_order_summary": {
                "table": "`sys_user`",
                "index_provides_order": true,
                "order_direction": "asc",
                "index": "PRIMARY",
                "plan_changed": true,
                "access_type": "index"
              } /* index_order_summary */
            } /* reconsidering_access_paths_for_index_ordering */
          },
          {
            "finalizing_table_conditions": [
              {
                "table": "`sys_user`",
                "original_table_condition": "(`sys_user`.`name` > 'a')",
                "final_table_condition   ": "(`sys_user`.`name` > 'a')"
              }
            ] /* finalizing_table_conditions */
          },
          {
            "refine_plan": [
              {
                "table": "`sys_user`"
              }
            ] /* refine_plan */
          },
          {
            "considering_tmp_tables": [
            ] /* considering_tmp_tables */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}

order by的优化

问题：在排序场景中很容易出现文件排序的问题，文件排序会对性能造成影响，所以需要优化

手段：

• 如果排序的字段创建了联合索引，在保证业务不冲突的情况下，遵循最左前缀法则来写排序语句
• 如果文件排序无法避免，尽量想办法使用覆盖索引 all-->index

分页优化

对于这样的分页查询，MySQL会把10010条数据全部取出，然后舍弃掉前10000条数据

explain select * from sys_role limit 10000,10;

如果主键连续的情况下，可以把主键作为查询条件，这种情况很少见

explain select * from sys_role where id < 10000 limit 10;

主键不连续的情况下

explain select * from sys_role order by name limit 10000,10;
--通过先进行覆盖索引的查找，然后用join进行连接查询获取所有数据，这样比全表扫描要快
explain select * from sys_role a inner join 
(select id from sys_role order by name limit 10000,10) b on a.id = b.id';

索引设计原则

一、代码先行，索引后上

二、联合索引尽量覆盖条件

就是可以创建两到三个联合索引，让每个联合索引都尽量包含sql语句中where、order by 、group by的字段，还要确保这些联合索引的字段顺序尽量满足最左前缀法则

三、不要在最小基数上创建索引

如genda字段，只有男女两种值，他的基数就是2

四、长字符串考虑前缀索引

如name varchar(255) 这种大字段建立索引会占用更多的空间，所以优化思路就是取该字段的前20位创建索引

create index idx_role_name_code on sys_role (role_name(20),role_code);

这样做order by name 就不能用了，如果优先优化order by 的情况下，不能考虑前缀索引

五、where 与 order by 冲突时，优先选where

在order by 与 where 出现索引设计冲突时，优先针对where条件进行索引设计，因为where条件使用索引可以很快的筛选出一部分数据，然后再对这些数据进行排序成本会更低一点

六、基于慢查询sql做优化

在slaver开启慢查询监控，根据监控到的慢查询sql创建合适的索引，进行优化

查看是否开启慢查询监控

SHOW VARIABLES LIKE 'slow_query_log';

慢查询日志文件名称

SHOW VARIABLES LIKE 'slow_query_log_file';

慢查询时间（多久没有查到结果才是慢查询，默认10s，开发中一个接口3s没有返回结果就超时了，可以设置为2）
SHOW VARIABLES LIKE 'long_query_time';

锁的定义和分类

锁的定义

锁是用来解决多个并发任务在访问同一共享资源时带来的数据安全问题。虽然使用锁解决了数据安全问题，但是会带来性能上的影响，频繁使用锁会使性能变得很差。

对于数据库管理软件mysql来说，必然会出现并发访问的问题，那么mysql是怎么在数据安全和性能上做权衡的？--mvcc设计思想

锁的分类

1. 从性能上划分为：乐观锁和悲观锁

1. 1. 悲观锁：悲观的认为当前的并发是非常严重的，所以在任何时候操作都是互斥的（读写都上锁），保证的线程安全，但是牺牲了并发性。
   2. 乐观锁：乐观的认为当前的并发并不严重，因此在读的时候不上锁，只有在写的时候才会上锁。--以CAS自旋锁为例，这时候某些情况下性能是ok的，但是频繁自旋会消耗一定的资源。

1. 从数据操作的粒度划分为：表锁和行锁

1. 1. 表锁：对整张表上锁
   2. 行锁：对表中的一行数据进行上锁

1. 从对数据库的操作类型上划分：读锁和写锁--这两种锁都属于悲观锁

1. 1. 读锁（共享锁）：对于同一行数据进行读操作，可以同时进行， 但是写操作不行
   2. 写锁（排它锁）：在上了写锁之后，及释放写锁之前，是不能进行任何其他操作的（其他任务不能对数据进行读写操作）

表锁

对整张表进行上锁。MyISAM存储引擎是天然支持表锁的，也就是说MYISAM存储引擎的表中出现并发情况，就会出现表锁的效果。MYISAM不支持事务。

给Innodb上一下表锁

#对一张表上读锁、写锁的格式
lock table 表名 read/write；
#举例
lock table sys_role read;
#查看当前回话对所有表的上锁情况
show open tables;
#释放当前回话的所有锁
unlock tables；

读锁：可以进行读，但是不能进行写

写锁：不能进行读写操作

行锁

MyISAM只支持表锁，Innodb支持表锁和行锁

在并发事务里，每个事务的增删改操作相当于上了行锁

上行锁的方式

#方式一
update sys_role set name = '测试角色11111' where id = 4；--对id为4的数据上了行锁
#方式二
select * from sys_role where id = 8 for update;--对id为8的数据上行锁

死锁

事务A和事务B互相持有对方需要的锁而不释放，导致双方资源无法获取，进入死锁状态

间隙锁

行锁只能对一行进行上锁，要对一个范围内的数据进行上锁，就需要使用间隙锁。比如上锁的条件是where id > 13 and id < 19; 就会对13和19所处的间隙进行上锁。

MVCC设计思想

MySQL为了权衡数据安全和并发性能，使用了MVCC多版本并发控制的设计

事务的特性

• 原子性：事务是一个最小的操作单元。一个事务中的多条sql，要么同时成功，要么同时失败

• • 有undo log日志实现

• 一致性：事务提交之前和回滚之后的数据是一致的。

• • 由其他三个特性及业务逻辑实现，是事务的最终目的

• 隔离性：多个事务在并发访问下，提供了一套隔离机制，不同的隔离级别有不同的并发效果。

• • 由各种锁及MVCC实现

• 持久性：事务一旦提交，对数据库的改变就是永久的。

• • 由redo log日志实现

事务的隔离级别

• read uncommitted（读未提交）：在一个事务中可以读取到另一个事务没有提交的数据（脏读）
• read committed（读已提交）：已经解决了脏读的问题，一个事务只能读取另一个事务提交过的数据，会出现不可重复读的问题。就是一个事务中重复读取数据的内容不一样。
• repeatable read（可重复读）：在一个事务中，两次读取的数据是一样的，不会出现脏读和不可重复读，可能会出现幻读的问题

• • 什么是幻读：在事务A中插入一条id为22的数据并提交，在事务B中进行同样的插入操作，提示主键重复，但是在事务B中查询并没有id为22的数据，就像出现了幻觉
  • 解决幻读的方法：通过加行锁

• serializable（串行化）：不允许事务的并发发生，相当于加了表锁。性能特别差

MVCC思想解读

在MySQL读和写操作中，对读的性能做了并发性保障，让所有的读都是快照读。在写的时候进行版本控制，如果真实版本比快照版本新，就要在写之前更新快照版本，这样既可以保证读操作的并发性，也可以保证写操作时数据安全。

MySQL 日志

在MySQL的InnoDB存储引擎中，日志文件是确保数据一致性和实现故障恢复的关键组件。以下是undo log、redo log和binlog三种日志的作用：

Undo Log（回滚日志）：

作用：undo log用于记录数据的修改前的状态，主要用于事务的回滚（rollback）和MVCC（多版本并发控制）。

事务回滚：当一个事务需要回滚时，undo log提供了原始数据的副本，使得事务可以恢复到事务开始前的状态。

MVCC：在MySQL的MVCC实现中，undo log保存了行的旧版本，使得在读取数据时，即使其他事务修改了数据，也可以根据undo log看到该事务开始时的数据版本。

大小：undo log的大小是预先分配的，并且是循环使用的。

Redo Log（重做日志）：

作用：redo log用于记录数据修改后的状态，主要用于事务的持久性和故障恢复。

持久性：确保事务的数据修改即使在数据库发生故障后也能被恢复，保证事务的ACID特性中的持久性（Durability）。

崩溃恢复：在数据库或服务器崩溃后，redo log可以被用来重做未提交事务的修改，确保数据的完整性。

结构：redo log由两个或多个日志文件组成，采用循环写入的方式，并且是顺序写入，这使得redo log的写入性能非常高。

Binlog（二进制日志）：

作用：binlog用于记录数据库执行的所有修改数据的操作，主要用于复制和数据恢复。

主从复制：在MySQL的主从复制中，binlog是主服务器上的数据变更记录，从服务器通过读取binlog来复制主服务器的数据变更。

数据恢复：binlog可以用于数据的恢复操作，尤其是在没有使用redo log的情况下，binlog可能是恢复数据的唯一途径。

类型：binlog有三种格式：STATEMENT、ROW和MIXED，分别记录了不同级别的操作信息。

大小：binlog是不断增长的，因为它记录了所有的修改操作，直到被清理或归档。

这三种日志在InnoDB存储引擎中扮演着不同的角色，共同确保了数据库的ACID特性和高可用性。undo log和redo log是InnoDB特有的，而binlog是MySQL服务器层面的日志。在实际应用中，这些日志文件通常都会被启用，以提供数据的完整性和一致性保障。

MySQL查询慢-除索引优化外的考虑角度

sql执行的过程：

连接管理模块会对连接（应用程序跟MySQL之间的连接）进行管理，应用程序会通过网络连接将应用程序中的sql语句发送给MySQL，MySQL收到SQL语句后会在分析器中先判断下SQL语句是否有语法错误，接下来是优化器会根据一定的规则选择索引，然后执行器会调用存储引擎（真正读写数据的地方）的接口函数，我们用的执行引擎一般都是Innodb，因为读取磁盘比较慢，所以Innodb内部加了一层缓存（Buffer Pool，里面既可以行数据，也放索引数据），查询sql到了Innodb中会根据优化器 里面计算到的索引去Buffer Pool中查询相应的索引页，如果索引页不在BufferPool里，会从磁盘中加载到BufferPool，再通过索引页查询得到数据页的位置，如果数据页不在BufferPool中，也从磁盘加载到BufferPool中，最后将查询到的查询结果返回给客户端。

除了索引之外，还有哪些因素会影响查询速度？

1. 1. 连接数过小，MySQL的连接管理模块作用是管理客户端与MySQL之间的长连接，如果两者之间只有一条长连接，那么在sql查询的时候就会阻塞等待结果返回，如果有大量并发请求，后面的请求都需要等待（所以有时候从应用程序日志看某条sql执行时间过长，但把sql单拎出来执行时间并不长只有几号秒） 解决方案：调大MySQL端跟客户端两边的连接数，MySQL默认是100，最大16384，可以通过 set global max_connections = 500;设置最大连接数；应用侧连接数设置如下：

spring:
  datasource:
    hikari:
      maximum-pool-size: 10 # 设置连接池最大连接数

客户端跟MySQL之间的连接是基于TCP协议的长连接，连接的建立比较耗时，会在客户端建立一个连接池，每次需要的时候会从连接池中拿出来用，用完再再回去，所以应用侧可以修改连接池的大小。

1. 1. BufferPool太小也会导致sql查询比较慢，BufferPool越大，可以缓存的数据也就越多，SQL查询时命中率也就越高。

查询BufferPool大小：SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

设置BufferPool大小：set global innodb_buffer_pool_size = 536870912;

查询BufferPool信息

SHOW STATUS LIKE 'Innodb_buffer_pool_%';

#BufferPool的缓存命中率：1-(996/27205)*100%

1-(Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests)*100%

#Innodb_buffer_pool_reads：读取磁盘次数 Innodb_buffer_pool_read_requests：查询bufferpool请求次数

正常情况下命中率会在99%以上，低于这个值可以考虑

SQL优化总结

根据SQL执行流程，可以从三个角度进行优化，1、索引优化，也是开发中最常用的，问题：如果一个查询SQL没有走索引，怎么优化？这个问题很宽泛，一定要先定位到没有走索引的原因，首先用explain命令查看SQL的执行计划，看下SQL是否有可能会走的索引，可能会走的索引是否是我们期望的，如果possible_key没有我们期望的索引，看下SQL是否写的有问题，比如：联合索引没有遵循最左前缀法则，又或者是否没有避免索引失效等等，然后看下Type列是否是range以上，如果SQL中有排序的话看下extra列是否走了文件排序，如果走了，也分下情况：用了联合索引，就遵循最左前缀法则，如果文件排序无法避免，就向覆盖索引优化，文件排序就是在内存中进行排序，分为单路排序和双路排序，单路排序是把查询到的结果集放在内存中排序，排序好的结果就是我们期望的，但是会占用较大的内存空间，双路排序又叫回表排序，是把id和排序字段放在内存中进行排序，然后根据id回表查询完整的数据再返回。对于分页优化只要是主键不连续的情况下，先进行覆盖索引的查找，然后用join连接查询获取所有的数据，这样比全表扫描要快。2、MySQL端和客户端连接数的设置也会影响SQL查询的性能，如果连接数过少，查询并发量特别大的话，就会导致后续查询处于阻塞状态，客户端的日志会显示SQL耗时过长。因此可以在MySQL服务器端设置合适的最大连接数，应用程序端设置合适的连接池大小。3、Innodb存储引擎是真正执行SQL的地方，Innodb内部有一个BufferPool缓存磁盘上的数据页和索引页，BufferPool中没有要查询的数据页和索引页，就会从磁盘中加载，然后返回。如果BufferPool过小，缓存命中率就会很低，根据缓存命中率（<99%的时候）调大BufferPool大小，增加命中率也可以提高查询性能。

回忆一下联合索引树，为什么联合索引要遵循最左前缀法则？仅针对Innodb存储引擎

1、索引的底层是B+树，B+树这种数据结构是一种平衡多叉树，其特点是一个节点可以存放多个数据，非叶子节点存放的是索引数据，叶子结点冗余所有数据，从左到右是按顺序存储的，且叶子结点之间有双向指针相连（支持范围查询），对于主键索引来说，叶子结点存放的是完整的数据，因此主键索引是聚簇索引（可以直接从索引树获取完整数据），而对于联合索引来说，叶子结点存放的是索引列及id，查询到索引列后再根据id查到聚簇索引树才能获得完整的数据，所以出主键索引外的其他索引又成为辅助索引。

2、联合索引的索引树的非叶子结点存放的是多个字段的索引，一个节点中只有查到第一个字段对应的子节点在索引树中才是有序的（可以理解为联合索引的索引树是按照第一个字段进行排序的，第一个字段排好序之后才会对后续字段排序），如果查询条件没有第一个字段，也就是联合索引最左边的字段，第二个字段乃至后面的字段都是无序的，也就不能通过索引树得到我们期望的结果，就会去全表扫描。

标签：数据,查询,sys,索引,MySQL,思路,优化,id,select
From： https://blog.csdn.net/qq_43583691/article/details/142360693