MySQL——性能优化

性能优化的思路
1、首先需要使用慢查询功能，去获取所有查询时间比较长的SQL语句。
MySQL——慢查询
2、其次使用explain命令去查看有问题的SQL的执行计划。
MySQL——执行计划EXPLAIN
3、最后可以使用show profile[s] 查看有问题的SQL的性能使用情况。
MySQL高级：show profile

SQL查询语法顺序和执行顺序

MySQL查询语法顺序

• SELECT
• FROM
• LEFT JOIN
• ON
• WHERE
• GROUP BY
• HAVING
• ORDER BY
• LIMIT

  SELECT * FROM user

LEFT JOIN order ON user.id = order.uid

WHERE order.price > 1000

GROUP BY user.name

HAVING count(1) > 5

ORDER BY user.name LIMIT 0,10
1、FROM（将最近的两张表，进行笛卡尔积）—VT1
2、ON（将VT1按照它的条件进行过滤）—VT2
3、LEFT JOIN（保留左表的记录）—VT3
4、WHERE（过滤VT3中的记录）–VT4…VTn

5、GROUP BY（对VT4的记录进行分组）—VT5
6、HAVING（对VT5中的记录进行过滤）—VT6
7、SELECT（对VT6中的记录，选取指定的列）–VT7
8、ORDER BY（对VT7的记录进行排序）–游标
9、LIMIT（对排序之后的值进行分页）

WHERE条件执行顺序（影响性能）
1、MYSQL：从左往右去执行WHERE条件的

1、合理的创建及使用索引（考虑数据的增删情况）。
2、合理的冗余字段（尽量建一些大表，考虑数据库的三范式和业务设计的取舍）。
3、使用SQL要注意一些细节：
select语句中尽量不要使用*、count(*)，
WHERE语句中尽量不要使用1=1、in语句（建议使用exists）、
注意组合索引的创建顺序按照顺序组着查询条件、
尽量查询粒度大的SQL放到最左边、
尽量建立组合索引。
4、合理利用慢查询日志、explain执行计划查询、show profile查看SQL执行时的资源使用情况。

5、表关联查询时务必遵循小表驱动大表原则。
6、使用查询语 where 条件时，不允许出现函数，否则索引会失效；
7、使用单表查询时，相同字段尽量不要用 OR，因为可能导致索引失效，比如：SELECT * FROM table WHERE name = '手机' OR name = '电脑'，可以使用 UNION 替代；
8、LIKE 语句不允许使用 % 开头，否则索引会失效；
9、组合索引一定要遵循 从左到右 原则，否则索引会失效；比如：SELECT * FROM table WHERE name = '张三' AND age = 18，那么该组合索引必须是 name,age 形式；
10、索引不宜过多，根据实际情况决定，尽量不要超过 10 个；
11、每张表都必须有 主键，达到加快查询效率的目的；
12、分表，可根据业务字段尾数中的个位或十位或百位（以此类推）做表名达到分表的目的；
13、分库，可根据业务字段尾数中的个位或十位或百位（以此类推）做库名达到分库的目的；
14、表分区，类似于硬盘分区，可以将某个时间段的数据放在分区里，加快查询速度，可以配合 分表 + 表分区 结合使用；
 

最左前缀匹配原则
在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，示例：
对列col1、列col2和列col3建一个联合索引

KEY test_col1_col2_col3 on test(col1,col2,col3);
1
联合索引 test_col1_col2_col3 实际建立了(col1)、(col1,col2)、(col,col2,col3)三个索引。

SELECT * FROM test WHERE col1='1' AND clo2='2' AND clo4='4'
1
上面这个查询语句执行时会依照最左前缀匹配原则，检索时会使用索引(col1,col2)进行数据匹配。
 

为什么要使用联合索引
减少开销。建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！

覆盖索引。对联合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。

效率高。索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W10%=100w条数据，然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出1000w10% 10% *10%=1w，效率提升可想而知！