阿里P8架构师谈：MySQL慢查询优化、索引优化、以及表等优化总结

MySQL数据库常见的两个瓶颈是：CPU和I/O的瓶颈。

CPU在饱和的时辰一样平常产生在数据装入内存或从磁盘上读取数据时辰。

磁盘I/O瓶颈产生在装入数据宏大于内存容量的时辰，假如应用漫衍在收集上，那么查询量相等大的时辰那么平瓶颈就会呈此刻收集上。

我们可以用mpstat, iostat, sar和vmstat来查察体系的机能状态。除了处事器硬件的机能瓶颈，对付MySQL体系自己，我们可以行使器材来优化数据库的机能。

Mysql的优化，概略可以分为三部门：索引的优化，sql语句的优化，表的优化

1.索引

一样平常的应用体系，读写比例在10：1阁下，并且插入操纵和一样平常的更新操纵很少呈现机能题目，在出产情形中，我们碰着最多的也是最轻易呈现题目的，照旧一些伟大的查询操纵，因此对查询语句的优化是重中之重，加快查询最好的要领就是索引。

索引：简朴的说，相等于图书的目次，可以辅佐用户快速的找到必要的内容。

在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据布局。可以或许大大进步查询服从。出格是当数据量很是大，查询涉及多个表时，行使索引每每能使查询速率加速成千上万倍。

总结：索引的目标在于进步查询服从，与我们查询图书所用的目次是一个原理：先定位到章，然后定位到该章下的一个小结，然后找到页数。相似的例子尚有：查字典，查舆图等。

2.索引范例

• 平凡索引

是最根基的索引，它没有任何限定。

• 独一索引

与前面的平凡索引相同，差异的就是：索引列的值必需独一，但应承有空值。假如是组合索引，则列值的组合必需独一。

• 组合索引

指多个字段上建设的索引，只有在查询前提中行使了建设索引时的第一个字段，索引才会被行使。

• 主键索引

是一种非凡的独一索引，一个表只能有一个主键，不应承有空值。一样平常是在建表的时辰同时建设主键索引

• 全文索引

首要用来查找文本中的要害字，而不是直接与索引中的值对较量。fulltext索引跟其余索引大不沟通，它更像是一个搜刮引擎，而不是简朴的where语句的参数匹配。fulltext索引共同match against操纵行使，而不是一样平常的where语句加like。它可以在create table，alter table ，create index行使，不外今朝只有char、varchar，text 列上可以建设全文索引。值得一提的是，在数据量较大时辰，现将数据放入一个没有全局索引的表中，然后再用CREATE index建设fulltext索引，要比先为一张表成立fulltext然后再将数据写入的速率快许多。

3.索引优化

• 只要列中含有NULL值，就最好不要在此例配置索引，复合索引假若有NULL值，此列在行使时也不会行使索引
• 只管行使短索引，假如可以，应该拟定一个前缀长度
• 对付常常在where子句行使的列，最好配置索引，这样会加速查找速率
• 对付有多个列where可能order by子句的，应该成立复合索引
• 对付like语句，以%可能‘-’开头的不会行使索引，以%末了会行使索引
• 只管不要在列长举办运算（函数操纵和表达式操纵）
• 只管不要行使not in和<>操纵

1.怎样捕捉低效sql

1）slow_query_log

这个参数配置为ON，可以捕捉执行时刻高出必然数值的SQL语句。

2）ong_query_time

当SQL语句执行时刻高出此数值时，就会被记录到日记中，提议配置为1可能更短。

3）slow_query_log_file

记录日记的文件名。

4）log_queries_not_using_indexes

这个参数配置为ON，可以捕捉到全部未行使索引的SQL语句，尽量这个SQL语句有也许执行得挺快。

2.慢查询优化的根基步调

1)先运行看看是否真的很慢，留意配置SQL_NO_CACHE

2）where前提单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段别离查询，看哪个字段的区分度最高

3)explain查察执行打算，是否与1预期同等（从锁定记录较少的表开始查询）

4)order by limit 情势的sql语句让排序的表优先查

5)相识营业方行使场景

6)加索引时参照建索引的几大原则

7)调查功效，不切合预期继承从1开始说明

2.优化原则

• 查询时，能不要*就不消*，只管写全字段名
• 大部门环境毗连服从宏大于子查询
• 多行使explain和profile说明查询语句
• 查察慢查询日记，找出执行时刻长的sql语句优化
• 多表毗连时，只管小表驱动大表，即小表 join 大表
• 在万万级分页时行使limit
• 对付常常行使的查询，可以开启缓存

数据库表优化

• 表的字段尽也许用NOT NULL
• 字段长度牢靠的表查询会更快
• 把数据库的大表定时刻或一些符号分成小表
• 将表拆分

数据表拆分：首要就是垂直拆分和程度拆分。

程度切分:将记录散列到差异的表中，各表的布局完全沟通，每次从分表中查询, 进步服从。

垂直切分:将表中大字段单独拆分到其它一张表, 形成一对一的相关。

总之：

（编辑：河北网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!