网站建设交流论坛上海网站建设公司服务怎么做

当前位置： 首页 > news >正文

网站建设交流论坛,上海网站建设公司服务怎么做,网站 pr,重庆微信网站代理商通过索引进行优化 索引基本知识 索引的优点 1、大大减少了服务器需要扫描的数据量2、帮助服务器避免排序和临时表3、将随机io变成顺序io 索引的用处 1、快速查找匹配WHERE子句的行2、从consideration中消除行,如果可以在多个索引之间进行选择#xff0c;mysql通常会使用找到…通过索引进行优化 索引基本知识 索引的优点 1、大大减少了服务器需要扫描的数据量2、帮助服务器避免排序和临时表3、将随机io变成顺序io 索引的用处 1、快速查找匹配WHERE子句的行2、从consideration中消除行,如果可以在多个索引之间进行选择mysql通常会使用找到最少行的索引3、如果表具有多列索引则优化器可以使用索引的任何最左前缀来查找行4、当有表连接的时候从其他表检索行数据5、查找特定索引列的min或max值6、如果排序或分组时在可用索引的最左前缀上完成的则对表进行排序和分组7、在某些情况下可以优化查询以检索值而无需查询数据行 索引的分类 主键索引唯一索引普通索引全文索引组合索引 面试技术名词 回表覆盖索引最左匹配索引下推 索引采用的数据结构 哈希表B树 索引匹配方式 create table staffs( id int primary key auto_increment, name varchar(24) not null default ‘’ comment ‘姓名’, age int not null default 0 comment ‘年龄’, pos varchar(20) not null default ‘’ comment ‘职位’, add_time timestamp not null default current_timestamp comment ‘入职时间’ ) charset utf8 comment ‘员工记录表’; ———–alter table staffs add index idx_nap(name, age, pos); 全值匹配 全值匹配指的是和索引中的所有列进行匹配explain select * from staffs where name ‘July’ and age ‘23’ and pos ‘dev’; 匹配最左前缀 只匹配前面的几列explain select * from staffs where name ‘July’ and age ‘23’;explain select * from staffs where name ‘July’; 匹配列前缀 可以匹配某一列的值的开头部分explain select * from staffs where name like ‘J%’;explain select * from staffs where name like ‘%y’; 匹配范围值 可以查找某一个范围的数据explain select * from staffs where name ‘Mary’; 精确匹配某一列并范围匹配另外一列 可以查询第一列的全部和第二列的部分explain select * from staffs where name ‘July’ and age 25; 只访问索引的查询 查询的时候只需要访问索引不需要访问数据行本质上就是覆盖索引explain select name,age,pos from staffs where name ‘July’ and age 25 and pos ‘dev’; 哈希索引 基于哈希表的实现只有精确匹配索引所有列的查询才有效在mysql中只有memory的存储引擎显式支持哈希索引哈希索引自身只需存储对应的hash值所以索引的结构十分紧凑这让哈希索引查找的速度非常快哈希索引的限制 1、哈希索引只包含哈希值和行指针而不存储字段值索引不能使用索引中的值来避免读取行2、哈希索引数据并不是按照索引值顺序存储的所以无法进行排序3、哈希索引不支持部分列匹配查找哈希索引是使用索引列的全部内容来计算哈希值4、哈希索引支持等值比较查询也不支持任何范围查询5、访问哈希索引的数据非常快除非有很多哈希冲突当出现哈希冲突的时候存储引擎必须遍历链表中的所有行指针逐行进行比较直到找到所有符合条件的行6、哈希冲突比较多的话维护的代价也会很高 案例 当需要存储大量的URL并且根据URL进行搜索查找如果使用B树存储的内容就会很大 select id from url where url“” 也可以利用将url使用CRC32做哈希可以使用以下查询方式 select id fom url where url“” and url_crcCRC32(“”) 此查询性能较高原因是使用体积很小的索引来完成查找
组合索引 当包含多个列作为索引需要注意的是正确的顺序依赖于该索引的查询同时需要考虑如何更好的满足排序和分组的需要案例建立组合索引a,b,c 不同SQL语句使用索引情况
聚簇索引与非聚簇索引 聚簇索引 不是单独的索引类型而是一种数据存储方式指的是数据行跟相邻的键值紧凑的存储在一起 优点 1、可以把相关数据保存在一起2、数据访问更快因为索引和数据保存在同一个树中3、使用覆盖索引扫描的查询可以直接使用页节点中的主键值 缺点 1、聚簇数据最大限度地提高了IO密集型应用的性能如果数据全部在内存那么聚簇索引就没有什么优势2、插入速度严重依赖于插入顺序按照主键的顺序插入是最快的方式3、更新聚簇索引列的代价很高因为会强制将每个被更新的行移动到新的位置4、基于聚簇索引的表在插入新行或者主键被更新导致需要移动行的时候可能面临页分裂的问题5、聚簇索引可能导致全表扫描变慢尤其是行比较稀疏或者由于页分裂导致数据存储不连续的时候 非聚簇索引 数据文件跟索引文件分开存放
覆盖索引 基本介绍 1、如果一个索引包含所有需要查询的字段的值我们称之为覆盖索引2、不是所有类型的索引都可以称为覆盖索引覆盖索引必须要存储索引列的值3、不同的存储实现覆盖索引的方式不同不是所有的引擎都支持覆盖索引memory不支持覆盖索引 优势 1、索引条目通常远小于数据行大小如果只需要读取索引那么mysql就会极大的较少数据访问量2、因为索引是按照列值顺序存储的所以对于IO密集型的范围查询会比随机从磁盘读取每一行数据的IO要少的多3、一些存储引擎如MYISAM在内存中只缓存索引数据则依赖于操作系统来缓存因此要访问数据需要一次系统调用这可能会导致严重的性能问题4、由于INNODB的聚簇索引覆盖索引对INNODB表特别有用 案例演示 覆盖索引 1、当发起一个被索引覆盖的查询时在explain的extra列可以看到using index的信息此时就使用了覆盖索引
mysql explain select store_id,film_id from inventory\G *************************** 1. row **************************id: 1select_type: SIMPLEtable: inventorypartitions: NULLtype: index possible_keys: NULLkey: idx_store_id_film_idkey_len: 3ref: NULLrows: 4581filtered: 100.00Extra: Using index 1 row in set, 1 warning (0.01 sec) 2、在大多数存储引擎中覆盖索引只能覆盖那些只访问索引中部分列的查询。不过可以进一步的进行优化可以使用innodb的二级索引来覆盖查询。 例如actor使用innodb存储引擎并在last_name字段又二级索引虽然该索引的列不包括主键actor_id但也能够用于对actor_id做覆盖查询 mysql explain select actor_id,last_name from actor where last_nameHOPPER\G ************************** 1. row **************************id: 1select_type: SIMPLEtable: actorpartitions: NULLtype: ref possible_keys: idx_actor_last_namekey: idx_actor_last_namekey_len: 137ref: constrows: 2filtered: 100.00Extra: Using index 1 row in set, 1 warning (0.00 sec) 优化小细节 当使用索引列进行查询的时候尽量不要使用表达式把计算放到业务层而不是数据库层select actor_id from actor where actor_id4;select actor_id from actor where actor_id15;尽量使用主键查询而不是其他索引因此主键查询不会触发回表查询使用前缀索引 有时候需要索引很长的字符串这会让索引变的大且慢通常情况下可以使用某个列开始的部分字符串这样大大的节约索引空间从而提高索引效率但这会降低索引的选择性索引的选择性是指不重复的索引值和数据表记录总数的比值范围从1/#T到1之间。索引的选择性越高则查询效率越高因为选择性更高的索引可以让mysql在查找的时候过滤掉更多的行。
​ 一般情况下某个列前缀的选择性也是足够高的足以满足查询的性能但是对应BLOB,TEXT,VARCHAR类型的列必须要使用前缀索引因为mysql不允许索引这些列的完整长度使用该方法的诀窍在于要选择足够长的前缀以保证较高的选择性通过又不能太长。 案例演示 –创建数据表 create table citydemo(city varchar(50) not null); insert into citydemo(city) select city from city;–重复执行5次下面的sql语句 insert into citydemo(city) select city from citydemo;–更新城市表的名称 update citydemo set city(select city from city order by rand() limit 1);–查找最常见的城市列表发现每个值都出现45-65次 select count() as cnt,city from citydemo group by city order by cnt desc limit 10;–查找最频繁出现的城市前缀先从3个前缀字母开始发现比原来出现的次数更多可以分别截取多个字符查看城市出现的次数 select count() as cnt,left(city,3) as pref from citydemo group by pref order by cnt desc limit 10; select count() as cnt,left(city,7) as pref from citydemo group by pref order by cnt desc limit 10; –此时前缀的选择性接近于完整列的选择性–还可以通过另外一种方式来计算完整列的选择性可以看到当前缀长度到达7之后再增加前缀长度选择性提升的幅度已经很小了 select count(distinct left(city,3))/count() as sel3, count(distinct left(city,4))/count() as sel4, count(distinct left(city,5))/count() as sel5, count(distinct left(city,6))/count() as sel6, count(distinct left(city,7))/count() as sel7, count(distinct left(city,8))/count() as sel8 from citydemo;–计算完成之后可以创建前缀索引 alter table citydemo add key(city(7));–注意前缀索引是一种能使索引更小更快的有效方法但是也包含缺点mysql无法使用前缀索引做order by 和 group by。 使用索引扫描来排序 mysql有两种方式可以生成有序的结果通过排序操作或者按索引顺序扫描如果explain出来的type列的值为index,则说明mysql使用了索引扫描来做排序 ​ 扫描索引本身是很快的因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列那么就不得不每扫描一条索引记录就得回表查询一次对应的行这基本都是随机IO因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢 ​ mysql可以使用同一个索引即满足排序又用于查找行如果可能的话设计索引时应该尽可能地同时满足这两种任务。 ​ 只有当索引的列顺序和order by子句的顺序完全一致并且所有列的排序方式都一样时mysql才能够使用索引来对结果进行排序如果查询需要关联多张表则只有当orderby子句引用的字段全部为第一张表时才能使用索引做排序。order by子句和查找型查询的限制是一样的需要满足索引的最左前缀的要求否则mysql都需要执行顺序操作而无法利用索引排序 –sakila数据库中rental表在rental_date,inventory_id,customer_id上有rental_date的索引 –使用rental_date索引为下面的查询做排序 explain select rental_id,staff_id from rental where rental_date2005-05-25 order by inventory_id,customer_id\G *************************** 1. row **************************id: 1select_type: SIMPLEtable: rentalpartitions: NULLtype: ref possible_keys: rental_datekey: rental_datekey_len: 5ref: constrows: 1filtered: 100.00Extra: Using index condition 1 row in set, 1 warning (0.00 sec) –order by子句不满足索引的最左前缀的要求也可以用于查询排序这是因为所以你的第一列被指定为一个常数–该查询为索引的第一列提供了常量条件而使用第二列进行排序将两个列组合在一起就形成了索引的最左前缀 explain select rental_id,staff_id from rental where rental_date2005-05-25 order by inventory_id desc\G ************************** 1. row **************************id: 1select_type: SIMPLEtable: rentalpartitions: NULLtype: ref possible_keys: rental_datekey: rental_datekey_len: 5ref: constrows: 1filtered: 100.00Extra: Using where 1 row in set, 1 warning (0.00 sec)–下面的查询不会利用索引 explain select rental_id,staff_id from rental where rental_date2005-05-25 order by rental_date,inventory_id\G ************************** 1. row **************************id: 1select_type: SIMPLEtable: rentalpartitions: NULLtype: ALL possible_keys: rental_datekey: NULLkey_len: NULLref: NULLrows: 16005filtered: 50.00Extra: Using where; Using filesort–该查询使用了两中不同的排序方向但是索引列都是正序排序的 explain select rental_id,staff_id from rental where rental_date2005-05-25 order by inventory_id desc,customer_id asc\G ************************** 1. row **************************id: 1select_type: SIMPLEtable: rentalpartitions: NULLtype: ALL possible_keys: rental_datekey: NULLkey_len: NULLref: NULLrows: 16005filtered: 50.00Extra: Using where; Using filesort 1 row in set, 1 warning (0.00 sec)–该查询中引用了一个不再索引中的列 explain select rental_id,staff_id from rental where rental_date2005-05-25 order by inventory_id,staff_id\G ************************** 1. row ***************************id: 1select_type: SIMPLEtable: rentalpartitions: NULLtype: ALL possible_keys: rental_datekey: NULLkey_len: NULLref: NULLrows: 16005filtered: 50.00Extra: Using where; Using filesort 1 row in set, 1 warning (0.00 sec) union all,in,or都能够使用索引但是推荐使用in explain select * from actor where actor_id 1 union all select * from actor where actor_id 2;explain select * from actor where actor_id in (1,2);explain select * from actor where actor_id 1 or actor_id 2; 范围列可以用到索引 范围条件是、范围列可以用到索引但是范围列后面的列无法用到索引索引最多用于一个范围列 强制类型转换会全表扫描 explain select * from user where phone13800001234; –不会触发索引explain select * from user where phone‘13800001234’; –触发索引 更新十分频繁数据区分度不高的字段上不宜建立索引 更新会变更B树更新频繁的字段建议索引会大大降低数据库性能类似于性别这类区分不大的属性建立索引是没有意义的不能有效的过滤数据一般区分度在80%以上的时候就可以建立索引区分度可以使用 count(distinct(列名))/count(*) 来计算 创建索引的列不允许为null可能会得到不符合预期的结果当需要进行表连接的时候最好不要超过三张表因为需要join的字段数据类型必须一致能使用limit的时候尽量使用limit单表索引建议控制在5个以内单索引字段数不允许超过5个组合索引创建索引的时候应该避免以下错误概念 索引越多越好过早优化在不了解系统的情况下进行优化