凡科做网站技巧,创世网站网络建设,资源网址有哪些,手机软件是怎么做出来的一、概述
索引是SQL优化的核心
索引(index)是帮助MySQL高效获取数据的数据结构。在数据之外#xff0c;数据库系统还维护着满足特定查找算法的数据结构#xff0c;这些数据结构以某种引用(指向数据)#xff0c;这样可以在这些数据结构上实现高级查找算法#xff0c;这种数…一、概述
索引是SQL优化的核心
索引(index)是帮助MySQL高效获取数据的数据结构。在数据之外数据库系统还维护着满足特定查找算法的数据结构这些数据结构以某种引用(指向数据)这样可以在这些数据结构上实现高级查找算法这种数据结构就是索引
下面演示了一条查询语句在有索引(假设维护了一个age二叉排序树)和无索引的情况下的查询效率 索引的优缺点 优点提高数据检查的效率降低数据库IO成本 通过索引列对数据进行排序降低数据排列的成本降低CPU消耗 缺点索引列也要占用空间 索引虽然提高了查询效率但由于要维护索引的数据结构所以在增删改的时候效率 较低 索引结构默认是B树索引所以我们在之后的学习中也主要学习这种结构
二、索引结构B树 为什么InnoDB要采用B树为索引结构
对于B-tree无论是叶子节点还是非叶子节点都会保存数据这样导致一页中存储的键值减少指针跟着减少要同样保存大量数据只能增加树的高度导致性能降低相对于二叉树层级更少搜索效率更高相对于Hash索引Btree支持范围匹配和排序操作
三、索引分类 在InnoDB存储引擎中根据索引的存储形式又可以分为
聚集索引B树最下面的叶子节点挂的是这一行的数据
二级索引B树最下面的叶子节点挂的是对应的主键
默认主键索引就是聚集索引 聚集索引 如果存在主键主键索引就是聚集索引 如果不存在主键将使用第一个唯一unique索引作为聚集索引 如果表没有主键没有合适的唯一索引InnoDB会自动生成一个rowid作为隐藏的聚集索引 四、思考题
下面两个sql语句哪一个效率高
id有主键name字段创建的有索引
select * from user where id10;
select * from user where nameArm;
答第一个效率高因为id是聚集索引我们直接去聚集索引中去查就可以而查到的刚好是行数据可以直接查到 第二个是二级索引需要先去二级索引中查到对应的id值然后回表查询(拿着查到的id去聚集索引中去查)相当于扫描了两个表所以效率偏低 五、索引语法 查看tb_user表索引
show index from tb_user;因为name字段可以重复我们就不能创建唯一索引所以要创建常规索引
索引名为idx_user_name
create index idx_user_name on tb_user(name);
给phone字段创建唯一索引
create unique index idx_user_phone on tb_user(phone);
给profession、age、status字段创建联合索引
create index idx_user_pro_age_sta on tb_userr(profession,age,status);
给email创建常规索引
create index idx_user_email on tb_user(email);
删除索引
drop index 索引名称 on tb_user;
六、SQL性能分析工具
我们做SQL性能优化前提是要知道哪些SQL需要我们去优化那么SQL性能分析工具就有必要去了解
1. 查看执行频次
查看数据库中哪些类型的sql语句执行频次较高针对这一类的sql进行优化会大大提高我们的效率
show global status like Com_______ 2. 慢查询日志
我们通过查看sql语句的执行频次发现select的执行频次较高那么我们到底针对哪些sql语句进行优化呢?
我们这里会用到慢查询日志来定位到哪些sql执行效率低从而找到需要优化的sql
慢查询日志记录了所有执行时间超过指定参数(long_query_time单位秒默认10秒)的所有sql语句的日志MySQL的慢查询日志默认没有开启需要在MySQL配置文件中(/etc/my.cnf)中配置如下信息 3. show profiles 执行一些sql语句然后show profiles可以看到这些sql语句的执行耗时情况 3. explain执行计划
explain执行计划个各个字段的含义
idselect查询的序列号表示查询中执行select子句或者是操作表的顺序(id相同执行顺序从上到下id不同值越大越先执行)
select_type表示select的类型常见的有simple(简单表即不使用表连接或者子查询)、primary(主查询即外层的查询)、union(union中的第二个或者后面的查询语句)subquery(select/where之后包含了子查询)
type表示连接类型性能由好到差的类型为nullsystemconsteq_refrefrangeindexall
possiable_key显示可能用在这张表上的索引
key实际用到的索引
key_len使用到索引的字节数长度越短越好
rowsMySQL认为必须要查询的行数是一个估计值不准确
filter返回结果的行数占需要读取行数的百分比
七、索引使用规则
通过上面介绍根据一个字段查询这个字段是不是建立了索引对于查询的时间消耗不是一个数量级的建立了索引后查询时间大幅减少。
下面我们将介绍索引的使用规则
1. 最左前缀法则
如果索引了多列(联合索引)要遵循最左前缀法则。最左前缀法则指的是查询从索引的最左列开始并且不跳过索引中的列。如果跳跃了某一列索引将部分失效(后面的索引失效)。
只要最左边的索引存在就可以走索引跟sql语句中的位置无关
2. 范围查询
联合索引中出现范围()范围查询右侧的列索引失效
要想不失效在业务允许的范围内尽量把替换成
3. 索引失效情况
①在索引列上进行运算操作索引将会失效
②字符串类型使用时sql语句没加单引号索引会失效
③模糊查询头部模糊匹配的时候索引将会失效
④or分割开的条件一个有索引一个没有索引则涉及的索引都不会被用到
⑤MySQL评估使用索引比全表扫描更慢则不适用索引
4. SQL提示
SQL提示是优化数据库的一个重要手段简单来说就是在sql语句中加入一些人为的提示来达到优化操作的目的
use index 用某个索引(最后MySQL会评估到底用不用有可能会不采用我们的建议)
ignore index 忽略某个索引(最后MySQL会评估到底用不用有可能会不采用我们的建议)
force index 强制用某个索引(忽略MySQL评估强制MySQL用我们的建议) 5. 覆盖索引回表查询
覆盖索引就是添加索引的字段覆盖了我们需要查询的字段如果没有覆盖那么就会产生回表查询即先查辅助索引表再查聚集索引表性能偏低
所以我们要尽量避免select * 的写法除非我们把所有的字段都添加了索引 下面一道思考题 6. 单列索引和联合索引
单列索引即一个索引只包含了单个列
联合索引即一个索引包含了多个列
在业务场景中如果存在多个查询条件考虑针对于查询字段建立索引时建议建立联合索引而非单列索引 7. 索引设计原则
针对于数据量较大且查询比较频繁的表建立索引针对于常作为查询条件(where)、排序(order by)、分组 (group by) 操作的字段建立索引。尽量选择区分度高的列作为索引尽量建立唯一索引区分度越高使用索引的效率越高。如果是字符串类型的字段字段的长度较长可以针对于字段的特点建立前缀索引。尽量使用联合索引减少单列索引查询时联合索引很多时候可以覆盖索引节省存储空间避免回表提高查询效率.要控制索引的数量索引并不是多多益善索引越多维护索引结构的代价也就越大会影响增删改的效率。如果索引列不能存储NUL值请在创建表时使用NOTNUL约束它。当优化器知道每列是否包含NUL值时它可以更好地确定哪个索引最有效地用于查询。
