MySQL-性能优化-索引和查询优化

朱雀 2022-05-23 08:57 416阅读 0赞

MySQL-性能优化-索引和查询优化

要知道为什么使用索引，要知道如何去使用好索引，使自己的查询达到最优性能，需要先了解索引的数据结构和磁盘的存取原理

参考博客：[MySQL索引背后的数据结构及算法原理][MySQL]

如上这篇博客写的挺好，我就不再造轮子了，对如上博客，我总结如下几个重要的要点：

1. 不使用顺序查找，因为顺序查找比较慢，通过特定数据结构的特点来提升查询速度，这种数据结构就是可以理解成索引。

![20180606084935963][]

2. 索引一般以文件形式存储在磁盘上，索引检索需要磁盘I/O操作，为了尽量减少磁盘I/O。磁盘往往不是严格按需读取，而是每次都会预读，而且主存和磁盘以页为单位交换数据，所以在读取的数据不在主存中时，会从磁盘中读取一批数据（页）到主存中。

![20180606084943971][]

3. 不管在哪种程序优化上，要想快速挺高性能，直接将常用的、少变更的数据直接读取到内存中，使用的时候就直接在内存上读取，而不去磁盘上读取，减少I/O操作，这样就能使程序快上10倍以上。但由于内存容量的限制，也不可能将所有的数据都放内存中。

![20180606084953181][]

MySQL索引分类

普通索引：最基本的索引，没有任何限制。  
唯一索引：与”普通索引”类似，不同的就是：索引列的值必须唯一，但允许有空值。  
主键索引：它是一种特殊的唯一索引，不允许有空值。  
全文索引：仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时好空间。  
组合索引：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。

覆盖索引(Covering Indexes)  
就是直接走的索引，直接在内存中就拿到值，不需要查询数据库。  
如分页就要走覆盖索引，因为性能比较高。

聚簇索引(Clustered Indexes)，主键就是聚集索引  
聚簇索引保证关键字的值相近的元组存储的物理位置也相同（所以字符串类型不宜建  
立聚簇索引，特别是随机字符串，会使得系统进行大量的移动操作），且一个表只能  
有一个聚簇索引。因为由存储引擎实现索引，所以，并不是所有的引擎都支持聚簇索  
引。目前，只有solidDB和InnoDB支持。

非聚簇索引  
二级索引叶子节点保存的不是指行的物理位置的指针，而是行的主键值。这意味着通  
过二级索引查找行。  
InnoDB对主键建立聚簇索引。如果你不指定主键，InnoDB会用一个具有唯一且非空值  
的索引来代替。如果不存在这样的索引，InnoDB会定义一个隐藏的主键，然后对其建  
立聚簇索引。一般来说，DBMS都会以聚簇索引的形式来存储实际的数据，它是其它二  
级索引的基础。

优化要注意的一些事（重点）

1. 索引其实就是一种归类方式，当某一个字段属性都不能归类，建立索引后是没什么效果的，或归类就二种（0和1），且各自都数据对半分，建立索引后的效果也不怎么强。

2. 主键的索引是不一样的，要区别理解。

3. 当时间存储为时间戳保存的可以建立前缀索引。

4. 在什么是字段上建立索引，需要根据查询条件而定，不要一上来就建立索引，浪费内存还有可能用不到。

5. 大字段（blob）不要建立索引，查询也不会走索引。

6. 常用建立索引的地方：  
1）主键的聚集索引  
2）外键索引  
3）类别只有0和1就不要建索引了，没有意义，对性能没有提升，还影响写入性能  
4）用模糊其实是可以走前缀索引

7. 唯一索引一定要小心使用，它带有唯一约束，由于前期需求不明等情况下，可能造成我们对于唯一列的误判。

8. 由于我们建立索引并想让索引能达到最高性能，这个时候我们应当充分考虑该列是否适合建立索引，可以根据列的区分度来判断，区分度太低的情况下可以不考虑建立索引，区分度越高效率越高。

SELECT COUNT(DISTINCT 列_xx)/COUNT(*) FROM 表

9. 写入比较频繁的时候，不能开启MySQL的查询缓存，因为在每一次写入的时候不光要写入磁盘还的更新缓存中的数据。

10. 建索引的目的：

1）加快查询速度，使用索引后查询有迹可循。  
2）减少I/O操作，通过索引的路径来检索数据，不是在磁盘中随机检索。  
3）消除磁盘排序，索引是排序的，走完索引就排序完成。

11. 其实建索引的原理就是将磁盘I/O操作的最小化，不在磁盘中排序，而是在内存中排好序，通过排序的规则去指定磁盘读取就行，也不需要在磁盘上随机读取。

12. 由于磁盘整理磁盘碎片，所有有的时候我们也可以通过建立聚集索引来减少这一类的问题。

13. 当一个表中有100万数据，而经常用到的数据只有40万或40万以下，是不用考虑建立索引的，没什么性能提升。

14. 什么时候不适合建立索引：

1）频繁更新的字段不适合建立索引  
2）where条件中用不到的字段不适合建立索引，都用不到建立索引没有意义还浪费空间  
3）表数据可以确定比较少的不需要建索引  
4）数据重复且发布比较均匀的的字段不适合建索引（唯一性太差的字段不适合建立索引），例如性别，真假值  
5）参与列计算的列不适合建索引，如：

select * from table where amount+100>1000，-- 这样是不走索引的，可以改造为：select * from table where amount>1000-100。

15. 使用count统计数据量的时候建议使用count(\*)而不是count(列)，因为count(\*)MySQL是做了优化的。

16. 二次SQL查询区别不大的时候，不能按照二次执行的时间来判断优化结果，没准第一次查询后又保存缓存数据，导致第二次查询速度比第二次快，很多时候我们看到的都是假象。

17. 什么时候开MySQL的查询缓存，交易系统（写多、读少）、SQL优化测试，建议关闭查询缓存，论坛文章类系统（写少、读多），建议开启查询缓存。

18. Explain 执行计划只能解释SELECT操作。

19. 查询优化可以考虑让查询走索引，走索引能提升查询速度，索引覆盖是最快的，如下就是让分页走覆盖索引提高查询速度。

Select * from fentrust e Inner join (select fid from fentrust limit 4100000, 10) a on a.fid = e.fid

20. 子查询比join快，虽然规律不绝对，但对大表多数有效

21. 复杂SQL语句优化的思路：

1）首先考虑在一个表中能不能取到有关的信息，尽量少关联表  
2）关联条件争取都走主键或外键查询条件，能走到对应的索引  
3）争取在满足业务上走小集合数据查找  
4）INNER JOIN 和子查询哪个更快，场景不一致速度也不同

22. where条件多条件一定要按照小结果集排大结果集前面

23. 尽量避免大事务操作，提高系统并发能力，有时无法避免，改用定时器延迟处理。

24. 什么情况不走索引：

SELECT ` famount ` FROM ` fentrust ` WHERE ` famount `+10=30;-- 不会使用索引,因为所有索引列参与了计算 SELECT `famount` FROM `fentrust` WHERE LEFT(`fcreateTime`,4) <1990; -- 不会使用索引,因为使用了函数运算,原理与上面相同 SELECT * FROM ` fuser` WHERE `floginname` LIKE‘138%' -- 走索引 SELECT * FROM ` fuser ` WHERE ` floginname ` LIKE "%7488%" -- 不走索引 -- 正则表达式不使用索引,这应该很好理解,所以为什么在SQL中很难看到regexp关键字的原因 -- 字符串与数字比较不使用索引; EXPLAIN SELECT * FROM `a` WHERE `a`=1 -- 不走索引 select * from fuser where floginname='xxx' or femail='xx' or fstatus=1 --如果条件中有or,即使其中有条件带索引也不会使用。换言之,就是要求使用的所有字段,都必须建立索引, 我们建议大家尽量避免使用or 关键字

25. 如果MySQL估计使用全表扫描要比使用索引快，则不使用索引。

26. 使用UNION ALL 替换OR多条件查询并集。

27. 在大数据表删除也是一个问题，避免删除过程数据库奔溃，可以考虑分配删除，一次删1000条，删完后等一会继续删除

delete from logs where log_date <= ’2012-11-01’ limit 1000

28. 大数据表优化：

1）建立汇总表  
2）建立流水表  
3）分库分表

29. 建立汇总表，首先不用考虑分库分表，使用定时器定时去汇总。

30. 分表，可以按水平或垂直切分。垂直分表其实就是将经常使用的数据和很少使用的数据进行垂直的切分，切分到不同的库，提高单库的数据容量，如：前3个月之前的交易记录就可以放另一个库中。

31. 建立流水表，数据冗余，有这个表记录流水变更就不用去写复杂SQL计算流水。

32. 分库，多数据库相同库结构，分发处理并发能力，但同时带来了数据同步问题，也可以使用分库做主备分离

32. SQL优化顺序：

1）尽量少作计算。  
2）尽量少 join。  
3）尽量少排序。  
4）尽量避免 select \*。  
5）尽量用 join 代替子查询。  
6）尽量少 or。  
7）尽量用 union all 代替 union。  
8）尽量早过滤。  
9）避免类型转换。  
10）优先优化高并发的 SQL，而不是执行频率低某些“大”SQL。  
11）从全局出发优化，而不是片面调整。  
12）尽可能对每一条运行在数据库中的SQL进行 Explain。

33. 如下是30条大数据表优化要点：

1）对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。  
2）应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num is null可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=0  
3）应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。  
4）应尽量避免在 where 子句中使用or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num=10 or num=20可以这样查询：select id from t where num=10 union all select id from t where num=20  
5）in 和 not in 也要慎用，否则会导致全表扫描，如：select id from t where num in(1,2,3) 对于连续的数值，能用 between 就不要用 in 了：select id from t where num between 1 and 3  
6）下面的查询也将导致全表扫描：select id from t where name like '李%'若要提高效率，可以考虑全文检索。  
7）如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：select id from t where num=@num可以改为强制查询使用索引：select id from t with(index(索引名)) where num=[@num][num]  
8）应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where num/2=100应改为:select id from t where num=100\*2  
9）应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where substring(name,1,3)='abc' ，name以abc开头的id 应改为: select id from t where name like 'abc%'  
10）不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。  
11）在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。  
12）不要写一些没有意义的查询，如需要生成一个空表结构：select col1,col2 into \#t from t where 1=0 这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样： create table \#t(...)  
13）很多时候用 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)  
14）并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。  
15）索引并不是越多越好，索引固然可 以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。  
16）应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。  
17）尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。  
18）尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。  
19）任何地方都不要使用 select \* from t ，用具体的字段列表代替“\*”，不要返回用不到的任何字段。  
20）尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。  
21）避免频繁创建和删除临时表，以减少系统表资源的消耗。  
22）临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。  
23）在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。  
24）如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。  
25）尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。  
26）使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。  
27）与临时表一样，游标并不是不可使 用。对小型数据集使用 FAST\_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时 间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。  
28）在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送DONE\_IN\_PROC 消息。  
29）尽量避免大事务操作，提高系统并发能力。  
30）尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

[MySQL]: http://blog.codinglabs.org/articles/theory-of-mysql-index.html
[20180606084935963]: /images/20220523/515638a2b1764961a02a03ad62db6a54.png
[20180606084943971]: /images/20220523/404d4311a01148b48abd12307f0701de.png
[20180606084953181]: /images/20220523/d4240ed3b8624c80baa680d245078211.png
[num]: https://my.oschina.net/u/1587116