亲宝软件园·资讯

展开

MySQL索引优化

大头程序员不头大 人气:0

一、索引的特点

当MySQL单表记录数过大时,增删改查性能都会急剧下降。MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。除非单表数据未来会一直不断上涨,否则不要一开始就考虑拆分,拆分会带来逻辑、部署、运维的各种复杂度。一般以整型值为主的表在千万级以下,字符串为主的表在五百万以下是没有太大问题的,而事实上很多时候MySQL单表的性能依然有不少优化空间,甚至能正常支撑千万级以上的数据量。

索引优势和劣势:

二、索引类型

Mysql目前主要有以下几种索引类型:FULLTEXT,HASH,BTREE,RTREE。

1.FULLTEXT

即为全文索引,目前只有MyISAM引擎支持。其可以在CREATE TABLE ,ALTER TABLE ,CREATE INDEX 使用,不过目前只有 CHAR、VARCHAR ,TEXT 列上可以创建全文索引。

全文索引并不是和MyISAM一起诞生的,它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。

FULLTEXT(全文)索引,仅可用于MyISAM和InnoDB

2.HASH

哈希索引用索引列的值计算该值的hashCode,然后在hashCode相应的位置存执该值所在行数据的物理位置,因为使用散列算法,因此访问速度非常快,但是一个值只能对应一个hashCode,而且是散列的分布方式。由于HASH的唯一(几乎100%的唯一)及类似键值对的形式,很适合作为索引。

HASH索引可以一次定位,不需要像树形索引那样逐层查找,因此具有极高的效率。但是,这种高效是有条件的,即只在“=”和“in”条件下高效,对于范围查询、排序及组合索引仍然效率不高。

3.BTREE

BTREE(B+TREE)索引就是一种将索引值按一定的算法,存入一个树形的数据结构中(二叉树),每次查询都是从树的入口root开始,依次遍历node,获取leaf。由于BTREE非叶子节点不存储数据(data),因此所有的数据都要查询至叶子节点,而叶子节点的高度都是相同的,因此所有数据的查询速度都是一样的。这是MySQL里默认和最常用的索引类型。

4.RTREE

RTREE在MySQL很少使用,仅支持geometry数据类型,支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。

相对于BTREE,RTREE的优势在于范围查找。

三、索引种类

四、索引的使用策略

1.什么时候要使用索引?

主键自动建立唯一索引;经常作为查询条件在WHERE或者ORDER BY;语句中出现的列要建立索引;作为排序的列要建立索引;查询中与其他表关联的字段,外键关系建立索引高并发条件下倾向组合索引;用于聚合函数的列可以建立索引,例如使用了max(column_1)或者count(column_1)时的column_1就需要建立索引。

2.什么时候不要使用索引?

经常增删改的列不要建立索引;有大量重复的列不建立索引;表记录太少不要建立索引。只有当数据库里已经有了足够多的测试数据时,它的性能测试结果才有实际参考价值。如果在测试数据库里只有几百条数据记录,它们往往在执行完第一条查询命令之后就被全部加载到内存里,这将使后续的查询命令都执行得非常快–不管有没有使用索引。只有当数据库里的记录超过了1000条、数据总量也超过了MySQL服务器上的内存总量时,数据库的性能测试结果才有意义。

3.索引失效的情况?

在组合索引中不能有列的值为NULL,如果有,那么这一列对组合索引就是无效的;在一个SELECT语句中,索引只能使用一次,如果在WHERE中使用了,那么在ORDER BY中就不要用了;LIKE操作中,’%aaa%'不会使用索引,也就是索引会失效,但是’aaa%'可以使用索引;在索引的列上使用表达式或者函数会使索引失效,例如:

select * from table where ceate_time > unix_timestamp(curdate());

将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成当前时间由程序作为参数传入:

select * from table where ceate_time > 1524561911;

4.mysql查询优化?

字段:

索引:

查询sql:

5.索引的常见问题

1.索引是干什么的?

索引用于快速找出在某个列中有一特定值的行。不使用索引,mysql必须从第一条记录开始读完整个表直到找出相关的行。表越大,花费的时间越多。如果表中查询的列有一个索引,mysql能快速到达一个位置搜寻到数据文件的中间,没有必要查看所有数据。

大多数mysql的索引(primary key、index、unique、fulltext)在B树中存储,只是空间列类型的索引使用R树,并且memory表还支持hash索引。

2.索引好复杂,我该怎么理解索引,有没有一个更形象的例子?

索引就像是一本书的目录。

3.索引越多越好?

大多数情况下,索引能大幅提高查询效率。但是:数据变更(增删改)都需要维护索引,因此更多索引意味着更多维护成本;也意味着需要更多控件空间(一本书100页,却有50页目录?);过小的表,建索引可能会更慢。

4.索引的字段类型问题

text类型,也可建索引(需要指定长度);MyISAM存储引擎长度综合不能超过1000字节;用来筛选的值尽量保持和索引列同样的数据类型。

5.like能用到索引?

尽量减少like查询,但是也不是绝对不可用,'xxx%'是可以用到索引的。除了like,以下操作符也可以用到索引:

<,<=,=,>,>=,between,in

这些用不到索引:

<>,not in,!=

6.什么样的字段不适合建索引?

列的值唯一性太小(比如性别,类型),不适合建索引。(什么叫大小?一般来说,同值的数据超过表的15%,那就没有必要建索引了)更新非常频繁的数据不适合建索引。

7.一次查询能用多个索引?

不能

8.多列查询该如何建索引?

一次查询只能用到一个索引, a列建索引还是b列建索引?谁的区分度(同值的少)更高,建谁!当然,联合索引也是个不错的方案。

9.联合索引的问题

-- 命中col1、col2联合索引
select col1,col2 from test where col1 = 'xxx';
-- 不能命中col1、col2联合索引
select col1,col2 from test where col2 = 'xxx';

所以大多数情况下,有col1、col2索引了,就不用再去建col1索引了

10.哪些常见的情况不能用到索引?

like '%xxx'
not in
!=

对列进行函数运算,如:

where md5(password) = "xxx"

存了数值的字符串类型字段(如手机号),查询是记得不要丢掉值的引号,否则无法命中索引:

select * from test where mobile = 13800002222;

如果mobile字段是char或者varchar类型,则上面查询无法命中索引,应为:

select * from test where mobile = '13800002222';

11.NULL的问题

Null会导致索引形同虚设,所以在设计表结构应避免NULL的存在。
可用其他方式来表达,比如-1。

加载全部内容

相关教程
猜你喜欢
用户评论