1.MySQL架构
2.sql
- 执行顺序：
FROM <LEFT_TABLE>
ON <JOIN_CONDITION>
<JOIN_TYPR> JOIN <RIGHT_TABLE>
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
- 执行时间长：
- 数据过多：分库分表
- 关联太多表，太多join：sql优化
- 没有充分利用索引：建立索引
- 服务器调优及各个参数设置：调整my.ini
3.存储引擎
-
MyISAM
在磁盘上存储成三个文件：
（1）frm文件：存储表的定义数据
（2）MYD文件：存放表具体记录的数据
（3）MYI文件：存储索引
frm和MYI可以存放在不同的目录下。MYI文件用来存储索引，但仅保存记录所在页的指针，索引的结构是B+树结构。下面这张图就是MYI文件保存的机制：
支持数据的类型也有三种：（1）静态固定长度表
这种方式的优点在于存储速度非常快，容易发生缓存，而且表发生损坏后也容易修复。缺点是占空间。这也是默认的存储格式。（2）动态可变长表
优点是节省空间，但是一旦出错恢复起来比较麻烦。（3）压缩表
上面说到支持数据压缩，说明肯定也支持这个格式。在数据文件发生错误时候，可以使用check table工具来检查，而且还可以使用repair table工具来恢复。 -
InnoDB
InnoDB是默认的数据库存储引擎，他的主要特点有：
（1）可以通过自动增长列，方法是auto_increment。
（2）支持事务。默认的事务隔离级别为可重复度，通过MVCC（并发版本控制）来实现的。
（3）使用的锁粒度为行级锁，可以支持更高的并发；
（4）支持外键约束；外键约束其实降低了表的查询速度，但是增加了表之间的耦合度。
（5）配合一些热备工具可以支持在线热备份；
（6）在InnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度；
（7）对于InnoDB类型的表，其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一块，都位于B+数的叶子节点上；InnoDB的存储表和索引也有下面两种形式：
（1）使用共享表空间存储：所有的表和索引存放在同一个表空间中。
（2）使用多表空间存储：表结构放在frm文件，数据和索引放在IBD文件中。分区表的话，每个分区对应单独的IBD文件，使用分区表的好处在于提升查询效率。 -
MEMORY
将表中的数据存储到内存中 -
MERGE
MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表结构必须完全相同，尽管其使用不如其它引擎突出，但是在某些情况下非常有用。Merge表就是几个相同MyISAM表的聚合器；Merge表中并没有数据，对Merge类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的MyISAM表进行操作。 -
ARCHIVE
Archive是归档的意思，在归档之后很多的高级功能就不再支持了，仅仅支持最基本的插入和查询两种功能。拥有很好的压缩机制，它使用zlib压缩库，在记录被请求时会实时压缩，所以它经常被用来当做仓库使用。 -
CSV
该引擎的MySQL数据库表会在MySQL安装目录data文件夹中的和该表所在数据库名相同的目录中生成一个.CSV文件（所以，它可以将CSV类型的文件当做表进行处理），这种文件是一种普通文本文件，每个数据行占用一个文本行。该种类型的存储引擎不支持索引，即使用该种类型的表没有主键列；另外也不允许表中的字段为null。 -
BLACKHOLE
该存储引擎支持事务，而且支持mvcc的行级锁，写入这种引擎表中的任何数据都会消失，主要用于做日志记录或同步归档的中继存储，这个存储引擎除非有特别目的，否则不适合使用。 -
PERFORMANCE_SCHEMA
该引擎主要用于收集数据库服务器性能参数。这种引擎提供以下功能：提供进程等待的详细信息，包括锁、互斥变量、文件信息；保存历史的事件汇总信息，为提供MySQL服务器性能做出详细的判断；对于新增和删除监控事件点都非常容易，并可以随意改变mysql服务器的监控周期，例如（CYCLE、MICROSECOND）。 MySQL用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表。 -
Federated
该存储引擎可以不同的Mysql服务器联合起来，逻辑上组成一个完整的数据库。这种存储引擎非常适合数据库分布式应用。
4.BTree&B+Tree
- BTree：
数据结构：
一颗b树，浅蓝色的块我们称之为一个磁盘块,可以看到每个磁盘块包含几个数据项（深蓝色所示)和指针（黄色所示) ,如磁盘块1包含数据项17和35,包含指针P1、P2、P3,P1表示小于17的磁盘块, P2表示在17和35之间的磁盘块, P3表示大于35的磁盘块。 - B+Tree：
数据结构：
- B+Tree与BTree的区别：
- B树的关键字和记录是放在一起的;B+树的非叶子节点中只有关键字和指向下一个节点的索引,记录只放在叶子节点中。
- 在B树中，越靠近根节点的记录查找时间越快，只要找到关键字即可确定记录的存在;而B+树中每个记录的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要再比较关键字。从这个角度看B树的性能好像要比B+树好,而在实际应用中却是B+树的性能要好些。因为B+树的非叶子节点不存放实际的数据，这样每个节点可容纳的元素个数比B树多,树高比B树小,这样带来的好处是减少磁盘访问次数。尽管B+树找到一个记录所需的比较次数要比B-树多，但是一次磁盘访问的时间相当于成百上千次内存比较的时间,因此实际中B+树的性能可能还会好些,而且B+树的叶子节点使用指针连接在一起,方便顺序遍历。
5.聚簇索引&非聚簇索引
- 聚簇索引：
聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据。
聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。 - 辅助索引(非聚簇索引)：
在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的Page Directory找到数据行。
6.索引
-
创建索引：
create index idx_字段名 on 表名(字段名); -
删除索引：
drop index idx_字段名 on 表名; -
索引分类：
- 单值索引： 一个索引只包含单个列,一个表可以有多个单列索引。
- 唯一索引： 索引列的值必须唯一,但允许有空值。
- 主键索引： 设定为主键后数据库会自动建立索引, innodb为聚簇索引。
- 复合索引： 一个索引包含多个列。
-
语法：
- 创建： CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX [indexName] ON table_name(column);
- 删除： DROP INDEX [indexName] ON mytable;
- 查看： SHOW INDEX FROM tabla_nama\G;
-
什么情况下需要创建索引：
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引
- 查询中与其它表关联的字段,外键关系建立索引
- 单键/组合索引的选择问题，组合索引性价比更高
- 查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度
- 查询中统计或者分组字段
-
什么情况下不需要创建索引：
- 表记录太少
- 经常增删改的表或字段
- where条件里用不到的字段
- 过滤性不好的字段：如性别、状态
7.性能分析(Explain)
EXPLAIN关键字可以模拟优化器执行SQL查询语句。
各字段解释：
-
id：select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序
- id相同：执行顺序由上至下
- id不同：如果是子查询, id的序号会递增, id值越大优先级越高,越先被执行
- id既有相同又有不同：id如果相同,可以认为是一组，从上往下顺序执行；在所有组中, id值越大,优先级越高，越先执行
-
select_type：查询的类型,主要是用于区别普通查询、联合查询、子查询等的复杂查询
-
table：显示这一行数据是哪个表的
-
partitions：代表分区表中的命中情况,非分区表,该项为null
-
type：访问类型
| 访问类型 | 性能高低从上到下的顺序 |
|---|---|
| system | 表只有一行记录(等于系统表) ,这是const类型的特列,平时不会出现,这个也可以忽略不计 |
| const | 表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据,所以很快-如将主键置于where列表中, MySQL就能将该查询转换为一个常量 |
| eq ref | 唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描 |
| ref | 非唯一性索引扫描,返回匹配某个单独值的所有行.本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而,它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体 |
| fulltext | 全文索引 |
| ref or null | 对于某个字段既需要关联条件,也需要null值得情况下。查询优化器会选择用refor_null连接查询。 |
| index merge | 在查询过程中需要多个索引组合使用,通常出现在有or的关键字的sql中 |
| unique subquery | 该联接类型类似于index-subquery.子查询中的唯一索引 |
| index subquery | 利用索引来关联子查询,不再全表扫描。 |
| range | 只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引一般就是在你的where语句中出现了between, <、>、in等的查询,这种范围扫描索引扫描比全表扫描要好,因为它只需要开始于索引的某一点,而结束语另一点,不用扫描全部索引。 |
| index | 出现index是sql使用了索引但是没用通过索引进行过滤,一般是使用了覆盖索引或者是利用索引进行了排序分组 |
| ALL | Full Table Scan,将遍历全表以找到匹配的行 |
注：—股来说，得保证查询至少达到range级别，最好能达到ref.
-
possible_keys：显示可能应用在这张表中的索引，可以是多个；查询涉及到的字段上若存在索引，则被列出，但不一定被使用
-
key：实际使用到的索引，null代表没使用索引；查询若使用覆盖索引，则该索引仅出现在key列表中
-
key_len：表示索引中使用的字节数，越小越好，显示的值为索引字段的最大可能长度，并非实际使用长度
-
ref：显示索引的哪一列被使用了，最好是常量
-
rows：根据表统计信息及索引选用情况，大致估计出找到所需的记录所需要读取的行数。行数越少越好
-
filtered：这个字段表示存储引擎返回的数据在server层过滤后,剩下多少满足查询的记录数量的比例,注意是百分比,不是具体记录数
-
Extra：包含不适合在其他列中显示但十分重要的额外信息
| 类型 | |
|---|---|
| using filesort | 像说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为"文件排序" |
| using temporary | 使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by |
| using index | 表示相应的select操作中使用了覆盖索引(Covering Index) ,避免访问了表的数据行,效率不错!如果同时出现using where,表明索引被用来执行索引键值的查找;如果没有同时出现using where,表明索引只是用来读取数据而非利用索引执行查找。 |
| using where | 表明使用了where过滤 |
| using join buffer | 使用了连接缓存 |
| impossible where | where子句的值总是false,不能用来获取任何元组 |
| select tables optimized away | 在没有GROUPBY子句的情况下,基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作,不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成优化。 |
8.索引失效问题
- 最好全值匹配；如 ：
select name,age from t_user where name = '张三' and age = '23'; 全值匹配： create index idx_name_age on t_user(name,age); - 最左前缀法则：如果索引了多列，查询从索引的最左前列开始，且不能跳过索引中的列；如：
create index idx_name_age_tel on t_user(name,age,tel); 有sql： 1. select name,age,tel from t_user where name = '张三' and age = '23' and tel = '123456'; // 能用上索引，效率最高 2. select name,age,tel from t_user where name = '张三' and tel = '123456'; // 能用上索引，只命中name 3. select name,age,tel from t_user where age = '23' and tel = '123456'; // 用不上索引，效率最低 - 不在索引列上做任何操作（计算，函数，类型转换），会导致索引失效而转向全表扫描；
- 存储引擎不能使用索引中范围条件右边的列，即范围之后全失效；
- 尽量使用覆盖索引，只访问索引的查询（索引列和查询列一致），减少selec *；
- MySQL在使用不等于的时候无法使用索引会导致全表扫描；
- is null，is not null，not exists 也无法使用索引；
- like 以通配符开头（’%aa‘）索引会失效，变成全表扫描；
- 字符串不加单引号，索引失效；
- 少用 or，用它来连接时候会索引失效
- 一个总结：假假设index(a,b,c)
| where条件 | 索引是否被引用 |
|---|---|
| where a = 3 | Y,使用到a |
| where a= 3 and b = 5 | Y,使用到a,b |
| where a = 3 and b = 5 and c = 4 | Y,使用到a,b,c |
| where b = 5 或 where b = 5 and c = 4 或 c = 4 | N |
| where a = 3 and c = 4 | Y,使用到a |
| where a = 3 and b > 5 and c = 4 | Y,使用到a,b |
| where a is null and b is not null | Y,使用到a |
| where a <> 3 | N |
| where abs(a) = 3 | N |
| where a = 3 and b like ‘5%’ and c = 4 | Y,使用到a,b,c |
| where a = 3 and b like ‘%5’ and c = 4 | Y,使用到a |
| where a = 3 and b like ‘5%5%’ and c = 4 | Y,使用到a,b,c |
建议：
- 对于单键索引,尽量选择针对当前query过滤性更好的索引
- 在选择组合索引的时候,当前query中过滤性最好的字段在索引字段顺序中,位置越靠前越好
- 在选择组合索引的时候,尽量选择可以能够包含当前query中的where字句中更多字段的索引
- 在选择组合索引的时候,如果某个字段可能出现范围查询时,尽量把这个字段放在索引次序的最后面
- 书写sql语句时,尽量避免造成索引失效的情况
9.关联查询优化建议
驱动表 left join 被驱动表：
- 保证被驱动表的join字段已经被索引
- leftjoin时,选择小表作为驱动表,大表作为被驱动表。
- inner join时, mysql会自己帮你把小结果集的表选为驱动表。
- 子查询尽量不要放在被驱动表,有可能使用不到索引。
- 能够直接多表关联的尽量直接关联,不用子查询。
10.分组排序优化：
order by子句,尽量使用Index方式排序,避免使用filesort方式排序。
注意：
- order by 排序如果想用索引，必须加过滤条件(limit也属于过滤条件)，否则extra出现using filesort
- 排序字段和索引创建的顺序不一致，否则extra出现using filesort
- 若order by name desc,age asc; 出现排序方向不一致情况，否则extra出现using filesort
- 先举个例子若where name = ‘张三’ and age > 23 order by grade; 创建索引 create index idx_name_age_grade,执行sql后命中name和age，grade失效； 所以：当范围条件和group by或者order by的字段出现二选一时，优先观察条件字段的过滤数量,如果过滤的数据足够多，而需要排序的数据并不多时,优先把索引放在范围字段上。反之，亦然。
- group by使用索引的原则几乎跟orderby一致,唯一区别是groupby即使没有过滤条件用到索引,也可以直接使用索引。
如果不在索引列上, filesort有两种算法: mysql就要启动双路排序和单路排序：
-
双路排序
MySOL 4.1之前是使用双路排序,意思就是两次扫描磁盘,最终得到数据,读取行指针和orderby列,对他们进行排序,然后扫描已经排序好的列表,按照列表中的值重新从列表中读取对应的数据输出。
从磁盘取排序字段,在buffer进行排序,再从磁盘取其他字段。 -
单路排序
取一批数据,要对磁盘进行了两次扫描,因为 IO是很耗时的,所以在mysql4.1之后,出现了第二种改进的算法,就是单路排序。从磁盘读取查询需要的所有列,按照order by列在buffer对它们进行排序,然后扫描排序后的列表进行输出它的效率更快一些,避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间,因为它把每一行都保存在内存中了。
-
使用单路存在的问题：
在sort buffer中,方法B比方法A要多占用很多空间，因为方法B是把所有字段都取出，所以有可能取出的数据的总大小超出了sort buffer的容量，导致每次只能取sort buffer容量大小的数据,进行排序(创建tmp文件,多路合并) ,排完再取取sort buffer容量大小，再排….从而多次IO。本来想省一次IO操作，反而导致了大量的IO操作，反而得不偿失。优化策略：
- Order by时select*是一个大忌，只Query需要的字段,这点非常重要。在这里的影响是:
1)当Query的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT I BLOB类型时,会用改进后的算法–单路排序，否则用老算法–多路排序。
2)两种算法的数据都有可能超出sort buffer的容量,超出之后,会创建tmp文件进行合并排序，导致多次IO，但是用单路排序算法的风险会更大一些,所以要提高sort_buffer_size。 - 尝试提高sort_buffer_size不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的1M-8M之间调整。
- 尝试提高max_length_for_sort_data提高这个参数，会增加用进算法的概率。但是如果设的太高,数据总容量超出sortbuffer size的概率就增大,明显症状是高的磁盘IO活动和低的处理器使用率.1k-8k之间调整。
- Order by时select*是一个大忌，只Query需要的字段,这点非常重要。在这里的影响是:
11.查询截取分析
-
慢查询日志：MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long_query time(默认值为10s)的SQL,则会被记录到慢查询日志中。
默认情况下, MySQL数据库没有开启慢查询日志,需要手动来设置这个参数。如果不是调优需要的话,一般不建议启该参数，因为开启慢查询日志会带来一定的性能影响。慢查询日志支持将日志记录写入文件。
- 查看是否开启：SHOW VARIABLES LIKE%slow_query_log%;
- 开启：set global slow_query_log=1;
注：使用set globalslow_query_log=1;开启了慢查询日志只对当前数据库生效,如果MySQL重启后则会失效。若要永久生效，就必须修改配置文件my.cnf，添加如下配置后重启MySQL：
[mysqld]
slow_query_log =1
slow_query_log_file=/var/lib/mysql/host_name-slow.log
long_query_time=3
log_output=FILE
// 参数slow_query_log_file ,它指定慢查询日志文件的存放路径，系统默认会给一个缺省的文件host nameslow.log
- 查看当前多少秒算慢：show variables like ‘%long_query_time%’;
- 设置慢的阙值时间：set global long_query_time = 1;
- 查看当前数据库有多少条慢SQL：show global status like ‘%Slow_queries%’;
-
全局查询日志
不可以在生产环境中开启这个功能。设置如下，编写的sql语句都会记录到mysql库中的general_log表
set global general_log = 1;
set global log_output = ‘TABLE’;
在生产环境中,如果要手工分析日志,查找、分析SQL,显然是个体力活,MySQL提供了日志分析工具mysqldumpslow，本文不再叙述mysqldumpslow…
587
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
