MySQL数据库3
1. 存储过程
-
创建存储过程
- 语法格式
CREATE PROCEDURE <==创建存储过程命令 sp_name([ proc_parameter[,proc_parameter...]]) <==命名存储过程及参数 routime_body - 说明
- routime_body
符合规定的SQL过程语句，可以使用复合语法(例如BEGIN…END)，复合语句可以包含声明，循环和其他控制结构语句 - proc_parameter
参数内容分为[IN|OUT|INOUT] parameter_name type
其中IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出；param_name表示参数名称；type表示参数的类型
- routime_body
- 语法格式
-
查看存储过程列表
SHOW PROCEDURE STATUS
此内容在mysql自建数据库information_schema.ROUTINES表中 -
查看存储过程定义
SHOW CREATE PROCEDURE sp_name -
调用存储过程:
- 有参数调用
CALL sp_name([ proc_parameter[,proc_parameter…]]) - 无参数调用
CALL sp_name
说明:当无参时,可以省略"()",当有参数时,不可省略"()” - 跨数据库调用
存储过程定义是依赖于数据库的，在哪个数据库创建，就在哪个数据库使用，在其他数据库使用时，要加上数据库名称MariaDB [db2]> CALL hellodb.showtime;
- 有参数调用
-
存储过程修改
ALTER语句修改存储过程只能修改存储过程的注释等无关紧要的东西,不能修改存储过程体,所以要修改存储过程,方法就是删除重建 -
删除存储过程:
DROP PROCEDURE [IF EXISTS] sp_name -
示例
- 创建无参存储过程:
MariaDB [hellodb]> delimiter // <==修改定界符为// MariaDB [hellodb]> CREATE PROCEDURE <==创建存储过程命令 -> showTime() <==定义函数名称及参数 -> BEGIN <==routime_body复合语法 -> SELECT now(); <==SQL过程语句 -> END// MariaDB [hellodb]> delimiter ; MariaDB [hellodb]> CALL showTime; <==调用存储过程 - 创建含参存储过程:只有一个IN参数
MariaDB [hellodb]> delimiter // MariaDB [hellodb]> CREATE PROCEDURE -> selectById(IN uid SMALLINT UNSIGNED) <==定义存储过程名，为输入参数名为uid 类型 -> BEGIN -> SELECT * FROM students WHERE stuid= uid; -> END// MariaDB [hellodb]> delimiter ; MariaDB [hellodb]> call selectById(2); - 在存储过程中定义局部变量
MariaDB [hellodb]> delimiter // MariaDB [hellodb]> CREATE PROCEDURE dorepeat(n INT) -> BEGIN -> SET @i= 0; <==定义变量@i -> SET @sum = 0; <==定义变量@i -> REPEAT SET @sum = @sum+@i; SET @i= @i+ 1; <==REPEAT为重复的意思，类似循环，与结尾的REPEAT成对 -> UNTIL @i> n END REPEAT; <==UNTIL定义结束时间 -> END// MariaDB [hellodb]> delimiter ; MariaDB [hellodb]> CALL dorepeat(100); MariaDB [hellodb]> SELECT @sum; <==查看返回值@sum - 创建含参存储过程:包含IN参数和OUT参数，OUT可以将参数传到mysql中使用
说明:创建存储过程deleteById,包含一个IN参数和一个OUT参数.调用时,传入删除的ID和保存被修改的行数值的用户变量@Line,select@Line;输出被影响行数MariaDB [hellodb]> delimiter // MariaDB [hellodb]> CREATE PROCEDURE -> deleteById(IN uid SMALLINT UNSIGNED, OUT num SMALLINT UNSIGNED) -> BEGIN -> DELETE FROM students WHERE stuid= uid; -> SELECT row_count() into num; <==row_count()为系统函数，记录删除行次数 -> END// MariaDB [hellodb]> delimiter ; MariaDB [hellodb]> call deleteById(2,@Line); MariaDB [hellodb]> SELECT @Line;
- 创建无参存储过程:
-
存储过程优势:
- 存储过程把经常使用的SQL语句或业务逻辑封装起来,预编译保存在数据库中,当需要时从数据库中直接调用,省去了编译的过程
- 提高了运行速度
- 同时降低网络数据传输量
-
存储过程与自定义函数的区别:
- 存储过程实现的过程要复杂一些,而函数的针对性较强
- 存储过程可以有多个返回值,而自定义函数只有一个返回值
- 存储过程一般独立的来执行,而函数往往是作为其他SQL语句的一部分来使用
-
流程控制
存储过程和函数中可以使用流程控制来控制语句的执行- IF：用来进行条件判断。根据是否满足条件，执行不同语句
- CASE：用来进行条件判断，可实现比IF语句更复杂的条件判断
- LOOP：重复执行特定的语句，实现一个简单的循环
- LEAVE：用于跳出循环控制
- ITERATE：跳出本次循环，然后直接进入下一次循环
- REPEAT：有条件控制的循环语句。当满足特定条件时，就会跳出循环语句
- WHILE：有条件控制的循环语句
2. 触发器（trigger）
-
概念
触发器的执行不是由程序调用，也不是由手工启动，而是由事件（对表的增、删、改）来触发、激活从而实现执行 -
创建触发器
- 语法格式
CREATE [DEFINER = { user | CURRENT_USER }] TRIGGER trigger_name trigger_time <==触发器的时间 trigger_event <==触发器的事件 ON tbl_name <==在那张表上 FOR EACH ROW <==针对那一行 trigger_body <==触发动作 - 说明
- trigger_name：触发器的名称
- trigger_time：{ BEFORE | AFTER }，表示在事件之前或之后触发
- trigger_event:：{ INSERT |UPDATE | DELETE }，触发的具体事件
- tbl_name：该触发器作用在表名
- 语法格式
-
查看触发器
- 在触发器创建数据库内输入
SHOW TRIGGERS - 查询系统自建表information_schema.triggers的方式指定查询条件，查看指定的触发器信息。
MariaDB [db2]> use information_schema; Database changed MariaDB [information_schema]> SELECT * FROM triggers WHERE trigger_name='trigger_student_count_insert'\G
- 在触发器创建数据库内输入
-
删除触发器
DROP TRIGGER trigger_name; -
示例
- 创建两个表student_info和student_count，给student_count设置一个初始值0
'1. 创建表student_info，存放id和姓名，主键为id' MariaDB [db2]> CREATE TABLE student_info( -> stu_id INT(11) NOT NULL AUTO_INCREMENT, -> stu_name VARCHAR(255) DEFAULT NULL, -> PRIMARY KEY (stu_id) -> ); '2. 创建表 student_count，汇总学生数' MariaDB [db2]> CREATE TABLE student_count( -> student_count INT(11) DEFAULT 0 -> ); 3. '为表 student_count，写入一个初始值0' MariaDB [db2]> INSERT INTO student_count VALUES(0); - 创建触发器，在向学生表INSERT数据时，学生数增加，DELETE学生时，学生数减少
1. 增加学员触发器 MariaDB [db2]> CREATE TRIGGER <==创建命令 -> trigger_student_count_insert <==触发器名称 -> AFTER INSERT <==触发时间 -> ON student_info FOR EACH ROW <==作用在表student_info的每一行 -> UPDATE student_count SET student_count=student_count+1; <==触发条件 MariaDB [db2]> CREATE TRIGGER trigger_student_count_delete -> AFTER DELETE -> ON student_info FOR EACH ROW -> UPDATE student_count SET student_count=student_count-1; - 增加、删除学员记录，验证触发器效果
'1. 增加两条学员信息' MariaDB [db2]> insert student_info values(1,'wang'); Query OK, 1 row affected (0.00 sec) MariaDB [db2]> insert student_info values(2,'mage'); Query OK, 1 row affected (0.01 sec) '2. 查看学员统计表是否自动增加' MariaDB [db2]> select * from student_count; +---------------+ | student_count | +---------------+ | 2 | +---------------+ '3. 删除学员信息，达成触发条件' MariaDB [db2]> delete from student_info; Query OK, 2 rows affected (0.00 sec) '4. 查看触发器效果' MariaDB [db2]> select * from student_count; +---------------+ | student_count | +---------------+ | 0 | +---------------+
- 创建两个表student_info和student_count，给student_count设置一个初始值0
3. MySQL用户和权限管理
3.1 MyAQL用户
-
授权表存放位置
元数据数据库mysql中系统授权表
db, host, user
columns_priv, tables_priv, procs_priv, proxies_priv -
创建用户：
- 命令：CREATE USER
- 默认权限：USAGE
- 语法格式：
CREATE USER 'USERNAME'@'HOST' [IDENTIFIED BY 'password']； - 示例
创建用户v9，允许此用户在192.168.50.网段的主机登录本机的数据库，密码为123456MariaDB [db2]> CREATE USER <==创建用户命令 'v9'@'192.168.50.%' <==定义用户名，允许登录主机IP IDENTIFIED BY '123456'; <==创建密码，IDENTIFIED BY为定义密码参数
-
用户重命名：
- 命令
RENAME USER - 语法格式
RENAME USER old_user_name TO new_user_name
- 命令
-
删除用户：
- 方法1，用户控制命令（在线连接的用户如果没有任何操作也可以删除）
- 命令
DROP USER - 语法格式
DROP USER ‘USERNAME’@'HOST‘ - 示例：删除安装系统时的匿名用户
MariaDB [db2]> DROP USER ''@'localhost'
- 命令
- 方法2，对表进行操作，删除对应行，不会立即生效，需要重新载入‘ FLUSH PRIVILEGES’
MariaDB [db2]> DELETE FROM mysql.user WHERE user='v9' AND host='192.168.50.100'; MariaDB [db2]> FLUSH PRIVILEGES <==如果未生效，需要重复文件
- 方法1，用户控制命令（在线连接的用户如果没有任何操作也可以删除）
-
修改密码：
-
方法一：用户控制命令
- 语法格式
SET PASSWORD FOR ‘user’@‘host’ = PASSWORD(‘password’); - 示例
MariaDB [(none)]> SET PASSWORD FOR 'v9'@'192.168.50.100'=password('123456')
- 语法格式
-
方法二：表操作命令
- 语法格式
UPDATE mysql.user SET password=PASSWORD(‘password’) WHERE clause; - 示例
此方法如果没有立即生效，需要执行下面指令：MariaDB [(none)]> UPDATE mysql.user SET password=password('123') WHERE host='hai7-8';mysql> FLUSH PRIVILEGES;
- 语法格式
-
方法三：登录mysql时，命令行命令
- 语法格式
mysqladmin -u root -poldpassword password ‘newpass‘ - 示例
-p后为旧密码，这里旧密码为空，password后为新密码 [root@hai7-8 yum.repos.d]$mysqladmin -uroot -p'' password '123456' Enter password: <==输入旧密码
- 语法格式
-
-
忘记管理员密码的解决办法：
- 修改配置文件，子[mysqld]块组下，加入
skip-grant-tables，重新启动服务
–skip-grant-tables <==忽略授权表，也就是登录不检查密码
–skip-networking <==忽略网络功能，即断开网络，避免其他用户远程登录 - 重启服务后，进入mysql后，使用UPDATE命令修改管理员密码
MariaDB [(none)]> update mysql.user set password=''; <==实验环境，这里就设为空密码了 MariaDB [(none)]> FLUSH PRIVILEGES; <==将修改生效 - 关闭mysqld进程，移除配置文件中
skip-grant-tables项，重启服务
- 修改配置文件，子[mysqld]块组下，加入
3.2 MyAQL权限管理
-
权限类别
- 管理类
权限 描述 CREATE TEMPORARY TABLES 创建临时表权限 CREATE USER 创建用户权限 FILE 文件访问权限 SUPER 执行kell线程权限 SHOW DATABASES 查看数据库权限 RELOAD 执行flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, reload等命令的权限 SHUTDOWN 关闭数据库权限 REPLICATION SLAVE 复制权限 REPLICATION CLIENT 复制权限 LOCK TABLES 锁表权限 PROCESS 查看进程权限 - 程序类：FUNCTION、PROCEDURE、TRIGGER
权限 描述 CREATE 创建数据库、表或索引权限 ALTER 更改表权限 DROP 删除数据库或表权限 EXCUTE 执行存储过程权限 - 数据库和表级别：DATABASE、TABLE
权限 描述 ALTER 更改表权限 CREATE 创建数据库、表或索引权限 CREATE VIEW 创建视图权限 DROP 删除数据库或表权限 INDEX 索引权限 SHOW VIEW 查看视图权限 GRANT OPTION 能将自己获得的权限转赠给其他用户 - 数据操作：
权限 描述 SELECT 查询权限 INSERT 插入权限 DELETE 删除数据权限 UPDATE 更新权限 - 字段级别：
权限 描述 SELECT(col1,col2,…) 指定字段查询权限 UPDATE(col1,col2,…) 指定字段更新权限 INSERT(col1,col2,…) 指定字段插入权限 - 所有权限：ALL PRIVILEGES 或ALL
3.3 授权操作
参考：https://dev.mysql.com/doc/refman/5.7/en/grant.html
- 授权指令 GRANT
-
语法格式
GRANT priv_type[(column_list)],… ON [object_type] priv_levelTO ‘user’@‘host’ [IDENTIFIED BY ‘password’] [WITH GRANT OPTION]; -
说明
- priv_type
权限类别: 例如ALL [PRIVILEGES] - (column_list)
针对于哪一列进行授权 - ON：表示将在哪个对象上实施授权
- object_type
实施对象，要指明库名（TABLE| FUNCTION | PROCEDURE） - priv_level
权限级别: *(所有库) | *.* | db_name.* | db_name.tbl_name| tbl_name(当前库的表) | db_name.routine_name(指定库的函数,存储过程,触发器) - [IDENTIFIED BY ‘password’]
设置密码，创建用户授权一起操作时使用 - with_option（授权的特殊选项）
- GRANT OPTION：运行将权限授权给他人
- MAX_QUERIES_PER_HOUR count：限定在每小时可以查询的次数
- MAX_UPDATES_PER_HOUR count：最多更新多少次
- MAX_CONNECTIONS_PER_HOUR count：最多连接次数
- MAX_USER_CONNECTIONS count：授权用户的最大连接数
- priv_type
-
示例
授权用户v9@'192.168.50.100’可以对 hellodb数据库中students表查询id和name的权限MariaDB [hellodb]> grant select(stuid,name) on hellodb.students to v9@'192.168.50.100' '在192.168.50.100主机上使用账号v9查看授予的权限' MariaDB [hellodb]> desc students; +-------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+----------------+ | StuID | int(10) unsigned | NO | PRI | NULL | auto_increment | | Name | varchar(50) | NO | | NULL | | +-------+------------------+------+-----+---------+----------------+授权并且创建账号
MariaDB [(none)]> grant all on *.* to v9@'192.168.50.109' IDENTIFIED BY '123456';
-
- 回收权限指令 REVOKE
- 语法格式
REVOKE priv_type[(column_list)] [ , priv_type[(column_list)]] ... ON [object_type] priv_level FROM user [, user] ... - 示例
回收用户 v9@'192.168.50.100’在数据库hellodb中students表上的查看字段stuid,name的权限MariaDB [(none)]> REVOKE SELECT (stuid,name) -> ON `hellodb`.`students` -> FROM v9@'192.168.50.100';
- 语法格式
- 查看指定用户获得的授权：
- 使用帮助
Help SHOW GRANTS - 查询指定用户权限
- 语法格式
SHOW GRANTS FOR ‘user’@‘host’; - 示例
MariaDB [hellodb]>SHOW GRANTS FOR v9@'192.168.50.100'; +----------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'v9'@'192.168.50.100' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' | 连接权限 | GRANT SELECT (name, stuid) ON `hellodb`.`students` TO 'v9'@'192.168.50.100' 查询权限 | +----------------------------------------------------------------------------------------------------------
- 语法格式
- 查看当前用户的权限
SHOW GRANTS FOR CURRENT_USER[()]; - 注意项
MariaDB服务进程启动时会读取mysql库中所有授权表至内存- GRANT或REVOKE等执行权限操作会保存于系统表中，MariaDB的服务进程通常会自动重读授权表，使之生效
- 对于不能够或不能及时重读授权表的命令，可手动让MariaDB的服务进程重读授权表：mysql> FLUSH PRIVILEGES;
- 回收授权，或者授权其他权限后，用户需要重新登录才可以生效
- 使用帮助
4. 存储引擎
4.1 从功能模块看MySQL体系结构
- 各模块说明
-
初始化模块
MySQL启动的时候，对整个系统做各种各样的初始化操作，比如各种buffer,cache结构的初始化和内存空间的申请，各种系统变量、存储引擎的初始化设置等 -
连接管理模块
负责管理远程连接用户请求，接收连接请求，转发所有连接请求到线程管理模块 -
线程管理模块
管理MYsQl的多个线程，负责为连接用户分配一个线程单独服务以及回收退出用户的空闲线程，回收的线程可以重复使用 -
用户模块
用户账号管理，主要包括用户的登录连接权限控制和用户的授权管理 -
命令分发器：
在MySQL中我们习惯将所有的Client端发送给Server端的命令都称为query，服务线程收到客户端的query后，传递给专门负责将各种query进行分类然后转发给各个对应的处理模块，这个模块就是命令分发器，主要工作就是讲query语句进行语义和语法的分析，然后按不同的操作类型进行分类，然后做针对性的转发 -
查询缓存模块
将客户端提交的SELECT类query请求的返回结果cache到内存中，与此quer的一个hash值做对应，此query所去数据的基表发生任何数据变化后，MySQL会自动使此query的cache失效，查询缓存模块对性能的提高非常显著，相对的对内存的消耗也非常大 -
日志记录模块
负责真个系统级别的逻辑层的日志记录，包括error log, binary log,slow query log等 -
命令解析器
非查询命令通过命令发生器，发送到命令解析器上，再分发到对应的模块中进行管理 -
查询优化器：
根据客户端请求的query语句，和数据库中的一些统计信息，在一系列算法的基础上进行分析，得出最有策略，告诉后面的程序如何取得query语句的结果 -
表变更模块
负责完成一些DML和DDL的query，如：update、delte、insert、create table、alter table等语句的处理 -
表维护模块
表的状态监测，错误修复，以及优化和分析等工作 -
复制模块
分为Master模块和Slave模块两部分，Master模块主要负责在replication环境中读取Master端的binary日志，以及与slave端的I/O线程交互等工作。Slave模块负责从Master端请求和接收binary日志，并写入本地relay log中的I/O线程，还要负责从relay log中读取相关日志文件，然后解析成可以在Slave端正确执行并得到和Master端完全相同的结果的命令并交给slave执行的SQL线程 -
状态模块
负责在客户端请求系统状态的时候，将各种张贴数据返回给用户，像DBA常用的各种show status命令，show variables命令等，所得到的结果都是这个模块返回的 -
访问控制模块：
根据用户模块中各用户的授权信息，以及数据库自身特有的各种约束，来控制用户对数据的访问 -
表管理模块
维护表的定义文件（也就是*.frm文件）、表结构信息cache、table级别的锁管理 -
存储引擎接口模块：
决定了将用户数据如何保存到物理设备上 -
网络交互模块
底层网络交互模块，抽象出底层网络交互所使用的接口api，实现底层网络数据的接收与发送，以方便其他各个模块调用，以及对这一部分的维护，所有源代码都在vio文件夹下面 -
核心API
提供需要非常高效的底层操作功能优化实现，包括各种底层数据结构的实现，特殊算法的实现，字符串处理，数字处理等，小文件I/O，格式化输出，以及内存管理；API模块的所有源代码集中在mysys和strings文件夹下
-
4.2 存储引擎
- 数据库特征说明
- srorage limits
最大存储 - Transactions:
事务，多个操作作为一个整体出现 - Locking granularity
锁，避免同时操作，带来不可预期的结果 - MVCC:
- 作用
多版本并发控制机制，在一个数据库中可能有多个事务同时访问一个资源，如果没有控制机制，很可能出现脏数据，MVCC控制机制可以解决此问题 - 原理
在数据库中的表，系统会增加两个隐藏字段insert 和delete，分别记录行数据创建和删除时的版本号，此版本号是随事务的创建而不断增长的数字，在事务开始时会记录它自己的系统版本号，后来的事务只会比之前的事务编号大
事务对表做出insert操作时，会在insert字段增加做此操作事务的编号，delete会在删除字段增加编号，修改会同时在insert和delete字段增加编号。 - 事务DELECT表时，得到的数据
- 必须找到一个行的版本，它至少要和事务的版本一样老(也即它的版本号不大于事务的版本号)。这保证了不管是事务开始之前，或者事务创建时，或者修改了这行数据的时候，这行数据是存在的。
- 这行数据的删除版本必须是未定义的或者比事务版本要大。这可以保证在事务开始之前这行数据没有被删除。这里的不是真正的删除数据，而是标志出来的删除。真正意义的删除是在commit的时候。
- 作用
- Geospatial data type support
地理空间数据类型支持 - Geospatial indexing support
地理空间索引支持 - B-tree indexes：B-tree索引
所有的索引节点都按照balance tree的数据结构来存储，所有的索引数据节点都在叶节点 - T-tree indexes：T-tree 索引
- Hash indexes：哈希索引
- Full-text search indexes：全文索引
- Clustered indexes：聚集索引
- Data caches：数据缓存，将表内的数据放在内存中，让用户直接使用
- index caches：索引缓存
- Compressed data：压缩数据
- Encrypted data：加密数据
- Cluster database support：集群数据库支持
- Replication support：复制支持
- Foreign key support ：外键支持
- Backup/point-in-time recovery：备份实时恢复
- Query cache support：查询缓存支持
- srorage limits
4.3 MyISAM引擎
-
特点
- 不支持事务，操作只能一步一步执行，如果在执行过程中出现故障，很难保证数据的完整性和一致性
- 表级锁定，影响并发操作
- 读写相互阻塞，写入不能读，读时不能写
- 只缓存索引
- 不支持外键约束
- 不支持聚簇索引
- 读取数据较快，占用资源较少
- 不支持MVCC（多版本并发控制机制）高并发
- 崩溃恢复性较差
- MySQL5.5.5前默认的数据库引擎
-
适用场景
只读（或者写较少）、表较小（可以接受长时间进行修复操作） -
支持索引类型
- B-TREE索引
是参与一个索引的所有字段的长度之和不能超过1000 字节 - R-TREE索引
主要设计用于为存储空间和多
维数据的字段做索引，所以目前的MySQL 版本来说，也仅支持geometry 类型的字段作索引。 - Full-text 索引
Full-text 索引就是我们长说的全文索引，他的存储结构也是b-tree。主要是为了
解决在我们需要用like 查询的低效问题
- B-TREE索引
-
MyISAM数据库文件存放
对于每一个表对应有三个文件，存放在所属数据库的文件夹下- tbl_name.frm:
与表相关的元数据信息，包括表结构的定义信息 - tbl_name.MYD:
存放表数据的文件 - tbl_name.MYI:
存放索引相关信息文件
- tbl_name.frm:
4.4 InnoDB引擎
- 特点：
- 锁定集中的改进，行级锁，通过索引来完成
- 支持事务，适合处理大量短期事务，innodb引擎最重要的一点
- 读写阻塞与事务隔离级别相关
- 可缓存数据和索引
- 实现外键约束
Innodb 实现了外键引用这一数据库的重要特性，使在数据库端控制部分数据的完整性成为可能。 - 支持聚簇索引
- 崩溃恢复性更好
- 支持MVCC高并发
- 从MySQL5.5后支持全文索引
- 从MySQL5.5.5开始为默认的数据库引擎
- InnoDB数据库文件
- 两类文件放在数据库独立目录中
- 数据文件(存储数据和索引)：tb_name.ibd
- 表格式定义：tb_name.frm
- 所有InnoDB表的数据和索引放置于同一个表空间中
表空间文件：datadir定义的目录下
数据文件：ibddata1, ibddata2, … - 设置每个表单独使用一个表空间存储表的数据和索引
启用：innodb_file_per_table=ON（MariaDB5.5以后版本默认开启）
参看：https://mariadb.com/kb/en/library/xtradbinnodb-server-system-variables/#innodb_file_per_table
- 两类文件放在数据库独立目录中
- MySQL中的系统数据库使用的引擎
- mysql数据库
是mysql的核心数据库，类似于sql server中的master库，主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息'查看mysql数据库中文件的结构' MariaDB [(none)]> show table status from mysql\G *************************** 30. row *************************** Name: user Engine: MyISAM <==存储引擎为 MyISAM Version: 10 Row_format: Dynamic ......省略 Comment: Users and global privileges - PERFORMANCE_SCHEMA数据库
MySQL 5.5开始新增的数据库，主要用于收集数据库服务器性能参数（例如连接情况、负载情况）,库里表的存储引擎均为PERFORMANCE_SCHEMA，用户不能创建存储引擎为PERFORMANCE_SCHEMA的表MariaDB [(none)]> show table status from performance_schema\G *************************** 52. row *************************** Name: users Engine: PERFORMANCE_SCHEMA - information_schema数据库
MySQL 5.0之后产生的，一个虚拟数据库，物理上并不存在。information_schema数据库类似与“数据字典”，提供了访问数据库元数据的方式，即数据的数据。比如数据库名或表名，列类型，访问权限（更加细化的访问方式），使用的存储引擎为MEMORYMariaDB [(none)]> show table status from information_schema\G *************************** 75. row *************************** Name: INNODB_SYS_SEMAPHORE_WAITS Engine: MEMORY
- mysql数据库
4.5 其它存储引擎
- Performance_Schema
mysql自建数据库 performance_schema使用的引擎 - Memory
将所有数据存储在RAM中，以便在需要快速查找参考和其他类似数据的环境中进行快速访问。适用存放临时数据,以前被称为HEAP引擎 - MRG_MyISAM
使MySQL DBA或开发人员能够对一系列相同的MyISAM表进行逻辑分组，并将它们作为一个对象引用。适用于VLDB(Very Large Data Base)环境，如数据仓库 - Archive
为存储和检索大量很少参考的存档或安全审核信息，只支持SELECT和INSERT操作；支持行级锁和专用缓存区 - Federated联合
用于访问其它远程MySQL服务器一个代理，它通过创建一个到远程MySQL服务器的客户端连接，并将查询传输到远程服务器执行，而后完成数据存取，提供链接单独MySQL服务器的能力，以便从多个物理服务器创建一个逻辑数据库。非常适合分布式或数据集市环境 - BDB
可替代InnoDB的事务引擎，支持COMMIT、ROLLBACK和其他事务特性 - Cluster/NDB
MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性，主要用于MySQL Cluster 分布式集群环境， - CSV
CSV存储引擎使用逗号分隔值格式将数据存储在文本文件中。可以使用CSV引擎以CSV格式导入和导出其他软件和应用程序之间的数据交换 - BLACKHOLE
-黑洞存储引擎接受但不存储数据，检索总是返回一个空集。作为中间代理使用，该功能可用于分布式数据库设计，数据自动复制，但不是本地存储，例如用于主从复制的中间代理 - example
“stub”引擎，它什么都不做。可以使用此引擎创建表，但不能将数据存储在其中或从中检索。目的是作为例子来说明如何开始编写新的存储引擎
4.6 管理存储引擎
-
查看mysql支持的存储引擎:
show engines; -
查看当前默认的存储引擎:
MariaDB [(none)]> SHOW VARIABLES LIKE '%storage_engine%' -
设置默认的存储引擎：
vim /etc/my.conf [mysqld] default_storage_engine=InnoDB <==在[mysqld]中增加系统此选项 -
查看库中所有表使用的存储引擎
Show table status from db_name;'查看hellodb数据库中表的引擎' MariaDB [(none)]> SHOW table STATUS FROM hellodb\G -
查看库中指定表的存储引擎
- 通过查看表状态信息获取
show table status like ’ tb_name’;'进入表所在数据库' MariaDB [(none)]> use hellodb '查询表students所用引擎' MariaDB [hellodb]> SHOW table STATUS LIKE 'students'\G *************************** 1. row *************************** Name: students 'Engine: InnoDB' <==存储引擎 - 通过查看表创建信息获取
show create table tb_name;MariaDB [hellodb]> SHOW CREATE table students; ......省略 ) 'ENGINE=InnoDB' AUTO_INCREMENT=26 DEFAULT CHARSET=utf8 |
- 通过查看表状态信息获取
-
设置表的存储引擎：
- 创建表时，指定存储引擎
CREATE TABLE tb_name(… ) ENGINE=InnoDB;'创建默认引擎为Innodb的表cc' MariaDB [db2]> CREATE TABLE cc(StuID int unsigned NOT NULL,Name varchar(50) NOT NULL) ENGINE=Innodb; - 修改表存储引擎
ALTER TABLE tb_name ENGINE=InnoDB;'修改表cc的存储引擎为MyISAM' MariaDB [db2]> ALTER TABLE cc ENGINE=MyISAM; '查看修改效果' MariaDB [db2]> SHOW TABLE STATUS LIKE 'cc'\G *************************** 1. row *************************** Name: cc Engine: MyISAM
- 创建表时，指定存储引擎
5. 服务器配置
-
mysql官方服务器配置表
- 官方地址
https://dev.mysql.com/doc/refman/5.7/en/mysqld-option-tables.html - 配置表格式
- 标准表内容
- Cmd-Linev
是否可以在命令行执行 - Option file
服务器选项，可以写在配置文件中，通常来讲连接符使用’-’，由于很多服务器选项同时也为变量所以也支持‘_’写法 - System Var
是否可以做服务器变量，连接符使用’_’，可以用作临时修改，修改又分为Global（不论连接多少会话都有效）和Session（针对当前会话有效）级别，Both表示两个都支持 - Status Var
状态变量，作为值来参考，不可以修改 - Dynamic
是否支持动态，在不重启主机的情况下直接修改
- Cmd-Linev
- 官方地址
-
mariadb官方服务器配置列表
- 官方地址
https://mariadb.com/kb/en/library/full-list-of-mariadb-options-system-and-status-variables/ - 格式
--aria-block-size：带–表示为服务器选项,
aria_block_size：表示同时为变量
点击蓝色部分，进入详细描述aria_block_size <==配置选项|变量 Description: Block size to be used for Aria index pages. Changing this requires dumping, deleting old tables and deleting all log files, and then restoring your Aria tables. If key lookups take too long (and one has to search roughly 8192/2 by default to find each key), can be made smaller, e.g. 2048 or 4096. Commandline: --aria-block-size=# <==是否支持命令行 Scope: Global <==变量范围级别 Dynamic: No <==是否支持动态修改 Data Type: numeric <==数据类型 Default Value: 8192 <==默认值
- 官方地址
-
系统变量
-
系统变量查询
- 格式
支持模糊查询，默认为SESSION级别的
SHOW VARIABLES [LIKE 'Variable_name']
查GLOBAL级别的变量
SHOW GLOBAL VARIABLES [LIKE 'Variable_name]' - 示例
'列出所有会话系统变量' MariaDB [(none)]> SHOW VARIABLES; '查找指定会话系统变量' MariaDB [(none)]>SHOW VARIABLESlike LIKE '%datadir%' ; +---------------+--------------+ | Variable_name | Value | +---------------+--------------+ | datadir | /data/mysql/ | <==变量的键值 +---------------+--------------+ - 常见的系统变量
max_connections：允许最大并发连接数MariaDB [hellodb]> show variables like '%connect%'; | max_connections | 151 |
- 格式
-
修改服务器变量的值：
- 获取设置SET帮助
mysql> help SET - 修改全局变量：仅对修改后新创建的会话有效；对已经建立的会话无效
SET GLOBAL system_var_name=value
SET @@global.system_var_name=valueMariaDB [db2]> SET GLOBAL max_connections=500; MariaDB [db2]> SET @@GLOBAL.max_connections=600; - 修改会话变量：
SET [SESSION] system_var_name=value
SET @@[session.] system_var_name=value
- 获取设置SET帮助
-
-
状态变量（只读）
用于保存mysqld运行中的统计数据的变量，不可更改-
格式
查看全局状态变量
SHOW GLOBAL STATUS [LIKE ‘Variable_name’]
查看会话状态变量
SHOW [SESSION] STATUS [LIKE ‘Variable_name’] -
示例
uptime：查看服务器启动时间MariaDB [(none)]> show status like 'uptime';Com_select：查看使用查询的次数
MariaDB [hellodb]> show status like '%select%'; | Com_select | 4 | <==查询计数器Com_insert：查看插入计数器
MariaDB [hellodb]> show status like '%insert%'; | Com_insert | 1 |Threads_connected：查看多少人正在连接数据库，Threads为进程项
MariaDB [hellodb]> show status like '%Threads%'; | Threads_connected | 1 | <==正在连接的 | Threads_running | 1 | <==正在运行的
-
-
服务器选项
- 查看服务器选项
获取mysqld的可用选项列表
获取默认设置[root@hai7-8 mysql]$mysqld --verbose --help
如果mysqld不在PATH变量中，需要写绝对路径[root@hai7-8 mysql]$mysqld --print-defaults查看进程，找到服务绝对路径 [root@hai7-8 ~]$ps aux|grep mysqld [root@hai7-8 ~]$/usr/libexec/mysqld --verbose --help 获取默认设置 [root@hai7-8 ~]$/usr/libexec/mysqld --print-defaults - 设置服务器选项方法
- 在命令行中设置:
[root@hai7-8 ~]$./mysqld_safe –-skip-name-resolve=1; - 在配置文件my.cnf中设置：
[root@hai7-8 ~]$vim /etc/my.cnf [mysqld] skip_name_resolve=1 <==增加在mysqld块里
- 在命令行中设置:
- 示例
设置默认将表数据独立存放为单个文件
拒绝将ip地址反向解析成名字vim /etc/my.conf [mysqld] innodb_file_per_table
将ip地址反向解析成名字可能会导致连接失败，例如集群操作时vim /etc/my.conf [mysqld] skip-name-resolve=on <==即是选项又是变量
- 查看服务器选项
-
服务器变量SQL_MODE
- 作用
对其设置可以完成一些约束检查的工作,可分别进行全局的设置或当前会话的设置，参看：https://mariadb.com/kb/en/library/sql-mode/ - 常见MODE:
- NO_AUTO_CREATE_USER
禁止GRANT(授权)创建密码为空的用户 - NO_ZERO_DATE
在严格模式，不允许使用‘0000-00-00’的时间 - ONLY_FULL_GROUP_BY
对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么将认为这个SQL是不合法的 - NO_BACKSLASH_ESCAPES
反斜杠“\”作为普通字符而非转义字符 - PIPES_AS_CONCAT
将"||"视为连接操作符而非“或运算符”
- NO_AUTO_CREATE_USER
- 作用
-
注意事项
- 其中有些参数支持运行时修改，会立即生效；有些参数不支持，且只能通过修改配置文件，并重启服务器程序生效；有些参数作用域是全局的，且不可改变；有些可以为每个用户提供单独（会话）的设置
- 纯变量不能放在配置文件中的，会影响启动，例如character_set_results
6. 查询缓存
-
从工作流程看MySQL架构
数据库本身是不能和磁盘直接交流的，是通过存储引擎将指令发送给内核，通过内核和文件系统打交道，再将数据写到磁盘文件中
- connectors
各种语言提供的连接数据库的能力 - connection pool
数据库中的连接池，提供了验证身份，线程复用（系统判断线程使用次数过多，自动销毁），连接限制，内存检查，缓存功能使用 - SQL interface
将用户提交的SQL语句进行检查，通过检查，则将SQL语句翻译给系统，进行执行 - parser
解析语句，验证访问对象权限 - optimizer
优化过程，访问路径 - Caches & Buffers
缓存与缓冲区的使用，例如写入数据库时，先缓冲至缓冲区然后批量写入 - Pluggable Storage Englnes
写入时用到的存储引擎，不同的引擎在硬件设备上保存数据的方式是不同的 - Files&Logs：
日志 - Management Services & Utilltles：
辅助管理工具
- connectors
-
查询的执行路径
- 客户端软件连接数据库
- 连接数据库使用通信协议，基于mysql的IPA接口开发出来的通信协议
- 建立通信后验证账号等安全检查，通过检查后用户发出请求，比如select，请求先到达mysql的查询缓存，如果缓存中有，直接返回结果
- 如果缓存中没有，将请求发送给解析器，生成解析树，经过预处理生成新的解析树
- 查询优化器将新的解析树中进行查询优化
- 执行计划，通过优化查询，判断出最佳查询路径，比如是否利用索引，利用那种索引等
- 将执行计划提交给存储引擎，通过存储引擎连接数据库进行访问，访问的数据是以块的形式存放的，大小为16k
-
查询缓存(只负责查询的缓存)
- 查询缓存（Query Cache ）原理：
缓存SELECT操作或预处理查询的结果集和SQL语句，当有新的SELECT语句或预处理查询语句请求，先去查询缓存，判断是否存在可用的记录集 - 判断标准
与缓存的SQL语句，是否完全一样，区分大小写(缓存中的数据，是根据查询的SQL语句，进行哈希运算，放在缓存中，所以如果大小写不同，哈希值就不同，在缓存中是查询不到结果的) - 优缺点
- 不需要对SQL语句做任何解析和执行，当然语法解析必须通过在先，直接从Query Cache中获得查询结果，提高查询性能
- 查询缓存的判断规则，不够智能，也即提高了查询缓存的使用门槛，降低其效率；
- 查询缓存的使用，会增加检查和清理Query Cache中记录集的开销
- 查询缓存（Query Cache ）原理：
-
哪些查询可能不会被缓存
- 查询语句中加了SQL_NO_CACHE参数，例如数据库数据已经更新，缓存中的数据为旧数据，可以加此参数跳过查询缓存
- 查询语句中含有获得值的函数(经常变化的值)，包含自定义函数，如：NOW()
CURDATE()、GET_LOCK()、RAND()、CONVERT_TZ()等 - 对系统数据库的查询：mysql、information_schema查询语句中使用SESSION级别变量或存储过程中的局部变量
- 查询语句中使用了LOCK IN SHARE MODE、FOR UPDATE的语句，查询语句中类似SELECT …INTO 导出数据的语句
- 对临时表的查询操作；存在警告信息的查询语句；不涉及任何表或视图的查询语句；某用户只有列级别权限的查询语句
- 事务隔离级别为Serializable时，所有查询语句都不能缓存
-
查询缓存相关的服务器变量
与缓存相关的变量 MariaDB [(none)]> SHOW VARIABLES LIKE 'query_cache%'; +------------------------------+----------+ | Variable_name | Value | +------------------------------+----------+ | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 33554432 | | query_cache_strip_comments | OFF | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | +------------------------------+----------+- query_cache_min_res_unit
查询缓存中内存块的最小分配单位，默认4k，较小值会减少浪费，但会导致更频繁的内存分配操作，较大值会带来浪费，会导致碎片过多，内存不足 - query_cache_limit
单个查询结果能缓存的最大值，默认为1M，对于查询结果过大而无法缓存的语句，建议使用SQL_NO_CACHE - query_cache_size
查询缓存总共可用的内存空间；单位字节，必须是1024的整数倍，最小值40KB，低于此值有警报，默认为0
以系统变量修改，仅支持以k为单位
在配置文件中修改支持以M为单位MariaDB [hellodb]> set global query_cache_size=33554432;[root@hai7-8 ~]$vim /etc/my.cnf [mysqld] query_cache_size=30M - query_cache_wlock_invalidate
如果某表被其它的会话锁定，是否仍然可以从查询缓存中返回结果，默认值为OFF，表示可以在表被其它会话锁定的场景中继续从缓存返回数据；ON则表示不允许。其他事务在修改表时上锁，如果这时候搜索，可能得到的结果是脏数据。 - query_cache_type:
是否开启缓存功能，取值为ON, OFF, DEMAND- query_cache_type的值为OFF或0时，查询缓存功能关闭
- query_cache_type的值为ON或1时，查询缓存功能打开，SELECT的结果符合缓存条件即会缓存，否则，不予缓存，显式指定SQL_NO_CACHE，不予缓存，此为默认值
- query_cache_type的值为DEMAND或2时，查询缓存功能按需进行，显式指定SQL_CACHE的SELECT语句才会缓存；其它均不予缓存
- query_cache_min_res_unit
-
SELECT语句的缓存控制
- SQL_CACHE: 显式指定存储查询结果于缓存之中
- SQL_NO_CACHE: 显式查询结果不予缓存
-
参看文献
https://mariadb.com/kb/en/library/server-system-variables/#query_cache_type
https://dev.mysql.com/doc/refman/5.7/en/query-cache-configuration.html -
优化查询缓存
- 命中率是否可以接受
命中率就是多次查询，有几次查询是在缓存中查到的- 如果可以接受，那就不用优化
- 不能接受，进入第2大项判断
- 判断是否大部分查询结果无法缓存
- 大部分无法缓存，判断是否 query_cache_limit是否设置过小
- 不是此原因则结束优化
- 单个缓存值设定太小，增大此值
- 大部分可以缓存，进入第3项判断
- 大部分无法缓存，判断是否 query_cache_limit是否设置过小
- 判断是否发生了许多验证工作（比如数据是否有效）
- 是，则去判断缓存是否被碎片化（内存最小单位设置不合理，造成多碎片）
- 是，通过重设块大小，或者清理碎片来优化
- 否，则判断是否有很多因为内存过低而发生的修复工作
- 是，则调节缓存大小
- 否，则判断是否有很多更新语句
- 是，结束优化，因为负载并不适合缓存
- 否，结束优化，有其他东西配置错误
- 否，进入第4项判断
- 是，则去判断缓存是否被碎片化（内存最小单位设置不合理，造成多碎片）
- 判断缓存是否启用
- 是，可能是该指令从来没有缓存过
- 否，启动缓存功能
- 命中率是否可以接受
-
查询缓存的状态变量，与存储引擎无关，是系统的缓存变量
- 查询缓存相关的状态变量：
SHOW GLOBAL STATUS LIKE ‘Qcache%’;MariaDB [hellodb]> SHOW GLOBAL STATUS LIKE 'Qcache%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 33536824 | | Qcache_hits | 0 | | Qcache_inserts | 2 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 28 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 1 | +-------------------------+----------+- Qcache_free_blocks：
处于空闲状态Query Cache中内存Block 数 - Qcache_total_blocks：
Query Cache 中总Block ，当Qcache_free_blocks相对此值较大时，可能用内存碎片，执行FLUSH QUERY CACHE清理碎片 - Qcache_free_memory：
处于空闲状态的Query Cache 内存总量 - Qcache_hits：
Query Cache 命中次数 - Qcache_inserts：
向Query Cache 中插入新的Query Cache 的次数，即没有命中的次数 - Qcache_lowmem_prunes：
0记录有多少条查询因为内存不足而被移除出查询缓存 - Qcache_not_cached：
没有被Cache 的SQL 数，包括无法被Cache 的SQL 以及由于query_cache_type设置的不会被Cache 的SQL语句 - Qcache_queries_in_cache：
在Query Cache 中的SQL 数量
- Qcache_free_blocks：
- 查询缓存相关的状态变量：
-
命中率和内存使用率估算
- 查询缓存中内存块的最小分配单位query_cache_min_res_unit：
(query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache - 查询缓存命中率
Qcache_hits/ ( Qcache_hits+ Qcache_inserts) * 100% - 查询缓存内存使用率
(query_cache_size–qcache_free_memory) / query_cache_size* 100%
- 查询缓存中内存块的最小分配单位query_cache_min_res_unit：
-
InnoDB存储引擎的缓冲池:
通常InnoDB存储引擎缓冲池的命中不应该小于99%- 查看相关状态变量
MariaDB [(none)]> SHOW GLOBAL STATUS LIKE 'innodb%read%'\G - 常用状态变量描述
- Innodb_buffer_pool_reads:
表示从物理磁盘读取页的次数 - Innodb_buffer_pool_read_ahead:
预读的次数 - Innodb_buffer_pool_read_ahead_evicted:
预读页，但是没有读取就从缓冲池中被替换的页数量，一般用来判断预读的效率 - Innodb_buffer_pool_read_requests:
从缓冲池中读取页次数 - Innodb_data_read:
总共读入的字节数 - Innodb_data_reads:
发起读取请求的次数，每次读取可能需要读取多个页
- Innodb_buffer_pool_reads:
- 相关计算
- 查看相关状态变量
7 提高数据库性能-索引
7.1 索引概述
- 索引作用方式
将索引定义在查找时作为查找条件的字段（如主键），将原本无序存放在磁盘存储器上的数据，转变为树状结构，按索引定义排序，以达到快速得到目标数据的功能；索引实现在存储引擎，与数据库和存储引擎密切相关，存储引擎不同，支持的索引页截然不同 - 优点：
- 索引可以降低服务需要扫描的数据量，减少了IO次数
- 索引可以帮助服务器避免排序和使用临时表
- 索引可以帮助将随机I/O转为顺序I/O
- 缺点：
- 占用额外空间
- 影响插入速度，平衡树结构要维持在一个正确状态，增删改数据都会改变平衡树各节点中的索引数据内容，数据库管理系统（DBMS）就会重新梳理树的结构，确保正确
7.2 索引结构
-
B-TREE：与二叉树相关
-
二叉树
-
二叉树是递归定义的，每个节点最多分两个叉
- 最上边的为
根节点 - 最下边的为
叶子节点 - 中间的全部为
分支节点
- 最上边的为
-
查询顺序为：例如查询6号
从根开始查，根为5号比6小，所以从右边开始查找，第二层为8比6大，所以从左分支走到第三层 找到7比6大，再从左边找得到6。就上图而言，找到6需要访问4次I/O
-
-
-
B-TREE索引
- 优化二叉树，降低I/O次数，即访问磁盘的次数，在每层放两个数据，减少层数
- 在B-TREE中，每个节点放2个数据，最多可以有3各分支，
- 节点中的指针P1指向比此区间大的下一层节点，指针P2指向在此区间范围的下一层节点，指针P3指向比此区间小的下一层节点
- 特征
- 关键字分布在整棵树中
- 任何一个关键字出现且只出现在一个结点中
- 搜索有可能在非叶子结点结束
- 缺点：
叶子节点之间没有联系，如果要查范围数据，比如10号到30号之间的数据，需要重复从根节点开始查询
-
B+TREE
-
特征
- 只有叶子节点存放数据，其他节点只放索引，分支节点不放数据，同样的块大小可以存放更多的索引，减少整个树的层次
- 在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，提高了区间访问性能
- 顺序存储，每一个叶子节点到根结点的距离是相同的，查询效率稳定
-
可以使用B-Tree索引的查询类型：
- 全值匹配：精确所有索引列
如：姓wang，名xiaochun，年龄30 - 匹配最左前缀：即只使用索引的第一列
如：姓wang - 匹配列前缀：只匹配一列值开头部分，
如：姓以w开头的 - 匹配范围值：
如：姓ma和姓wang之间 - 精确匹配某一列并范围匹配另一列
如：姓wang,名以x开头的
- 全值匹配：精确所有索引列
-
B-Tree索引的限制
- 如果不从最左列开始，则无法使用索引
如：组合键为name+city，查找city为某地的用户，是无法使用索引的，因为city是针对name排序后，在name的基础上再排序，单个city是没有顺序的 - 不能跳过索引中的列
如：例如查看某人来自哪个城市，直接查城市是不能匹配的 - 如果查询中某个列是为范围查询，那么其右侧的列都无法再使用索引
如：姓名以a开头的所有人，如果加上右侧city的索引，就会漏掉一部分a
- 如果不从最左列开始，则无法使用索引
-
-
Hash索引
基于哈希表实现，只有精确匹配索引中的所有列的查询才有效，存储引擎会根据所有的索引计算出一个哈希值，哈希索引将所有的哈希值存储在索引中，索引自身只存储索引列对应的哈希值和数据指针，索引结构紧凑，查询性能好- 适用场景：
- 只支持等值比较查询，包括=, <=>, IN()
- Memory存储引擎支持显式hash索引，InnoDB和MyISAM存储引擎不支持
- 不适合使用hash索引的场景：
- 不适用于顺序查询
hash索引中存放哈希值和指针，而哈希值与原值不一定相同，所 以不能用来排序 - 不支持模糊匹配
- 不支持范围查询
- 不支持部分索引列匹配查找
如A，B列索引，只查询A列索引无效
- 不适用于顺序查询
- 适用场景：
-
特别提示
- 索引列的顺序和查询语句的写法应相匹配，才能更好的利用索引
- 为优化性能，可能需要针对相同的列但顺序不同创建不同的索引来满足不同类型的查询需求
7.2 索引分类
-
地理空间索引（Geospatial indexing ）：
MyISAM支持地理空间索引，可以使用任意维度组合查询，使用特有的函数访问，常用于做地理数据存储，使用不多
InnoDB从MySQL5.7之后也开始支持 -
全文索引(FULLTEXT)：
先定义一个词库，然后在文章中查找每个词条(term)出现的频率和位置，把这样的频率和位置信息按照词库的顺序归纳，这样就相当于对文件建立了一个以词库为目录的索引，这样查找某个词的时候就能很快的定位到该词出现的位置
InnoDB从MySQL 5.6之后也开始支持 -
聚簇和非聚簇索引、二级（辅助）索引
-
聚簇索引(Clustered Index)，数据和索引在一个文件中，例如INNODB
- 特征
- 表数据按照索引的的顺序存储，也就是说索引与表中的记录物理顺序一致，
- 对于聚集索引，叶子结点即存储了真实的数据行，不再有另外单独的数据页。
- 在一张表上最多只能创建一个聚集索引，因为真实数据的物理顺序只能有一种，系统默认会将主键设置为聚簇索引
- 聚簇索引的二级索引：
叶子节点不会保存引用的行的物理位置，而是保存了行的主键，通过二级索引查找行，存储引擎需要找到二级索引的叶子节点获得对应的主键值，然后根据这个值去聚簇索引中查找到对应的行。 - 优点
- 数据与叶子节点存储在一起，和主键一起载入内存中，找到节点就可以立即返回数据，只按主键搜索来说，获取速度快
- 使用覆盖索引扫描的查询可以直接使用节点中的主键值
- 缺点
- 插入速度严重依赖于插入顺序，如果不是按照主键顺序加载数据，那么在加载完成后最好使用OPTIMIZE TABLE命令重新组织一下表
- 更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置
- 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临“页分裂”的问题
- 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候
- 二级索引包含主键列，比较大，而且需要经过两次B-TREE
- 特征
-
非聚簇索引(Non- Clustered Index)，索引和数据独立存放
例如mysql数据库使用的MyISAM存储引擎，生成3个文件，分别存放索引、表结构、数据- 特征
- 表数据存储顺序与索引顺序无关。
- 对于非聚集索引，叶结点包含索引字段值及指向数据页数据行的逻辑指针，其行数量与数据表行数据量一致
- 非聚簇索引可以有多个
- 非聚簇索引的二级索引：
主键索引和二级索引区别不大，只是主键索引是唯一的存放的是主键，二级索引存放的是辅助键
- 特征
-
-
稠密索引、稀疏索引
是否索引了每一个数据项- 稠密索引
每个记录都有一个索引项，记录在数据区存放是任意的，但索引是按序的 - 稀疏数据线
将所有数据记录关键字值分成许多组，每组一个索引项
- 稠密索引
-
简单索引、组合索引
-
单一索引
索引列为一列的情况,即新建索引的语句只实施在一列上 -
复合索引，
由多列创建的索引称为复合索引，在符合索引中的前导列必须出现在where条件中，索引才会被使用
-
-
主键索引与唯一键索引
-
唯一索引
唯一索引比较好理解，不允许具有索引值相同的行，这样的索引选择性是最好的 -
主键索引
主键索引就是唯一索引，不过主键索引是在创建表时就创建了，唯一索引可以随时创建。 -
区别
- 主键是主键约束+唯一索引
- 主键一定包含一个唯一索引，但唯一索引不是主键
- 唯一索引列允许空值，但主键列不允许空值
- 一个表只能有一个主键，但可以有多个唯一索引
-
-
覆盖索引
select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖 -
冗余和重复索引：
- 冗余索引：（A），（A，B）
复合索引A,B已经对A进行排序，再建立A索引没有意义 - 重复索引：已经有索引，再次建立索引
针对相同的字段重复建立，例如字段age，先建立idx_age1，又在其上建立idx_age2
- 冗余索引：（A），（A，B）
7.4 索引优化策略：
- 独立地使用列
尽量避免其参与运算，独立的列指索引列不能是表达式的一部分，也不能是函数的参数，在where条件中，始终将索引列单独放在比较符号的一侧'建立索引在字段age上' MariaDB [hellodb]> create index index_age ON students(age); 'age+5就是指索引参与运算，混在一起计算是无法利用索引的' MariaDB [hellodb]> select * from students where age+5=25; +-------+--------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+--------------+-----+--------+---------+-----------+ | 9 | Ren Yingying | 20 | F | 6 | NULL | | 22 | Xiao Qiao | 20 | F | 1 | NULL | +-------+--------------+-----+--------+---------+-----------+ '如果一定要运算，应该讲运算放在=右边' MariaDB [hellodb]> select * from students where age=20+5;- 左前缀索引
构建索引时指定字段字符数，具体长度要通过索引选择性来评估
例如：将名字作为索引时，表内名字都在10个字符以上，经过评估仅使用前3个字符就可以达到区别各行的目的，就可以考虑左前缀索引，限定为3各字符 - 多列索引
AND操作时更适合使用多列索引，而非为每个列创建单独的索引 - 选择合适的索引列顺序
无排序和分组时，将选择性最高放左侧 - 只要列中含有NULL值，就最好不要在此例设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引
- 尽量使用短索引，如果可以，应该制定一个前缀长度
- 对于经常在where子句使用的列，最好设置索引
- 对于有多个列where或者order by子句，应该建立复合索引
- 对于like语句，以%或者‘-’开头的不会使用索引，以%结尾会使用索引
- 尽量不要使用not in和<>操作
- 左前缀索引
- SQL语句性能优化
- 查询时，能不要*就不用*，尽量写全字段名
- 大部分情况连接效率远大于子查询
- 多表连接时，尽量小表驱动大表，即小表join 大表
- 在有大量记录的表分页时使用limit
- 对于经常使用的查询，可以开启缓存
- 多使用explain和profile分析查询语句
- 查看慢查询日志，找出执行时间长的sql语句优化
扫一扫
682
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
