Mysql性能调优

目录

一.Mysql架构设计

1.Mysql架构图       

二.Mysql常见的存储引擎

1.InnoDB引擎

2.MyISAM存储引擎

3.MEMORY存储引擎

三.Mysql索引

1.索引的分类

2.mysql中空值和null的区别

3.mysql中索引的查询和删除

4.索引的优缺点分析

5.索引的数据结构

6.Mysql的索引实现

如何避免回表？

四.Mysql性能调优

1.Mysql性能优化的思路

2.开启mysql的慢查询日志

3.MySQL性能分析 EXPLAIN

4.如何避免回表

5.索引优化案例

五.Mysql索引失效场景

---

一.Mysql架构设计

1.Mysql架构图       

        MySQL 架构可以分为 Server层 和 Engine层两部分，架构图如下

二.Mysql常见的存储引擎

1.InnoDB引擎

（1）概述

        InnoDB是事务型数据库的首选引擎。支持事务（ACID），支持行级锁，支持外键

（2）使用场景

        如果需要对事务的完整性要求比较高，或者需要频繁的更新、删除操作的数据库，都可以选择InnoDB。

（3）数据落盘

        使用 InnoDB存储引擎 MySQL将在数据目录下创建一个名为 ibdata1的10MB大小的自动扩展数据文件，以及两个名为 ib_logfile0和 ib_logfile1的5MB大小的日志文件。

2.MyISAM存储引擎

（1）概述

        MyISAM拥有较高的插入、查询速度，空间和内存使用比较低，但不支持事务。

（2）使用场景

        如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比 较低，也可以使用。

（3）数据落盘

        使用MyISAM引擎创建数据库，将产生3个文件。文件的名字以表名字开始，扩展名之处文件类型：frm文件存储表定义、数据文件的扩展名为.MYD（MYData）、索引文件的扩展名时.MYI（MYIndex）。

3.MEMORY存储引擎

（1）概述

        MEMORY存储引擎将所有的数据都在内存中，数据的处理速度快，但是安全性不高。

（2）使用场景

        如果需要很快的读写速度，对数据的安全性要求较低，可以选择MEMOEY。它对表的大小有要求，不能建立太大的表。所以，这类数据库只使用在相对较小的数据库表。

注意：同一个数据库也可以使用多种存储引擎的表。如果一个表要求比较高的事务处理，可以选择InnoDB。这个数据库中可以将查询要求比较高的表选择MyISAM存储。如果该数据库需要一个用于查询的临时表，可以选择MEMORY存储引擎。

三.Mysql索引

1.索引的分类

（1）主键索引

        设定为主键后，数据库自动建立索引，InnoDB为聚簇索引，主键索引列值不能为空（Null）。

# (1) 创建表添加主键索引
CREATE TABLE `table_name` (
    [...] ,
    PRIMARY KEY (`col_name`),
)
# (2) 添加主键索引
ALTER TABLE `table_name` ADD PRIMARY KEY (`col_name`);

（2）普通索引（单列索引）

        单列索引是最基本的索引，它没有任何限制。

#  (1) 直接创建索引
CREATE INDEX index_name ON table_name(`col_name`);
#  (2) 修改表结构的方式添加索引
ALTER TABLE `table_name` ADD INDEX index_name(`col_name`);
#  (3) 创建表的时候同时创建索引
CREATE TABLE `table_name` (
    [...] ,
    PRIMARY KEY (`id`),
    INDEX index_name (`col_name`)
)
#  (4) 删除索引
DROP INDEX index_name ON table_name;   
alter table `表名` drop index 索引名;

（3）复合索引

        复合索引是在多个字段上创建的索引。复合索引遵守“最左前缀”原则，即在查询条件中使用了复合索引的第一个字段，索引才会被使用。因此，在复合索引中索引列的顺序至关重要。

# （1）创建一个复合索引
create index index_name on table_name(`col_name1`,`col_name2`,...);
# （2）修改表结构的方式添加索引
alter table table_name add index index_name(`col_name1`,`col_name2`,...);

（4）唯一索引

        唯一索引和普通索引类似，主要的区别在于，唯一索引限制列的值必须唯一，但允许存在空值（只允许存在一条空值）。

-- （1）创建唯一索引
# 创建单个索引
CREATE UNIQUE INDEX index_name ON table_name(`col_name`);

# 创建多个索引
CREATE UNIQUE INDEX index_name on table_name(`col_name`,...);

-- （2）修改表结构
# 单个
ALTER TABLE table_name ADD UNIQUE index index_name(`col_name`);
# 多个
ALTER TABLE table_name ADD UNIQUE index index_name(`col_name`,...);

-- （3）创建表的时候直接指定索引
CREATE TABLE `table_name` (
    [...] ,
    PRIMARY KEY (`id`),
    UNIQUE index_name_unique(`col_name`)
)

2.mysql中空值和null的区别

“空值” 和”NULL”的概念： 
1：空值是不占用空间的 .
2: MySQL中的NULL其实是占用空间的.

长度验证：注意空值的之间是没有空格的。

> select length(''),length(null),length(' ');
+------------+--------------+-------------+
| length('') | length(null) | length(' ') |
+------------+--------------+-------------+
|          0 |         NULL |           1 |
+------------+--------------+-------------+

3.mysql中索引的查询和删除

#查看:
show indexes from `表名`;
#或
show keys from `表名`;
 
#删除
alter table `表名` drop index 索引名;

4.索引的优缺点分析

（1）优点

        大大提高数据查询速度

（2）缺点

        索引会占据磁盘空间

        降低更新表的效率

        维护索引需要消耗数据库资源

5.索引的数据结构

（1）索引数据结构类型

        MySQL索引使用的数据结构主要有BTree索引和hash索引

（2）Hash索引

        对于hash索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景建议选择BTree索引。

（3）BTree索引

        B+树可以保证等值和范围查询的快速查找，MySQL的索引就采用了B+树的数据结构。

6.Mysql的索引实现

（1）InnoDB索引

        <1> 聚簇索引

        聚簇索引 ，叶子节点存储的数据是整行记录。聚簇索引使用B+树构建，一般情况下，聚簇索引等同于主键索引，当一个表没有创建主键索引时，InnoDB会自动创建一个ROWID字段来构建聚簇索引。

        <2> 辅助索引

       辅助索引， 除聚簇索引之外的所有索引都称为辅助索引。InnoDB的辅助索引只会存储主键值而非磁盘地址。

如下查询age = 19的数据，需要根据辅助索引age查到 age= 19 对应的id，再根据id回表查询全部数据。

         以上回表的操作，在数据量比较大的时候，回表必然会消耗很多的时间影响性能。

如何避免回表？

        使用覆盖索引查询（创建联合索引，让创建的索引包含查询结果）

举例：创建一张user表

CREATE TABLE `user`
(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name`  int(11)     DEFAULT NULL,
  `sex`  char(3)     DEFAULT NULL,
  `address`  varchar(10) DEFAULT NULL,
  `hobby`  varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `i_name` (`name`)
) ENGINE = InnoDB;

-- 查询名字为张三的数据
select id,name,sex from user where name = 'zhangsan';

        这个语句在业务上频繁使用到，而user表中的其他字段使用频率远低于这几个字段，如果我们使用联合索引（name，sex），查询到的结果就包括了我们所需要的查询结果的所有字段的完整数据，这样就不需要再次回表查询去检索sex字段的数据了。

覆盖索引和联合索引的概念

        覆盖索引是指查询语句查询方式（查询的结果包含了联合索引，以此来避免回表）；

        联合索引是指索引类型；

（2）MyIsam索引

         <1> 主键索引

        MyISAM的数据文件和索引文件是分开存储的。MyISAM使用B+树构建索引树时，叶子节点中存储的键值为索引列的值，数据为索引所在行的磁盘地址。

        <2> 辅助索引

       在MyISAM存储引擎中，辅助索引和主键索引的结构是一样的，没有任何区别。 主键列索引的键值是唯一的，而辅助索引的键值是可以重复的。

四.Mysql性能调优

1.Mysql性能优化的思路

（1）首先需要使用慢查询功能，去获取所有查询时间比较长的SQL语句
（2）其次使用explain命令去查询由问题的SQL的执行计划
（3）最后可以使用show profile[s] 查看有问题的SQL的性能使用情况
（4）优化SQL语句

2.开启mysql的慢查询日志

（1）临时开启

set global slow_query_log=on;
set global long_query_time=1;

（2）永久开启

        修改/etc/my.cnf，添加以下内容，然后重启MySQL服务

[mysqld]
lower_case_table_names=1
slow_query_log=ON
#慢查询日志存放位置
slow_query_log_file=D:\dev\mysql-8.0.22-winx64\data\DESKTOP-LEC7QQM-slow.log
long_query_time=1

（3）查看是否开启慢查询

SHOW VARIABLES LIKE "%slow_query%" ;
结果字段说明：
slow_query_log：是否开启慢查询,on为开启,off为关闭;
slow_query_log_file：慢查询日志文件路径

 （4）慢查询日志分析

慢查询日志打开如下所示 

第1行：SQL查询执行的具体时间
第2行：执行SQL查询的连接信息，用户和连接IP
第3行：记录了一些我们比较有用的信息，
    Query_timme，这条SQL执行的时间，越长则越慢
    Lock_time，在MySQL服务器阶段(不是在存储引擎阶段)等待表锁时间
    Rows_sent，查询返回的行数
    Rows_examined，查询检查的行数，越长就越浪费时间
第4行：设置时间戳，没有实际意义，只是和第一行对应执行时间。
第5行：执行的SQL语句记录信息

3.MySQL性能分析 EXPLAIN

EXPLAIN字段介绍

（1）id

        select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序，id值越大优先级越高，越先被执行，id相同，执行顺序由上至下。

（2）select_type 

        查询类型，主要用于区别普通查询，联合查询，子查询等的复杂查询。

simple：简单的select查询，查询中不包含子查询或者UNION
primary :  表示包含子查询的语句（最外层）
subquery : 在select或where列表中包含的子查询
derived : 在from列表中包含的子查询被标记为derived（衍生），MySQL会递归执行这些子查询，把结果放到临时表中
union :  如果第二个select出现在UNION之后，则被标记为UNION，如果union包含在from子句的子查询中，外层select被标记为derived
union result : UNION 的结果

（3）table

        查询的表名

（4）type

        type显示的是连接类型，是较为重要的一个指标。（一般来说,需要保证查询至少达到 range级别,最好能到ref）

-- 从好到差依次为
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
-- 简化
system > const > eq_ref > ref > range > index > ALL

system: 表仅有一行 (等于系统表)。这是const连接类型的一个特例,很少出现。
const: 表示通过索引一次就找到了。
eq_ref: 唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配. 常见与主键或唯一索引扫描。
ref: 非唯一性索引扫描, 返回匹配某个单独值的所有行, 本质上也是一种索引访问, 它返回所有匹配某个单独值的行, 这是比较常见连接类型。
range: 只检索给定范围的行,使用一个索引来选择行。
index: 出现index 是 SQL 使用了索引, 但是没有通过索引进行过滤,一般是使用了索引进行排序分组。比如：EXPLAIN SELECT * FROM L1 ORDER BY id;
ALL：对于每个来自于先前的表的行组合,进行完整的表扫描。

（5）possible_keys 

        显示可能用到的索引

（6）key

        实际使用的索引，若为null，则没有使用到索引。（两种可能，1.没建立索引, 2.建立索引，但索引失效）。查询中若使用了覆盖索引，则该索引仅出现在key列表中。

（7）key_len

        表示索引中使用的字节数, 可以通过该列计算查询中使用索引的长度。key_len 字段能够帮你检查是否充分利用了索引。

ken_len 越长, 说明索引使用的越充分。

（8）ref 

        显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。

（9）rows 

        表示找到所需的记录所需要读取的行数，越少越好。

（10）filtered

        它指返回结果的行占需要读到的行(rows列的值)的百分比，越大越好。

（11）Extra

        Extra 是 EXPLAIN 输出中另外一个很重要的列，该列显示MySQL在查询过程中的一些详细信息。

Extra主要指标的含义(有时会同时出现)如下

using index：使用覆盖索引的时候就会出现
Using filesort：得到所需结果集，需要对所有记录进行文件排序。这类SQL语句性能极差，需要进行优化。（典型的，在一个没有建立索引的列上进行了order by，就会触发filesort，常见的优化方案是，在order by的列上添加索引，避免每次查询都全量排序。）
using where：在查找使用索引的情况下，需要回表去查询所需的数据
using index condition：查找使用了索引，但是需要回表查询数据
using index & using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据
Using temporary：说明需要建立临时表 (temporary table) 来暂存中间结果。
Using join buffer (Block Nested Loop) 说明，需要进行嵌套循环计算, 这里每个表都有五条记录，内外表查询的type都为ALL。
问题在于两个关联表join 使用 uname，关联字段均未建立索引，就会出现这种情况。
常见的优化方案是，在关联字段上添加索引，避免每次嵌套循环计算。

explain总结

当一个sql需要进行优化时，可以从一下几个方面进行考量：

表的读取顺序。（对应id）
数据读取操作的操作类型。（对应select_type）
哪些索引可以使用。（对应possible_keys）
哪些索引被实际使用。（对应key）
每张表有多少行被优化器查询。（对应rows）
评估sql的质量与效率 (对应type)

4.如何避免回表

使用覆盖索引查询（创建联合索引，让创建的索引包含查询结果）

5.索引优化案例

（1）驱动表

        多表关联查询时，第一个被处理的表就是驱动表，使用驱动表去关联其他表。

驱动表的确定非常的关键，会直接影响多表关联的顺序，也决定后续关联查询的性能。

驱动表选取规则：在对最终的结果集没有影响的前提下,优先选择结果集最小的那张表作为驱动表。

-- user表为驱动表,order表为被驱动表
select * from user t1 left join order t2 on t1.id = t2.user_id;
-- 转换成代码就是：
for(user表行 uRow : user表){
    for(Order表的行 oRow : order表){
        if(uRow.id = oRow.user_id){
            return uRow;
        }
    }
}

（2）join buffer

        MySQL 默认 buffer 大小 256K，如果有 n 个 join 操作，会生成 n-1 个 join buffer。

mysql> show variables like '%join_buffer%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+

mysql> set session join_buffer_size=262144;
Query OK, 0 rows affected (0.00 sec)

优化方案：

1.永远用小结果集驱动大结果集(其本质就是减少外层循环的数据数量)；
2.为匹配的条件增加索引(减少内层表的循环匹配次数)；
3.增大join buffer size的大小（一次缓存的数据越多，那么内层包的扫表次数就越少）；
4.减少不必要的字段查询（字段越少，join buffer 所缓存的数据就越多）。

（3）in和exists函数

        优化方案：in后面跟的是小表，exists后面跟的是大表。

（4）order by 优化

        尽量减少额外的排序，通过索引直接返回有序数据。

排序优化案例：

为 employee 表创建以下辅助索引，聚簇索引为id
-- 联合索引
ALTER TABLE employee ADD INDEX idx_name_age(NAME,age);

-- 为薪资字段添加索引
ALTER TABLE employee ADD INDEX idx_salary(salary);

场景1：只查询用于排序的 索引字段, 可以利用索引进行排序,最左原则

        查询 name, age 两个字段, 并使用 name 与 age 行排序

场景2：排序字段在多个索引中,无法使用索引排序

        查询 name , salary 字段, 并使用 name 与 salary 排序

场景3：只查询用于排序的索引字段和主键, 可以利用索引进行排序

        查询 id , name , 使用 name 排序

场景4：查询主键之外的没有添加索引的字段，不会利用索引排序

        查询 dep_id ,使用 name 进行排序

场景5：排序字段顺序与索引列顺序不一致,无法利用索引排序

        使用联合索引时, ORDER BY子句也要求, 排序字段顺序和联合索引列顺序匹配。

场景6：where 条件是 范围查询时, 会使order by 索引 失效

        添加一个条件 : age > 18 ,然后再根据 age 排序。

场景7：ORDERBY子句不要求必须索引中第一列,没有仍然可以利用索引排序。但是有个前提条件，只有在等值过滤时才可以，范围查询时不可以

 场景8：升降序不一致,无法利用索引排序

        name字段升序,age字段降序,索引失效

（5）count(*) 和 count(1)和count(列名)区别

        <1> 执行效果:
            count(*) 包括了所有的列,在统计时 不会忽略列值为null的数据.
            count(1) 用1表示代码行,在统计时,不会忽略列值为null的数据.
            count(列名)在统计时,会忽略列值为空的数据,就是说某个字段的值为null时不统计.
        <2> 执行效率:
            列名为主键, count(列名)会比count(1)快
            列名为不是主键, count(1)会比count(列名)快
            如果表没有主键,count(1)会比count(*)快
            如果表只有一个字段,则count(*) 最优

（6）datetime优化

        问题：为datetime类型字段创建索引，查询时需要转换为日期再匹配，需要查询出所有行进行过滤， 所以会导致索引失效。

        优化方案：改为使用 between ... and ... ，使索引生效。

EXPLAIN SELECT NAME,mobile FROM user_contacts  WHERE DATE_FORMAT(create_date,'%Y-%m-%d')='2017-02-16';

EXPLAIN SELECT NAME,mobile FROM user_contacts  WHERE create_date 
BETWEEN '2017-02-16 00:00:00' AND '2017-02-16 23:59:59';

 （7）分页优化

        需求：获取用户通讯录表第10万条数据开始后的100条数据

-- 查询记录量越来越大，所花费的时间也会越来越多
EXPLAIN SELECT * FROM user_contacts uc LIMIT 1000000,1000;

 优化方案：使用子查询优化，先查出索引的分页位置，再根据索引查询数据。

-- 根据获取到的id值向后查询.
EXPLAIN SELECT * FROM user_contacts WHERE id >=
(SELECT id FROM user_contacts LIMIT 100000,1) LIMIT 100;

五.Mysql索引失效场景

1.不满足最左前缀原则

2.select * （尽量使用覆盖索引）

3.索引列上有计算或者函数

4.like左边包含%（使用覆盖索引可以使左边加%也能走索引）

5.not in 和not exists 和 <> 和 is null 和 is not null

6.字段类型不同（比如字符串类型 不加引号）

7.范围查询右边的条件索引全部失效

8. or 连接