1.基础概念

1.1.存储引擎

MyISAM

1. 不支持事务，不支持外键，访问速度快
2. 每张表由3个文件构成照（存储表定义文件，数据存储文件，索引文件）
3. 支持3种存储格式（默认为静态表，会自动将把保存的内容的空格去除）

InnoDB

1. 外键绑定功能
2. 提供事务能力

其他

MEMORY，MERGE，TokuDB

1.2.数据类型的选择

varchar，text，blob

1. text，blob可以存储大文本，但是若存在大量删除该字段的情况时，最好定时采用optimize命令优化表结构
2. text，blob的精确匹配性能的提升可以采用Synthetic索引方案来实现（即对text字段进行md5哈希散列，每次用md5值去查），进一步，对于前缀索引，即：xxx%，的搜索，可以通过对text中前n个字段进行索引创建来提高效率——8.2节

浮点数与定点数

选定点数decimal

DATETIME和TIMESTAMP

DATETIME适用于较长时间范围

TIMESTAMP适用于需要区分时期的情况，他会自动换算时区（最长时间为2038年。。。）

1.3.字符集

合适的字符集选取可以减少数据量，从而减少磁盘IO，提升查询速度。

字符集-character

对于纯中文汉字的数据库应该采用GBK字符集（汉字，英文都是2字节），而含有英文的数据库则应采用UTF-8字符集（汉字3字节，英文1字节）

校对规则-collation

一个字符集可以有多个校对规则，校对规则是用了比较字符串的，即在select的时候，判断where条件中的字符串是否和数据库数据相等，比如忽略大小写的校对规则等等。

字符集的修改

值得注意的是，字符集的修改并不会对已有数据产生影响

1.4.索引

MyISAM支持更多类型索引，但是事务性不好，故不展开。

1. InnoDB支持前缀索引，在对字符串进行索引时，最好使用前缀索引，具体可以百度用法。
2. BTREE索引在>,<,>=,<=,BETWEEN,<>,!=，LIKE ‘patter’时也是生效的。

2.SQL优化

2.1.优化手段

统计sql命令——show status

该命令可以用于统计数据库sql的执行情况，包括增删改查以及各自总计影响行数的数量的统计，也可以统计慢sql查询。eg：

1
2
3
4
show status global 'Com_select';--查询select执行的次数
show status global 'Innodb_rows_read';--查询返回命中的行数
show status global 'Slow_queries';--慢查询

分析sql命令——explain

结果字段总结：

select_type

查询的类型，可分为：SIMPLE，PRIMARY，UNION，SUBQUREY；可用于定位改查询是sql中的哪条查询语句

table

查询的表，作用同select_type；

type

定位命中数据行的方式，性能：ALL<index<range<ref<eq_ref<const,system<NULL

1. ALL

   全表扫描。。

2. index

   全索引扫描，eg：select id from table, select count(*) from table

3. range

   部分索引扫描，eg：select * from table where id > 10,即能使用上索引进行部分数据的排除，常见于基于索引的范围搜索。

4. ref

   非唯一索引的单值扫描或前缀索引扫描，eg：select * from table where column1 = 1 and column2 = 1其中，table包含了复合索引KEY idx_parent_id (column1,column2,column3)。

   注意，对于select * from table where column1 > 1 and column2 = 1这种非单值扫描走的是ALL类型。

   另外，在 join操作中，对于被join的表来说，一般也是 ref 类型。

5. eq_ref

   类似ref，一般出现在join语法，并且on的字段是primary key或者 unique key

6. const，system

   一般是primary key或者 unique key的等值查询，即可以确定只有一条记录返回。eg：select * from talbe where id = 1。具体的，const表示数据集合有很多，但是结果就一条。system表示数据集合就一条，结果也就是这一条（从只有一条结果的子查询中查询的时候）。

7. NULL

   不用查表就能知道结果的情况。一般是查不到数据或者直接查常量。eg：select 1 from table

8. 其他情况请Google

possible_keys,key

不展开

key_length

表示索引使用的字节数

filtered

条件过滤效率，100最好。

extra

using where 表示需要回表扫描，using index表示采用了覆盖索引的扫描

剖析sql命令——show profile

这个命令是在sql命令执行完成后使用的。在navicate中就是结果集后面的剖析选项。一般主要的耗时步骤是：

executing和sending data步骤

完全解读sql执行过程——trace分析优化器

可以通过SELECT * FROM information_schema.optimizer_trace;命令获取trace。

具体的分析步骤可以参考：https://blog.csdn.net/LiuRenyou/article/details/103934808

2.2.常用sql优化技巧

批量插入优化

采用batchInsert进行插入，注意mybatis中的sql长度是有限制的。

排序order by优化

排序时，尽量避免extra 出现 filesort。因此，order by的时候，尽量select选择索引字段，从而使用覆盖索引（extra： using index）来避免额外排序。对于需要返回大量字段的可以采用子查询的方式，先用覆盖索引排序，再查全字段。

order by中的排序最好统一升序或者统一都是降序。具体情况可以看下书。

另外，其实mysql中的复合索引都是包含id的，因此select可以包含id也不会说是索引失效。

优化filesort也有方案，就是select尽量少的数据，从而避免mysql使用二次扫描算法进行filesort。具体看书P293。

group by优化

group by的内部其实就是先用了order by进行排序，然后再group，所以order by的优化方法适用于group by。如果group by的结果不用排序，可以考虑sql中追加order by null。

嵌套查询优化

转为join的形式。因为join的形式使用了储存引擎先过滤一遍数据的方式。

OR条件优化

保证or两边的条件都走索引，否则只一边走，一遍不走，那mysql会直接通过储存引擎的api把全量数据拿出来，在内存里面进行or。（因为一边没走索引也要全量，那还不如直接全量）。

分页优化

1. 走覆盖索引的方式只select id，然后再回表反查对应的数据。
2. 1不行的话就用 where id > lastId limit pageSize的形式进行快速过滤数据。

注意，方法2在order by的字段重复性较高的情况下，会丢数据！！！，即 where id > lastId order by name limit 10这种情况会丢数据。

3.略读部分

3.1.事务控制和锁

通过 BEGIN搭配COMMIT,ROLLBACK来进行事务控制，值得注意的是，开始事务的BEGIN语法会导致当前的LOCK所表命令失效。

3.2.分区

MySQL分区可以加快查询速度，分区类型可以分为RANGE，LIST，HASH，KEY四种。在5.1版本中，对于指定了主见的表，分区只能以主键或者唯一键进行。

1. RANGE分区采用了LESS THAN的方式进行分区，类比switch语法。
2. LIST分区采用了IN的方式进行分区，类比枚举语法。
3. HASH分区可以分为取模和线性分区两种，前者会均匀分区，但是对于分区的拓展性不好，后者可以较好的支持分区拓展。
4. KEY分区未深入了解

3.3.SQL模式

模式适用于数据库迁移，可以通过设置SQL模式的方法，将数据转为对应数据库类型的语法。

4.跳过部分

1. 触发器
2. 视图
3. 存储过程&函数
4. 分布式事务

5.杂谈

1. mysql执行过程

详见：https://www.cnblogs.com/fanguangdexiaoyuer/p/10268570.html

如书中所述的，mysql会对sql语句进行优化，针对优化后的语句，mysql中有专门的执行引擎，会通过调用储存引擎的通用api（屏蔽innodb，myisam等储存引擎区别）进行数据查询。基于不同的sql语句，执行引擎会调用多次储存引擎的api进行数据查询，并生成临时表，然后对临时表进行进一步数据过滤，排序等加工，最终才会返回给mysql的客户端，也就是mybatis（服务器dao层）。

由于在储存引擎中做数据过滤，和把数据搬到mysql的内存中做数据过滤的效率不一样，因此可以通过优化sql来减少回表（重复调用api拿数据）次数，从而提供查询效率。

2. on 和 where的效率

on操作是在储存引擎中做过滤，where是在mysql中做过滤，因此对于索引字段，可以考虑在on中先进行过滤，生成临时表后再进行where过滤。不过注意，这个很可能产生表A-null类型的数据。

详见https://www.cnblogs.com/Jessy/p/3525419.html

最后

以上就是任性发卡最近收集整理的关于深入浅出MySQL——小结1.基础概念2.SQL优化3.略读部分4.跳过部分5.杂谈的全部内容，更多相关深入浅出MySQL——小结1内容请搜索靠谱客的其他文章。