高性能mysql学习笔记总结

（架构及历史）

Mysql逻辑架构

（索引1）

简介

索引（Mysql里面成为键（KEY））是存储引擎用于快速找到记录的数据结构。

我们都知道，索引对于大数据量的查询来说非常有用；当然，不恰当的索引在数据量很大的时候对性能的影响也会很大。

索引类型

B-Tree（实际上是B Tree）

正常情况下，我们说的Mysql的索引就是B Tree实现的，其每个叶子节点有子叶子节点的指针，这些指针同时还存储这其子叶子节点的值的上下限。B Tree对索引列是顺序组织存储的，所以很适合查找范围数据。

哈希索引

哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。

其他

空间数据索引（R-Tree）、全文索引、其他第三方索引

B Tree索引使用举例

索引的优点

除了可以快速定位到要查的数据行外，以B Tree为例，其是顺序存储的，所以还可以用来做ORDER BY或GROUP BY操作；另外索引中存储了实际的值，如果查询的值只存在与索引中的话可以直接从索引中得到。

索引大大的减少了数据库需要检索的数据量。
索引可以帮助服务器避免排序和临时表。
索引可以将随机I/O变为顺序I/O。
索引优化

独立的列

就是所用的索引不能是表达式或者函数的一部分。

比如where id 1 = 5

前缀索引和索引选择性

前缀索引

前缀索引是指如果是varchar类型的列，只用前面的一部分作为索引，从而节省索引空间，但是这样就不能直接从索引中得到数据了（因为索引只存了一部分）。

索引选择性

索引选择性是指不重复的索引值（也叫基数）与所有数据（#T）的比。范围是1/#T到1之间。当然选择性为1的时候是最好的，一般我们的主键索引就是1。

文本类型的列mysql是不允许把全部内容作为索引的。这时候我们就要选择足够长的内容保证选择性，而又不能太长导致存储空间过大。

书中给出的方案就是先以前十为样本，看总量（COUNT(*)），然后截取量（LEFT(xxx, n)）一点点增加，如果和总量相似，则可以把这个长度作为前缀。

另外一个方案就是COUNT(DISTINCT xxx)/COUNT(*)与COUNT(DISTINCTLEFT(xxx, n))/COUNT(*)比较，n一点点增大，当比例比较相似的时候则可以使用此长度。

多列索引

多列索引需要注意的一个问题就是索引的顺序。有的人以为为每列都做索引比较好，其实这是个错误的认识。虽然mysql后来有了索引合并的功能，但是性能并不是很好，同时也说明这个索引建立的不是最优。

索引列顺序

B Tree是按照顺序存储的索引，当有多列索引存在时，mysql是首先按照第一列的顺序存储，之后按照第二列顺序存储。

有个三星索引的概念：索引将相关的记录放在一起获得第一星；如果索引中的数据顺序和查找中的排列顺序一致，获得第二星；如果索引中的列包含了查询中包含的全部列则获得第三星。

通常情况下，索引的排序应该是，排序列、分组列、选择性列排序。

聚簇索引

聚簇就是聚集在一起的意思嘛。

其实一般情况下，我们定义的主键就会是聚簇索引，而如果我们不定义主键的话，mysql也会选择一个非空的唯一索引来作为聚簇索引，如果没有这样的索引，mysql也会隐式的给我们定义一个主键作为聚簇索引。

优点

相关的数据保存在一起。
访问数据快，聚集索引和数据行同时保存在BTree中。
使用覆盖索引的时候可以直接使用页节点上的主键值。

缺点

如果数据都在内存，则聚簇索引就没什么用了。
不是顺序插入的话速度会比较慢。（其实用自增逐渐就可以了，UUID这种就会超级慢）
更新聚簇索引的列的代价很高。
如果行比较稀疏的话全表扫描会比较慢。
二级索引包含主键、二级索引需要两次查询（先找到主键、再通过主键查询数据行）。

非聚簇索引（二级索引）

二级索引就是除了主键外的其他索引。

由于主键及数据行存储在了B Tree中，通过主键索引的查询会非常快，但是二级索引至存储了主键，所以需要造成二次查询。

覆盖索引

索引查询数据确实会很快，但是Mysql也可以直接从索引中获取数据。如果要查询的所有数据都在索引中，Mysql就不需要再去数据行中读取数据了。

如果一个索引包含所有要查询的字段，那么这个索引称为覆盖索引。

而二级索引都会额外包含主键索引，所以二级索引列及主键也可以成为覆盖索引，完成查询。

使用索引扫描做排序

Mysql有两种方式排序，一种是对结果排序，一种是直接通过索引扫描排序。

要用索引排序，除非索引列的顺序和ORDER BY的顺序一致并且顺序（要么DESC，要么ASC）也一致，才能使用索引扫描排序（如果不一致，可以把其中之一改为相反数或反串）。

而这个排序的限制是ORDER BY子句的第一个字段必须为索引的最左前缀，否则就不能用索引排序。

这里有个例外，就是如果排序索引的前面列被指定为常数，则可以使用。

比如有索引(date, id1, id2)，语句如下：

select … from xxx where date = ‘2015-04-30’ ORDER BY id1, id2

这时候第一个索引date为常量，可以使用第二、三列索引完成索引排序。

冗余索引和重复索引

有的人可能对一个字段做了多种类型的索引，这其实是多余的（重复索引）。

但是冗余索引和重复索引还是不一样的。如果创建了索引(A, B)又创建了索引(A)则是冗余索引，其实(A)就是(A, B)的前缀，完全可以用(A, B)取代（对于B Tree来说）。但如果创建索引(B)就不一样了。

未使用的索引

有些索引我们当初建表的时候想当然的就创建了，但可能实际上我们根本就没有使用。大家都知道索引会导致表数据更新的时候的效率变低，这样的索引实际上就是累赘，我们应该删除他们。

我们可以使用Percona Toolkit的pt-index-usage来读取查询日志，并对日志中的每条查询进行EXPLAIN，然后打印出关于索引和查询的报告。这样我们就会发现哪些索引几乎没有使用过，可以果断删掉了。

（索引2）

索引优化案例

我就以书中的案例给大家介绍一下。

假设要设计一个在线约会网站，用户信息表包含这些列：国家、地区、城市、性别、眼睛颜色等。其需要设计根据用户各种信息进行搜索，还需要根据用户的最后上线时间、其他会员对用户的评分排序。

尽量用到索引排序

使用索引排序会严格限制到索引的设计与查询。如果希望根据用户评分排序，就没法使用索引查询年龄范围；如果使用范围查询，就没法使用其他索引（其后面的索引）进行排序了。如果这个where条件很常用，那还是数据查出来在排序吧。

支持多种过滤条件

country、sex选择性其实并不高，国家多数就那么几个，sex一般情况也没几个，但是这种查询条件基本上都会用到，甚至有的时候sex都是单选的，所以这些选项加入到索引中也是可以的，索引为(sex, country)。如果需要查多个情况的时候，我们可以使用in查询，这时候可以查询到需要的数据，也可以用到前缀索引。

但是in的条件还是不宜过长。虽然年龄也可以用in完成，使之不用变为范围索引，但是年龄的范围多数不是很短。

所以我们应该尽量把age放到最后，因为一般会用age >= xx and age =< xx，这样age索引用后，再往后的索引就不能用了，很少会出现age=xx的使用方式。

同样我们可以把眼睛颜色、头发颜色使用in来处理，但是这种方式实际上会出现指数增长现象。等于实际上的组合形式是各种in的数量相乘。

避免多个范围条件

比如我们想查询近一周（7天）登录过的用户，以及年龄范围的用户，这样无论如何都只能使用一个索引了。因为范围索引后的索引列都不能使用了。当然我们可以把年龄用in了，但是年龄的范围数量真的有点多。

这里同样提供了一种方式，我们可以设置不让用户输入时间段，而采用近1天登录过、近3天登录过、近7天登录过、近半月登录过、近1月登录过等选项让用户选择。

然后增加一个字段，每次用户登录把值设置为0，然后由定时任务处理，把符合上述要求的用户分别赋值为1、2、3等，这样只需使用=条件即可完成，而非范围索引。

优化排序

当我们执行排序的时候，我们使用select * from xxx order by xxx limit xx, xx的时候，就算有索引，翻页到很后面性能会很低，由于每次都会去关联到指定行去判断数据，所以IO很高。

这时候可以使用延迟关联的方式来更高效的使用索引。

select * from xxx inner join (

select pk from xxx order by xxx limit xx, xx

) as x using(pk)

这样可以利用二级索引自带主键索引，进行二次查询完成优化。

索引与表的维护

维护表有三个主要目的：找到并修复损坏的表、维护准确的索引统计信息、减少碎片。

找到并修复损坏的表

表损坏通常都是系统或者硬件问题，如果你发现了什么莫名其妙的问题，试着使用check table命令来检查是否发生了表损坏。一般innodb引擎的表，我们使用如下命令可以修复表：

ALTER TABLE xxx ENGINE=INNODB;

如果innodb的表发生了损坏，一般是很严重的问题，因为innodb一般不会损坏。如果损坏了，要么是硬件问题、要么是DBA的一些错误操作，比如在Mysql外部操作的文件。

更新统计信息

可以通过命令ANALYZE TABLE来重新生成统计信息（比如行数，INNODB通常是不准的）。

可以使用SHOW INDEX FROM xx来查看索引的基数（Cardinality）。这个会有很多信息，需要注意的是，Cardinality给出了（估算）这个索引列有多少不同的值。

减少索引和数据碎片

BTree所以可能会碎片化，这会严重影响查询的效率。碎片化的索引可能会以很差或无序的方式存储在磁盘上。

有三种类型的数据碎片：

行碎片：这种碎片指的是数据行被存储为多个地方的多个片段中。即使查询只从索引中访问一行记录，行碎片也会导致性能降低；
行间碎片：行碎片是指逻辑上顺序的页，或者行在磁盘上不是顺序存储的；
剩余空间碎片：剩余空间碎片是指数据页中有大量的空余空间，这会导致服务器读取大量不需要的数据，从而造成浪费。

对于MySIAM存储引擎，这三种碎片都会出现。

对于InnoDB，行碎片不会出现，InnoDB会移动短小的行并重写到片段中。

可以使用Optimize table 表名来整理数据。如果不支持这个命令，可以使用ALTER TABLE xxx ENGINE=INNODB;这个命令来重建表。

索引小结

在创建索引或者利用这些索引编写查询语句的时候，应注意以下三点：

单行访问是很慢的。如果服务器从存储中读取一个数据块只为一行数据，那么就浪费了很多的工作。最好读取的块能够尽可能多的包含需要的行数据。
按顺序访问范围数据是很快的，并且GROUP BY操作也无须为排序和按组聚合消耗太多性能了。
索引覆盖查询是很快的。如果查询的结果可以直接从索引中得到，可以省去二次查询获取行数据。

其实多数情况我们都希望能够设计一个完美的三星索引适合所有查询，但是这真的很难，我们需要取舍，对于常用查询必须有很好的索引，对于一些比较少用的查询，我们可以容许对查询出的数据块做排序、筛选

（查询性能优化）

优化mysql，除了需要最优的库表设计、很好的索引设计以外，还需要编写合理的查询。如果查询写的比较烂，索引有可能根本就用不上。那样不管库表、索引设计的再好也没用。

下面会介绍如何写出高效的查询语句，以及明白高效与低效的原因。

为什么查询速度会慢

查询需要快速，主要是要响应时间快。

查询需要消耗的时间如下：网络、CPU计算、生成统计信息和执行计划、锁等待（互斥等待）等，尤其是向底层存储引擎检索数据的时候的这些调用。这些调用需要在内存操作、CPU操作和内存不足时导致的IO操作上消耗时间。

有些操作我们做了一些不必要的额外操作、甚至重复调用。所以我们需要优化这些查询，去优化和消除这些操作花费的时间。

慢查询基础：优化数据访问

查询性能低的最主要原因就是访问的数据过多。有时候我们可能并不总是需要那么多的数据，但是我们仍然去访问那么多的数据。

通常我们从两方面去分析会比较有效：

1、确认应用程序是否在检索大量超过需要的数据。通常是访问了太多的行，也有可能是访问了太多的列。
2、确认Mysql是否在分析大量超过需要分析的数据。

是否向数据库请求了不需要的数据

有些查询会请求超过需要的数据，然后这些多余的数据会被应用程序丢弃。这会给Mysql服务器带来额外的负担，并增加网络开销，另外也会消耗应用服务器的CPU和内存资源。

下面是一些常见的错误：

查询不需要的记录

使用ResultSet返回结果集的时候，我们可能只需要前10条，一些人会天真的以为应用程序是一个循环，拿一条数据，我们关闭结果集，就不会继续查询了。实际上Mysql不会这样，而会全部查出，然后存在内存里。这种情况我们最好使用LIMIT来截取数据。

多表关联时返回全部列

join时最好只取需要的列，减少子查询内容（有时更可以让子查询使用覆盖查询）。

总是取出全部的列

使用select *进行查询，当我们看到这样的语句的时候，总要用怀疑的眼光看看是否真的需要所有的列，这样会导致查询无法使用索引覆盖查询。不过很多人觉得这样可以简化开发，同时这样写其实有时候可以使用到查询缓存。

多次查询相同的数据

有时候一个接口需要几处相同的数据，我们尽可能的把一样的数据通过一次查询获取出来。不要一遍遍的查询，耗费性能。

Mysql是否在扫描额外的记录

在确定只返回了需要的记录之后，我们需要在确定下是否只扫描了需要的记录。衡量Mysql查询开销最简单的三个准则就是：

响应时间
扫描行数
返回行数

这三个指标都会记录到Mysql的慢日志中，我们主要查看是否扫描了过多的行这点最为重要。

响应时间

响应时间其实只是表面上的值。

响应时间=服务时间 排队时间。

一般比较常见的排队等待时间是消耗在了I/O及锁上。一般这个并不是很好分析时间是否在一个合理的范围，多数是凭经验去判断这样一个查询，它的响应时间是否在一个合理的值。

扫描的行数和返回的行数

理想情况下，扫描的行数和返回的行数应该是相同的，但是理想的情况往往很少，尤其是在做关联查询的时候。但是我们也应该尽可能的减少扫描的行数。

扫描行数和访问类型

Mysql有好几种方式可以查询一条数据，有些方式需要扫描好多行才能获得一条数据，有些方式不需要扫描就能获取数据。

在EXPLAIN语句返回的type列反映了返回类型。访问类型有很多种：从全表扫描到索引扫描、范围扫描、唯一索引扫描、常数引用等。这里列的是速度从慢到快，扫描的行数也少从多到少。

如果查询没有办法找到合适的访问类型，那么最好的办法就是为之建立一个合适的索引。

比如select * from xxx where id=1

这种查询，当id是主键索引的时候，我们使用EXPLAIN的时候发现，type是ref，其另一个参数值rows为1也说明了这个查询只需要访问一条数据。如果没有索引呢，这时候type会是ALL，同时rows一般会比较大，约等数据条数，另外这里的Extra参数会显示为Using where，标识Mysql是通过where条件来确定数据行的。

一般Mysql通过三种方式应用where条件：

在索引中使用where条件过滤不匹配的记录，这是在存储引擎层做的。
使用覆盖索引返回记录（Extra会提示为Using index），直接从索引过滤并返回需要的记录。
从数据表返回数据然后过滤数据（Extra会提示为Using where）。

所以说创建好的索引可以大大优化我们的查询性能，但也并不总是可以优化，有时候确实是只能访问很多数据而没有什么好的索引能够帮忙。

一般我们如果发现，一个查询扫描的行比返回的行多很多，我们通常可以采用下面的方法去解决：

使用索引覆盖扫描。
改变库表结构，比如使用单独的汇总表。
重写查询语句，让Mysql能够更好的优化这个查询（后面会说）

 

您可能感兴趣的文章：
影响MySQL性能的查询类型有哪些
分享：mysql随机查询若干条数据的方法
mysql动态游标与mysql存储过程游标(示例)
mysql随机查询大量数据的sql语句性能分析
mysql数据库性能优化技巧
Golang笔记：语法，并发思想，web开发，Go微服务相关
mysql分页limit优化方法
mysql视图的学习笔记总结
笔记本电脑散热不好什么原因,笔记本散热差的解决办法
高性能mysql(第二版)之查询性能优化