mysql学习笔记2

引言

在日常的开发工作中，MySQL是使用得最多的关系型数据库。本文将从mysql的索引和慢查询的排查角度出发，分享一些在实际业务中sql优化的经验分享。

1、MySQL索引

索引是对数据库表中一列或多列的值进行排序的一种结构。MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

1.1 索引的数据结构

MySQL中常用的索引结构

索引	InnoDB	MyISAM	Memory
B+tree索引	支持	支持	支持
Hash 索引	不支持	不支持	支持
R-tree 索引	不支持	支持	不支持
Full-text	5.6版本之后支持	支持	不支持

我们平常所说的索引，如果没有特别指明，都是指 B+ 树结构组织的索引。因此我们简单介绍一下B+树。

MySQL 对经典的 B+Tree 进行了优化。在原 B+Tree 的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree ，提高区间访问的性能，利于排序。

 

它的特点是：

1.所有的叶子结点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。
2.所有的中间结点元素都同时存在于子结点，在子结点元素中是最大（或最小）元素。

这种结构有两个优点：

1.可以使得单一结点存储更多的元素，除了叶子结点，其他的结点只是包含了键，没有保存值，这样的话，树的高度就能有效降低，从而减少查询的IO次数；
2.同时，因为叶子结点包含了下个叶子结点的指针，所以范围查询的时候如果搜索到第一个叶子结点的话，就能根据指针指向查询后面的数据，不用再从根结点遍历了。这也是为什么很多大神建议表的主键设计成自增长的好，因为这样范围查询能提高效率

 

1.2 索引的分类

按作用范围划分：

• 主键索引：一张表只能有一个主键索引，不允许重复、不允许为 NULL；
• 唯一索引：数据列不允许重复，允许为 NULL 值，一张表可有多个唯一索引，索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
• 普通索引：一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许 NULL 值插入；
• 全文索引：它查找的是文本中的关键词，主要用于全文检索。
• 联合索引：它其实不是一种索引分类，就是包含多个字段的普通索引，比如有个联合索引为index(a，b)，查找的时候可以用 a and b 作为条件。

 

物理划分：

• 聚簇索引（clustered index）不是单独的一种索引类型，而是一种数据存储方式。这种存储方式是依靠B+树来实现的，根据表的主键构造一棵B+树且B+树叶子节点存放的都是表的行记录数据时，方可称该主键索引为聚簇索引。聚簇索引也可理解为将数据存储与索引放到了一块，找到索引也就找到了数据。
• 非聚簇索引：数据和索引是分开的，B+树叶子节点存放的不是数据表的行记录。

虽然InnoDB和MyISAM存储引擎都默认使用B+树结构存储索引，但是只有InnoDB的主键索引才是聚簇索引，InnoDB中的辅助索引以及MyISAM使用的都是非聚簇索引。每张表最多只能拥有一个聚簇索引。

覆盖索引：搜索的索引键中的字段恰好是查询的字段（或是组合索引键中的其它字段）。覆盖索引的查询效率极高，原因在于不用做回表查询。

最左前缀匹配原则：在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

1.3 索引的使用

索引的创建原则

主键索引的选择：

• InnoDB中主键不宜定义太大，因为辅助索引也会包含主键列，如果主键定义的比较大，其他索引也将很大。如果想在表上定义 、很多索引，则争取尽量把主键定义得小一些。InnoDB 不会压缩索引。
• InnoDB中尽量不使用非单调字段作主键（不使用多列），因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

 

索引创建主要遵循的原则有：

• 字段的选择上，经常需要搜索的列，比如where条件经常用到的字段；其他表的外键字段，作为连接表的条件字段，可以有效加快连表查询速度；查询中作为排序、统计或者是分组的字段；
• 最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
• =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。
• 尽量选择区分度高的列作为索引，这样的列也应该放到联合索引前面，区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。
• 索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。
• 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

同样，对于有些字段不应该创建索引：

• 频繁更新的字段不适合创建索引，因为每次更新不单单是更新记录，还会更新索引，保存索引文件
• where条件里用不到的字段，不创建索引；
• 表记录太少，不需要创建索引；
• 尽量避免在联合索引中包含大型列，比如对于那些定义为text，image类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少，不利于使用索引，如果要使用，可以考虑建立前缀索引；
• 数据重复且分布平均的，即区分度低的字段，这种字段建立索引就没有太大的效果，例如性别字段，只有男女，不适合建立索引。

 

在查询中使用索引

查询语句中可以用到索引的关键字

1. distinct  
2. where  
3. 集合比较， in 和 exsit都可以用到索引。但是not in 或者 not exsit用不到索引。
4. 文本匹配，like，like ‘name%’
5. 数值比较，between=，>=,<=,> , <
6. order by 
7. group by 
8. 聚集函数 用不到索引 

 

查询语句的改写

(1)  避免 SELECT *，只查询需要的字段。

(2)  小表驱动大表，即小的数据集驱动大的数据集。

(3)  尽量使用连接代替子查询，join不需要生成临时表。or 查询改写成 union 查询。

(4) 分解关联查询，把三次以上的联表拆分成多个语句。

(5) limit偏移量过大时，可以利用主键索引优化查询

select * from table_name as table inner join ( select id from table limit 3000000,10 ) as tmp on tmp.id=table.id;

(6) 避免在索引列上使用 is null 和 is not null

 

针对查询语句创建联合索引

（1） ORDER BY的索引优化。如果一个SQL语句形如：

SELECT [column1],[column2],.... FROM [TABLE] ORDER BY [sort];

在[sort]这个栏位上建立索引就可以实现利用索引进行order by 优化。

（2）WHERE + ORDER BY的索引优化，形如：

SELECT [column1],[column2],....... FROM [TABLE] WHERE [columnX] = [value] ORDER BY [sort];

建立一个联合索引(columnX,sort)来实现order by 优化。

如果columnX对应多个值，如下面语句就无法利用索引来实现order by的优化

SELECT [column1],[column2],....... FROM [TABLE] WHERE [columnX] IN ([value1],[value2],.....) ORDER BY[sort];

（3）WHERE+ 多个字段ORDER BY

SELECT * FROM [table] WHERE uid=1 ORDER x,y LIMIT 0,10;

建立索引(uid,x,y)实现order by的优化,比建立(x,y,uid)索引效果要好得多。

 

1.4 索引的优缺点总结

优点：

• 索引大大减小了服务器需要扫描的数据量，从而大大加快数据的检索速度，这也是创建索引的最主要的原因。
• 索引可以帮助服务器避免排序和创建临时表
• 索引可以将随机IO变成顺序IO
• 索引对于InnoDB（对索引支持行级锁）非常重要，因为它可以让查询锁更少的元组，提高了表访问并发性
• 关于InnoDB、索引和锁：InnoDB在二级索引上使用共享锁（读锁），但访问主键索引需要排他锁（写锁）
• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
• 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
• 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
• 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

缺点：

• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
• 索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间，如果需要建立聚簇索引，那么需要占用的空间会更大
• 对表中的数据进行增、删、改的时候，索引也要动态的维护，这就降低了整数的维护速度
• 如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
• 对于非常小的表，大部分情况下简单的全表扫描更高效；

 

 

2 MySQL慢查询

2.1 什么是慢查询

慢查询日志是MySQL提供的一种日志记录，用来记录在MySQL中响应时间超过阀值的语句。具体来说，运行时间超过long_query_time值的SQL语句，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是记录运行10秒以上的语句。

现在配置的慢查阈值是500ms。

对于进行中的慢查，可以使用 show processlist 指令查看当前正在执行的进程来发现慢查的sql和持有锁的sql，但一般业务上都会有监控直接告诉我们出现慢查的语句，因此我们可以直接对慢查进行分析。

 

2.2 通过explain来分析性能

2.2.1 explain的作用

explain是MySql中用来分析SQL语句执行效率的一条命令，通过explain命令我们可以知道以下信息：表的读取顺序，数据读取操作的类型，哪些索引可以使用，哪些索引实际使用了，表之间的引用，每张表有多少行被优化器查询等信息

主要根据extra字段来查看是否出现了Using temorary或者using fileSort；通过key来查看是否用了索引。

 

2.3 各字段含义

主要有extra（用于查看是否出现了Using temorary或者using fileSort），key（用于查看是否用了索引）

2.1 id

表示select子句的执行顺序。id相同，执行顺序由上而下。

2.2 select_type

表示查询中每个select子句的类型

 

2.3 table

查询涉及到的表名

2.4 type

查询类型，效率由高到底如下：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all

一般来说，好的sql查询至少达到range级别，最好能达到ref。

 

1. const，通过主键访问

2. eq_ref，唯一的索引扫描，只有一条记录与之匹配，常见于主键索引

3. ref，通过普通索引扫描，返回匹配某个单值所有行，如下，col1是普通索引字段。

4. range，相对于ref而言，返回一个索引字段满足的一个范围

5. index，扫描整个索引表

6.all，全表扫描

 

2.5 possible_keys

possible_keys可能对应多个索引，但是在mysql中，一个表上查询语句只能用到一个索引。如果对于联合查询的sql语句，也是一个表对应一个索引，有n个表，最多使用n个索引。

2.6 key

在实际查询中这正使用到哪个索引，对于一个表，使用到的索引个数或者为0，或者为1。

2.7 key_len

对应key字段中索引对应的字段的长度，可通过该列计算查询中使用的索引的长度(key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的)

不损失精确性的情况下，长度越短越好。

2.8 ref

表示on后面的字段，联表查询时，连接的字段。

2.9 rows

估算执行计划中每一个查询找到匹配行所需要读取的行数。mysql默认进了优化，将rows较小的作为驱动表。即如果使用了order by 结果集较多的字段，那么此时即使我们写的sql语句中把大结果集的表作为驱动表，mysql优化仍然会将其作为被驱动表，此时可以使用Straight_Join。

2.10 filtered

filtered表示通过查询条件获取的最终记录行数占通过type字段指明的搜索方式搜索出来的记录行数的百分比。因此一个比较低filtered值表示需要有一个更好的索引。

2.10 Extra

1. Using index

表示相应的select语句中使用了覆盖索引。Using index condition表示用到索引下推的优化特性

2. Using where

如果查询未能使用索引，Using where只是提醒我们MySL将用where子句进行结果过滤。

3. Using temorary

嵌套查询的时候或者group或者被驱动表使用了order by，表示MySQL将使用临时表来存储结果集,常见于分组和排序查询。

4. Using filesort

MySQL将无法使用索引进行排序的的操作称为”file sort”。

5. Using join buffer

表示在进行表关联的时候，没有用到索引，使用了连接缓存区存储临时结果。

 

2.4 对explain的总结

explain不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况

explain不考虑各种Cache

explain不能显示MySQL在执行查询时所作的优化工作

部分统计信息是估算的，并非精确值

explain只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划