MySQL索引优化
在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。
心已赠人
红太狼
我就是我
爱被打了一巴掌
今天药忘吃喽~
还没有评论，来说两句吧...