最新有限公司网站,网站后台管理界面模板,WordPress可以上传附件,网站建设技术咨询协议1、索引是对DB优化最有效的方式 varchar(10)定义的是字符的个数#xff0c;如果是utf-8的话#xff0c;最大是3X10个字节 二、索引类型 1、MySql的索引是在存储引擎层实现的#xff0c;各个存储引擎的的索引方式也是不同的 2、B-Tree索引 MyISAM索引通过数据的物理位置引用被… 1、索引是对DB优化最有效的方式 varchar(10)定义的是字符的个数如果是utf-8的话最大是3X10个字节 二、索引类型 1、MySql的索引是在存储引擎层实现的各个存储引擎的的索引方式也是不同的 2、B-Tree索引 MyISAM索引通过数据的物理位置引用被索引的行数据存储位置变化时需要更新索引INNODB则根据主键引用被索引的行没有主键默则根据默认的策略生成主键。 B树是平衡树 聚簇索引和非聚簇索引的分析http://www.cnblogs.com/Arlen/articles/1605626.html 主键索引是聚簇索引二级索引是非聚簇索引。 INNODB索引的使用 1、全值匹配 2、匹配最左前缀 3、匹配列前缀 4、匹配范围值 5、精确匹配某一列并范围匹配另一列 6、索引覆盖 7、因为索引树的节点是有序的所以除了按值查找之外索引还能用于order by和group by操作。 一般如果B树可以按照某种方式找到值那么也能按照这种方式排序。 限制 1、必须匹配最左前缀 2、不能跳过索引中的列 3、如果某个列使用了范围查询则其右边的所有列都有不能使用索引了。 4、order by 要么都是升序要么都是降序 5、如果是多个表只有全部是第一个表才能使用 3、Hash 索引 在MySql里只有Memory引擎显式的支持hash索引。 哈希索引自身只存储对应的哈希值和行索引而不存储字段值所以不能使用索引的值来避免读取行。 哈希不支持部分索引列的查找只支持全部匹配 哈希索引只支持列的全值匹配只支持等值比较查询。 INNODB里面内置了自适应hash索引 4、全文索引 5、空间数据索引 6、在INNODB里面模拟hash索引来优化 如url列的内容很大创建的索引占用的磁盘块数也很大。如果按照url列来等值查找数据where urlwww.yunzhu.com则下面的优化方法较好 加一列hash_url该列存储的是url的MD5值。同时对hash_url创建索引然后这样查找where urlwww.yunzhu.com and hash_urlMD5(www.yunzhu.com) 则mysql会优先走hash_url索引找到对应的行然后比较url是否相同。 原理hash_url索引占的磁盘块数很小而且是是整数比较比字符串比较快很多所以会很快。 三、高性能索引策略 1、不是独立的列索引列是表达式或者函数的入参 2、前缀索引原理给索引瘦身 有时候某个列值是text或者varchar类型而且值很大。这个时候如果直接对该列创建索引磁盘块数将会很大可以采取前缀索引进行优化。 前缀索引只对某个列的前几位做索引而不是所有位。无法使用前缀索引进行order by和group by 创建前缀索引语法alter table mytable add key city(n) 则创建的索引对对city的前n个字符创建索引。 如何选择n 实验找出选择性较高的n。 不能进行索引排序 3、多列索引 问题index_merge 在每个列上都创建索引并不能提高mysql的查询性能。 当出现服务器对多个索引做相交操作的时候多个and条件通常意味着需要一个包含所有相关列的索引而不是多个独立的单列索引。 当服务器出现对多个索引做相交操作的时候多个or条件需要消耗大量的CPU资源去重排序等操作上。 4、选择合适的索引列顺序下面的两种条件都需要综合考虑 1、在不考虑排序和分组的时候将选择性较高的列放在前面通常是很好的。 考虑下面的一种极端的情况 虽然列A的选择性很高但是A的列有个值B重复率很大如果where AB来查询的话则效率会很低。此时应该由上层的程序来控制不走这个索引。 极端的情况下B值的重复lv很高高到和不走索引是一样的。 2、需要根据运行频率最高的查询来调整索引列的顺序。 三、聚簇索引 不是一种索引方式而是一种存储方式代表主键和数据紧凑的存储在一起。思考下聚簇索引的优点和缺点 优点1、覆盖索引查询可以直接使用主键值 2、因为索引和数据存储在同一个树上因此查找快。 3、聚簇 缺点1、主键需要递增否则会造成页分裂和碎片等。 MySql的数据文件就是索引文件非叶子节点存储的是主键叶子节点存储了全部的数据。 非聚簇索引也叫辅助索引。查询的时候一般是先根据辅助索引查询到主键之后再根据聚簇索引查询到所有的数据行。 如果没有定义主键INNODB会选择一个唯一的非空索引代替。如果没有这样的索引INNODB会隐式的定义一个主键作为聚簇索引。 聚簇索引的插入速度严重依赖插入顺序。 更新聚簇索引的成本好高因为需要将索引移到新的位置。 基于聚簇索引的表在插入新行或者主键被更新导致需要移动行的时候可能导致列分裂的问题。 使用optimize table命令来重建表并进行优化页的填充。 INNODB应该尽可能的按照主键的顺序插入数据并且尽可能的使用单调增加的聚簇值来插入新行避免更新主键值。 否则需要移动聚簇索引同时造成页分裂造成碎 片。 四、索引覆盖 如果二级索引能够覆盖查询则可以避免对主键索引的二次查询 在发起一个覆盖查询的时候explain的extrea列可以看到using index的信息 很多的查询语句可以通过部分走索引覆盖而进行优化。 可以通过扩展索引来实现索引覆盖从而进行优化 五、使用索引排序 如果explain的type值为index则说明mysql可能使用了索引扫描来排序。 MySql可以使用同一个索引既满足排序也能用于查找行。 只有当索引的列顺序和order by自居的顺序完全一致并且所有的列的排序方向都一致时MySql才能使用索引来对结果进行排序。 如果查询需要关联多个表只有当order by子句引用的字段全部为第一个表时优化执行器选择执行的第一个表不是sql的第一个select表才能使用索引做排序。 order by子句和查找型查询的限制是一样的需要满足索引的最左前缀要求否则mysql都需要执行排序操作而无法利用索引排序。 六、 kengyu索引 有时候坑与索引能在一定的程度上优化查询。 七、连接查询 Inner Join Natural Join
Left Outer Join
Right Outer Join
Full Outer Join
Cross Join 八、查询性能优化 1、 主要是两个方面进行性能优化 1、客户端到服务器端之间的性能优化减少数据包的传输等 1、客户端是否请求了过多的行和列列少的话可能会走覆盖索引同时能减小数据包的大小 2、客户端可以先查缓存减少查db的次数 2、mysql服务器端和存储引擎端的优化 主要是根据3个指标 1、响应时间请求锁时间排队时间IO时间 2、扫描的行数和返回的行数一般这个比例是10:1甚至更大 2、where条件的利用好坏从好到坏排序 1、where用于索引 2、服务器层做过滤 3、切分查询讲一个大的查询切分成小的 为什么切分查询 1、现在的网络带宽比较大所以将一个查询切分成多个是可行的 2、一次执行大量数据的删除和查询会一次占用很多的资源和锁给DB造成很大的压力。这样做可以将DB的压力分散到不同的时间。 3、将一个大的查询如连接查询)切分成小的查询能更好的利用缓存和操作缓存。 4、在应用层做关联可以更好的对 数据库进行切分更容易做到高性能和可扩展。 5、在应用层做关联查询在一个事务中可以把某个请求结果存储起来然后再找个事务的执行过程中就不断的重复去DB查了这样就减少了请求DB的次数。 如如果直接请求mysql执行一个个大sql返回的结果是没有子查询的结果的如果下次需要子查询的结果还需要如DB查。 4、客户端/服务器端的通信协议 1、半双工这导致两方对通讯过程不能有很好的控制能力在发送出去报文之后就只能干等着了。 2、客户端用一个单独的数据包将查询发送给服务器。max_allowed_packet就规定了这个数据包的大小客户端发送完请求之后只能等着了。 3、服务器端的响应由多个数据包组成客户端只有接收到完整的全部结果之后服务器端才会释放资源。 4、客户端默认是先缓存全部的结果集使得mysql服务器端能尽快的释放掉资源。客户端可以通过参数决定自己是不是缓存全部的结果集。jdbc也有api能设置。 5、服务器得到第一条数据的时候就可以返回数据了这样客户端也可以同时处理数据了。服务器不是先查询到所有的数据然后才返回数据是查询到第一条有效数据就返回数 据这样可以避免即使服务器端需要返回大量的数据也不会占用大量的内存。mysql会针对每一行使用通讯协议进行包装然后写到socket当然tcp可能会把多行以一个批次传输。问题客户端可以指定是否缓存服务器端的数据那么这个时候服务器端返回数据是不是就 受到限制了 SQL_BUFFER_RESULT forces the result to be put into a temporary table. This helps MySQL free the table locks early and helps in cases where it takes a long time to send the result set to the client. This option can be used only for top-level SELECT statements, not for subqueries or following UNION. 这样能尽快的释放锁但是会占用mysql的大量内存和资源 九、其他 1、mysql在生成执行计划的时候5.6版本之前如果一个语句里面含有子查询时子查询是需要执行的但是5.6解除了这个限制。 2、当扫描大量的行数时可以采取下面的措施p201 1、使用索引覆盖 2、使用汇总表 3、重写查询 3、查看各个连接的状态 show full processlist 在command列能看到各个查询现在的状态 1、sleep 2、query 3、locked 4、analysing and statistics 5、copying to tmp table[on disk] group by,sortunion的时候会使用临时文件 6、sorting result 7、sending data 十、explain和explain extend explain extand能额外的查到mysql执行优化器优化之后的sql语句 1、type访问类型 all 聚簇索引去扫描整个表limit时不会扫描整个表此时磁盘是顺序访问的。 1、只有聚簇索引 2、不能走索引覆盖 index 索引扫描整个表使用索引排序的时候会走 1、extra列里面有using index时只是磁盘顺序读取所有的索引并不会再根据聚簇索引去磁盘随机读取其他列的数据 2、extra列里面没有using index时此时还需要根据聚簇索引去随机读取其他列比较好性能。 range 使用索引范围查询 INOR也是显示的range但是和正常的这种范围查询有区别 ref非唯一性索引或者使用索引前缀查询 索引等值查询但是可能查询多多行 eq_ref唯一性索引例如主键id 索引等值查询但是只可能查询到一条语句。 const,system 不用执行能根据SQL和mysql统计信息就能得到结果如select id from XXX where id1; NULL 不用执行就能得到结果 分析all和index all和index都是扫表。当是索引覆盖查询或者能利用索引排序的时候才会走 2、rows 大概显示会扫描到多少行如果索引覆盖查询的话一个索引也算一行所以这里rows指的不是扫描行同时得到所有的列 rows也不能表明limit如select * from XX limit 1,实际上mysql只会查询扫描一列就结束。 3、key_len所使用索引的最大程度 索引的长度能根据这个值确定最终sql在联合索引里面到底使用了前几个列走索引 4、possible_keys 可能走的索引具体的例子见案例分析的第6个 5、key 优化器从possible_keys里最终选择的索引根据IO次数选择合适的具体的例子见案例分析的第6个 6、id 根据sql里面的id的顺序由1递增排列 7、explain的执行结果顺序 explain的执行结果里面的id可能不是递增的explain显示的列的顺序就是mysql的执行顺序。 8、select_type 1、simple 2、SUBQUERYDEPENDENT_SUBQUERY select select XX from XX from XX 括号里面的是SUBQUERY 3、UNION 4、DERIVED from (select XXX from XXX) 括号里面的是DERIVED 5、PRIMARY 最外层循环 9、table 1、DERIVED 2、真实表 3、UNION之后的表 UNION 1,2 10、extra using index 说明只使用该索引不用再根据索引获得的id去查询聚簇索引获得数据也就是索引覆盖 ,using where 不带where的也可能出现using where说明查询可收益于不同的索引 using tempory临时表 using filesore可能是在内存或者磁盘进行排序 十一、案例分析 1、status命令 能查看mysql的版本号和服务器的运行快照信息以及mysql客户端的信息 2、show create table; 查看建表语句 3、索引覆盖案例 表结构 CREATE TABLE test (id int(11) NOT NULL,name varchar(10) DEFAULT NULL,PRIMARY KEY (id),KEY name (name) ) ENGINEInnoDB DEFAULT CHARSETutf8 select * from test;执行结果如下可以看出执行优化器选择了索引覆盖查询。注意任何的索引默认主键都是索引的一部分 当sql没有指定order by的时候查询出的结果是无序的。 select * from test order by id;执行结果如下可以看出优化器选择了使用索引排序 4、聚簇索引和不是聚簇索引的选择 select count(*) from test; 可以看出count(*)也是扫描整个索引 count(*)因为只用统计数据肯定走索引覆盖聚簇索引或者非聚簇索引。 selct count(*)和select a,b基本类似但是count(*)一般可以走索引覆盖,extra 肯定包含using index。 CREATE TABLE AA ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 主键,gmt_create datetime NOT NULL COMMENT 创建时间,gmt_modified datetime NOT NULL COMMENT 修改时间,appkey varchar(32) NOT NULL COMMENT ,std_api_id varchar(64) NOT NULL COMMENT ,cfg_h_group varchar(128) NOT NULL COMMENT ,cfg_key varchar(128) NOT NULL COMMENT ,cfg_value varchar(300) NOT NULL COMMENT 配置项value,creator varchar(32) NOT NULL COMMENT 创建人,modifier varchar(32) NOT NULL COMMENT 修改人,cfg_v_group varchar(32) NOT NULL COMMENT ,PRIMARY KEY (id),KEY index_appkey_std_api_id (appkey,std_api_id),KEY index_appkey_h_group (appkey,cfg_h_group)
) ENGINEInnoDB AUTO_INCREMENT490004 DEFAULT CHARSETutf8 COMMENT explain select count(*) from AA 该表存在聚簇索引还有两个联合索引因为肯定要全部扫描所有的行但不一定是所有的列优化执行器会比较怎么扫描读的磁盘块数IO数更少。 1、聚簇索引叶子部分存储的是全部的列所以pass。所以一般count(*)都不会走聚簇索引扫描 2、联合索引 选择联合索引数据量较小的所以最后优化执行器选择了下面的执行计划 1 id 1 2 select_type SIMPLE 3 table AA 4 type index 5 possible_keys 6 key index_appkey_std_api_id 7 key_len 292 8 ref 9 rows 61181 10 Extra Using index 6、索引的选择 explain select count(*) from AA where appkeyaa; 从执行计划可以看出优化执行器锁定两个索引从possible_keys上看出但是两个联合索引都只能选择使用第一列所以在两个联合索引中选择索引占的数据较小的那个索 引最终生成了下面的执行计划 1 id 1 2 select_type SIMPLE 3 table AA 4 type ref 5 possible_keys index_appkey_std_api_id,index_appkey_h_group 6 key index_appkey_std_api_id 7 key_len 98 8 ref const 9 rows 1 10 Extra Using where; Using index 7、 select (select XX from XX) from XX类型分析 返回的行数 等于select count(*) from XX; select (select id from s) from test; 本质上是个循环嵌套查询 8、select常量 返回多少行数 select count(*) from XX; 从执行计划可以看出这种sql肯定走索引覆盖using index因为查的是常量。分析这种sql时和普通的sql一样对待只是查的列比较特殊肯定走索引覆盖 9、连接查询只支持左外连接右外连接内连接等值连接 虽然没有 join 关键字但是下面的查询也是连接查询。 下面的查询没有where语句实际是得到一个笛卡尔乘积执行引擎在执行的时候是递归执行随意所以外层循环的数量尽可能少所以查执行table s然后再执行table test。table S只有主键所以type是all。table test可以进行索引覆盖所以type是index。 10、虽然select可能是全部数据或者部分数据但是联合索引能做到覆盖查询而且可以使用索引排序。 11、IN查询 注意using where 12、derived深入分析 1、生成derived表的查询是可以走索引的 2、primary对derived的查询时不能走索引的只能走where。 十二、具体的优化 1、limit分页 limit 10000,10此时mysql需要读取很多的行去获得偏移量为10000的数据但是之前读取过的10000个数据是无用的。 方案1 select film_id,description from sakila.film order by title limit 50,5 可以看出需要全表扫描然后再根据title排序然后在取偏移量为50的5条数据。性能弱爆了。 优化成select film.film_id,film.dscription from sakila.film inner join (select film_id from sakila.film order by title limit 50,5)as lim using(fim_id); mysql虽然查的前50个没有用但是因为using index只是浪费了50次查一个索引没有查到一个索引得到主键再去查所有的数据。这里实际上用了延迟技术。 方案2 想办法使得limit A,B中的A变小 2、UNION UNION肯定会使用临时表存放下来然后再发送数据给客户端而实际上是可以直接发送数据给客户端的。 一般尽量使用UNION ALL否则mysql需要对UNION的结果排除重复的 3、最大值最小值 优化方法因为最大值和最小值只可能是一个值所以可以使用limit1来进行优化。 极端的情况下会进行扫表然后才得到一个最大或者最小值通过改写SQL能改变这种情况。 查索引上的最大值和最小值时可以直接得到。 4、优化count查询 1、count(A),其中A可以是表达式可以是列也可以是*。*代表查询全部的行数列代表查询非NULL值的个数表达式代表有值的个数。 2、MYISAM在查询不带where条件的count(*)时是const类型但是如果带where或者count(A)不能转换成count(*)和其他的引擎的执行一样。 在MYISAMselect count(*) from a where id5;则执行时会扫描很多的行数。可以优化成 select count(*) from a - select count(*) from a where id5; 3、使用近似值汇总表等 5、优化连接查询 1、A和B连接时只需要在一个表上建立索引就可以了。 2、确保group by 和order by 中的表达式只涉及到一个表中的列只有这样mysql才能使用索引优化这个过程 6、优化group by和distinct 当没有办法使用索引的时候mysql会使用临时表或者文件排序来分组。 使用分组的时候select的列尽量是和group by 有关的字段。 十三、其他 1、建议执行优化器 SE INDEX在你查询语句中表名的后面添加 USE INDEX 来提供你希望 MySQ 去参考的索引列表就可以让 MySQL 不再考虑其他可用的索引。Eg:SELECT * FROM mytable USE INDEX (mod_time, name) ... IGNORE INDEX如果你只是单纯的想让 MySQL 忽略一个或者多个索引可以使用 IGNORE INDEX 作为 Hint。Eg:SELECT * FROM mytale IGNORE INDEX (priority) ... FORCE INDEX为强制 MySQL 使用一个特定的索引可在查询中使用 FORCE INDEX 作为 Hint。Eg:SELECT * FROM mytable FORCE INDEX (mod_time) ... 转载于:https://www.cnblogs.com/YDDMAX/p/5371329.html
