Mysql性能优化

—————————— Yesterday is history, tomorrow is a mystery, but today is a gift. That is why it’s called the present. ——————————

---

性能优化可分为两部分：设计优化和查询优化

1. 设计优化

1.1 字段类型

1、数据类型越小越好，数据越小需要的存储空间就越小，每个页能够存储的信息就越多。例如 tinyint 占据一个字节，可存储 0~255 范围内的数值（无符号）。smallint 占据两个字节，可存储 0~65535 范围内的数值（无符号），需要根据具体的场景来选择最合适的类型。
2、简单的数据类型操作代价更低
3、尽量避免使用null，可以限定列not null。因为null存储需要额外的空间，可以考虑使用0来代替null。

2. 查询优化

2.1 不要使用 select * 进行查询

这个提到的很多，可以分为两种情况，一种是 select * from students where id = 2，id是主键，也就是使用聚簇索引进行查询。这种情况下是可以一次查询到所有的信息，此时使用 select *的主要影响是会增加内存和网络消耗。
另外一种情况是 select * from students where name = 'zhou'，name是普通索引，也就是使用非聚簇索引进行查询。这种情况下需要进行回表操作，根据聚簇索引查找其他信息。如果只需要name和age两个字段，可以创建name_age联合索引，使用 select name, age from students where name = 'zhou' 进行查询，这样就不用再进行回表，即覆盖索引。

2.2 使用like时避免前缀模糊查询

一般不建议使用like，如果使用 select from students where name like '%xxx%' 这样的语句是不能走索引的，但是使用 like 'zhou%' 可以走索引查询。
students 表中共有8条记录，其中 name 有普通索引，使用 select * from students where name like 'zhou%' 是可以走索引的。

2.4 尽量避免负向查询

尽量不要使用 not null、!=、<>、not in、not like 这些查询词，负向查询不会走索引，但不是绝对的。创建测试表 testTable1，表中有2000条数据，其中 num=0 700条，num=1 300条，num=2 1000条，已创建 num_sta 联合索引。

使用 select * from testTable1 where num != 2进行查询，会进行全表扫描。

可以考虑使用 select * from testTable1 where num = 0 UNION ALL select * from testTable1 where num = 1 进行查询

使用 select num from testTable1 where num != 2 进行查询，即覆盖索引，是可以走索引的

2.5 禁止在索引字段上做数学运算或函数运算

对索引字段上做数学运算或函数运算可能会破坏索引的有序性，所以优化器会放弃走索引。只要进行数学运算和函数运算就不会走索引，就算没有破坏索引有序性。

同样根据上表进行举例，select * from testTable1 where num = 0 会走索引，但是 select * from testTable1 where num + 1 = 1 不会索引，尽管数学运算并没有破坏索引有序性。

注意，隐式类型转换同样属于函数运算，这里需要了解 mysql 进行隐式类型转换的规则：
The following rules describe how conversion occurs for comparison operations:
1. If one or both arguments are NULL, the result of the comparison is NULL, except for the NULL-safe <=> equality comparison operator. For NULL <=> NULL, the result is true. No conversion is needed.
2. If both arguments in a comparison operation are strings, they are compared as strings.
3. If both arguments are integers, they are compared as integers.
4. Hexadecimal values are treated as binary strings if not compared to a number.
5. If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed. This is done to be more ODBC-friendly. This is not done for the arguments to IN(). To be safe, always use complete datetime, date, or time strings when doing comparisons. For example, to achieve best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type.
A single-row subquery from a table or tables is not considered a constant. For example, if a subquery returns an integer to be compared to a DATETIME value, the comparison is done as two integers. The integer is not converted to a temporal value. To compare the operands as DATETIME values, use CAST() to explicitly convert the subquery value to DATETIME.
6. If one of the arguments is a decimal value, comparison depends on the other argument. The arguments are compared as decimal values if the other argument is a decimal or integer value, or as floating-point values if the other argument is a floating-point value.
In all other cases, the arguments are compared as floating-point (double-precision) numbers. For example, a comparison of string and numeric operands takes place as a comparison of floating-point numbers.

测试表 testTable1 中的 sta 字段类型是 char，如果将其和数值进行比较则不会走索引，例如：select * from testTable1 where sta = 1，这个sql相当于 select * from testTable1 where cast(sta as float) = 1，同样是对字段进行函数运算。

Mysql官方文档

2.6 不要进行深度分页查询

分页查询是经常使用的，但是不要进行深度分页查询，例如 SELECT * FROM u_server_order LIMIT 10 OFFSET 10000，会先筛选100010数据，然后进行回表，再返回最后的10条数据。 有以下两个个办法可以进行优化：
1、先查询ID，进行截取后再查询需要的字段。
2、添加上次查询的结果作为限制条件，例如 SELECT * FROM testTable WHERE id > XXX LIMIT 10