微网站微名片,网站制度建设,网站推广上首页,frontpage制作个人网站 技巧MySQL 之INDEX 索引
1.4 INDEX 索引
1.4.1 索引介绍
索引#xff1a;是排序的快速查找的特殊数据结构#xff0c;定义作为查找条件的字段上#xff0c;又称为键 key#xff0c;索引通过存储引擎实现。
优点 大大加快数据的检索速度; 创建唯一性索引#xff0c;保证数…MySQL 之INDEX 索引
1.4 INDEX 索引
1.4.1 索引介绍
索引是排序的快速查找的特殊数据结构定义作为查找条件的字段上又称为键 key索引通过存储引擎实现。
优点 大大加快数据的检索速度; 创建唯一性索引保证数据库表中每一行数据的唯一性; 加速表和表之间的连接; 在使用分组和排序子句进行数据检索时可以显著减少查询中分组和排序的时间。 缺点 索引需要占物理空间。 当对表中的数据进行增加、删除和修改的时候索引也要动态的维护降低了数据的维护速度。 索引类型 B TREE、HASH、R TREE、FULL TEXT 聚簇集索引、非聚簇索引数据和索引是否存储在一起 主键索引、二级辅助索引 稠密索引、稀疏索引是否索引了每一个数据项 简单索引、组合索引: 是否是多个字段的索引 左前缀索引取前面的字符做索引 覆盖索引从索引中即可取出要查询的数据性能高
1.4.2 索引结构
参考链接https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
树的简介
树跟数组、链表、堆栈一样是一种数据结构。它由有限个节点组成具有层次关系的集合。因为它看起来像一棵树所以得其名。
树是包含n(n为整数大于0)个结点 n-1条边的有穷集它有以下特点 每个结点或者无子结点或者只有有限个子结点 有一个特殊的结点它没有父结点称为根结点 每一个非根节点有且只有一个父节点 树里面没有环路 概念相关 结点的度一个结点含有的子结点个数称为该结点的度 树的度一棵树中最大结点的度称为树的度 父结点若一个结点含有子结点则这个结点称为其子结点的父结点 深度对于任意结点NN的深度为从根到n的唯一路径长根结点的深度为0 高度对于任意结点NN的高度为从n到一片树叶的最长路径长所有树叶的高度为0 树的分类
按照有序性可以分为有序树和无序树 无序树树中任意节点的子结点之间没有顺序关系 有序树树中任意节点的子结点之间有顺序关系 按照节点包含子树个数可以分为B树和二叉树二叉树可以分为以下几种 二叉树每个节点最多含有两个子树的树称为二叉树 二叉查找树首先它是一颗二叉树若左子树不空则左子树上所有结点的值均小于它的根结点的值若右子树不空则右子树上所有结点的值均大于它的根结点的值左、右子树也分别为二叉排序树 满二叉树叶节点除外的所有节点均含有两个子树的树被称为满二叉树 完全二叉树如果一颗二叉树除去最后一层节点为满二叉树且最后一层的结点依次从左到右分布 霍夫曼树带权路径最短的二叉树 红黑树红黑树是一种特殊的二叉查找树每个节点都是黑色或者红色根节点、叶子节点是黑色。如果一个节点是红色的则它的子节点必须是黑色的 平衡二叉树(AVL)一 棵空树或它的左右两个子树的高度差的绝对值不超过1并且左右两个子树都是一棵平衡二叉树 二叉树
二叉树binary tree是指树中节点的度不大于2的有序树它是一种最简单且最重要的树。二叉树的递归定义为二叉树是一棵空树或者是一棵由一个根节点和两棵互不相交的分别称作根的左子树和右子树组成的非空树左子树和右子树又同样都是二叉树。
参考链接https://www.cs.usfca.edu/~galles/visualization/BST.html
红黑树
红黑树Red Black Tree 是一种自平衡二叉查找树是在进行插入和删除操作时通过特定操作保持二叉查找树的平衡从而获得较高的查找性能。
红黑树特点 根节点是黑色的叶节点是不存储数据的黑色空节点 任何相邻的两个节点不能同时为红色红色节点被黑色节点隔开红色节点的子节点是黑色的 任意节点到其可到达的叶节点间包含相同数量的黑色节点保证任何路径相差不会超出2倍从而实现基本平衡
参考链接https://www.cs.usfca.edu/~galles/visualization/RedBlack.html B-树
B-树读作B树中间的横线是连字符不是减号B树将一个节点的大小设置为每个数据页(Page也可以称为块block)的大小一般是16KB并且B树中是将数据和索引放在一起的以减少IO次数加快查询速度一个节点能放多少数据通常取决于一条数据占用的空间大小。
mysql show variables like %page_size%;-------------------------| Variable_name | Value |-------------------------| innodb_page_size | 16384 || large_page_size | 0 |-------------------------2 rows in set (0.00 sec)
B树索引
B树是B-树的变体也是一棵多路搜索树MySQL普遍使用B树来实现索引。
https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html
B树和B-树的主要区别 B-树内部节点是保存数据的而B树内部节点是不保存数据的只作索引作用它的叶子节点才保存数据。 B树相邻的叶子节点之间是通过链表指针连起来的B-树却不是。 查找过程中B-树在找到具体的数值以后就结束而B树则需要通过索引找到叶子结点中的数据才结束 B-树中任何一个关键字出现且只出现在一个结点中而B树可以出现多次。 可以使用BTree索引的查询类型假设前提姓名年龄三个字段建立了一个复合索引 全值匹配精确所有索引列如姓zhang名ming年龄20 匹配最左前缀即只使用索引的第一列如姓zhang 匹配列前缀只匹配一列值开头部分如姓以w开头的记录 匹配范围值如姓wang和姓zhang之间 精确匹配某一列并范围匹配另一列如姓wang名以x开头的记录 只访问索引的查询 BTree索引的限制 如不从最左列开始则无法使用索引如查找名为xiaoming或姓为g结尾 不能跳过索引中的列如查找姓为wang年龄30的只能使用索引第一列 特别提示 索引列的顺序和查询语句的写法应相匹配才能更好的利用索引 为优化性能可能需要针对相同的列但顺序不同创建不同的索引来满足不同类型的查询需求 Hash索引 Hash索引基于哈希表实现只有精确匹配索引中的所有列的查询才有效索引自身只存储索引列对应的哈希值和数据指针索引结构紧凑查询性能好。 Memory 存储引擎支持显式 hash 索引InnoDB 和 MyISAM 存储引擎不支持。 适用场景只支持等值比较查询包括IN()。 不适合使用hash索引的场景 不适用于顺序查询索引存储顺序的不是值的顺序 不支持模糊匹配 不支持范围查询 不支持部分索引列匹配查找如AB列索引只查询A列索引无效 地理空间数据索引 R-Tree Geospatial indexing
MyISAM 支持地理空间索引可使用任意维度组合查询使用特有的函数访问常用于做地理数据存储使用不多。
InnoDB从MySQL5.7之后也开始支持
全文索引FULLTEXT
在文本中查找关键词而不是直接比较索引中的值类似搜索引擎
InnoDB 从 MySQL 5.6 之后也开始支持
聚簇和非聚簇索引主键和二级索引
在 MySQL 的 InnoDB 引擎中每个索引都会对应一棵 B 树而聚簇索引和非聚簇索引最大的区别在于叶子节点存储的数据不同聚簇索引叶子节点存储的是行数据因此通过聚簇索引可以直接找到真正的行数据而非聚簇索引叶子节点存储的是主键信息所以使用非聚簇索引还需要回表查询因此我们可以得出聚簇索引和非聚簇索引的区别主要有以下几个 聚簇索引叶子节点存储的是行数据而非聚簇索引叶子节点存储的是聚簇索引通常是主键 ID。 聚簇索引查询效率更高而非聚簇索引需要进行回表查询因此性能不如聚簇索引。 聚簇索引一般为主键索引而主键一个表中只能有一个因此聚簇索引一个表中也只能有一个而非聚簇索引则没有数量上的限制。 冗余和重复索引 冗余索引AAB注意如果同时存在仍可能会使用(A)索引 重复索引已经有索引再次建立索引 1.4.3 索引优化
参考资料阿里的《Java开发手册》
https://developer.aliyun.com/topic/java2020 独立地使用列尽量避免其参与运算独立的列指索引列不能是表达式的一部分也不能是函数的 参数在where条件中始终将索引列单独放在比较符号的一侧尽量不要在列上进行运算函数操作和表达式操作 左前缀索引构建指定索引字段的左侧的字符数要通过索引选择性不重复的索引值和数据表的记录总数的比值来评估尽量使用短索引如果可以应该制定一个前缀长度 多列索引AND操作时更适合使用多列索引而非为每个列创建单独的索引 选择合适的索引列顺序无排序和分组时将选择性最高放左侧 只要列中含有NULL值就最好不要在此列设置索引复合索引如果有NULL值此列在使用时也不会使用索引 对于经常在where子句使用的列最好设置索引 对于有多个列where或者order by子句应该建立复合索引 对于like语句以 % 或者 _ 开头的不会使用索引以 % 结尾会使用索引 尽量不要使用not in和操作虽然可能使用索引但性能不高 不要使用RLIKE正则表达式会导致索引失效 查询时能不要就不用尽量写全字段名比如select id,name,age from students; 大部分情况连接效率远大于子查询 在有大量记录的表分页时使用limit 对于经常使用的查询可以开启查询缓存 多使用explain和profile分析查询语句 查看慢查询日志找出执行时间长的sql语句优化 1.4.4 管理索引
查看帮助
mysql help index
查看索引
SHOW INDEX FROM [db_name.]tbl_name;
mysql desc student;--------------------------------------------------------------| Field | Type | Null | Key | Default | Extra |--------------------------------------------------------------| id | int unsigned | NO | PRI | NULL | auto_increment || name | varchar(20) | NO | | NULL | || age | tinyint unsigned | YES | | NULL | || gender | enum(M,F) | YES | | M | |--------------------------------------------------------------4 rows in set (0.00 sec)
#默认会在主键上创建索引mysql show index from student\G*************************** 1. row ***************************Table: studentNon_unique: 0Key_name: PRIMARYSeq_in_index: 1Column_name: idCollation: ACardinality: 6Sub_part: NULLPacked: NULLNull:Index_type: BTREE #B树索引Comment:Index_comment:Visible: YESExpression: NULL1 row in set (0.01 sec)
查看语句是否利用索引
mysql explain select * from student where id12\G;*************************** 1. row ***************************id: 1select_type: SIMPLEtable: studentpartitions: NULLtype: const #只读取一次possible_keys: PRIMARYkey: PRIMARY #使用了主键索引key_len: 4ref: constrows: 1 #扫描了1条数据filtered: 100.00Extra: NULL1 row in set, 1 warning (0.00 sec)
mysql explain select * from student where namewangwu\G;*************************** 1. row ***************************id: 1select_type: SIMPLEtable: studentpartitions: NULLtype: ALL #全表扫描possible_keys: NULL #没有使用索引字段key: NULL #没有使用索引字段key_len: NULLref: NULLrows: 6 #扫描了6条数据整个表就是6条数据filtered: 16.67Extra: Using where #在存储引擎检索行后再进行过滤1 row in set, 1 warning (0.00 sec)
创建索引
CREATE [UNIQUE] INDEX index_name ON tbl_name (index_col_name[(length)],...);ALTER TABLE tbl_name ADD INDEX index_name(index_col_name[(length)]);
mysql create index idx_name on student(name);Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0#create index idx_name on student(name(10)); 表示取 name 字段中的前 10 个字符做索引
再次查看
mysql show index from student\G*************************** 1. row ***************************Table: studentNon_unique: 0Key_name: PRIMARYSeq_in_index: 1Column_name: idCollation: ACardinality: 6Sub_part: NULLPacked: NULLNull:Index_type: BTREEComment:Index_comment:Visible: YESExpression: NULL*************************** 2. row ***************************Table: studentNon_unique: 1Key_name: idx_nameSeq_in_index: 1Column_name: nameCollation: ACardinality: 6Sub_part: NULLPacked: NULLNull:Index_type: BTREEComment:Index_comment:Visible: YESExpression: NULL2 rows in set (0.00 sec)
再次测试
mysql explain select * from student where namewangwu\G;*************************** 1. row ***************************id: 1select_type: SIMPLEtable: studentpartitions: NULLtype: refpossible_keys: idx_name #使用了索引key: idx_name #使用了索引key_len: 62ref: constrows: 1 #只扫描1条数据filtered: 100.00Extra: NULL1 row in set, 1 warning (0.00 sec)
不是所有查询都能用到索引B树索引是左前缀特性即左匹配可以使用索引
#like 查询左匹配可以使用索引mysql explain select * from student where name like g%\G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: studentpartitions: NULLtype: rangepossible_keys: idx_name #索引key: idx_name #索引key_len: 62ref: NULLrows: 1filtered: 100.00Extra: Using index condition1 row in set, 1 warning (0.00 sec)
#like 查询右匹配不使用索引mysql explain select * from student where name like %g\G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: studentpartitions: NULLtype: ALL #全表扫描possible_keys: NULL #不走索引key: NULL #不走索引key_len: NULLref: NULLrows: 6filtered: 16.67Extra: Using where1 row in set, 1 warning (0.00 sec)
#like 查询包含匹配不使用索引mysql explain select * from student where name like %g%\G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: studentpartitions: NULLtype: ALLpossible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 6filtered: 16.67Extra: Using where1 row in set, 1 warning (0.00 sec)
mariadb 中关于索引利用的优化
MariaDB [testdb] select count(*) from student;----------| count(*) |----------| 28 |----------1 row in set (0.000 sec)
MariaDB [testdb] select count(*) from student where name like m%;----------| count(*) |----------| 22 |----------1 row in set (0.000 sec)
MariaDB [testdb] select count(*) from student where name like z%;----------| count(*) |----------| 3 |----------1 row in set (0.000 sec)MariaDB [testdb] show index from student\G*************************** 1. row ***************************Table: studentNon_unique: 0Key_name: PRIMARYSeq_in_index: 1Column_name: idCollation: ACardinality: 28Sub_part: NULLPacked: NULLNull:Index_type: BTREEComment:Index_comment:*************************** 2. row ***************************Table: studentNon_unique: 1Key_name: idx_nameSeq_in_index: 1Column_name: nameCollation: ACardinality: 28Sub_part: NULLPacked: NULLNull:Index_type: BTREEComment:Index_comment:2 rows in set (0.001 sec)
MariaDB [testdb] explain select * from student where name like m%\G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: studenttype: ALL #全表扫描possible_keys: idx_name #可能会用到的索引key: NULL #没有使用索引key_len: NULLref: NULLrows: 28Extra: Using where1 row in set (0.000 sec)
MariaDB [testdb] explain select * from student where name like z%\G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: studenttype: range #范围查询possible_keys: idx_name #可能会用到的索引key: idx_name #实际使用了索引key_len: 62ref: NULLrows: 3Extra: Using index condition1 row in set (0.000 sec)全表总共28条记录name 字段中以 m 开头的有22条以 z 开头的有3条所以在此情况下查询以 m 开头内容直接全表扫描反而会更快这是 mariadb 中的优化MySQL8.0 中也有此功能但旧版本中没有此优化
删除索引
DROP INDEX index_name ON tbl_name;ALTER TABLE tbl_name DROP INDEX index_name(index_col_name);
#删除索引mysql drop index idx_name on student;Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0
再次测试
mysql explain select * from student where namewangwu\G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: studentpartitions: NULLtype: ALL #全表扫描possible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 6filtered: 16.67Extra: Using where1 row in set, 1 warning (0.01 sec)
查看索引的使用
SET GLOBAL userstat1;SHOW INDEX_STATISTICS;#仅在mariadb中使用MariaDB [testdb] select userstat;------------| userstat |------------| 0 |------------1 row in set (0.000 sec)
#开启MariaDB [testdb] SET GLOBAL userstat1;Query OK, 0 rows affected (0.000 sec)
#查看MariaDB [testdb] SHOW INDEX_STATISTICS;Empty set (0.001 sec)
#查询MariaDB [testdb] select * from student where id13;----------------------------| id | name | age | gender |----------------------------| 13 | zhangfei | 20 | F |----------------------------1 row in set (0.000 sec)
MariaDB [testdb] select * from student where name like z%;-------------------------------| id | name | age | gender |-------------------------------| 13 | zhangfei | 20 | F || 11 | zhangsan | 12 | M || 16 | zhugeiliang | 39 | M |-------------------------------3 rows in set (0.001 sec)
#再次查看MariaDB [testdb] SHOW INDEX_STATISTICS;-------------------------------------------------| Table_schema | Table_name | Index_name | Rows_read |-------------------------------------------------| testdb | student | idx_name | 3 || testdb | student | PRIMARY | 1 |-------------------------------------------------2 rows in set (0.000 sec)
优化表空间
OPTIMIZE TABLE tb_name;
对 MySQL 进行大量或频繁的写操作(insertdeleteupdate)容易产生碎片这些碎片会影响MySQL 性能。在此情况下我们可以通过 optimize 命令来进行优化。此命令在使用时会锁表需要保证在不对业务产生影响的情况下使用。
这里的碎片指的是经过某些操作导致数据库中的表对应的硬盘上的物理文件中的数据不是紧密排列的。
1.4.5 EXPLAIN 工具
可以通过EXPLAIN来分析索引的有效性获取查询执行计划信息用来查看查询优化器如何执行查询。
官方文档
https://dev.mysql.com/doc/refman/5.7/en/explain-output.htmlhttps://dev.mysql.com/doc/refman/8.0/en/explain-output.html
格式
EXPLAIN SELECT clause mysql explain select * from student where id13\G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: studentpartitions: NULLtype: constpossible_keys: PRIMARY #可能用到的键key: PRIMARYkey_len: 4ref: constrows: 1filtered: 100.00Extra: NULL1 row in set, 1 warning (0.00 sec)
EXPLAIN输出信息说明 id执行编号标识select所属的行。如果在语句中没子查询或关联查询只有唯一的 select每行都将显示1。否则内层的 select 语句一般会顺序编号对应于其在原始语句中的位置如果explain的结果包括多个id值则数字越大越先执行而对于相同id的行则表示从上往下依次执行。 select_type查询类型具体见下表。 table具体查询的表名如查 SQL 语句中定义了别名则此处显示别名 。 partitions当前查询匹配记录的分区。对于未分区的表返回 NULL。 type关联类型或访问类型即 MySQL 决定的如何去查询表中的行的方式具体见下表。 possible_keys查询可能会用到的索引此处列出的索引字段是在真正执行查询前的优化过程中创建的因此有些不会被使用。 key实际查询中用到的索引 。 key_len实际查询中使用索引数据中的字节数可通过该列计算查询中使用的索引的长度key_len 显示的值为索引字段的最大可能长度并非实际使用长度即 key_len 是根据表定义计算而得不是通过表内检索出的不损失精确性的情况下长度越短越好 。 ref当使用索引列等值查询时与索引列进行等值匹配的对象信息列与索引的比较表示上述表的连接匹配条件即哪些列或常量被用于查找索引列上的值如果 ref 是一个函数则使用的值是函数的结果。要想查看是哪个函数可在EXPLAIN语句之后紧跟一个SHOW WARNING语句。 rows为了找到所需的行而需要读取的行数估算值不精确。 filtered按表条件过滤的行百分比表示符合查询条件的数据百分比最大100。用 rows × filtered 可获得和下一张表连接的行数。 Extra展示有关本次查询的附加信息具体见下表。
select_type 查询类型具体说明 关键字 说明 SIMPLE 简单查询没有使用 UNION 或子查询) PRIMARY 最外层的查询 UNION 联合查询在 UNION 中的第二个和随后的 SELECT 被标记为 UNION如果UNION 被 FROM 子句中的子查询包含那么它的第一个 SELECT 会被标记为DERIVED DEPENDENTUNION UNION 中的第二个或后面的查询依赖了外层查询 UNION RESULT UNION 查询结果 SUBQUERY 子查询中的第一个 SELECT 查询 DEPENDENT SUBQUERY 子查询中的第一个 SELECT 查询依赖了外层查询 DERIVED 用来表示包含在 FROM 子句的子查询中的 SELECT , MySQL 会递归执行并将结果放到一个临时表中MySQL 内部将此临时表称为 DERIVED table派生表因为该临时表是从子查询中派生出来的 DEPENDENT DERIVED 派生表并且有依赖于其它表 MALTERIALIZED 物化子查询 UNCACHEABLE SUBQUERY 子查询但结果无法缓存必须对于外部查询的每一行重新评估 UNCACHEABLEUNION UNION 属于 UNCACHEABLE SUBQUERY 的第二个或后面的查询
type 关联类型具体说明性能从好到坏排序 关键字 说明 NULL MySQL 在优化过程中分解语句执行时甚至不用访问表或索引此种查询最高效 system 该表只有一行相当于系统表system 是 const 类型的特例 const 针对主键或唯一索引的等值查询扫描,最多只返回一行数据 const 查询速度非常快, 它仅仅读取一次即可。 eq_ref 当使用了索引的全部组成部分并且索引是PRIMARY KEY或UNIQUE NOT NULL 才会使用该类型 ref 当满足索引的最左前缀规则或者索引不是主键也不是唯一索引时才会发生。如果使用的索引只会匹配到少量的行 fulltext 全文索引 ref_or_null 该类型类似于 ref但是 MySQL 会额外搜索哪些行包含了 NULL。这种类型常见于解析子查询。 index_merge 此类型表示使用了索引合并优化表示一个查询里面用到了多个索引 unique_subquery 该类型和 eq_ref 类似但是使用了 IN 查询且子查询是主键或者唯一索引 index_subquery 和 unique_subquery 类似只是子查询使用的是非唯一索引 range 范围扫描表示检索了指定范围的行主要用于有限制的索引扫描。比较常见的范围扫描是带有BETWEEN子句或WHERE子句里有、、、、IS NULL、、BETWEEN、LIKE、IN()等操作符 index 全索引扫描和ALL类似只不过index是全盘扫描了索引的数据。当查询仅使用索引中的一部分列时可使用此类型 ALL 全表扫描性能最差
Extra 额外信息说明 关键字 关键字 Using where 不用读取表中所有信息仅通过索引就可以获取所需数据这发生在对表的全部的请求列都是同一个索引的部分的时候表示 MySQL 服务器将在存储引擎检索行后再进行过滤。 Using temporary 表示 MySQL 需要使用临时表来存储结果集常见于排序和分组查询常见 group byorder by。 Using filesort 当 Query 中包含 order by 操作而且无法利用索引完成的排序操作称为 “文件排序”。 Using join buffer 改值强调了在获取连接条件时没有使用索引并且需要连接缓冲区来存储中间结果。如果出现了这个值那应该注意根据查询的具体情况可能需要添加索引来改进能。 Impossible where 这个值强调了where语句会导致没有符合条件的行通过收集统计信息不可能存在结果。 Select tables optimized away 这个值意味着仅通过使用索引优化器可能仅从聚合函数结果中返回一行。 No tables used Query语句中使用from dual 或不含任何from子句。
mysql explain select * from student where id12\G;*************************** 1. row ***************************id: 1select_type: SIMPLEtable: studentpartitions: NULLtype: const #只读取一次possible_keys: PRIMARYkey: PRIMARY #使用了主键索引key_len: 4ref: constrows: 1 #扫描了1条数据filtered: 100.00Extra: NULL1 row in set, 1 warning (0.00 sec)
mysql explain select * from student where namewangwu\G;*************************** 1. row ***************************id: 1select_type: SIMPLEtable: studentpartitions: NULLtype: ALL #全表扫描possible_keys: NULL #没有使用索引字段key: NULL #没有使用索引字段key_len: NULLref: NULLrows: 6 #扫描了6条数据整个表就是6条数据filtered: 16.67Extra: Using where #在存储引擎检索行后再进行过滤1 row in set, 1 warning (0.00 sec)
1.4.6 profile 工具
开启 profiling 设置可以记录 SQL 语句执行的详细过程
mysql select profiling;-------------| profiling |-------------| 0 |-------------1 row in set, 1 warning (0.00 sec)
#无记录mysql show profiles;Empty set, 1 warning (0.00 sec)
#开启mysql set profiling1;Query OK, 0 rows affected, 1 warning (0.00 sec)
#执行SQL语句mysql select * from student where namewangwu;--------------------------| id | name | age | gender |--------------------------| 12 | wangwu | 13 | M |--------------------------1 row in set (0.00 sec)
#再次查看mysql show profiles;-----------------------------------------------------------------| Query_ID | Duration | Query |-----------------------------------------------------------------| 1 | 0.00091900 | select * from student where namewangwu |-----------------------------------------------------------------1 row in set, 1 warning (0.00 sec)
#删除索引mysql drop index idx_name on student;Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0
#再次查询mysql select * from student where namewangwu;--------------------------| id | name | age | gender |--------------------------| 12 | wangwu | 13 | M |--------------------------1 row in set (0.01 sec)
#两次用时对比mysql show profiles;-----------------------------------------------------------------| Query_ID | Duration | Query |-----------------------------------------------------------------| 1 | 0.00091900 | select * from student where namewangwu || 2 | 0.01600600 | drop index idx_name on student || 3 | 0.00112750 | select * from student where namewangwu | #数据量太小不能体现索引优势-----------------------------------------------------------------3 rows in set, 1 warning (0.00 sec)
导入大表再次对比
[rootrocky86 ~]# cat testlog.sqlcreate table testlog (id int auto_increment primary key,name char(10),salary int default 20);delimiter $$create procedure sp_testlog()begindeclare i int;set i 1;while i 100000do insert into testlog(name,salary) values (concat(wang,FLOOR(RAND() * 100000)),FLOOR(RAND() * 1000000));set i i 1;end while;end$$delimiter;#导入[rootrocky86 ~]# mysql testdb testlog.sqlmysql use testdb;Database changed
mysql show tables;------------------| Tables_in_testdb |------------------| student || t1 || testlog |------------------3 rows in set (0.00 sec)
mysql select count(*) from testlog;----------| count(*) |----------| 0 |----------1 row in set (0.01 sec)
#执行存储过程mysql call sp_testlog;Query OK, 1 row affected (1 min 2.02 sec)
mysql select count(*) from testlog;----------| count(*) |----------| 100000 |----------1 row in set (0.01 sec)
#查询mysql select * from testlog limit 5;-----------------------| id | name | salary |-----------------------| 1 | wang64276 | 606675 || 2 | wang10506 | 705314 || 3 | wang21136 | 940888 || 4 | wang7034 | 529049 || 5 | wang43421 | 583940 |-----------------------5 rows in set (0.00 sec)#默认主键索引mysql show index from testlog\G;*************************** 1. row ***************************Table: testlogNon_unique: 0Key_name: PRIMARYSeq_in_index: 1Column_name: idCollation: ACardinality: 98399Sub_part: NULLPacked: NULLNull:Index_type: BTREEComment:Index_comment:Visible: YESExpression: NULL1 row in set (0.00 sec)
#查询mysql select * from testlog where salary583940;-----------------------| id | name | salary |-----------------------| 5 | wang43421 | 583940 |-----------------------1 row in set (0.01 sec)
#查看索引使用情况mysql explain select * from testlog where salary583940\G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: testlogpartitions: NULLtype: ALL #全表扫描possible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 98399filtered: 10.00Extra: Using where1 row in set, 1 warning (0.00 sec)
#查看执行时间mysql show profiles;-----------------------------------------------------------------| Query_ID | Duration | Query |-----------------------------------------------------------------| 1 | 0.01881575 | select * from testlog where salary583940 |-----------------------------------------------------------------1 row in set, 1 warning (0.00 sec)#在salary列创建索引mysql create index idx_salary on testlog(salary);Query OK, 0 rows affected (0.16 sec)Records: 0 Duplicates: 0 Warnings: 0
#再次查询mysql select * from testlog where salary529049;----------------------| id | name | salary |----------------------| 4 | wang7034 | 529049 |----------------------1 row in set (0.00 sec)
#对比mysql show profiles;------------------------------------------------------------------| Query_ID | Duration | Query |------------------------------------------------------------------| 1 | 0.01881575 | select * from testlog where salary583940 || 2 | 0.15655150 | create index idx_salary on testlog(salary) || 3 | 0.00038900 | select * from testlog where salary529049 |------------------------------------------------------------------3 rows in set, 1 warning (0.00 sec)
mysql explain select * from testlog where salary529049\G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: testlogpartitions: NULLtype: refpossible_keys: idx_salarykey: idx_salarykey_len: 5ref: constrows: 1 #只扫描1条记录filtered: 100.00Extra: NULL1 row in set, 1 warning (0.00 sec)
