Mysql高级部分

最新推荐文章于 2021-03-23 11:32:31 发布

ok_1234

最新推荐文章于 2021-03-23 11:32:31 发布

阅读量177

点赞数

CC 4.0 BY-SA版权

分类专栏： mysql

本文链接：https://blog.csdn.net/ok_1234/article/details/108423633

mysql 专栏收录该内容

3 篇文章

订阅专栏

1、索引

索引（index)是帮助Mysql高效获取数据的数据结构。
索引的目的在于提高查询效率，可以类比字典。

可以简单理解为“排好序的快速查找数据结构”。

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

为了加快索引的查找，可以维护一个二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。

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

我们平常所说的索引，如果没有特别指明，都是指B树（多路搜索树，不一定是二叉的）结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈希索引（hash index)等。

优势

类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

劣势

实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的
虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，Mysql不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所代理的键值变化后的索引信息
索引只是提高效率的一个因素，如果你的MySql有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询

1.1、MySQL索引分类

单值索引：即一个索引只包含单个列，一个表可以有多个单列索引
唯一索引：索引列的值必须唯一，但允许有空值
复合索引：即一个索引包含多个列
基本语法：
- 创建：CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length);
- 删除 DROP INDEX[indexName] ON mytable;
- 查看 SHOW INDEX FROM table_name\G
- 使用ALTER命令

1.2、MySQL索引结构

BTree索引
Hash索引
full-text全文索引
R-Tree索引

1.3、哪些情况需要创建索引：

主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引
查询中与其他表关联的字段，外键关系建立索引
频繁更新的字段不适合创建索引（因为每次更新不单单是更新了记录还会更新索引结构
where条件里用不到的字段不创建索引
单键/组合索引的选择问题，who?(在高并发下倾向创建组合索引）
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。
查询中统计或者分组字段

1.4、哪些情况不需要创建索引：

表记录太少
经常增删改的表（提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATTE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
数据重复且分布平均的表字段，因此应该只为最精彩查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

1.5、性能分析

explain

使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈

怎么玩：
- Explain + SQL语句
- 执行计划包含的信息：
  - | id | select_type | table | type |possible_keys |key |key_len | ref |rows | Extra|
  - 各字段解释
    - id select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序（三种情况：1）id相同，执行顺序由上至下；2）id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行；3）id相同不同，同时存在
    - select type
      - SIMPLE:简单的select查询，查询中不包含子查询或者UNION
      - PRIMARY:查询中若包含任何复杂的子部分，最外层查询则被标记为primary
      - SUBQUERY:在select或where列表中包含了子查询
      - DERIVED:在FROM列表中包含的子查询被标记为DERIVED(衍生）Mysql会递归执行这些子查询，把结果放在临时表里。
      - UNION 若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
      - UNION RESULT:从UNION表获取结果的SELECT
    - type: | all | index | range | ref | eq_ref | const,system | null |
      - 显示查询使用了何种类型，从最好到最差依次是：system>const>eq_ref>ref>range>index>all
        system：表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不记
        const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行树，如将主键置于where列表中，Mysql就能将该查询转换为一个常量
        eq_ref: 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引
        ref: 非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，他可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体
        range:只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。一般就是在你的where语句中出现了between、<、>、in等的查询，这种范围扫描索引比扫描全表要好，因为他只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
        index:Full Index Scan ,index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）
        all: Full Table Scan,将遍历全表以找到匹配的行
        备注：一般来说，得保证查询至少达到range级别，最好能达到ref
    - possible_keys:显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引被列出，但不一定被查询实际使用
    - key：实际使用的索引。如果为NULL，则没有使用索引。查询中若使用了覆盖索引，则该索引仅出现在key列表中
    - key_len:表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。
    - ref:显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
    - rows:根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数
    - Extra:包含不适合在其他列中显示但十分重要的额外信息
      - Using filesort:说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引进行读取。Mysql中无法利用索引完成的排序操作成为“文件排序”
      - Using temporary:使用了临时表保存中间结果，Mysql在对查询结果排序时使用临时表。常见于排序order by和分组查询group by
      - Using index 表示相应的select操作中使用了覆盖索引（Covering Index),避免访问表的数据行，效果不错！如果同时出现using where,表明索引被用来执行索引值的查找；如果没有同时出现using where,表明索引用来读取数据而非执行查找动作。
        -覆盖索引（covering index），一说为索引覆盖。理解方式一：就是select的数据列只用从索引中就能够取得，不必读取数据行，Mysql可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖。注意:如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select*。因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。
能干嘛
- 表的读取顺序
- 数据读取操作的操作类型
- 哪些索引可以使用
- 哪些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询

1.6、性能优化

由左连接特性决定的。LEFT JOIN条件用于确定如何从右表搜索行，左边一定都有，所以右边是我们的关键点，一定需要建立索引。
尽可能减少Join语句中的NestedLoop的循环总次数；“永远用小结果驱动大的结果集”。优先优化NestedLoop的内层循环；
保证Join语句中被驱动表上Join条件字段已经被索引；
当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝啬JoinBuffer的设置；

1.7、索引失效（应该避免）

全值匹配我最爱
最佳左前缀法则：如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。（带头大哥不能死，中间大哥不能断）
不在索引列上做任何操作（计算、函数、（自动or手动）类型转化），会导致索引失效而转向全表扫描
存储引擎不能使用索引中范围条件右边的列
尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select*
MySQL在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描
is nul , is not null 也无法使用索引
like以通配符开头（‘%abc…’)mysql索引失效会变成全表扫描的操作（建议使用 like aa% 把通配符写在右边)
字符串不加单引号索引失效，类型转化导致索引失效
10.少用or,用它来连接时会索引失效

解决like’%字符串%'时索引不被使用的方法？？
使用覆盖查询字段的索引，可以解决索引失效的问题

1.8、查询截取分析

查询优化
慢查询日志
批量数据脚本
Show Profile
全局查询日志

总结：

慢查询的开启并捕获
explain+慢SQL分析
show profile查询SQL在Mysql服务器里面的执行细节和生命周期情况
SQL数据库服务器的参数调优

1.9、优化原则

1）小表驱动大表，即小的数据集驱动大的数据集。

###########原理（RBO)#################

select * from A where id in (select id from B)
等价于：
for select id from B
for select * from A where A.id = B.id

当B表的数据集必须小于A表的数据集时，用in优于exists。

select * from A where exists (select 1 from B where B.id = A.id)
等价于：
for select * from A
for select * from B where B.id = A.id

当A表的数据集小于B表的数据集时，用exists优于in.
注意：A表与B表的ID字段应建立索引。

EXISTS
SELECT …FROM table WHERE EXISTS(subquery)
该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE或FALSE）来决定主查询的数据结果是否得以保留。

2）Order by子句

尽量使用Index方式排序，避免使用FileSort方式排序
尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀
如果不在索引列上，filesort有两种算法：mysql就要启动双路排序和单路排序

双路排序：MySql4.1之前使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和order列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。

单路排序：从磁盘读取查询需要的所有列，按照order by列在buffer对他们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了。

在sort_buffer中，方法B比方法A要多占用很多空间，因为方法B是把所有字段都取出，所有有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取sort_buffer容量大小，再排…从而多次I/O。

提高Order By的速度

Order by时select * 是一个大忌只Query需要的字段，这点非常重要。在这里的影响是：
1.1 当Query的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时，会用改进后的算法—单路排序，否则用老算法----多路排序。
1.2 两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建temp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些，所以要提高sort_buffer_size.
尝试提高sort_buffer_size
不管用那种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的
尝试提高max_length_for_sort_data
提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort——buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率。

为排序使用索引
Mysql两种排序方式：文件排序或扫描有序索引排序
Mysql能为排序与查询使用相同的索引

KEY a_b_c(a,b,c)

order by能使用索引最左前缀

order by a
order by a,b
order by a,b,c
order by a desc,b desc , c desc

如果where使用索引的最左前缀定义为常量，则order by能使用索引

where a = const order by b,c
where a = const AND b = const order by c
where a = const order by b,c
where a = const and b>const order by b,c

不能使用索引进行排序

order by a asc,b desc, c desc /排序不一致/
where g = const order by b,c /丢失a索引/
where a = const order by c /丢失b索引/
where a = const order by a,d /d不是索引的一部分/
where a in (…) order by b,c /对于排序来说，多个相等条件也是范围查询/

3）group by

实质是先排序后进行分组，遵照索引建的最佳左前缀
当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置
where高于having，能写在where限定的条件就不要去having限定了

1.10、show profile

查看执行语句的历史记录

先将profiles打开 SET profiling = 1;
展示历史的运行情况 show profiles

诊断SQL

show profile cpu,block io for query 3; 数字是针对profiles中的id

若出现以下内容会导致查询缓慢

converting heap to myISAM查询结果太大，内存都不够用了往磁盘上搬了。
creating tmp table 创建临时表（1.拷贝数据到临时表 2）用完再删除）
Copying to tmp table on disk 把内存中临时表复制到磁盘，危险
locked

1.11、全局查询日志

命令

set global general_log = 1;
set global log_output=‘TABLE’;
此后，sql语句将会记录到mysql库里的general_log表，可以用下面的命令查看
Select * from mysql.general_log

2、mysql锁机制

表锁

手动增加表锁
lock table 表名字 read(write),表名字2read(write),其它；

查看表是否被锁
show open tables;

释放表
unlock tables

加了读锁之后，自己能去读，但是不能写，别人也能查询该表的记录。当前session不能查询其他没有锁定的表，其他session可以查询或者更新未锁定的表，当前session中插入或者更新锁定的表都会提示错误，其他session插入或者更新锁定表会一直等待获得锁。

加了读锁，当前session能对锁定表进行查询+更新+插入操作，其他session对锁定表的查询被阻塞，需要等待锁被释放

总结
读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都堵塞。

Myisam的读写锁调度是写优先，这也是myisam不适合做写为主表的引擎。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。

行锁

偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

InnoDB与MyISAM的最大不同有两点：一是支持事务（transaction）；二是采用了行级锁

什么是间隙锁
当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”
InnoDB也会对这个“间隙”加锁，这种加锁机制就是所谓的间隙锁（Next-Key锁）。

危害
因为Query执行过程中通过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。
间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的仍和数据。在某些场景下这可能会对性能造成很大的危害。

优化建议：

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

3、主从复制

Mysql复制过程分成三步：

master将改变记录到二进制日志（binary log)。这些记录过程叫做二进制日志事件，binary log events;
slave将master的binary log events拷贝到它的中继日志（relay log);
slave重做中继日志中的事件，将改变应用到自己的数据库中。MySQL复制是异步的且串行化的