高性能MySQL

一、MySQL索引引擎对比

 

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

① 查询语句写的差

② 索引失效

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

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

三、手写与机写SQL

手写模板：

SELECT DISTINCT
    <select_list>
FROM
    <left_table> <join_type>
JOIN <right_table> ON <join_condition>
WHERE 
    <where_condition>
GROUP BY
    <group_by_list>
HAVING
    <having_condition>
ORDER BY
    <order_by_condition>
LIMIT 
    <limit_number>

机写模板

FORM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT
DISTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_number>

 

 四、笛卡积

 

 

五、索引

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 address

select * 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的写法来表达合适索引的目的。