1、MySQL基础 2、数据库操作 3、MySQL用户和权限管理 4、查询缓存 5、索引 6、MySQL存储引擎 7、并发访问控制 ================================================================================================================================================ MySQL基础 1、RDBMS 2、配置文件 3、安装后设定 4、服务器变量、状态变量 5、sql_mod 6、数据引擎 7、查看表状态信息 1、RDBMS 关系型数据库管理系统。通常为C/S架构,客户端通过专有协议与服务器端进行通信。 Orale、Sybase、Infomix、DB2 MySQL、MariaDB、PostgreSQL、SQLlite https://db-engines.com/ # 数据库排名 数据库类型 关系型数据库 RDBMS MySQL、MariaDB、PostgreSQL、SQLlite 非关系型数据库 NoSQL Document store key/value store Graph store Wide column store 搜索引擎 Solr ElasticSearch 时序数据库 2、配置文件 集中式配置,能够为mysql个应用程序提供配置信息 配置段: [mysqld] # MySQL程序的配置信息 [mysqld_safe] # 线程安全的MySQL应用配置 命令行工具配置段: [client] # 对客户端都有效的配置段 [mysql] # MySQL客户端 [mysql_upgrade] [mysqladmin] [mysqlbinlog] [mysqlcheck] [mysqldump] # 数据备份工具配置 [mysqlimport] [mysqlshow] [mysqlslap] 配置文件查找路径顺序(多个参数重读配置,最后读取的生效): /etc/my.cnf /etc/mysql/my.cnf $MYSQL_HOME/my.cnf 传递的命令行参数:--default-extra-file=/path/to/my.cnf 当前用户的家目录下:.my.cnf 3、安装后设定 设定root用户密码 删除匿名用户 DROP USER 'username'@'hostname|IP'; 关闭主机名反解功能 4、服务器变量、状态变量 MySQL客户端工具: mysql # 交互式CLI工具 mysqldump # 备份工具。基于mysql协议向mysqld发起查询请求,并将查询所得的数据转换成inseret语句保存到文本文件中 mysqladmin # 基于MySQL协议管理mysqld mysqllimport# 数据库导入工具 其他非客户端管理工具: myisamchk myisampack 获取mysql程序默认使用配置: mysql --print-defaults mysqld --print-defaults 获取可用参数列表: mysqld --help --verbose 获取运行中的mysql进程使用的参数值: mysql> SHOW GLOBAL VARIABLES; # 全局变量值 mysql> SELECT GLOBAL @@GLOBAL.long_query_time; # 查看某个指定的参数值 mysql> SHOW SESSION VARIABLES; # 会话级别的变量值 Note:其中有些参数支持运行时修改,立即生效,有些不支持,且只能修改配置文件重启生效 修改[服务器变量值]: 全局变量修改: mysql> SET GLOBAL system_var_name=value; mysql> SET @@global.system_var_name=value; 会话变量修改: mysql> SET [SESSION] system_var_name=value; mysql> SET @@[session].system_var_name=value; 查看[状态变量](用于保存mysqld运行中的统计数据的变量): mysql> SHOW GLOBAL STATUS; mysql> SHOW [SESSION] STATUS; 5、sql_mod 定义mysqld对约束等的响应行为 修改方式: set GLOBAL sql_mod='MODE'; set @@global.sql_mod='MODE'; 对已经建立的会话无效。 查看sql_mod的值: mysql> SHOW GLOBAL VARIABLES LIKE 'sql_%'; 常用mod: TRADITIONAL # 传统模型,不允许对非法数据操作 STRICT_TRANS_TABLES # 对支持事务的表做严格约束 STRICT_ALL_TABLES # 对所有表做严格约束 6、数据引擎 Storage Engine是指标类型,即表在创建时被指明使用的存储引擎。同一个数据库中的表可以使用不同的存储引擎,但通常不建议这样做。 7、查看表状态信息 SHOW TABLE STATUS LIBKE 'table_name'\G; mysql root@localhost:mysql> SHOW TABLE STATUS LIKE 'user'\G; ***************************[ 1. row ]*************************** Name | user Engine | MyISAM Version | 10 Row_format | Dynamic # 行格式类型 Rows | 2 # 行数 Avg_row_length | 126 # 现有表的平均长度 Data_length | 252 # 数据长度 Max_data_length | 281474976710655 # 表数据最大容量 Index_length | 4096 # 索引大小 Data_free | 0 # 已经分配的空间,但尚未存入的数据 Auto_increment | <null> # 自动增长字段下一条数据的增长值 Create_time | 2017-10-25 17:58:28 Update_time | 2017-10-25 19:17:49 Check_time | <null> Collation | utf8_bin # 字符编码 Checksum | <null> Create_options | # 创建表示指明的徐昂想 Comment | Users and global privileges # 注释信息 ================================================================================================================================================ 数据库操作 1、操作索引 索引是特殊的数据结构,能够加速数据的查询。索引通常定义在查找条件的字段上。索引也可以索引名称。索引 只能删除和创建。提高查询速度,但占用额外的空间,影响插入速度。 创建索引: CREATE INDEX index_name [index_type] ON tbl_name (index_col_name,...); 查看索引: SHOW INDEXES FROM [db_name.]table_name; 删除索引: DROP INDEX index_name ON tbl_name 2、索引类型 聚集索引: 非聚集索引:数据是否与索引存储在一起,如果存储在一起,则为非聚集索引 主键索引: 稠密索引:是否索引每一个数据项 稀疏索引:是否索引每一个数据项 简单索引|组合索引: 覆盖索引: B+ TREE、HASH、R TREE 左前缀索引:将索引字段的前几个字符作为索引 3、索引操作 查看索引: SHOW {INDEX | INDEXES | KEYS} {FROM | IN} tbl_name [{FROM | IN} db_name] [WHERE expr] 添加索引: ALTER TABLE students ADD INDEX(Age); 查看查询过程: EXPLAIN SELECT * FROM students WHERE Age=53\G; 4、试图 VIEW,虚表。通常不能给视图中插入数据,取决于基表的定义,视图的数据事实上存储在基表上。 创建试图: CREATE VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION] CREATE VIEW test_view AS SELECT StuID,Name,Age FROM students; SELECT * FROM test_view; # 查看视图 删除视图: 5、修改数据 UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ... [WHERE where_condition] [ORDER BY ...] [LIMIT row_count] 6、查询数据 FROM Clause --> WHERE Clause --> GROUP By --> HAVING Clause --> ORDER BY --> SELECT --> LIMIT 7、单表查询: | SELECT | Syntax: | | | | SELECT | | | | [ALL | DISTINCT ] | | | | [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] | | | | select_expr [, select_expr ...] | | | | [FROM table_references | | | | [PARTITION partition_list] | | | | [WHERE where_condition] | | | | [GROUP BY {col_name | expr | position} | | | | [ASC | DESC], ... [WITH ROLLUP]] | | | | [HAVING where_condition] | | | | [ORDER BY {col_name | expr | position} | | | | [ASC | DESC], ...] | | | | [LIMIT {[offset,] row_count | row_count OFFSET offset}] | | | | [PROCEDURE procedure_name(argument_list)] | | | | [INTO OUTFILE 'file_name' | | | | [CHARACTER SET charset_name] | | | | export_options | | | | | INTO DUMPFILE 'file_name' | | | | | INTO var_name [, var_name]] | | | | [FOR UPDATE | LOCK IN SHARE MODE]] 示例: # 取出字段的重复值 DISTINCT SELECT DISTINCT Gender FROM students; # 显示指定存储查询结果到缓存中 SQL_CACHE query_cache_type的值on时,缓存功能才打开 # 指定存储查询结果不存到缓存中 SQL_NO_CACHE query_cache_type的值为DEMAND时,查询缓存按需进行 # 查看缓存命令 SHOW GLOBAL STATUS LIKE 'Com_select'; SHOW GLOBAL STATUS LIKE 'Qcache_hits'; # 字段显示别名 SELECT Name AS XUEKAIXIN FROM students; 1、WHERE子句: WHERE子句是一个布尔型表达式,其中可以使用的: 算数操作符: + - * / % 比较操作符: = != <> BETWEEN main_num AND max_num IN (num1,num2,num3...) IS NULL IS NOT MULL LIKE {可以使用通配符 % _ } RLIKE {使用正则表达式} REGEXP 逻辑操作符: NOT AND OR XOR 异或 示例: SELECT * FROM students WHERE Age > 50; SELECT StuID,Name,Age FROM students WHERE Age BETWEEN 40 AND 100; 2、GROUP BY: 根据指定的条件将查询结果进行分组,用于做聚合运算。avg(),max(),min(),count(),sum() 对分组后的结果进行过滤: HAVING 根据指定字段对查询结果进行排序: ORDER BY 升序:ASC 降序:DESC 示例: SELECT avg(Age),Gender FROM students GROUP BY Gender; SELECT avg(Age) AS 年龄,Gender FROM students GROUP BY Gender HAVING 年龄 > 20; SELECT count(StuID),ClassID FROM students GROUP BY `ClassID`; SELECT count(StuID),ClassID FROM students GROUP BY `ClassID` HAVING ClassID>2 ORDER BY ClassID; SELECT Name,Age FROM students ORDER BY Age DESC; 3、[LIMIT [m],n] 对查询的结果进行输出行数数量限制。m为偏移量、n为取出的行数的数量 示例: SELECT Name,Age FROM students ORDER BY Age DESC LIMIT 7,10; SELECT Name,Age FROM students ORDER BY Age DESC LIMIT 7; 4、[FOR UPDATE | LOCK IN SHARE MODE]] 对查询结果中的数据请求施加锁。 FOR UPDATE:写锁,排它锁 LOCK IN SHARE MODE:读锁,共享锁 练习 (1) 在students表中,查询年龄大于25岁,且为男性的同学的名字和年龄; SELECT Name,Age FROM students WHERE Age > 25 AND Gender='M'; (2) 以ClassID为分组依据,显示每组的平均年龄; SELECT ClassID,avg(Age) AS 平均年龄 FROM students GROUP BY ClassID; (3) 显示第2题中平均年龄大于30的分组及平均年龄; SELECT ClassID,avg(Age) AS 平均年龄 FROM students GROUP BY ClassID HAVING 平均年龄 > 30; (4) 显示以L开头的名字的同学的信息; SELECT * FROM students WHERE Name LIKE 'L%'; (5) 显示TeacherID非空的同学的相关信息; SELECT * FROM students WHERE TeacherID IS NOT NULL; (6) 以年龄排序后,显示年龄最大的前10位同学的信息; SELECT * FROM students ORDER BY Age LIMIT 10; (7) 查询年龄大于等于20岁,小于等于25岁的同学的信息;用三种方法; SELECT * FROM students WHERE Age BETWEEN 20 AND 25; SELECT * FROM students WHERE Age >= 20 AND Age <= 25; SELECT * FROM students WHERE Age RLIKE '2[0-5]'; 8、多表查询 连接方式 交叉连接:笛卡尔乘积 内连接: 等值连接:具有相同意义的字段连接起来 不等值连接 自然连接 自连接 外连接: 左外连接:左表的每一项都有,右表的没有值的项留空。 FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2=col 右外连接 FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2=col 示例: # 查找每个学生的老师名称 SELECT s.Name AS StudentName,t.Name AS TeachersName FROM students AS s,teachers AS t WHERE s.`TeacherID`=t.`TID`; # 查询每个学生所在的班级 SELECT s.Name,c.Class FROM students AS s,classes AS c WHERE s.ClassID=c.ClassID; # 左连接示例 SELECT s.Name,c.Class FROM students AS s LEFT JOIN classes AS c ON s.ClassID=c.ClassID; # 右连接示例 SELECT s.Name,c.Class FROM students AS s RIGHT JOIN classes AS c ON s.ClassID=c.ClassID; 9、子查询 在查询语句中嵌套查询语句,基于某语句的查询结果再次查询的方式。 示例: # 用在WHERE子句中的查询: SELECT Name,Age FROM students WHERE Age > (SELECT avg(Age) FROM students); # 用在IN中的子查询: SELECT Name,Age FROM students WHERE Age IN (SELECT Age FROM teachers); # 用于FROM子句中查询: SELECT avg(Age),ClassID FROM students WHERE ClassID IS NOT NULL GROUP BY ClassID; 10、联合查询 将两个select语句的查询结果合并在一起。 SELECT Name,Age FROM students UNION SELECT Name,Age FROM teachers; 练习 (1) 以ClassID分组,显示每班的同学的人数; SELECT ClassID,count(ClassID) AS 人数 FROM students GROUP BY `ClassID`; (2) 以Gender分组,显示其年龄之和; SELECT Gender,sum(Age) AS 性别年龄之和 FROM students GROUP BY Gender; (3) 以ClassID分组,显示其平均年龄大于25的班级; SELECT avg(Age),ClassID FROM students WHERE Age > 25 GROUP BY ClassID; (4) 以Gender分组,显示各组中年龄大于25的学员的年龄之和; (5) 显示前5位同学的姓名、课程及成绩; (6) 显示其成绩高于80的同学的名称及课程; (7) 求前8位同学每位同学自己两门课的平均成绩,并按降序排列; (8) 显示每门课程课程名称及学习了这门课的同学的个数; ================================================================================================================================================ MySQL用户和权限管理 1、权限类别 库级别 表级别 字段级别 管理类:是否允许创建用户 程序类:是否允许运行某个存储过程和存储函数 管理类: CREATE USER RELOAD LOCK TABLES REPLICATION CLIENT, REPLICATION SLAVE SHUTDOWN FILE SHOW DATABASES PROCESS SUPER 程序类: FUNCTION,PROCEDURE,TRIGGER 操作:CREATE,ALTER,DROP,EXECUTE 库和表级别: CREATE,ALTER,DROP INDEX CREATE VIEW SHOW VIEW GRANT:能够把自己获得的权限生成一个副本转赠给其它用户; OPTION 数据操作: 表: INSERT/DELETE/UPDATE/SELECT 字段: SELECT(col1,col2,...) UPDATE(col1,col2,...) INSERT(col1,col2,...) 所有权限:ALL, ALL PRIVILEGES 2、数据库元数据数据库:mysql 表: +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | event | | func | | general_log | | help_category | | help_keyword | | help_relation | | help_topic | | host | | ndb_binlog_index | | plugin | | proc | | procs_priv | | proxies_priv | | servers | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 3、用户 'USERNAME'@'HOST' 创建用户: CREATE USER 'USERNAME'@'HOST' [IDENTIFIED BY 'password']; 查看用户获得的授权: SHOW GRENTS FOR 'USERNAME'@'HOST'; 重命名用户: RENAME USER old_user_name TO new_user_name; 示例:RENAME USER 'xuekaixin'@'172.18.26.2' TO 'guomi'@'172.18.26.2'; 删除用户: DROP USER 'USERNAME'@'HOST'; 示例:DROP USER 'guomi'@'172.18.26.2'; 修改用户密码: 1、SET PASSWORD FOR 'user'@'host' = PASSWORD('password'); 2、UPDATE mysql.user SET password=PASSWORD('password') WHERE User=' ' [AND Host=' ']; 3、mysqladmin -uUSERNAME -hHOST -p password 'NEW_PASS' 忘记MySQL密码: 1、启动mysqld进程时,使用 --skip-grant-tables 和 --skip-networking 参数 2、UPDATE mysql.user SET password=PASSWORD('password') WHERE User=' '; 3、重新启动mysqld 4、授权 GRANT 语法格式: GRANT ALL PRIVILEGES ON db_name.table_name TO 'user_name'@'hostname' [IDENTIFIED BY 'user-passwd']; # 创建用户并授权访问数据库 | GRANT | Syntax: | | | | GRANT | | | | priv_type [(column_list)] | | | | [, priv_type [(column_list)]] ... | | | | ON [object_type] priv_level | | | | TO user_specification [, user_specification] ... | | | | [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}] | | | | [WITH with_option ...] | | | | | | | | GRANT PROXY ON user_specification | | | | TO user_specification [, user_specification] ... | | | | [WITH GRANT OPTION] | | | | object_type: | | | | TABLE | | | | | FUNCTION | | | | | PROCEDURE | | | | | | | | priv_level: | | | | * | | | | | *.* | | | | | db_name.* | | | | [WITH with_option ...] | | | | | | | | GRANT PROXY ON user_specification | | | | TO user_specification [, user_specification] ... | | | | [WITH GRANT OPTION] | | | | | | | | object_type: | | | | TABLE | | | | | FUNCTION | | | | | PROCEDURE | | | | | | | | priv_level: | | | | * | | | | | *.* | | | | | db_name.* | | | | | db_name.tbl_name | | | | | tbl_name | | | | | db_name.routine_name | | | | | | | | user_specification: | | | | user | | | | [ | | | | IDENTIFIED BY [PASSWORD] 'password' | | | | | IDENTIFIED WITH auth_plugin [AS 'auth_string'] | | | | ] | | | | | | | | ssl_option: | | | | SSL | | | | | X509 | | | | | CIPHER 'cipher' | | | | | ISSUER 'issuer' | | | | | SUBJECT 'subject' | | | | | | | | with_option: | | | | GRANT OPTION | | | | | MAX_QUERIES_PER_HOUR count | | | | | MAX_UPDATES_PER_HOUR count | | | | | MAX_CONNECTIONS_PER_HOUR count | | | | | MAX_USER_CONNECTIONS count | | 5、授权管理 MySQL服务器属性变量查看 SHOW {GLOBAL|SESSION} VARIABLES [LIKE where_condition]; 状态变量查看 SHOW {GLOBAL|SESSION} STATUS [LIKE where_condition]; 用户授权 GRANT ALL PRIVILEGES ON db_name.table_name TO 'user_name'@'hostname' [IDENTIFIED BY 'user-passwd']; # 创建用户并授权访问数据库 ALL # 所有权限 SELECT|ALTER # 查询,修改权限 *.* # 所有库的所有表 db_name.* # 指定库的指定表 db_name.tbl_name # 指定库的指定表 db_name.routine_name # 指定库的存储例程 'user_name'@'172.18.%.%' # 表示172.18网段所有主机都可以登录 'user_'@'172.18.%.%' # 表示172.18网段所有主机且用户名是user开头后面跟一个字符的账号都可以登录 FLUSH PRIVILEGES; # 通知mysql服务器重读授权表 查看某个用户的授权信息 SHOW GRANTS FOR 'username'@'hostname'; SHOW GRANTS FOR CURRENT_USER; # 查看当前用户的授权 6、回收授权 REVOKE priv_type,.... ON db_name,tb_name FROM 'user_name'@'hostname'; 示例: REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost'; REVOKE ALL PRIVILEGES ON xjx_data.* FROM 'rem'@'172.18.26.1'; Note:MariaDB 服务进程启动时都会读取 mysql 库中的所有授权表至内存中。GRANT 或者 REVOKE 等执行权限操作 会保存于表中,MariaDBd 的服务进程会自动重读授权表。 对于不能够或者不能及时重读授权表的命令,可手动让 MariaDB 的服务进程重读授权表:FLUSH PRIVILEGES ================================================================================================================================================ 查询缓存 1、缓存空间过大带来的问题 随着计算机的核心数不断增多,当并发多个请求连接时并查询数据时,每个核心都能够独立的完成一个查询语句的执行,运行之前都需要去查询缓存 是否可能命中,因此缓存可能成为资源竞争的单点和热点。从而造成性能的瓶颈,甚至造成系统的僵死宕机。 MySQL的缓存全部维持在内存中,存储在内存中的有一个问题就是可能会带来内存碎片。 优化MySQL缓存性能: {SHOW GLOBAL VARIABLES LIKE 'query%';} query_cache_min_res_unit=4096 # 给查询结果分配的内存块的最小分配单位,值太小,会减少浪费,但可能会导致更频繁的内存分配操作, 值太大会带来浪费,会导致碎片过多。此值单位为字节 query_cache_limit=1048576 # 能够缓存的最大查询结果,对于有较大结果的查询语句,建议使用SQL_NO_CACHE {单语句可缓存最大值} query_cache_size=10240000 # 缓存可用的最大空间,单位是字节,必须是1024的倍数 query_cache_type=ON|OFF|DEMAND # 是都启用缓存功能。DEMAND表示按需缓存,仅缓存SELECT语句中带SQL_CACHE的查询结果 query_cache_wlock_invalidate=OFF # 如果某表被其他连接锁定,是否仍然可以从查询缓存中返回结果。OFF表示允许 2、如何判断是否命中缓存 通过查询语句的哈希值判断是否命中。哈希值计算比对考虑的因素还有:查询的数据库、客户端使用的协议版本等,以及查询语句任何字符上的不同 都可能导致缓存不能命中。 3、有可能不会被缓存的数据 查询语句中包含用户自定义函数UDF、存储函数、临时表、自定义变量、包含mysql库系统表、有着不确定值的函数。 查询语句中包含UDF(User-Defined Functions) 存储函数 用户自定义变量 临时表 mysql系统表或者包含列级别权限的查询 有着不确定据俄国值的函数(date()) 4、缓存命中率的评估 Qcache_hits/(Qcache_hits+Com_select)=命中率 mariadb root@localhost:(none)> SHOW GLOBAL STATUS LIKE 'Qcache%'; mariadb root@localhost:(none)> SHOW GLOBAL STATUS LIKE '%select%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Qcache_free_blocks | 0 | | Qcache_free_memory | 0 | # 空闲的内存空间 | Qcache_hits | 0 | # 命中次数 | Qcache_inserts | 0 | # 向缓存中插入缓存结果的次数 | Qcache_lowmem_prunes | 0 | # 多少次向内存中存放缓存时因为内存太小而不得不清理缓存的次数 | Qcache_not_cached | 0 | # 可缓存但没有被缓存的次数 | Qcache_queries_in_cache | 0 | # 当前缓存中被缓存的查询语句个数 | Qcache_total_blocks | 0 | # 缓存可使用的内存块数 +-------------------------+-------+ ================================================================================================================================================ 索引 1、索引应该构建在被用作查询条件的字段上。索引类型: 1、B+ Tree索引:顺序存储,每一个叶子节点到根节点的距离是相同的。顺序存储、左前缀索引、适合于范围类型的数据查询 2、哈希索引:基于哈希表实现,数据结构是键值的的方式。将哈希值进行排序,查询时先将查询请求做哈希计算,然后在比对查找。 哈希索引的查询方式中,数据无论是多少,查询的时间都是恒定的。 3、R-Tree索引(空间索引):MyISAM支持空间索引 4、全文索引(FULLTEXT):在文本中查找关键词 2、B-Tree索引 适合使用B-Tree索引的查询类型,全键值、键值范围或键值前缀查找: 全键值匹配:精确某个值,"xxxxx" 最左前缀匹配:只精确匹配起头部分 LIKE 'xxxxx%' 范围值匹配: 精确匹配某一列并范围匹配另一列: 只访问索引的查询:值查询索引就能够找到数据 不适合使用B-Tree的索引: 如果不从最左侧时开始时,索引引无效 index(age,Fname), WHERE Fname='Jerry'; (无效), WHERE age>30 AND Fname='Smith';(有效) 不能跳过索引中的列: index(name,age,gender) WHERE name='black' and age > 30; WHERE name='black' AND gender='F'; 查询中某个列是范围查询,右侧的列是无法再使用索引优化的查询 WHERE age>30 AND Fname='Smith'; 3、哈希索引 哈希索引适合精确查找,支持等值比较查询。只有Memory存储引擎支持显示hash索引。不适用于与顺序查询,不支持模糊匹配。 4、索引优点 索引可以降低服务需要扫描的数据量,减少I/O次数; 帮助服务器避免排序和使用临时表; 能够将随机I/O转化为顺序I/O; 5、高性能索引 查询语句中要使用独立的列,如果列参与了运算,则无法使用索引; 例如 WHERE age+2 > 18 左前缀索引:索引构建于左侧的多少个字符,要通过索引选择评估。不重复的索引值和数据表的记录总数的比值; 多列索引:AND 操作更适合使用多列索引。尽量使用多列索引,而非多个单键索引 WHERE gender='F' AND age>18 index(gender) index(age) {低效率} index(gender,age) {有效} 6、通过 EXPALIN 分析索引的有效性 EXPLAIN SELECT clause 通过获取查询执行计划的信息,用开查询查询优化器如何查询。 mariadb root@localhost:hellodb> EXPLAIN SELECT Name FROM students WHERE StuID > 10\G; ***************************[ 1. row ]*************************** id | 1 # 编号。表示当前查询语句中,每个SELECT语句的编号 select_type | SIMPLE # 查询类型,标明当前语句查询的类型。SIMPLE、SUBQUERY、DERIVED(用于FROM中的子查询) UNION(UNION语句的第一个之后的SELECT语句)、UNION RESULT table | students # 查询所关联到的表 type | ALL # 关联到类型。[ALL]表示全表扫描,[index]表示索引的次序进行全表扫描,如果在Extra列出现Using index,则表示使用了覆盖索引, 非全表扫描,[range]有范围限制的进行范围索引扫描,扫描位置始于索引中的某一点到另一点。[ref]表示根据索引匹配某单个值的 所在行,表示有多个值是一样的,[eq_ref]仅返回一个行,但与需要额外与某个参考值做比较,[const,system],直接返回单个行,此 种性能最好。[cost] [system] 与某个常量做比较,且只返回一行,性能最好 possible_keys | PRIMARY # 查询可能会用到的索引 key | <null> # 查询中使用了的索引 key_len | <null> # 索引中使用的字节数 ref | <null> # 在利用key字段所表示的索引完成查询时所有的列或者某常量值 rows | 25 # 为了完成查询所要读取到的行 Extra | Using where # 额外信息。 Useing index:MySQL将会使用覆盖索引,避免访问表 Useing where:MySQL服务器将在存储引擎检索后,再进行一次过滤 Useing temporary:多结果排序时使用了临时表 Useing filesort:对结果使用一个外部索引排序 ================================================================================================================================================ MySQL存储引擎 1、存储引擎 插件式存储引擎 InnoDB 支持事务 在 MariaDB 上的实现是 Percona 公司实现了 XtraDB 存储引擎。Percona 有自主研发的数据库 Percona-server。 表文件表现格式: 数据存储于表空间中,InnoDB使用表空间的方式有两种,第一种是将所有的数据和索引放置于同一个表空间中,第二种是单独使用一个表空间存储表 的数据和索引,使用innodb_file_per_table=ON参数开启此功能。第一种方式的表空间文件表现为在datadir目录下的ibddata1、ibddata2 .... 等 文件。第二种方式的表空间文件表现为在datadir目录下的xuekaixin.ibd xuekaixin.frm(表格式).... 等文件,但此种方式需要 innodb_file_per_talbe=ON配置。 特性: 事务性存储引擎,适合对事务要求较高的场景中,但比较适用于大量短期事务 基于MVCC(Mutil Version Concurrency Control)支持高并发 支持所有的四个隔离级别,默认级别是REPEATABLE READ 聚集索引:主键索引是聚集索引。{聚集索引只能有一个} 数据和索引在一起 支持自适应hash索引: 行级锁 {锁力度} 间隙锁防止幻读 支持热备 MyISAM 不支持事务 在 MariaDB 上实现是 Aria。适用于只读或写少的场景或者表较小可接受长时间修复操作。每个表保存在一个文件中。 表文件表现格式: 每个表表现为三个文件。 classes.frm # 表格式定义 classes.MYD # 数据文件 classes.MYI # 索引文件 特性: 支持全文索引(FULLTEXT index) 压缩 空间函数(GIS) 不支持事务 表级锁 奔溃后无法安全恢复 非聚集索引 延迟更新索引键 支持压缩表 其他存储引擎: CSV # 将普通的CSV作为MySQL表使用 MRG_MYISAM # 将多个MyISAM表合并为一个虚拟表 MEMORY # 所有数据都保存于内存中,支持hash索引,表级锁 BLACKHOLE # 存储于BLACKHOLE的表都将丢弃。类似于/dev/null MyISAM InnoDB ARCHIVE # 仅支持SELECT和INSERT操作。支持行级锁和专用缓存区 FEDERATED # 自动将自己扮演为一个MySQL客户端,向远程服务器连接并将查询传输到远程服务器执行,而后完成数据存取 PERFORMANE_SCHEMA # 伪存储引擎,只有MySQL启动后,才启用 Aria MariaDB支持的其他存储引擎: OQGraph SphinxSE TokuDB Cassandra 2、相关命令 SHOW ENGINES; # 查看当前数据库支持的数据库 SHOW TABLE STATUS LIKE '' WHERE Engine='InnoDB'; SHOW ENGINE engine_name STATUS; 3、InnoDB存储引擎相关的变量参数 innodb_buffer_pool_size = 134217728 # 默认为128M,innodb存储引擎使用的内存大小。主要作用在于缓存innodb存储引擎的语句的缓存大小。 缓存索引、数据、插入数据时的缓冲区等。专用服务器建议使用物理内存的百分之七十到百分之八十。 如果数据集本身较小,可根据数据变化幅度等设定合理值。MySQL-5.7之后的版本支持动态修改此参数。 innodb_buffer_pool_instances = 1 # 将innodb_buffer_pool_size定义的内存大小定义为多少个单独管理的内存段 innodb_file_per_talbe = 1 # 对每个表都使用单独的表空间 innodb_flush_logs_at_trx_commit = 1 # 0:表示log buffer(内存)每秒一次同步到log gile中,且同时会进行log file到data file的同步操作。 1:表示每次提交时,log buffer同步到log file,同时进行log file到data file的同步操作。 2:表示每次提交时,log buffer同步到log file,但不会同时进行log file到data file的同步操作。 innodb_read_io_threads = 4 # 文件读写io线程,可根据cpu数量和并发能力调整 innodb_write_io_threads = 4 # 文件读写io线程,可根据cpu数量和并发能力调整 innodb_open_files = 300 # innodb可打开的文件数量上限 innodb_flush_method = 8 # 通常为CPU数量的2倍 ================================================================================================================================================ 并发访问控制 基于MVCC支持高并发 1、锁类型 读锁,共享锁。可被多个读操作共享,但其他线程不写 写锁,独占锁。一个线程加写锁之后,其他线程既不能写,也不能读 2、锁粒度(锁策略) 表级锁:并发性较低 行级锁:并发性高,但维持锁状态的成本较大 3、锁类别 隐式锁:用户手动请求施加的锁 显示锁:存储引擎自行根据需要施加锁 4、手动对表添加锁 方式一: LOCK TABLES tbl_name [[AS] alias] lock_type[, tbl_name [[AS] alias] lock_type] ...; UNLOCK TABLES; lock_type: READ [LOCAL] [LOW_PRIORITY] WRITE 示例: LOCK TABLES students WRITE; UNLOCK TABLES; EXPLAIN SELECT * FROM students; 方法二: FLUSH TABLES; # 将内存中的数据同步到磁盘 FLUSH TABLES [tbl_name,...] WITH READ LOCK; UNLOCK TABLES; 方法三: SELECT cluase [FOR UPDATE | LOCK IN SHARE MODE] ================================================================================================================================================