mysql
2021-01-22 17:19:39 39 举报AI智能生成
mysql相关优化
作者其他创作
大纲/内容
索引优化
基础知识
索引分类
使用分类
主键索引，唯一索引，普通索引，组合索引，全文索引
结构分类
聚簇索引
索引结构和数据保存在同一个文件中
innodb引擎使用的就是聚簇索引，数据存储在叶子节点中，包含文件： .frm .idb
优点
数据和索引保存在一起，查询效率较快
发生覆盖索引时可以直接使用主键值
缺点
1、聚簇数据最大限度地提高了IO密集型应用的性能，但如果数据全部在内存，那么聚簇索引就没有什么优势
2、插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式
3、更新聚簇索引列的代价很高，因为会强制将每个被更新的行移动到新的位置
4、基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临叶（叶子节点）分裂的问题
5、基于聚簇索引的表在删除行时，可能面临叶合并的问题
6、聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于叶分裂导致数据存储不连续的时候
非聚簇索引
索引结构和数据分别保存在不同的文件中
Myisam引擎使用的是非聚簇索引，节点中保存的只是数据文件的地址指针，包含文件：.frm .myi .myd
索引语法
查看索引
show index from table_name;
添加索引
主键索引：alter table table_name add primary key (column1);
唯一索引：alter table table_name add unique (column1);
普通索引：alter table table_name add index index_name (column1);
组合索引：alter table table_name add index index_name(column1,column2...);
删除索引
drop index index_name on table_name;
索引优点
优点
1、大大减少了服务器需要扫描的数据量
2、帮助服务器避免排序和产生临时表
3、将随机io改为顺序io，减少磁盘io次数
使用
1、快速查找匹配符合WHERE子句的行
2、从consideration中消除行，如果可以在多个索引之间进行选择，mysql通常会使用找到最少行的索引
3、如果表具有多列索引，则优化器可以使用索引的任何最左前缀来查找行
4、当有表连接的时候，从其他表检索行数据
5、查找特定索引列的min或max值
6、如果排序或分组时是在可用索引的最左前缀上完成的，则会使用索引对表进行排序或分组
7、在某些情况下，可以优化查询以检索值而无需查询数据行
相关名词
回表
主要针对于普通索引，查询结果包含除索引外的其他列，需要经过普通索引查找到主键值，然后再根据主键查找才能返回结果（二次查找）
覆盖索引
查询结果只包含主键列以及查询条件中所使用的其他索引列，再经过一次的索引查询后就可以返回结果，避免了回表查询
不同的存储实现覆盖索引的方式不同，不是所有的引擎都支持覆盖索引，memory不支持覆盖索引；因为innodb是基于聚簇索引的，所以覆盖索引的效果极好
eg：select sname,sid from sort where sname = 'XX'; --（sname列已添加索引）覆盖索引，查询语句前添加explain进行查看extra列中显示为Using index
最左匹配
针对于组合索引，在使用中只会先从组合索引列的最左边列开始进行列匹配，匹配不成功，索引失效（有一才有二，有二才有三...）
索引下推
在数据返回server端前，执行引擎就完成了对所有索引列数据的匹配过滤，将过滤后的数据再返回server端，减少了返回的数据条数，减少了io次数
索引匹配方式
全值匹配
全值匹配指的是和索引中的所有列进行匹配
explain select * from staffs where name = 'July' and age = 23 and pos = 'dev';
匹配最左前缀
只匹配前面的几列
explain select * from staffs where name = 'July' and age = 23;
explain select * from staffs where name = 'July';
匹配列前缀
可以匹配某一列的值的开头部分
explain select * from staffs where name like 'J%'; -- type=range,Extra=Using index condition
explain select * from staffs where name like '%y'; --索引失效， type=all,Extra=Using where
匹配范围值
可以查找某一个范围的数据
explain select * from staffs where name > 'Mary';
精确匹配某一列并范围匹配另外一列
可以查询第一列的全部和第二列的部分
explain select * from staffs where name = 'July' and age > 25;
只访问索引的查询
查询的时候只需要访问索引，不需要访问数据行，本质上就是覆盖索引
explain select name,age,pos from staffs where name = 'July' and age = 25 and pos = 'dev';
注：staffs包含主键索引id，以及组合索引（name，age，pos）
组合索引使用
建立组合索引（a,b,c）
select * from emp where a=1 and b=2 and c=3;
使用了索引abc
select * from emp where b=2 a=1 and and c=3;
使用了索引abc，mysql内部优化器会对sql语句进行优化重排序
select * from emp where a=1 and b>2 and c=3;
使用了索引ab，范围匹配后的索引列失效
select * from emp where a=1 and c=3;
使用了索引a
select * from emp where b=2 and c=3;
没有使用索引，不符合最左匹配
select * from emp where a=1 and b like '%2%' and c=3;
使用了索引a，模糊匹配列不生效
哈希索引
根据hash值进行一一映射存储，只能进行精确匹配；不能进行范围匹配，模糊匹配，分组与排序
mysql中只有memory引擎是基于hash结构存储的，纯内存的结构；hash索引只存储自身计算的hash值，索引结构十分紧凑，精确匹配查询的效率非常快
当hash冲突比较多时，维护代价较高，需要使用较好的hash函数进行运算以减少hash冲突的发生
可以利用CRC32（循环冗余校验）做哈希，使用体积很小的索引来完成较大数据的存储
优化细节
1、当使用索引列进行查询的时候尽量不要使用表达式，把计算放到业务层而不是数据库层
explain select * from menu where menu_id+1=5; -- 主键索引失效
explain select * from menu where menu_id=4; -- 使用索引查询
2、尽量使用主键查询，而不是其他索引，因为主键查询不会触发回表查询
3、使用前缀索引
当频繁进行查询操作的列数据较长时，可截取该列的不重复的前n长度建立索引进行查询，以避免在整个字段建立索引较为消耗资源
1.通过计算完整列的选择性，来选择合适的长度建立索引
select count(distinct left(city,3))/count(*) as sel3 from emp
计算city字段的前n个长度的不重复值dv（distinct value），选择dv基本不变的n值建立索引
2.创建前缀索引
alter table emp add index idx_city_dv(city(7));
4、使用索引扫描来排序
索引的建立最好能兼顾查询和排序，这样可最大程度的利用索引
如果排序列可以和条件查询列组成最左前缀索引，会大大提高查询的效率（查询条件要先匹配左才能生效）
查询和排序组合成最左前缀时，排序中字段的升降也要保持一样（同asc同desc），两者兼顾也会进行全表扫描，导致索引失效
查询中没有使用到索引，即使排序字段使用了索引也不会生效（会进行全表的扫描，explain中的Extra列会出现Using filesort）
5、union all，in，or都能够使用索引，但是推荐使用in
查询相同的情况下，union all 实际是进行了两次查询，in和or都是一次查询，但是in的效率要比or的效率高
union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序
union all：对两个结果集进行并集操作，包括重复行，不进行排序（效率比union高）
select * from menu where menu_id=1 or menu_id=2 union all select * from menu where menu_id=1 or menu_id=3
select * from menu where menu_id=1 or menu_id=2 union select * from menu where menu_id=1 or menu_id=3
select * from menu where menu_id=1 or menu_id=2 union select * from menu where menu_id=1 or menu_id=3
上述查询，如果查询的id列都存在，union查询结果返回三条数据，union all 查询结果返回四条数据
6、范围查询可以使用到索引
>，<，范围列可以使用索引，但是范围列后面的列无法用到索引，索引最多用于一个范围列
范围查询中使用一般索引可能会失效：在sql语句执行前，会通过分析器优化器进行sql的分析优化，当发现使用索引的效率要远大于不使用索引时，索引才会生效
7、强制类型转换可能会导致索引失效进行全表扫描
为emp表的tel添加索引：alter table emp add index idx_tel(tel);
explain select * from emp where tel=15738967250; -- type=all，索引失效，进行全表扫描
explain select * from emp where tel='15738967250'; -- type=ref，使用索引
8、更新十分频繁，或者数据区分度不高的字段上不宜建立索引
更新会变更B+树，更新频繁的字段建索引会大大降低数据库性能
类似于性别这类区分不大的属性，建立索引是没有意义的，不能有效的过滤数据
一般区分度在80%以上的时候就可以建立索引，区分度可以使用 count(distinct(列名))/count(*) 来计算
9、join连接使用
当需要进行表连接的时候，最好不要超过三张表，需要join的字段，数据类型必须一致（出自alibaba开发手册）
join时遵循小表join大表的原则（系统优化后不一定会是书写sql的顺序）
尽量使用关联查询来替代子查询（子查询会产生较大的临时表）
Simple Nested-Loop Join Algorithm
简单嵌套循环连接算法
连接表的个数相当于嵌套循环的层数，每次从一个循环中的第一个表中读取一行，然后将每一行传递给一个嵌套循环，该循环处理联接中的下一个表。
重复此过程的次数与要连接的表的次数相同。
因为NLJ算法从外循环到内循环一次传递一行，所以它通常会多次读取在内循环中处理的表
Index Nested-Loop Join Algorithm
索引嵌套循环连接算法
要求非驱动表的连接列有索引，可以通过索引来较少比较，加速查询
在查询时，驱动表会根据关联字段的索引进行查找，根据索引找到符合的值时，在进行回表查找（只有当匹配到索引才进行回表查询）
如果非驱动表的关联字段是主键，性能会更高；如果不是主键，则要再进行回表查询，性能会比主键索引较低一些
Block Nested-Loop Join Algorithm
块嵌套循环连接算法
如果有索引会采用Index Nested-Loop Join Algorithm，如果没有索引会采用Block Nested-Loop Join Algorithm
该连接方式在每两个表之间会有一个join-buffer缓冲区，会将驱动表的所有关联列以及查询需要的结果列先缓存到join buffer中
然后再用join buffer中的行数据批量与非驱动表进行匹配，将第一种的每次循环匹配合并为一次匹配，降低了非驱动表的访问
join_buffer_size系统默认值是256K，可以自定义参数大小；N个表进行关联查询时，会产生N-1个buffer
10、其他
创建索引的列，不允许为null，为null时可能会得到不符合预期的结果（非强制）
能用limit的要尽量使用limit
索引并不是越多越好，单个表中索引建议控制在5个以内；组合索引字段数不允许超过5个
索引监控
show status like 'Handler_read%';
参数
Handler_read_first：读取索引第一个条目的次数
Handler_read_key：通过index获取数据的次数
Handler_read_last：通过index获取最后一个条目的次数
Handler_read_next：通过索引读取下一条数据的次数
Handler_read_prev：通过索引读取上一条数据的次数
Handler_read_rnd：从固定位置读取数据的次数
Handler_read_rnd_next：从数据节点读取下一条数据的次数
性能分析
Handler_read_key，Handler_read_rnd_next：参数值越大说明索引使用的次数越多，性能越优
查询优化
sql查询慢的原因
网络，cpu，io
上下文切换
系统调用
生成统计时间
锁等待时间
优化数据访问
查询性能低下的主要原因是访问的数据太多，某些查询不可避免的需要筛选大量的数据，可以通过减少访问数据量进行优化
确认应用程序是否在检索大量超过需要的数据
确认mysql服务器层是否在分析大量超过需要的数据行
是否向数据库请求了不需要的数据
查询不需要的记录
在使用mysql分页查询时，实际上mysql会查询出分页前的数据一直到分页数据
select * from emp limit 1000 5; -- 分页前的1000条数据也会进行查取
多表关联时返回全部列
总是取出全部列
禁止使用select *,虽然这种方式能够简化开发，但是会影响查询的性能，所以尽量不要使用
重复查询相同的数据
如果需要不断的重复执行相同的查询，且每次返回完全相同的数据，可以将这部分数据缓存起来，来提高查询效率
执行过程优化
查询缓存
如果需要不断的重复执行相同的查询，且每次返回完全相同的数据，可以将这部分数据缓存起来，来提高查询效率；
mysql缓存机制在8.0之后的版本中被弃用，（对数据的增删改操作都需要进行频繁的操作缓存，不易维护）
sql执行过程：1、连接器，2、分析器（经过分析会先去查询缓存，如果缓存中有匹配直接返回结果），3、优化器，4、执行器
mysql查询完缓存之后会经过以下几个步骤：解析SQL、预处理、优化SQL执行计划，这几个步骤出现任何的错误，都可能会终止查询
语法解析器和预处理
mysql通过关键字将SQL语句进行解析，并生成一颗解析树，mysql解析器将使用mysql语法规则验证和解析查询
例如验证使用了错误的关键字或者顺序是否正确等等，预处理器会进一步检查解析树是否合法，例如表名和列名是否存在，是否有歧义，还会验证权限等
优化器
select count(*) from emp; show status like 'last_query_cost'; 可以查看这条sql对应的花费
在很多情况下mysql会选择错误的执行计划
统计信息不准确：InnoDB因为其mvcc的架构，并不能维护一个数据表的行数的精确统计信息
执行计划的成本估算不等同于实际执行的成本
mysql的最优可能跟你想的不一样：mysql的优化是基于成本模型的优化，但是有可能不是最快的优化
mysql不考虑其他并发执行的查询
mysql不会考虑不受其控制的操作成本：如执行存储过程或者用户自定义函数
优化器的优化策略
静态优化
直接对解析数进行分析，完成优化
动态优化
动态优化与查询的上下文有关，也可能跟取值、索引对应的行数有关
mysql对查询的静态优化只需要一次，但对动态优化在每次执行时都需要重新评估
优化器的优化类型
重新定义关联表的顺序：数据表的关联并不总是按照在查询中指定的顺序进行，决定关联顺序是优化器很重要的功能
将外连接转化成内连接，内连接的效率要高于外连接
使用等价变换规则，mysql可以使用一些等价变化来简化并规划表达式
优化count(),min(),max()：索引和列是否可以为空通常可以帮助mysql优化这类表达式：例如，要找到某一列的最小值，只需要查询索引的最左端的记录即可，不需要全文扫描比较
索引覆盖，当索引中的列包含所有查询中需要使用的列的时候，可以使用覆盖索引
子查询优化：mysql在某些情况下可以将子查询转换一种效率更高的形式，从而减少多个查询多次对数据进行访问，例如将经常查询的数据放入到缓存中
等值传播
排序优化
双次传输排序
第一次数据读取是将需要排序的字段读取出来，然后进行排序；第二次是根据排好序的结果按照需要去读取数据行
劣势：效率比较低，原因是第二次读取数据的时候因为已经排好序，需要去读取所有记录而此时更多的是随机IO，读取数据成本会比较高
优势：在排序的时候存储尽可能少的数据，让排序缓冲区可以尽可能多的容纳数据行来进行排序操作
单次传输排序
先读取查询所需要的所有数据列，然后再根据给定列进行排序，排序后直接返回排序结果
优势：只需要一次顺序IO读取所有的数据，而无须任何的随机IO
劣势：当查询的列特别多的时候，会占用大量的存储空间，无法存储大量的数据
当需要排序的列的总大小超过max_length_for_sort_data定义的字节，mysql会选择双次排序，反之使用单次排序；可以设置此参数的值来选择排序的方式
优化特定类型的查询
count统计查询
MyISAM的count函数比较快，这是有前提条件的，只有没有任何where条件的count(*)才是比较快的（MyISAM引擎在文件中存储有表记录的行数）
一般情况下，count()需要扫描大量的行才能获取精确的数据，其实很难优化，在实际操作的时候可以考虑使用索引覆盖扫描，或者增加汇总表，或者增加外部缓存系统
count(1)，count(*)，count(id)查询效果一样；但是count(1)、count(*)会查询所有列，count(id)会忽略id为空的列
优化关联查询
确保on或者using子句中的列上有索引，在创建索引的时候就要考虑到关联的顺序
当表A和表B使用列c关联的时候，如果优化器的关联顺序是B、A，那么就不需要再B表的对应列上建上索引，可以在A表的c列建立索引
没有用到的索引只会带来额外的负担，一般情况下来说，只需要在关联顺序中的第二个表（非驱动表）的相应列上创建索引
确保任何的group by和order by中的表达式只涉及到一个表中的列，这样mysql才有可能使用索引来优化
优化limit分页
优化limit查询的最简单的办法就是尽可能地使用覆盖索引，而不是查询所有的列
select film_id,description from film order by title limit 50,5
explain select film_id,film.description from film inner join (select film_id from film order by title limit 50,5) as lim using(film_id);
优化union查询
mysql总是通过创建并填充临时表的方式来执行union查询，因此很多优化策略在union查询中都没法很好的使用。经常需要手工的将where、limit、order by等子句下推到各个子查询中，以便优化器可以充分利用这些条件进行优化
除非确实需要服务器消除重复的行，否则一定要使用union all；因为没有all关键字，mysql会在查询的时候给临时表加上distinct关键字去重，这个操作的代价很高
分区表
应用场景
表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他均是历史数据
分区表的数据更容易维护
批量删除大量数据可以使用清除整个分区的方式
可以对一个独立分区进行优化、检查、修复等操作
分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备
可以使用用于创建分区表的语句子句的DATA DIRECTORY和INDEX DIRECTORY选项，将每个分区的数据和索引分配给特定目录；表的DATA DIRECTORY单个分区和子分区仅支持InnoDB
可以使用分区表来避免某些特殊的瓶颈
innodb的单个索引的互斥访问
可以备份和恢复独立的分区
分区表的限制
一个表最多只能有1024个分区，在5.7版本的时候可以支持8196个分区
在早期的mysql中，分区表达式必须是整数或者是返回整数的表达式，在mysql5.5中，某些场景可以直接使用列来进行分区
如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来
分区表无法使用外键约束
分区表的原理
定义
分区表由多个相关的底层表实现，这个底层表也是由句柄对象标识，我们可以直接访问各个分区；
存储引擎管理分区的各个底层表和管理普通表一样（同一分区表的所有分区都必须使用相同的存储引擎）；
分区表的索引支持在各个底层表上各自加上一个完全相同的索引；
从存储引擎的角度来看，底层表和普通表没有任何区别，存储引擎也无须知道这是一个普通表还是一个分区表的一部分。
使用
select： 当查询一个分区表的时候，分区层先打开并锁住所有的底层表，优化器先判断是否可以过滤部分分区，然后再调用对应的存储引擎接口访问各个分区的数据
insert： 当写入一条记录的时候，分区层先打开并锁住所有的底层表，然后确定哪个分区接受这条记录，再将记录写入对应底层表
delete： 当删除一条记录时，分区层先打开并锁住所有的底层表，然后确定数据对应的分区，最后对相应底层表记录进行删除
update：当更新一条记录时，分区层先打开并锁住所有的底层表，l先确定需要更新的记录再哪个分区，然后取出数据并更新，再判断更新后的数据应该再哪个分区，最后对底层表进行写入操作，并对原数据所在的底层表进行删除行操作
注意
有些操作是支持过滤的，例如，当删除一条记录时，MySQL需要先找到这条记录，如果where条件恰好和分区表达式匹配，就可以将所有不包含这条记录的分区都过滤掉，这对update同样有效
如果是insert操作，则本身就是只命中一个分区，其他分区都会被过滤掉，mysql先确定这条记录属于哪个分区，再将记录写入对应的分区表，无须对任何其他分区进行操作
虽然每个操作都会“先打开并锁住所有的底层表”，但这并不是说分区表在处理过程中是锁住全表的，如果存储引擎能够自己实现行级锁（innodb），则会在分区层释放对应表锁
分区表的类型
范围分区
RANGE分区， 根据列值在给定范围内将数据行分配给分区；分区范围是连续的，但不能重叠，并且是使用VALUES LESS THAN运算符定义的
CREATE TABLE employees (
id INT NOT NULL, -- id
name VARCHAR(30),
separated DATE NOT NULL DEFAULT '9999-12-31',
store_id INT NOT NULL -- 商店id
)
id INT NOT NULL, -- id
name VARCHAR(30),
separated DATE NOT NULL DEFAULT '9999-12-31',
store_id INT NOT NULL -- 商店id
)
使用商店id进行分区
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (10), -- 存放商店id为1-10的员工
PARTITION p1 VALUES LESS THAN (21), -- 存放商店id为11-20的员工
PARTITION p2 VALUES LESS THAN MAXVALUE -- 存放大于20的
);
PARTITION p0 VALUES LESS THAN (10), -- 存放商店id为1-10的员工
PARTITION p1 VALUES LESS THAN (21), -- 存放商店id为11-20的员工
PARTITION p2 VALUES LESS THAN MAXVALUE -- 存放大于20的
);
使用日期进行分区
PARTITION BY RANGE ( YEAR(separated) ) (
PARTITION p0 VALUES LESS THAN (1981),
PARTITION p1 VALUES LESS THAN (1991),
PARTITION p2 VALUES LESS THAN (2001),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
PARTITION p0 VALUES LESS THAN (1981),
PARTITION p1 VALUES LESS THAN (1991),
PARTITION p2 VALUES LESS THAN (2001),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
PARTITION BY RANGE COLUMNS(separated) (
PARTITION p0 VALUES LESS THAN ('1960-01-01'),
PARTITION p1 VALUES LESS THAN ('1970-01-01'),
PARTITION p2 VALUES LESS THAN ('1980-01-01'),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
PARTITION p0 VALUES LESS THAN ('1960-01-01'),
PARTITION p1 VALUES LESS THAN ('1970-01-01'),
PARTITION p2 VALUES LESS THAN ('1980-01-01'),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
每个分区的定义顺序是从低到高的，这是PARTITION BY RANGE语法要求；进行表分区后在mysql数据文件中会使用emp#P#p0.ibd命名来与单个的emp.ibd文件做区分
列表分区
类似于range分区，区别在于list分区是基于列值匹配一个离散值集合中的某个值来进行选择分区
PARTITION BY LIST(store_id) (
PARTITION pNorth VALUES IN (3,5,6,9,17),
PARTITION pEast VALUES IN (1,2,10,11,19,20),
PARTITION pWest VALUES IN (4,12,13,14,18),
PARTITION pCentral VALUES IN (7,8,15,16)
);
PARTITION pNorth VALUES IN (3,5,6,9,17),
PARTITION pEast VALUES IN (1,2,10,11,19,20),
PARTITION pWest VALUES IN (4,12,13,14,18),
PARTITION pCentral VALUES IN (7,8,15,16)
);
列分区
可参考范围分区和列表分区（range columns）
Hash分区
分区依据HASH主要用于确保在预定数量的分区之间均匀分布数据。对于范围或列表分区，必须明确指定将给定列值或一组列值存储到哪个分区中；
使用散列分区，只需要基于要散列的列值和要划分的分区表的分区数来指定列值或表达式
PARTITION BY HASH(store_id) PARTITIONS 4; -- 根据store_id进行散列，分区数为4
key分区
PARTITION BY KEY() PARTITIONS 2; -- 如果有主键则自动使用主键；没有主键有唯一键也可以，但是不能有null列
PARTITION BY KEY(id) PARTITIONS 2; -- 要显示的定义出id列为 primary key
子分区
子分区（也称为复合分区）是分区表中每个分区的进一步划分
在mysql5.7中，可以对通过RANGE或进行分区的表进行子分区LIST，子分区可以使用 HASH或KEY分区，这也称为复合分区。
CREATE TABLE ts (id INT, purchased DATE)
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) )
SUBPARTITIONS 2 (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (2000),
PARTITION p2 VALUES LESS THAN MAXVALUE
);
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) )
SUBPARTITIONS 2 (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (2000),
PARTITION p2 VALUES LESS THAN MAXVALUE
);
通过范围分区后，再使用Hash进行分区，分区数为3*2=6个
分区表的使用
全量扫描数据，无法使用索引时
使用简单的分区方式存放表，不要任何索引，根据分区规则大致定位需要的数据为止，通过使用where条件将需要的数据限制在少数分区中，这种策略适用于以正常的方式访问大量数据
分区使在一个表中存储的数据比在单个磁盘或文件系统分区中存储的数据更多
分离热点数据
通过删除仅包含该数据的一个或多个分区，可以轻松地从分区表中删除无用的数据。
相反，在某些情况下，通过添加一个或多个用于专门存储该数据的新分区，可以大大简化添加新数据的过程
满足以下条件的某些查询可以大大优化
满足给定WHERE子句的数据只能存储在一个或多个分区上，这会自动从搜索中排除任何剩余的分区。
由于可以在创建分区表之后更改分区，因此可以重新组织数据以增强在首次设置分区方案时可能不经常使用的频繁查询。这种排除不匹配分区（以及因此包含的任何行）的能力，通常称为分区修剪
MySQL支持显式的分区选择查询
SELECT * FROM t PARTITION (p0,p1) WHERE c < 5; -- 仅选择在分区p0和p1的行匹配WHERE 条件
在这种情况下，MySQL不检查table的其他分区t；当已经知道要查数据所在的分区时，可以大大加快查询速度
选择分区还支持数据修改语句 DELETE， INSERT， REPLACE， UPDATE，和 LOAD DATA， LOAD XML
注意问题
null值会使分区过滤无效
分区列和索引列不匹配，会导致查询无法进行分区过滤
打开并锁住所有底层表的成本可能很高
选择分区的成本可能很高
维护分区的成本可能很高
参数配置
general
datadir=/var/lib/mysql
数据文件存放的目录
socket=/var/lib/mysql/mysql.sock
mysql.socket表示server和client在同一台服务器，并且使用localhost进行连接，就会使用socket进行连接
pid_file=/var/lib/mysql/mysql.pid
存储mysql的pid
port=3306
mysql服务的端口号
default_storage_engine=InnoDB
mysql存储引擎
skip-grant-tables
当忘记mysql的用户名密码的时候，可以在mysql配置文件中配置该参数，跳过权限表验证，不需要密码即可登录mysql
character
character_set_client
客户端数据的字符集
character_set_connection
mysql处理客户端发来的信息时，会把这些数据转换成连接的字符集格式
character_set_results
mysql发送给客户端的结果集所用的字符集
character_set_database
数据库默认的字符集
系统默认是utf8mb4
character_set_server
mysql server的默认字符集
系统默认是utf8mb4
connection
max_connections
mysql的最大连接数，如果数据库的并发连接请求比较大，可以设置该值的大小
max_user_connections
限制每个用户的最大连接数
back_log
mysql能够暂存的连接数量，当mysql的线程在一个很短时间内得到非常多的连接请求时，就会起作用
如果mysql的连接数量达到max_connections时，新的请求会被存储在堆栈中，以等待某一个连接的释放，如果等待连接的数量超过back_log，则不再接受连接请求
wait_timeout
mysql server在关闭一个非交互连接之前需要等待的时长
interactive_timeout
mysql server在关闭一个交互连接之前需要等待的秒数
connection_timeout
连接超时时间s
log
log_error
指定错误日志文件名称，用于记录当mysqld启动和停止时，以及服务器在运行中发生任何严重错误时的相关信息
log_bin
指定二进制日志文件名称，用于记录对数据造成更改的所有查询语句
binlog_do_db
指定将更新记录到二进制日志的数据库，其他所有没有显式指定的数据库更新将忽略，不记录在日志中
binlog_ignore_db
指定不将更新记录到二进制日志的数据库
show master status; -- 查看命令
sync_binlog
指定多少次写日志后同步磁盘，控制binlog日志刷到磁盘的操作，（系统默认是1，表示每次事务完成都会进行刷写）
general_log
是否开启查询日志记录
general_log_file
指定查询日志文件名，用于记录所有的查询语句
slow_query_log
是否开启慢查询日志记录，系统默认开启
slow_query_log_file
指定慢查询日志文件名称，用于记录耗时比较长的查询语句
long_query_time
设置慢查询的时间限制，超过这个时间的查询语句才会记录在慢查询日志中
log_slow_admin_statements
是否将管理语句写入慢查询日志
cache
key_buffer_size
索引缓冲区的大小（只对MyISAM索引的表起作用）
query cache
query_cache_size
查询缓存的大小，在8版本中被删除
show status like '%Qcache%';查看缓存的相关属性
Qcache_free_blocks：缓存中相邻内存块的个数，如果值比较大，那么查询缓存中碎片比较多
Qcache_free_memory：查询缓存中剩余的内存大小
Qcache_hits：表示有多少此命中缓存
Qcache_inserts：表示多少次未命中而插入
Qcache_lowmen_prunes：多少条query因为内存不足而被移除cache
Qcache_queries_in_cache：当前cache中缓存的query数量
Qcache_total_blocks：当前cache中block的数量
query_cache_limit
超出此大小的查询将不被缓存
query_cache_min_res_unit
缓存块最小大小
query_cache_type
缓存类型，决定缓存什么样的查询
0表示禁用
1表示将缓存所有结果，除非sql语句中使用sql_no_cache禁用查询缓存
2表示只缓存select语句中通过sql_cache指定需要缓存的查询
sort_buffer_size
每个需要排序的线程分派的缓冲区大小
innodb_sort_buffer_size：使用innodb引擎排序的缓冲区大小
myisam_sort_buffer_size：使用myisam引擎排序的缓冲区大小
当两参数设置不同时，将会选择更小粒度的参数生效
max_allowed_packet=32M
限制server接受的数据包大小
join_buffer_size=2M
进行表关联查询时每个连接中缓冲区的大小
thread_cache_size
Threads_cached：代表当前此时此刻线程缓存中有多少空闲线程
Threads_connected：代表当前已建立连接的数量
Threads_created：代表最近一次服务启动，已创建现成的数量，如果该值比较大，那么服务器会一直再创建线程
Threads_running：代表当前激活的线程数
innodb
innodb_buffer_pool_size
指定大小的内存来缓存数据和索引，最大可以设置为物理内存的80%
innodb_flush_log_at_trx_commit
主要控制innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，值分别为0，1，2
innodb_thread_concurrency
设置innodb线程的并发数，默认为0表示不受限制，如果要设置建议跟服务器的cpu核心数一致或者是cpu核心数的两倍
innodb_log_buffer_size
此参数确定日志文件所用的内存大小，以M为单位
innodb_log_file_size
此参数确定数据日志文件的大小，以M为单位
innodb_log_files_in_group
以循环方式将日志文件写到多个文件中
read_buffer_size
mysql读入缓冲区大小，对表进行顺序扫描的请求将分配到一个读入缓冲区
read_rnd_buffer_size
mysql随机读的缓冲区大小
innodb_file_per_table
此参数确定为每张表分配一个新的文件
innodb_page_size
mysql数据页的大小（16K）
锁机制
锁介绍
锁是计算机协调多个进程或线程并发访问某一资源的机制（保证系统数据访问的一致性）
表锁
InnoDB和MyISAM都支持表锁
开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低
行锁
InnoDB支持行锁，MyISAM不支持行锁
开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高
MyISAM
共享读锁（Table Read Lock），独占写锁（Table Write Lock）
MyISAM表的读操作：在读之前会自动给涉及的表加读锁，其他用户可以同时进行读操作，但是写操作会被阻塞
MyISAM表的写操作：在写之前会自动给涉及的表加写锁，其他用户同时对涉及的表进行读写操作都将会被阻塞
MyISAM表的读操作与写操作之间，以及写操作之间是串行的，系统会自动进行加锁，不需要用户的干预
写阻塞读
当一个线程获得一个表的写锁后，只有当前线程可以对该表进行读写操作；其他线程的读写操作都会阻塞，直到锁释放为止
1、线程a获取表的写锁：lock table mylock write;
线程b没有获得锁
2、线程a进行：select，insert等操作可以正常返回结果
线程b进行：select，insert等操作被阻塞等待
2、线程a释放锁：unlock tables；
在线程a释放锁的瞬间线程b就会返回上面操作的结果
读阻塞写
当一个线程获取一个表的读锁后，该线程可以对该表进行读操作，写操作会出错，对其他表的读写操作都会出错；
其他线程也可以对该表进行读操作，但是写操作均会被阻塞，直到锁被释放
1、线程a获取mylock表的读锁：lock table mylock read；
线程b没有获取锁
2、线程a只能对mylock表进行读操作，对mylock表进行写操作会出错；对其他表进行读写操作均会出错；
线程b只能对mylock表进行读操作，写操作会被阻塞，可以正常对其他表进行读写操作
3、线程a释放锁：unlock tables；
在线程a释放锁的瞬间线程b的写请求会立即响应返回结果
并发插入问题
当一个线程获取一个表的本地读锁后（read local），该线程只能查看该表加锁前的数据（加锁后其他线程插入的数据不可见），不能进行其他操作；
而其他线程可以对该表进行查询和插入操作，并可以查询全量数据，但是更新操作会被阻塞
1、线程a获取mylock的锁：lock table mylock read local;
线程b没有获取锁
2、查询mylock表：线程a可以返回结果
线程b可以返回结果
3、插入mylock表数据：线程a报错
线程b可以返回结果
4、更新mylock表数据：线程a报错
线程b发生阻塞等待
4、查询mylock表：线程a可以返回结果（不完整，没有线程b刚插入的数据）
线程b返回完整数据
5、对其他表进行读写：线程a报错
线程b可以返回结果
6、线程a释放锁后可以查看到线程b之前插入的数据
线程a释放锁的瞬间线程b完成更新操作
InnoDB
支持事务（ACID）
原子性（Actomicity）：一个事务是一个原子操作单元，要么全部执行成功，要么执行失败（由undo_log保证）
一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态（最终共同保证数据的一致性）
隔离性（Isolation）：数据库系统提供了一定的隔离机制，保证事务不受外界并发操作的影响“独立”执行（由锁机制保证）
持久性（Durable）：事务完成之后，对于数据的修改是永久性的，即使出现系统故障也不会发生改变（由redo_log保证）
并发事务
问题
脏读（Dirty Read）：A事务在执行过程中，B事务读取了A事务修改后的数据；但是由于某些原因，A事务进行了RollBack，则B事务所读取的数据就是脏数据
不可重复读（non-repeatable Read）：B事务读取了两次数据，在这两次读取过程中A事务修改了数据，造成B事务两次读取的数据不一样，即不可重复读
幻读：B事务读取了两次数据，在这两次读取过程中A事务添加了数据，造成B事务这两次读取出来的集合不一样，即幻读
问题分析
上述问题都是有关于数据库读一致性的问题，可以通过事务的隔离机制来保证；
数据库的事务隔离级别越高，并发副作用就越小，但付出的代价也就越大
因为事务隔离本质上就是使事务在一定程度上串行化，需要根据具体的业务需求来决定使用哪种隔离级别
隔离级别
read uncommitted（读未提交）：允许一个事务读取另一个事务未提交的数据，可能会发生脏读，幻读，不可重复读（不建议使用）
read committed（读已提交）：一个事务只能读取另一个事务已经提交的数据，可以避免脏读；可能发生幻读、不可重复读
repeatable read（可重复读）：一个事务可以多次执行某一查询操作，并且每次的返回结果相同；可以避免脏读、不可重复读，可能发生幻读
serializable（串行化）：每个事务都有序的进行，事务之间互相不干扰，可以防止脏读，幻读，不可重复读，但是会影响系统的效率。
锁
mysql InnoDB引擎默认的修改数据语句：update、delete、insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁
可以使用select …for update（加排他锁），使用select … lock in share mode（加共享锁）；所以加过排他锁的数据行在其他事务中是不能修改的，也不能通过for update或lock in share mode的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制
共享锁（S）
又称读锁；获取行数据的共享锁的事务可以进行行数据的读取，但是不能修改；其他事物也可以获取该行数据的共享锁，但是不能获取排他锁
事务a对数据对象data加了S锁，则事务a可以读data但不能修改data；其他事务只能再对data加S锁，而不能加X锁，直到事务a释放data上的S锁；这保证了其他事务可以读data，但在事务a释放data上的S锁之前不能对data做任何修改
排他锁（X）
又称写锁；允许获取排他锁的事务进行读写数据，阻止其他事务获取相同数据集的读锁和写锁
若事务a对数据对象data加上X锁，事务a可以读data也可以修改data；其他事务不能再对data加任何锁，直到a释放data上的锁为止
InnoDB的行锁是通过给索引项加锁实现的，这种行锁的实现特点意味着：只有通过索引条件检索数据，InnoDB才会使用行级锁，否则，InnoDB将使用表锁
案例一
1、创建无索引的表
create table tab1(id int,name varchar(10)) engine=innodb;
insert into tab1 values(1,'1'),(2,'2'),(3,'3'),(4,'4');
insert into tab1 values(1,'1'),(2,'2'),(3,'3'),(4,'4');
2、关闭自动提交事务
set autocommit=0;
3、两个线程操作
①不加锁
线程a： select * from tab1 where id = 1; -- 查询成功
线程b：select * from tab1 where id = 2; -- 查询成功
②加写锁
线程a： select * from tab1 where id = 1 for update; -- 查询成功
线程b： select * from tab1 where id = 2 for update; -- 查询堵塞
b线程查询id=2被阻塞，说明无索引加的表锁，a线程提交事务b才能返回结果
4、给tab1的id列加索引
alter table tab1 add index idx_id(id);
5、两个线程操作
加写锁
线程a： select * from tab1 where id = 1 for update; -- 查询成功
线程b： select * from tab1 where id = 2 for update; -- 查询成功
a，b线程分别查询不同记录加锁，查询成功，说明有索引加的行锁
结论：查询列带索引，加行锁；不带锁，加表锁。
案例二
①读锁
线程a：select * from tab1 where id=1 lock in share mode; --操作成功
线程b：select * from tab1 where id=1 lock in share mode; --操作成功
某一线程持有一数据行的读锁，其他线程也可以对该数据行加读锁
②写锁
线程a：select * from tab1 where id=1 for update; --操作成功
线程b
select * from tab1 where id=1 for update; --阻塞等待
select * from tab1 where id=1 lock in share mode; --阻塞等待
select * from tab1 where id=2 for update; --操作成功
某一线程持有一数据行的写锁，其他线程对该数据行加锁（读锁，写锁）都会进行阻塞等待，可以操作其他数据
③对索引加锁再理解
插入相同id不同name的数据（id列带索引）：insert into tab1 values(1,'hhhh'),(1,'kkkk');
线程a
select * from tab1 where id=1 and name='hhhh' for update; --操作成功
线程b
select * from tab1 where id=1 and name='kkkk' for update; --阻塞等待
虽然查询的不是同一数据，但是查询使用的是同一索引，对索引加锁，所以线程b会阻塞等待
④同一线程加锁
先加读锁，再加写锁
线程a：select * from tab1 where id=1 lock in share mode; --操作成功
线程a：select * from tab1 where id=1 for update; --操作成功
先加写锁，再加读锁
线程a：select * from tab1 where id=1 for update; --操作成功
线程a：select * from tab1 where id=1 lock in share mode; --操作成功
同一线程可以对同一数据反复加锁（读锁、写锁）
MVCC
认识MVCC
什么是MVCC
Multi-Version Concurrency Control，多版本并发控制
MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存
MVCC在MySQL InnoDB中的实现主要是为了提高数据库的并发能力，用更好的方式去处理读写冲突，在有读写冲突时，即使不加锁也能进行非阻塞的并发读操作
说白了MVCC就是为了实现读-写冲突不加锁，而这个读指的就是快照读, 而非当前读，当前读实际上是一种加锁的操作，是悲观锁的实现
当前读与快照读
当前读
像select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读
为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。
快照读
像不加锁的select操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读
之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即MVCC；
快照读可能读到的数据并不一定是最新版本的，有可能是之前的历史版本
快照读可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；
与MVCC的关系
准确的说，MVCC多版本并发控制指的是 “维持一个数据的多个版本，使得读写操作没有冲突” 这一个概念；这仅仅是一个理想概念
在MySQL中，快照读就是MySQL实现MVCC理想模型的一个具体非阻塞读功能的落地实现
快照读本身也是一个抽象概念，MVCC模型在MySQL中的具体实现则是由 3个隐式字段，undo日志 ，Read View 等去完成的
数据库并发场景
读-读
不存在问题，不需要进行并发控制
读-写
存在并发安全问题，会产生数据一致性问题，发生脏读、幻读、不可重复读
写-写
存在并发安全问题，会产生数据一致性问题，数据更新丢失
MVCC带来的好处
多版本并发控制（MVCC）是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照数据
在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能
同时还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决数据更新丢失问题
实现原理
MVCC的目的就是多版本并发控制，在数据库中的实现，就是为了解决读写冲突，它的实现原理主要是依赖记录中的 3个隐式字段，undo日志 ，Read View 来实现的
三个字段
DB_TRX_ID
6byte，最近修改(修改/插入)事务ID：记录创建这条记录/最后一次修改该记录的事务ID
DB_ROW_ID
6byte，隐含的自增ID（隐藏主键），如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引
DB_ROLL_PTR
7byte，回滚指针，用于配合undo日志，指向上一个旧版本（存储于rollback segment里）
实际还有一个删除flag隐藏字段, 既记录被更新或删除并不代表真的删除，而是删除flag变了
undo log
insert undo log
代表事务在insert新记录时产生的undo log, 只在事务回滚时需要，并且在事务提交后可以被立即丢弃
update undo log
事务在进行update或delete时产生的undo log; 不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除
purge
为了实现InnoDB的MVCC机制，更新或者删除操作都只是设置一下老记录的deleted_bit，并不真正将过时的记录删除。
为了节省磁盘空间，InnoDB有专门的purge线程来清理deleted_bit为true的记录。
为了不影响MVCC的正常工作，purge线程自己也维护了一个read view（这个read view相当于系统中最老活跃事务的read view）;
如果某个记录的deleted_bit为true，并且DB_TRX_ID相对于purge线程的read view可见，那么这条记录一定是可以被安全清除的。
Read View
Read View（读视图）是事务进行快照读操作的时候产生的读视图(Read View)，在该事务执行快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的ID(当每个事务开启时，都会被分配一个ID, 这个ID是递增的，所以最新的事务，ID值越大)
Read View主要是用来做可见性判断的, 即当某个事务执行快照读的时候，对该记录创建一个Read View读视图，把它作为条件用来判断当前事务能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录的undo log里面的某个版本的数据。
https://www.jianshu.com/p/8845ddca3b23
日志
undo log
undo log是为了实现事务的原子性，是innodb存储引擎级别的日志，用undo Log来实现多版本并发控制(MVCC)
在写数据之前，先将数据备份到undo log中，然后再进行写操作，如果出现了错误或者执行了ROLLBACK，系统可以利用undo log将数据恢复到事务开始之前的状态
可以理解为：当delete时，undo log中会记录对应的insert；当insert时，undo log中会记录对应的delete；当update时，undo log中记录对应相反的update
redo log
redo log是为了实现事务的持久性，是innodb存储引擎级别的日志
当发生数据修改的时候，innodb引擎会先将记录写到redo log中，并更新内存，此时更新就算是完成了，同时innodb引擎会在合适的时机将记录更新到磁盘中
redo log是固定大小的，是循环写的过程； 有了redo log之后，innodb就可以保证即使数据库发生异常重启，之前的记录也不会丢失，叫做crash-safe
redo log的保存分为两个阶段（prepare和commit）
binlog
binlog是MySQL server服务端的日志级别，主要记录mysql功能层面的日志
与redo log的区别
redo log是innodb独有的，binlog是所有引擎都可以使用的
redo log是物理日志，记录的是在某个数据页上做了什么修改；binlog是逻辑日志，记录的是这个语句的原始逻辑
redo log是循环写的，会覆盖旧的信息，空间会用完；binlog是可以追加写的，不会覆盖之前的日志信息
binlog中会记录所有的逻辑，并且采用追加写的方式，一般在企业中数据库会有备份系统，可以定期执行备份，备份的周期可以自己设置
恢复数据的过程：找到最近一次的全量备份数据；从备份的时间点开始，将备份的binlog取出来，重放到要恢复的那个时刻
数据更新的流程
1、执行器先从引擎中找到数据，如果在内存中直接返回，如果不在内存中，查询后返回
2、执行器拿到数据之后会先修改数据，然后调用引擎接口重新吸入数据
3、引擎将数据更新到内存，同时写数据到redo log中，此时处于prepare阶段，并通知执行器执行完成，随时可以操作
4、执行器生成这个操作的binlog
5、执行器调用引擎的事务提交接口，引擎把刚刚写完的redo log改成commit状态，更新完成
若在commit没有发生，redo log和binlog都没有提交成功，保证了数据恢复时的一致性
0 条评论
下一页
