MySQL高级优化

2022/5/10 19:04:05

本文主要是介绍MySQL高级优化,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

1. MySQL_简介

  • MySQL是一个关系型数据库管理系统,由瑞典MySQL AB(创始人Michael Widenius)公司开发,2008被Sun收购(10亿美金),2009年Sun被Oracle收购。MariaDB
  • MariaDB基于事务的Maria存储引擎,替换了MySQL的MyISAM存储引擎; 它使用了Percona的 XtraDB代替了InnoDB存储引擎。
  • MySQL是一种关联数据库管理系统,将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。
  • MySQL是开源的,所以你不需要支付额外的费用。
  • MySQL是可以定制的,采用了GPL(GNU General Public License)协议,你可以修改源码来开发自己的MySQL系统。
  • MySQL支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
  • MySQL支持大型数据库,支持5000万条记录的数据仓库,32位系统表文件最大可支持4GB,64位系统支持最大的表文件为8TB。
  • MySQL使用标准的SQL数据语言形式。
  • MySQL可以允许于多个系统上,并且支持多种语言。这些编程语言包括C、C++、C#、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。

2. MySQL架构_逻辑架构

image

(1)连接层

最上层是一些客户端和连接服务,包含本地socket通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。

主要完成一些类似于连接处理、授权认证、及相关的安全方案。

在该层上引入了线程池的概念,为通过认证安全接入的客户端提供线程。

同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

(2)服务层

第二层架构主要完成大多数的核心服务功能,如SQL接口,并完成缓存的查询,SQL的分析和优化及部分内置函数的执行。

所有跨存储引擎的功能也在这一层实现,如过程、函数等。

在该层,服务器会解析查询并创建相应的内部解析树,并对其完成相应的优化:如确定查询表的顺序,是否利用索引等,最后生成相应的执行操作。

如果是select语句,服务器还会查询内部的缓存。如果缓存空间足够大,这样在解决大量读操作的环境中能够很好的提升系统的性能。

2.1 Management Serveices & Utilities: 系统管理和控制工具

2.2 SQL Interface: SQL接口

· 接受用户的SQL命令,并且返回用户需要查询的结果。比如select from就是调用SQL Interface

2.3 Parser: 解析器

· SQL命令传递到解析器的时候会被解析器验证和解析。

2.4 Optimizer: 查询优化器。

· SQL语句在查询之前会使用查询优化器对查询进行优化。
image

2.5 Cache和Buffer: 查询缓存。

· 如果查询缓存有命中的查询结果,查询语句就可以直接去查询缓存中取数据。

· 这个缓存机制是由一系列小缓存组成的。比如表缓存,记录缓存,key缓存,权限缓存等

(3)引擎层

存储引擎层,存储引擎真正的负责了MySQL中数据的存储和提取,服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同,这样我们可以根据自己的实际需要进行选取。后面介绍MyISAM和InnoDB

(4)存储层

数据存储层,主要是将数据存储在运行于裸设备的文件系统之上,并完成与存储引擎的交互。

(5)利用show profiles 查看sql的执行周期

了解查询语句底层执行的过程:查看是否开启计划。

修改配置文件/etc/my.cnf,先开启查询缓存

新增一行:query_cache_type=1

重启mysql:systemctl restart mysqld

再开启查询执行计划

show variables like '%profiling%';

set profiling=1;

执行语句两次:select * from mydb.mytbl where id=1 ;

显示最近执行的语句

show profiles;

显示执行计划

show profile cpu,block io for query 6;

执行编号7时,比执行编号6时少了很多信息,从下面截图中可以看出查询语句直接从缓存中获取数据;

注意:SQL必须是一致的,否则,不能命中缓存。

如果对数据库表进行 insert ,update ,delete 这个时候,缓存会失效!

如:select * from mydb.mytbl where id=2select * from mydb.mytbl where id>1 and id<3 虽然查询结果一致,但并没有命中缓存。

(6) 查询说明

mysql的查询流程大致是

  • 首先,mysql客户端通过协议与mysql服务器建连接,发送查询语句,先检查查询缓存,如果命中,直接返回结果,否则进行语句解析,也就是说,在解析查询之前,服务器会先访问查询缓存(query cache)——它存储SELECT语句以及相应的查询结果集
  • 如果某个查询结果已经位于缓存中,服务器就不会再对查询进行解析、优化、以及执行。它仅仅将缓存中的结果返回给用户即可,这将大大提高系统的性能。
  • 语法解析器和预处理:首先mysql通过关键字将SQL语句进行解析,并生成一颗对应的“解析树”
    mysql解析器将使用mysql语法规则验证和解析查询
  • 预处理器则根据一些mysql规则进一步检查解析树是否合法。
  • 查询优化器当解析树被认为是合法的了,并且由优化器将其转化成执行计划
  • 一条查询可以有很多种执行方式,最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。
    然后,mysql默认使用的B+TREE索引,并且一个大致方向是:无论怎么折腾sql,至少在目前来说,mysql只少用到表中的一个索引。
    image

3.数据结构

(1)数据结构分类

数据结构有很多种,一般来说,按照数据的逻辑结构对其进行简单的分类,包括线性结构和非线性结构两类。

线性结构:

•线性结构作为最常用的数据结构,其特点是数据元素之间存在一对一的线性关系。
•线性结构有两种不同的存储结构,即顺序存储结构和链式存储结构。
◦顺序存储的线性表称为顺序表,顺序表中的存储元素是连续的
◦链式存储的线性表称为链表,链表中的存储元素不一定是连续的,元素节点中存放数据元素以及相邻元素的地址信息
•线性结构常见的有:数组、链表、队列和栈。
image

非线性结构:

非线性结构包括:二维数组,多维数组,树结构,图结构
常见的算法时间复杂度由小到大依次为:O---理解为一个函数
Ο(1)<Ο(log2N)<Ο(n)<Ο(nlog2N)<Ο(n^2)<Ο(n^3)< Ο(n^k) <Ο(2^n)

image

image

MySQL的InnoDB引擎的存储方式也是B+树机制。

image

4. MySQL架构_存储引擎简介

查看mysql提供什么存储引擎:show engines;

(1)MyISAM和InnoDB 面试题

image

(2)我们手写sql的顺序

image

(3)机器读取顺序

image

FOJWGHSDOL(佛叫我干活速度上线(OL)):大型范围过滤,越靠前越好

5. 索引优化分析_预热_慢

image

(1).性能下降SQL慢 执行时间长 等待时间长

  • 数据过多——分库分表 mycat
  • 索引失效,没有充分利用到索引——索引建立
  • 关联查询太多join(设计缺陷或不得已的需求)——SQL优化
  • 服务器调优及各个参数设置(缓冲、线程数等)——调整my.cnf

6. MySQL7种JOIN

# t_emp表(以下称为A表)
CREATE TABLE `t_emp` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(20) DEFAULT NULL,
 `age` INT(3) DEFAULT NULL,
 `deptId` INT(11) DEFAULT NULL,
`empno` INT(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_dept_id` (`deptId`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

# t_dept表(一下称为B表)
CREATE TABLE `t_dept` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `deptName` VARCHAR(30) DEFAULT NULL,
 `address` VARCHAR(40) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

# 插入数据
INSERT INTO t_dept(id,deptName,address) VALUES(1,'华山','华山');
INSERT INTO t_dept(id,deptName,address) VALUES(2,'丐帮','洛阳');
INSERT INTO t_dept(id,deptName,address) VALUES(3,'峨眉','峨眉山');
INSERT INTO t_dept(id,deptName,address) VALUES(4,'武当','武当山');
INSERT INTO t_dept(id,deptName,address) VALUES(5,'明教','光明顶');
INSERT INTO t_dept(id,deptName,address) VALUES(6,'少林','少林寺');

INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(1,'风清扬',90,1,100001);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(2,'岳不群',50,1,100002);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(3,'令狐冲',24,1,100003);

INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(4,'洪七公',70,2,100004);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(5,'乔峰',35,2,100005);

INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(6,'灭绝师太',70,3,100006);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(7,'周芷若',20,3,100007);

INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(8,'张三丰',100,4,100008);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(9,'张无忌',25,5,100009);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(10,'韦小宝',18,NULL,100010);

(1) A、B两表共有(查询所有有部门的员工->员工和部门之间必须存在关联的数据)

image

SELECT a.*,b.* FROM t_emp a INNER JOIN t_dept b ON a.deptId = b.id;

image

(2) A、B两表共有+A的独有(列出所有用户,并显示其机构信息)A的全集

image

SELECT a.*,b.* FROM t_emp a LEFT JOIN t_dept b ON a.deptId = b.id;

image

(3) A、B两表共有+B的独有(列出所有部门,并显示其部门的员工信息 )B的全集

image

SELECT a.*,b.* FROM t_emp a RIGHT JOIN t_dept b ON a.deptId = b.id;
# 或者下面写法
SELECT a.*,b.* FROM t_dept b LEFT JOIN t_emp a ON b.id=a.deptId;

image

(4) A的独有 (查询没有加入任何部门的员工)

image

SELECT a.*,b.* FROM t_emp a LEFT JOIN t_dept b ON a.deptId=b.id WHERE b.id is null

image

(5) B的独有(查询没有任何员工的部门)

image

SELECT a.*,b.* FROM t_emp a RIGHT JOIN t_dept b ON a.deptId=b.id WHERE a.deptId is null

image

(6) AB全有(查询所有员工和所有部门),MySQL不支持全外连接,所以我们只能使用union(去重排序拼接,效率较低)|union all(无脑拼接,效率高) 进行SQL拼接了;(UNION在使用时,两张表的字段保证一致,如果不一致,请在slect后面列选字段,不要使用*)

注意:使用union或者union all时,两张拼接的结果集必须要结构一模一样
image

SELECT a.*,b.* FROM t_emp a LEFT JOIN t_dept b ON a.deptId = b.id
UNION
SELECT a.*,b.* FROM t_emp a RIGHT JOIN t_dept b ON a.deptId = b.id;

image

SELECT a.*,b.* FROM t_emp a LEFT JOIN t_dept b ON a.deptId = b.id
UNION ALL
SELECT a.*,b.* FROM t_emp a RIGHT JOIN t_dept b ON a.deptId = b.id;

image

注意上图的unionunion all的区别(面试题喔~)

(7) A的独有+B的独有(查询没有加入任何部门的员工,以及查询出部门下没有任何员工的部门)

image

# A的独有 (查询没有加入任何部门的员工)
SELECT a.*,b.* FROM t_emp a LEFT JOIN t_dept b ON a.deptId=b.id WHERE b.id is null
union

# B的独有(查询没有任何员工的部门)
SELECT a.*,b.* FROM t_emp a RIGHT JOIN t_dept b ON a.deptId=b.id WHERE a.deptId is null

image

7. 索引优化分析

(1)是什么?

MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。
索引的本质:索引是数据结构。可以简单理解为“排好序的快速查找数据结构”。
image

MySQL索引

image

主键索引号就是聚簇索引,非主键索引就是非聚簇索引
  • 聚簇索引的好处:
    • 按照聚簇索引排列顺序,查询显示一定范围数据的时候,由于数据都是紧密相连,数据库不用从多个数据块中提取数据,所以节省了大量的io操作。
  • 聚簇索引的限制:
    • 对于MySQL数据库目前只有innodb数据引擎支持聚簇索引,而Myisam并不支持聚簇索引。
    • 由于数据物理存储排序方式只能有一种,所以每个MySQL的表只能有一个聚簇索引。一般情况下就是该表的主键。

为了充分利用聚簇索引的聚簇的特性,所以innodb表的主键列尽量选用有序的顺序id,而不建议用无序的id,比如uuid这种。

(2)能干嘛?

  • MySQL索引分类
    • 单值索引:即一个索引只包含单个列,一个表可以有多个单列索引
    • 唯一索引:索引列的值必须唯一,但允许有空值
    • 主键索引:设定为主键后数据库会自动建立索引,innodb为聚簇索引
    • 复合索引:即一个索引包含多个列

(3)怎么玩?

索引的基本语法

  • 1.创建
    • CREATE [UNIQUE ] INDEX indexName ON mytable(column name(length));
    • length : 默认可以不用写
    • 如果是CHAR,VARCHAR类型,length可以小于字段实际长度; 如果是BLOB和TEXT类型,必须指定length。
    • ALTER mytable ADD [UNIQUE ] INDEX [indexName] ON (columnname(length))
  • 2.删除
    • DROP INDEX [indexName] ON mytable;
  • 3.查看
    • SHOW INDEX FROM table_name
  • 4.使用ALTER命令
    • 有四种方式来添加数据表的索引:
      • ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL。
      • ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次) 。
      • ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引,索引值可出现多次。
      • ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ,用于全文索引

哪些情况需要创建索引

  • 1)主键自动建立唯一索引
  • 2)频繁作为查询条件的字段应该创建索引
  • 3)查询中与其它表关联的字段,外键关系建立索引
  • 4)单键/组合索引的选择问题,who?(在高并发下倾向创建组合索引)
  • 5)查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度
  • 6)查询中统计或者分组字段
思考:排序和分组哪个更伤性能? 1000以上建索引。

分组 = 排序 + 去重 ,也就是说,真正的分组其实就是先将数据排序,然后再看根据distinct进行去重. 所以分组比排序更消耗性能

哪些情况不要创建索引

  • 1)表记录太少
    300万数据时MySQL性能就开始下降了,这时就可以开始开始优化了
  • 2)经常增删改的表
    提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。
    因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件
  • 3)where条件里用不到的字段不创建索引

where 后面带的字段,一定考虑建索引:

t_order:对比业务思考,订单业务大量的where都是使用orderNum,为他建索引,偶尔的业务使用username,username不用建
大数据量表某(多)个字段经常被用来当做查询条件,就一定要建立索引

8.索引优化分析_explain查看执行计划

Explain

是什么?

  • 一句话:查看执行计划
  • 使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是 如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈
  • 官网介绍 https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

能干嘛?

  • 表的读取顺序
  • 数据读取操作的操作类型
  • 哪些索引可以使用
  • 哪些索引被实际使用
  • 表之间的引用
  • 每张表有多少行被优化器查询

怎么玩?

EXPLAIN SELECT * FROM t_emp;

image

索引优化分析_explain_各字段解释

id★

  • 1.select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序

  • 2.三种情况

    • 1)id相同,执行顺序由上至下
      Explain select * from t1,t2,t3;

    • 2)id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行 
      explain select t1.id from t1 where t1.id = (select t2.id from t2 where t2.id = (select t3.id from t3 where t3.content='t3_897'));

  • id相同,不同,同时存在;

  • id如果相同,可以认为是一组,从上往下顺序执行;

  • 在所有组中,id值越大,优先级越高,越先执行;

  • 关注点:id号每个号码,表示一趟独立的查询。一个sql的查询趟数越少越好。

select_type

  • 1.有哪些
    image

  • 2.查询的类型,主要是用于区别 普通查询、联合查询、子查询等的复杂查询

    • 1)SIMPLE
      简单的 select 查询,查询中不包含子查询或者UNION
      EXPLAIN SELECT * FROM t1;

    • 2)PRIMARY
      查询中若包含任何复杂的子部分,最外层查询则被标记为primary
      EXPLAIN select t1.id from t1 where t1.id = (select t2.id from t2 where t2.id = (select t3.id from t3 where t3.content='t3_897'));

    • 3)SUBQUERY
      在SELECT或WHERE列表中包含了子查询

    • 4)DEPENDENT SUBQUERY
      在SELECT或WHERE列表中包含了子查询,子查询基于外层
      EXPLAIN SELECT * FROM t3 WHERE id = ( SELECT id FROM t2 WHERE content = t3.content);

    • 5)UNCACHEABLE SUBQUREY
      表示这个subquery的查询要受到外部系统变量的影响
      EXPLAIN SELECT * FROM t3 WHERE id = ( SELECT id FROM t2 WHERE content = @@character_set_server);

    • 6)UNION
      若第二个SELECT出现在UNION之后,则被标记为UNION; 若UNION包含在FROM子句的子查询中,外层SELECT将被标记为:DERIVED
      EXPLAIN SELECT * FROM (SELECT * FROM t1 UNION SELECT * FROM t2) aa;

    • 7)UNION RESULT
      从UNION表获取结果的SELECT

table

显示这一行的数据是关于哪张表的

partitions

代表分区表中的命中情况,非分区表,该项为null
https://dev.mysql.com/doc/refman/5.7/en/alter-table-partition-operations.html

type★

  • 1.访问类型排列
    type显示的是访问类型,是较为重要的一个指标,结果值从最好到最坏依次是:
    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
    一般来说,得保证查询至少达到range级别,最好能达到ref。
  • 2.类型介绍

1.system

表仅有一行记录,必须是系统表,这是const类型的特例,查询起来非常迅速。
explain SELECT * from mysql.proxies_priv WHERE User='root';

2.const

explain select * from t1 where id = 1;

表示通过索引一次就找到了,const用于primary key或者unique索引。
因为只匹配一行数据,所以很快 如将主键置于where列表中,MySQL就能将该查询转换为一个常量

3.eq_ref

explain select * from t1,t2 where t1.id = t2.id;

唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见主键或唯一索引扫描

4.ref *

create index idx_content on t1(content);
EXPLAIN SELECT * FROM t1, t2 WHERE t1.content = t2.content;

非唯一性索引扫描,返回匹配某个单独值的所有行. 本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而, 它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体

5.range *

explain select * from t2 where id >1 and id <5;

  • 只检索给定范围的行,使用一个索引来选择行。
  • key 列显示使用了哪个索引 一般就是在你的where语句中出现了between、<、>、in等的查询 这种范围索引扫描比全表扫描要好,因为它只需要开始于索引的某一点,而结束语另一点,不用扫描全部索引。

6.Index

explain select id from t1;

  • 出现index是sql使用了索引但是没用通过索引进行过滤,一般是使用了覆盖索引或者是利用索引进行了排序分组 Full Index Scan,index与ALL区别为index类型只遍历索引树。这通常比ALL快,因为索引文件通常比数据文件小。也就是说虽然all和Index都是读全表,但index是从索引中读取的,而all是从硬盘中读的

7.all

explain select * from t2;

Full Table Scan,将遍历全表以找到匹配的行

possible_keys

显示可能应用在这张表中的索引,一个或多个。 查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用

key

实际使用的索引。如果为NULL,则没有使用索引

key_len ★

okey_len表示索引使用的字节数,根据这个值可以判断索引的使用情况,特别是在组合索引的时候,判断该索引有多少部分被使用到非常重要。值越大越好。不损失精度情况下key_len越小 索引效果越好
EXPLAIN SELECT SQL_NO_CACHE * FROM t_emp WHERE t_emp.age=30 AND t_emp.name LIKE 'ab%';
create index idx_age_name on t_emp (age,name);

如何计算

  • 第一组
    key_len=age的字节长度+name的字节长度=(4+1) + ( 20*3+2+1) = 5+63 = 68
  • 第二组
    key_len=age的字节长度=4+1=5

key_len的长度计算公式:
varchr(10)变长字段且允许NULL    =  10 * ( character set:utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)
varchr(10)变长字段且不允许NULL =  10 * ( character set:utf8=3,gbk=2,latin1=1)+2(变长字段)
char(10)固定字段且允许NULL        =  10 * ( character set:utf8=3,gbk=2,latin1=1)+1(NULL)
char(10)固定字段且不允许NULL    =  10 * ( character set:utf8=3,gbk=2,latin1=1)

ref

显示将哪些列或常量与键列中命名的索引进行比较,以从表中选择行。
explain select * from t1,t2 where t1.id = t2.id;

rows★

rows列显示MySQL认为它执行查询时必须检查的行数。值越小越好

filtered

这个字段表示存储引擎返回的数据在mysql server层过滤后,剩下多少满足查询的记录数量的比例,注意是百分比,不是具体记录数

Extra★

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

1.Using filesort  *

出现filesort的情况:order by 没有用上索引。

优化后(给deptno和ename字段建立复合索引),去掉filesort
create index idx_deptno_ename on emp (deptno,ename);
查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度 说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。 MySQL中无法利用索引完成的排序操作称为“文件排序”

2.Using temporary *

出现Using temporary情况:分组没有用上索引。产生临时表。注意:分组操作是需要先排序后分组的。所以,也会出现Using filesort。
优化前存在 using temporary 和 using filesort

优化后(给deptno和ename建立复合索引)去掉using temporary 和 using filesort,性能发生明显变化:
create index idx_deptno_ename on emp (deptno,ename);

使用了临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序order by 和分组查询group by。

3.Using index *

表示使用了覆盖索引 [content是一个索引]
如果同时出现using where,表明索引被用来执行索引键值的查找;

如果没有同时出现using where,表明索引用来读取数据而非执行查找动作。

4.Using where

o表明使用了where过滤!

5.using join buffer *

如果有它则表明关联字段没有使用索引!
使用了连接缓存

6.impossible where

where 后面筛选条件有错误!

9.查询优化建议口诀:

全值匹配我最爱 最左前缀要遵守
带头大哥不能死 中间兄弟不能断
索引列上少计算 范围之后全失效
LIKE百分写最右 覆盖索引不写*
不等空值还有OR 索引影响要注意
VAR引号不可丢 SQL优化有诀窍

单表查询一般性建议

  • 对于单键索引,尽量选择过滤性更好的索引(例如:手机号,邮件,身份证)
  • 在选择组合索引的时候,过滤性最好的字段在索引字段顺序中,位置越靠前越好。
  • 选择组合索引时,尽量包含where中更多字段的索引
  • 组合索引出现范围查询时,尽量把这个字段放在索引次序的最后面
  • 尽量避免造成索引失效的情况

连表查询一般性建议

  • 保证被驱动表的join字段被索引
  • left join 时,选择小表作为驱动表,大表作为被驱动表
    • 1.驱动表用小表
  • inner join 时,mysql会自动将小结果集的表选为驱动表。选择相信mysql优化策略。
  • 能够直接多表关联的尽量直接关联,不用子查询
    • 总结: 驱动表选小表,索引建在被驱动

查询优化_子查询优化一般性建议

  • NOT IN -->LEFT JOIN xxx ON xx WHERE xx IS NULL
  • 尽量不要使用not in 或者 not exists

查询优化_排序、分组优化一般性建议

无过滤 不索引
顺序错 必排序
方向反 必排序
LIKE百分写最右 覆盖索引不写*
不等空值还有OR 索引影响要注意
VAR引号不可丢 SQL优化有诀窍
  • 当【范围条件】和【group by 或者 order by】的字段出现二选一时,优先观察条件字段的过滤数量,如果过滤的数据足够多,而需要排序的数据并不多时,优先把索引放在范围字段上。反之,亦然。
GROUP BY关键字优化
  • group by 先排序再分组,遵照索引建的最佳左前缀法则
  • 当无法使用索引列,增大max_length_for_sort_data和sort_buffer_size参数的设置
    • where高于having,能写在where限定的条件就不要写在having中了
  • group by没有过滤条件,也可以用上索引。Order By 必须有过滤条件才能使用上索引。

查询优化_覆盖索引优化

  • 简单说就是,select 到 from 之间查询的列使用了索引!
    • select * from tName 不建议使用*
  • 未使用覆盖索引之前:
    • explain select sql_no_cache * from emp where name like '%abc';
    • 减少回表次数
    • 禁止使用select *
    • 禁止查询与业务无关字段
    • 尽量使用覆盖索引

10.查询截取分析_慢查询日志

是什么?

  • MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中

怎么玩?

  • 默认情况下,MySQL数据库没有开启慢查询日志,需要我们手动来设置这个参数。
  • 当然,如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件
查看

SHOW VARIABLES LIKE '%slow_query_log%'; 默认情况下slow_query_log的值为OFF,表示慢查询日志是禁用的
image

开启

set global slow_query_log=1; 只对窗口生效,重启服务失效

慢查询日志记录long_query_time时间

SHOW VARIABLES LIKE '%long_query_time%';
SHOW GLOBAL VARIABLES LIKE 'long_query_time';
image

设置临时的sql慢查询时间SET SESSION long_query_time=0.1;

创建一个数据库,给数据库添加10w行数据
# 建一张表
 CREATE TABLE `dept` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `deptName` VARCHAR(30) DEFAULT NULL,
 `address` VARCHAR(40) DEFAULT NULL,
 `ceo` INT NULL ,
 PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

# 创建二个函数
DELIMITER $$
	CREATE FUNCTION  rand_num (from_num INT ,to_num INT) RETURNS INT(11)
	BEGIN   
	DECLARE i INT DEFAULT 0;  
	SET i = FLOOR(from_num +RAND()*(to_num - from_num+1))   ;
	RETURN i;  
END$$ 

DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN    
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO  
SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));  
SET i = i + 1;
END WHILE;
RETURN return_str;
END $$

# 编写一个存储过程
DELIMITER $$
CREATE PROCEDURE `insert_dept`(  max_num INT )
BEGIN  
DECLARE i INT DEFAULT 0;   
 SET autocommit = 0;    
 REPEAT  
 SET i = i + 1;  
 INSERT INTO dept ( deptname,address,ceo ) VALUES (rand_string(8),rand_string(10),rand_num(1,500000));  
 UNTIL i = max_num  
 END REPEAT;  
 COMMIT; 
END$$

# 调用存储过程添加数据


image

日志分析工具mysqldumpslow

2.查看mysqldumpslow的帮助信息
a)mysqldumpslow --help

  • -a: 将数字抽象成N,字符串抽象成S
  • -s: 是表示按照何种方式排序;
    • c: 访问次数
    • l: 锁定时间
    • r: 返回记录
    • t: 查询时间
    • al:平均锁定时间
    • ar:平均返回记录数
    • at:平均查询时间
  • -t: 即为返回前面多少条的数据;
  • -g: 后边搭配一个正则匹配模式,大小写不敏感的;

得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/localhost-slow.log
得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/localhost-slow.log
得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/localhost-slow.log
另外建议在使用这些命令时结合 | 和more 使用 ,否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/localhost-slow.log | more

11.View视图

是什么

  • 将一段查询sql封装为一个虚拟的表。
  • 这个虚拟表只保存了sql逻辑,不会保存任何查询结果。

作用

  • 封装复杂sql语句,提高复用性
  • 逻辑放在数据库上面,更新不需要发布程序,面对频繁的需求变更更灵活

适用场景

  • 共用查询结果
  • 报表

语法

  • 创建
    CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition
  • 使用
    • 查询
      select * from view_name
    • 更新
      CREATE OR REPLACE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition
    • 删除
      drop view view_name;

mysql 视图会随着表中的数据变化而动态变化!

12.主从复制

复制的基本原理

image

  • MySQL复制三步骤:
    • 1 master将写操作记录到二进制日志(binary log)。这些记录过程叫做二进制日志事件,binary log events;
    • 2 slave将master的binary log events拷贝到它的中继日志(relay log);从服务器I/O线程将主服务器的二进制日志读取过来记录到从服务器本地文件,然后SQL线程会读取relay-log日志的内容并应用到从服务器,从而使从服务器和主服务器的数据保持一致!
    • 3 slave重做中继日志中的事件,将改变应用到自己的数据库中。 MySQL复制是异步的且串行化的,而且重启后从接入点开始复制。
复制的最大问题(延时)

主从的集群搭建

1.我这里使用的是VMware虚拟机进行搭建,并且使用docker容器化技术搭建

docker pull mysql:5.7 先下载mysql镜像文件
image

2. 启动master和连个slave

image

docker run  -d  -p 3307:3306 \
-v /mysql01/data:/var/lib/mysql \
-v /mysql01/conf:/etc/mysql/conf.d \
-e MYSQL_ROOT_PASSWORD=123456 \
--name mysql01 --restart=always --privileged=true \
mysql:5.7

docker run  -d  -p 3308:3306 \
-v /mysql02/data:/var/lib/mysql \
-v /mysql02/conf:/etc/mysql/conf.d \
-e MYSQL_ROOT_PASSWORD=123456 \
--name mysql02 --restart=always --privileged=true \
mysql:5.7

docker run  -d  -p 3309:3306 \
-v /mysql03/data:/var/lib/mysql \
-v /mysql03/conf:/etc/mysql/conf.d \
-e MYSQL_ROOT_PASSWORD=123456 \
--name mysql03 --restart=always --privileged=true \
mysql:5.7
3.创建三个客户端连接

image

4.修改主节点的配置
# 创建一个base.cnf配置文件
vim base.cnf

[client]
default-character-set=utf8
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
5.修改从节点
[mysqld]
server-id=2
relay-log=mysql-relay
6.重启三个mysql

docker restart mysql01 mysql02 mysql03

7.在master主机上给两个slave分配两个账号密码,用户连接master进行主从同步数据

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123456';
flush privileges;

查看master状态

show master status;

8.两个从机执行命令,使用分配的账号密码进行数据同步
CHANGE MASTER TO MASTER_HOST='192.168.137.72',
MASTER_USER='slave',MASTER_PASSWORD='123456',MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=0;

START SLAVE;
9.查看状态

show slave status;
image

10.测试

image

主机创建一个数据库,刷新从机会发现从机也有了
image

创建表,添加记录,会发现从机也会立马同步过去的
image

这样我们的主从搭建完毕

至此MySQL高级相关内容整理完毕,关于乐观锁,悲观锁,行锁...等等锁相关问题,后续再继续更新



这篇关于MySQL高级优化的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!


扫一扫关注最新编程教程