MySQL学习——优化

在系统性能问题中，数据库往往是性能的瓶颈关键因素。那么如何去检测mysql的性能问题，如何构建高性能的mysql，如何编写出高性能的sql语句？为此，整理一些建议。

库表结构优化

• 尽量使用小、简单的字段。
  （1）能用tiny就不要用int
  （2）varchar(n)，这里的n只需要满足业务需求即可，不必取指过大，因为在临时表和排序时，系统可能按照最大长度进行分配内存。

sql查询优化

• not exists、not in、is not null 在mysql5.7中，这种类型的语句无法使用索引
• 切分查询：
  1、概念：把大查询切分小查询，每个查询功能完全一样，只完成一小部分，每次只返回-- 小部分查询结果。
  2、实例：删除旧数据，定期删除大量数据时，如果用一个大的语句一次性完成的话，则可能需要一次锁住很多数据，占满整个事务日志，耗尽系统资源，阻塞很多小但是重要的查询。例如有sql语句：DELETE FROM messages WHERE create < DATE_SUB(NOW(), INTERVAL 3 MONTH);，那么可以采取以下方式进行优化:DELETE FROM messages WHERE create < DATE_SUB(NOW(), INTERVAL 3 MONTH) limit 10000;,一次只删除10000行数据一般来说是一个比较高效并且对服务器影响较小的方法，如果在每次删除以后，隔一段时间再进行删除，可以将服务器上面的压力一次性分散到一个很长的时间段中，就可以大大降低对服务器的影响，还可以大大减少删除时锁的持有时间。
• 分解关联查询：
  1、概念：很多高性能的应用都会对关联查询进行分解。可以对每一个表进行一次单表查询，然后将结果在应用中进行关联。
  2、优点：
  （1）让缓存的效率更高。许多应用程序可以方便的缓存单表查询的结果。另外对于MySQL查询缓存来说，如果关联的某个表发生了变化，就无法使用查询缓存了，而拆分后，如果某个表很少变化，就可以重复利用查询缓存。
• 大于>、大于等于>=、小于<、小于等于<=使用索引视情况而定，如果字段是字符串，则不使用索引，如果是

索引优化

• B-Tree
  1、结构描述：
  
  （1）所有的值都是按顺序存储的，每一个叶子节点到根的距离相同。B-Tree能够加快访问速度，因为按条件查询数据时，如果满足索引查询条件，存储引擎不需要进行全表扫描，而是从索引的根节点开始进行搜索。根节点中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过”要查找的值“和“节点页的值”相比较，可以找到适合的指针进入下层节点。最终存储引擎要么找到对应的值的叶子节点，要么找不到。
  （2）叶子节点比较特殊，他们的指针指向被索引的数据。
  （3）树的深度和表的大小直接相关。
  （4）B-Tree对索引列是顺序组织存储的，所有很适合查找范围数据。
  （5）索引树中的节点，所以除了按值查找以外，索引还可以用于查询中的order by操作（按顺序查找）。
  （6）索引存储了实际的列值。
  2、实例：
  假设有table：
  

  对应的组合索引的结构：
  
  解读：这里使用last_name、first_name、dob三列作为组合索引。B-Tree索引进行排序的依据是根据创建索引是列的顺序。这里先根据last_name进行排序，相同的话，再依次根据first_name、dob进行排序。
  3、最左原则：
  （1）如果不是按照最左列开始查找，则无法使用索引。比如（a，b，c）这个组合索引，实际上生成（a），（a，b），（a，b，c）三个
  4、优点：
  （1）索引大大减少了数据库服务器需要扫描的数据量
  （2）索引可以帮助数据库服务器避免排序和临时表
  （3）索引可以将随机IO变成顺序IO
  5、无法使用索引的情况：
  （1）索引列不能是表达式的一部分，也不能是函数的参数
  （2）
  6、索引的选择性：
  （1）概念：不重复的索引值和数据表的记录总数的比值
  （2）索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，是性能最好的索引。

• 前缀索引：
  1、概念：使用字符串的前缀作为索引值，加入前缀的选择性足够高，就能利用前缀索引（更小的存储空间）达到优秀的性能。
  2、建立：alter table table_name add key(city(7)); //使用city列的前7个字节作为索引。
  3、缺点：MySQL无法使用前缀索引做order by和group by，覆盖扫描
• 聚族索引：
  1、概念：聚族索引并不是一种索引类型，而是一种数据存储方式。数据行实际上存放在叶子节点中。InnoDB通过主键聚集数据，如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚族索引。
  2、实例：
  图片描述
  这里使用id作为聚集索引
  3、不建议直接用长的字符串（比如UUID）作为主键，这样子的话，InnoDB使用主键作为聚族索引，将导致页分裂，影响性能。
  4、回表：由于数据行存储在索引中，所以能通索引直接找到数据行，可以避免再到数据表中进行查找数据（也就是回表）。

分表、分库

检查耗时

show profiles 用于查看执行语句的耗时，不过默认情况下，profiles是关闭的，可以通过set profiling=1;开启profiles。通过select @@profiling; 查看profiles是否开启了。

explain

这里主要对explain查询结果的参数进行说明。

type:该字段表明数据库引擎查找表的方式。常见的有all、index、range、ref、eq_ref、const。从左到右，它们的效率依次是增强的。撇开sql的具体应用环境以及其他因素，你应当尽量优化你的sql语句，使它的type尽量靠右，但实际运用中还是要综合考虑各个方面的。下面具体讲下这几种常见的类型：
（1）all：这便是所谓的“全表扫描”，如果是展示一个数据表中的全部数据项，倒是觉得也没什么，如果是在一个查找数据项的sql中出现了all类型，那通常意味着你的sql语句处于一种最原生的状态，有很大的优化空间。
（2）index：索引全扫描。type=index情况下，扫描的是索引，但是会扫描所有的索引。由于索引是有序的，并且索引文件通常比数据文件小。，所以效率会优于all
（3）range：range指的是有范围的索引扫描，相对于index的全索引扫描，它有范围限制，因此要优于index。关于range比较容易理解，需要记住的是出现了range，则一定是基于索引的。同时除了显而易见的between，and以及'>','<'外，in和or也是索引范围扫描。
（4）ref：出现该连接类型的条件是： 查找条件列使用了索引而且不为主键和unique。其实，意思就是虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。实例如下，cname为非唯一索引：

（5）ref_eq：对于每个来自于前面的表的行组合,从该表中读取一行（并且使用了主键或者唯一性索引进行查找的情况）。这可能是最好的联接类型,除了const类型。实例如下：

（6）const：通常情况下，如果将一个主键放置到where后面作为条件查询，mysql优化器就能把这次查询优化转化为一个常量。（表最多有一个匹配行）
（7）system：表仅有一行(=系统表)。这是const联接类型的一个特例（这次存疑，因为表只有一行的情况下，使用主键查询，显示type=const）

possible_keys:可能用到的索引

key:实际用到的索引

key_len:MySQL决定使用的键长度。如果键是NULL,则长度为NULL。key_len的计算.
（1）所有的索引字段，如果没有设置not null，则需要加一个字节。
（2）定长字段，int占四个字节、date占三个字节、char(n)占n个字符。
（3）对于变成字段varchar(n)，则有n个字符+两个字节。
（4）不同的字符集，一个字符占用的字节数不同。latin1编码的，一个字符占用一个字节，gbk编码的，一个字符占用两个字节，utf8编码的，一个字符占用三个字节。

ref:是通过常量const，或是某个表的某个字段来过滤的。常见的值有 const, func, NULL, 具体字段名。当 key 列为 NULL ，即不使用索引时，此值也相应的为 NULL。

rows:估计需要扫描的行数

Extra:显示以上信息之外的其他信息
（1）Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。
（2）Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。
（3）range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。
（4）Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。
（5）Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。
（6）Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果。
（7）Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。
（8）Using sort_union(...), Using union(...), Using intersect(...):这些函数说明如何为index_merge联接类型合并索引扫描。
（9）Using index for group-by:类似于访问表的Using index方式,Using index
（10）for group-by表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。

filtered : 显示了通过条件过滤出的行数的百分比估计值。

参考：
《高性能mysql》
https://blog.csdn.net/dennis2...