成为VIP
商务合作:请加微信(QQ)：2230304070
视频教程推荐
以下视频教程的网址：
已经更改为： 请知悉！
1
精选文章正文
为什么你写的sql查询慢？为什么你建的索引常失效？通过本篇内容，你将学会MySQL性能下降的原因，索引的简介，索引创建的原则，explain命令的使用，以及explain输出字段的意义。助你了解索引，分析索引，使用索引，从而写出更高性能的sql语句。
案例分析
我们先简单了解一下非关系型数据库和关系型数据库的区别。
MongoDB是NoSQL中的一种。NoSQL的全称是Not only SQL，非关系型数据库。它的特点是性能高，扩张性强，模式灵活，在高并发场景表现得尤为突出。但目前它还只是关系型数据库的补充，它在数据的一致性，数据的安全性，查询的复杂性问题上和关系型数据库还存在一定差距。
MySQL是关系性数据库中的一种，查询功能强，数据一致性高，数据安全性高，支持二级索引。但性能方面稍逊与MongoDB，特别是百万级别以上的数据，很容易出现查询慢的现象。这时候需要分析查询慢的原因，一般情况下是程序员sql写的烂，或者是没有键索引，或者是索引失效等原因导致的。
公司ERP系统数据库主要是MongoDB（最接近关系型数据的NoSQL），其次是Redis，MySQL只占很少的部分。现在又重新使用MySQL，归功于阿里巴巴的奇门系统和聚石塔系统。考虑到订单数量已经是百万级以上，对MySQL的性能分析也就显得格外重要。
我们先通过两个简单的例子来入门。后面会详细介绍各个参数的作用和意义。
场景一：订单导入，通过交易号避免重复导单
业务逻辑：订单导入时，为了避免重复导单，一般会通过交易号去数据库中查询，判断该订单是否已经存在。
最基础的sql语句
查询的本身没有任何问题，在线下的测试环境也没有任何问题。可是，功能一旦上线，查询慢的问题就迎面而来。几百上千万的订单，用全表扫描？啊？哼!
怎么知道该sql是全表扫描呢？通过explain命令可以清楚MySQL是如何处理sql语句的。打印的内容分别表示：
id: 查询序列号为1。
select_type: 查询类型是简单查询，简单的select语句没有union和子查询。
table: 表是 itdragon_order_list。
partitions: 没有分区。
type: 连接类型，all表示采用全表扫描的方式。
possible_keys: 可能用到索引为null。
key: 实际用到索引是null。
key_len: 索引长度当然也是null。
ref: 没有哪个列或者参数和key一起被使用。
Extra: 使用了where查询。
因为数据库中只有三条数据，所以rows和filtered的信息作用不大。这里需要重点了解的是type为ALL，全表扫描的性能是最差的，假设数据库中有几百万条数据，在没有索引的帮助下会异常卡顿。
初步优化：为transaction_id创建索引
这里创建的索引是唯一索引，而非普通索引。
唯一索引打印的type值是const。表示通过索引一次就可以找到。即找到值就结束扫描返回查询结果。
普通索引打印的type值是ref。表示非唯一性索引扫描。找到值还要继续扫描，直到将索引文件扫描完为止。(这里没有贴出代码)
显而易见，const的性能要远高于ref。并且根据业务逻辑来判断，创建唯一索引是合情合理的。
再次优化：覆盖索引
这里将select * from 改为了 select transaction_id from 后
Extra 显示 Using index，表示该查询使用了覆盖索引，这是一个非常好的消息，说明该sql语句的性能很好。若提示的是Using filesort(使用内部排序)和Using temporary(使用临时表)则表明该sql需要立即优化了。
根据业务逻辑来的，查询结构返回transaction_id 是可以满足业务逻辑要求的。
场景二，订单管理页面，通过订单级别和订单录入时间排序
业务逻辑：优先处理订单级别高，录入时间长的订单。
既然是排序，首先想到的应该是order by， 还有一个可怕的 Using filesort 等着你。
最基础的sql语句
首先，采用全表扫描就不合理，还使用了文件排序Using filesort，更加拖慢了性能。
MySQL在4.1版本之前文件排序是采用双路排序的算法，由于两次扫描磁盘，I/O耗时太长。后优化成单路排序算法。其本质就是用空间换时间，但如果数据量太大，buffer的空间不足，会导致多次I/O的情况。其效果反而更差。与其找运维同事修改MySQL配置，还不如自己乖乖地建索引。
初步优化：为order_level,input_date 创建复合索引
创建复合索引后你会惊奇的发现，和没创建索引一样？？？都是全表扫描，都用到了文件排序。是索引失效？还是索引创建失败？我们试着看看下面打印情况
将select * from 换成了 select order_level,input_date from 后。type从all升级为index索引失效的几种情况，表示（full index scan）全索引文件扫描，Extra也显示使用了覆盖索引。可是不对啊！！！！检索虽然快了，但返回的内容只有order_level和input_date 两个字段，让业务同事怎么用？难道把每个字段都建一个复合索引？
MySQL没有这么笨，可以使用force index 强制指定索引。在原来的sql语句上修改 force index(idx_order_levelDate) 即可。
再次优化：订单级别真的要排序么？
其实给订单级别排序意义并不大，给订单级别添加索引意义也不大。因为order_level的值可能只有，低，中，高，加急，这四种。对于这种重复且分布平均的字段，排序和加索引的作用不大。
我们能否先固定 order_level 的值，然后再给 input_date 排序？如果查询效果明显，是可以推荐业务同事使用该查询方式。
和之前的sql比起来，type从index 升级为 ref(非唯一性索引扫描)。索引的长度从68变成了5，说明只用了一个索引。ref也是一个常量。Extra 为Using index condition 表示自动根据临界值，选择索引扫描还是全表扫描。总的来说性能远胜于之前的sql。
上面两个案例只是快速入门，我们需严记一点：优化是基于业务逻辑来的。绝对不能为了优化而擅自修改业务逻辑。如果能修改当然是最好的。
索引简介
官方定义：索引（Index） 是帮助MySQL高效获取数据的数据结构。
大家一定很好奇，索引为什么是一种数据结构，它又是怎么提高查询的速度？我们拿最常用的二叉树来分析索引的工作原理。看下面的图片：
创建索引的优势
1 提高数据的检索速度，降低数据库IO成本：使用索引的意义就是通过缩小表中需要查询的记录的数目从而加快搜索的速度。
2 降低数据排序的成本，降低CPU消耗：索引之所以查的快，是因为先将数据排好序，若该字段正好需要排序，则真好降低了排序的成本。
创建索引的劣势
1 占用存储空间：索引实际上也是一张表，记录了主键与索引字段索引失效的几种情况，一般以索引文件的形式存储在磁盘上。
2 降低更新表的速度：表的数据发生了变化，对应的索引也需要一起变更，从而减低的更新速度。否则索引指向的物理数据可能不对，这也是索引失效的原因之一。
3 优质索引创建难：索引的创建并非一日之功，也并非一直不变。需要频繁根据用户的行为和具体的业务逻辑去创建最佳的索引。
索引分类
我们常说的索引一般指的是BTree（多路搜索树）结构组织的索引。其中还有聚合索引，次要索引，复合索引，前缀索引，唯一索引，统称索引，当然除了B+树外，还有哈希索引（hash index）等。
单值索引：一个索引只包含单个列，一个表可以有多个单列索引
唯一索引：索引列的值必须唯一，但允许有空值
复合索引：一个索引包含多个列，实际开发中推荐使用
实际开发中推荐使用复合索引，并且单表创建的索引个数建议不要超过五个
基本语法：
创建：
create [unique] index indexName on tableName (columnName...)
alter tableName add [unique] index [indexName] on (columnName...)
删除：
drop index [indexName] on tableName
限时特惠：本站每日持续更新海量设计资源，一年会员只需29.9元，全站资源免费下载
站长微信：ziyuanshu688
htebazile
