MySQL索引优化
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL索引优化,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含5960字,纯文字阅读大概需要9分钟。
内容图文
![MySQL索引优化](/upload/InfoBanner/zyjiaocheng/863/6dd8dd10d70a4bfdb1f52a9d95f8b8e1.jpg)
在MySQL中,我们经常会为表中的某些字段建立索引,那么怎么通过索引优化我们的查询呢?
索引的分类与基本概念
索引可以分为以下五种:
-
普通索引:仅加速查询。
-
唯一索引:普通索引+列值唯一(可以有null)。
-
主键索引:唯一索引+列值不能为空。
-
组合索引:多列值组成一个索引,专门用于组合搜索,其效率大于索引合并。
-
全文索引:对文本的内容进行分词,进行搜索,通常用于varchar或text字段(只支持MyISAM引擎)。
根据索引的存储方式还可以分为以下两种:
-
聚簇索引(Clustered Index):数据与索引存储在一起,主键索引底层就是用聚簇索引实现的。
-
非聚簇索引(Secondary Index):数据与索引分开存放,其他非主键索引底层都是非聚簇索引实现的,也叫二级索引。
聚簇索引的优点与缺点:
-
优点:因为索引和数据存在为同一棵B+树中,所以访问数据速度快。
-
缺点:插入速度严重依赖于插入顺序,按照主键的顺序插入是最快的方式,更新聚簇索引列的代价很高,因为会强制将每个被更新的行移动到新的位置,基于聚簇索引的表在插入新行,或者主键被更新导致需要移动行的时候,可能面临页分裂的问题,聚簇索引可能导致全表扫描变慢,尤其是行比较稀疏,或者由于页分裂导致数据存储不连续的时候。
几个基本概念:
-
索引合并:使用多个单列索引组合搜索。
-
回表查询:在非聚簇索引上并没有存放数据行,存放的只是主键ID,如果需要查询除索引列、主键列的其他列就需要回到聚簇索引上根据主键ID查询。
-
覆盖索引:查询的数据列从索引中就能够获取到,不必回表查询,换句话说就是所查询的列能被所建的索引覆盖到。
索引的使用
创建索引
可以在创建表时创建索引,格式如下:
CREATE TABLE 表名[字段名 数据类型] [UNIQUE|FULLTEXT|SPATIAL|...] [INDEX|KEY] [索引名字] (字段名[length])
创建表时创建索引:
create table t1 (id int, name varchar(50), index idx_id(id)); -- 创建普通索引
create table t2 (id int, name varchar(50), unique index idx_id(id)); -- 创建唯一索引
create table t3 (id int, name varchar(50), primary key(id)); -- 创建主键索引
create table t4 (id int primary key, name varchar(50)); -- 创建主键索引
create table t5 (id int, name varchar(50), age int, index idx_id_name_age(id, name, age)); -- 创建组合索引
表已经创建好后增加索引,语法:
ALTER TABLE 表名 ADD[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [索引名] (索引字段名(长度))
使用:
alter table t5 add index idx_name(name);
使用CREATE INDEX创建索引,语法:
CREATE [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] 索引名称 ON 表名(创建索引的字段名[length])
使用:
create index idx_age on t5(age);
查询索引
mysql> show index from t5;
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t5 | 1 | idx_id_name_age | 1 | id | A | 0 | NULL | NULL | YES | BTREE | | |
| t5 | 1 | idx_id_name_age | 2 | name | A | 0 | NULL | NULL | YES | BTREE | | |
| t5 | 1 | idx_id_name_age | 3 | age | A | 0 | NULL | NULL | YES | BTREE | | |
| t5 | 1 | idx_name | 1 | name | A | 0 | NULL | NULL | YES | BTREE | | |
| t5 | 1 | idx_age | 1 | age | A | 0 | NULL | NULL | YES | BTREE | | |
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.05 sec)
删除索引
使用ALTER删除索引:
alter table t5 drop index idx_name;
使用DROP删除索引:
drop index idx_age on t5;
索引的最佳实践
-
避免使用select *,实际业务中需要哪些属性就获取哪些(按需获取),假设需要获取的列都在索引上面,这样就能直接返回,无需回表查询。
-
当使用索引列进行查询的时候不要使用表达式,这样会导致索引失效,可以把计算放到业务层而不是数据库层。
-
当使用索引列进行查询的时候不要使用not in,<>,这样会导致索引失效。
-
当使用索引列进行查询的时候,like '%xxx%'不会走索引,like 'xxx%'会走索引的一部分。
-
当使用索引列进行查询的时候,条件的类型与字段的类型需保持一致,字符串类型除外,字符串类型可以转换为任意类型。
-
尽量使用主键查询,而不是其他索引,因为主键查询不会触发回表查询。
-
更新十分频繁,数据区分度不高的字段上不宜建立索引,更新会变更B+树,更新频繁的字段建议索引会大大降低数据库性能,类似于性别这类区分不大的属性,建立索引是没有意义的,不能有效的过滤数据,一般区分度在80%以上的时候就可以建立索引,区分度可以使用 count(distinct(列名))/count(*) 来计算。
-
当需要进行表连接的时候,最好不要超过三张表,因为需要join的字段,数据类型必须一致。
-
能使用limit的时候尽量使用limit。
-
单表索引建议控制在5个以内,单索引字段数不允许超过5个(组合索引)。
-
exists代替in。
-
最左前缀原则。
关于exists代替in
exists对外表用loop逐条查询,每次查询都会查看exists的条件语句,当 exists里的条件语句能够返回记录行时(无论记录行是的多少,只要能返回),条件就为真,返回当前loop到的这条记录,反之如果exists里的条件语句不能返回记录行,则当前loop到的这条记录被丢弃,exists的条件就像一个bool条件,当能返回结果集则为true,不能返回结果集则为false。
假设现在有如下两条SQL语句:
select * from A where exists (select * from B where B.id = A.id);
select * from A where A.id in (select id from B);
查询1可以转化以下伪代码,便于理解:
j = 0;
for (i = 0; i < count(A); i++) {
a = get_record(A, i); // 从A表逐条获取记录
if (B.id == a.id) // 如果子条件成立
result[j++] = a;
}
return result;
大概就是这么个意思,其实可以看到,查询1主要是用到了B表的索引,A表如何对查询的效率影响不大。
假设B表的所有id为1,2,3,查询2可以转换为:
select * from A where A.id = 1 or A.id = 2 or A.id = 3;
这个好理解了,这里主要是用到了A的索引,B表如何对查询影响不大。
如果查询的两个表大小相当,那么用in和exists差别不大。
如果两个表中一个较小,一个是大表,则子查询表大的用exists,子查询表小的用in。
内容总结
以上是互联网集市为您收集整理的MySQL索引优化全部内容,希望文章能够帮你解决MySQL索引优化所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。