做网站不搭建本地环境,win7创建wordpress,网上装修平台哪个最好,金华网站建设网站索引 概述 是帮助MySQL高效获取数据的数据结构#xff0c;在数据之外#xff0c;数据库系统还维护着满足特定查找算法的数据结构#xff0c;这些数据结构以某种方式引用#xff08;指向#xff09;数据#xff0c;这样就可以在数据结构上实现高效查找算法#xff0c;这种…索引 概述 是帮助MySQL高效获取数据的数据结构在数据之外数据库系统还维护着满足特定查找算法的数据结构这些数据结构以某种方式引用指向数据这样就可以在数据结构上实现高效查找算法这种数据结构就是索引
优缺点 优势提高数据检索的效率降低数据库的IO成本通过索引列对数据进行排序降低数据排序的成本降低cpu的消耗。 劣势索引列也要占空间提高了查询效率降低了表的更新速度 索引结构
MySQL的索引是存储在引擎层实现的不同的存储引擎有不同的结构。
BTree 索引 最常见的索引大部分引擎都支持B树索引Hash索引 底层数据结构是用哈希表实现的只有精准匹配索引列的查询才有效不支持范围查询Memory支持R-tree空间索引空间索引是MyISAM引擎的一个特列索引类型主要用于地理空间类型通常使用较少MyISAMFulll_text全文索引是通过建立倒排索引快速匹配文档的方式类似于LuceneSolrESMyISAM5.6版本后的InnoDB支持
平时说的索引没有特别的说明都是指B树结构组织的索引。
二叉树缺点顺序插入时会形成一个链表查询性能大大降低。大量数据的情况下层级较深检查速度慢。红黑树大数据量情况下层级较深检索速度慢。 B-Tree多路平衡查找树
以一颗最大度树max-degree为 5阶的 b-tree 树为例每个节点最多存储4个key5个指针。 BTree
MySQL索引数据结构对经典的Btree进行了优化。在原Btree的基础上增加一个指向相邻的叶子节点的链表指针就形成了带有顺序的BTree提高区间访问的性能。 Hash
哈希索引就是采用一定的hash算法把键值换算成新的hash值然后存储在hash表中如果两个或多个键值映射到一个相同的槽位上他们就产生了hash冲突也称为hash碰撞可以通过链表解决。
Hash索引特点
Hash索引只能用于对等比较in)不支持范围查询between。无法利用索引完成排序操作查询效率高通常只需要一次检索就可以了效率通常要高于Btree索引。 存储引擎支持
在MySQL中支持hash索引的是Memory引擎而InnoDB中具有自适应hash功能hash索引是存储引擎根据BTree索引在指定条件下自动构建的。 思考为什么InnoDB存储引擎选择使用Btree索引结构
相对于二叉树层次更少搜索效率高。
对于B-tree无论是叶子节点还是非叶子节点都会保存数据这样导致一页中的存储的键值减少指针跟着减少要同样保存大量数据只能增加树的高度导致性能降低。
相对Hash索引Btree支持范围匹配及排序操作。 索引分类
主键索引针对表中主键创建的索引 只能有一个 primary
唯一索引避免同一个表中数据列中的值重复 可以有多个 unique
常规索引快速定位特定数据 可以有多个
全文索引全文索引查找的是文本中的关键词而不是比较索引的值 可以有多个 fulltext 根据索引的形式又可以分为以下两种
聚集索引将数据存储与索引放到了一块索引结构的叶子节点保存了行数据。必须有而且只能有一个
二级索引辅助索引 非聚集索引将数据与索引分开存储索引结构的叶子节点关联的是对应的主键。可以存在多个 聚集索引选取规则
如果存在主键主键就是聚集索引b
不存在主键使用第一个| UNIQUE | 唯一索引作为聚集
如果都没有则InnoDB会自动生成一个rowid作为隐藏的聚集索引。 回表查询先在二级索引拿到主键值再根据主键值走聚集索引拿到这一行的数据。 select * from user where id10; select * from user where nameArm id为主键name字段创建的有索引。 第一条SQL语句执行效率更高一点不用回表查询 思考InnoDB主键索引的Btree为多高呢
假设一行数据大小为1k一页可存16行InnoDB指针占用6个字节的空间、主键为bigint占字节数为8.
假设高度为2 设主键个数为n n * 8 (n1) * 6 16 * 1024 n约为1170所以指针个数为1071
若高度为3 1171 * 1171 * 16 21939856 索引语法 创建索引 create [ unique | fulltext ] index index_name on table_name (index_col_name,.......); 查看 show index from table_name; 删除索引 drop index index_name on table_name; SQL性能分析
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)中配置如下信息 #查看状态的开关语句 show variables like slow_query_log; #开启MySQL慢日志查询开关。 slow_query_log1; #设置慢日志的时间为2秒SQL语句执行时间超过2秒。就会视为慢查询记录慢查询日志。 long_query_time2; 配置后重新启动MySQL服务器进行测试、查看慢日志文件中记录的信息。 /var/lib/mysql/localhost-slow.log profile 详情 show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling 参数能够看到当前MySQL是否支持profile操作。 select have_profiling 默认profiling是关闭的可以通过set语句在session/global 级别开启profiling。 set porfiling1; #查看每一条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执行计划各字段含义
idselect查询的序列号表示查询中执行select子句或者是操作表的顺序id相同执行顺序从上到下id不同值越大越先执行。
select _type表示select类型常见的取值有simple简单表即不使用表或子查询、primary主查询、即外层的查询、primary主查询、即外层的查询、UNIONunion中的第二个或后面的查询语句、subqueryselect/where 之后包含了子查询等。
type表示连接类型性能由好到差的连续类型为NULLsystemconsteq-ref indexall.
possible_key:显示可能应用在这张表上的索引一个或多个。
key实际使用的索引如果为NULL即没有使用索引。
key-len表示索引中使用的字节数该值为索引字段的最大可能长度在不损失精确性的前提下长度越短越好。
rowsMySQL认为必须要执行的行数在innodb引擎的表中是一个估计值可能并不准确。
filtered表示返回结果的行数占需读取行数的百分比filtered的值越大越好。 索引使用
验证索引效率
在未建立索引之前执行如下SQL语句查看SQL耗时 select * from tb_sku where sn10000003145001 针对字段创建索引 create index idx_sku_sn on tb_sku(sn); 然后再次执行查看SQL耗时的那个语句 最大前缀法则必须包含索引的最左列 - 不止位置只要存在即可
如果索引了多列联合索引要遵守最左前缀法则最左前缀法则指的是查询从索引的最左列开始并且不跳过索引的列。如果跳跃某一列索引将部分失效后面的字段索引失效 范围查询
联合索引中出现范围查询,范围查询右侧的列索引失效尽量加上就不失效
索引列运算
不要在索引列上进行运算操作索引将失效。 explain select * from tb_user where substring(phone,10, 2) 15 keyNULL变成all 字符串不加引号
字符串类型字段使用时不加引号索引将失效。
模糊查询
如果仅仅是尾部模糊查询索引不会失效如果是头部模糊失效索引失效。 explain select * from tb_user where profession like 软件%; 走索引 explain select * from tb_user where profession like %工程; 不走索引 or连接的条件
用or分离开的条件如果or前的条件中的列有索引而后面的列中没有索引那么涉及的索引都不会被用到。 explain select * from tb_user where id10 or age 23; expalin select * from tb_user where age23 or phone1299........ 由于age没有索引所以即使id、phone有索引也要针对age建立索引。
数据分布影响数据多不走索引
如果MySQL 评估使用索引会比全表更慢则不使用索引。
SQL提示
SQL提示是优化数据库的一个重要手段简单来说就是在SQL语句中加入一些人为的提示来达到优化操作的目的。
user index: explain select * from tb_user use index(idx_user_pro) when profession软件工程; ignore index: explain select * from tb_user ignore index(idx_user_pro) when profession软件工程; force index: explain select * from tb_user force index(idx_user_pro) when profession软件工程; 覆盖索引
尽量使用覆盖索引查询使用了索引并且需要返回的列在该索引中已经全部能够找到减少select*。 using where,using index 性能高 using index condition 性能低 注using index condition 查找使用了索引但需要回表查询数据。
using where,using index查找使用了索引但是需要的数据都在索引列中能找到所以不需要回表查询。
一张表有四个字段idusernamepasswordstatus由于数据量大需要对以下SQL进行优化该如何进行优化。 set idusernamepassword from tb_user where usernameitcast; 建立username和password的联合索引
前缀索引
当字符串类型为字符串varchar、text等时有时候需要索引很长的字符串这会让索引变得很大查询时浪费大量磁盘IO影响效率此时可以将字符串的一部分前缀建立索引这样可以大大节约系统空间从而提高效率。
语法 create index idx_xxx on table_name(coloumnln)) 前缀长度可以根据索引的选择性来决定而选择性指不重复的索引值基数和数据表记录总数的比值索引选择越高则查询效率越高唯一索引的选择性是1这是最好的索引选择性性能也是最好的。 eg create index idx_email_5 on tb_user(email(5)); select count(distinct email)/count(*) from tb_user; select count(distinct substring(email,1,5))/count(*) from tb_user; 单列索引和联合索引
单列索引即一个索引只包含一列
联合索引即一个索引包含了多个列
在业务场景中如果存在多个查询条件考虑针对于查询字段建立索引时而非单列索引。
多条件联合查询时MySQL优化器会评估哪个字段的效率更高会选择该索引完成本次查询。 索引设计原则
数据量大查询频繁查询条件、排序、分组操作的字段建立索引区分度高的尽量建立唯一索引区分越高使用效率越高如果是字符串类型的字段建立前缀索引尽量使用联合索引减少单列索引前者可覆盖索引节省存储空间提高查询效率。控制索引数量如果索引不能存储NULL值请在创建表时用NOT NULL约束优化器知道每列是否包含NULL值能更好的确定哪个索引用于查询。
