优化mysql实验(explain;索引)+利用 index、explain和profile优化mysql数据库查询小结

比眉伴天荒 2022-05-06 00:58 279阅读 0赞

# 1.优化实验 #

实验环境：

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;">1、sql工具：Navicat2、sql<a href="http://www.2cto.com/database/" rel="nofollow">数据库</a>，使用openstack数据库作为示例</td> 
  </tr> 
 </tbody> 
</table>

一、mysql索引查询

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;">show index from instances</td> 
  </tr> 
 </tbody> 
</table>

![\\][20150113100332254.png]结果字段解释：vcmRlcj0="1" cellpadding="2" cellspacing="0">

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;">Table：数据库表名Non_unique：索引不能包括重复词，则为0。可以，则为1。Key_name：索引的名称。<br> 索引中的列序列号，从1开始。列名称列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）。索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机 会就越大。如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。指示关键字如何被压缩。如果没有被压缩，则为NULL。如果列含有NULL，则为YES。如果没有，则该列为NO。用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。Comment：注释。</td> 
  </tr> 
 </tbody> 
</table>

二、验证Mysql的主键会自动创建索引？创建一个没有主键的ttx\_index数据库表：![\\][20150113100336257.png]查询索引：![\\][20150113100339260.png]结果显示没有索引。  
改变ttx\_index数据库表字段id，将之设为主键，再次查询索引：![\\][20150113100340262.png]![\\][20150113100348264.png]  
得出结论，在Mysql中，数据库主键会自动建立索引。  
  
三、Mysql性能优化利器：explain1、首先查看instances数据库表的索引：![\\][20150113100349265.png]  
2、EXPLAIN 用法详解：

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;">EXPLAIN SELECT * FROM instances</td> 
  </tr> 
 </tbody> 
</table>

![\\][20150113100351267.png]![\\][20150113100352268.png]根据上述结果，可以此查询花了0.027ms，没有可用的索引。  
  
explain字段详解：

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;"> <p>table：显示这一行的数据是关于哪张表的</p> <p>type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL</p> <p>possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句</p> <p>key： 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引</p> <p>key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好</p> <p>ref：显示索引的哪一列被使用了，如果可能的话，是一个常数</p> <p>rows：MYSQL认为必须检查的用来返回请求数据的行数</p> <p>Extra：关于MYSQL如何解析查询的额外信息。将在下表中讨论，但这里可以看到的坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢</p> &nbsp; <p>extra列返回的描述的意义：</p> 
    <table> 
     <tbody> 
      <tr> 
       <td style="vertical-align:top;"> <p>Distinct:一旦MYSQL找到了与行相联合匹配的行，就不再搜索了</p> <p>Not exists: MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了</p> <p>Range checked for each Record（index map:#）:没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一</p> <p>Using filesort: 看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行</p> <p>Using index: 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候</p> <p>Using temporary 看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上</p> <p>Where used 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题不同连接类型的解释（按照效率高低的顺序排序）</p> <p>system 表只有一行：system表。这是const连接类型的特殊情况</p> <p>const:表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待</p> <p>eq_ref:在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用</p> <p>ref:这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好</p> <p>range:这个连接类型使用索引返回一个范围中的行，比如使用&gt;或&lt;查找东西时发生的情况</p> <p>index: 这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）</p> <p>ALL:这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免</p> </td> 
      </tr> 
     </tbody> 
    </table></td> 
  </tr> 
 </tbody> 
</table>

那么如何才能让sql走索引查询呢？

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;">EXPLAIN SELECT * FROM instances WHERE id=1</td> 
  </tr> 
 </tbody> 
</table>

![\\][20150113100354270.png]从上图可以，该sql语句走了索引。因为该表中id为主键，mysql会自动创建索引，因此当将id作为where条件查询时，数据库会自动走索引。  
接下来实验，当不走索引还是查询id=1这条数据时候，会是如何？

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;">SELECT id, display_name FROM instances WHERE id=1</td> 
  </tr> 
 </tbody> 
</table>

![\\][20150113100355271.png]

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;">EXPLAIN SELECT * FROM instances WHERE display_name = "vm1"</td> 
  </tr> 
 </tbody> 
</table>

![\\][20150113100356274.png]  
  
结论：在查询时候，如果where条件中的字段有索引（走不走索引，取决于where条件中的字段），在执行sql语句时，mysql会自动走索引。  
但是有个问题是，在走不走索引，查询花费时间都是0.001ms，似乎没有得到性能提高？

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;">SELECT COUNT(*) FROM instances</td> 
  </tr> 
 </tbody> 
</table>

![\\][20150113100357275.png]  
在数据库表中instance数据总条数才74条，因此索引没法发挥它的性能优势，接下来人为制造上w条数据：

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;">insert instances(display_name) select display_name from instances</td> 
  </tr> 
 </tbody> 
</table>

注：上述语句，可用来为数据库表指数形式插入新数据。![\\][20150113100358277.png]  
再次查询总条数：

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;">SELECT COUNT(*) FROM instances</td> 
  </tr> 
 </tbody> 
</table>

![\\][20150113100358278.png]  
这次数据已经有接近500w了。再次验证上述索引性能问题：  
1、为了对比的真实性,将id=1的数据记录的display\_name修改为唯一名字test\_index\_dispaly\_name

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;">SELECT id, display_name FROM instances WHERE id=1</td> 
  </tr> 
 </tbody> 
</table>

![\\][20150113100359280.png]  
2、不走索引查询：

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;">SELECT * FROM instances WHERE display_name = 'test_index_dispaly_name'</td> 
  </tr> 
 </tbody> 
</table>

![\\][20150113100400281.png]  
3、通过id走索引查询：

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;">SELECT * FROM instances WHERE id=1</td> 
  </tr> 
 </tbody> 
</table>

![\\][20150113100400283.png]  
结论：对于百万上亿级数据，走不走索引效率影响相当明显（效率差别都到万了）。

4、哪些情况sql不会走索引？时间关系，此处暂且未总结，后续有时间补上。若有需要请自行网上查找。

# 2.小结 #

想必大家对index，explain和profile的利用也很多，这是我最近两天优化mysql语句查询资料整理的一些内容，希望大家可以一起来补充一下。

index的使用：

1.最好是在相同类型的字段间进行比较的操作。在MySQL 3.23版之前，这甚至是一个必须的条件。例如不能将一个建有索引的INT字段和BIGINT字段进行比较；但是作为特殊的情况，在CHAR类型的字段和VARCHAR类型字段的字段大小相同的时候，可以将它们进行比较。

2.在建有索引的字段上尽量不要使用函数进行操作，尽量不要在数据库中做运算。

3.避免负向查询和%前缀模糊查询，like 'xxx%'百分号后缀查询可以在此字段上使用索引。

4.不要在生产环境程序中使用select \* from 的形式查询数据。只查询需要使用的列。

5.查询尽可能使用limit减少返回的行数，减少数据传输时间和带宽浪费。

6.对查询列使用函数用不到索引。

7.避免隐式类型转换，例如字符型一定要用’’，数字型一定不要使用’’。

8.所有的SQL关键词用大写，养成良好的习惯，避免SQL语句重复编译造成系统资源的浪费。

9.联表查询的时候，记得把小结果集放在前面，遵循小结构及驱动大结果集的原则,这条很重要！

10.开启慢查询，定期用explain优化慢查询中的SQL语句。

11.任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。

12.IN、OR子句常会使用工作表，使索引失效。如果不产生大量重复值，可以考虑把子句拆开。拆开的子句中应该包含索引。

13.索引不会包含有NULL值的列，只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

14.使用短索引，对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

15.排序的索引问题，mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

什么情况下应不建或少建索引

1.表记录太少

如果一个表只有5条记录，采用索引去访问记录的话，那首先需访问索引表，再通过索引表访问数据表，一般索引表与数据表不在同一个数据块，这种情况下ORACLE至少要往返读取数据块两次。而不用索引的情况下ORACLE会将所有的数据一次读出，处理速度显然会比用索引快。   如表zl\_sybm（使用部门）一般只有几条记录，除了主关键字外对任何一个字段建索引都不会产生性能优化，实际上如果对这个表进行了统计分析后ORACLE也不会用你建的索引，而是自动执行全表访问。如：   select \* from zl\_sybm where sydw\_bh=’5401’（对sydw\_bh建立索引不会产生性能优化）   经常插入、删除、修改的表

对一些经常处理的业务表应在查询允许的情况下尽量减少索引，如zl\_yhbm，gc\_dfss，gc\_dfys，gc\_fpdy等业务表。

2.数据重复且分布平均的表字段

假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。   经常和主字段一块查询但主字段索引值比较多的表字段如 gc\_dfss（电费实收）表经常按收费序号、户标识编号、抄表日期、电费发生年月、操作 标志来具体查询某一笔收款的情况，如果将所有的字段都建在一个索引里那将会增加数据的修改、插入、删除时间，从实际上分析一笔收款如果按收费序号索引就已 经将记录减少到只有几条，如果再按后面的几个字段索引查询将对性能不产生太大的影响。   对千万级MySQL数据库建立索引的事项及提高性能的手段.

MySQL索引类型包括：

1.普通索引

这是最基本的索引，它没有任何限制。它有以下几种创建方式：

◆创建索引

CREATE INDEX indexName ON mytable(username(length)); 如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length，下同。

◆修改表结构

ALTER mytable ADD INDEX \[indexName\] ON (username(length)) ◆创建表的时候直接指定

CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   INDEX \[indexName\] (username(length))   );  删除索引的语法：

DROP INDEX \[indexName\] ON mytable;

2.唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

◆创建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length)) ◆修改表结构

ALTER mytable ADD UNIQUE \[indexName\] ON (username(length)) ◆创建表的时候直接指定

CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   UNIQUE \[indexName\] (username(length))   );

3.主键索引

它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引：

CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   PRIMARY KEY(ID)   );  当然也可以用 ALTER 命令。记住：一个表只能有一个主键。

4.组合索引

为了形象地对比单列索引和组合索引，为表添加多个字段：

CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   city VARCHAR(50) NOT NULL,   age INT NOT NULL  );  为了进一步榨取MySQL的效率，就要考虑建立组合索引。就是将 name, city, age建到一个索引里：

ALTER TABLE mytable ADD INDEX name\_city\_age (username(10),city,age); 建表时，usernname长度为 16，这里用 10。这是因为一般情况下名字的长度不会超过10，这样会加速索引查询速度，还会减少索引文件的大小，提高INSERT的更新速度。

如果分别在 usernname，city，age上建立单列索引，让该表有3个单列索引，查询时和上述的组合索引效率也会大不一样，远远低于我们的组合索引。虽然此时有了三个索引，但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。

建立这样的组合索引，其实是相当于分别建立了下面三组组合索引：

usernname,city,age   usernname,city   usernname  为什么没有 city，age这样的组合索引呢？这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引，下面的几个SQL就会用到这个组合索引：

SELECT \* FROM mytable WHREE username="admin" AND city="郑州"  SELECT \* FROM mytable WHREE username="admin" 而下面几个则不会用到：

SELECT \* FROM mytable WHREE age=20 AND city="郑州"  SELECT \* FROM mytable WHREE city="郑州"

建立索引的时机

到这里我们已经学会了建立索引，那么我们需要在什么情况下建立索引呢？

一般来说，在WHERE和JOIN中出现的列需要建立索引，但也不完全如此，因为MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引。例如：

SELECT t.Name  FROM mytable t LEFT JOIN mytable m    ON t.Name=m.username WHERE m.age=20 AND m.city='郑州' 此时就需要对city和age建立索引，由于mytable表的userame也出现在了JOIN子句中，也有对它建立索引的必要。

索引的不足之处

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：

1.虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

2.建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。

索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

优化常用简单操作：

set profiling = 1;

expain (查询语句);

show profiles;

show profile for query (查询语句id);

create index aIndex on tablename(columnname);

根据explain检测出mysql内部执行查询的步骤和具体参数来优化自己的select语句。

[20150113100332254.png]: http://www.2cto.com/uploadfile/Collfiles/20150113/20150113100332254.png
[20150113100336257.png]: http://www.2cto.com/uploadfile/Collfiles/20150113/20150113100336257.png
[20150113100339260.png]: http://www.2cto.com/uploadfile/Collfiles/20150113/20150113100339260.png
[20150113100340262.png]: http://www.2cto.com/uploadfile/Collfiles/20150113/20150113100340262.png
[20150113100348264.png]: http://www.2cto.com/uploadfile/Collfiles/20150113/20150113100348264.png
[20150113100349265.png]: http://www.2cto.com/uploadfile/Collfiles/20150113/20150113100349265.png
[20150113100351267.png]: http://www.2cto.com/uploadfile/Collfiles/20150113/20150113100351267.png
[20150113100352268.png]: http://www.2cto.com/uploadfile/Collfiles/20150113/20150113100352268.png
[20150113100354270.png]: http://www.2cto.com/uploadfile/Collfiles/20150113/20150113100354270.png
[20150113100355271.png]: http://www.2cto.com/uploadfile/Collfiles/20150113/20150113100355271.png
[20150113100356274.png]: http://www.2cto.com/uploadfile/Collfiles/20150113/20150113100356274.png
[20150113100357275.png]: http://www.2cto.com/uploadfile/Collfiles/20150113/20150113100357275.png
[20150113100358277.png]: http://www.2cto.com/uploadfile/Collfiles/20150113/20150113100358277.png
[20150113100358278.png]: http://www.2cto.com/uploadfile/Collfiles/20150113/20150113100358278.png
[20150113100359280.png]: http://www.2cto.com/uploadfile/Collfiles/20150113/20150113100359280.png
[20150113100400281.png]: http://www.2cto.com/uploadfile/Collfiles/20150113/20150113100400281.png
[20150113100400283.png]: http://www.2cto.com/uploadfile/Collfiles/20150113/20150113100400283.png