MySQL高级-SQL语言

一、MySQL的架构介绍

1、简介：

是关系型数据库管理系统，即RDBMS；

将数据保存在不同的表中，而不是将所有数据放在一个大仓库中，增加速度并提高灵活性；

支持大型数据库，支持5000万条记录的数据仓库，32位系统表文件最大可支持4GB，64位系统支持最大的表文件为8TB；（但实际300万就需要优化）

MySQL支持定制，采用了GPL协议，可以修改源代码来开发自己的MySQL系统；

2、高级MySQL(了解，dba的内容)：

（1）mysql内核：（2）sql优化工程师：（3）mysql服务器的优化：（4）各种参数常量设定

（5）查询语句优化（6）主从复制（7）软硬件升级（8）容灾备份（9）sql编程

3、下载安装：

（1）GA版本为：稳定版，企业更趋向；Beta版本：测试版，有最新的技术，但是不一定稳定

（2）选择linux平台的，企业一般后台服务器都是在linux上的；

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

（1）连接层：主要完成一些类似于连接处理、授权认证、及相关的安全方案。是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似tcp/ip的通信。这一层引入了“连接池”，为通过认证安全接入的客户端提供线程；也可以实现基于SSL的安全链接。服务器也会为每个客户端验证所具有的操作权限。

（2）服务层：主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化及部分内置函数的执行。所有的跨存储引擎的功能也在这一层实现，比如过程、函数等。

（3）引擎层：真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎各具特色。

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

5、MySQL存储引擎：

（1）查看命令：

             show  variables  like  ’%storage_engine%‘ #查看当前默认和当前的存储引擎

             show  engines;#可以提供的存储引擎

（2）MyISAM和InnoDB的对比 ：前者更适合读；

（3）阿里巴巴、淘宝现状：

         阿里大部分MySQL数据库是使用的percona的原型加以修改。Percona，提供了XtraDB存储引擎的包装器和其他分析工具，其中XtraDB可以完全代替innodb，并且在性能和并发上做的更好。

二、索引优化分析

（一）索引（排好序的快速查找数据结构）

1、定义：除了数据本身外，数据库还维护这一个满足特定查找算法的数据结构，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构称为索引；

一般来说，索引本身也很大，不可能全部存储在内存，因此往往以索引文件的形式存储到硬盘上。

2、索引的底层结构：

常见的有：B-tree（多路搜索树） ;B+tree（InnoDB默认的）,HASH等；

3、优点：

• 大大减小了服务器需要扫描的数据量，提高数据的检索速度，降低数据库的IO成本；
• 通过索引列多数据进行排序，降低数据排序的成本，降低了CPU的消耗；

4、缺点：

• 创建和维护索引需要耗费时间，时间与数据量呈正相关；
• 索引实际上也是一个表，保存了主键与索引速度，并指向实体表的记录，所以需要占物理空间
• 需要动态维护数据的增删改，降低了维护速度

5、创建索引时选择列的原则：

需要创建的情况：

• 作为主键的列上，会自动被创建；
• 经常被用在连接(john)的列上，即外键，可以加快连接的速度
• 经常需要排序的列
• 经常需要统计或者分组字段（分组必排序）
• 经常需要被搜索的列；
• 经常使用在where子句中的列
• 高并发下倾向创建复合索引；

不需要创建的情况：

• 表中记录太少（遍历更高效）
• 经常增删改的表
• 包含很多重复值的数据

6、索引分类：（一个表的索引最多不好超过5个）

（1）逻辑功能：

• 主键索引：一张表只能有一个； 主键自动创建
• 唯一索引:  一张表可以有多个，但数据必须唯一，允许有null；
• 普通索引：一张表可以有多个，允许数据重复；
• 全文索引：查找的是文本中的关键词；

（2）按照列数划分：

• 单值索引：即一个索引只包含单个列，一个表可以有多个；
• 复合索引：即一个索引包含多个列；

（3）物理分类：

• 聚簇索引：是为了提高某个属性的查询速度，将相同值的元组集中存放在连续的物理块中；比如B+结构
• 非聚簇索引：数据和索引是分开的；

7、基本语法：

（1）创建：两种方式

create [unique] index 索引名 on 表名（字段列表）

alter table 表名 add [unique] index [索引名]  (字段列表)
#常见的四种方式添加索引：
alter table 表名 add primary key(字段列表)；#添加一个主键，意味着索引值必须唯一且不能为null
alter table 表名 add unique [index] [索引名](字段列表)；#添加唯一索引，必须唯一，允许多个null
alter table 表名 add index [索引名](字段列表)；#添加普通索引，可以重复
alter table 表名 add fulltext [索引名](字段列表)；#添加全文索引

（2）删除：

drop index 索引名 on 表名；

（3）查看：

show index from 表名\G；

 

（二）索引优化分析

1、性能下降SQL慢（执行时间长、等待时间长）：

查询语句写的慢；索引失效（单值索引，复合索引）；关联查询太多join；服务器调优及各个参数设置（缓冲、线程）;

2、Join查询

（1）select查询语法：select ... from ... join ... on  ... where...group by.. having... order by ...

（2）select解析顺序：from(多个表的笛卡尔积)->on(主表保留原则)->John（如果指定是非内连接，将on过滤的那些也作为外来行加入）->where->group by->having->select->distinct->order by ->limit 

（3）Join图：A∩B； A；A-A∩B；B；B-A∩B; A∪B; A∪B-A∩B；

                    #  -A∩B的格式中带where  从表.key is null ；另，A∪B-A∩B中A.key is null or B.key is null;

（4） 建表SQL:

             语法：create table 表名( , , , )[engine=innodb auto_increment=1  default charset=utf8]

3、性能分析：

（1）MySQL查询优化器：由MySQL解析器模块分类出select转发为MySQL查询优化器，对语句进行简化和转换，并利用读取所涉及对象的统计信息来进行优化；

（2）MySQL常见瓶颈：CPU;IO;服务器硬件的性能瓶颈；

（3）Explain:可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句的，从而分析出查询语句或表结构的性能瓶颈；

• 用法：explain  SQL语句; 
• 执行后包含的信息：（绿色为重要的）

id :   select查询（可能包含了若干子查询和多个表）的序列号，包含了一组数字（也可以为null，比如union联合的最后结果），表示查询中执行select语句或操作表的顺序；

       有三种情况：一组数字中id值相同：按照显示的从上到下执行；（小表驱动大表）

                             一组数字中id值全不相同：id值越大的越先执行（子查询，id序号会递增）

                             一组数字中id不全相同：id值相同的为一组，从上往下执行；id值越大的组越先执行；

select_type:  常见的有值包括 simple(不包含子查询和union)，primary(主查询)，subquery(子查询), derived(是from后的子查询，被放在临时表中),union(第二个select出现在union后，标记为~),union result(从union表获取结果的select);

table： 显示这一行的数据时关于哪个表的；

type:  显示查询使用的类型，由好到坏依次为： system(单表单行)->const(通过索引一次就可以找到 ，比较主键索引或唯一索引只匹配一行数据)->eq_ref(唯一性索引扫描，常用的为联表的主键或唯一索引，匹配到一行数据)>ref(非唯一性索引扫描，返回匹配某个单独值的所有行)>range(只检索给定范围的行，使用一个索引来选择行；比如where中between in 等)>index(遍历的是索引树来读取数据)>all(全表扫描)； 一般至少到range,最好到ref.

possible_keys:显示可能应用到这张表的索引，一个或多个；查询涉及到的字段上有索引，则该索引被列出，但实际上不一定被查询使用

key:  实际使用的索引,如果没使用，则为null。如果使用了覆盖索引，则该索引仅出现在key列表中.其中覆盖索引指select的数据列只用从索引中就能够取到，不必读取数据行，尽量不适用 *。

key_len:表示索引中使用的字节数，key_len显示的值为索引字段的最大可能长度，并非实际长度，由表定义计算而得。当key为null时，key_len也为null.精度越高长度越长；

ref:  知道索引的哪一列被使用，显示了被用于查找索引列上值的列或常量。

rows:根据表统计信息以及索引选用情况，大致估算出找到所需记录所需要的读取的行数

extra: 包含不在其他列显示但是十分重要的信息，包括： 

        using filesort: 说明对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取；（文件排序：无法利用索引完成的排序操作）；如果显示这个说明很糟糕，可以的话需要优化

        using temporary:使用了临时表保存中间结果，常见于order by和group by；如果出现这个说明更加糟糕，可以的话分组和排序后使用的是索引列

        using index: 说明相应的select操作中使用了覆盖索引，避免了访问表的数据行；如果出现这个说明效率好（和key的覆盖索引呼应）。

        using  where: 表名使用了where条件查询

        using  join buffer: 使用了连接缓存

        impossible where：where子句的值总是为false,不能获得任何元组

        select tables optimized away: 在没有group子句时，基于索引优化MIN/MAX操作或MyISAM优化countz操作，在查询计划生成阶段就完成优化。

        distinct: 优化distinct的操作

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

4、索引分析：

（1）单表创建复合索引时：一般涉及到分组和排序以及where后涉及到的字段，且精度大的在前面（=的精度要大于  > <等）；range类型的字段会导致后面的字段索引失效；

（2）两表连接外连接查询创建索引时：在从表中创建索引，因为主表一定都要遍历的；

（3）三表连接外连接查询创建索引时：在所有的从表中创建索引；

总结：Join语句的优化：

• 尽可能减少join语句中的循环总次数，“小结果集驱动大结果集”；
• 优先优化嵌套的内层循环
• 保证join语句从表上join条件字段已经被索引
• 当无法保证从表条件字段被索引且内存资源充足的情况下，可以允许joinbuffer的设置；

5、索引失效：

• 全值匹配我最爱：使用的字段与复合索引的字段一致且相同；
• 最佳左前缀法则：查询从索引的最左前列开始并且不跳过索引中的列（虽然跳过type也是ref,但是被跳过的后面的列索引会失效）；
• 不在索引列上做任何操作（计算，函数，类型转换），否则索引失效而转向全表扫描
• 存储引擎不能使用复合索引中范围条件(in between like)的右边列，否则索引失效而转向全表扫描
• 尽量使用覆盖索引，减少select *
• like 以通配符%开头会导致索引失效而全表扫描；（解决办法：覆盖索引+子查询，覆盖索引下即使使用%开头，但是type会为index）
• 字符串不加单引号索引失效(虽然查询结果可能不会出错，但是索引会失效)；
• or连接时会导致索引失效
• mysql在使用不等于（!=或<>），否则索引失效而转向全表扫描（亲测8.0版本是range而不是all）
• is null, is not null 也无法使用索引；（亲测8.0版本是range而不是null）

案例：index(c1,c2,c3,c4,c5):

where c1='a1' and c2='a2' and c5='a5' order by c3,c4;

# c3，c4,c5失效；c3,c4没有用来查找，但是被用来排序，不会出现using filesort； 

#但如果c3 c4换位置，则排序无用，出现using filesort;

#但如果c3,c4换了位置，而且c3（以等于的形式）已经出现在where中，依然不会出现 using filesort;

where c1='a1' and c2='a2' and c5='a5' group by c4,c3;#出现using filesort;using temporary;

总结：定值，范围还是排序，一般order by是个范围；分组必排序，否则会有临时表产生；

6、一般性建议：

• 单值索引，尽量选择对当前query过滤性更好的索引；
• 组合索引，当前query过滤性最好的字段在索引字段顺序中越靠左越好
• 组合索引，尽量选择可以包含当前query中的where子句中更多字段
• 尽可能分析统计信息和调整query的写法达到选择合适索引的目的

 

三、查询截断分析

（一）查询优化（"小表驱动大表"）

1、类似嵌套循环：exists 和 in的选择

      select * from A where id in (select id from B)   #  |A| > |B|

      select * from A where exists(select 1 from B where B.id=A.id)    #  |A| < |B|

补充：

　（1）、exists是对外表做循环，每次循环再对内表进行查询，那么因为对内表的查询使用的索引（内表效率高，故可用大表），而外表有多大都需要遍历，不可避免（尽量用小表）；

　（2）、in是把外表和内表做hash连接，先查询内表，再把内表结果与外表匹配，对外表使用索引（外表效率高，可用大表），而内表多大都需要查询，不可避免；

　（3）、如果用not in ，则是内外表都全表扫描，无索引，效率低，可考虑使用not exists，也可使用A left join B on A.id=B.id where B.id is null 进行优化。

2、order by排序优化

（1）尽量使用index方式排序，避免使用filesort方式排序；使用index的两种方式：

• order by语句使用索引最左前列
• 使用where子句和order by子句条件列组合满足索引最左前列

（2）尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀

（3）如果不在索引列上，filesort有两种算法：

• 双路排序：4.1版本以前采用这种排序，需要两次磁盘扫描得到数据（先读排序字段在buffer中排序，然后在从磁盘读取其他字段）；
• 单路排序：4.1版本后采用这种排序，只需要一次磁盘扫描得到数据（先读取所有列到buffer按照排序字段进行排序，然后输出）；

单路总体好多双路，但是当数据大于sort_buffer时，效果不如双路排序（所以不要随便用 *）；此时，可以优化服务器参数，sort_buffer_size参数增大；max_length_for_sort_data参数增大；

3、group by分组优化：

（1）本质是先排序后分组，遵照索引建的最佳左前缀；

（2）当无法使用索引列时，增大sort_buffer_size和max_length_for_sort_data两个参数；

（3）where高于having,条件优先写在where限定中；

 

（二）慢查询日志

1、是一种MySQL提供的日志记录，用来记录在MySQL中响应时间超过阈值的语句，其中阈值通过long_query_time设置，默认为10；

2、慢查询日志默认是关闭的，一般需要调优的时候才需要开启；

show variables like '%slow_query_log%' ;#查看慢日志状态和路径
set global slow_query_log=1; #开启慢日志，只对当前数据库有效；关闭数据库后关闭；
show variables like '%long_query_time%'
set global long_query_time=新的秒数； #重新开启一个会话才有效果

#若想永久性开启，可在my.ini中[mysqld]下修改或增加参数：
slow_query_log=1;
slow_query_log_file=/路径/文件名.log
long_query_time=新的秒数
log_output=FILE

3、日志分析工具：mysqldumpslow 命令

（三）批量数据脚本

（四）show profile

1、分析当前会话中语句执行的资源消耗情况，可以用于sql的调优测量。默认情况下关闭，并保存最近15次的运行结果。

2、相关语法：

show variables like 'profiling'; #查看状态
set profiling=on; #开启
#运行几条SQL以便使用；
show profiles; #显示最近执行的语句表达式，执行时间，并为每一个语句赋了一个query_id；
show profile cpu,block io(,all,memory,...) for query query_id; #为具体的SQL进行诊断；

3、日常开发中遇到的问题status:

• converting HEAP to MyISAM:查询结果太大，内存不够用需要磁盘；
• creating tmp table：创建临时表（拷贝数据到临时表，用完在删除）
• copying to tmp table on disk:把内存中临时表复制到磁盘上
• locked

（五）全局查询日志(不可以在生产环境中使用)

1、配置启用：

general_log_file=/路径
log_ouput=file #输出格式

2、编码启用：

set global general_log=1; #开启
set global log_output=table; #以表的形式输出

select * from mysql.general_log; #查看日志

 

四、MySQL锁机制

（一）概述

1、锁定义：

锁是计算机协调多个进程或线程并发访问某一资源的机制，其中资源除了传统的计算机资源（cpu ram i/o等），数据也是需要共享的资源

2、锁分类：

（1）对数据操作：

读锁（共享锁）：针对同一个数据，多个读操作可以同时进行不会互相影响

写锁（排他锁）：当前写操作没有完成前，会阻断其他写锁和读锁；

（2）从对数据的粒度：行锁；表锁

3、相关语法：

#表锁定义：
show open tables； #展示表被锁的情况
lock table 表名 read(write),表名1 read(write); #手动加表锁，读锁或写锁
unlock tables;#释放所有被锁的表
#表锁分析
show status like 'table%'
#table_locks_immediate:产生表级锁定的次数，
#table_locks_waited:出现表级锁争用而等待的次数，过高说明存在严重表级锁争用情况；


#行锁
begin;
select ...  for update;
commit;
#行锁分析
show status like 'innodb_row_lock%';
#innodb_row_lock_current_waits:当前正在等待锁定的数量
#innodb_row_lock_waits:系统从启动到现在总共等待的次数

4、三种锁的介绍：

（1）表锁（偏读）：偏向MyISAM存储引擎（写优先，不适合写为主表的引擎，易堵塞），开销小，加锁快；无死锁；锁定粒度大，发生冲突概率最高，并发性低；

• 读锁：添加锁的会话可以读被锁的表，不可以更改插入被锁表的数据，不可以读其他表；其他会话可以同时读被锁的表，但更新插入被锁表时会被堵塞；
• 写锁：添加锁的会话可以读改被锁的表，但是不可以读其他表；其他会话读改被锁的表会被堵塞；

（2）行锁（偏写）：偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定颗粒小，发生锁冲突概率低，并发度最高；（注：行锁是自动的,select为读锁，增删改为写锁，同个行会被堵塞；不同行不干扰）

• 无索引（以及索引失效的情况）会导致行锁变为表锁；
• 间隙锁：当我们使用范围条件而不是相等条件检索数据，并请求共享或排他锁时，innodb会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但是不存在的记录，为间隙；innodb也会对这个间隙加锁，称为间隙锁（next-key锁）；

行锁优化：

• 尽可能让所有数据检索都通过索引完成，避免无索引行锁升级为表锁；
• 合理设计索引，缩小锁的范围；
• 尽可能较少检验条件，避免间隙锁；
• 尽量控制事务大小，减少锁定资源量和时间长度；
• 尽可能低级别事务隔离；

（3）页锁：介于行锁和表锁之间，会发生死锁；

五、主从复制

1、基本复制原理：

（1）从master读取binlog来进行数据同步；

（2）过程：（MySQL复制是异步的且串行化的）

master将改变记录到二进制日志（binarylog）.这些记录过程被称为二进制日志事件（binary log events）

-> slave将master的binarylog events拷贝到它的中继日志（relay log）.

-> slave重做中继日志中的事件，将改变应用到自己的数据库中

2、复制的基本原则：

每一个slave只有一个master；

每个master可以有多个slave；

每个slave只有唯一的服务器ID；

3、一主一从常见配置：

（1）MySQL版本一致且后台以服务运行；

（2）主从都配置在[mysqld]节点下，都是小写；

（3）主机从机关闭防火墙

（4）从主机上建立账户并授权slave；

命令： grant replication slave on *.*  to ’主机名‘@’IP地址‘  by  密码；

flush privileges;

show master status;

（5）从从机上配置需要复制的主机；

命令：change master to master_host='IP地址'， master_user='主机名' , master_password=‘密码’ , master_log_file=,master_log_pos=;#根据主机上show的结果填入；

start slave;

show slave status;

（6）主机开始DDL,DML；从机复制；

（7）stop slave;#从机停止复制