MySQL添加索引特点及优化问题

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

索引优势和劣势:

优势: 大大减少了服务器需要扫描的数据量,可以帮助服务器避免排序和临时表,实现快速检索,将随机I/O变成顺序I/O,减少I/O次数,加快检索速度;根据索引分组和排序,可以加快分组和排序;劣势: 索引本身也是表,因此会占用存储空间,一般来说,索引表占用的空间的数据表的1.5倍;索引表的维护和创建需要时间成本,这个成本随着数据量增大而增大;构建索引会降低数据表的修改操作(删除,添加,修改)的效率,因为在修改数据表的同时还需要修改索引表;创建索引时需要对表加锁,因此实际操作中需要在业务空闲期间进行。

二、索引类型

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

对于较大的数据集,把数据添加到一个没有FULLTEXT索引的表,然后添加FULLTEXT索引的速度比把数据添加到一个已经有FULLTEXT索引的表快。5.6版本前的MySQL自带的全文索引只能用于MyISAM存储引擎,如果是其它数据引擎,那么全文索引不会生效。5.6版本之后InnoDB存储引擎开始支持全文索引。在MySQL中,全文索引支队英文有用,目前对中文还不支持。5.7版本之后通过使用ngram插件开始支持中文。在MySQL中,如果检索的字符串太短则无法检索得到预期的结果,检索的字符串长度至少为4字节,此外,如果检索的字符包括停止词,那么停止词会被忽略。

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的优势在于范围查找。

三、索引种类

普通索引:仅加速查询。唯一索引:加速查询 + 列值唯一(可以有null)。主键索引:加速查询 + 列值唯一(不可以有null)+ 表中只有一个。组合索引:多列值组成一个索引,专门用于组合搜索,其效率大于索引合并,遵循“最左前缀”原则,把最常用作为检索或排序的列放在最左,依次递减,组合索引相当于建立了col1,col1col2,col1col2col3三个索引,而col2或者col3是不能使用索引的。全文索引:对文本的内容进行分词,进行搜索。

四、索引的使用策略

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;

其它通配符同样,也就是说,在查询条件中使用正则表达式时,只有在搜索模板的第一个字符不是通配符的情况下才能使用索引;在查询条件中使用不等于,包括<符号、>符号和!=会导致索引失效。特别的是:如果对主键索引使用!=则不会使索引失效,如果对主键索引或者整数类型的索引使用<符号或者>符号也不会使索引失效。(不等于,包括<符号、>符号和!,如果占总记录的比例很小的话,也不会失效);在查询条件中使用IS NULL或者IS NOT NULL会导致索引失效;字符串不加单引号会导致索引失效。更准确的说是类型不一致会导致失效,比如字段mobile是字符串类型的,使用WHERE mobile=99999 则会导致失败,应该改为WHERE mobile=‘99999’;在查询条件中使用OR连接多个条件会导致索引失效,除非OR链接的每个条件都加上索引,这时应该改为两次查询,然后用UNION ALL连接起来;如果排序的字段使用了索引,那么select的字段也要是索引字段,否则索引失效。特别的是:如果排序的是主键索引则select * 也不会导致索引失效;尽量不要包括多列排序,如果一定要,最好为这队列构建组合索引。

4.mysql查询优化?

字段:

尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT,如果非负则加上UNSIGNED;VARCHAR的长度只分配真正需要的空间;使用枚举或整数代替字符串类型;尽量使用TIMESTAMP而非DATETIME;单表不要有太多字段,建议在20以内;避免使用NULL字段,很难查询优化且占用额外索引空间;用整型来存IP。

索引:

索引并不是越多越好,要根据查询有针对性的创建,考虑在WHERE和ORDER BY命令上涉及的列建立索引,可根据EXPLAIN来查看是否用了索引还是全表扫描;应尽量避免在WHERE子句中对字段进行NULL值判断,否则将导致引擎放弃使用索引而进行全表扫描;值分布很稀少的字段不适合建索引,例如“性别”这种只有两三个值的字段;字符字段只建前缀索引;字符字段最好不要做主键;不用外键,由程序保证约束;尽量不用UNIQUE,由程序保证约束;使用多列索引时主意顺序和查询条件保持一致,同时删除不必要的单列索引。

查询sql:

可通过开启慢查询日志来找出较慢的SQL;不做列运算:SELECT id WHERE age + 1 = 10,任何对列的操作都将导致表扫描,它包括数据库教程函数、计算表达式等等,查询时要尽可能将操作移至等号右边;sql语句尽可能简单:一条sql只能在一个cpu运算;大语句拆小语句,减少锁时间;一条大sql可以堵死整个库;不用SELECT *;OR改写成IN:OR的效率是n级别,IN的效率是log(n)级别,IN的个数建议控制在200以内;不用函数和触发器,在应用程序实现;避免%xxx式查询,’%xxx%'不会使用索引,可以使用全文索引,然后:SELECT * FROM tablename MATCH(index_colum) ANGAINST(‘word’);少用JOIN;使用同类型进行比较,比如用’123’和’123’比,123和123比;组合索引要遵循最做前缀原则,排序分组频率最高的列放在最左边,以此类推;尽量避免在WHERE子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描;对于连续数值,使用BETWEEN不用IN:SELECT id FROM t WHERE num BETWEEN 1 AND 5;列表数据不要拿全表,要使用LIMIT来分页,每页数量也不要太大;使用短索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的 列,如果在前10 个或20 个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

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。

文档下载:MySQL添加索引特点及优化问题.doc文档

THE END
喜欢就支持一下吧