mysql索引

最新推荐文章于 2024-06-17 15:31:11 发布

转载最新推荐文章于 2024-06-17 15:31:11 发布 · 57 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/seven7seven/p/4754844.html

本文详细解析MySQL索引的存储结构、索引类型、使用策略及优化方法，包括主索引、辅助索引、索引使用策略、索引选择性与前缀索引、InnoDB主键选择与插入优化等内容，旨在提升数据库查询性能。

一、体系结构

存储引擎一般为InnoDB，所以下面的讲解以InnoDB为例。

1.1 配置文件

配置文件为：my.cnf
有多个配置文件，可以用命令查看：

mysql --help | grep my.cnf

查看结果中，后面的配置文件会将前面的覆盖

1.2 InnoDB存储结构

存储单位
页（page）:最小IO单位
区（extent）
段（segment）
InnoDB存储引擎记录必须包含以下三个列：
1. rowid
  其选择顺序如下：
  PRIMARY KEY
  NOT NULL UNIQUE KEY
  系统创建6字节自增的列
  对用户不可见
2. xid（事务ID）：6字节
3. roll_ptr（回滚指针）：7字节

二、索引 http://blog.jobbole.com/24006/

索引在数据库引擎中所用的内部数据结构一般是B+树。

聚集索引：索引中键值的逻辑顺序决定了表中相应行的物理顺序。
非聚集索引：索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。

一个表只有一个聚集索引，默认为主键；
一个表可以有多个非聚集索引，非聚集索引必须以聚集索引为基础；

2.1 主索引(主键索引、聚集索引、一级索引)——索引即数据（如何根据主键索引进行查找）

InnoDB的数据文件本身就是索引文件。表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

上图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键，如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

2.2 辅助索引(非主键索引、非聚集索引、二级索引)——索引值指向主键（如何根据非主键索引进行查找）

辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，下图为定义在Col3上的一个辅助索引：

聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

三、索引使用策略及优化

//调优
show profiles; 
show variables like "%pro%";
set profiling=1;

MySQL的优化主要分为结构优化（Scheme optimization）和查询优化（Query optimization）。本章讨论的高性能索引策略主要属于结构优化范畴。

3.1 最左前缀原理与相关优化

高效使用索引的首要条件是知道什么样的查询会使用到索引，这个问题和B+Tree中的“最左前缀原理”有关，下面通过例子说明最左前缀原理。
这里讨论联合索引，单列索引可以看成联合索引元素数为1的特例。

SHOW INDEX FROM table;//查询表索引
EXPLAIN sql语句;//查询该sql语句执行时索引的使用情况

3.2 LIKE 匹配某列的前缀字符串

如果通配符%不出现在开头，则可以用到索引，但根据具体情况不同可能只会用其中一个前缀

3.3 范围查询

范围列可以用到索引（必须是最左前缀），但是范围列后面的列无法用到索引。同时，索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引。
这里特别要说明MySQL一个有意思的地方，那就是仅用explain可能无法区分范围索引和多值匹配，因为在type中这两者都显示为range。同时，用了“between”并不意味着就是范围查询，例如下面的查询：

EXPLAIN SELECT * FROM employees.titles
WHERE emp_no BETWEEN '10001' AND '10010'
AND title='Senior Engineer'
AND from_date BETWEEN '1986-01-01' AND '1986-12-31';
+
----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+
----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

|  1 | SIMPLE      | titles | range | PRIMARY       | PRIMARY | 59      | NULL |   16 | Using where |
+
----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

看起来是用了两个范围查询，但作用于emp_no上的“BETWEEN”实际上相当于“IN”，也就是说emp_no实际是多值精确匹配。可以看到这个查询用到了索引全部三个列。因此在MySQL中要谨慎地区分多值匹配和范围匹配，否则会对MySQL的行为产生困惑。

3.4 查询条件中含有函数或表达式，则MySQL不会为这列使用索引

3.5 索引选择性与前缀索引

索引并不是越多越好。一般两种情况下不建议建索引。

第一种情况是表记录比较少，我个人的经验是以2000作为分界线，记录数不超过 2000可以考虑不建索引，超过2000条可以酌情考虑索引。
另一种不建议建索引的情况是索引的选择性较低。所谓索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值：
Index Selectivity = Cardinality / #T
显然选择性的取值范围为(0, 1]，选择性越高的索引价值越大，这是由B+Tree的性质决定的。
有一种与索引选择性有关的索引优化策略叫做前缀索引，就是用列的前缀代替整个列作为索引key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销。

SELECT count(DISTINCT(concat(first_name, left(last_name, 4))))/count(*) AS Selectivity FROM employees.employees;//查看索引选择性
ALTER TABLE employees.employees
ADD INDEX `first_name_last_name4` (first_name, last_name(4));//建立前缀索引

前缀索引兼顾索引大小和查询速度，但是其缺点是不能用于ORDER BY和GROUP BY操作，也不能用于Covering index（即当索引本身包含查询所需全部数据时，不再访问数据文件本身）。

3.6 InnoDB的主键选择与插入优化

在使用InnoDB存储引擎时，如果没有特别的需要，请永远使用一个与业务无关的自增字段作为主键。

上文讨论过InnoDB的索引实现，InnoDB使用聚集索引，数据记录本身被存于主索引（一颗B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）。

如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。这样就会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。
如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置。此时MySQL不得不为了将新记录插到合适位置而移动数据。

四、建立索引的一般原则

创建索引而非过度索引
对于查询占主要的应用来说，索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的，或者说没有添加更为有效的索引导致。如果不加索引的话，那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描，如果一张表的数据量很大而符合条件的结果又很少，那么不加索引会引起致命的性能下降。但是也不是什么情况都非得建索引不可，比如性别可能就只有两个值，建索引不仅没什么优势，还会影响到更新速度，这被称为过度索引。
如有一条语句是这样的：select * from users where area=’beijing’ and age=22;
如果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效率，但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age, salary)的复合索引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。
索引不会包含有NULL值的列(例子验证此处说的不对，查阅资料发现null值只是可能会影响索引优化)
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
排序的索引问题
mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
like语句操作
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
不要在列上进行运算
select * from users where YEAR(adddate)<2007;
将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成
select * from users where adddate<‘2007-01-01’;
不使用NOT IN和<>操作
NOT IN和<>(!=)操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替，id<>3(id!=3)则可使用id>3 or id<3来代替。

五、不走索引的情况

1、条件字段选择性弱，查出的结果集较大，不走索引；
2、where条件等号两边字段类型不同，不走索引；
3、优化器分析的统计信息陈旧也可能导致不走索引；
4、索引字段 is null 不走索引；
5、对于count(*)当索引字段有not null约束时走索引，否则不走索引；
6、like 后面的字符当首位为通配符时不走索引；
7、使用不等于操作符如：<>、!= 等不走索引；
8、索引字段前加了函数或参加了运算不走索引；
9、Where条件中使用的是联合索引的非前导列
10、过多的随机读取
11、查询结果占到总行数的20%

转载于:https://www.cnblogs.com/seven7seven/p/4754844.html