MySQL 索引优化

爱被打了一巴掌 2022-02-17 02:07 378阅读 0赞

## MySQL 索引优化 ##

## \------------------------------------------------------------------------- ##

## 一、索引的优点 ##

1、通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。例如：PK,NOT NULL and UNIQUE.  
2、可以大大加快数据的检索速度，这也是创建索引的最主要的原因。  
3、可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。  
4、在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。  
5、通过使用索引，可以在查询的过程中，使用查询优化器，提高系统的性能。

## 二、索引的缺点 ##

1、创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。  
2、索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立聚簇索引，那么需要的空间就会更大。  
3、当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

## 三、什么样的字段适合创建索引 ##

索引是建立在数据库表中的某些列的上面。因此，在创建索引的时候，应该仔细考虑在哪些列上可以创建索引，在哪些列上不能创建索引。 一般来说，应该在具备下述特性的列上创建索引：  
1、在经常需要搜索的列上，可以加快搜索的速度；  
2、在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；  
3、在经常用连接的列上，这些列主要是一些外键，可以加快连接的速度；  
4、在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；  
5、在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；  
6、在经常使用在 WHERE 子句中的列上面创建索引，加快条件的判断速度。  
建立索引，一般按照 select 的 where 条件来建立，比如： select 的条件是 where f1 andf2，那么如果我们在字段 f1 或字段 f2 上建立索引是没有用的，只有在字段 f1 和 f2 上同时建立索引才有用等。

## 四、什么样的字段不适合创建索引 ##

1、对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。  
2、对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。  
3、对于那些定义为 text, image ，blod和 bit 数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。  
4、当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

## 五、MySQL 中的索引种类 ##

**1、B-Tree 索引**  
  B-Tree 索引，顾名思义，就是所有的索引节点都按照 balance tree 的数据结构来存储。B-tree 结构可以显著减少定位记录时所经历的中间过程，从而加快存取速度。B-tree 中，每个结点包含：  
1、本结点所含关键字（例如PK的个数）的个数；  
2、指向父结点的指针；  
3、关键字（例如PK）；  
4、指向子结点的指针；  
  对于一棵 m 阶 B-tree，每个结点至多可以拥有 m 个子结点。各结点的关键字和可以拥有的子结点数都有限制，规定 m 阶 B-tree 中，根结点至少有 2 个子结点，除非根结点为叶子节点，相应的，根结点中关键字的个数为 1m-1；非根结点至少有\[m/2\]（\[\]，向上取整）个子结点，相应的，关键字个数为\[m/2\]-1m-1。（深度尽可能小，广度尽可能大）  
B-tree 有以下特性：  
1、关键字集合分布在整棵树中；  
2、任何一个关键字出现且只出现在一个结点中；  
3、搜索有可能在非叶子结点结束；  
4、其搜索性能等价于在关键字全集内做一次二分查找；  
5、自动层次控制；  
  由于限制了除根结点以外的非叶子结点，至少含有 M/2 个儿子，确保了结点的至少利用率，其最低搜索性能为：  
其中，M 为设定的非叶子结点最多子树个数，N 为关键字总数；  
所以 B-树的性能总是等价于二分查找（与 M 值无关），也就没有 B 树平衡的问题；  
  由于 M/2 的限制，在插入结点时，如果结点已满，需要将结点分裂为两个各占 M/2 的结点；删除结点时，需将两个不足 M/2 的兄弟结点合并。  
**2、Full-text 索引**  
  Full-text 索引就是我们常说的全文索引，他的存储结构也是 b-tree。主要是为了解决在我们需要用 like 查询的低效问题。只能解决’xxx%’的 like 查询。如：字段数据为 ABCDE，索引建立为：A、AB、ABC、ABCD、ABCDE 五个。

## 六、 MySQL 中的索引管理 ##

在 MySQL 中，对索引的查看和删除操作是所有索引类型通用的。  
**1、普通索引**  
  这是最基本的索引，它没有任何限制 MyIASM 中默认的 BTREE 类型的索引，也是我们大多数情况下用到的索引。  
**2、创建索引**  
  当数据字段字符较少时（比如3,5,10），可以不指定索引长度，当字段较长时（100，200），指定索引长度有利于提高查询效率。  
  100个字符，取前20个字符作为索引可以排除百分之八十左右的重复数据。  
  如果索引长度很长的话会降低索引效率。100个字符取全部作为索引是没有意义的，反而会降低索引效率，增大空间占用，增大维护力度。  
CREATE INDEX index\_name ON table\_name (column(length))  
ALTER TABLE table\_name ADD INDEX index\_name (column(length))  
CREATE TABLE table\_name (id int not null auto\_increment,title varchar(30) ,PRIMARY  
KEY(id) , INDEX index\_name (title(5)))  
**3、 查看索引**  
SHOW INDEX FROM \[table\_name\]  
SHOW KEYS FROM \[table\_name\] \# 只在 MySQL 中可以使用 keys 关键字。  
**4、 删除索引**  
DROP INDEX index\_name ON talbe\_name  
ALTER TABLE table\_name DROP INDEX index\_name  
ALTER TABLE table\_name DROP PRIMARY KEY  
**5、唯一索引**  
与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值（注意和主键不同）。  
如果是组合索引，则列值的组合必须唯一，创建方法和普通索引类似  
**6、 创建索引**  
CREATE UNIQUE INDEX index\_name ON table\_name (column(length))  
ALTER TABLE table\_name ADD UNIQUE index\_name (column(length))  
CREATE TABLE table\_name (id int not null auto\_increment,title varchar(30) ,PRIMARY  
KEY(id) , UNIQUE index\_name (title(length)))  
**7、全文索引（FULLTEXT）**  
  MySQL 从 3.23.23 版开始支持全文索引和全文检索，FULLTEXT 索引仅可用于 MyISAM表；他们可以从 CHAR、VARCHAR 或 TEXT 列中作为 CREATE TABLE 语句的一部分被创建，或是随后使用 ALTER TABLE 或 CREATE INDEX 被添加。  
对于较大的数据集，将你的资料输入一个没有 FULLTEXT 索引的表中，然后创建索引，其速度比把资料输入现有 FULLTEXT 索引的速度更为快。不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。  
**8、 创建索引**  
CREATE FULLTEXT INDEX index\_name ON table\_name(column(length))  
ALTER TABLE table\_name ADD FULLTEXT index\_name( column)  
CREATE TABLE table\_name (id int not null auto\_increment,title varchar(30) ,PRIMARY  
KEY(id) , FULLTEXT index\_name (title))  
**9、组合索引（最左前缀）**  
CREATE TABLE article(id int not null, title varchar(255), time date);  
  平时用的 SQL 查询语句一般都有比较多的限制条件，所以为了进一步榨取 MySQL 的效率，就要考虑建立组合索引。例如上表中针对 title 和 time 建立一个组合索引：  
ALTER TABLE article ADD INDEX index\_title\_time (title(50),time(10))。  
  索引是从字段数据的最左侧截取的。  
  建立这样的组合索引，其实是相当于分别建立了下面两组组合索引：  
–title,time  
–title  
  为什么没有 time 这样的组合索引呢？这是因为 MySQL 组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始连续组合。并不是只要包含这两列的查询都会用到该组合索引，  
如下面的几个 SQL 所示：  
1.使用到上面的索引  
符合–title,time 的组合索引  
SELECT \* FROM article WHERE title=‘测试’ AND time=1234567890;  
符合–title的组合索引  
SELECT \* FROM article WHERE title=‘测试’;  
2.不使用上面的索引  
SELECT \* FROM article WHERE time=1234567890;  
参考：[https://segmentfault.com/a/1190000008131735\#articleHeader5][https_segmentfault.com_a_1190000008131735_articleHeader5]  
创建索引：  
CREATE INDEX index\_name ON table\_name (column\_list)

## 七、MySQL 中的索引优化 ##

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点。虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE和 DELETE 次数大于查询次数时，放弃索引。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。索引只是提高效率的一个因素，如果你的 MySQL 有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。  
**1、索引不会包含有 NULL 值的列**  
  只要列中包含有 NULL 值都将不会被包含在索引中，组合索引中只要有一列含有 NULL值，那么这一列对于此组合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为 NULL。  
create table table\_name(c1 varchar(32) default ‘0’)  
**2、使用短索引**  
  对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个 CHAR(255)的列，如果在前 10 个或 20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和 I/O 操作。  
CREATE INDEX index\_name ON table\_name (column(length))  
**3、索引列排序**  
  MySQL 查询只使用一个索引，因此如果 where 子句中已经使用了索引的话，那么 orderby 中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。  
**4、like 语句操作**  
  一般情况下不鼓励使用 like 操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引，而 like “aaa%”可以使用索引。  
**5、不要在列上进行运算**  
例如：select \* from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致 索 引 失 效 而 进 行 全 表 扫 描 ， 因 此 我 们 可 以 改 成 ：  
select \* from users where adddate<’2007-01-01′

## 八、 索引总结 ##

最后总结一下，MySQL 只对以下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些时候的 like(不以通配符%或\_开头的情形)。而理论上每张表里面最多可创建 16 个索引，不过除非是数据量真的很多，否则过多的使用索引也不是那么好玩的。（!= 和 <> 会进行全表扫描，降低索引速率）  
建议：一个表的索引数最好不要超过 6 个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

## 九、MySQL 中的 SQL 的常见优化策略 ##

**1、避免全表扫描**  
  对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。  
**2、避免判断 null 值**  
  应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：  
select id from t where num is null  
  可以在 num 上设置默认值 0，确保表中 num 列没有 null 值，然后这样查询：  
select id from t where num=0  
**3、避免不等值判断**  
  应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。  
**4、避免使用 or 逻辑**  
  应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：  
select id from t where num=10 or num=20  
  可以这样查询：  
select id from t where num=10  
union all  
select id from t where num=20  
**5、慎用 in 和 not in 逻辑**  
  in 和 not in 也要慎用，否则会导致全表扫描，如：  
select id from t1 where num in(select id from t2 where id > 10)  
此时外层查询会全表扫描，不使用索引。可以修改为：  
select id from t1,(select id from t1 where id > 10) t2 where [t1.id][] = [t2.id][]  
  此时索引被使用，可以明显提升查询效率。  
**6、注意模糊查询**  
  下面的查询也将导致全表扫描：  
select id from t where name like ‘%abc%’ 模糊查询如果是必要条件时，可以使用 select id from t where name like 'abc%'来实现模糊查询，此时索引将被使用。如果头匹配是必要逻辑，建议使用全文搜索引擎（Elastic search、Lucene、Solr 等）。  
**7、避免查询条件中字段计算**  
  应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：  
select id from t where num/2=100  
应改为:  
select id from t where num=100*2  
**8、避免查询条件中对字段进行函数操作**  
 &emsp*;应尽量避免在 where 子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：\*  
select id from t where substring(name,1,3)=‘abc’–name 以 abc 开头的 id  
应改为:  
select id from t where name like ‘abc%’ 9.9WHERE 子句“=”左边注意点不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。  
**9、组合索引使用**  
  在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。  
**10、不要定义无意义的查询**  
  不要写一些没有意义的查询，如需要生成一个空表结构：  
select col1,col2 into \#t from t where 1=0  
这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：  
create table \#t(…)  
**11、exists**  
  很多时候用 exists 代替 in 是一个好的选择：  
select num from a where num in(select num from b)  
  用下面的语句替换：1表示真，0表示假  
select num from a where exists(select 1 from b where num=a.num)  
**12、索引也可能失效**  
  并不是所有索引对查询都有效，SQL 是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL 查询可能不会去利用索引，如一表中有字段 sex，male、female 几乎各一半，那么即使在 sex 上建了索引也对查询效率起不了作用。  
**13、表格字段类型选择**  
  尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。（数字类型数据无论是比较还是计算，效率都是比字符要高很多）  
  尽可能的使用 varchar 代替 char ，因为首先可变长度字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。（例如：用户名建议用varchar ，密码建议用char ，密码进行加密过后是一个定长。如果没有索引的话char的查询效率要比varchar高，因为varchar会有一个结束符号。如果数据是固定长度的用char，不是固定长度的尽量用varchar。 ）  
**14、查询语法中的字段**  
  任何地方都不要使用 select \* from t ，用具体的字段列表代替“\*”，不要返回用不到的任何字段。返回字段越多，时间越长。  
**15、索引无关优化**  
  尽量不使用 union，union all 等关键字、尽量不使用 or 关键字、尽量使用等值判断。  
  表连接建议不超过 5 个。如果超过 5 个，则考虑表格的设计。（互联网应用中）  
  表连接方式使用外联优于内联。  
  外连接有基础数据存在。如：A left join B,基础数据是 A。  
  A inner join B，没有基础数据的，先使用笛卡尔积完成全连接，在根据连接条件得到内连接结果集。  
  大数据量级的表格做分页查询时，如果页码数量过大，则使用子查询配合完成分页逻辑。  
  Select \* from table limit 1000000, 10  
  Select \* from table where id in (select pk from table limit 100000, 10)

[https_segmentfault.com_a_1190000008131735_articleHeader5]: https://segmentfault.com/a/1190000008131735#articleHeader5
[t1.id]: http://t1.id
[t2.id]: http://t2.id