程序地带

MySQL的索引优化分析(一)


一、SQL分析
性能下降、SQL慢、执行时间长、等待时间长
查询语句写的差
索引失效关联查询太多join(设计缺陷)
单值索引:在user表中给name属性创建索引,create index idx_name on user(name);
复合索引:在user表中给name、email属性创建索引,create index idx_name_email on user(name,email);
服务器调优及各个参数设置(缓冲、线程数等)
二,join查询
1,SQL执行顺序
  a)手写SQL

    


  b)机读

   


  c)总结

  


2,JOIN查询

  


a)建表语句

CREATE TABLE tbl_dept(
id INT(11) NOT NULL AUTO_INCREMENT,
deptName VARCHAR(30) DEFAULT NULL,
locAdd VARCHAR(40) DEFAULT NULL,
PRIMARY KEY(id)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE tbl_emp (
id INT(11) NOT NULL AUTO_INCREMENT,
NAME VARCHAR(20) DEFAULT NULL,
deptId INT(11) DEFAULT NULL,
PRIMARY KEY (id),
KEY fk_dept_Id (deptId)
#CONSTRAINT "fk_dept_Id" foreign key ("deptId") references "tbl_dept"("Id")
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO tbl_dept(deptName,locAdd) VALUES("RD",11);
INSERT INTO tbl_dept(deptName,locAdd) VALUES("HR",12);
INSERT INTO tbl_dept(deptName,locAdd) VALUES("MK",13);
INSERT INTO tbl_dept(deptName,locAdd) VALUES("MIS",14);
INSERT INTO tbl_dept(deptName,locAdd) VALUES("FD",15);
INSERT INTO tbl_emp(NAME,deptId) VALUES("z3",1);
INSERT INTO tbl_emp(NAME,deptId) VALUES("z4",1);
INSERT INTO tbl_emp(NAME,deptId) VALUES("z5",1);
INSERT INTO tbl_emp(NAME,deptId) VALUES("w5",2);
INSERT INTO tbl_emp(NAME,deptId) VALUES("w6",2);
INSERT INTO tbl_emp(NAME,deptId) VALUES("s7",3);
INSERT INTO tbl_emp(NAME,deptId) VALUES("s8",4);
INSERT INTO tbl_emp(NAME,deptId) VALUES("s9",51);
View Code
b)满足条件的join语句
#左连接
SELECT * FROM tbl_dept t1 LEFT JOIN tbl_emp t2 on t1.id = t2.deptId;
#右连接
SELECT * FROM tbl_dept t1 RIGHT JOIN tbl_emp t2 on t1.id = t2.deptId;
#内连接(交集)
SELECT * FROM tbl_dept t1 INNER JOIN tbl_emp t2 on t1.id = t2.deptId;
#左连接(只取A独有的)
SELECT * FROM tbl_dept t1 LEFT JOIN tbl_emp t2 on t1.id = t2.deptId WHERE t2.deptId is NULL;
#右连接(只取B独有的)
SELECT * FROM tbl_dept t1 RIGHT JOIN tbl_emp t2 on t1.id = t2.deptId WHERE t1.id is NULL;
#A和B均有
SELECT * FROM tbl_dept t1 LEFT JOIN tbl_emp t2 on t1.id = t2.deptId
UNION
SELECT * FROM tbl_dept t1 RIGHT JOIN tbl_emp t2 on t1.id = t2.deptId;
#A和B均是独有
SELECT * FROM tbl_dept t1 LEFT JOIN tbl_emp t2 on t1.id = t2.deptId WHERE t2.deptId is NULL
UNION
SELECT * FROM tbl_dept t1 RIGHT JOIN tbl_emp t2 on t1.id = t2.deptId WHERE t1.id is NULL;
三、索引
1,索引是什么
索引(Index)是帮助MySQL高效获取数据的数据结构。
排好序的快速查找数据结构,即索引 = 排序 + 查找
一般来说索引本身占用内存空间也很大,不可能全部存储在内存中,因此索引往往以文件形式存储在硬盘上
聚集索引,次要索引,覆盖索引,复合索引,前缀索引,唯一索引默认都是使用B+树索引,统称索引。当然,除了B+树这种类型的索引之外,还有哈希索引(hash index)等。
2,索引原理


3,索引优劣势
a)索引的优势
索引能提高数据检索的效率,降低数据库的IO成本
通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性
在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间
加速两个表之间的连接,一般在外键上创建索引
b)劣势
实际上索引也是一张表,该表保存了主键和索引字段,并指向实体表的记录,所以索引列也是要占用空间的
虽然索引大大提高了查询速度,同时却也会降低更新表的速度,如对表insert、update和delete。因为更新表时,不仅要保存数据,还要保存一下索引文件。
索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立优秀的索引,或优化查询语句
4,MySQL索引分类
普通索引:是最基本的索引,它没有任何限制,即一个索引只包含单个列,一个表可以有多个单列索引;建议一张表索引不要超过5个,优先考虑复合索引
唯一索引:与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一
主键索引:是一种特殊的唯一索引,一个表只能有一个主键,不允许有空值。一般是在建表的时候同时创建主键索引
复合索引:指多个字段上创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用。使用组合索引时遵循最左前缀集合
全文索引:主要用来查找文本中的关键字,而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同,它更像是一个搜索引擎,而不是简单的where语句的参数匹配
5,MySQL索引语法
创建索引
CREATE [UNIQUE] INDEX index_name ON mytable(column_name(length));
删除索引
DROP INDEX index_name ON mytable;
查看索引(G表示将查询到的横向表格纵向输出,方便阅读)
SHOW INDEX FROM table_nameG
采用alter方式添加索引
#该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL。
ALTER TABLE tbl_name ADD PRIMARY KEY(column_list)
#这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)
ALTER TABLE tbl_name ADD UNIQUE index_name(column_list)
#添加普通索引,索引值可出现多次。
ALTER TABLE tbl_name ADD INDEX index_name(column_list)
#该语句指定了索引为FULLTEXT,用于全文索引。
ALTER TABLE tbl_name ADD FULLTEXT index_name(column_list)
6,MySQL的索引结构

  详情可查看:多路查找树(B-Tree,B+Tree,B*Tree)


a)B-Tree与B+Tree的区别

  B-树的关键字(数据项)和记录是放在一起的; B+树的非叶子节点中只有关键字和指向下一个节点的索引, 记录只放在叶子节点中。


b)B+Tree 与 BTree 的查找过程
在 B 树中, 越靠近根节点的记录查找时间越快, 只要找到关键字即可确定记录的存在; 而 B+ 树中每个记录的查找时间基本是一样的, 都需要从根节点走到叶子节点, 而且在叶子节点中还要再比较关键字。
从这个角度看 B 树的性能好像要比 B+ 树好, 而在实际应用中却是 B+ 树的性能要好些。 因为 B+ 树的非叶子节点不存放实际的数据,这样每个节点可容纳的元素个数比 B 树多, 树高比 B 树小, 这样带来的好处是减少磁盘访问次数。
尽管 B+ 树找到一个记录所需的比较次数要比 B 树多, 但是一次磁盘访问的时间相当于成百上千次内存比较的时间, 因此实际中B+ 树的性能可能还会好些, 而且 B+树的叶子节点使用指针连接在一起, 方便顺序遍历(范围搜索), 这也是很多数据库和文件系统使用 B+树的缘故。
7,创建索引的场景
  a)何时需要创建索引
主键自动建立唯一索引
频繁作为查询的条件的字段应该创建索引
查询中与其他表关联的字段,外键关系建立索引
频繁更新的字段不适合创建索引
Where 条件里用不到的字段不创建索引
单间/组合索引的选择问题(在高并发下倾向创建组合索引)
查询中排序的字段,排序字段若通过索引去访问将大大提高排序的速度
查询中统计或者分组字段
  b)何时不创建索引
表记录太少
经常增删改的表
数据重复且分布平均的表字段。如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果。
四、Explain
1,作用
表的读取顺序(id 字段,越大优先级越高)
数据读取操作的操作类型(select_type 字段)
哪些索引可以使用(possible_keys 字段)
哪些索引被实际使用(keys 字段)
表之间的引用(ref 字段)
每张表有多少行被优化器查询(rows 字段)
2,id
id相同,执行顺序由上至下
id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行
id相同不同,同时存在:id如果相同,可以认为是一组,从上往下顺序执行;在所有组中,id值越大,优先级越高,越先执行;衍生=DERIVED

       


2,select_type

  select_type:查询的类型,主要用于区别普通查询、联合查询、子查询等复杂查询


SIMPLE:简单的select查询,查询中不包含子查询或者UNION
PRIMARY:查询中若包含任何复杂的子部分,最外层查询则被标记为PRIMARY
SUBQUERY:在SELECT或者WHERE列表中包含了子查询
DERIVED:在FROM列表中包含的子查询被标记为DERIVED(衍生)MySQL会递归执行这些子查询,把结果放在临时表里
UNION:若第二个SELECT出现在UNION之后,则被标记为UNION;若UNION包含在FROM子句的子查询中,外层SELECT将被标记为:DERIVED
UNION RESULT:从UNION表获取结果的SELECT
3,table

  table:显示这一行的数据是关于哪张表的


4,type

  type:访问类型排列,显示查询使用了何种类型,其中从好到坏依次是:system>const>eq_ref>ref>fultext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL。其中,相比比较重要的指标为:system > const > eq_ref > ref > range > index > ALL


system:表只有一行记录(等于系统表),这是const类型的特例,平时不会出现,这个也可以忽略不计
const:表示通过索引一次就能找到了,const用于比较primary key或者unique索引。因为只匹配一行数据,所以很快。如将主键置于where列表中,MySQL就能将该查询转换为一个常量  

  


eq_ref:唯一性索引,对于每个索引建,表中只有一条记录与之匹配,常见于主键或唯一索引扫描

  


ref:非唯一索引扫描,返回匹配某个单独值的所有行。本质上也是一种索引访问,它返回所有匹配某个单独值的行。不过,它可能找到多个符合条件的行,所以它应该属于查找和扫描的混合体

  


range:只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引,一般就是你的where语句中出现了between、<、>、in等的查询,这种范围扫描索引比全表扫描要好,因为他只需要开始索引的某一个点,而结束于另一点,不用扫描全部索引

  


index:Full Index Scan,index与ALL区别为index类型只遍历索引树。这通常比ALL快,因为索引文件通常比数据文件小。(也就是说虽然all和index都是读全表,但index是从索引中读取的,而all是从硬盘数据库文件中读的)

  


ALL:Full Table Scan,将遍历全表以找到匹配的行(全表扫描)

  


5,possible_keys
显示可能应用在这张表上的索引,一个或多个
若查询涉及的字段上存在索引,则该索引将被列出,但不一定被查询实际使用
6,key
实际使用的索引,如果为null,则没有使用索引
若查询中使用了覆盖索引,则该索引仅出现在key列表中

  


7,key_len
表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下,长度越短越好
key_len显示的值为索引最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的

   


8,ref
显示索引哪一列被使用了,如果可能的话,最好是一个常数。哪些列或常量被用于查找索引列上的值
由key_len可知t1表的的索引idx_col1_col2被充分使用,t1表的col1匹配t2表的col1,t1表的col2匹配一个常量("ac")

  


9,rows

  根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数


  


10,Extra
a)Using filesort(文件排序)
在使用order by关键字的时候,如果待排序的内容不能由所使用的索引直接完成排序的话,那么mysql有可能就要进行文件排序
filesort是通过相应的排序算法,将取得的数据在内存中进行排序
MySQL需要将数据在内存中进行排序,所使用的内存区域也就是我们通过sort_buffer_size 系统变量所设置的排序区

  


b)Using temporary(创建临时表)
using temporary一般出现在多张表的数据需要排序的情况下. MySQl会先使用using temporary保存临时数据, 然后再在临时表上使用filesort进行排序
如果有ORDER BY子句和一个不同的GROUP BY子句, 或者如果ORDER BY或GROUP BY中的字段都来自其他的表而非连接顺序中的第一个表的话, 就会创建一个临时表了。
MySQL首先创建heap引擎的临时表, 如果临时的数据过多, 超过max_heap_table_size配置的大小, 会自动把临时表转换成MyISAM引擎的表来使用

  


c)Using indexing(覆盖索引)

表示相应的select操作中使用了覆盖索引(Coveing Index),避免访问了表的数据行。


如果同时出现using where,表明索引被用来执行索引键值的查找


如果没有同时出现using where,表明索引用来读取数据而非执行查找动作  


  


覆盖索引:  理解方式一:就是select的数据列只用从索引中就能够取得,不必读取数据行,MySQL可以利用索引返回select列表中的字段,而不必根据索引再次读取数据文件,换句话说查询列要被所建的索引覆盖。
  理解方式二:索引是高效找到行的一个方法,但是一般数据库也能使用索引找到一个列的数据,因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据;当能通过读取索引就可以得到想要的数据,那就不需要读取行了。一个索引包含了(或覆盖了)满足查询结果的数据就叫做覆盖索引。
  注意:如果要使用覆盖索引,一定要注意select列表中只取出需要的列,不可select * ,因为如果将所有字段一起做索引会导致索引文件过大,查询性能下降。
d)Using where

  表示使用了where过滤


e)Using join buffer

  表示使用了连接缓存


f)impossible where

  where子句的值总是false,不能用来获取任何元组


  


g)select tables optimized away

  在没有GROUP BY子句的情况下,基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作,不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成优化。


  


h)distinct

  优化distinct,在找到第一匹配的元组后即停止找同样值的工作


 


版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://www.cnblogs.com/bbgs-xc/p/14276005.html

随机推荐

京东淘宝day03

京东淘宝day03

1.SpringBoot整合web资源1.1创建动态web资源1.2项目结构1.3添加资源/jar包1).添加资源2).添加jar包文件<!--springBoot整合JSP添加依赖-->...

Riqk_Qin 阅读(114)

LONZA细胞核转染试剂盒使用说明及常见问题

LONZA细胞核转染试剂盒使用说明及常见问题

LONZA4D-Nucleofector细胞核转染系统,结合电穿孔技术和细胞特异性转染液,通过系统内置优化的转染程序,将外源基因高效导入目标细胞的细胞质中&#...

磕盐gilr三三 阅读(586)

京淘4-3

京淘4-3

1.SpringBoot整合web资源1.1创建动态web资源1.2项目结构1.3添加资源/jar包1).添加资源2).添加jar包文件<!--springBoot整合JSP添加依赖-->...

parker(No.1) 阅读(154)

什么是vuex

什么是vuex

什么是vuex​VueX从Java角度上说有点像单例模式全局唯一,但又有不同支持。总之就是把一个数据作为一个单独的区域,其他模块都可以使用它。Vuex使用安装npmivue...

踏行JAVA 阅读(752)

浮动 层叠 定位 H5新标签

浮动float:浮动方向1、任何元素都可以浮动。浮动元素会生成一个块级框,而不论它本身是何种元素。2、假如在一行之上只有极少的空间可供浮动元素,那么这个元素会...

adsdsasada 阅读(140)

CSP-J2020第四题(方格取数)题解

题目题目传送门解题思路从上下走不方便看,先翻转一下,就变成了可以向下向左向右走。设f[i][j][0]表示现在在(i,j),向左。f[i][j][1]表示向右...

蒟蒻一枚 阅读(712)

给转行IT行业的文科生的4点学习建议

2020年的毕业季说来就来,突发的疫情将可能让2020年的就业形势变得雪上加霜。居危思变,在大环境不好的背景下,很多毕业生都准备暂时先不考虑就业,...

重庆千锋 阅读(905)