浅谈MySQL:结构、存储引擎、索引、优化

MySQL 体系结构

1.模块

1、 Connector:用来支持各种语言和 SQL 的交互,比如 PHP,Python,Java 的JDBC;
2、 Management Serveices & Utilities:系统管理和控制工具,包括备份恢复、MySQL 复制、集群等等;
3、 Connection Pool:连接池,管理需要缓冲的资源,包括用户密码权限线程等等;
4、 SQL Interface:用来接收用户的 SQL 命令,返回用户需要的查询结果
5、 Parser:用来解析 SQL 语句;
6、 Optimizer:查询优化器;
7、 Cache and Buffer:查询缓存,除了行记录的缓存之外,还有表缓存,Key 缓存,权限缓存等等;
8、 Pluggable Storage Engines:插件式存储引擎,它提供 API 给服务层使用,跟具体的文件打交道。

2. 架构分层

2.1、连接层: 跟客户端对接。
客户端连接 MySQL 服务器 3306 端口,要跟服务端建立连接,管理所有的连接,验证客户端的身份和权限等功能就在连接层完成。
2.2、服务层: 真正执行操作的。
连接层会把 SQL 语句交给服务层,这里面又包含一系列的流程:
如查询缓存的判断、根据 SQL 调用相应的接口,对 SQL 语句进行词法和语法的解析(语法的正确、关键字识别、名识别等)。然后优化器 SQL 语句进行优化,再交给执行器去执行。
3.3、存储引擎层: 和跟硬件打交道的
存储引擎是数据存放的地方,在 MySQL 里面支持不同的存储引擎。再往下就是内存或者磁盘。

MySQL语法解析和预处理

解析器(Parser)
  1、词法解析:把一个完整的 SQL 语句分成一个个的单词。
  2、语法解析:先对 SQL 做语法检查,然后根据 MySQL 定义的语法规则,根据 SQL 语句生成一个数据结构为解析树(select_lex)。
预处理器(Preprocessor)
  检查生成的解析树,解决解析器无法解析的语义,会检查表和列名是否存在,检查名字和别名。预处理之后得到一个新的解析树。

3. SQL 的执行

3.1、缓冲池(Buffer Pool)

InnnoDB 的数据是放在磁盘上的,InnoDB 操作数据有一个最小的逻辑单位是页(索引页和数据页)。
对于数据的操作,不是每次都直接操作磁盘,因为IO太慢。InnoDB 用缓冲池的技术,把磁盘读到的页放到一块内存里(缓冲池(Buffer Pool))。下一次读相同的页,先判断是不是在缓冲池里。
改数据时,先改缓冲池里面的页。内存的数据和磁盘数据不一致的时,则为脏页。InnoDB 里有后台线程把 Buffer Pool 数据写入到磁盘,每隔一段时间就一次性地把多个修改写入磁盘,为刷脏。
Buffer Pool 是 InnoDB 里面非常重要的一个结构,它的内部又分成几块区域。这里我们趁机到官网来认识一下 InnoDB 的内存结构和磁盘结构。

3.2、InnoDB 内存结构和磁盘结构

1.内存结构(默认128M)主要分为3个部分: Buffer Pool、Change Buffer、Adaptive Hash Index,还有(redo)log buffer。
    1、Buffer Pool :缓存页信息,包括数据页、索引页。 查看服务器状态: SHOW STATUS LIKE '%innodb_buffer_pool%';。 查看参数(系统变量): SHOW VARIABLES like '%innodb_buffer_pool%'。
    2、Change Buffer 写缓冲
    Insert、delete、update后如果这个数据页不是唯一索引,不用从磁盘加载索引页判断数据是不是重复(唯一性检查)。
    先把修改记录在内存的缓冲池中,提升更新语句的执行速度。访问这个数据页的时把 Change Buffer 记录(merge)到磁盘数据页。 
    如果数据库业务写多读少且大部分索引都是非唯一索引,可调大Change Buffer(写缓冲)值:SHOW VARIABLES LIKE 'innodb_change_buffer_max_size';
    3、Adaptive Hash Index
    自适应哈希索引,InnoDB 内部使用哈希索引来实现自适应哈希索引特性。
    InnoDB 只支持显式创建 B+Tree 索引,对于一些热点数据页,InnoDB 会自动建立自适应 Hash 索引,也就是在 B+Tree 索引基础上建立 Hash 索引,这个过程对于客户端是不可控制的,隐式的。
    我们在 Navicat 工具里面选择索引方法是哈希,但是它创建的还是 B+Tree 索引,这个不是我们可以手动控制的。
    这个开关默认是 ON:show variables like 'innodb_adaptive_hash_index';
    从存储引擎的运行信息中可以看到:show engine innodb status\G;
    4、(redg)Log Buffer(分成内存和磁盘两部分)
    InnoDB 把对页的修改操作写入日志文件,并在数据库启动时从这个文件进行恢复操作(实现 crash-safe)。写日志,再写磁盘(顺序IO取代随机IO)。
    日志文件就是磁盘的redo log(重做日志),位于/var/lib/mysql/目录下的ib_logfile0 和 ib_logfile1,每个48M。
    show variables like 'innodb_log%';
        磁盘的最小组成单元是扇区,通常是512个字节。数据库操作系统和内存打交道,最小的单位是页 Page。操作系统和磁盘打交道,读写磁盘,最小的单位是块 Block。
        随机IO: 如果数据是随机分散在不同页的不同扇区中,那么找到相应的数据需要等到磁臂旋转到指定的页,然后盘片寻找到对应的扇区,才能找到需要的数据,一直重复此过程直到读完所有数据。
        顺序IO: 如果数据连续的,就不要重新寻址,可以依次拿到所需的数据。
    redo log 有时也写在Buffer Pool里,Buffer Pool里有一块内存区域(Log Buffer默认16M)来保存即将要写入日志文件的数据,可以节省磁盘 IO。
    SHOW VARIABLES LIKE 'innodb_log_buffer_size';
    刷盘是随机 I/O,而记录日志是顺序 I/O,顺序 I/O 效率更高。因此先把修改写入日志,可以延迟刷盘时机,进而提升系统吞吐。
2.磁盘结构
表空间(5大类): InnoDB存储引擎逻辑结构的最高层,所有的数据都在表空间中。
系统表空间(system tablespace).InnoDB 存储引擎默认有一个共享表空间(/var/lib/mysql/ibdata1),也叫系统表空间。
1、系统表空间包含 InnoDB 数据字典和双写缓冲区(Change Buffer、Undo Logs),如果没有指定file-per-table,也包含用户创建的表和索引数据。
    1、undo:回滚信息。
    2、数据字典: 由内部系统表组成,存储表和索引的元数据(定义信息)。
    3、双写缓冲: 写入页的副本(double write),一份是内存的double write,一份是磁盘上的double write。写入失效,就用页的副本来还原这个页,再应用redo log。
2、独占表空间(file-per-table tablespaces)
    可让每张表独占一个表空间。通过 innodb_file_per_table 设置,默认开启。
    SHOW VARIABLES LIKE 'innodb_file_per_table';
    开启后,每张表会开辟一个表空间,这个文件就是数据目录下的 ibd 文件(如/var/lib/mysql/gupao/user_innodb.ibd),存放表的索引和数据。
    但其他类的数据,如回滚(undo)信息,插入缓冲索引页、系统事务信息,二次写缓冲(Double write buffer)等还是存在共享表空间。
3、通用表空间(general tablespaces)
    通用表空间也是一种共享的表空间。用来存储不同数据库的表,数据路径和文件可以自定义。语法:
    create tablespace ts2673 add datafile '/var/lib/mysql/ts2673.ibd' file_block_size=16K engine=innodb;
    在创建表的时候可以指定表空间,用 ALTER 修改表空间可以转移表空间。
    create table t2673(id integer) tablespace ts2673;
    不同表空间的数据是可以移动的。
    删除表空间需要先删除里面的所有表:
    drop table t2673;
    drop tablespace ts2673;
4、临时表空间(temporary tablespaces)
    存储临时表的数据,包括用户创建的临时表,和磁盘的内部临时表,对应数据目录下的 ibtmp1 文件。当数据服务器正常关闭时,该表空间被删除,下次重新产生。

5、事务表空间(undo log tablespace)
    undo log(撤销日志或回滚日志)记录了事务发生之前的数据状态(不包括 select)。如果修改数据时出现异常,可以用 undo log 来实现回滚操作(保持原子性)。
    在执行 undo 的时候,仅仅是将数据从逻辑上恢复至事务之前的状态,而不是从物理页面上操作实现的,属于逻辑格式的日志。
    redo Log 和 undo Log 与事务密切相关,统称为事务日志。
    undo Log 的数据默认在系统表空间 ibdata1 文件中,因为共享表空间不会自动收缩,也可以单独创建 undo 表空间。
    show global variables like '%undo%';

3.3、InnoDB的后台线程

master thread:  负责刷新缓存数据到磁盘并协调调度其它后台进程。
IO thread:          为 insert buffer、log、read、write 进程。分别用来处理 insert buffer、重做日志、读写请求的 IO 回调。
purge thread:    回收 undo 页。
page cleaner thread: 刷新脏页。

3.4、binlog日志文件

MySQL 的 Server 有binlog的日志文件,它可被所有的存储引擎使用。它的文件内容是可以追加的,没有固定大小限制。
开启binlog功能后,可以把 binlog 导出成 SQL 语句,把所有的操作重放一遍,来实现数据的恢复。binlog 的另一个功能就是用来实现主从复制,它的原理就是从服务器读取主服务器的 binlog,然后执行一遍。

MySQL通信

1.通信类型

同步通信:
1、同步通信依赖于被调用方,受限于被调用方的性能。应用操作数据库,线程会阻塞,等待数据库的返回。
2、只能做到一对一,很难做到一对多的通信。
异步通信:
1、异步可以避免应用阻塞等待,但是不能节省 SQL 执行的时间。
2、如果异步存在并发,每一个 SQL 的执行都要单独建立一个连接,避免数据混乱。这样会给服务端带来巨大的压力(一个连接就会创建一个线程,线程间切换会占用大量 CPU 资源)。
异步通信编码复杂,所以一般不建议使用。如果要异步,必须使用连接池,排队从连接池获取连接而不是创建新连接。

2.连接方式:长连接或者短连接(一般接池用长连接)

短连接是操作完毕后,马上 close 掉。
长连接可以保持打开,减少服务端创建和释放连接的消耗,后面的程序访问的时候还可以使用这个连接。
长连接会消耗内存。长时间不活动的连接,MySQL 服务器会断开(默认 28800 秒,8小时)。
show global variables like 'wait_timeout';   -- 非交互式超时时间,如 JDBC 程序.
show global variables like 'interactive_timeout'; -- 交互式超时时间,如数据库工具.
SHOW PROCESSLIST;                                 --(root 用户)查看 SQL 的执行状态。
show global status like 'Thread%';                -- 查看 MySQL 当前多少个连接( 5.7 默认151,最大 16384).
        Threads_cached:缓存中的线程连接数。
        Threads_connected:当前打开的连接数。
        Threads_created:为处理连接创建的线程数。
        Threads_running:非睡眠状态的连接数,通常指并发连接数。

3.通信协议

3.1、Linux 服务器 Unix Socket。
    没指定-h 参数,用 socket 方式登录(省略了-S /var/lib/mysql/mysql.sock)。
    不用通过网络协议,也可以连接到 MySQL 的服务器,需要用到服务器上的一个物理文件(/var/lib/mysql/mysql.sock)。
    select @@socket;
    指定-h 参数,就用第二种方式,TCP/IP 协议。
    mysql -h192.168.8.211 -uroot -p123456
    编程语言的连接模块都是用 TCP 协议连接到 MySQL 服务器的
    如: mysql-connector-java-x.x.xx.jar。
3.2、命名管道(Named Pipes)和内存共享(Share Memory)的方式
    这两种通信方式只能在 Windows 上面使用,用得较少。

4.通信方式(MySQL 用了半双工)

单工:    在两台计算机通信的时候,数据的传输是单向不可逆的(只问不答)。
半双工: 在两台计算机之间,数据传输是双向可逆的,相互发送。但同一时间只能有一方在发送数据(整段的一问一答)。
全双工: 数据的传输是双向的,并且可以同时传输(相互分段传输)。
MySQL 为什么使用半双工的
要么是客户端向服务端发送数据,要么是服务端向客户端发送数据,这两个动作不能同时发生。
不管你的 SQL 语句有多大,都是一次性发送。用SQL生成了一个批量插入的语句,插入过多数据,或 where 条件 in 里面的值太多,须调整 MySQL 服务器配置 max_allowed_packet 参数的值(默认是 4M)否则会报错。

数据类型

UTF-8编码中一个汉字占三个字节

整型
名称字节位数大小
tinyint18255
smallint21665535(六万)
mediumint32416777215(一千六百万)
int4324294967295(42亿)
bigint818446744073709551615
浮点型
名称字节精度精度范围
float4精度范围7位左右
double8精确范围15位左右
字符串型
名称长度类型字节
char(n) 定长最大255字节
varchar(n)变长最大65535字节
tinytext变长最大255字节
text变长最大65535字节
mediumtext变长最大2^24-1字节
longtext变长最大2^32-1字节

 char和varchar类型

char列的长度固定为创建表时声明的长度为0~255,varchar的值是变长字符串,长度在0~65535之间。检索的时候,char列会删除尾部的空格而varchar则保留了这些空格。

时间类型
year1字节YYYY  范围:1901~2155
time3字节HH:MM:SS  如:19:26:32
date4字节YYYY-MM-DD 如:2010-03-14
timestamp4字节YYYY-MM-DD HH:MM:SS 特性:不赋值,该列会为自己赋当前的具体时间
datetime8字节YYYY-MM-DD HH:MM:SS 如:2010-03-14 19:26:32

 存储引擎

 组织和存取数据的一种机制。是一种软件。

Mysql存储引擎相关的Sql

查询数据库支持的存储引擎
show engines
查询默认存储引擎
select @@default_storage_engine;
查看正在使用的存储引擎
show variables like 'storage_engine%';
查看innodb的表有哪些
select table_schema,table_name,engine from information_schema.tables where engine='innodb';
查看myisam的表有哪些
select table_schema,table_name,engine from information_schema.tables where engine='myisam';
查看共享表空间
show variables like '%path%';
查看独立表空间
show variables like '%per_table%';

InnoDB 存储引擎(默认的存储引擎)

InnoDB 逻辑存储结构
MySQL的存储结构分为5级: 表空间、段、簇、页、行。
表空间(Table Space)
表空间是 InnoDB 存储引擎逻辑结构的最高层,所有的数据都存在表空间中。分为: 系统表空间、独占表空间、通用表空间、临时表空间、Undo 表空间。
段Segment
表空间是由各个段组成的,常见的段有数据段、索引段、回滚段等,段是一个逻辑的概念。一个 ibd 文件(独立表空间文件)里由很多个段组成。
创建一个索引会创建两个段,一个是索引段:leaf node segment,一个是数据段:non-leaf node segment。索引段管理非叶子节点的数据。数据段管理叶子节点的数据。
簇Extent
段(Segment)由很多的簇(区)组成,每个区的大小是 1MB(64个连续的页)。
页 Page
簇是由连续的页(Page)组成,一个簇中有64个连续的页(1MB/16KB=64)。页在物理上和逻辑上是连续的。
页是 InnoDB 存储引擎磁盘管理的最小单位,通过 innodb_page_size 设置。
一个表空间最多拥有 2^32 个页,默认一个页的大小为16KB,一个表空间最多存储 64TB 的数据。
操作系统中最小单位是页Page的内存页通常是4K。
SHOW VARIABLES LIKE 'innodb_page_size';
如果数据(主键)不是连续的,往已经写满的页中插入数据,会导致叶页面分裂:
行Row(具体我也不太懂)
InnoDB 存储引擎是面向行(row-oriented),数据按行存放。
    1、REDUNDANT Row Format。
    2、COMPACT Row Format(5.6 默认)。
InnoDB Plugin 支持的文件格式,新增两种行格式:
    1、DYNAMIC Row Format(5.7 默认)
    2、COMPRESSED Row Format
建表指定行格式
    CREATE TABLE tf1(c1 INT PRIMARY KEY) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
查看行格式:
SHOW TABLE STATUS LIKE 'student' \G;

InnoDB 特性

1、支持事务、行锁设计、外键、类似 Oracle 的非锁定读,即默认读取操作不会产生锁。
2、表数据进行整理来优化基于主键的查询(每张表是按主键的顺序进行存储的,没有显式地指定主键,会为每一行生成一个6字节的 ROWID,并以此作为主键)。
3、支持外键引用完整性约束。
4、出现故障后快速自动恢复(csr)。
5、用于在内存中缓存数据和索引的缓冲区池。
6、支持 B-tree、Full-text 等索引(5.6以上版本),不支持 Hash 索引。
7、跨平台可直接拷贝使用。
8、不保存表的具体行数,扫描表来计算有多少行。
9、DELETE 表时,是一行一行的删除。
10、.frm文件存放表结构数据;.ibd存放数据和索引。

MyISAM 存储引擎

1、不支持事务、表锁设计。不支持外键。
2、BLOB和TEXT列可以被索引。
3、对于AUTO_INCREMENT类型的字段,在MyISAM表中,可以和其他字段一起建立联合索引。
4、每个MyISAM表最大索引数是64,每个索引最大的列数是16。
5、每个MyISAM类型的表都有一个AUTO_INCREMENT的内部列,当INSERT和UPDATE操作的时候该列被更新,同时AUTO_INCREMENT列将被刷新。所以说,MyISAM类型表的AUTO_INCREMENT列更新比InnoDB类型的AUTO_INCREMENT更快。
6、支持 B-tree、Full-text 等索引,不支持 Hash 索引。
7、跨平台不可直接拷贝使用。
8、保存表的具体行数,不带where时,直接返回保存的行数。
9、DELETE 表时,先drop表,然后重建表。
10、可以把数据文件和索引文件放在不同目录。
     (1)frm文件:存储表的定义数据。
     (2)MYD文件:存放表具体记录的数据。
     (3)MYI文件:存储索引。

Memory 存储引擎

1、支持的数据类型有限制,比如:不支持TEXT和BLOB类型,只支持固定长度的字符串类型,VARCHAR会被自动存储为CHAR类型。
2、由于数据是存放在内存中,一旦服务器出现故障,数据都会丢失。
3、查询的时候,如果有用到临时表,而且临时表中有BLOB,TEXT类型的字段,那么这个临时表就会转化为MyISAM类型的表,性能会急剧降低。
4、存储引擎默认使用哈希 索引,而不是通常熟悉的 B+ 树索引。
5、如果一个内部表很大,会转化为磁盘表。
6、支持 B-tree、Hash 等索引,不支持 Full-text 索引。

对MyISAM和InnoDB总结

1、有where条件,count(*)两个存储引擎性能差不多。
2、不要使用全文索引,应当使用《索引外置》的设计方案。
3、事务影响性能,强一致性要求才使用事务。
4、不用外键,由应用程序来保证完整性。
5、不命中索引,InnoDB也不能用行锁。

索引

查看数据和索引的大小:
select CONCAT(ROUND(SUM(DATA_LENGTH/1024/1024),2),'MB') AS data_len,CONCAT(ROUND(SUM(INDEX_LENGTH/1024/1024),2),'MB') as index_lenfrom information_schema.TABLES where table_schema='gupao' and table_name='user_innodb';
存储的内容:
1、索引的键值。如在 name 上面创建一个索引,用 where name =JunSouth 的条件查询的时就会找到索引里面的 name 的键值。
2、数据的磁盘地址,因为索引的作用就是去查找数据的存放的地址(一般为主键)。
3、左右子节点的引用。

索引的类型

单列索引

一个索引只包含单个列,一个表中可以有多个单列索引。
聚集索引: 主键就是默认的聚集索引,它的数据的物理存储顺序和索引顺序一致,存储的物理顺序也不会改变。没有主键就会自动建立。(InnoDB会自动在表的主键上创建索引,数据结构使用B Tree。)
二级索引(主键以外的索引): 叶子节点存储的是索引和主键,找到索引后,得到对应的主键,再到聚集索引中找主键对应的数据。

组合索引

多个字段组合上创建的索引,只有在查询条件中使用了这些字段的左边字段时索引才会被使用,使用组合索引时遵循最左前缀集合。

全文索引

全文索引,在MyISAM引擎上使用,在CHAR,VARCHAR,TEXT类型字段上使用全文索引,通过其中的某个关键字等,找到该字段所属的记录行。解决where name like "%word%"这类针对文本的模糊查询效率较低的问题。
MyISAM和InnoDB 支持全文索引, 全文索引的使用: select * from fulltext_test where match(content) against('JunSouth' IN NATURAL LANGUAGE MODE)。

空间索引

对空间数据类型的字段建立的索引,MySQL中的空间数据类型有四种,GEOMETRY、POINT、LINESTRING、POLYGON。在创建空间索引时,使用SPATIAL关键字。引擎为MyISAM,创建空间索引的列,必须将其声明为NOT NULL。

回表

普通索引结构中包含聚簇索引的值(一般就是主键id)。select 所需获得列中有非索引列,那首先查询普通索引的B+树,再查询聚集索引的B+树。最后得到那条行记录。多扫了一遍索引树,多耗费了CPU、IO、内存等。

回表解决方案

1、查主键。
2、覆盖索引:索引包含或者说覆盖所有需要查询的字段的值,就不用回表查询。
3、建组合索引。

B+树索引的实现

磁盘IO与预读

磁盘读取数据是机械运动,分为寻道时间、旋转延迟、传输时间三个部分。
寻道时间: 磁臂移动到指定磁道所需要的时间,一般在5ms以下。
旋转延迟: 磁盘转速,磁盘7200转为每分钟能转7200次,就是1秒钟能转120次,旋转延迟就是1/120/2 = 4.17ms。
传输时间: 从磁盘读出或将数据写入磁盘的时间,一般在零点几毫秒,可以忽略不计。一次磁盘IO的时间约5+4.17 = 9ms。一台500 -MIPS的机器每秒可以执行5亿条指令,执行一次IO的时间可以执行40万条指令,每次9毫秒的时间,显然是个灾难。计算机操作系统做了一些优化,当一次IO时,把当前磁盘地址相邻的数据也都读取到内存缓冲区内。计算机访问一个地址的数据的时候,与其相邻的数据也会很快被访问到。每一次IO读取的数据为一页(page)。一般为4k或8k(具体大小跟操作系统有关),我们读取一页内的数据时候,发生了一次IO。

索引实现的方法

先介绍下二叉树

1、节点存数据。
2、一个节点最多两个向下指针。

满二叉树

再介绍下B-树

1、节点存数据。
2、一个节点由有多个向下指针。
3、叶节点不存数据。

B+树(存储引擎默认的数据结构)

1、节点不存数据只存指针信息,数据都在叶上。
2、一个节点由有多个向下指针。
3、叶子节点两两相连且有序排列。

b+树的查找过程

从上往下逐层读取节点,每层读取就是一次IO,二分查找法直到找到数据。如果没有索引,将全部数据一点点读入内存中,显然成本非常高。

b+树性质

1、O次数取决于b+树的高度h,数据为N,磁盘块的数据项的数量为m,则有h=㏒(m+1)N,当数据量N一定的情况下,m越大,h越小;而m = 磁盘块的大小 / 数据项的大小,磁盘块的大小也就是一个数据b+树的查找过程页的大小,是固定的,如果数据项占的空间越小,树的高度越低。所以即索引字段要尽量的小,B+树要求把数据放到叶子节点。
2、当b+树的数据项是复合的数据结构,比如(name,age,sex)的时候,是按照从左到右的顺序来建立搜索树的,有序。
当(张三,20,F)这样的数据来检索的时候,b+树会优先比较name来确定下一步的所搜方向,如果name相同再依次比较age和sex,最后得到检索的数据。当(20,F)这样的没有name的数据来的时候,b+树就不知道下一步该查哪个节点,索引失效。

为什么索引结构默认使用B+Tree,不用Hash、二叉树、红黑树?

B+tree: 因为B树节点都会存数据,导致在节点中能保存的指针数量少,增加树的高度、IO,查询性能变低。
Hash: 虽然可以快速定位,但是没有顺序,IO复杂度高。
二叉树: 树的高度不均匀,不能自平衡,查找效率跟数据有关(树的高度)并且IO代价高。
红黑树: 树的高度随着数据量增加而增加,IO代价高。

为什么用自增长主键为索引?

主键连续,在插入过程减少页分裂,即使页分裂,也只会分裂很少一部分。并且减少数据的移动,每次插入都是插入到最后。总之就是减少分裂和移动的频率。

mysql默认存储引擎innodb只显式支持B-Tree(从技术上来说是B+Tree)索引,
频繁访问的表innodb会透明建立自适应hash索引,在B树索引基础上建立hash索引,显著提高查找效率,对于客户端是不可控制的。

哈希索引

哈希索引是采用哈希算法,把键值换成哈希值,检索时只需一次哈希算法即可立刻定位到相应的位置,速度快。

  • 如果是等值查询,哈希索引很快,只经过一次算法即找到相应的键值;如果键值不唯一,就先找到该键所在位置,扫描链表往,直到找到相应的数据。
  • 如果是范围查询检索,无用。like ‘%XX%’ 这样的部分模糊查询(这种部分模糊查询,是范围查询)。
  • 哈希索引不能用索引排序。
  • 哈希索引不支持多列联合索引的最左匹配规则。
  • 有大量重复键值情况下,哈希索引的效率也是极低的,存在所谓的哈希碰撞问题

建索引的几大原则

1、最左前缀匹配原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,a = 1 and b = 2 and c > 3 and d = 4 建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。
2、=和in可以乱序,a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式。
3、尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,唯一键的区分度是1,一些状态、性别字段可能在大数据面前区分度就是0,一般join的字段我们都要求是0.1以上,即平均1条扫描10条记录。
4、索引列不能参与计算,保持列"干净",from_unixtime(create_time) = ’2014-05-29’就用不到索引,b+树中存的都是数据表中的字段值,进行检索时,要把所有元素都应用函数才能比较,所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。
5、尽量的扩展索引,不要新建索引。已有a的索引,现在要加(a,b)的索引,只要修改原来的索引即可。

慢查询优化基本步骤

1、先运行看看是否真的很慢,注意设置SQL_NO_CACHE。
2、where条件单表查,锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起,单表每个字段分别查询,看哪个字段的区分度最高。
3、explain查看执行计划,是否与1预期一致(从锁定记录较少的表开始查询)。
4、order by limit 形式的sql语句让排序的表优先查。
5、了解业务方使用场景。
6、加索引时参照建索引的几大原则。
7、观察结果,不符合预期继续从1分析。

Sql

创建索引1 create index 索引名 on 表名(字段)
create index account_index on test2(id); 
创建索引2 取字段前三位创建索引(仅限于char和varchar)
create index na_index on test2 (name(3));
创建索引3  create table 表名 add index 索引名(字段名)
alter table test2 add index name_index(name);
 
查看索引 show index from table_name(表名)
show index from test2;
 
删除索引 alter table 表名 drop index 索引名
alter table test2 drop index name_index;
 
创建唯一索引
create unique index name_index on test2(name);
alter table test2 add unique index phone_index(phone);
 
创建组合索引
create index name_phone_addres_index on test2 (name,phone,address);
 
创建全文索引1
alter table test2 add fulltext index na_index(name);
创建全文索引2-- create fulltext index nam_index on test2(name); 
 
show index from test2;

分析查询语句

explain SQL;可知道MySQL是如何处理SQL语句,分析查询语句或是表结构的性能瓶颈。
explain select * from user where id = 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)
 
各列的含义如下:
id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.
select_type: SELECT 查询的类型.
table: 查询的是哪个表
partitions: 匹配的分区
type: join 类型
possible_keys: 此次查询中可能选用的索引
key: 此次查询中确切使用到的索引.
ref: 哪个字段或常数与 key 一起被使用
rows: 显示此查询一共扫描了多少行. 这个是一个估计值.
filtered: 表示此查询条件所过滤的数据的百分比
extra: 额外的信息

数据库管理

创建用户
create user user1 identified by 'pass';   -- identified by 会将纯文本密码加密作为散列值存储
在所有库上分配全部权限
grant all privileges on *.* 'user1'@'localhost' identified by 'pass'; -- on *.* :所有库
撤销权限
revoke all privileges on *.* from 'username'@'localhost';
在test1库上分配全部权限
grant all privileges on test1.* to 'username'@'localhost' identified by 'password';
在test1库上分配select和update命令
grant select,update on test1.* to 'user1'@'localhost' identified by 'pass';
创建用时户分配权限
grant [权限1,权限2...] on 某库.某表 to 新用户名@'主机名/IP地址' identified by '密码';
 
查看权限
show grants for '用户'@'IP地址';
删除权限
revoke [权限1,权限2...] on 某库.某表 from '用户名'@'主机名/IP';
修改用户名
rename user '用户名'@'IP地址' to '新用户名'@'IP地址';
修改密码
set password for '用户名'@'IP地址' = Password('新密码');
删除用户
drop user '用户名'@'IP地址';
刷新权限:
flush privileges;

查询Mysql数据库的性能参数

show status like 'Connections'  连接mysql服务器的次数
show status like 'Uptime'  mysql服务器的上线时间
show status like 'Slow_queries'  慢查询的次数
show status like 'Com_select'  查询操作的次数
show status like 'Com_insert'  插入操作的次数
show status like 'Com_update'  更新操作的次数
show status like 'Com_delete'  删除操作的次数

分区

将大表,根据条件分割成若干个小表。

分区类型

range
基于属于一个给定连续区间的列值,把多行分配给分区。这些区间要连续且不能相互重叠,使用values less than操作符来进行定义。

list
基于列值匹配一个离散值集合中的某个值来进行选择,而非连续的。list分区通过使用"partition by list(列值)"来实现。

hash
将数据均匀地分布到预先定义的各个分区中,保证各分区的数据量大致都是一样的。

key
key分区和HASH分区相似,不同之处在于HASH分区使用用户定义的函数进行分区,支持字符串HASH分区,key分区使用MySQL数据库提供的函数进行分区,这些函数基于与PASSWORD()一样的运算法则。

range分区
create table test8(
    id int primary key auto_increment,     
    name varchar(30),    
    phone int(3),
    address varchar(50)
)
partition by range(id)(
  partition p1 values less than(10),
  partition p2 values less than(100),
);
 
list分区
create table user5 (  
     id int UNSIGNED not null AUTO_INCREMENT primary key,  
     name varchar(30) not null default '' ,
     user_type int not null
)  
partition by list (id ) (  
     partition p0 values in (0,4,8,12), 
     partition p1 values in (1,5,9,13)
);  
 
hash分区
create table user(  
     id int unsigned not null auto_increment primary key,  
     username varchar(30) not null default '',  
     email varchar(30) not null default ''  
)  
partition by hash (id) partitions 3 (  
     partition p0 ,  
     partition p1,  
     partition p2
);  
 
key分区
create table user(  
     id INT UNSIGNED not null auto_increment primary key,  
     name varchar(30) not null default '',  
     email varchar(30) not null default ''  
)  
partition by key(id) partitions 4 (  
     partition p0,  
     partition p1,  
     partition p2,  
     partition p3
);
 
添加分区
alter table test8 add partition(
    partition p3 values less than maxvalue
);
删除分区
alter table test8 drop partition p3;
查看分区
select partition_name,partition_expression ,partition_description ,table_rows 
from information_schema.partitions where table_schema=schema() 
and table_name='test8';

触发器

名称                    介绍
trigger_name       标识触发器名称,用户自行指定。
trigger_time         标识触发时机,取值为 before 或 after。
trigger_event       标识触发事件,取值为 insert、update 或 delete。
tbl_name             标识建立触发器的表名,即在哪张表上建立触发器。
trigger_stmt         触发器程序体,可以是一句SQL语句,或者用 begin 和 end 包含的多条语句。

六种触发器

before insert、before update、before delete、
after insert、after update、after delete。

Sql

语法规则
DELIMITER 
||
create trigger 触发器名 before|after 触发事件 ON 表名 for each row 
begin 
    执行语句列表
end 
||
DELIMITER;
 
DELIMITER 
 || 
 create trigger demo before delete on users for each row 
 begin 
 insert into logs values(NOW()); 
 end 
 || 
 Query OK, 0 rows affected (0.06 sec)
DELIMITER ;


Sql案例
创建用户users表
create table `users` (
  `id` int(11) unsigned not null AUTO_INCREMENT,
  `name` varchar(255) character SET utf8mb4 default null,
  `add_time` int(11) default null,
  primary key (`id`),
  key `name` (`name`(250)) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=1000001 default CHARSET=latin1;
 
创建日志logs表
create table `logs` (
  `Id` int(11) not null AUTO_INCREMENT,
  `log` varchar(255) default null comment '日志说明',
  primary key (`Id`)
) ENGINE=InnoDB default CHARSET=utf8mb4 comment='日志表';
 
创建触发器
DELIMITER $ 
create trigger user_log after insert on users for each row 
begin 
declare s1 varchar(40)character set utf8; 
declare s2 varchar(20) character set utf8; 后面发现中文字符编码出现乱码,这里设置字符集 
set s2 = " is created"; 
set s1 = concat(new.name,s2);   -- 函数concat可以将字符串连接,new 用来表示将要(before)或已经(after)插入的新数据,new.columnName(columnName为相应数据表某一列名)
insert into logs(log) values(s1);
end $
DELIMITER ;
 
显示触发器
show triggers ;
 
删除触发器
drop trigger name

储存过程

将重复性高的一些SQL,封装到一个存储过程中,简化了对这些SQL的调用。

参数

in(输入)、out(输出)、inout(输入输出)参数,有多个参数用","分割开。
in:  参数的值必须在调用存储过程时指定,在存储过程中修改该参数的值不能被返回,为默认值。
out:  该值可在存储过程内部被改变,并可返回。
inout:  调用时指定,并且可被改变和返回。

Sql

语法
DELIMITER
||
create procedure 过程名([[in|out|inout] 参数名 数据类型[,[in|out|inout] 参数名 数据类型…]]) [特性...] 过程体 
DELIMITER ||
create procedure myproc(out s int)
  begin
    select count(*) into s from students; 
  end
||
DELIMITER ;


Sql案例
in 参数
DELIMITER ||
create procedure in_param(in p_in int)
  begin
   select p_in;
   set p_in=2;
   select p_in;
  end;
||
DELIMITER;
调用
set @p_in=1;
call in_param(@p_in);
select @p_in;
 
out 参数
DELIMITER ||
create procedure out_param(out p_out int)
  begin
    select p_out;
    set p_out=2;
    select p_out;
  end;
||
DELIMITER;
调用
set @p_out=1;
call out_param(@p_out);
select @p_out;
 
inout 参数
DELIMITER ||
create procedure inout_param(inout p_inout int)
  begin
    select p_inout;
    set p_inout=2;
    select p_inout;
  end;
||
DELIMITER;
调用
set @p_inout=1;
call inout_param(@p_inout);
select @p_inout;
 
查询存储过程
select name from mysql.proc where db='数据库名';
select routine_name from information_schema.routines where routine_schema='数据库名';
show procedure status where db='数据库名';
查看存储过程详细信息
show create procedure 数据库.存储过程名;
 
存储过程的删除
drop procedure [过程1[,过程2…]]

慢查询日志发现有效率问题的SQL

1、查看MySQL是否开启慢查询日志: show variables like 'slow_query_log';
2、设置没有索引的记录到慢查询日志: set global log_queries_not_using_indexes=on;
3、查看超过多长时间的sql进行记录到慢查询日志:  show variables like 'long_query_time';
4、开启慢查询日志: set global slow_query_log=on;
5、设置超时时间: Set global long_query_time=5; --超过5s的语句才记录日志 
6、查看慢查询日志的位置: show variables like 'slow%';

MySql优化建议

1、排除缓存干扰(8.0以下): NoCache SQL语句;通过 explain 和 show profiles分析,procedure analyse()让MySQL给出建议。

2、优化关联查询:以小表驱动大表。子查询换成join,因为join在MySQL不用在内存中创建临时表,减少join语句中的循环次数。

3、选择合适的索引列,选择在where、group by、order by、on从句中出现的列作为索引项,对于离散度不大的列没有必要创建索引。判断离散程度(越大越离散): select count(distinct ziduan1),count(distinct ziduan2) from tablename;
   
4、避免回表。

5、越小的列会越快,date比datetime快,tinyint比smallint比mediumint比int快。

6、尽量用 not null。

7、用enum代替varchar,enum类型快、紧凑。

8、固定长度表(不包含varchar,text,blob等不定长字段)会更快。

9、拆分大的(耗时较长的) delete 或 insert 语句,这两个操作会锁表。

10、MySQL 在 Windows 下不区分大小写,在 Linux 下默认区分大小写。

11、建议不用外键, 数据的完整性靠程序来保证。

12、单条记录大小禁止超过8k,字段不要太多,内容过大的字段需单独存到一张表。

13、左链接,右表的条件列加索引;右链接,左表的条件列加索引。

14、order by 索引(最佳左前缀)。

15、group by 索引(最佳左前缀,能用where就不用了having)。

16、优化 limit 分页: 偏移量大的时,如: limit 10000 20这样的查询,要查询10020条后返回20条记录,前10000条都被抛弃,这样的代价非常高。
    修改前:
      select id,name from user order by title limit 50,5;
    修改后:
      select user.id,user.name from user inner join (select id from user order by title limit 50,5) as tmp using(id);
    优化 union: 除非确实需要服务器去重,否则就一定要使用 union all ,
    如果没有ALL关键字,MySQL会给临时表加上DISTINCT选项,这会导致整个临时表的数据做唯一性检查,这样做的代价非常高。

索引不起作用

1、like,前导模糊查询不能使用索引。(在建立索引时用reverse(columnName)这种方法进行处理)。
2、数据区分度不大的字段不宜使用索引。
3、复合索引最左前缀不满足。
4、MySQL规定: 函数计算索引失效。
      失效: select id from user where id + 1 = 10000;
      有效: select id from user where id = 9999;
5、隐式类型转换
      select * from user where id = 1;如果id是字符类型的,1是数字类型的,索引失效(当于加CAST( id AS signed int)函数)。
6、隐式字符编码转换
      两个表的字符集不一样,一个是utf8mb4,一个是utf8,相当于加了CONVERT(id USING utf8mb4)函数,索引失效。
7、当用 or 时,查询条件中只有or关键字,且or前后的两个条件中的列都有索引时,查询中才使用索引。
8、不要在 SQL 中用双引号。
9、使用 <> 、not in 、not exist、!= 会使索引失效。
10、不要用null直接与运算符比较,应用 is null 或 is not null 进行比较,或用 isnull 函数。

buffer pool(缓存池)

缓存最热的数据页(data page)与索引页(index page)。

change buffer(缓存变更记录)

InooDB会将更新操作缓存在change buffer中,下次访问这个数据页的时直接读取。将change buffer更新到数据库,得到最新结果为merge。除了访问这个数据页会触发merge外,系统会定期merge。如数据库正常关闭时。change buffer对更新性能的提升明显的。对于写多读少的业务来说,写完马上访问到的概率较小,此时change buffer的使用效果最好,常见账单类、日志类的系统。
但写入后马上查询,随机访问IO的次数不会减少,增加了change buffer的维护代价change buffer反而起到了副作用。
change buffer只限于普通索引,而能用于唯一索引。对唯一索引,必须先查表才能改,所以必须先把数据从磁盘读入内存。

优化数据库参数

MySQL服务的配置参数都在my.cnf或my.ini
thread_pool_size: 如果主引擎(primary storage engine)为InnoDB,thread_pool_size最佳设置可能在16和36之间,最常见的优化值倾向于24到36。
thread_pool_stall_limit: 用处理被阻塞和长时间运行的语句,确保服务器不完全被阻塞。设置过长会导致线程被阻塞,引起性能问题。
tmp_table_size: 设置临时表的大小,例如做order by,GROUP BY操作生成的临时表。调高该值,将增加heap表的大小,可达到提高联接查询速度的效果。
innodb_buffer_pool_size: 缓存区域大小。
innodb_change_buffer_max_size: 设置缓存区的change buffer的大小,设为50的时候(25~50),表示change buffer最多只能占用buffer pool的50%。
key_buffer_size:索引缓冲区大小
table_cache:能同时打开表的个数
query_cache_size和query_cache_type:前者是查询缓冲区大小,后者是前面参数的开关,0表示不使用缓冲区,1表示使用缓冲区,但可以在查询中使用SQL_NO_CACHE表示不要使用缓冲区,2表示在查询中明确指出使用缓冲区才用缓冲区,即SQL_CACHE.
sort_buffer_size:排序缓冲区.

硬优化

1.配置多核心和频率高的cpu,多核心可以执行多个线程.
2.配置大内存,提高内存,即可提高缓存区容量,因此能减少磁盘I/O时间,从而提高响应速度.
3.配置高速磁盘或合理分布磁盘:高速磁盘提高I/O,分布磁盘能提高并行操作的能力.

分库分表

数据库压力过大,高峰期系统性能会降低,对系统做分库分表 + 读写分离,把一个库拆分为多个库,部署在多个数据库服务上,这时作为主库承载写入请求。然后每个主库都挂载至少一个从库,由从库来承载读请求。

缓存集群

数据库单机每秒承载的并发就在几千的数量级。高并发架构都有缓存系统。
单机承载的并发量都在每秒几万,甚至每秒数十万,对高并发的承载能力比数据库系统要高出一到两个数量级。通过缓存集群,就可以用更少的机器资源承载更高的并发。

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值