高性能mysql_高性能MySQL

一、MySQL索引引擎对比

二、性能下降SQL慢，执行时间长，等待时间长

① 查询语句写的差

② 索引失效

③ 关联查询太多join(设计缺陷或不得已的需求)

④ 服务器调优及各个参数设置(缓冲，线程数等)

三、手写与机写SQL

手写模板：

SELECT DISTINCTFROMJOIN ON WHEREGROUP BYHAVINGORDER BYLIMIT

机写模板

1 FORM

2 ON

3 JOIN

4 WHERE

5 GROUP BY

6 HAVING

7 SELECT8 DISTINCT

9 ORDER BY

10 LIMIT

四、笛卡积

五、索引

5.1 mysql索引分类

单值索引：一个索引只包含单个列，一个表可以有多个单列索引

唯一索引：索引列的值必须唯一，但允许有空值

复合索引：一个索引包含多个列

基本语法：

创建：1.CREATE [UNIQUE] indexName ON mytable (columnname(length));——如果是CHAR，VARCHAR类型，length可以小于字段实际长度，如果是BLOB和TEXT类型，必须指定length。

2.ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnname(length))

删除：DROP INDEX [IndexName] ON mytable

查看：SHOW INDEX FROM table_name\G

table user：

id name email phone addressselect * from user where name ="";//在表user上建立name的索引create index idx_user_name on user(name);

5.2、mysql索引结构

B+Tree索引、Hash索引、full-text全文索引、(空间数据索引)R-Tree

5.3、B+Tree索引的查询类型

1、全值匹配：和索引中的所有列进行匹配

2、匹配最左前缀

3、匹配列前缀

4、匹配范围值

5、精确匹配某一列并范围匹配另外一列

6、只访问索引的查询

同理：B+Tree索引限制

1、如果不是按照索引的最左列开始查找，则无法使用索引。

2、不能跳过索引的列

3、如果查询中有个列的范围查询，则其右边所有列都无法使用索引优化查找。

5.4、索引的优点

1、索引大大减少了服务器需要扫描的数据量。

2、索引可以帮助服务器避免排序和临时表。

3、索引可以将随机I/O变成顺序I/O。

就、索引策略

1、前缀索引和索引选择性。

选择足够长的前缀以保证较高的选择性，不能太长(以便节省空间)，但得足够长，使得前缀索引的选择性接近于索引这个列。

2、选择合适的索引列顺序。

当不考虑排序和分组时，将选择性最高的列放在前面，此时索引的作用只是优化WHERE条件的查找。

5.5、聚簇索引

聚簇索引不是索引类型，而是数据存储方式。InnoDB的聚簇索引——同一结构中保存了B+Tree索引和数据行。

当表有聚餐索引时，它的数据行实际上存放在索引的叶子页中。

如果没有定义主键，InnoDB会选择一个唯一的非空索引替代。如果没有这样的索引，InnoDB会隐式的定义一个主键来作为聚簇索引。

聚簇索引的优点：

1、可以把关联数据保存在一起。

2、数据访问更快。聚簇索引将索引和数据保存在同一个B+Tree中，因此从聚簇索引中获取数据通常比非聚簇索引中查找要快。

3、使用覆盖索引扫描的查询可以直接使用叶节点的主键值。

注意：二级索引需要两次索引查找：二索引叶子节点保存是行的主键值。先B-Tree查主键值，再B-Tree聚簇索引查对应的行。

页分裂：一个数据页满了，按照B+Tree算法，新增加一个数据页，叫做页分裂，会导致性能下降。空间利用率降低大概50%。当相邻的两个数据页利用率很低的时候会做数据页合并，合并的过程是分裂过程的逆过程。

5.6、覆盖索引

如果一个索引包含(覆盖)所有需要查询的字段的值——覆盖索引。

优点：

1、索引条目通常小于书数据行大小，如果只需要读取索引，那mysql就会极大减少数据访问量。

2、因为索引是按照列值顺序存储的，所以对于I/O密集型的范围查找会比随机从磁盘中读取每一行的数据I/O要少得多。

3、由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点保存了行主键值，如果二级索引能够覆盖查询，那么就避免对主键索引的二次查询。

索引下推：在MySQL5.6之前，只能从根据最左前缀查询到ID开始一个个回表。到主键索引上找出数据行，再对比字段值。MySQL5.6引入的索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

5.7、索引扫描做排序

mysql有两种方式可以生成有序的结果：通过排序操作；或者按索引顺序扫描；如果EXPLAIN出来的type列的值为“index”，则说明mysql使用了索引扫描来排序。

mysql可以使用同一索引即满足排序，有用于查找行。因此，设计索引尽可能满足这两种任务。

只有当索引的列顺序和ORDER BY字句的顺序完全一样，并且所有列的排序方向(倒序或正序)都一样，mysql才能使用索引对结果排序。

5.8、创建索引建议

1、主键自动建立唯一索引

2、频繁作为查询条件的字段

3、查询中与其他表关联的字段，外键关系

4、频繁更新的字段不适合

5、查询中排序的字段(见八)

6、查询中统计或者分租。

5.9、索引使用注意

1、全值匹配

2、最佳左前缀法则：索引多列，要遵守最左前缀法则，查询从最左开始，不跳列

3、不在索引上做任务操作(计算、函数、(手动或自动)类型转化)，会导致索引失效全表扫描

4、尽量使用覆盖索引，减少select *

5、mysql在使用不等于(！=或<>)无法使用索引

6、is null，is not null无法使用索引

7、like以通配符开头，索引失效

8、字符串不加单引号，索引失效

9、少用or，用它来连接时索引会失效。

六、Mysql常见瓶颈

CPU：CPU在饱和的时候发生在数据装入内存或从磁盘上读取数据时候

IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候

服务器硬件的性能瓶颈：top,free,iostat和vmstat来查看系统的性能状态。

七、执行计划(Explain SQL)

id

select_type

table

type

possible_keys

key

ken_len

ref

rows

Extra

作用：

表的读取顺序

数据读取操作的操作类型

哪些索引可以使用

那些索引被实际使用

表之间的引用

每张表有多少行被优化器查询

重点关注：id——select_type——type——key——rows——Extra

7.1 id：如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

7.2 select_type：

① SIMPLE:简单的select查询，查询中不包含子查询或者UNION

② PRIMARY:查询中若包含任何复杂的子查询，最外层查询则被标记为

③ SUBQUERY:在SELECT 或WHERE列表中包含子查询

④ DERIVED:在FROM列表中包含的子查询被标记为DERIVED(衍生)，mysql会递归执行这些子查询，把结果放在临时表里。

⑤ UNION:若第二个SELECT出现在UNION之后，则被标记为UNION;若UNION包含在FROM子查询中，外层SELECT将被标记为：DERIVED

⑥ UNION RESULT：从UNION表获取结果的SELECT

7.3 type——访问类型排列

显示查询使用了何种类型，最好到最差：system>const>eq_ref>ref>rang>index>ALL

7.4 Extra——额外信息

① Using filesort(需要文件排序辅助处理，差)

② Using temporary(需要临时表辅助处理，很差)

③ Using index(用到索引，好)

④ using where

⑤ using join buffer(使用了连接缓存，差)

⑥ impossible where

⑦ select tables optimized away

⑧ distinct

八、索引优化

① 单表索引

② 两表索引：left join(建立在右表)：左表是全部都要走一遍的(可以根据主键)，右边才是关注重点，通过索引快速定位。同理，right join(索引建立在左表)。

③ 三表索引：建立在后面两张表关联字段上。

Join语句的优化：

(1)尽可能减少Join语句中的NestedLoop的循环总次数；用小结果集驱动大结果集。

(2)优先优化NestedLoop的内层循环

(3)保证Join语句中被驱动表上Join条件字段已经被索引；

(4)当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝惜JoinBuffer的设置。

题目分析：

定值、范围还是排序，一般order by是给个范围；group by基本上都需要进行排序，会有临时表产生。

一般建议：

① 对于单键索引，尽量选择针对当前query过滤性更好的索引

② 在选择组合索引的时候，当前query中过滤性最好的字段在索引字段排序中，应该位置越靠前越好。

③ 在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引

④ 尽可能通过分析统计信息和调整query的写法来表达合适索引的目的。