MySQL优化
# MySQL优化概述
MySQL数据库常见的两个瓶颈是：CPU和I/O的瓶颈。
CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候。
磁盘I/O瓶颈发生在装入数据远大于内存容量的时候，如果应用分布在网络上，那么查询量相当大的时候那么平瓶颈就会出现在网络上。
我们可以用mpstat, iostat, sar和vmstat来查看系统的性能状态。除了服务器硬件的性能瓶颈，对于MySQL系统本身，我们可以使用工具来优化数据库的性能。
# MySQL优化方案
Mysql的优化，大体可以分为三部分：索引的优化，sql语句的优化，表的优化
# 索引
一般的应用系统，读写比例在10：1左右，而且插入操作和一般的更新操作很少出现性能问题，在生产环境中，我们遇到最多的也是最容易出现问题的，还是一些复杂的查询操作，因此对查询语句的优化是重中之重，加速查询最好的方法就是索引。
索引：简单的说，相当于图书的目录，可以帮助用户快速的找到需要的内容。
在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。能够大大提高查询效率。特别是当数据量非常大，查询涉及多个表时，使用索引往往能使查询速度加快成千上万倍。
总结：索引的目的在于提高查询效率，与我们查询图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小结，然后找到页数。相似的例子还有：查字典，查地图等。
# 索引类型
- 普通索引
是最基本的索引，它没有任何限制。
- 唯一索引
与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
- 组合索引
指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。
- 主键索引
是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引
- 全文索引
主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。
# 索引优化
- 只要列中含有NULL值，就最好不要在此例设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引
- 尽量使用短索引，如果可以，应该制定一个前缀长度
- 对于经常在where子句使用的列，最好设置索引，这样会加快查找速度
- 对于有多个列where或者order by子句的，应该建立复合索引
- 对于like语句，以%或者‘-’开头的不会使用索引，以%结尾会使用索引
- 尽量不要在列上进行运算（函数操作和表达式操作）
- 尽量不要使用not in和<>操作
# SQL慢查询的优化
# 如何捕获低效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语句有可能执行得挺快。
# 慢查询优化的基本步骤
先运行看看是否真的很慢，注意设置SQL_NO_CACHE
where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）
order by limit 形式的sql语句让排序的表优先查
了解业务方使用场景
加索引时参照建索引的几大原则
观察结果，不符合预期继续从1开始分析
# 优化原则
- 查询时，能不要就不用，尽量写全字段名
- 大部分情况连接效率远大于子查询
- 多使用explain和profile分析查询语句
- 查看慢查询日志，找出执行时间长的sql语句优化
- 多表连接时，尽量小表驱动大表，即小表 join 大表
- 在千万级分页时使用limit
- 对于经常使用的查询，可以开启缓存
# 数据库表优化
- 表的字段尽可能用NOT NULL
- 字段长度固定的表查询会更快
- 把数据库的大表按时间或一些标志分成小表
- 将表拆分
数据表拆分：主要就是垂直拆分和水平拆分。
水平切分:将记录散列到不同的表中，各表的结构完全相同，每次从分表中查询, 提高效率。
垂直切分:将表中大字段单独拆分到另外一张表, 形成一对一的关系。
# 索引优化
# 使用复合索引的的全部字段
当建立了复合索引时，在查询的where条件中，最好全部的字段都使用到，并且严格按照索引顺序，这样的查询效率最高。
# 最左前缀原则
如果建立的是复合索引，索引的顺序要按照建立时的顺序，即从左到右，如：a->b->c（和 B+树的数据结构有关）
# 禁止对索引做运算和函数
以下用法会导致索引失效
- 计算，如：+、-、*、/、!=、<>、is null、is not null、or
- 函数，如：sum()、round()等等
- 手动/自动类型转换，如：id = "1"，本来是数字，给写成字符串了
# 索引不要放在范围查询右边
比如复合索引：a->b->c，当 where a="" and b>10 and 3=""，这时候只能用到 a 和 b，c 用不到索引，因为在范围之后索引都失效（和 B+树结构有关）
# 减少 select * 的使用-使用覆盖索引
尽量保持select 查询字段和 where 中使用的索引字段一致。
# like模糊搜索优化
# 失效情况
- like "%张三%"
- like "%张三"
# 解决方案
- 使用复合索引，即 like 字段是 select 的查询字段，如：select name from table where name like "%张三%"
- 使用 like "张三%"
# order优化
当查询语句中使用 order by 进行排序时，如果没有使用索引进行排序，会出现 filesort 文件内排序，这种情况在数据量大或者并发高的时候，会有性能问题，需要优化。
# filesort 出现的情况举例
- order by 字段不是索引字段
- order by 字段是索引字段，但是 select 中没有使用覆盖索引，如：select * from staffs order by age asc;
- order by 中同时存在 ASC 升序排序和 DESC 降序排序，如：select a, b from staffs order by a desc, b asc;
- order by 多个字段排序时，不是按照索引顺序进行 order by，即不是按照最左前缀法则，如：select a, b from staffs order by b asc, a asc;
# 索引层面解决方法
- 使用主键索引排序
- 按照最左前缀法则，并且使用覆盖索引排序，多个字段排序时，保持排序方向一致
- 在 SQL 语句中强制指定使用某索引，force index(索引名字)
- 不在数据库 (opens new window)中排序，在代码层面排序
# order by 排序算法
双路排序Mysql4.1 之前是使用双路排序，字面的意思就是两次扫描磁盘，最终得到数据，读取行指针和 ORDER BY 列，对他们进行排序，然后扫描已经排好序的列表，按照列表中的值重新从列表中读取对数据输出。也就是从磁盘读取排序字段，在 buffer 进行排序，再从磁盘读取其他字段。
文件的磁盘 IO 非常耗时的，所以在 Mysql4.1 之后，出现了第二种算法，就是单路排序。
单路排序从磁盘读取查询需要的所有列，按照 orderby 列在 buffer 对它们进行排序，然后扫描排序后的列表进行输出， 它的效率更快一些，避免了第二次读取数据，并且把随机 IO 变成顺序 IO，但是它会使用更多的空间， 因为它把每一行都保存在内存中了。
当我们无可避免要使用排序时，索引层面没法在优化的时候又该怎么办呢？尽可能让 MySQL 选择使用第二种单路算法来进行排序。这样可以减少大量的随机 IO 操作,很大幅度地提高排序工作的效率。
# explain语句
# 用法
explan select ...// select查询语句
# id
是一组数字，代表多个表之间的查询顺序，或者包含子句查询语句中的顺序，id 总共分为三种情况，依次详解
- id 相同，执行顺序由上至下
- id 不同，如果是子查询，id 号会递增，id 值越大优先级越高，越先被执行
- id 相同和不同的情况同时存在
# select_type
select_type 包含以下几种值
- simple：简单的select查询，查询中不包括子查询或者union查询
- primary：如果select包含子查询，子查询的最外层会被标记为primary
- subquery：在select或者where字段中包含子查询，子查询被标记为subquery
- derived：在from中包含的子查询，会被标记为drived（衍生查询）
- union：如果有两个 select 查询语句，他们之间用 union 连起来查询，那么第二个 select 会被标记为 union
- union result：union 的结果被标记为 union result，它的 id 被标记为null
# type
type表示的是扫描方式，代表 MySQL 使用了哪种索引类型，不同的索引类型的查询效率是不一样的
- system：系统表，少量数据，往往不需要进行磁盘IO
- const：常量连接
- eq_ref：主键索引(primary key)或者非空唯一索引(unique not null)等值扫描
- ref：非主键非唯一索引等值扫描
- range：范围扫描
- index：索引树扫描
- ALL：全表扫描(full table scan)
type扫描方式由快到慢：
system > const > eq_ref > ref > range > index > ALL
# system
从系统库mysql的系统标proxies_priv里查询数据，这里的数据在Mysql服务启动时候已经加载在内存中，不需要进行磁盘IO。
官方文档中的解释：该表只有一行（=系统表）。这是const联接类型的特例
# const
上例中id是主键(primary key)，连接部分是常量1，通过索引一次就能找到，速度非常快
场景：
- 命中主键(primary key)或者唯一索引(unique)
- 被连接的部分是一个常量值(const)
# eq_ref-唯一索引扫描
上例中对于前表user表（id是主键）中的每一行（row），对应后user_balance表（uid是主键）只有一行被扫描，这类扫描的速度也非常的快
场景：
- 联表(join)查询
- 命中主键(primary key)或者非空唯一索引(unique not null)
- 等值连接
# ref-非唯一索引扫描
联表查询
当user_balance的**uid改为普通非唯一索引（index）**后，对于前表user表的每一行(row)，后表user_balance表可能有多于一行的数据被扫描
单表查询
当user_balance的**uid改为普通非唯一索引（index）**后，常量的连接查询，也由const降级为了ref，因为非唯一索引所以有多于一行的数据被可能被扫描
ref每一次匹配可能有多行数据返回，虽然它比eq_ref要慢，但它仍然是一个很快的join类型
场景：
- 联表查询
- 普通非唯一索引
# range
range比较好理解，它是索引上的范围查询，它会在索引上扫码特定范围内的值，一般出现在 where 条件中的 between、and、<、>、in 等范围查找中。
# index
index类型，需要扫描索引上的全部数据，它仅比全表扫描快一点
# ALL
如果id上不建索引,则全表扫描
# possible_keys
此次查询中涉及字段上若存在索引，则会被列出来，表示可能会用到的索引，但并不是实际上一定会用到的索引
# key
此次查询中实际上用到的索引
# key_len
表示索引中使用的字节数，通过该属性可以知道在查询中使用的索引长度，注意：这个长度是最大可能长度，并非实际使用长度，在不损失精确性的情况下，长度越短查询效率越高
# ref
显示关联的字段。如果使用常数等值查询，则显示 const，如果是连接查询，则会显示关联的字段。
上图中，user表的id字段是主键，user_balance表的uid字段也是主键，user的name字段是非唯一性普通索引
- user表是非唯一性索引扫描（ref），实际使用的索引列为name，并且，由于user.name = 'zhangsan'的'zhangsan'是常量，所以ref字段为const。
- user_balance表示唯一索引扫描，它实际使用了主键索引，索引关联的字段是user表的id字段。
# rows
根据表信息统计以及索引的使用情况，大致估算说要找到所需记录需要读取的行数，rows 越小越好
# extra
根据表信息统计以及索引的使用情况，大致估算说要找到所需记录需要读取的行数，rows 越小越好
# using filesort
- 俗称 " 文件排序 " ，在数据量大的时候几乎是“九死一生”。
- 在 order by 或者在 group by 排序的过程中，order by 的字段不是索引字段，或者 select 查询字段存在不是索引字段，或者 select 查询字段都是索引字段，但是 order by 字段和 select 索引字段的顺序不一致，都会导致 fileSort
上图中的name字段不是索引字段，是普通字段。
# using temporary
使用了临时表保存中间结果，常见于 order by 和 group by 中。
# using index
表示相应的 select 操作中使用了覆盖索引（Coveing Index）,避免访问了表的数据行，效率不错！ 如果同时出现 using where，表明索引被用来执行索引键值的查找；如果没有同时出现 using where，表面索引用来读取数据而非执行查找动作。
上图的name字段是普通非唯一性索引，id是主键，这里使用了覆盖索引，避免了回表查询。
# using where
表明使用了 where 过滤
# 参考
MySQL索引优化，explain详细讲解 - 云+社区 - 腾讯云 (opens new window)
Mysql Explain之type详解 - 掘金 (opens new window)
# 总之
Mysql的优化主要就在于：索引的优化，sql语句的优化，表的优化，在高并发网络环境下，除了优化数据库外，还会涉及到分布式缓存，CDN，数据库读写分离等高并发优化技术。
以上就是MySQL优化的介绍，更加详细深入的视频讲解，请点击查看：最强MySQL性能优化合集 (opens new window)