昆明网站建设服务至上,外国人做的汉字网站,怎么建设商品网站,工程软件专业pre#xff1a;
分享目标#xff1a;怎样看到索引有被用到、索引的注意事项等等
索引有哪些#xff1a;全文索引、主键索引、唯一索引、普通索引、覆盖索引、组合索引、B-Tree索引、哈希索引…
InnoDB使用聚簇索引#xff0c;B树索引。
B 索引 MySQL InnoDB的索引数据结…pre
分享目标怎样看到索引有被用到、索引的注意事项等等
索引有哪些全文索引、主键索引、唯一索引、普通索引、覆盖索引、组合索引、B-Tree索引、哈希索引…
InnoDB使用聚簇索引B树索引。
B 索引 MySQL InnoDB的索引数据结构B树 首先库表的数据和索引都存储在磁盘中而在磁盘中读取数据时是按磁盘块来读取每访问一次磁盘块就是一次磁盘IO
选择树作为存储索引的数据结构是为了提高查找效率但普通的二叉树会有退化为链表的极端情况查找最后一个元素相当于进行全表扫描所以我们可以进一步考虑使用平衡二叉树。
但是对于磁盘和一般的平衡二叉树而言一个树节点相当于一个磁盘块当有海量数据时树的高度会非常高查询数据时会进行大量的IO效率极低。
基于这样的场景我们可以进一步考虑使用平衡多叉树B树每个节点可以存储更多的键值和数据可以拥有更多的子节点从而降低树的高度但是数据分散在各个节点上对于范围、排序、分组、去重等类型的查找会十分困难。
基于这样的场景我们可以使用B树B树是对B树的优化每个节点之间通过双向列表连接节点中的数据通过单向链表连接此外由于每个树节点即磁盘块的大小是有限制的默认16k,如果节点不存储数据就可以存储更多的键值从而有更多的子节点树的高度更低例如假设主键使用bigint类型存储一个主键占8个字节的空间对于3层的B树最多可以存储16x1024x16x1024≈2.7亿的数据而根节点是常驻内存的也就是说查找2.7亿数据只需要两次磁盘IO
聚簇索引的优点 可以把相关数据保存在一起。例如实现电子邮箱时可以根据用户ID来聚集数据这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引则每封邮件都可能导致一次磁盘I/O. 数据访问更快。聚簇索引将索引和数据保存在同一个B-Tee中因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快。 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。
慢查询的原因 join或者子查询过多 锁或者死锁拿不到锁。show processlist in元素过多如果太多可以进行分组查询分组查询限制查询的数量小于500等措施 返回了不必要的行和列 没有索引或者索引设计不合理 左右连接关联的字段编码格式不一样 优化器选错了索引使用force index强制使用某个索引 group by 使用临时表且会默认排序 Extra 这个字段的Using temporary表示在执行分组的时候使用了临时表select city ,count(*) as num from staff group by city; 先查出city,再计数。 解决
group by 后面的字段加索引 order by null 不用排序 尽量只使用内存临时表 使用SQL_BIG_RESULT
避免多个范围查询出现两个范围条件都是索引都使用了in查询就无法同时使用。
1、如何建立高性能的索引 1、索引的选择性是指不重复的索引值也称为基数cardinality)和数据表的记录总数(#T)的比值范围从l/#T到1之间。索引的选择性越高则查询效率越高因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1这是最好的索引选择性性能也是最好的。也就是每个索引都对应一行。
如何创建好索引
经常作为where条件的列建议创建索引,只为用于搜索、排序或分组的列创建索引不要过度创建索引 和其他表具有关联关系的列建议创建索引(这条是公司的索引创建规范
单键索引选择针对当前query过滤性更好的索引
联合索引where条件中过滤性最好的放在最左侧
尽可能选择覆盖索引避免回表
通过分析统计信息和调整query的写法来达到选择合适索引的目的
索引的失效场景
不满足联合索引的最左匹配原则
索引列上有计算索引列上有函数
字段类型不同
like左边包含%
使用or关键字
not in和not exist
字段设计 建议使用TINYINT来代替ENUM类型 用DECIMAL代替FLOAT和DOUBLE存储精确浮点数
表设计时的常见优化手段: 1.尽量为字段选择合适的数据类型; 字段所占的空间越小, 检索速度越快, 网络传输也快.
2.将冷热数据分离开来, 因为Mysql在检索数据时, 并不会单单读取某一个字段, 而是会把包含该字段的整行数据一起读取出来.
3.对于经常需要联表查询的数据, 可以做数据冗余, 增加查询速度.
4.建立合适的索引.
查看执行计划
id 包含一组数字表示查询中执行select子句或操作表的顺序,代表是否是子查询
select_type 表示查询中每个select子句的类型简单 OR复杂
SIMPLE查询中不包含子查询或者UNION
查询中若包含任何复杂的子部分最外层查询则被标记为PRIMARY
在SELECT或WHERE列表中包含了子查询该子查询被标记为SUBQUERY
在FROM列表中包含的子查询被标记为DERIVED衍生
若第二个SELECT出现在UNION之后则被标记为UNION若UNION包含在 FROM子句的子查询中外层SELECT将被标记为DERIVED
从UNION表获取结果的SELECT被标记为UNION RESULT
type 表示MySQL在表中找到所需行的方式又称“访问类型”
ALLindexrange ref eq_ref const,system NULL
all 全数据表扫描 index 全索引表扫描extra:Using index采用了索引覆盖也就是索引可以覆盖所需的 SELECT 字段就不需要进行回表这样就减少了数据查找的开销 index_merge 合引并索使用多个单列索引搜索 range 对索引列进行范围查找 const 常量表最多有一个匹配行因为只有一行在这行的列值可被优化器认为是常数。const 类型表示我们使用了主键或者唯一索引所有的部分与常量值进行比较
eq_ref 搜索时使用primary key或unique类型常用于多表联查.(使用主键或唯一索引时产生的访问方式.
ref 根据索引查找一个或多个值 system 系统表只有一行(一般用于MyISAM或Memory表)。是const连接类型的特例
possible_keys 指出MySQL能使用哪个索引在表中找到行查询涉及到的字段上若存在索引则该索引将被列出但不一定被查询使用
key 显示MySQL在查询中实际使用的索引若没有使用索引显示为NULL
TIPS查询中若使用了覆盖索引则该索引仅出现在key列表中
key_len 表示索引中使用的字节数可通过该列计算查询中使用的索引的长度
ref 表示上述表的连接匹配条件即哪些列或常量被用于查找索引列上的值,显示索引的哪一列被使用了
rows
表示MySQL根据表统计信息及索引选用情况估算的找到所需的记录所需要读取的行数
Extra 包含不适合在其他列中显示但十分重要的额外信息
a.Using index
该值表示相应的select操作中使用了覆盖索引Covering Index
3、limit优化
limit深分页导致SQL变慢原因有两个
limit语句会先扫描offsetn行然后再丢弃掉前offset行返回后n行数据。也就是说limit 100000,10就会扫描100010行而limit 0,10只扫描10行。 limit 100000,10 扫描更多的行数也意味着回表更多的次数。 解决方法标签记录法和延迟关联法。
1、 limit的问题主要在于偏移量太大需要扫描太多无用数据如果可以记录上次取数据的位置那么下次可以直接从这个位置扫描这样就可以避免使用offset。
select * from cc_flow where id 12900 limit 20
2、
尽可能使用覆盖索引而不是查询所有的列然后根据需要做一次关联获取全量数据。通俗来讲就是利用搜索条件查找到对应id根据id查找全量数据。
使用inner join内连接即可
SELECT * FROM coupon_00 inner join ( SELECT id from cc_flow WHERE status “locked” LIMIT 700,1000 )b USING (id);
其他 B-TREE索引在范围查找的SQL语句中更加适合顺序存储
对于非常小的表大部分情况下简单的全表扫描更高效。
对于中到大型的表索引就非常有效。
但对于特大型的表建立和使用索引的代价将随之增长。
这种情况下则需要一种技术可以直接区分出查询需要的一组数据而不是一条记录一条记录地匹配。
