mysql索引最佳实践-笔记

最新推荐文章于 2023-09-29 16:22:35 发布

baiyangfu

最新推荐文章于 2023-09-29 16:22:35 发布

阅读量1.9k

点赞数 1

分类专栏： mysql 文章标签： mysql 存储 loops 引擎优化 nested

本文链接：https://blog.csdn.net/baiyangfu_love/article/details/7931417

版权

mysql 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

在网上找到一篇mysql索引最佳实践的ppt，感觉很好，记一下笔记：

mysql使用索引干什么？

1、数据查找。

2、排序。

3、避免读“数据”

4、特殊优化。

我们可能用到的索引类型：

BTREE索引：大部分mysql的索引都是这种索引。

RTREE索引：只有MyISAM引擎用，for GIS

HASH索引：存储引擎有 MEMORY， NDB

BITMAP索引：目前mysql还不支持。

FULLTEXT索引：在mysql5.6中，MyISAM和Innodb将支持。

B+Tree非常适用于磁盘存储：数据存储在叶节点：

在MyISAM引擎中，数据指针指向数据文件的物理偏移。

在InnodB中，

PRIMARY KEY —数据存储在索引的叶节点上，不用指针。

Secondary Index就和普通的B-Tree索引差不多了，只不过在Secondary Index的所有Leaf Nodes上面同时包含了所指向数据记录的主键信息，而不是直接指向数据记录的位置

信息。所以，如果主键太大，会影响整个索引占的空间，影响查询性能。

字符串索引：

按照字符串排序方法进行排序： "AAAA” < "AAAB"

LIKE关键字是一种特殊的范围查找：

LIKE "ABC%" 等同于： "ABC[LOWEST]" < KEY <"ABC[HIGHEST]"

但是，将%放到前面（“%ABC”）不能使用索引查询。

使用多列索引：

在排序时，按照列的次序依次进行:

KEY（col1, col2, col3） : (1, 2, 3) < (1, 3, 1)

基于多列的BTREE索引，其实使用的是一个BTREE索引，不会分别对多列进行索引。

索引的开销：

索引是需要额外的开销的，不要增加你不需要的索引。大多数情况下，扩展索引比增加一个好。

写开销——更新索引经常是数据库写开销的主要开销。

读开销——增加索引将会占用更多的磁盘和内存空间，会增加查询的开销。

索引开销的影响（InnodB）：

如果使用的 PRIMARY KEY 比较长，将会导致所有的Secondary keys 更长而且更慢。

“随机”PRIMARY KEY （不连续的 PRIMARY KEY 如：以字符串为PRIMARY KEY ），插入新值会造成很多的页拆分。

长索引一般会更慢。

关联索引会降低索引开销

如：insert_time 与 auto_increment _id 关联。

数据是以PRIMARY KEY 来划分的：

选择合适的PRIMARY KEY。

PRIMARY KEY 其实会暗地加到所有的索引中。

KEY(A) 其实是KEY(A,ID)

覆盖索引对排序非常有用。

1、查找数据

使用多列索引有点麻烦：

INDEX（A,B,C）列的顺序很重要，他会影响查询。以下三种情况：

a、使用索引查询

A>5

A=5 AND B>6

A=5 AND B=6 AND C=7

A=5 AND B IN (2,3) AND C>5

b、不使用索引查询

B>5 起始列不匹配索引

B=6 AND C=7 起始列不匹配索引

c、使用部分索引

A>5 AND B=2 在第一列使用范围查找，只是用这一列的部分。

A=5 AND B>6 AND C=2 在第二列使用范围查询，使用前两列索引部分。

mysql在使用多列索引时，遇到第一个范围查询时，将停止使用索引查询，但是，如果右面的是 if IN（……）的话，将会继续使用索引列查询。

2、排序

SELECT * FROM PLAYERS ORDER BY SCORE DESC LIMIT 10

如果SCORE 列上没有索引，将会使用 “filesort”，这将是非常耗时的。

SELECT * FROM PLAYERS WHERE COUNTRY="US" ORDER BY SCORE DESC LIMIT 10

如果使用在（COUNTRY,SCORE）列上的索引，将会非常高效。

使用多列索引：

将会更加严格： KEY (A, B)

a、将会使用索引排序

ORDER BY A 使用第一列排序

A=5 ORDER BY B EQ过滤第一列，使用第二列排序

ORDER BY A DESC, B DESC 以相同的方式，使用两列索引排序

A>5 ORDER BY A 范围查询在第一列，并且排序也在这一列

b、不使用索引排序

ORDER BY B

A >5 ORDER BY B

A IN (1,2) ORDER BY B

ORDER BY A DESC, B ASC

在不同的索引列使用不同的方式进行排序将不使用索引排序。

只能在非ORDER BY 列上使用 “等于” 过滤，否则，不使用索引排序。

3、避免读数据

“covering index ”：覆盖索引，指你查询要求的字段，只扫描索引树就能满足要求了。

只读索引，不读数据。

SELECT STATUS FROM ORDERS WHERE CUSTOMER_ID=123 ： KEY (CUSTOMER_ID, STATUS)

索引一般比数据要小。

4、特殊优化

在以下情况中，索引将对 MIN() MAX()聚合函数的优化很有帮助：

SELECT MAX(ID) FROM TBL;

SELECT MAX(SALARY) FROM EMPLOYEE GROUP BY DEPT_ID

将使用（DEPT_ID, SALARY）索引

使用索引进行 group by

索引和 Joins

mysql一般使用 “Nested Loops” 实现 joins

SELECT * FROM POSTS, COMMENTS WHERE AUTHOR="PETER" AND COMMENTS.POST_ID=POSTS.ID

先到POSTS表中找出所有作者是Peter的posts。

对于找出的每一个post，到COMMENTS表中找出所有的comments

索引只会在将要查找的表中使用。

所以，上面例子中，POSTS.ID（索引）不会在这个查找中用到

使用多列索引：

SELECT * FROM TBL WHERE A=5 AND B=6

可以分别使用 A 列和 B列的单独索引。

但是，使用（A,B）两列的索引更好。

SELECT * FROM TBL WHERE A=5 OR B=6

两个单独列（A）（B）的索引都会有很好的过滤效果

（A, B）两列的索引不会被使用。这是为什么？

前缀索引：（什么是前缀索引？）

有时需要索引很长的字符列，它会使索引变大而且变慢。一个策略就是模拟哈希索引。但是有时这也不够好，那通常可以索引开始的几个字符，而不是全部值，以节约空间并得到好的性能。这使索引需要的空间变小，但是也会降低选择性。索引选择性是不重复的索引值和表中所有行的比值。高选择性的索引有好处，因为它使mysql在查找匹配的时候可以过波掉更多的行。唯一索引的选择率为1，为最佳值。

如果索引BLOG和TEXT列，或者很长的varchar列，就必须定义前缀索引，因为mysql不允许索引它们的全文化。可以在同一个查询中针对许多不同的前缀长度进行计算，选择选择性好的。

前缀索引能很好地减少索引的大小及提高速度，但是mysql不能在order by 和group by查询中使用前缀索引，也不能把它们用伯覆盖索引。

有时后缀索引也挺有用，例如查找某个域名的所有电子邮件地址。mysql不支持反向索引，但是可以把反向字符串保存起来，并且索引它的前缀。可以用触发器维护这种索引。

你可以在列的最左边前缀创建索引。

ALTER TABLE TITLE ADD KEY(TITLE(20))

前缀索引不能用在覆盖索引。

如何选择前缀索引的长度？

前缀索引应该具有很好的过滤能力。

去重前缀 VS 所有去重数据：效果如下：