索引优化分析上 -- 索引和性能分析【MySQL高级篇2】
2023-09-27 14:25:57 时间
1、索引1.1、什么是索引?MySQL官方对索引的定义为:索引是帮助MySQL高效获取数据的数据结构。可以得到索引的本质:索引是数据结构。1.2、索引的目的索引的目的在于提高查询效率,可以类比字典。
举例说明
如果要查询“mysql”这个单词,我们肯定需要定位到m字母,然后从上往下找到y字母,再找到剩下的sql。如果没有索引,那么你可能需要从a一直查找到z。是不是觉得如果没有索引,这个事情就完成的很慢很慢呀。
1.3、索引是什么样的数据结构?你可以简单理解为“排好序的快速查找数据结构”。在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引。
如下图所示
1. 索引分类
索引名定义单值索引即一个索引只包含单个列,一个表中可以有多个单列索引唯一索引索引列的值必须唯一,但允许有空值复合索引即一个索引包含多个列2. 建索引语法
创建
CREATE [UNIQUE] INDEX indexName ON mytable (columnname(length)); ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnname(length));
删除
DROP INDEX [indexName] ON mytable
查看
SHOW INDEX FROM table_name
使用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)1.7、MySQL索引结构
1. 结构分类
BTree索引Hash索引full-text索引R-Tree索引2. BTree索引检索原理
初始化介绍
一棵B+树,浅蓝色的块我们称之为一个磁盘块,可以看到每个磁盘块包含几个数据项(深蓝色所示)和指针(黄色所示)如磁盘块1包含数据项17和35,包含指针P1、P2、P3。P1表示小于17的磁盘块,P2表示在17和25之间的磁盘块,P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据,只存储指引搜索方向的数据项,如17、35并不真实存在于数据表中。查找过程
如果要查找数据项29,那么首先会把磁盘块1由磁盘块加载到内存,此时发生一次IO。在内存中使用二分查找确定29在17和35之间,锁定磁盘块1的P2指针,内存时间因为非常短(相比于磁盘的IO)可以忽略不计,通过磁盘块1的P2指针的磁盘块地址把磁盘块3由磁盘加载到内存,发生第二次IO。29在26和30之间,锁定磁盘块3的P2指针,通过指针加载磁盘块8到内存,发生第三次IO。同时内存中做二分查找找到29,结束查询,总计三次IO。1.8、哪些情况需要建索引?主键自动建立唯一索引频繁作为查询条件的字段应该创建索引查询中与其它表关联的字段,外键关系建立索引。频繁更新的字段不适合创建索引(因为每次更新不单单是更新了记录还会更新索引,加重了IO负担)WHERE条件里用不到的字段不创建索引。单键/组合索引的选择问题(高并发下倾向创建组合索引)查询中排序的字段,排序字段如果通过索引去访问将大大提高排序速度。查询中统计或者分组字段。1.9、哪些情况不需要建索引?表记录太少。经常增删改的表:提高了 查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。数据重复且分布平均的表字段,因此应该只为最经常查询和最经常排序的数据建立索引。注意:如果某个数据列包含许多重复的内容,为它建立索引没有太大的实际效果。假如一个表有10万条行记录,有一个字段A只有T和F两种值,且每个值的分布概率大约为50%,那么对这种表A字段建索引一般不会提高数据库的查询速度。索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录,表索引列有1980个不同的值,那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近于1,这个索引的效率就越高。2、性能分析2.1、性能分析前置知识1. MySQL Query Optimizer
MySQL中有专门负责优化的SELECT语句的优化器模块,主要功能:通过计算分析系统中收集到的统计信息,为客户端请求的Query提供他认为最优的执行计划(他认为最优的数据检索方式,但不见得是DBA认为是最优的,这部分最耗费时间)。当客户端向MySQL请求一条Query,命令解析器模块完成请求分类,区别出是SELECT并转发给MySQL Query Optimizer时,MySQL Query Optimizer首先会对整条Query进行优化,处理掉一些常量表达式的预算,直接换算成常量值。并对Query中的查询条件进行简化和转换,如去掉一些无用或显而易见的条件、结构调整等。然后分析Query中的Hint信息(如果有),看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint或Hint信息还不足以完全确定执行计划,则会读取所涉及对象的统计信息,根据Query进行写相应的计算分析,然后再得出最后的执行计划。2. MySQL常见瓶颈
CPU:CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据。IO:磁盘IO瓶颈发生在装入数据远大于内存容量的时候服务器硬件的性能瓶颈:top,free,iostat和vmstat来查看系统的性能状态2.2、explain简介2.2.1、explain是什么(即执行计划)?使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。
2.2.2、explain能干嘛?表的读取顺序数据读取操作的操作类型哪些索引可以使用哪些索引被实际使用表之间的引用每张表有多少行被优化器执行2.2.3、explain怎么玩?explain + SQL语句EXPLAIN SELECT * FROM tbl_emp
执行计划包含的信息
三种情况
id相同,执行顺序由上至下。1. 常见的分类
SIMPLE:简单的select查询,查询中不包含子查询或者UNION。PRIMARY:查询中如果包含任何复杂的子部分,最外层查询则被标记为SUBQUERY:在SELECT或WHERE列表中包含了子查询。DERIVED:在FROM列表中包含的子查询被标记为DERIVED(衍生),MySQL会递归执行这些子查询,把结果放在临时表里。UNION:如果第二个SELECT出现在UNION之后,则被标记为UNION;如果UNION包含在FROM子句的子查询中,外层SELECT将被标记为DERIVED。2. 作用
主要用于区别普通查询、联合查询、子查询等的复杂查询。3. table介绍
table:显示这一行的数据是关于那张表的。2.5、explain之type介绍type : 显示查询使用了何种类型。一般来说,得保证查询至少达到range级别,最好达到ref。从最好到最差依次是: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,将遍历全表以找到匹配的行。2.6、explain之possible_keys和key介绍possible_keys:显示可能应用在这张表中得索引,一个或多个。查询涉及到得字段上如果存在索引,则该索引被列出,但不一定被查询实际使用。就是理论上会使用到得索引。key:实际使用的索引。如果为NULL,则没有使用索引。查询中如果使用了覆盖索引,则该索引仅出现在key列表中。2.7、explain之key_len介绍表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下,长度越短越好。key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的。如果查询出来的结果相同,那么key_len越小越好。2.8、explain之ref介绍ref:显示索引的哪一列被使用了,如果可能的话,是一个常数。哪些列或常量被用于查找索引列上的值。没建索引之前
建了索引之后
常见的有
Using filesort:说明MySQL会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。相关文章
- 【MySQL】MySQL的存储引擎和索引详解(聚集索引和非聚集索引)
- 精品spring boot+MySQL学生在线考试系统vue
- mysql元数据以及一些常用命令
- 面试官问我MySQL索引,我
- mysql笔记系列(七)唯一索引和普通索引的性能区别
- Mysql:Changes in MySQL 8.0.13 (2018-10-22, General Availability):utf8mb3 ->utf8mb4
- 【数据库技术】MySQL索引背后的数据结构及算法原理
- logstash-input-jdbc实现mysql 与elasticsearch实时同步(ES与关系型数据库同步)
- mysql性能测试(索引)
- windows7 64位安装mysql 5.7.11 zip压缩版
- 亲身体验MySQL的索引对搜索性能的提升
- mysql 修改root密码
- Mysql性能优化:什么是索引下推?
- 如何查看sql查询是否用到索引(mysql)
- 2022-11-30 mysql-innodb-索引-分析
- 2022-09-08 mysql/stonedb-慢SQL-记录
- MySQL:互联网公司常用分库分表方案汇总
- MySQL的binlog有啥用?谁写的?在哪里?怎么配置
- mysql -- 索引
- Egg 项目怎么连接 MySQL 实现增删改查接口?
- 说一个在工作中遇到的mysql索引失效的问题
- mysql中的视图、事务和索引
- MYSQL因IN的范围太大导致索引失效问题