MySQL知识沉淀

引擎在修改表数据时只需修改内存拷贝，再把修改行为记录到硬盘的事务日志中，而不是每次都将修改的数据持久到磁盘。事务日志采取追加的方式，因此写日志的操作是磁盘上一小块的区域内顺序I/O，而不像随机I/O在磁盘多个地方移动磁盘，因此速度会快很多。在事务日志持久后内存中被修改的数据在后台可慢慢得回刷到磁盘。

若事务日志持久后系统奔溃，存储引擎在重启后依旧会自动恢复这部分的数据

1.4 存储引擎

在文件系统中mysql使用目录和文件来保存数据库和表的定义，表的定义后缀名为.frm

不同的存储引擎对表和索引的存储方式不同，表的定义是在服务层进行统一处理

1.4.1 InnoDB

由于该引擎支持事务，所以通常会被用在业务库上

采用MVCC来支持高并发，并支持四个标准隔离级别。默认是REPEATABLE READ（可重复读），通过间隙锁策略防止幻读的出现。

InnoDB是基于簇索引建立，簇索引对于主键查询有很高的性能，它的二级索引中必须包含主键列，若主键列很大其他的索引也会很大

InnoDB通过一些机制和工具可支持热备份（数据库运行的情况下进行备份），其他的存储引擎不支持热备份

1.4.2 MyISAM

MySql 5.1以及之前的版本是默认的该存储引擎，不支持事务也不支持行级锁。导致系统崩溃后数据无法安全恢复

存储：数据文件和索引存储在不同的文件中分别是在.MYD和.MYI，以紧密格式进行存储

1.4.3 MyISAM压缩表

可以使用myisampack对MyISAM表进行压缩且支持索引，压缩后数据不能修改只能读。在需要读取数据解压的时候带来的开销影响并不大，可减少I/O

1.4.4 Archive

只支持insert和select操作，select操作会进行全表扫描，但是在insert操作时会对写如的数据进行zlib压缩，所以会有更少的I/O。在存储日志类的表中更适合使用

1.4.5 Memory引擎

若表需要快速的访问数据，并且数据不会被修改，重启后丢失也没有关系可以使用该存储引擎，因为所有的数据存在内存中，不需要对磁盘进行I/O。可以理解为临时表

但是和create temporary table 语句不同的是，该存储引擎的表结构在重启后还会存在

2. 数据类型

原则：1.更小的通常效果更好，因为占用更少的磁盘，内存和cpu缓存

2. 简单就好，简单的数据类型通常需要更少的cpu周期，如整型的字符集比字符串字符集简单，操作代价低

3. 尽量避免null值

类型	数据类型	存储	说明
整型	tinyint	8位（-2^(n-1)~2^(n-1)-1）	整数的计算一般使用64位的bigint类型计算，32位环境也是一些聚合函数是用decimal和double int(1) 和int(20)对于存储和计算来说是一样的
	smallint	16位（-2^(n-1)~2^(n-1)-1）
	mediumint	24位（-2^(n-1)~2^(n-1)-1）
	int	32位（-2^(n-1)~2^(n-1)-1）
	bigint	64位（-2^(n-1)~2^(n-1)-1）
关键词	unsigned		选择无符号可以使得正数的上限提高一倍
实数	DECIMAL	9字节，小数点占1字节，小数点前后各占4字节	尽量只在小数进行精确计算时使用可以存比bigint更大的整型数据，最大可存64个数字	float 在存储时候会丢失精度 double 在计算的时候会丢失精度 decimal 精确计算
	FLOAT	4字节	cpu直接支持原生浮点型计算,指定浮点类型的精度已经被遗弃
	DOUBLE	8字节	mysql内部计算小数时是double计算的
字符串	VARCHAR()	可变<=255 使用1字节来记录长度，>255使用2字节记录长度存储字符	适用场景在最大长度远大于平均长度时使用，若正好存储超出页的长度Innodb采用分裂页的方式存储列的更新很少使用了utf-8字符集，因为每个字符使用不同的字节数存储容易产生碎片在使用时尽量还是指定合适的长度，因为更长的列会消耗更多的内存。虽然存储上是一样的一个字符占3个字节,数值转换为字符后一个数值占1个字节
	CHAR()	固定长度，存储字符	适合存储很短、定长的字符串末尾的空格会被自动清除经常变动的列可以使用，因为不容易产生碎片当存储的字符串小于指定长度时使用空格填充
	VARBINARY	存储二进制	二进制字符串，存储的是字节码，mysql使用\0来进行填充在进行比较时是用字节的数值来比较，所以更简单更快
	BINARY	存储二进制	二进制字符串，存储的是字节码，mysql使用\0来进行填充在进行比较时是用字节的数值来比较，所以更简单更快
	BLOB	存储大数据量的字符若数据很大需要在外部存储数据行内为1-4字节的指针	BLOB：二进制方式，字符类型：TINYBLOB,SMALLBLOB,BLOB,MEDIUMBLOB,LONGBLOB TEXT：字符方式，字符类型：TINYTEXT,SMALLTEXT,TEXT,MEDIUMTEXT,LONGTEXT 在处理这两个类型时mysql会党最独立的对象进行特殊处理在进行排序时只会对前面的一小串字符串进行排序
	TEXT	存储大数据量的字符若数据很大需要在外部存储数据行内为1-4字节的指针
枚举	ENUM
日期和时间	DATETIME	8字节，10001年~9999年精确到秒	格式：YYYYMMDDHHMMSS，与时区无关
日期和时间	TIMETAMP	4字节，1970年~2038年	与UNIX时间戳相同，受时区的影响，FROM_UNIXTIME()转换为日期。UNIX_TIMETAMP()来讲日期转换为UNIX时间 timestamp类型具有自动初始化和自动更新的特性。

3. 索引

3.1 索引的介绍

B+Tree叶子节点包含的数据: 主键值、事务、MVCC、行数据、指向下个节点的指针

索引类型
B-Tree	节点处也存储数据，由于页的存储空间有限，若行数据大的情况下每个页存储不了多少节点数据
B+Tree	每一个叶子节点都指向下一个叶子节点的指针，方便遍历在该树内所有的数据都是顺序存储的 InnoDB 存储引擎中页的大小为 16KB。一般表的主键类型为 int（4字节）或 bigint（8字节），指针类型也一般为4或8字节，一页中大概存储 16KB / (8B + 8B) = 1KB（粗记 103）个键值。一个深度为 3 的 B+Tree 索引大约可以维护 103 * 103 * 103 ≈ 10亿条记录 InnoDB在存储数据时页只会使用15/16的空间，预留一部分空间一个深度为3的B+Tree搜索一次数据需要3次I/O 该图是展示了主键和数据的存储，辅助索引的构建与之类似，只不多data存储的是主键。key值是依据索引字段来构建的。自增长的主键ID是随机的并不是顺序增长,乱序的主键id会增加数据插入时候的寻址、分裂页以及磁盘碎片的产生分裂页：当该页的存储被沾满时，会新增一个页来存放溢出的数据，此时需要新增一系列的指针来维持InnoDB的数据顺序存储合并页：每个页都会有一个阈值（一般是50%），当该页的数据存储缩减到该阈值就会出发页合并，会找相邻达到该阈值的页进行数据合并	root page：在索引首次创建时分配，page id 保存在数据字典中，记录保存指向子页的指针。root page 填满时不会发生分裂，而是将值移动到新的子页上，并分裂成两个。 non-leaf page：存放指向 leaf page 或其他 non-leaf page 的指针，也称为 internal page。 leaf page：存放数据。每条记录包含一个 next-record 指针，记录下一条记录的偏移量。记录按升序排列，但物理上并非顺序存放。infimum 和 supremum 是伪记录，代表最小值和最大值。每个 page 都有一个 level，leaf page 的 level 是 0，root page 的 level 取决于整个 B+Tree 的高度树的高度：h = logM/logN M: 主键数量即数据的条数 N: 每个索引页存放的数据条数 N = pagesize/(keysize + pointsize) eg: pagesize = 16k keysize (bigint) = 8字节 pointsize = 8字节 N = 16k/(8+8) = 1024= 2^10 M = 1.34亿 ≈ 2^27 h = log(2^27)/log(2^10) = 25/10 ≈ 2.7
联合索引	存储结构也是B+Tree，可以值是所包含的列，data值为主键。使用该索引需要按照索引的最左列开始查找，且不能跳过中间的某一列，某一列是模糊查询其后面的列都无法走索引在联合索引的数据结构中，data块存放的是主键
哈希索引	构造哈希索引只会包含指定列转换后的哈希值(所以无法支持部分索引列查找)以及行指针只有精确匹配索引所有的列才能有效使用索引，并不能避免读取行，但是哈希索引结构紧凑，且哈希码值较小，所以在内存中读取的速度也会很快。哈希索引数据并不按照索引值顺序存储，无法用于排序，只支持等值比较(=,in(),<=>)查询，不支持范围查询哈希冲突：会出现不同的索引列值有相同的哈希码值，需要遍历相同哈希码对应的所有指针逐行进行比较。 InnoDB有一个功能：自适应哈希索引，某些索引值被使用频繁，会在B-Tree的基础上在创建一个哈希索引，使其具备哈希索引的快速查找。完全自动用户无法控制和匹配自己创建哈希索引：新增一列通过CRC32()函数，将所需要的列准换为哈希码，并创建索引
空间索引（R-Tree）	MySQL对GIS支持并不完善，开源数据库中对GIS支持较好的是PostgreSQL，PostGIS
全文索引	全文索引也是B+Tree，它有两层，第一层是所有的关键字，第二层是每个关键字指向文档的指针全文索引的更新操作消耗非常大，在向含有全文索引的表种导入数据时，最好先通过dinable keys来禁用全文索引，导入结束后使用过enable keys来建立全文索引若词在文本中出现率为50%及以上，不会搜索该词相关参数 show variables like 'ft%' – 查看全文索引的参数 ft_boolean_syntax：表示布尔查询时的可以用的符号。改变IN BOOLEAN MODE的查询字符，不用重新启动MySQL也不用重建索引 ft_max_word_len : 最长的索引字符串，默认值为84，修改后要重建索引 ft_min_word_len : 最短的索引字符串，默认值为4，修改后要重建索引 ft_query_expansion_limit: 查询括展时取最相关的几个值用作二次查询 ft_stopword_file (built-in): 停词文件，这个文件里的词查询时会忽略掉，结合不同的语言选用不同的停用词使用 create fulltext index index_name on table_name(column_name1,column_name2,...) – 创建全文索引 where match(column_name1,column_name2,...) against('词语1 词语2'); -- 匹配词语1 匹配词语2 where match(列名...) against(‘ “短语1” ’); -- 在单引号里用双引号包裹一个短语，让返回结果精确匹配指定的短语 where match(列名...) against(‘ +词语1 -词语2 ’ IN BOOLEAN MODE); -- 返回结果必须含有词语1，但不能有词语2 where match(列名...) against(‘ >词语1 <词语2 ’ IN BOOLEAN MODE); -- 含有词语1的话优先级升高，含有词语2的话优先级降低 select id,match(列名...) against(' 词语1 ') as factor from 表名 where.... //返回每行匹配词语1的匹配度缺点占才存储空间大，若内存一次装不下所有索引，性能会非常差增删改的代价大全文索引的优先级较高，优化器无法选择更好的索引且不能使用覆盖索引

3.2 索引策略

索引	索引特性	索引创建原则
是否使用索引	小表：大部分情况下全表扫描更高效中到大型表：索引非常有效特大型表：索引的代价会增大需要结合或者改用其他的技术 eg:分区技术、元数据技术
单列索引	在索引列上进行任何的计算或者使用函数都会使得索引失效	基数/总记录数该值越大查询效率越高基数：数据列中不重复的数据条数总记录数：数据列中总的数据条数
前缀索引	当所需索引的列是长字符串模拟哈希索引构建前缀索引，只是将字符串的前几个字符进行索引。如何选择字符长度按照：基数/总记录数 + 实际情况来选择前缀索引：alter table table_name add index index_name(column_name(n))
联合索引	查询或者关联条件中涉及到多个列，对于如何选择索引顺序索引列的顺序是按照最左列进行排序，索引可以按照升序或者降序进行工作。排序时order by字段在索引里面，当字段是最左或者最左已经给常量可走索引。当order by的字段为多个且顺序不一致时不走索引经验法则：是将选择性最高的列放在前面推论：依据基数/总记录数来进行创建
唯一索引	基数/总记录数 = 1 是最好的索引选择也是性能最好的
聚簇索引	InnoDB通过主键来聚集数据，也就是主键为聚簇索引。若没有定义主键会指定一个唯一的非空索引替代，若没有这样的索引，InnoDB会隐式定义一个主键作为聚簇索引优点：可以将相关数据保存在一起(主键为非自增长ID) 聚簇索引将索引和数据存放在同一个B-Tree中，查找速度更快使用覆盖索引扫描的查询可直接使用页节点中的主键值最大限度地提高了I/O密集型应用缺点：数据全都在内存中时，访问顺序就没那么重要，聚簇索引也就没什么优势了插入速度严重依赖插入顺序，按照主键的顺序插入是加载数据到InnoDB表中最快的方式。如果不是按照主键顺序插入，加载完后最好用OPTINIZE TABLE命令重新组织表更新聚簇索引代价高，会强制InnoDB将每个被更新的行移动到新的位置插入新行或者主键更新需要移动行，可能页中剩余的空间无法全部存放该行数据，会导致分裂页的操作。分裂页会导致表占用更多的磁盘空间当行比较稀疏或者由于分裂页导致数据存储不连续的时候聚簇索引会导致全表扫描变慢二级索引由于叶子节点包含了引用行的主键列所以可能会很大，且二级索引的查询是两次索引查找	OPTINIZE TABLE table_name:：目前只有MyISAM、InnoDB、BDB支持该功能作用：整理数据文件的碎片，释放多余空间碎片的产生：在对数据进行操作时会使得某些页上的存储空间并没有存放数据，从而产生了碎片主键为什么要顺序：在事务插入操作中，数据可统一加载到缓存刷新到磁盘。若是无序的则主要找到新增的数据应该存放在哪个页的叶子节点，增加I/O。若页数据已满则需要分裂页会带来更有多的麻烦
覆盖索引	当索引中的数据已经满足查询需求时mysql优化器会默认使用
松散索引	按照不连续的方式扫描一个索引，跳过不需要的数据只取需要的数据。 mysql在5.0版本之后进行分组查询最大值、最小值时可以使用松散索引 mysql在5.6版本之后关于松散索引扫描的一些限制将通过“索引条件下推(index condition pushdown)”的方式解决

3.3索引使用技巧


尽可能将需要范围查询的列放在索引后面，以便尽可能多的索引列查询中经常使用，但是可选择性不高的列，可选择放在索引列的前面，在使用的时候使用in()全部列举出来即可在查询中尽量避免使用多个范围查询，可将后面的范围查询变成等值查询。若引入松散索引扫描可解决该问题。	在表与表关联时不会先进行范围筛选例如性别这一列，完全可以在条件中写 in（0,1）单个范围查询全量(Using where)，多个范围查询全量(Using index condition)

优点	缺点	细节
较少了服务器需要扫描的数据量帮助服务器避免排序和临时表可以将随机I/O变为顺序I/O 可以让查询锁定更少的行	索引越多insert update delete 的速度越慢索引会占用存储空间	InnoDB在二级索引上使用共享锁，在主键索引上使用排它锁

4. 查询优化

查询慢的原因

分析

访问太多不需要的数据

需要访问的数据是否是我们所需要的包括行与列
在mysql服务器层是否在分析大量超过需要的数据行

访问过多不需要的数据，会给mysq服务器带来额外的负担，增加网络开销，也会消耗应用服务器的cpu和内存资源

取出了全部列

select * 的使用会导致mysql的优化器不会使用覆盖索引

会给服务器带来额外的I/O、内存和淳朴消耗

扫描大量数据返回少数的行

可以通过对需要的列创建索引，走覆盖索引
改变库表结构，创建汇总表

分段删除

-- 分多次执行删除语句限制条数

delete from table_name where column_name = 1 order by id limit 10000;

2. 创建存储过程通过do...while...循环来进行删除

当一次的删除需要操作大量的数据，会给mysql服务器带来极大的压力，而且会锁表影响别的查询

分解关联查询为多个单表查询

将多个表关联的查询拆分成单个表的查询

目的：

让缓存效率更高，单个表查询可能会用到mysql缓存内的数据。若是多表关联，在关联表中任意一表发生了改变都不会使用缓存内的数据，也减少了缓存中的冗余数据
执行单个表的查询可减少锁的竞争
应用层关联更容易对数据库进行拆分，更容易做到高性能和可扩展
单个表查询中查指定的多个值可用in(),并且按照顺序查询，比关联查询时的随机匹配效率更高

查看mysql是否扫描额外记录

依据三个指标：

响应时间
1. 服务时间：
2. 排队时间：等待资源的时间
扫描行数
返回行数

4.1 查询基础

4.2 MySql客户端/服务器通信协议

在任何一个时刻，要么由服务器想客户端发送数据，耀目由客户端向服务器发送数据，两个动作不能同时发生

查询状态：

序号	关键词	含义
1	sleep	线程在等待客户端发送新的请求
2	query	线程正在执行查询或者正在将结果发送给客户端
3	locked	在mysql服务层、线程在等待表锁。存储引擎级别实现的锁
4	analyzing and statistics	线程正在手机存储引擎的统计信息，并生成查询的执行计划
5	copying to tmp table [on disk]	线程正在执行查询，且将结果集复制到临时表中，该操作一般实在做group by、文件排序或者union操作 on disk 表示mysql在将一个诶村临时表放在磁盘上
6	sorting result	线程正在对结果排序
7	sending data	线程在多个状态之间传送数据，或者生成结果集，或者向客户端返回数据

4.3 查询缓存

MySQL将存放在查询缓存的查询结果存放在一长引用表中，通过一个哈希值引用(哈希值包括：查询本身、当前查询的数据库、客户端协议的版本等)，当一个查询执行的时候先和查询缓存中的进行检查是否命中，当查询命中该缓存，MySQL会立即返回结果，跳过了解析、优化和执行阶段。

查询缓存带来的消耗

读查询在开始之前必须先检查是否命中缓存
如果这个查询可以被缓存，当完成执行后，MySQL发现查询缓存中没有这个查询，会将其结果存入查询缓存，会带来额外的系统消耗。
对写操作也会有影响，当向某个表写入数据的时候，mysql必须将对应表的所有缓存都设置为失效。如果查询缓存非常大或者碎片很多，这个操作可能会带来很大的系统消耗
查询缓存的失效操作是靠锁来进行排他操作，消耗也挺大

查询缓存不命中的情况

新的sql语句结果还未存入查询缓存
任何字符上的不同例如空格、注释等任何的不同都会导致缓存的不命中。所以统一的规范和编码很重要
查询缓存系统会跟踪查询涉及的每个表，如果这些表发生变化，那么和这个表相关的所有的缓存数据都将失效。但是这种简单实现代价很小，使用的价值很大。
当查询中有一些不确定的数据，不会被缓存如函数now()、current_date()、current_user()、cunnection_id()、自定义函数、存储函数、用户变量、临时表、mysql库中的系统表或者任何包含列级别权限的表
查询缓存中的查询被执行了缓存失效操作

查询缓存配置参数

参数	说明
query_cache_type	是否打开查询缓存，可设置OFF、ON、DEMAND DEMAND表示止只有在查询语句中明确写SQL_CACHE的语句才会放入查询缓存，可会话级别也可全局级别
query_cache_size	查询缓存使用的总内存空间，单位是字节，必须是1024的整数倍
query_cache_min_res_unit	查询缓存中分配内存块时的最小单位	合适的大小可减少由碎片导致的内存空间浪费，可通过(query_cache_size-Qcache_free_memory)/Qcache_queries_in_cache得到值小会导致频繁的内存块申请，值太大会导致碎片增多。
query_cache_limit	能够缓存的最大查询结果，若大于该值不被缓存。由于在数据生成时就开始尝试缓存数据，当数据全部换回后才能知道是否超出限制当超出限制后会增加状态值qcache_not_cached并将结果从查询缓存中删除
query_cache_wlock_invalidate	若某个表被其他链接锁住，是否任然从查询缓存中返回结果。默认为OFF

查询缓存状态参数

show status like 'qcache%';


	Qcache_free_blocks	查询缓存中碎片空间
	Qcache_free_memory	查询缓存中空闲的内存空间
	Qcache_hits	命中次数，每次查询在缓存中命中就增加
	Qcache_inserts	缓存中插入查询次数，每次插入就增加
	Qcache_lowmen_prunes	由于查询缓存内存空间沾满，导致新的查询缓存结果进来的时候会将老的查询缓存删除掉。该操作会使得该参数的值增加。该数值增长速度快且Qcache_free_blocks碎片少，说明是查询缓存内存空间不足
	Qcache_note_cached	查询不进入缓存
	Qcache_queries_in_cache	此时查询缓存中已经保存的查询结果集个数
	Qcache_total_blocks	查询缓存总内存大小
	FLUSH QUERY CACHE	查询缓存块碎片整理，会访问所有的查询缓存，在这期间无法访问查询缓存，会导致服务器僵死一段时间
	RESET QUERY CACHE	清空查询缓存
	查询缓存命中率	Qcache_hits/(Qcache_hits+Qcache_inserts)
	查询缓存碎片率	Qcache_free_blocks/Qcache_total_blocks*100% – 碎片率超过20%就需要整理
	缓存利用率	(query_cache_size-qcache_free_memory)/query_cache_size*100%

分析和配置查询缓存流程

4.4 查询优化处理

包含一下几个子阶段，整个过程但凡有任何错误，都会终止查询

解析sql：解析sql语句生成对应的“解析树”，解析器对语法规则进行验证和解析查询

预处理：检查数据表、列、别名等存在与否是否合理，验证权限吧

优化sql执行计划：Mysql是基于成本的优化器，预测一个查询使用某种执行计划时的成本，选择成本最小的一个

优化器选择错误的执行计划的原因：

原因	解释
统计信息不准确	MySql依赖存储引擎提供的统计信息来评估成本，InnoDB是MVCC架构不能维护一个数据表的行数的精确统计信息
执行计划中的成本估计不等同于实际执行的成本	MySQL并不知道哪些页面在内存，哪些在磁盘，所以查询实际执行的过程中需要多少次I/O无法得知
成本最优	Mysql是基于成本的优化器，并不是考虑时间最短的执行计划不会考虑不受其控制的操作的成本，如自定义函数
不会考虑并发查询	执行计划只会关注当前的语句，实际的执行会受到并发执行的查询的影响

4.5 mysql的优化


关联查询	MySQL对任何关联都执行嵌套循环关联操作，即MySQL先再一个表中获取出单条数据，然后嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为止。-- 嵌套循环关联数据表的关联并不总是按照语句中的顺序，优化器可决定关联的顺序可以借助查询优化的提示(hint)来指定关联顺序在关联表涉及到group by时，可判断查询列的粒度与关联条件的粒度是否一致，若一致可用关联条件进行group by 效率会高很多 ~~group by 和 oeder by 中只涉及一个字段才会走索引~~
将外链接转换为内链接	当外连接等价于一个内链接时，优化器可识别并调整关联顺序
等价变换规则	可以使用等价变换来简化规范表达式，可移除一些恒等式或者恒不等式。
优化count(),max(),min()	max(),min()可直接使用索引的最左或者最右值，因为索引是顺序存储 count(): 指定列为统计该列的值的数量。是统计数据行数没有任何where条件的count（）通常可以使用存储引擎提供的一些优化 eg：MyISAM维护了一个变量来存放数据表的行数当有where条件时,可以判断反向条件的查询条数是否少于正向条件的查询条数，若反向查询条数远远少于正向查询可改写为： eg: select count() from table_name where id >= 5 当数据量占总数据量很大时可写为 select (select count() from table_name) - count(*) from table_name where id < 5这样扫描的数量可大大减少可以通过整理汇总表来存储相关统计信息尽量使用覆盖索引
预估并转化为常数表达式	当Mysql检测到当一个表达式可转化为常数的时候，会一直把该表达式作为常数进行优化处理。
覆盖索引查询	索引列中包含所有要查询的列时，会选择走覆盖索引
子查询优化	可以将子查询转换一种效率更高的形式，从而减少多个查询多次对数据进行访问
提前终止查询	只取需要的数据，不必查出全部数据 eg：limit 分页查询，当偏移量很大的时候，代价会很大。尽量使用覆盖索引查询来完成优化在 limit 语句中加上SQL_CALC_FOUND_ROWS提示，可获得去掉limit后满足条件的行数
等值传播	若两个列的只通过等式关联，Mysql可将其中一个列的where条件传递到另一列上 eg：关联on条件上的等值条件会传递给关联的表中做where
列表in()	mysql处理in()中的数据会先进行排序，然后通过二分法确定列表中的值是否满足条件O(logn)。or的复杂度O(n)。in()中数据量大的时候处理更快很多数据库系统中in等同于多个or条件的子句 in() 列表中存放顺序的枚举值，查询速度会很快，当列表中是查询语句则效率不高，此时将语句改写为 (not) exists 可提高查询效率 exists 中当子查询中的表有重复数据的时候，这种关联并不会取出重复的数据，在与left join相比有一定的优势，毕竟不需要进行去重
同一个表上进行查询和更新	update table_name1 t1 inner join (select type,count(*) as cnt from table_name1 group by type) as t2 on t1.type = t2.type set t1.cnt = t2.cnt 该语句在更新之前会先执行子查询的语句，并将数据存放在临时表中，在关联的时候实际上是关联的临时表
union (all)	MySQL在执行union的时候是将上下两块的数据先存放在临时表中再进行合并 union 会进行去重 union all 不会进行去重

4.6 排序优化

使用索引进行排序
当数据量较小的时候在内存中进行“快速排序”，当内存不够排序，将数据分块，对独立的块进行“快速排序”，将各个块的数据结果放在磁盘上，然后对各个排好序的块进行合并 – SQL_SMALL_RESULT、SQL_BIG_RESULT可指定排序所在的地方
单次传输排序：（新版本使用）先读取查询需要的所有列，根据给定列进行排序。
1. 优点：只需要一次顺序I/O
2. 缺点：返回的列非常多、非常大会额外占用大量空间
两次传输排序：（旧版本使用）读取行指针和需要排序的字段，进行排序，然后依据排序结果读取所需要的数据
1. 优点：排序时存储尽可能少的数据，使得可以在内存中进行排序
2. 缺点：两次I/O，第二次读取数据时会有大量的随机I/O
进行文件排序是需要使用临时存储空间，该空间可能会比想象中大很多。因为在排序时需要对每一个排序记录分配一个足够长的定长空间来存放，定长空间必须足够容纳数据中最长的字符串

4.7 查询优化器的提示(hint)

序号	关键词	作用	说明
1	HIGN_PRIORITY LOW_PRIORITY	用于select，将select语句重新调度到所有等待表锁的语句之前用在select、insert、update、delete语句。让语句一直处在等待状态，直到之前没有访问该表的语句	只对使用表锁的存储引擎有用，不能用在InnoDB或者其他粒度锁机制和并发控制的引擎中使用	SELECT [LOW_PRIORITY \| HIGH_PRIORITY] * FROM tbl_name
2	DELAYED	对insert、replace有效。将插入行的数据放入到缓冲区，然后在表空闲时批量写入。日志系统使用这样的提示非常有效，或者时需要大量写入数据，但是客户端不需要等待语句完成I/O	并不是所有的存储引擎都支持这样做会导致函数LAST_INSERT_ID()无法正常工作	INSERT [LOW_PRIORITY \| DELAYED \| HIGH_PRIORITY] [IGNORE] INTO tbl_name
3	STRAIGHT_HION	放在select 之后：让查询中所有的表按照在语句中出现的顺序进行关联放在两个关联表的名字之间：固定其前后两个表的关联顺序	当Mysql没有正确选择关联顺序，或者由于顺序太多导致无法评估所有的关联顺序时使用先用explain来查看优化器选择顺序，再使用该提示来重写
4	SQL_SMALL_RESULT SQL_BIG_RESULT	放在select之后，告诉优化器结果集会很小，可以将结果集放在内存中的临时表告诉优化器结果集会很大，建议使用磁盘临时表做排序操作	只对select有效，告诉优化器对 group by 或者 distinct 查询时如何使用临时表排序
5	SQL_BUFFER_RESULT	放在select之后，将查询结果放入到一张临时表，尽可能块的释放表锁	该临时表是存放在服务端，无须在客户端上消耗太多内存
6	SQL_CACHE SQL_NO_CACHE	放在select之后，告诉mysql这个结果集是否应该缓存在查询缓存中	后续章节补充
7	SQL_CALC_FOUND_ROWS	加上该提示后mysql会计算除去limit子句后这个查询要返回的结果集总数，但是在返回的时候只返回limit要求的结果集	不应该使用该部分，后续讲
8	FOR UPDATE LOCK IN SHARE MODE	主要控制select语句的锁机制，只对实现了行级锁的存储引擎有效，使用该提示会对符合查询条件的数据进行加锁	insert...select 不需要这两个提示目前唯一内置支持的存储引擎是InnoDB，使用该提示会让某些优化无法正常使用如覆盖索引尽量避免使用这两个提示，因为有替代方法后面的章节进行讨论
9	use index ignore index force index	force index，use index基本相同，force index是告诉优化器全表扫描的成本会远高于索引扫描
10	optimizer_search_depth	控制优化器在穷举执行计划时的限度	如果查询长时间处于’statistics‘状态，可考虑调低该参数
11	optimizer_prune_level	默认打开，优化器根据需要扫描的行数来决定是否跳过某些执行计划
12	optimizer_switch	包含了一些开启/关闭优化器特性的标志位

5. mysql高级特性

5.1 分区表

使用场景：

表非常大，以至于无法全部放在内存
只在表的最后部分有热点数据，其他的数据都是历史数据
分区的数据可以存放在不同的物理设备上

限制

一个表最多有1024个分区
MYSQL5.1版本中，分区的表达式必须是整数或者是返回整数的表达式，MYSQL5.5中某些场景可以直接使用列来进行分区
分区字段中有主键或者唯一索引的列，所有主键列和唯一所有列都必须包含进来

序号分区实现说明

range partition (范围分区)

范围分区

create table table_name (

id int(8) not null auto_increment

order_data datetime not null,

...

primary key(id order_data)

) ENGINE = innodb partition by range(year(order_data))(

partition p_2020 values less than (2020),

partition p_2021 values less than (2021),

partition p_2023 values less than (2023),

partition p_max values less than maxvalue

)

对单列进行分区

create table table_name (

order_data datetime not null,

...

) ENGINE = innodb partition by range columns(order_data)(

partition p_2020 values less than ('2022-01-01'),

partition p_2021 values less than ('2022-01-02'),

partition p_2022 values less than ('2022-01-03'),

partition p_max values less than maxvalue

)

对多列进行分区

create table table_name (

a int,

b int,

...

) ENGINE = innodb partition by range columns(a,b)(

partition p_0 values less than (0,10),

partition p_1 values less than (10,20),

partition p_2 values less than (20,30),

partition p_max values less than (maxvalue,maxvalue)

)

创建分区表的时候，分区字段必须包含在主键字段
分区键null的处理：
1. range分区键null值会被存储在第一个分区里面
2. list分区键null值需要指定存储在哪个分区里面
3. hash、key分区：对于null值默认为0
range分区的数据是<指定数值并非<=，所以
range、list不指定列时分区的值必须为整数，可以使用函数
range、list分区指定列，不限数据类型，不可使用函数

partition p_max values less than maxvalue 非必须

新增分区：
1. range分区：alter table table_name add partition (partition p_xxxx values less than())，只能在最大分区上新增分区
2. lsit分区：alter table table_name add partition (partition p_xxxx values in())
3. hash、key分区：alter table table_name add partitions 3；-- 新增3个分区
删除分区：
1. range.list分区: alter table table_name drop partition p_xxxx
2. hash、key分区: alter table table_name coalesce partition 1; – 移除一个分区
拆分分区：alter table table_name reorganize partition p_2023 into (partition p_2022 values less than (2022),partition p_2023 values less than (2023))
在插入分区数据时超出范围的数据处理：
1. range分区：创建了maxvalue的分区会存放在这里
2. list分区：会报错通过 ignore 关键字来过滤数据 insert ignore into tablename values(...)
hash分区：
1. 普通hash分区：依据分区字段和分区数取模计算来确定数据存储分区位置 eg:(6,5)=1
  1. 计算方式不一定是最快的
  2. 分布均匀
2. 闲心hash分区：需进行线性计算来确定数据存储分区位置
  1. 计算机对线性计算非常快，所以适合存放大量的数据
  2. 可能分布不是很均匀
分区基本维护
1. alter table table_name rebuild partition p_xxxx(all); – 重建分区清除碎片
2. alter table table_name check partition p_xxxx(all); – 检查分区
3. alter table table_name analyze partition p_xxxx(all); – 分析分区进行键值分布统计以便更好生成执行计划
4. alter table table_name repair partition p_xxxx(all); – 修复所有分区
5. alter table table_name optimize partition p_xxxx(all); – 优化分区

list partition (列表分区)

指定list分区

create table table_name (

exam_code int(20),

order_data datetime not null,

...

) ENGINE = innodb partition by list (exam_code)(

partition p_0 values in (exam_code1),

partition p_1 values in (exam_code3),

partition p_2 values in (null)

)

指定列list分区

create table table_name (

exam_code int(20),

order_data datetime not null,

...

) ENGINE = innodb partition by list columns(exam_code)(

partition p_0 values in (exam_code1),

partition p_1 values in (exam_code2),

partition p_3 values in (exam_code3),

partition p_4 values in (exam_code4)

)

指定多列list分区

create table table_name (

exam_code int(20),

school_code int(20),

order_data datetime not null,

...

) ENGINE = innodb partition by list columns(exam_code,school_code)(

partition p_0 values in (exam_code1,school_code1),

partition p_1 values in (exam_code2,school_code2),

partition p_3 values in (exam_code3,school_code3),

partition p_4 values in (exam_code4,school_code4)

)

hash partition (哈希分区)

普通hash分区

create table table_name (

id int not null,

exam_code int(20),

school_code int(20),

order_data datetime not null,

...

) ENGINE = innodb partition by hash(id)

partitions 5; -- 表示创建5个分区，不指定默认是1个

线性hash分区

create table table_name (

id int not null,

exam_code int(20),

school_code int(20),

order_data datetime not null,

...

) ENGINE = innodb partition by linear hash(id)

partitions 5; -- 表示创建5个分区，不指定默认是1个

key partition (键值分区)

普通key分区

create table table_name (

id int not null primary key,

exam_code int(20),

school_code int(20),

order_data datetime not null,

...

-- unique key(id)

) ENGINE = innodb partition by key() -- 不指定，默认使用主键或唯一键

partitions 5; -- 表示创建5个分区，不指定默认是1个

线性key分区

create table table_name (

id int not null primary key,

exam_code int(20),

school_code int(20),

order_data datetime not null,

...

-- unique key(id)

) ENGINE = innodb partition by linear key() -- 不指定，默认使用主键或唯一键

partitions 5; -- 表示创建5个分区，不指定默认是1个

subparitionint(子分区)

create table table_name (

id int(8) not null auto_increment

order_data datetime not null,

...

primary key(id order_data)

) ENGINE = innodb

partition by range(year(order_data))

subparition by hash(to_days(order_data))

subparition 3

(

partition p_2020 values less than (2020),

partition p_2021 values less than (2021),

partition p_2023 values less than (2023),

partition p_max values less than maxvalue

)

-- 显示指定分区

create table table_name (

id int(8) not null auto_increment

order_data datetime not null,

...

primary key(id order_data)

) ENGINE = innodb

partition by range(year(order_data))

subparition by hash(to_days(order_data))

(

partition p_2020 values less than (2020)(

subparition s0,

subparition s1,

subparition s2

),

partition p_2021 values less than (2021)(

subparition s0,

subparition s1,

subparition s2

),

partition p_2023 values less than (2023)(

subparition s0,

subparition s1,

subparition s2

),

partition p_max values less than maxvalue(

subparition s0,

subparition s1,

subparition s2

)

5.2 视图

create view view_name as

select column_1 ,

column_2 ,

column_3

from table_name

where column_1 = ''

5.2 存储过程

优点：

在服务器内部执行，节省带宽和网络延迟
这是代码重用，可以方便得统一业务规则，保证某些行为总是一致。
为应用提供一定的安全性，如：提供不同细粒度的权限控制、提供一个中间表防止业务表直接暴露
服务器可缓存存储过程的执行计划，对于反复调用的过程，会大大降低消耗
备份、维护都可在服务器端完成

缺点：

Mysql本身没提供好的开发和调试工具，编写存储过程麻烦一些
没什么选项可控制存储过程的资源消耗，所以一个错误可能会直接把服务器拖死

-- 创建存储过程

drop procedure if exists procedure_name;

delimiter //

create procedure procedure_name (in loops int)

begin

declare v1 int;

set v1 = loops;

while v1 > 0 do

isnert into table_name values(null,0,'123');

set v1 = v1 - 1;

end while;

end;

//

delimiter;

5.3 游标

游标是只读、单向的

5.4 字符集

创建对象时的默认值

character_set_client 设置数据库表的默认字符，在创建列而没有为列指定字符集的时候该默认字符集才有作用

服务器和客户端通信时的设置

服务器端总是假设客户端按照character_set_client 设置的字符来传输数据和sql语句。

当服务器受到客户端的sql时，先将其转换成字符集character_set_connection。还使用该设置来决定如何将数据转换成字符串。

当服务器端返回数据或错误信息给客户端时将其准换成character_set_result

字符集不同会报错：ERROR 1267(HY000):Illegal mix of collations. 此时需要通过函数convert() 来转换字符集。可以使用charset()、collation()、coercibility()来定位字符集相关的错误。

字符集要依据存储的数据来决定，utf-8使用的情况较多

explain 关键词


type	index	使用了索引
	range	查询中有范围查询
Extra	Using union(indexc_name,index_name...)	使用了两个索引扫描的联合
	Using index	使用覆盖索引，不用回表
	Using where	通过索引回表查询数据，在存储引擎返回行数据后再用where条件进行过滤
	Using where;Using index;	使用了覆盖索引且在服务层进行了筛选
	Using index condition	使用了索引条件下推，即尽量使用二级索引减少回表的记录数（5.6版本不支持分区表的ICP）
	Using filesort	在关联处理第一个表的时候就对文件进行排序
	Using temporary;Using filesort	将数据全都关联出来，在临时表中进行排序
	Using index for group-by	使用松散索引扫描

show processlist 关键词

mysql 非常用语句

序号	语句	作用
1	OPTINIZE TABLE table_name	整理文件碎片
2	check table table_name	检查表是否损坏
3	repair table table_name	修复损坏的表
4	alter table table_name engine = innodb	通过修改表的存储引擎为当前的存储引擎来修复表