sql优化
-
mysql的性能分析/优化工具:
- mysql show profiles :mysql 性能分析工具
- Explain :查看 SQL 的执行计划。计划内容:
- id :选择标识符
- select_type:表示查询的类型
- table:输出结果集的表
- partitions:匹配的分区
- type :表示表的连接类型
- 常用的类型包括:ALL、index、range、ref、eq_ref、const、system、NULL( 从左到右,性能从差到好 )
- ALL:Full Table Scan,Mysql将遍历全表已匹配找到的行
- index:Full Index Scan,Mysql将遍历索引树
- range:只检索给定范围的行,使用一个索引来选择行
- ref:表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列的值
- eq_ref:类似ref,区别是使用的是唯一索引,对于每一个索引值,表中只有一条记录匹配,简单来说,就是多表连接中使用 primary key或者 unique key作为关联条件
- const、system:当Mysql对查询某部分进行优化,并转换为一个常量时,使用这些类型访问。如将主键置于where列表中,Mysql就能将该查询转化为一个常量,system是const类型的一个特例,当查询的表只有一行的情况下使用system。
- NULL:Mysql在优化过程中分解语句,执行时甚至不用访问表和索引,例如从一个索引列里选取最小的值可以通过单独索引查询完成。
- possible_keys:表示查询时,可能使用的索引
- key :表示实际使用的索引
- key_len:索引字段的长度,在组合索引中判断索引被使用的情况尤为重要
- ref:列与索引的比较
- rows:扫描出的行数
- filtered:按表条件过滤的行百分比
- Extra:执行情况的描述与说明
-
关于索引的划分
-
主键索引:唯一、非空。如果表上有定义主键,则该主键索引就是聚簇索引;如果没有主键,Mysql 会选取第一个唯一索引且非空的列作为主键, InnoDB 使用它作为聚簇索引。如果没有这样的列, InnoDB 会自己产生一个这样的 id值 ,它有六个字节,而且还是隐藏的,使其作为聚簇索引。
-
二级索引:一般手动创建的索引都是辅助索引,因为辅助索引访问数据总是需要进行二次查找,所以也叫作 二级索引。非聚簇索引都是二级索引
-
普通索引:无法生效的情况见下方 针对索引优化 的内容。
alert table add key(coulmn) 或 add index(column)
-
前缀索引:即将字段名称的前几个字符作为索引
alert table add index(coulmn(5))
-
创建原则:需要测试前缀字符的个数和整列索引的个数进行对比,在个数和比之间选择合适的值。
select count(distinct aa)/count(*) from table /*值1*/
select count(distinct left(aa,index))/count(*) from table /*值2*/
当 index 值小,且值1 和值2 相差比较小时,取 index 的值作为前缀索引的字符数。
-
优点:大量节省索引空间,提高查询效率
-
无法进行 group by 和 order by 检索
-
-
哈希索引:通过 hash值 来维护索引,理论上在冲突较小(即重复数据少)的情况下,效率要比普通索引高很多。(使用频率很小,仅做了解)alert table add index using hash(coulmn)
- hash 索引只有 MyISAM 引擎支持
- 缺点:不支持范围查询;不支持索引完成排序;不支持联合索引的最左前缀匹配原则。
-
组合索引:
alert table add index(column1,column2)
-
最左匹配原则:
-
索引生效的范围,条件只能是
(1)、(1,2)
这种格式,如果是单独的 (2),则索引不生效 ,与查询条件的摆放顺序无关,因为 mysql 会根据索引的顺序自动优化条件顺序。 -
当组合索引中的一个列的索引失效,后面的索引将全部失效。如(a1,a2,a3),a2 like ‘%xxx%’,则a3也将失效。
-
索引做排序:只有当索引的列顺序和 order by 的列顺序完全一致且排序为全部升序或全部降序时,索引排序才能生效。
-
特殊情况:当索引的最左列在 where 条件中是一个 = 号条件,后面的排序生效,例:
index(aa,bb,cc) select * from table where aa='daaf' order by bb,cc
-
多表关联查询时,order by的字段全部来自一张表,索引排序才有可能生效。
-
-
-
-
覆盖索引(由于 Mysql 自带 sql 优化的能力,对于索引也是择优选择,因此一般 不会去考虑使用覆盖索引(除非你是数据库大牛) ,仅做了解):需要查询的所有字段及条件中的字段都可以从索引中找到。换句话说就是查询的列和条件被使用的索引覆盖了。
-
-
索引碎片:数据的添加或删除会产生索引碎片,导致查询变慢。下面给出索引碎片相关的两个命令:
-
show table status like '表名' /*查看 data_free > 0 则表示有碎片*/
-
optimize table '表名' /* 清理碎片 */
-
-
-
针对索引进行优化:
-
创建索引的原则: 有大量不重复数据。可以将该列不重复(distinct )数据的count与该列的所有数据的count进行比较,比例越高越有价值。查询频率越高,创建索引的价值越高。
-
1、 对查询进行优化,要考虑在 where 和 order by 所涉及的列上建立索引。
-
2、 查询语句尽可能简单,大语句拆成小语句,减少锁时间。(引出问题:如何拆分?)
-
3、 列表数据使用分页查询,每页数据量不要太大。
-
分页查询的思想是避免数据量大时扫描过多的记录。通常用 limit 关键字实现分页查询,limit 接受一个或两个参数,且参数必须是一个整数常量,第一个参数表示返回记录行的偏移量,第二个参数表示返回记录行的最大数目。
查询从第 1000000 行之后的 30 条记录:
select * from table order by id limit 1000000,30
直接实例 limit 获取第 1000000 条数据后的 30 条数据,会使引擎扫描前 1000000 条数据直到定位到第 1000000 条,随着 limit 偏移量的不断增大,速度会越来越慢。因此我们可以对其进行修改,先通过索引定位到第 1000000 条数据,然后在获取后 30条记录。
-
当查询数列比较多的时候,将浪费很多的内存空间。
-
对 limit 的优化,不是直接使用 limit,而是首先获取到 offset 的 id,然后直接使用 limit size 来获取数据。
-
子查询的分页方式
select * from table where id >= (select id from table order by id limit 1000000,1) limit 30
- JOIN 分页方式
select * from table as t1 join (select id from table order by id desc limit (($page-1)*$pagesize),1 as t2 where t1.id <= t2.id order by t1.id desc limit $pagesize
- join分页 和 子查询分页,效果上相差不多。
-
-
-
4、 尽量避免在 where 子句中对字段进行 is null 值判断,否则将导致引擎放弃使用索引而进行全表扫描。
select id from table where num is null
设置字段时,尽量不要使用 null 填充字段,因为 NULL 同样占用空间。如:char(100) 型,在字段建立时空间就固定了,不管是否插入值(NULL 也包含在内),都是占用 100 个字符的空间,但 varchar 字段中,null 不占用空间。
-
可以通过其他办法,使字段表示无值,如:在 num 上设置默认值 0,确保表中 num 列没有 null,在查询时使用如下语句查询:
select id from table where num = null
-
-
5、 尽量避免在 where 子句中使用 != 或 < > 操作符,否则将导致引擎放弃使用索引而进行全表查询
-
6、 尽量避免在 where 子句中使用 or 来连接条件,如果一个字段有索引,一个字段没有索引,将导致引擎放弃使用索引而进行全表扫描,如:
select id from table where num=5 or Name='miger'
可以修改语句,通过使用 union 或 union all(包含重复项) 来拼接两个查询结果:
select id from table where num=5 union all select id from table where Name='miger'
-
7、 关于 in 和 exists ,需要考虑要查询的两张表的大小关系。
- mysql 中对 in 的子查询支持很糟,尽量用左联或 exists 代替。
- 如果两张表的大小相当,则两种关键字的差别不大,
- 如果两张表中一张小表,一张大表,则子查询的表大的用 exists,子查询表小的用 in。
也可以尝试使用 exists 代替 in:
select num from a where num in(select num from b)
替换成:
select num from a where exists(select 1 from b where num=a.num)
这里可以注意到我使用的是 1 而不是 ,select 1 from table / select * from table 在用法上大同小异,都是查看表中是否有记录,select 1 from table 中的 1 是一常量,查到的所有行的值都是它。但从效率讲 1>,因为不用查字典表。
- 对于 not in 和 not exists :使用 not in 会对内外表都进行全表扫描,没有用到索引;而 not exists 的子查询依然可以用索引,所以使用 not exists 比使用 not in 要快。
-
8、 在使用关键字 like 进行模糊查询时,尽量避免第一个字符就是 % 的情況出现 如:‘%xxx%’、%xxx ,会使引擎放弃使用索引而进行全表扫描,如:
select id from table where name like '%世俗%'
只要第一个字符不是 % ,查询时都会使用索引。
-
9、 尽量避免在 where 子句中对字段使用函数或触发器。
-
10、 查询是否会产生临时表,临时表的数据量是不是很大。
- 查看方式:可以通过使用 Explain 分析执行计划,其中的字段 extra 为 Using Temporary ,因为临时表是没有索引的,如果临时表过大,不仅占内存而且查询慢。
- 可能会产生临时表的查询关键字:
- union 关键字;
- group by 和 order by 不是同一个字段;
- distinct 查询中加 order by 字段;
- 关联查询时, order by 的字段不是驱动表的字段(驱动表即 explain 查看汇总第一行的表);
- from 中的子查询。
-
11、 当无条件时,查询表中的精确数据,count(*) 比 count(column) 效率高
-
12、 关联查询需要 explain 查看查询顺序,通常情况下 mysql 会用数据最少的一张表作为第一顺序查询,此时把索引建在第二张表的查询比较高效
- order by 和 group by 要在一张表中才能更好的使用索引
-
13、 当遇到多张表关联查询时, mysql 的优化方式是把查询次数最少的那张表作为最外层查询的表,优先使用那张表的索引。我们也可以通过 hint 命令指定表的查询顺序,将 join 换成 STRAIGHT_JOIN 来指定表的执行顺序。
-
14、 当关联(join、left join等)多表查询多个数据时,可以考虑拆分成两个查询,一个查询作为原来查询的列和查询的条件,另一个查询做一个列的查询并做第一个查询的过滤条件。根据阿里的开发规范手册建立, 关联的表最多不要超过3张表 。
-
15、 尽量使用 union all 而非 union ,使用 union 时,将条件写在子查询中。
-
-
针对 表结构 设计优化
- 使用可以存下数据的最小数据类型。
- 尽量使用 tyntint、smallint、mediumint 作为整数类型而非 int。
- 尽量使用 not null 定义字段,因为 null 占用4字节空间,如数字可以默认为 0,字符串默认为 “”。
- 尽量少用 text 类型,非用不可时最好独立出一张表。
- 尽量使用 timestap ,而非 datetime。
- 单表不要有太多字段,建议在 20个字段以内。
-
需要关注的问题:
- 索引是建的越多越好么:
- 数据量小的表不需要建立索引,建立会增加额外的索引开销。
- 不常引用的列,不要建立索引,没多大意义
- 经常频繁更新的列不要建立索引,会影响插入或更新的效率,因为不仅要保存数据,还要保存索引文件
- 数据重复且分布均匀的字段,如:性别(只有男女,不适合建立索引)
- 数据变更需要维护索引,意味着索引越多,维护成本越高
- 建立索引会产生索引文件,占用存储空间。
- 索引是建的越多越好么:
参考链接
limit的用法和分页查询优化
https://www.cnblogs.com/yunfeifei/p/3850440.html 这个建议挑着看吧。