【Mysql】大量数据查询时的优化相关知识

今天在查询大量数据时，遇到了查询速度较慢的问题，特地搜集整理了相关的问题解决办法。

一、对于sql的优化

1 使用explain判断sql语句是否使用了索引

1. explain能够干什么

• 读取表的顺序
• 哪些索引能够被使用
• 数据读取操作的操作类型
• 哪些索引能够被实际使用
• 表之间的引用
• 每张表有多少行被物理查询

2. explain各个字段代表的意思

• id ：select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
• select_type ：查询类型 或者是 其他操作类型
• table ：正在访问哪个表
• partitions ：匹配的分区
• type ：访问的类型
• possible_keys ：显示可能应用在这张表中的索引，一个或多个，但不一定实际使用到
• key ：实际使用到的索引，如果为NULL，则没有使用索引
• key_len ：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度
• ref ：显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值
• rows ：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数
• filtered ：查询的表行占表的百分比
• Extra ：包含不适合在其它列中显示但十分重要的额外信息

3. id与table字段

• 通过id和table可以完全判断出你的每一条sql语句的执行顺序和表的查询顺序
• id越大的表table读取顺序越靠前，id相同的表table读取顺序自上而下

4. select_type字段

• SIMPLE 简单查询，不包括子查询和union查询
• PRIMARY 当存在子查询时，最外面的查询被标记为主查询
• SUBQUERY 子查询
• UNION 当一个查询在UNION关键字之后就会出现UNION
• UNION RESULT 连接几个表查询后的结果
• DERIVED 在FROM列表中包含的子查询被标记为DERIVED(衍生)，MySQL会递归执行这些子查询，把结果放在临时表中。MySQL5.7+ 进行优化了，增加了derived_merge（派生合并），默认开启，可加快查询效率。

5. partitions字段

• 该列显示的为分区表命中的分区情况，非分区表该字段为空null

6. type字段

NULL > system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL （越靠左边的越优秀）

• NULL：MySQL能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引，NULL的前提是你已经建立了索引！
• SYSTEM：表只有一行记录（等于系统表），这是const类型的特列，平时不大会出现，可以忽略。
• const：表示通过索引一次就找到了，const用于比较primary key或unique索引，因为只匹配一行数据，所以很快，如主键置于where列表中，MySQL就能将该查询转换为一个常量。简单来说，const是直接按主键或唯一键读取。
• eq_ref：用于联表查询的情况，按联表的主键或唯一键联合查询。多表join时，对于来自前面表的每一行，在当前表中只能找到一行。这可能是除了system和const之外最好的类型。当主键或唯一非NULL索引的所有字段都被用作join联接时会使用此类型。
• ref：可以用于单表扫描或者连接。如果是连接的话，驱动表的一条记录能够在被驱动表中通过非唯一（主键）属性所在索引中匹配多行数据，或者是在单表查询的时候通过非唯一（主键）属性所在索引中查到一行数据。
• ref_or_null：类似ref，但是可以搜索值为NULL的行
• index_merge：表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取多个索引，性能可能大部分时间都不如range。
• range：索引范围查询，常见于使用 =, <>, >, >=, <, <=, IS NULL, <=>,BETWEEN, IN()或者like等运算符的查询中
• index：index只遍历索引树，通常比All快。因为，索引文件通常比数据文件小，也就是虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘读的。
• ALL 如果一个查询的type是All,并且表的数据量很大，那么请解决它！！！

7. possible_keys字段

• 这个表里面存在且可能会被使用的索引，可能会在这个字段下面出现，但是一般都以key为准。

8. key字段

• 实际使用的索引，如果为null,则没有使用索引，否则会显示你使用了哪些索引，查询中若使用了覆盖索引（查询的列刚好是索引），则该索引仅出现在key列表。

9. ref字段

• 显示哪些列被使用了，如果可能的话，最好是一个常数。哪些列或常量被用于查找索引列上的值

10. rows字段和Filter字段

• rows是根据表的统计信息和索引的选用情况，优化器大概帮你估算出你执行这行函数所需要查询的行数。
• Filter是查询的行数与总行数的比值。其实作用与rows差不多，都是数值越小，效率越高。

11. extra字段

• 这一字段包含不适合在其他列显示，但是也非常重要的额外信息。
• Using filesort：表示当SQL中有一个地方需要对一些数据进行排序的时候，优化器找不到能够使用的索引，所以只能使用外部的索引排序，外部排序就不断的在磁盘和内存中交换数据，这样就摆脱不了很多次磁盘IO，以至于SQL执行的效率很低。
• Using tempporary：表示在对MySQL查询结果进行排序时，使用了临时表,这样的查询效率是比外部排序更低的，常见于order by和group by。
• Using index 表示使用了索引，很优秀👍。
• Using where 使用了where但是好像没啥用。
• Using join buffer 表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些。
• impossible where 筛选条件没能筛选出任何东西
• distinct 优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

2 创建索引

1. 使用CREATE INDEX创建
   • CREATE INDEX indexName ON tableName (ColumnName,ColumnName…);
2. 使用ALTER语句创建
   • ALTER TABLE tableName ADD INDEX indexName(columnName);
3. 建表时创建

3 联合索引

• 联合索引的重点就是最左匹配原则，就是说你的sql语句中用到了联合索引中的最左边的索引，那么这条sql语句就可以利用这个联合索引去进行匹配，但是当遇到了范围查询（>,<,between,like）就会停止匹配，但是遇到的这个范围查询还是能使用到索引，只是之后的语句就用不到了
• 在创建联合索引时，要将区分度高的字段放在前面，区分度低的字段放在后面
• IN视为等值查询，可以继续最左匹配
• 如果 ORDER BY x前面是范围查询，则不用将x放入联合索引中，因为，在范围查询里x是无序的，如果前面是等值查询，则可以放入

4 覆盖索引

• 覆盖索引是一种特殊的联合索引，可以减少回表查询次数
• 覆盖索引的目的就是避免发生回表查询，通过覆盖索引，只需要扫描一次 B+ 树即可获得所需的行记录。
• 覆盖索引即要查询的值作为索引在其联合索引中

5 聚集索引

• 聚集索引就是按照每张表的主键构造一棵 B+ 树，同时叶子节点中存放的即为表中一行一行的数据
• 聚集索引一般都是加在主键上的

6 辅助索引（Secondary Index）

• 辅助索引也称为 非聚集索引、二级索引。其和聚集索引的最大区别就在于，辅助索引的叶子节点并不包含行记录的全部数据。
• 辅助索引的叶子节点包含的是：每行数据的辅助索引键 + 该行数据对应的聚集索引键。

二、对于数据库的优化

1 优化现有mysql数据库。

• 优点：不影响现有业务，源程序不需要修改代码，成本最低。
• 缺点：有优化瓶颈，数据量过亿就玩完了。

2 升级数据库类型，换一种100%兼容mysql的数据库。

• 优点：不影响现有业务，源程序不需要修改代码，你几乎不需要做任何操作就能提升数据库性能。
• 缺点：多花钱。

3 一步到位，大数据解决方案，更换newsql/nosql数据库。

• 优点：扩展性强，成本低，没有数据容量瓶颈。
• 缺点：需要修改源程序代码

方案一具体做法

1. 数据库设计和表创建时就要考虑性能
  mysql数据库本身高度灵活，造成性能不足，严重依赖开发人员能力。
设计表时要注意：

• 1.表字段避免null值出现，null值很难查询优化且占用额外的索引空间，推荐默认数字0代替null。
• 2.尽量使用INT而非BIGINT，如果非负则加上UNSIGNED（这样数值容量会扩大一倍），当然能使用TINYINT、SMALLINT、MEDIUM_INT更好。
• 3.使用枚举或整数代替字符串类型
• 4.尽量使用TIMESTAMP而非DATETIME
• 5.单表不要有太多字段，建议在20以内
• 6.用整型来存IP

索引：

• 1.索引并不是越多越好，要根据查询有针对性的创建，考虑在WHERE和ORDER BY命令上涉及的列建立索引，可根据EXPLAIN来查看是否用了索引还是全表扫描
• 2.应尽量避免在WHERE子句中对字段进行NULL值判断，否则将导致引擎放弃使用索引而进行全表扫描
• 3.值分布很稀少的字段不适合建索引，例如"性别"这种只有两三个值的字段
• 4.字符字段只建前缀索引
• 5.字符字段最好不要做主键
• 6.不用外键，由程序保证约束
• 7.尽量不用UNIQUE，由程序保证约束
• 8.使用多列索引时主意顺序和查询条件保持一致，同时删除不必要的单列索引

简言之就是使用合适的数据类型，选择合适的索引

选择合适的数据类型:

• （1）使用可存下数据的最小的数据类型，整型 < date,time < char,varchar < blob
• （2）使用简单的数据类型，整型比字符处理开销更小，因为字符串的比较更复杂。如，int类型存储时间类型，bigint类型转ip函数
• （3）使用合理的字段属性长度，固定长度的表会更快。使用enum、char而不是varchar
• （4）尽可能使用not null定义字段
• （5）尽量少用text，非用不可最好分表

选择合适的索引列:

• （1）查询频繁的列，在where，group by，order by，on从句中出现的列
• （2）where条件中<，<=，=，>，>=，between，in，以及like 字符串+通配符（%）出现的列
• （3）长度小的列，索引字段越小越好，因为数据库的存储单位是页，一页中能存下的数据越多越好
• （4）离散度大（不同的值多）的列，放在联合索引前面。查看离散度，通过统计不同的列值来实现，count越大，离散程度越高

2. sql的编写需要注意优化

• 1.使用limit对查询结果的记录进行限定
• 2.避免select *，将需要查找的字段列出来
• 3.使用连接（join）来代替子查询
• 4.拆分大的delete或insert语句
• 5.可通过开启慢查询日志来找出较慢的SQL
• 6.不做列运算：SELECT id WHERE age + 1 = 10，任何对列的操作都将导致表扫描，它包括数据库教程函数、计算表达式等等，查询时要尽可能将操作移至等号右边
• 7.sql语句尽可能简单：一条sql只能在一个cpu运算；大语句拆小语句，减少锁时间；一条大sql可以堵死整个库
• 8.OR改写成IN：OR的效率是n级别，IN的效率是log(n)级别，in的个数建议控制在200以内
• 9.不用函数和触发器，在应用程序实现
• 10.避免%xxx式查询
• 11.少用JOIN
• 12.使用同类型进行比较，比如用’123’和’123’比，123和123比
• 13.尽量避免在WHERE子句中使用!=或<>(不等于)操作符，否则将引擎放弃使用索引而进行全表扫描
• 14.对于连续数值，使用BETWEEN不用IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5
• 15.列表数据不要拿全表，要使用LIMIT来分页，每页数量也不要太大

引擎
  目前广泛使用的是MyISAM和InnoDB两种引擎：
MyISAM
  MyISAM引擎是MySQL 5.1及之前版本的默认引擎，它的特点是：

• 1.不支持行锁，读取时对需要读到的所有表加锁，写入时则对表加排它锁
• 2.不支持事务
• 3.不支持外键
• 4.不支持崩溃后的安全恢复
• 5.在表有读取查询的同时，支持往表中插入新纪录
• 6.支持BLOB和TEXT的前500个字符索引，支持全文索引
• 7.支持延迟更新索引，极大提升写入性能
• 8.对于不会进行修改的表，支持压缩表，极大减少磁盘空间占用

InnoDB

  InnoDB在MySQL 5.5后成为默认索引，它的特点是：

• 1.支持行锁，采用MVCC来支持高并发
• 2.支持事务
• 3.支持外键
• 4.支持崩溃后的安全恢复
• 5.不支持全文索引

  总体来讲，MyISAM适合SELECT密集型的表，而InnoDB适合INSERT和UPDATE密集型的表MyISAM速度可能超快，占用存储空间也小，但是程序要求事务支持，那就只能选择InnoDB。

3. 分区

  MySQL在5.1版引入的分区是一种简单的水平拆分，用户需要在建表的时候加上分区参数，对应用是透明的无需修改代码。
  对用户来说，分区表是一个独立的逻辑表，但是底层由多个物理子表组成，实现分区的代码实际上是通过对一组底层表的对象封装，但对SQL层来说是一个完全封装底层的黑盒子。MySQL实现分区的方式也意味着索引也是按照分区的子表定义，没有全局索引
  用户的SQL语句是需要针对分区表做优化，SQL条件中要带上分区条件的列，从而使查询定位到少量的分区上，否则就会扫描全部分区，可以通过EXPLAIN PARTITIONS来查看某条SQL语句会落在那些分区上，从而进行SQL优化。

分区的好处是：

• 1.可以让单表存储更多的数据
• 2.分区表的数据更容易维护，可以通过清除整个分区批量删除大量数据，也可以增加新的分区来支持新插入的数据。另外，还可以对一个独立分区进行优化、检查、修复等操作
• 3.部分查询能够从查询条件确定只落在少数分区上，速度会很快
• 4.分区表的数据还可以分布在不同的物理设备上，从而高效利用多个硬件设备
• 5.可以使用分区表赖避免某些特殊瓶颈，例如InnoDB单个索引的互斥访问、ext3文件系统的inode锁竞争
• 6.可以备份和恢复单个分区

分区的限制和缺点：

• 1.一个表最多只能有1024个分区
• 2.如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来
• 3.分区表无法使用外键约束
• 4.NULL值会使分区过滤无效
• 5.所有分区必须使用相同的存储引擎

分区的类型：

• 1.RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区
• 2.LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择
• 3.HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式
• 4.KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值
• 5.具体关于mysql分区的概念请自行google或查询官方文档，我这里只是抛砖引玉了。

4. 分表
  分表就是把一张大表，按照如上过程都优化了，还是查询卡死，那就把这个表分成多张表，把一次查询分成多次查询，然后把结果组合返回给用户。
  分表分为垂直拆分和水平拆分，通常以某个字段做拆分项。比如以id字段拆分为100张表：表名为 tableName_id%100
  但是分表需要修改源程序代码，会给开发带来大量工作，极大的增加了开发成本，故：只适合在开发初期就考虑到了大量数据存在，做好了分表处理，不适合应用上线了再做修改，成本太高。

5. 分库
  把一个数据库分成多个，读写分离、主从复制、集群等等，真正的做分库也会带来大量的开发成本,适合在开发初期就考虑到。

方案二具体做法

  mysql性能不行，那就换个数据库。为保证源程序代码不修改，保证现有业务平稳迁移，故需要换一个100%兼容mysql的数据库。

开源选择
1.tiDB https://github.com/pingcap/tidb
2.Cubrid https://www.cubrid.org/
3.开源数据库会带来大量的运维成本且其工业品质和MySQL尚有差距，有很多坑要踩，如果你公司要求必须自建数据库，那么选择该类型产品。

云数据选择
1、阿里云
2、腾讯云
3、百度云

方案三具体做法

  数据量过亿，使用大数据引擎处理数据

开源解决方案:
  hadoop家族。hbase/hive怼上就是了。但是有很高的运维成本，一般公司是玩不起的，需要很大的投入。

云解决方案
  这个就比较多了，也是一种未来趋势，大数据由专业的公司提供专业的服务，小公司或个人购买服务，大数据就像水/电等公共设施一样，存在于社会的方方面面。

三、对于Mysql性能优化（面试题）

1. 硬件：运维人员负责
2. 架构设计：
   • 主从集群
   • 读写分离
   • 分库分表
   • 热点数据引入redis分布式数据库
3. sql优化
   • 慢sql的定位和排查，使用慢查询日志（日志分析工具）分析
   • 执行计划分析explain
   • show profile工具分析sql语句资源消耗
4. sql语句优化
   • sql查询给予索引进行数据扫描
   • 避免索引列上使用函数或运算符（索引失效）
   • where中的like把%放到右侧
   • 联合索引中列从左往右命中越多越好
   • 使用sql语句用到的索引完成排序
   • 查询语句少用*
   • 用小结果集驱动大结果集
5. mysql程序配置优化