合肥网站制作公司,开鲁网站seo免费版,2018年公司网站建设费分录,一对一直播app源码一、MySQL 执行流程 innoDB表引擎#xff1a;默认的事务型引擎#xff0c;最重要最广泛的存储引擎#xff0c;性能非常优秀,数据村粗在共享表空间#xff0c;可以通过配置分开,主键查询性能高于其他引擎
myISM表引擎#xff1a;5.1版本前这个是默认的存储引擎#xff0c…一、MySQL 执行流程 innoDB表引擎默认的事务型引擎最重要最广泛的存储引擎性能非常优秀,数据村粗在共享表空间可以通过配置分开,主键查询性能高于其他引擎
myISM表引擎5.1版本前这个是默认的存储引擎拥有全文索引、压缩、空间函数。不支持事务和行级锁不支持安全恢复安全性低存储在MYD和MYI两个文件中
innoDB和myISM的区别InnoDB支持行锁myISM支持表锁myISM存储在MYD和MYI两个文件中InnoDB存在共享表空间InnoDB支持事务处理myISM不支持
InnoDB 存储引擎B 树索引的叶子节点保存数据本身 MyISAM 存储引擎B 树索引的叶子节点保存数据的物理地址
二、索引分类
分类标准结果数据结构Btree索引、Hash索引、Full-text索引物理存储聚簇索引主键索引、二级索引辅助索引字段特性主键索引、唯一索引、普通索引、前缀索引字段个数单列索引、联合索引
三、数据结构分类
索引类型innoDB 引擎MyISAM引擎Memory引擎B 索引√√√Hash 索引×不支持Hash索引但在内存结构中有一个自适应hash索引×√full-text 索引√√×
四、InnoDB 聚簇索引主键索引生成策略
根据主键ID 自动生成聚簇索引没有主键 选择第一个不为 NULL 的唯一索引作为聚簇索引以上均没有则会使用一个 6 个字节长整型的隐式字段 ROWID 构建聚簇索引自增
在建表时 InnoDB 存储引擎默认会创建一个主键索引也就是聚簇索引其它索引都属于二级索引
四、索引回表
定义先通过索引扫描再通过ID(主键)索引去取索引中未能提供的数据即为回表
假设有表T 三个字段id k name 其中对 k 建立了单独索引 如果语句是 select * from T where id200即主键查询方式(聚集索引)则只需要搜索 ID 这棵 B 树查询一表即可 如果语句是 select id, k from T where k3即普通索引查询方式则只要搜索 k 索引树这样的话查询一表即可。 如果语句是 select id, k , name from T where k3第一次通过普通索引查询方式得到 id 的值为 200再到 id 索引树搜索一次(需要回表才能查到name这个数据)。这个过程称为回表。那如何避免回表将k和name建成联合索引即可当然就算回表也会比没有建立索引快
也就是说基于非主键索引的查询需要多扫描一棵索引树。因此我们在应用中应该尽量使用主键(聚集索引)查询
如何避免回表所以在查询时可以尽量用聚集索引来查即主键查询或根据业务需求建立的索引能满足索引查询字段。 但实际业务中很难建立一个索引就满足所有查询要求所以正常情况回表也没事只要能用到索引也能大大加快查询速度。
参考连接 https://www.kancloud.cn/qq1014407916/huangwei123456/3019227
五、字段特性索引分类
类型说明主键索引建立在主键字段上的索引通常在创建表的时候一起创建一张表最多只有一个主键索引索引列的值不允许有空值唯一索引建立在 UNIQUE 字段上的索引一张表可以有多个唯一索引索引列的值必须唯一但是允许有空值普通索引建立在普通字段上的索引既不要求字段为主键也不要求字段为 UNIQUE前缀索引指对字符类型字段的前几个字符建立的索引而不是在整个字段上建立的索引前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。使用前缀索引的目的是为了减少索引占用的存储空间提升查询效率。创建前缀索引的方式如下
前缀索引
六、联合索引
通过将多个字段组合成一个索引该索引就被称为联合索引。 联合索引遵循最左匹配原则
假设创建了一个 (a, b, c) 三字段的联合索引那么如果查询条件是以下这几种就可以匹配上联合索引
…where ax …where ax and by and cz …where ax and by
假设 a字段 是全局有序的1, 2, 2, 3, 4, 88,99,104,106而 b 是全局是无序的1278892638852。因此直接执行where b 12 这种查询条件没有办法利用联合索引的利用索引的前提是索引里的 key 是有序的。
在 a 相同的情况下 b 才是有序的比如 a 等于 2 的时候假设此时 b 的值为7889这时就是有序的这个有序状态是局部的因此执行where a 2 and b 7是 a 和 b 字段能用到联合索引的也就是联合索引生效了。
联合索引之范围查询
场景一 select * from t_table where a 1 and b 12 联合索引a, b哪一个字段用到了联合索引的 BTree
为 a
场景二 select * from t_table where a 1 and b 12 联合索引a, b哪一个字段用到了联合索引的 BTree
a,b 都用到了 但其中 b 索引只在 a1 时使用
场景三: select * from t_table where a between 2 AND 88 AND b 12联合索引a, b哪一个字段用到了联合索引的 BTree
a,b联合索引
场景四: select* from t_user where a like ‘2%’ and b 78联合索引name, age哪一个字段用到了联合索引的 BTree
a,b联合索引
七、执行流程SQL优化-索引下推
执行器与存储引擎之间的交互过程
主键索引查询 全表扫描 索引下推 对于联合索引a, b在执行 select * from table where a 1 and b 12 语句时只有 a 字段能用到索引 在联合索引的 BTree 找到第一个满足条件的主键值id 为 2后还需要判断其他条件是否满足看 b 是否等于 12那么是在联合索引里判断还是在主键索引判断 MySQL 5.6 之前只能从 id 2 主键值开始一个个回表到主键索引上找出数据行再对比 b 字段值 MySQL 5.6 引入的索引下推优化index condition pushdown) 可以在联合索引遍历过程中对联合索引中包含的字段先做判断直接过滤掉不满足条件的记录从而减少回表次数。
当查询语句的执行计划里出现了 Extra 为 Using index condition 说明使用了索引下推优化
联合索引之索引区分度 由于建立联合索引时的字段顺序对索引效率也有很大影响越靠前的字段被用于索引过滤的概率越高 建立联合索引时要把区分度大的字段排在前面这样区分度大的字段越有可能被更多的 SQL 使用到
区分度计算方式 某个字段所有值的不同个数 除以 总行数
如果索引的区分度很小假设字段的值分布比较均匀那么无论搜索哪个值都可能得到一半的数据在这种情况下还不如不要索引 因为 MySQL 有一个查询优化器查询优化器发现某个值出现在表的数据行中的百分比常用的百分比界线是 30%很高时一般会忽略索引进行全表扫描!
八、常见索引优化
前缀索引优化使用前缀索引是为了减小索引字段大小。但前缀索引有一定的局限性例如 order by 就无法使用前缀索引 无法把前缀索引用作覆盖索引
覆盖索引优化执行SQl以达到索引覆盖
主键索引设置自增每插入一条新记录都是追加操作不需要重新移动数据如果我们使用非自增主键会出现页分裂页分裂可能会造成大量的内存碎片导致索引结构不紧凑从而影响查询效率。
索引尽量设置为 NOT NULL索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂例如 count 会省略值为NULL 的行 NULL 值是一个没意义的值但会占用物理空间至少会用 1字节
防止索引失效通过执行计划分析 sql
九、索引失效
对索引使用左或者左右模糊匹配 %xxx, %xxxxx%对索引使用函数 例如点 length 的时候对索引进行表达式计算 加减计算对索引隐式类型转换 (假设设置了索引字段为 varchar 类型但是写sql 时使用了 整形的写法mysql 会通过默认的类型转换机制进行转换即通过函数等方式也就变相导致索引失效但有时仅仅对值做了转换而没有对字段使用函数那么即使做了潜在类型置换也不会导致索引失效) 可以通过 select “10” 9 的结果来知道MySQL 的数据类型转换规则是什么 如果规则是 MySQL 会将自动「字符串」转换成「数字」就相当于 select 10 9这个就是数字比较所以结果应该是 1 如果规则是 MySQL 会将自动「数字」转换成「字符串」就相当于 select “10” “9”这个是字符串比较字符串比较大小是逐位从高位到低位逐个比较按ascii码 那么10字符串相当于 “1”和“0”字符的组合所以先是拿 “1” 字符和 “9” 字符比较因为 “1” 字符比 “9” 字符小所以结果应该是 0。 实际上面的结果为 1说明 MySQL 在遇到字符串和数字比较的时候会自动把字符串转为数字然后再进行比较
例如
# 字段是字符串写为整形
select * from t_user where aaabc 123456;
# 相当于
select * from t_user where CAST(aaabc AS signed int) 123456;
# 失效# 字段为整形 写为 字符串
select * from t_user where id 1;
# 相当于
select * from t_user where id CAST(1 AS signed int);
# 有效参考文献 https://zhuanlan.zhihu.com/p/471209432?utm_id0
联合索引非最左匹配见上方联合索引说明WHERE 子句中的 OR在 WHERE 子句中如果在 OR 前的条件列是索引列而在 OR 后的条件列不是索引列那么索引会失效
九、执行计划
执行计划中的参数可以参考如下
column意义id序号,非Idselect_type查询类型table查询表名partitions匹配分区type表示数据扫描类型主要看此类型processible_keys可能选择的索引key实际选择的索引列字段表示实际用的索引如果这一项为 NULL说明没有使用索引key_len索引长度ref与索引做比较的列rows预计要检索的行数filtered查询过滤的行数百分比Extra其他额外信息
type 扫描类型执行效率从低到高为
类型 从低到高意义All全表扫描index全索引扫描range索引范围扫描ref非唯一索引扫描eq_ref唯一索引扫描const结果只有一条的主键或唯一索引扫描
索引长度计算说明
使用在 查询SQL 前加入关键字 EXPLAIN 即可执行查询对应sql 的执行计划
count(*) 和 count(1)
count() 是一个聚合函数函数的参数不仅可以是字段名也可以是其他任意表达式该函数作用是统计符合查询条件的记录中函数指定的参数不为 NULL 的记录有多少个
如果表里有二级索引时InnoDB 循环遍历的对象就不是聚簇索引而是二级索引。
count(1) 相比 count(主键字段) 少一个步骤就是不需要读取记录中的字段值所以通常会说 count(1) 执行效率会比 count(主键字段) 高一点
性能排序 count(*) count(1) count(主键字段) count(字段)
