mysql 索引性能检测_MYSQL性能故障优化利器之索引优化

MYSQL性能故障优化利器之索引优化

从性能角度看80%的性能问题是设计出来的，从性能测试问题统计概率来分析，80%的性能问题来源与数据打交道引发的问题，其中SQL语法问题占比比价高，而这类问题大部分是因为检索数据的方式问题引起的，例如全表扫描、多表关联设计、大表使用等导致查询数据慢，而这类问题中优化方式有：语法拆解、优化表连接方式、表物理分区、逻辑分区、合适利用数据库参数、合理使用不同类型的索引、优化硬件资源、优化业务需求等来提高检索数据速率。

而对于测试人员来说主要是通过设计合理的测试场景，通过合理的监控利器，这样能快速发现问题，把将来生产可能出现的故障，解决在摇篮之中，而有些索引的使用合理性，也只能在测试过程中通过各种调试尝试，才能知道怎么优化最合理，而这时我们身为测试人员，也可以学习怎么建立合适的索引才能起到最优效果，这也是我们作为测试人员能亲自体验亲自优化的，当然我们自己要了解数据库索引使用原理，才能知道怎么优化，毕竟发现SQL问题容易，解决问题比较难，难得原因在于不了解它们的工作原理。

那我们作为测试人员既然要做到如何测试才能测试出问题，然后去诊断诊断分析问题，优化问题，从测试角度看我们要了解测试方案的设计、测试模型的分析、测试策略的定制、监控模型设计、测试案例设计、测试结果分析、测试报告编写等这些简单易，我们更要学习架构原理、操作系统原理、数据库原理、JVM原理等目的是了解他们的底层原理才能更有效的发现问题，提供优化解决方案、发现问题是技术行为、解决优化问题是艺术行为。

索引设计要求：

1. 适合索引的列是出现在where子句中的列，或者连接子句中指定的列；

2、表基数较小，索引效果较差，没有必要在此列建立索引；

3、 表类型单一的不建议使用索引，例如性别表，男和女

4、不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。

5、. 过滤的数据量比较少，一般来说<20%,一般会走索引。20%-40% 可能走索引也可能不走索引，看数据库成本分析。> 40% 基本不走索引(会全表扫描)

6、 对索引的字段进行计算时，必须在运算符右侧进行计算。也就是 to_char(oc.create_date, 'yyyyMMdd')是没用的--可以使用函数索引oracle等

那我们本文主要讲解的是MYSQL的索引使用原理与效能。

一、什么是索引？为什么要建立索引？

索引用于快速找出在某个列中有一特定值的行，不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多，如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。

就像我们中华字典，如果不知道偏旁、拼音，找一个字怎么读，怎么解释，需要一页一页的、一行一行的查找，直到找到想要的字。这速度多慢可想而知。

例如，这一张用户user表，有一千万条数据，需要查找张三丰、家住武当山、、电话号码为15900xxxxxx1的个人信息，如果没有使用索引，那边需要从表中第一笔记录开始一笔一笔的遍历查询，直找到该条信息为主，如果刚好第一笔是那可能快，如果在表最后一笔呢？如果这时有了索引，那那张子杰根据姓名、电话号码进行索引检索，就能够快的查找出对应的数据，而不用挨笔的遍历。

既然知道通过索引就能提高查询效率，那我们需要了解索引的存储类型有几种：BTREE、HASH。

查看索引类型方法：

可以使用SHOW INDEX FROM table_name;查看索引详情：

Table:创建索引的表

Non_unique：表示索引非唯一，1代表 非唯一索引， 0代表 唯一索引，意思就是该索引是不是唯一索引

Key_name：索引名称　Seq_in_index 表示该字段在索引中的位置，单列索引的话该值为1，组合索引为每个字段在索引定义中的顺序(这个只需要知道单列索引该值就为1，组合索引为别的)

Column_name：表示定义索引的列字段，Sub_part：表示索引的长度；

Null：表示该字段是否能为空值

Index_type：表示索引类型

主键索引 PRIMARY KEY

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

唯一索引 UNIQUE

唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一

-创建唯一索引

CREATE UNIQUE INDEX indexName ON table(column(length))

–修改表结构

ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

普通索引 INDEX

这是最基本的索引，它没有任何限制。

–--直接创建索引

CREATE INDEX index_name ON table(column(length))

–--修改表结构的方式添加索引

ALTER TABLE table_name ADD INDEX index_name ON (column(length))

–--删除索引

DROP INDEX index_name ON table

全文索引(FULLTEXT)

全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引

–添加全文索引

CREATEFULLTEXT INDEXindex_content ONarticle(content)

–修改表结构添加全文索引

ALTER TABLE article ADD FULLTEXT index_content(content)

组合索引

在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。

案例说明

具体索引使用情况，案例描述如：