Mysql索引

zhangswww
1,529 阅读19分钟

概念

官方定义：索引是帮助mysql高效获取数据的数据结构。所以索引的本质就是数据结构，可以理解为排好序的快速查找的数据结构。
数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

优势

  • 类似于图书馆书目索引，提高数据检索效率，降低数据库的io成本
  • 通过索引列对数据进行排序，降低数据排序的成本，降低cpu的消耗

劣势

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要更新数据，还要更新一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。同时索引还会占用一定的磁盘空间。

分类

  • 单值索引
    一个索引只包含单个列，一个表可以有多个单列索引
  • 唯一索引
    索引列的值必须唯一，但允许空值
  • 复合索引
    即一个索引包含多个列

语法

  • 创建
    1. CREATE [UNIQUE] INDEX indexName ON tableName(字段名...);
    2. ALTER TABLE tableName ADD [UNIQUE] INDEX indexName (字段名...);
  • 删除
    DROP INDEX indexName ON tableName;
  • 查看
    SHOW INDEX FROM tableName

索引的最佳实践

哪些情况创建索引

  1. 主键自动建立唯一索引
  2. 频繁作为查询条件的字段应该创建索引(where 后面的语句或者ORDER BY 语句中出现的列)
  3. 查询中与其他表关联的字段，外键关系建立索引
  4. 单键、组合索引的选择问题（在高并发下推荐创建组合索引）
  5. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
  6. 查询中统计或者分组的字段

哪些情况不创建索引

  1. 表记录太少
  2. 经常增删改的表
  3. 数据重复且分布平均的表字段，因此只为最经常查询和最经常排序的数据列创建索引。注意，如果某个数据列包含许多重复的内容，为他建立索引就没有太大的实际效果。（比如14亿中国人的国籍都是中国，这种类型字段就可以不建立索引，或者性别）
  4. Where条件里用不到的字段不创建索引

性能分析

使用EXPLAIN关键字可以模拟优化器执行sql查询语句，从而知道mysql是如何处理你的sql语句。分析你的查询语句或是表结构的性能瓶颈。

语法

explain sql语句

相关字段

每列的定义如下

  • id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符
    1. id相同，执行顺序由上至下
    2. id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
  • select_type: SELECT 查询的类型.主要是用于区别普通查询、联合查询、子查询等的复杂查询
    1. SIMPLE：简单的 select 查询,查询中不包含子查询或者UNION
    2. PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为Primary
    3. DERIVED：在FROM列表中包含的子查询被标记为DERIVED(衍生)MySQL会递归执行这些子查询, 把结果放在临时表里。
    4. SUBQUERY：在SELECT或WHERE列表中包含了子查询
    5. UNION：若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED
    6. UNCACHEABLE SUBQUREY：无法被缓存的子查询
    7. DEPENDENT SUBQUERY：在SELECT或WHERE列表中包含了子查询,子查询基于外层
    8. UNION RESULT：从UNION表获取结果的SELECT
  • table: 查询的是哪个表, 显示这一行的数据是关于哪张表的
  • type: join 类型（决定性能的指标） 显示查询使用了何种类型，从最好到最差依次是： system>const>eq_ref>ref>range>index>ALL
    1. system：表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计
    2. const：表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快如将主键置于where列表中，MySQL就能将该查询转换为一个常量
    3. eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
    4. ref：非唯一性索引扫描，返回匹配某个单独值的所有行.本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体（例如：根据姓名查询人的信息，姓名是可以重复的，并不是唯一的字段）
    5. range：只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引 一般就是在你的where语句中出现了between、<、>、in等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。
    6. Index：Full IndexScan，index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）
    7. All：Full Table Scan，将遍历全表以找到匹配的行
      备注：一般来说，得保证查询至少达到range级别，最好能达到ref。
  • possible_keys: 此次查询中可能选用的索引显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
  • key: 此次查询中确切使用到的索引
  • Key_len: 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。key_len字段能够帮你检查是否充分的利用上了索引
  • ref: 哪个字段或常数与key一起被使用.显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
  • rows: 显示此查询一共扫描了多少行. 这个是一个估计值.（找到所需记录扫描的行数）
  • filtered: 表示此查询条件所过滤的数据的百分比
  • extra: 包含不适合在其他列中显示但十分重要的额外信息
    1. Using filesort:说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”
    2. Using where：表明使用了where过滤
    3. Using Index ：表示索引覆盖(Covering Index)，不会回表查询（"覆盖索引扫描",表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错）
    4. Using temporary: 使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。
    5. Covering Index：覆盖索引，就是查询的列要被所建的索引覆盖

主要字段：Id/type/key/rows/Extra

查询优化

索引的失效情况以及索引的优化

测试sql

CREATE TABLE staffs (
 id INT PRIMARY KEY AUTO_INCREMENT,
 NAME VARCHAR (24) NULL DEFAULT '' COMMENT '姓名',
 age INT NOT NULL DEFAULT 0 COMMENT '年龄',
 pos VARCHAR (20) NOT NULL DEFAULT '' COMMENT '职位',
 add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间'
) CHARSET utf8 COMMENT '员工记录表' ;

INSERT INTO staffs(NAME,age,pos,add_time) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('2000',23,'dev',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES(null,23,'dev',NOW());
SELECT * FROM staffs;

ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name, age, pos);
  1. 全值匹配我最爱
    索引 idx_staffs_nameAgePos 建立索引时 以 name ， age ，pos 的顺序建立的。全值匹配表示 按顺序匹配的
    EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';

可以看到使用到了索引，并且是ref级别的
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25;

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25 AND pos = 'dev';

2. 最佳左前缀法则
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
and 忽略左右关系。既即使没有没有按顺序 由于优化器的存在，会自动优化。
经过试验结论建立了 idx_nameAge 索引id 为主键
当使用覆盖索引的方式时，(select name/age/id from staffs where age=10 (后面没有其他没有索引的字段条件))，即使不是以 name 开头，也会使用 idx_nameAge 索引。 既 select 后的字段 有索引，where 后的字段也有索引，则无关执行顺序。
除开上述条件 才满足最左前缀法则。

EXPLAIN SELECT * FROM staffs WHERE age = 25 AND pos = 'dev';

可以看到并没有使用到索引，原因是查询条件中跳过了索引的第一个字段。
EXPLAIN SELECT * FROM staffs WHERE pos = 'dev';

3. 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描
EXPLAIN SELECT * FROM staffs WHERE left(NAME,4) = 'July';

4. 存储引擎不能使用索引中范围条件右边的列
EXPLAIN SELECT * FROM staffs WHERE name = 'z3' and age > 20 and pos = 'manmger';
5. 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *

可以看出使用覆盖索引性能会更好
6. mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
使用 != 和 <> 的字段索引失效(!= 针对数值类型。 <> 针对字符类型
前提 where and 后的字段在混合索引中的位置比当前字段靠后 where age != 10 and name='xxx' ,这种情况下，mysql自动优化，将 name='xxx' 放在 age ！=10 之前，name 依然能使用索引。只是 age 的索引失效)
7. is not null 也无法使用索引,但是is null是可以使用索引的
8. like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作
这种情况会导致索引失效全表扫描，而abc%这种情况下是可以使用索引的
解决方案：这种情况下尽量使用覆盖索引（即select后面的字段和所建立的索引的字段一致），部分情况下可以解决这种索引失效问题
9. 字符串不加单引号索引失效
例如某字段name的名称为100，是VARCHAR类型的但是作为查询条件100并没有加''号，底层进行转换使索引失效，使用了函数造成索引失效
10. 少用or,用它来连接时会索引失效

一般性建议

  • 对于单键索引，尽量选择针对当前query过滤性更好的索引
  • 在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。(避免索引过滤性好的索引失效)
  • 在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引
  • 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

单表查询优化

sql

CREATE TABLE IF NOT EXISTS `article` (
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`author_id` INT(10) UNSIGNED NOT NULL,
`category_id` INT(10) UNSIGNED NOT NULL,
`views` INT(10) UNSIGNED NOT NULL,
`comments` INT(10) UNSIGNED NOT NULL,
`title` VARBINARY(255) NOT NULL,
`content` TEXT NOT NULL
);

INSERT INTO `article`(`author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES
(1, 1, 1, 1, '1', '1'),
(2, 2, 2, 2, '2', '2'),
(1, 1, 3, 3, '3', '3');

SELECT * FROM article;
  • 查询 category_id 为1 且 comments 大于 1 的情况下,views 最多的 article_id。
    EXPLAIN SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;

结论：很显然,type 是 ALL,即最坏的情况。Extra里还出现了Usingfilesort,也是最坏的情况。优化是必须的。
开始优化：
1.1 新建索引+删除索引
ALTER TABLE article ADD INDEX idx_article_ccv ( category_id , comments, views );
create index idx_article_ccv on article(category_id,comments,views);
DROP INDEX idx_article_ccv ON article

1.2 第2次EXPLAIN
EXPLAIN SELECT id,author_id FROM article WHERE category_id = 1 AND comments >1 ORDER BY views DESC LIMIT 1;

结论： type 变成了 range,这是可以忍受的。但是 extra 里使用 Using filesort 仍是无法接受的。 但是我们已经建立了索引,为啥没用呢?
这是因为按照 BTree 索引的工作原理, 先排序 category_id, 如果遇到相同的 category_id 则再排序 comments,如果遇到相同的 comments 则再排序 views。 当 comments 字段在联合索引里处于中间位置时, 因comments > 1 条件是一个范围值(所谓 range), MySQL 无法利用索引再对后面的 views 部分进行检索,即 range 类型查询字段后面的索引无效。

1.3 删除第一次建立的索引 DROP INDEX idx_article_ccv ON article;

1.4 第2次新建索引
ALTER TABLE article ADD INDEX idx_article_cv ( category_id , views ) ; create index idx_article_cv on article(category_id,views);

1.5 第3次EXPLAIN
EXPLAIN SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;

结论：可以看到,type 变为了 ref,Extra 中的 Using filesort 也消失了,结果非常理想。 DROP INDEX idx_article_cv ON article;

关联查询优化

sql

CREATE TABLE IF NOT EXISTS `class` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `book` (
`bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`bookid`)
);


INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));


INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  • 下面开始explain分析
    EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;

结论：type有All
添加索引优化
ALTER TABLE book ADD INDEX Y ( card);
第2次explain
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;

可以看到第二行的type变为了ref,rows也变成了优化比较明显。
这是由左连接特性决定的。LEFT JOIN条件用于确定如何从右表搜索行,左边一定都有, 所以右边是我们的关键点,一定需要建立索引。

删除旧索引+新建+第3次explain
DROP INDEX Y ON book;
ALTER TABLE class ADD INDEX X (card);
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;

建议

  • 保证被驱动表的join字段已经被索引
  • left join 时，选择小表作为驱动表，大表作为被驱动表。
  • inner join 时，mysql会自己帮你把小结果集的表选为驱动表。
  • 子查询尽量不要放在被驱动表，有可能使用不到索引。

子查询优化

用in 还是 exists
有索引 大表驱动小表
select sql_no_cache sum(sal) from emp where deptno in (select deptno from dept);
select sql_no_cache sum(sal) from emp where exists (select 1 from dept where emp.deptno=dept.deptno);
用 exists 是否存在，存在返回一条记录，exists 是作为一个查询判断用，所以 select 后返回什么不重要。
select sql_no_cache sum(sal) from emp inner join dept on emp.deptno=dept.deptno;

有索引 小表驱动大表
select sql_no_cache sum(e.sal) from (select * from emp where id<10000) e where exists (select 1 from emp where e.deptno=emp.deptno);
select sql_no_cache sum(e.sal) from (select * from emp where id<10000) e inner join (select distinct deptno from emp) m on m.deptno=e.deptno;
select sql_no_cache sum(sal) from emp where deptno in (select deptno from dept);

有索引小驱动大表性能优于大表驱动小表
无索引 小表驱动大表
select sql_no_cache sum(e.sal) from (select * from emp where id<10000) e where exists (select 1 from emp where e.deptno=emp.deptno);
select sql_no_cache sum(e.sal) from (select * from emp where id<10000) e inner join (select distinct deptno from emp) m on m.deptno=e.deptno;
select sql_no_cache sum(sal) from emp where deptno in (select deptno from dept);

无索引大表驱动小表
select sql_no_cache sum(sal) from emp where deptno in (select deptno from dept);
select sql_no_cache sum(sal) from emp where exists (select 1 from dept where emp.deptno=dept.deptno);
select sql_no_cache sum(sal) from emp inner join dept on emp.deptno=dept.deptno;
结论
有索引的情况下 用 inner join 是最好的 其次是 in ，exists最糟糕
无索引的情况下用
小表驱动大表 因为join 方式需要distinct ，没有索引distinct消耗性能较大
所以 exists性能最佳 in其次 join性能最差？
无索引的情况下大表驱动小表
in 和 exists 的性能应该是接近的 都比较糟糕 exists稍微好一点 超不过5% 但是inner join 优于使用了 join buffer 所以快很多
如果left join 则最慢

order by关键字优化

ORDER BY子句，尽量使用Index方式排序,避免使用FileSort方式排序
MySQL支持二种方式的排序，FileSort和Index，Index效率高. 它指MySQL扫描索引本身完成排序。FileSort方式效率较低。
ORDER BY满足两情况，会使用Index方式排序:

  • ORDER BY 语句使用索引最左前列
  • 使用Where子句与Order BY子句条件列组合满足索引最左前列
  • where子句中如果出现索引的范围查询(即explain中出现range)会导致order by 索引失效。

尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀
如果不在索引列上，filesort有两种算法：mysql就要启动双路排序和单路排序
双路排序

  • MySQL 4.1之前是使用双路排序,字面意思就是两次扫描磁盘，最终得到数据， 读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。多路排序需要借助 磁盘来进行排序。所以 取数据，排好了取数据。两次 io操作。比较慢单路排序 ，将排好的数据存在内存中，省去了一次io操作，所以比较快，但是需要内存空间足够。
  • 从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。

单路排序

  • 取一批数据，要对磁盘进行了两次扫描，众所周知，I\O是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序。从磁盘读取查询需要的所有列，按照orderby列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间，因为它把每一行都保存在内存中了。

单路排序引申出的问题
在sort_buffer中，方法B比方法A要多占用很多空间，因为方法B是把所有字段都取出,所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取取sort_buffer容量大小，再排……从而多次I/O。本来想省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失。

优化策略

  • 增大sort_buffer_size参数的设置
    用于单路排序的内存大小
  • 增大max_length_for_sort_data参数的设置
    单次排序字段大小。(单次排序请求)
  • 去掉select 后面不需要的字段
    select后的多了，排序的时候也会带着一起，很占内存，所以去掉没有用的

GROUP BY关键字优化

  • group by实质是先排序后进行分组，遵照索引建的最佳左前缀
  • 当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置
  • where高于having，能写在where限定的条件就不要去having限定了。

去重优化

尽量不要使用distinct关键字去重
例如：
t_mall_sku 表   id  shp_id      kcdz                
------  ------ --------------------
     3       1    北京市昌平区  
     4       1    北京市昌平区  
     5       5    北京市昌平区  
     6       3       重庆              
     8       8     天津              
例子：
select kcdz form t_mall_sku where id in( 3,4,5,6,8 ) 将产生重复数据，
select distinct kcdz form t_mall_sku where id in( 3,4,5,6,8 ) 使用 distinct 关键字去重消耗性能
优化： select  kcdz form t_mall_sku where id in( 3,4,5,6,8 )  group by kcdz 能够利用到索引

案例

。。。。。。

avatar
文章
阅读
粉丝