MySQL性能优化小技巧

1、了解了MySQL的执行过程，我们才知道如何进行sql优化。

1. 客户端发送一条查询语句到服务器；

2. 服务器先查询缓存，如果命中缓存，则立即返回存储在缓存中的数据；

3. 未命中缓存后，MySQL通过关键字将SQL语句进行解析，并生成一颗对应的解析树，MySQL解析器将使用MySQL语法进行验证和解析。例如，验证是否使用了错误的关键字，或者关键字的使用是否正确；

4. 预处理是根据一些MySQL规则检查解析树是否合理，比如检查表和列是否存在，还会解析名字和别名，然后预处理器会验证权限；

5. 根据执行计划查询执行引擎，调用API接口调用存储引擎来查询数据；

6. 将结果返回客户端，并进行缓存；

2、数据库常见规范

1. 所有数据库对象名称必须使用小写字母并用下划线分割；

2. 所有数据库对象名称禁止使用mysql保留关键字；

3. 数据库对象的命名要能做到见名识意，并且最后不要超过32个字符；

4. 临时库表必须以tmp_为前缀并以日期为后缀，备份表必须以bak_为前缀并以日期(时间戳)为后缀；

5. 所有存储相同数据的列名和列类型必须一致；

3、所有表必须使用Innodb存储引擎

没有特殊要求（即Innodb无法满足的功能如：列存储，存储空间数据等）的情况下，所有表必须使用Innodb存储引擎（mysql5.5之前默认使用Myisam，5.6以后默认的为Innodb）。

Innodb 支持事务，支持行级锁，更好的恢复性，高并发下性能更好。

4、每个Innodb表必须有个主键

Innodb是一种索引组织表：数据的存储的逻辑顺序和索引的顺序是相同的。每个表都可以有多个索引，但是表的存储顺序只能有一种。

Innodb是按照主键索引的顺序来组织表的

1. 不要使用更新频繁的列作为主键，不适用多列主键；

2. 不要使用UUID、MD5、HASH、字符串列作为主键（无法保证数据的顺序增长）；

3. 主键建议使用自增ID值；

5、数据库和表的字符集统一使用UTF8

兼容性更好，统一字符集可以避免由于字符集转换产生的乱码，不同的字符集进行比较前需要进行转换会造成索引失效，如果数据库中有存储emoji表情的需要，字符集需要采用utf8mb4字符集。

6、查询SQL尽量不要使用select *，而是具体字段

select *的弊端：

1. 增加很多不必要的消耗，比如CPU、IO、内存、网络带宽；

2. 增加了使用覆盖索引的可能性；

3. 增加了回表的可能性；

4. 当表结构发生变化时，前端也需要更改；

5. 查询效率低；

7、避免在where子句中使用 or 来连接条件

1. 使用or可能会使索引失效，从而全表扫描；

2. 虽然mysql是有优化器的，处于效率与成本考虑，遇到or条件，索引还是可能失效的；

8、尽量使用数值替代字符串类型

1. 因为引擎在处理查询和连接时会逐个比较字符串中每一个字符；

2. 而对于数字型而言只需要比较一次就够了；

3. 字符会降低查询和连接的性能，并会增加存储开销；

9、使用varchar代替char

1. varchar变长字段按数据内容实际长度存储，存储空间小，可以节省存储空间；

2. char按声明大小存储，不足补空格；

3. 其次对于查询来说，在一个相对较小的字段内搜索，效率更高；

10、财务、银行相关的金额字段必须使用decimal类型

• 非精准浮点：float,double

• 精准浮点：decimal

1. Decimal类型为精准浮点数，在计算时不会丢失精度；

2. 占用空间由定义的宽度决定，每4个字节可以存储9位数字，并且小数点要占用一个字节；

3. 可用于存储比bigint更大的整型数据；

11、where中使用默认值代替null

1. 并不是说使用了is null或者 is not null就会不走索引了，这个跟mysql版本以及查询成本都有关；

2. 如果mysql优化器发现，走索引比不走索引成本还要高，就会放弃索引，这些条件 !=，<>，is null，is not null经常被认为让索引失效；

3. 其实是因为一般情况下，查询的成本高，优化器自动放弃索引的；

4. 如果把null值，换成默认值，很多时候让走索引成为可能，同时，表达意思也相对清晰一点；

12、避免在where子句中使用!=或<>操作符

1. 使用!=和<>很可能会让索引失效

2. 应尽量避免在where子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描

3. 实现业务优先，实在没办法，就只能使用，并不是不能使用

13、inner join 、left join、right join，优先使用inner join

三种连接如果结果相同，优先使用inner join，如果使用left join左边表尽量小。

• inner join 内连接，只保留两张表中完全匹配的结果集；

• left join会返回左表所有的行，即使在右表中没有匹配的记录；

• right join会返回右表所有的行，即使在左表中没有匹配的记录；

为什么？

• 如果inner join是等值连接，返回的行数比较少，所以性能相对会好一点；

• 使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少；

• 这是mysql优化原则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优；

14、提高group by语句的效率

        先过滤，后分组

15、操作delete或者update语句，加个limit或者循环分批次删除

（1）降低写错SQL的代价

清空表数据可不是小事情，一个手抖全没了，删库跑路？如果加limit，删错也只是丢失部分数据，可以通过binlog日志快速恢复的。

（2）SQL效率很可能更高

SQL中加了limit 1，如果第一条就命中目标return， 没有limit的话，还会继续执行扫描表。

（3）避免长事务

delete执行时,如果age加了索引，MySQL会将所有相关的行加写锁和间隙锁，所有执行相关行会被锁住，如果删除数量大，会直接影响相关业务无法使用。

（4）数据量大的话，容易把CPU打满

如果你删除数据量很大时，不加 limit限制一下记录数，容易把cpu打满，导致越删越慢。

（5）锁表

一次性删除太多数据，可能造成锁表，会有lock wait timeout exceed的错误，所以建议分批操作。

16、表连接不宜太多，索引不宜太多，一般5个以内

（1）表连接不宜太多，一般5个以内

1. 关联的表个数越多，编译的时间和开销也就越大

2. 每次关联内存中都生成一个临时表

3. 应该把连接表拆开成较小的几个执行，可读性更高

4. 如果一定需要连接很多表才能得到数据，那么意味着这是个糟糕的设计了

（2）索引不宜太多，一般5个以内

1. 索引并不是越多越好，虽其提高了查询的效率，但却会降低插入和更新的效率；

2. 索引可以理解为一个就是一张表，其可以存储数据，其数据就要占空间；

3. 索引表的数据是排序的，排序也是要花时间的；

4. insert或update时有可能会重建索引，如果数据量巨大，重建将进行记录的重新排序，所以建索引需要慎重考虑，视具体情况来定；

5. 一个表的索引数最好不要超过5个，若太多需要考虑一些索引是否有存在的必要；

17、如何选择索引列的顺序

建立索引的目的是：希望通过索引进行数据查找，减少随机IO，增加查询性能 ，索引能过滤出越少的数据，则从磁盘中读入的数据也就越少。

区分度最高的放在联合索引的最左侧（区分度=列中不同值的数量/列的总行数）。

尽量把字段长度小的列放在联合索引的最左侧（因为字段长度越小，一页能存储的数据量越大，IO性能也就越好）。

使用最频繁的列放到联合索引的左侧（这样可以比较少的建立一些索引）。

18、对于频繁的查询优先考虑使用覆盖索引

覆盖索引：就是包含了所有查询字段(where,select,ordery by,group by包含的字段)的索引。

覆盖索引的好处：

（1）避免Innodb表进行索引的二次查询

Innodb是以聚集索引的顺序来存储的，对于Innodb来说，二级索引在叶子节点中所保存的是行的主键信息，如果是用二级索引查询数据的话，在查找到相应的键值后，还要通过主键进行二次查询才能获取我们真实所需要的数据。

而在覆盖索引中，二级索引的键值中可以获取所有的数据，避免了对主键的二次查询 ，减少了IO操作，提升了查询效率。

（2）可以把随机IO变成顺序IO加快查询效率

由于覆盖索引是按键值的顺序存储的，对于IO密集型的范围查找来说，对比随机从磁盘读取每一行的数据IO要少的多，因此利用覆盖索引在访问时也可以把磁盘的随机读取的IO转变成索引查找的顺序IO。

19、优化like语句

模糊查询，程序员最喜欢的就是使用like，但是like很可能让你的索引失效。

• 首先尽量避免模糊查询，如果必须使用，不采用全模糊查询，也应尽量采用右模糊查询， 即like ‘…%’，是会使用索引的；

• 左模糊like ‘%...’无法直接使用索引，但可以利用reverse + function index的形式，变化成 like ‘…%’；

• 全模糊查询是无法优化的，一定要使用的话建议使用搜索引擎。

20、关于临时表

1. 避免频繁创建和删除临时表，以减少系统表资源的消耗；

2. 在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log；

3. 如果数据量不大，为了缓和系统表的资源，应先create table，然后insert；

4. 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除。先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

21、使用explain分析你SQL执行计划

（1）type

1. system：表仅有一行，基本用不到；

2. const：表最多一行数据配合，主键查询时触发较多；

3. eq_ref：对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型；

4. ref：对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取；

5. range：只检索给定范围的行，使用一个索引来选择行。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比较关键字列时，可以使用range；

6. index：该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小；

7. all：全表扫描；

8. 性能排名：system > const > eq_ref > ref > range > index > all。

9. 实际sql优化中，最后达到ref或range级别。

（2）Extra常用关键字

• Using index：只从索引树中获取信息，而不需要回表查询；

• Using where：WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行，如果Extra值不为Using where并且表联接类型为ALL或index，查询可能会有一些错误。需要回表查询。

• Using temporary：mysql常建一个临时表来容纳结果，典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时；

22、禁止在表中建立预留字段

1. 预留字段的命名很难做到见名识义；

2. 预留字段无法确认存储的数据类型，所以无法选择合适的类型；

3. 对预留字段类型的修改，会对表进行锁定；

23、禁止在数据库中存储图片，文件等大的二进制数据

通常文件很大，会短时间内造成数据量快速增长，数据库进行数据库读取时，通常会进行大量的随机IO操作，文件很大时，IO操作很耗时。

通常存储于文件服务器，数据库只存储文件地址信息。

24、TEXT或BLOB类型只能使用前缀索引

因为MySQL对索引字段长度是有限制的，所以TEXT类型只能使用前缀索引，并且TEXT列上是不能有默认值的。

25、一些其它优化方式

（1）当只需要一条数据的时候，使用limit 1

limit 1可以避免全表扫描，找到对应结果就不会再继续扫描了。

（2）如果排序字段没有用到索引，就尽量少排序

（3）所有表和字段都需要添加注释

使用comment从句添加表和列的备注，从一开始就进行数据字典的维护。

（4）SQL书写格式，关键字大小保持一致，使用缩进。

（5）修改或删除重要数据前，要先备份。

（6）很多时候用 exists 代替 in 是一个好的选择

（7）where后面的字段，留意其数据类型的隐式转换。

（8）尽量把所有列定义为NOT NULL

NOT NULL列更节省空间，NULL列需要一个额外字节作为判断是否为 NULL的标志位。NULL列需要注意空指针问题，NULL列在计算和比较的时候，需要注意空指针问题。

（9）伪删除设计

（10）索引不适合建在有大量重复数据的字段上，比如性别，排序字段应创建索引

（11）尽量避免使用游标

因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。