-
索引概述:
-
索引是帮助MySQL高效获取数据的数据结构(有序)
-
-
索引结构:
-
MySQL的索引是在存储引擎层实现的,不同的存储引擎由不同的结构,主要包含这几种
-
B+tree 索引
-
最常见的索引类型,大部分引擎都支持B+树索引
-
-
Hash 索引
-
底层数据结构是用哈希表实现的,只有精确匹配索引列的查询才有效,不支持范围查询
-
-
R-tree (空间索引)
-
空间索引是MyISAM引擎的一个特殊索引类型,主要用于地理空间数据类型,通常比较少
-
-
Full-text (全文索引)
-
是一种通过建立倒排索引,快速匹配文档的方式。类似于Lucene,Solr,ES
-
-
-
索引
存储引擎:InnoDB、MyISAM、Memory -
B+tree:支持、支持、支持
-
Hash:不支持、不支持、支持
-
R-tree:不支持、支持、不支持
-
Full-text:5.6版本后支持、支持、不支持
-
二叉树缺点:顺序插入时,会形成一个链表,查询性能大大降低。大数据量情况下,层级较深,检索速度慢
-
红黑树:大数据量情况下,层级较深,检索速度慢
-
B+Tree相比于B-Tree区别
-
所有的数据都会出现在叶子节点
-
叶子节点形成一个单向链表
-
在MySQL里进行了优化,形成了带有顺序指针的B+Tree
-
-
-
Hash
-
哈希索引就是采用一定的hash算法,将键值换算成新的hash值,映射到对应的槽位上,任何储存到hash表中,如果两个(或多个)键值,映射到一个相同的槽位上,就形成了hash冲突/碰撞,可以通过链表来解决
-
特点:
-
Hash索引只能用于对等比较(=,in),不支持范围查询
-
无法利用索引完成排序操作
-
查询效率高,通常只需要一次检索就可以了,效率通常高于B+Tree索引
-
-
-
补充:数据结构可视化网站Data Structure Visualization,可以去插入数据玩玩(需右键翻译)
索引分类:
-
在InnoDB存储引擎中,根据索引的存储形式,又可以分为以下两种:
-
聚焦索引(Clustered Index)
-
将数据存储与所有放到了一块,索引结构的叶子节点保存了行数据
-
必须有,而且只有一个
-
-
二级索引(Secondary Index)
-
将数据与所有分开存储,所有结构的叶子节点关联的是对应的主键
-
可以存在多个
-
-
-
聚集索引选取规则:
-
如果存在主键,主键索引就是聚集索引
-
如果不存在索引,将第一个唯一索引作为聚集索引
-
如果表没有主键,或没有合适的唯一索引,则InnoDB会自动生成一个rowid作为隐藏的聚集索引
-
-
-
-
索引语法
-
创建索引
-
create [unique|fulltext] index 索引名 on 表名(索引列...);
-
-
查看索引
-
show index from 表名;
-
-
删除索引
-
drop index 索引名 on 表名;
-
-
-
SQL性能分析
-
SQL执行频率
-
MySQL客服端连接成功后,通过show [session|global] status 命令可以提供服务器状态信息。
-
通过该指令可以查看当前数据库INSERT、UPDATE、DELETE、SELECT访问频次
-
show global status like 'Com_______';
-
模糊匹配,七个_
-
-
-
-
慢查询日志
-
慢查询日志记录了索引执行时间超过指定参数(long_query_time,单位:秒,默认10秒)的所有SQL语句的日志
-
MySQL的慢查询日志默认没有开启,需要在MySQL的配置文件(/etc/my.cnf)中配置信息:
-
开启MySQL慢查询日志开关
-
slow_query_log=1;
-
-
设置慢查询日志的时间为2秒
-
long_query_time=2;
-
-
-
配置完毕后,通过指令重启MySQL服务器进行测试,查看慢查询日志文件中记录的信息/var/lib/mysql/localhost-slow.log
-
查看慢查询日志是否开启
-
show variables like 'slow_query_log';
-
-
-
profile详情
-
show profiles 能够在做SQL优化时帮助我们了解时间的耗费在哪里。通过have_profiling参数,能够看到当前MySQL是否支持profile操作:
-
select @@have_profiling
-
或者show variables like 'have_pro%';
-
-
-
默认profiling是关闭的,可以通过set语句在session/global级别开启profiling:
-
set profiling=1;
-
-
执行一系列SQL的操作,然后通过这些指令查看指令的执行耗时:
-
查看每一条SQL的耗时基本情况
-
show profiles;
-
-
查看指定query_id的SQL语句各个阶段的耗时情况
-
show profile for query query_id;
-
-
查看指定query_id的SQL语句CPU的使用情况
-
show profile cpu for query query_id;
-
-
-
-
explain执行计划
-
explain或者desc命令获取MySQL如何执行select语句的信息,包括select语句执行过程中表如何连接和连接的顺序
-
语法:
-
直接在select语句之前加上关键字explain/desc
-
explain select 字段列表 from 表名 where 条件;
-
-
-
explain执行计划各字段含义:
-
id
-
select查询的序列号,表示查询中执行select子句或者是操作表的顺序(id相同,执行顺序从上到下;id不同,值越大,越先执行)
-
-
select_type
-
表示select的类型,常见的取值有SIMPLE(simple 简单表,即不使用表连接或子查询)、PRIMARY(primary 主查询,即外层的查询)、UNION(union中的第二个或者后面的查询语句)、SUBQUERY(select/where之后包含了子查询)等
-
-
type
-
表示连接类型,性能由好到差的连接类型为NULL、system、const、en_ref、ref、range、index、all
-
-
possible_key
-
显示可能应用在这张表上的索引,一个或多个
-
-
Key
-
实际使用的索引,如果为NULL,则没有使用索引
-
-
Key_len
-
表示索引中使用的字节数,该值为索引字段最大可能长度,并非实际使用长度,在不损失精确度的前提下,长度越短越好
-
-
rows
-
MySQL认为必须要执行查询的行数,在innodb引擎的表中,是一个估计值,可能并不总是准确的
-
-
filerted
-
表示返回的行数占需要读取行数的百分比,filtered的值越大越好
-
-
-
-
-
索引使用
-
最左前缀法则
-
如果索引了多列(联合索引),要遵循最左前缀法则。最左前缀法则指的是查询从索引的最左列开始,并不能跳过索引中的列,如果跳过某一列,索引将部分失效(后面的字段索引失效)
-
-
范围查询
-
联合索引中,如果出现范围查询(>,<),范围查询右侧的列索引失效
-
但用>=,<=时不会
-
-
-
索引列计算
-
不要在索引列上进行运算操作,索引将失效
-
如explain select * from tu_user where substring(phone,10,2)='15';
-
-
-
字符串不加引号
-
字符串类型字段使用时,不加引号,索引将失效
-
-
模糊查询
-
如果仅仅是尾部模糊匹配,索引不会失效。如果是头部模糊匹配,索引失效
-
-
or连接的条件
-
用or分割开的条件,如果or前的条件中的列有索引,而后面的列没有索引,那么涉及的索引都不会被用到
-
-
数据发布影响
-
如果MySQL评估使用索引比全表更慢,则不使用索引
-
-
SQL提示
-
SQL提示,是优化数据库的一个重要手段,在SQL语句中加入一些人为的提示来达到优化操作的目的
-
use index
-
explain select & from tb_user use index(idx_user_pro) where profession='软件工程';
-
只是建议,MySQL还会评估后选择是否使用
-
-
-
ignore index
-
explain select & from tb_user ignore index(idx_user_pro) where profession='软件工程';
-
-
force index
-
explain select & from tb_user force index(idx_user_pro) where profession='软件工程';
-
-
-
覆盖索引
-
查询使用了索引,并且需要返回的列,在该索引中已经全部能够找到,不用回表查询,减少select
-
using index condition:查找使用了索引,但是需要回表查询数据
-
using where;using index:查询使用了索引,但是需要的数据都在索引列中能找到,所以不需要回表查询数据
-
-
前缀索引
-
当字符类型为字符串时,有时候需要索引很长的字符串,会使索引变得很大,查询时,浪费大量的磁盘IO,影响查询效率。此时可以只将字符串的一部分前缀,建立索引,这样可以大大节约索引空间,从而提高索引效率
-
语法:
-
create index idx_xxxx on table_name(column(n));
-
-
前缀长度
-
可以根据索引的选择性来决定,而选择性是指不重复的索引值(基数)和数据表的记录总数的比值,索引选择性越高则查询效率越高,唯一索引的选择性是1,索引选择性最好,性能最好
-
select count(distinct email)/count(*) from tb_user;
-
select count(distinct substring(email,1,5))/count(*) from tb_user;
-
-
-
-
索引设计原则(来源:mysql建立索引的几大原则(仅学习) - 知乎)原文更加详细
-
1.选择唯一性索引
-
唯一性索引的值是唯一的,可以更快速的通过该索引来确定某条记录。例如,学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话,可能存在同名现象,从而降低查询速度。
-
2.为经常需要排序、分组和联合操作的字段建立索引
-
经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段,排序操作会浪费很多时间。如果为其建立索引,可以有效地避免排序操作。
-
3.为常作为查询条件的字段建立索引
-
如果某个字段经常用来做查询条件,那么该字段的查询速度会影响整个表的查询速度。因此,为这样的字段建立索引,可以提高整个表的查询速度。
-
4.限制索引的数目
-
索引的数目不是越多越好。每个索引都需要占用磁盘空间,索引越多,需要的磁盘空间就越大。修改表时,对索引的重构和更新很麻烦。越多的索引,会使更新表变得很浪费时间。
-
5.尽量使用数据量少的索引
-
如果索引的值很长,那么查询的速度会受到影响。例如,对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。
-
6.尽量使用前缀来索引
-
如果索引字段的值很长,最好使用值的前缀来索引。例如,TEXT和BLOG类型的字段,进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符,这样可以提高检索速度。
-
7.删除不再使用或者很少使用的索引
-
表中的数据被大量更新,或者数据的使用方式被改变后,原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引,将它们删除,从而减少索引对更新操作的影响。
-
8 . 最左前缀匹配原则,非常重要的原则。
-
mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a 1=”” and=”” b=”2” c=”“> 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。
-
9 .=和in可以乱序。
-
比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式
-
10 . 尽量选择区分度高的列作为索引。
-
区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就 是0,那可能有人会问,这个比例有什么经验值吗?使用场景不同,这个值也很难确定,一般需要join的字段我们都要求是0.1以上,即平均1条扫描10条 记录
-
11 .索引列不能参与计算,保持列“干净”。
-
比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本 太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
-
12 .尽量的扩展索引,不要新建索引。
-
比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可
-
13、当单个索引字段查询数据很多,区分度都不是很大时,则需要考虑建立联合索引来提高查询效率
-
注意:选择索引的最终目的是为了使查询的速度变快。上面给出的原则是最基本的准则,但不能拘泥于上面的准则。读者要在以后的学习和工作中进行不断的实践。根据应用的实际情况进行分析和判断,选择最合适的索引方式。
-