MySQL索引介绍
1. 索引类型
索引可以提升查询速度,会影响where查询,以及order by排序。MySQL索引类型如下:
- 从索引存储结构划分:B Tree索引、Hash索引、FULLTEXT全文索引、R Tree索引
- 从应用层次划分:普通索引、唯一索引、主键索引、复合索引
- 从索引键值类型划分:主键索引、辅助索引(二级索引)
- 从数据存储和索引键值逻辑关系划分:聚集索引(聚簇索引)、非聚集索引(非聚簇索引)
1.1普通索引
这是最基本的索引类型,基于普通字段建立的索引,没有任何限制。
创建普通索引的方法如下:
- CREATE INDEX <索引的名字> ON tablename (字段名);
- ALTER TABLE tablename ADD INDEX [索引的名字] (字段名);
- CREATE TABLE tablename ( […], INDEX [索引的名字] (字段名) );
1.2 唯一索引
与"普通索引"类似,不同的就是:索引字段的值必须唯一,但允许有空值 。在创建或修改表时追加唯一约束,就会自动创建对应的唯一索引。
创建唯一索引的方法如下:
- CREATE UNIQUE INDEX <索引的名字> ON tablename (字段名);
- ALTER TABLE tablename ADD UNIQUE INDEX [索引的名字] (字段名);
- CREATE TABLE tablename ( […], UNIQUE [索引的名字] (字段名) ;
1.3主键索引
它是一种特殊的唯一索引,不允许有空值。在创建或修改表时追加主键约束即可,每个表只能有一个主键。
创建主键索引的方法如下:
- CREATE TABLE tablename ( […], PRIMARY KEY (字段名) );
- ALTER TABLE tablename ADD PRIMARY KEY (字段名);
1.4复合索引
单一索引是指索引列为一列的情况,即新建索引的语句只实施在一列上;用户可以在多个列上建立索引,这种索引叫做组复合索引(组合索引)。复合索引可以代替多个单一索引,相比多个单一索引复合索引所需的开销更小。
索引同时有两个概念叫做窄索引和宽索引,窄索引是指索引列为1-2列的索引,宽索引也就是索引列超过2列的索引,设计索引的一个重要原则就是能用窄索引不用宽索引,因为窄索引往往比组合索引更有效。
创建组合索引的方法如下:
- CREATE INDEX <索引的名字> ON tablename (字段名1,字段名2…);
- ALTER TABLE tablename ADD INDEX [索引的名字] (字段名1,字段名2…);CREATE TABLE tablename ( […], INDEX [索引的名字] (字段名1,字段名2…) );
复合索引使用注意事项: - 何时使用复合索引,要根据where条件建索引,注意不要过多使用索引,过多使用会对更新操作效率有很大影响。
- 如果表已经建立了(col1,col2),就没有必要再单独建立(col1);如果现在有(col1)索引,如果查询需要col1和col2条件,可以建立(col1,col2)复合索引,对于查询有一定提高。
1.5全文索引
查询操作在数据量比较少时,可以使用like模糊查询,但是对于大量的文本数据检索,效率很低。如果使用全文索引,查询速度会比like快很多倍。
在MySQL 5.6 以前的版本,只有MyISAM存储引擎支持全文索引,从MySQL 5.6开始MyISAM和InnoDB存储引擎均支持。
创建全文索引的方法如下:
- CREATE FULLTEXT INDEX <索引的名字> ON tablename (字段名);
- ALTER TABLE tablename ADD FULLTEXT [索引的名字] (字段名);
- CREATE TABLE tablename ( […], FULLTEXT KEY [索引的名字] (字段名) ;
和常用的like模糊查询不同,全文索引有自己的语法格式,使用 match 和 against 关键字,比如
select * from user where match(name) against('aaa');
全文索引使用注意事项:
- 全文索引必须在字符串、文本字段上建立。
- 全文索引字段值必须在最小字符和最大字符之间的才会有效。(innodb:3-84;myisam:4-84)
- 全文索引字段值要进行切词处理,按syntax字符进行切割,例如b+aaa,切分成b和aaa
- 全文索引匹配查询,默认使用的是等值匹配,例如a匹配a,不会匹配ab,ac。如果想匹配可以在布尔模式下搜索a*
select * from user where match(name) against('a*' in boolean mode);
2. 索引原理
MySQL官方对索引定义:是存储引擎用于快速查找记录的一种数据结构。需要额外开辟空间和数据维护工作。
- 索引是物理数据页存储,在数据文件中(InnoDB,ibd文件),利用数据页(page)存储。
- 索引可以加快检索速度,但是同时也会降低增删改操作速度,索引维护需要代价。
索引涉及的理论知识:二分查找法、Hash和B+Tree。
2.1 二分查找法
二分查找法也叫作折半查找法,它是在有序数组中查找指定数据的搜索算法。它的优点是等值查询、范围查询性能优秀,缺点是更新数据、新增数据、删除数据维护成本高。
(1).首先定位left和right两个指针
(2).计算(left+right)/2判断除2后索引位置值与目标值的大小比对
(3).索引位置值大于目标值就-1,right移动;如果小于目标值就+1,left移动
2.2 Hash结构
Hash底层实现是由Hash表来实现的,是根据键值 <key,value> 存储数据的结构。非常适合根据key查找value值,也就是单个key查询,或者说等值查询。其结构如下所示:
从上面结构可以看出,Hash索引可以方便的提供等值查询,但是对于范围查询就需要全表扫描了。Hash索引在MySQL 中Hash结构主要应用在Memory原生的Hash索引 、InnoDB 自适应哈希索引。
InnoDB自适应哈希索引是为了提升查询效率,InnoDB存储引擎会监控表上各个索引页的查询,当InnoDB注意到某些索引值访问非常频繁时,会在内存中基于B+Tree索引再创建一个哈希索引,使得内存中的 B+Tree 索引具备哈希索引的功能,即能够快速定值访问频繁访问的索引页。
InnoDB自适应哈希索引:在使用Hash索引访问时,一次性查找就能定位数据,等值查询效率要优于B+Tree。
自适应哈希索引的建立使得InnoDB存储引擎能自动根据索引页访问的频率和模式自动地为某些热点页建立哈希索引来加速访问。另外InnoDB自适应哈希索引的功能,用户只能选择开启或关闭功能,无法进行人工干涉。
2.3 B+Tree结构
MySQL数据库索引采用的是B+Tree结构,在B-Tree结构上做了优化改造。
B-Tree结构
- 索引值和data数据分布在整棵树结构中
- 每个节点可以存放多个索引值及对应的data
- 树节点中的多个索引值从左到右升序排列
B树的搜索:从根节点开始,对节点内的索引值序列采用二分法查找,如果命中就结束查找。没有命中会进入子节点重复查找过程,直到所对应的的节点指针为空,或已经是叶子节点了才结束。
B+Tree结构
- 非叶子节点不存储data数据,只存储索引值,这样便于存储更多的索引值
- 叶子节点包含了所有的索引值和data数据
- 叶子节点用指针连接,提高区间的访问性能
相比B树,B+树进行范围查找时,只需要查找定位两个节点的索引值,然后利用叶子节点的指针进行遍历即可。而B树需要遍历范围内所有的节点和数据,显然B+Tree效率高。
2.3 聚簇索引和辅助索引
聚簇索引和非聚簇索引:B+Tree的叶子节点存放主键索引值和行记录就属于聚簇索引;如果索引值和行记录分开存放就属于非聚簇索引。
主键索引和辅助索引:B+Tree的叶子节点存放的是主键字段值就属于主键索引;如果存放的是非主键值就属于辅助索引(二级索引)。
-
聚簇索引(聚集索引)
聚簇索引是一种数据存储方式,InnoDB的聚簇索引就是按照主键顺序构建 B+Tree结构。B+Tree 的叶子节点就是行记录,行记录和主键值紧凑地存储在一起。 这也意味着 InnoDB 的主键索引就是数据表本身,它按主键顺序存放了整张表的数据,占用的空间就是整个表数据量的大小。通常说的主键索引就是聚集索引。
InnoDB的表要求必须要有聚簇索引: -
如果表定义了主键,则主键索引就是聚簇索引
-
如果表没有定义主键,则第一个非空unique列作为聚簇索引
-
否则InnoDB会从建一个隐藏的row-id作为聚簇索引
-
辅助索引
InnoDB辅助索引,也叫作二级索引,是根据索引列构建 B+Tree结构。但在 B+Tree 的叶子节点中只存了索引列和主键的信息。二级索引占用的空间会比聚簇索引小很多, 通常创建辅助索引就是为了提升查询效率。一个表InnoDB只能创建一个聚簇索引,但可以创建多个辅助索引。
索引分析与优化
3.1 EXPLAIN
MySQL 提供了一个 EXPLAIN 命令,它可以对 SELECT 语句进行分析,并输出 SELECT 执行的详细信息,供开发人员有针对性的优化。例如:
explain select * from workflow_base where id < 3;
EXPLAIN 命令的输出内容大致如下:
select_type(查询的类型):
- SIMPLE : 表示查询语句不包含子查询或union
- PRIMARY:表示此查询是最外层的查询(没有嵌套子查询)
- UNION:表示此查询是UNION的第二个或后续的查询
- DEPENDENT UNION:UNION中的第二个或后续的查询语句,使用了外面查询结果
- UNION RESULT:UNION的结果
- SUBQUERY:SELECT子查询语句
- DEPENDENT SUBQUERY:SELECT子查询语句依赖外层查询的结果。
type:
system > const > eq_ref > ref > range > index > All
表示存储引擎查询数据时采用的方式。比较重要的一个属性,通过它可以判断出查询是全表扫描还是基于索引的部分扫描。常用属性值如下,从上至下效率依次增强。
- all:表示全表扫描,性能最差。
- index:表示基于索引的全表扫描,先扫描索引再扫描全表数据。
- range:表示使用索引范围查询。使用>、>=、<、<=、in等等。
- ref:表示使用非唯一索引进行单值查询。
- eq_ref:多出现在多表join查询,表示前面表的每一个记录,都只能匹配后面表的一行结果。
- const:表示使用主键或唯一索引做等值查询,常量查询。
- null:表示不用访问表,速度最快。
possible_keys:
表示查询时能够(可能)使用到的索引。注意并不一定会真正使用,显示的是索引名称。
key:
表示查询时真正使用到的索引,显示的是索引名称。
rows:
MySQL查询优化器会根据统计信息,估算SQL要查询到结果需要扫描多少行记录。原则上rows是越少效率越高,可以直观的了解到SQL效率高低。
key_len:
表示索引中使用的字节数,可以通过该列计算查询中使用的索引长度,在不损失精确性的情况下,长度越短越好,key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_ken是根据表定义计算而得,不是通过表内检索出的。
有个例子,比如我要通过相亲的信息来找对象,目前我只知道性别,筛选了一批女性,这个时候索引长度假如为2,那不能是个女的就可以,我要身材好的,那我再加个索引,查出来了一批,这个时候索引长度为6,依次下去当我想要更多的信息,精度越精细,得到最后一个属于我的女人的时候,这个时候索引长度会很长,有点类似时间换空间的概念。
Extra(额外的信息):
包含不适合在其他列中显示但十分重要的信息:
- Using where:表示查询需要通过索引回表查询数据。
- Using index:表示查询需要通过索引,索引就可以满足所需数据。
- Using filesort:表示查询出来的结果需要额外排序,数据量小在内存,大的话在磁盘,因此有Using filesort建议优化。
- Using temprorary:查询使用到了临时表,一般出现于去重、分组等操作。
3.2 回表查询
InnoDB索引有聚簇索引和辅助索引。聚簇索引的叶子节点存储行记录,InnoDB必须要有,且只有一个。*辅助索引的叶子节点存储的是主键值和索引字段值,通过辅助索引无法直接定位行记录,通常情况下,需要扫码两遍索引树。*先通过辅助索引定位主键值,然后再通过聚簇索引定位行记录,这就叫做回表查询,它的性能比扫一遍索引树低。
总结:通过索引查询主键值,然后再去聚簇索引查询记录信息
3.3 覆盖索引
只需要在一棵索引树上就能获取SQL所需的所有列数据,无需回表,速度更快,这就叫做索引覆盖。
实现索引覆盖最常见的方法就是:将被查询的字段,建立到组合索引。只要保证查询语句里面的字段都在索引中,就无需进行回表查询。
3.4 最左前缀原则
复合索引使用时遵循最左前缀原则,最左前缀顾名思义,就是最左优先,即查询中使用到最左边的列,那么查询就会使用到索引,如果从索引的第二列开始查找,索引将失效。
3.5 LIKE查询
- 1.MySQL在使用like模糊查询时,索引能不能起作用?
MySQL在使用Like模糊查询时,索引是可以被使用的,只有把%字符写在后面才会使用到索引。
select * from user where name like '%o%'; //不起作用
select * from user where name like 'o%'; //起作用
select * from user where name like '%o'; //不起作用
- 2.如何解决like ‘%xxx%’ 字符时索引不被使用的情况?
答案是用覆盖索引避免索引失效,我们这里的索引是(name, age, pos),索引我们在查询的时候不要写select *,只要写具体的字段值,任何一个列被覆盖索引覆盖,就可以解决两边百分号的问题!!!
3.6 NULL查询
面试题:如果MySQL表的某一列含有NULL值,那么包含该列的索引是否有效?
对MySQL来说,NULL是一个特殊的值,从概念上讲,NULL意味着“一个未知值”,它的处理方式与其他值有些不同。比如:不能使用=,<,>这样的运算符,对NULL做算术运算的结果都是NULL,count时不会包括NULL行等,NULL比空字符串需要更多的存储空间等。
NULL列需要增加额外空间来记录其值是否为NULL。对于MyISAM表,每一个空列额外占用一位,四舍五入到最接近的字节。
虽然MySQL可以在含有NULL的列上使用索引,但NULL和其他数据还是有区别的,不建议列上允许为NULL。最好设置NOT NULL,并给一个默认值,比如0和 ‘’ 空字符串等,如果是datetime类型,也可以设置系统当前时间或某个固定的特殊值,例如’1970-01-01 00:00:00’。
3.7 索引与排序
MySQL查询支持filesort和index两种方式的排序,filesort是先把结果查出,然后在缓存或磁盘进行排序操作,效率较低。使用index是指利用索引自动实现排序,不需另做排序操作,效率会比较高。
filesort有两种排序算法:双路排序和单路排序。
**双路排序:**需要两次磁盘扫描读取,最终得到用户数据。第一次将排序字段读取出来,然后排序;第二次去读取其他字段数据。
**单路排序:**从磁盘查询所需的所有列数据,然后在内存排序将结果返回。如果查询数据超出缓存sort_buffer,会导致多次磁盘读取操作,并创建临时表,最后产生了多次IO,反而会增加负担。解决方案:少使用select *;增加sort_buffer_size容量和max_length_for_sort_data容量。
判断使用了哪种方式:
如果我们Explain分析SQL,结果中Extra属性显示Using filesort,表示使用了filesort排序方式,需要优化。如果Extra属性显示Using index时,表示覆盖索引,也表示所有操作在索引上完成,也可以使用index排序方式,建议大家尽可能采用覆盖索引。
1.使用index方式的排序的情况:
- ORDER BY 子句索引列组合满足索引最左前列
explain select id from user order by id; //对应(id)、(id,name)索引有效
- WHERE子句+ORDER BY子句索引列组合满足索引最左前列
explain select id from user where age=18 order by name; //对应(age,name)索引
2.使用filesort方式的排序的情况:
- 对索引列同时使用了ASC和DESC
explain select id from user order by age asc,name desc; //对应(age,name)索引
- WHERE子句和ORDER BY子句满足最左前缀,但where子句使用了范围查询(例如>、<、in等)
explain select id from user where age>10 order by name; //对应(age,name)索引
- ORDER BY或者WHERE+ORDER BY索引列没有满足索引最左前列
explain select id from user order by name; //对应(age,name)索引
- 使用了不同的索引,MySQL每次只采用一个索引,ORDER BY涉及了两个索引
explain select id from user order by name,age; //对应(name)、(age)两个索引
- WHERE子句与ORDER BY子句,使用了不同的索引
explain select id from user where name='tom' order by age; //对应(name)、(age)索引
- WHERE子句或者ORDER BY子句中索引列使用了表达式,包括函数表达式
explain select id from user order by abs(age); //对应(age)索引
3.7 分页查询优化
- 一般性分页
分页查询使用简单的 limit 子句就可以实现。limit格式如下:
SELECT * FROM 表名 LIMIT [offset,] rows
第一个参数指定第一个返回记录行的偏移量,注意从0开始;
第二个参数指定返回记录行的最大数目;
如果只给定一个参数,它表示返回最大的记录行数目;
思考1:如果偏移量固定,返回记录量对执行时间有什么影响?
select * from user limit 10000,1;
select * from user limit 10000,10;
select * from user limit 10000,100;
select * from user limit 10000,1000;
select * from user limit 10000,10000;
结果:在查询记录时,返回记录量低于100条,查询时间基本没有变化,差距不大。随着查询记录量越大,所花费的时间也会越来越多。
思考2:如果查询偏移量变化,返回记录数固定对执行时间有什么影响?
select * from user limit 1,100;
select * from user limit 10,100;
select * from user limit 100,100;
select * from user limit 1000,100;
select * from user limit 10000,100;
结果:在查询记录时,如果查询记录量相同,偏移量超过100后就开始随着偏移量增大,查询时间急剧的增加。(这种分页查询机制,每次都会从数据库第一条记录开始扫描,越往后查询越慢,而且查询的数据越多,也会拖慢总查询速度。)
- 分页优化方案
第一步:利用覆盖索引优化
select * from user limit 10000,100;
select id from user limit 10000,100; // 时间更短
第二步:利用子查询优化
select * from user limit 10000,100;
select * from user where id>= (select id from user limit 10000,1) limit 100
3.7 常用索引规范
1.唯一索引使用uniq_[字段名]来命名(在Innodb,B+Tree的事务型数据库中,索引是通过物理主键来建立关系的,物理主键会作为所有B+Tree的最底层节点,其他所以会跟这个主键建立映射关系)
2.非唯一索引使用idx_[字段名]来命名
3.不建议在频繁更新的字段上建立索引(一旦更新索引会频繁重建,锁表,产生死锁,导入项目瘫痪)
4.非必要不要进行JOIN,如果要进行join查询,被join的字段必须类型相同,并建立索引。()
5.单张表的索引数量建议控制在5个以内,索引过多,不仅会导致插入更新性能下降,还可能导致MYSQL的索引出错和性能下降
6.组合索引字段数量不建议超过5个,理解组合索引的最左匹配原则,避免重复建设索引。比如你建立了(x,y,z) 相当于你建立了(x),(x,y),(x,y,z)必须以X开头
4.索引失效分析
- like查询 以%开头,索引无效;当like前缀没有%,后缀有%时,索引有效。
- 组合索引,不是使用第一列索引,索引失效。(最左匹配原则)
- 数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型,使索引无效,产生全表扫描。(隐式转换造成索引失效,比如select * from user where telephone = ‘150000000’;但是数据库telephone字段设计的是Bigint类型,这条sql不会出错,执行中隐式转换把string类型转成Bigint类型造成索引失效,不会被命中)
- or语句前后没有同时使用索引。当or左右查询字段只有一个是索引,该索引失效,只有当or左右查询字段均为索引时,才会生效(select * from user where telephone = ‘150000000’ or name = ‘张三’;索引只存在user表的telephone字段,name字段没有索引)
- 在索引字段上使用not,<>(范围),!=。不等于操作符是永远不会用到索引的,因此对它的处理只会产生全表扫描。 优化方法: key<>0 改为 key>0 or key<0。
- 对索引字段进行计算操作、字段上使用函数,比如to_char、to_date、to_number等,包括加减乘除的谓词运算。
- 当全表扫描速度比索引速度快时,mysql会使用全表扫描,此时索引失效。
- 索引无法存储null值。查询时,采用is null条件时,不能利用到索引,只能全表扫描。原因:索引是有序的。NULL值进入索引时,无法确定其应该放在哪里。
如果需要把空值存入索引,方法有二:其一,把NULL值转为一个特定的值,在WHERE中检索时,用该特定值查找。
基础规范
1.表必须有主键,建议使用整型作为主键(因为字符串占的磁盘空间更大,Long占4个字节,BigInt占8个字节,varchar类型33个字节)
2.禁止使用外键(影响性能和深度),表之间的关联性和完整性通过应用层来控制
3.表在设计之初,应该考虑到大致的数据级,若表记录小于1000万,尽量使用单表,不建议分表。
4.建议将大字段,访问频率低,或者不需要作为筛选条件的字段拆分到拓展表中,(做好表垂直拆分)
5.控制单实例表的总数,单个表分表数控制在1024以内。
列设计规范
1.正确区分tinyint、int、bigint的范围
2.使用varchar(20)存储手机号,不要使用整数(隐式转换造成索引失效,比如select * from user where telephone = ‘150000000’;但是数据库telephone字段设计的是Bigint类型,这条sql不会出错,执行中隐式转换把string类型转成Bigint类型造成索引失效,不会被命中)
3.使用int存储ipv4 不要使用char(15),方便大小的比较
4.涉及金额使用decimal或者varchar(居多,不用考虑精度),并制定精度,float和double会产生精度丢失
5.不要设计null的字段,而是用空字符,因为null需要更多的空间,并且使得索引和统计变得更复杂。
SQL规范
Mysql查看每条sql执行的时间:
show variables like 'profiling';
set profiling =1;
show profiles;
1.禁止使用selet ,只获取必要字段,select 会增加cpu/i0/内存、带宽的消耗。
2.insert 必须指定字段,禁止使用insert into Table values().指定字段插入,在表结果变更时,能保证对应应用程序无影响。
3.隐私类型转换会使索引失效,导致全表扫描。(比如:手机号码搜索时未转换成字符串)
4.禁止在where后面查询列使用内置函数或者表达式,导致不能命中索引,导致全表扫描
5.禁止负向查询(!=,not like ,no in等)以及%开头的模糊查询,造成不能命中索引,导致全表扫描
6.避免直接返回大结果集造成内存溢出,可采用分段和游标方式。
7.返回结果集时尽量使用limit分页显示。(limit x,y;x过大,查询速度会很慢)
Mysql中先过滤,在分页查询
8.尽量在order by/group by的列上创建索引。
9.大表扫描尽量放在镜像库上去做
10.禁止大表join查询和子查询—字段冗余
11.尽量避免数据库内置函数作为查询条件
12.应用程序尽量捕获SQL异常
表的垂直拆分
垂直拆分:业务模块拆分、商品库,用户库,订单库
水平拆分:对表进行水平拆分(也就是我们说的:分表)
表进行垂直拆分:表的字段过多,字段使用的频率不一。(可以拆分两个表建立1:1关系)
将一个属性过多的表,一行数据较大的表,将不同的属性分割到不同的数据库表中。以降低单库表的大小。
特点:
- 每个表的结构不一致
- 每个表的数量都是全量
- 表和表之间一定会有一列会进行关联,一般都是主键
原则:
- 将长度较短,访问频率较高的字段放在一个表中,主表
- 将长度较长、访问频率比较低的字段放一个表中
- 将经常访问字段放一个表中。
- 所有表的并集是全量数据。
如何平滑添加字段
场景:在开发时,有时需要给表加字段,在大数据量且分表的情况下,怎么样平滑添加。
1:直接alter table add column,数据量大时不建议,新增列,产生表锁,锁住表,就无法向这个表插入数据(会产生写锁)
alter table ksd_user add column api_pay_no varchar(32) not null comment ‘用户扩展订单号’
alter table ksd_user add column api_pay_no varchar(32) not null unique comment ‘用户扩展订单号’
2:提前预留字段(不优雅:造成空间浪费,预留多少很难控制,拓展性差,建议全部预留varchar类型)
3:新增一张表,(增加字段),迁移原表数据,在重新命名新表作为原表。
4:放入extinfo(无法使用索引)
5:提前设计,使用key/value方法存储,新增字段时 ,直接加一个key就好了(优雅)比如:Redis
推荐1 2 5(最好的方式)