泊涯
作者泊涯2019-05-10 11:08
系统测试工程师, 高伟达公司

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

字数 2449阅读 2410评论 0赞 1

从性能角度看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;查看索引详情:

q0vlddtxfok1

q0vlddtxfok1

5xctijzszcqz

5xctijzszcqz

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类型字段上使用全文索引

–添加全文索引

CREATE FULLTEXT INDEX index_content ON article(content)

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

ALTER TABLE article ADD FULLTEXT index_content(content)

组合索引

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

如果觉得我的文章对您有用,请点赞。您的支持将鼓励我继续创作!

1

添加新评论0 条评论

Ctrl+Enter 发表

作者其他文章

相关文章

相关问题

相关资料

X社区推广