MYSQL数据引擎

post thumb
Mysql
作者 Louis 发表于 2018年6月9日

摘要:mysql的数据引擎,服务器配置,query_cache,索引,锁等相关的介绍

MYSQL数据库引擎

MyISAM引擎特点:

  • 不支持事务
  • 表级锁定
  • 读写相互阻塞,写入不能读,读时不能写
  • 只缓存索引
  • 不支持外键约束
  • 不支持聚簇索引
  • 读取数据较快,占用资源较少
  • 不支持MVCC(多版本并发控制机制)高并发
  • 崩溃恢复性较差
  • MySQL5.5.5前默认的数据库引擎

Myisam适用场景:只读,小文件,引擎文件如下

Myisam的引擎文件:

  • tbl_name.frm: 表格式定义
  • tbl_name.MYD: 数据文件
  • tbl_name.MYI: 索引文件

innoDB

InnoDB引擎特点:

  • 行级锁

  • 支持事务,适合处理大量短期事务

  • 读写阻塞与事务隔离级别相关

  • 可缓存数据和索引

  • 崩溃恢复性更好

  • 支持MVCC高并发

  • 从MySQL5.5后支持全文索引

  • 从MySQL5.5.5开始为默认的数据库引擎

  • Locking granularity raw lock

  • transactions 支持事务,适合处理大量短期事务

  • Clustered indexes 聚簇索引

  • Date/Index caches

  • Foreign key support

每个表单独使用一个表空间存储表的数据和索引 启用:innodb_file_per_table=ON 两类文件放在数据库独立目录中 数据文件(存储数据和索引):tb_name.ibd 表格式定义:tb_name.frm。

$vim /etc/my.cnf
[mysqld]
innodb_file_per_table
default_storage_engine=InnDB
$ systemctl restart mariadb
$mysql < hellodb1_innodb.sql
$ll /var/lib/mysql/hellodb1/
-rw-rw----. 1 mysql mysql  8636 Jun  8 10:38 classes.frm
-rw-rw----. 1 mysql mysql 98304 Jun  8 10:38 classes.ibd
  • 查看mysql支持的存储引擎:
show engines;
show variables like '%storage_engine%';
  • 设置默认的存储引擎:
vim /etc/my.conf
[mysqld]
default_storage_engine= InnoDB;
  • 查看库中所有表使用的存储引擎

Show table status from db_name;

  • 查看库中指定表的存储引擎
show table status like ' tb_name ';
show create table tb_name;
  • 设置表的存储引擎:
CREATE TABLE tb_name(... ) ENGINE=InnoDB;
ALTER TABLE tb_name ENGINE=InnoDB;

服务器配置

  • 服务器系统变量:分全局和会话两种
  • 服务器状态变量:分全局和会话两种
  • 获取运行中的mysql进程使用各服务器参数及其值
mysql> SHOW GLOBAL VARIABLES;
mysql> SHOW [SESSION] VARIABLES;

  • 设置服务器系统变量三种方法:

    • 在命令行中设置: shell> ./mysqld_safe –-skip-name-resolve=1;

    • 在配置文件my.cnf中设置: ]#vim /etc/my.cnf

      skip_name_resolve=1;

    • 在mysql客户端使用SET命令: mysql>SET GLOBAL sql_log_bin=0

  • 修改服务器变量的值:

    • mysql> help SET
  • 修改全局变量:仅对修改后新创建的会话有效;对已经建立的会话无效

    • 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;

varialbes

查询一些系统自带的变量,数据文件datadir,baseddir, tmpdir

MariaDB [(none)]> show variables like 'datadir';
MariaDB [(none)]> show variables like 'basedir';
MariaDB [(none)]> show variables like '%dir';

SQL_MODE

**SQL_MODE:**对其设置可以完成一些约束检查的工作,可分别进行全局的设置或当前会话的设置,参看:官方介绍 常见MODE:

  • NO_AUTO_CREATE_USER 禁止GRANT创建密码为空的用户
  • NO_AUTO_VALUE_ON_ZERO 在自增长的列中插入0或NULL将不会是下一个自增长值
  • NO_BACKSLASH_ESCAPES 反斜杠“\”作为普通字符而非转义字符
  • PAD_CHAR_TO_FULL_LENGTH 启用后,对于CHAR类型将不会截断空洞数据
  • PIPES_AS_CONCAT 将"||"视为连接操作符而非“或运算符”
MariaDB [(none)]> SHOW VARIABLES LIKE 'sql_mode';  #默认为空
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+
MariaDB [hellodb]> set sql_mode='NO_AUTO_CREATE_USER';
MariaDB [hellodb]> grant all on *.* to hong@'192.%' ;  #这里授权创建用户就提示错误
ERROR 1133 (42000): Can't find any matching row in the user table
MariaDB [hellodb]> set sql_mode='';
MariaDB [hellodb]> grant all on *.* to hong@'192.%' ; #删除sql_mode,立马生效,

这里的sql_mode权限比较多,set起来比较麻烦,系统自带关键字traditional

traditonal=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

设置sql_mode权限如下:

MariaDB [(none)]> set sql_mode='traditional'
MariaDB [(none)]> show variables like 'sql_mode';  #可以看到一大串的sql_mode了。

Query Cache

  • 优缺点 不需要对SQL语句做任何解析和执行,当然语法解析必须通过在先,直接从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 |	#1M,单个查询结果能缓存的最大值
| query_cache_min_res_unit     | 4096    |	#4k,查询缓存中内存块的最小分配单位
| query_cache_size             | 0       |	#查询缓存总共可用的内存空间,1024的整数倍
| query_cache_strip_comments   | OFF     |	
| query_cache_type             | ON      |	#是否开启缓存功能
| query_cache_wlock_invalidate | OFF     |	#某表被其它的会话锁定,是否从查询返回结果
+------------------------------+---------+
  • 查询缓存相关的状态变量
MariaDB [(none)]> show global status like 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 33536824 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 0        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 4        |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+

解释如下:

  1. Qcache_free_blocks:处于空闲状态 Query Cache中内存 Block 数
  2. Qcache_free_memory:处于空闲状态的 Query Cache 内存总量
  3. Qcache_hits:Query Cache 命中次数
  4. Qcache_inserts:向 Query Cache 中插入新的 Query Cache 的次数,即没有命中的次数
  5. Qcache_lowmem_prunes:当 Query Cache 内存容量不够,需要删除老的Query Cache 以给新的 Cache 对象使用的次数
  6. Qcache_not_cached:没有被 Cache 的 SQL 数,包括无法被 Cache 的 SQL
  7. 及由于 query_cache_type 设置的不会被 Cache 的 SQL语句
  8. Qcache_queries_in_cache:在 Query Cache 中的 SQL 数量
  9. Qcache_total_blocks:Query Cache 中总的 Block

修改query_cache_size的值,保存到配置文件使之生效

$vim /etc/my.cnf
$sed -i '/mysqld\query_cache_size=10M/'  /etc/my.cnf
$systemctl restart mariadb

索引

索引简介

索引:系统根据某种算法,将已有的数据(未来可能增加的数据),单独建立一个文件,文件能实现亏啊苏匹配数据,并且能够快速找到对应表的记录。

索引是特殊数据结构:

  • 定义在查找时作为查找条件的字段

索引实现在存储引擎

优点:

  • 索引可以降低服务需要扫描的数据量,减少了IO次数
  • 索引可以帮助服务器避免排序和使用临时表
  • 索引可以帮助将随机I/O转为顺序I/O

缺点:

  • 占用额外空间,影响插入速度

索引类型:

  • 聚簇(集)索引、非聚簇索引:
    • 数据和索引存储顺序是否一致,数据和所以是否存放一起
  • 主键索引、辅助索引
  • 稠密索引、稀疏索引:是否索引了每一个数据项
  • B+ TREEHASH、R TREE
  • 简单索引、组合索引

左前缀索引:

  • 取前面的字符做索引

覆盖索引:

  • 从索引中即可取出要查询的数据,性能高
  • explain 跟踪解释索引的信息
MariaDB [hellodb]> SET GLOBAL userstat=1;	#查询索引使用情况的开关
MariaDB [hellodb]> explain select * from students where age = 20\G; 
raws: 25
MariaDB [hellodb]> CREATE INDEX INDEX_AGE ON students(AGE); #建立age索引
MariaDB [hellodb]> explain select * from students where age = 20\G; #解析命令查询结果
raws: 2
MariaDB [hellodb]> CREATE INDEX INDEX_NAME ON students(name);
MariaDB [hellodb]> explain select * from students where name like '%b'\G;
MariaDB [hellodb]> SHOW INDEX_STATISTICS;

删除索引,并建立符合索引

drop index index_age on students;
create index index_name_age on students(name,age);
explain select * from students  where  name like'S%'; -- 用到索引
explain select * from students  where  name like'%x%'; -- 没有用到索引
explain select * from students  where  age=19;  	-- 没有用到索引
explain select * from students  where stuid >10 and age=19; -- 用到索引
explain select * from students  where  name='Xi Ren'; -- 用到索引
explain select * from students where age > (select avg(age) from students )\G;
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: students
        type: ALL
*************************** 2. row *************************** 
           id: 2          
  select_type: SUBQUERY
        table: students
         type: index

总结:复合索引的顺序很重要,比如index_name_age,name相当于主索引,age相当于副索引,不同的语法导致会用不到索引。可以用explain比较两条SQL语句查询用到的raws数,比较命令的好坏

B+TREE

  • B+ Tree索引:顺序存储,每一个叶子节点到根结点的距离是相同的;左前缀索引,适合查询范围类的数据
  • 可以使用B-Tree索引的查询类型:

全值匹配:精确所有索引列,如:姓wang,名xiaochun,年龄30 匹配最左前缀:即只使用索引的第一列,如:姓wang 匹配列前缀:只匹配一列值开头部分,如:姓以w开头的 匹配范围值:如:姓ma和姓wang之间 精确匹配某一列并范围匹配另一列:如:姓wang,名以x开头的 只访问索引的查询

B-Tree索引的限制:

  • 如果不从最左列开始,则无法使用索引:如:查找名为xiaochun,或姓为g结尾
  • 不能跳过索引中的列:如:查找姓wang,年龄30的,只能使用索引第一列
  • 如果查询中某个列是为范围查询,那么其右侧的列都无法再使用索引:如:姓wang,名x%,年龄30,只能利用姓和名上面的索引

特别提示:

  • 索引列的顺序和查询语句的写法应相匹配,才能更好的利用索引
  • 为优化性能,可能需要针对相同的列但顺序不同创建不同的索引来满足不同类型的查询需求

Hash 索引

  • Hash索引:基于哈希表实现,只有精确匹配索引中的所有列的查询才有效,索引自身只存储索引列对应的哈希值和数据指针,索引结构紧凑,查询性能好

  • 只有Memory存储引擎支持显式hash索引

  • 适用场景:

    • 只支持等值比较查询,包括=, IN(), <=>
  • 不适合使用hash索引的场景:

    • 不适用于顺序查询:索引存储顺序的不是值的顺序
    • 不支持模糊匹配
    • 不支持范围查询
    • 不支持部分索引列匹配查找:如A,B列索引,只查询A列索引无效

索引优化策略:

  • 独立地使用列:尽量避免其参与运算,独立的列指索引列不能是表达式的一

部分,也不能是函数的参数,在where条件中,始终将索引列单独放在比较 符号的一侧

  • 左前缀索引:构建指定索引字段的左侧的字符数,要通过索引选择性来评估

索引选择性:不重复的索引值和数据表的记录总数的比值

  • 多列索引:AND操作时更适合使用多列索引,而非为每个列创建单独的索引
  • 选择合适的索引列顺序:无排序和分组时,将选择性最高放左侧

索引优化建议:

只要列中含有NULL值,就最好不要在此例设置索引,复合索引如果有NULL值,此列在使用时也不会使用索引;

尽量使用短索引,如果可以,应该制定一个前缀长度;

对于经常在where子句使用的列,最好设置索引;

对于有多个列where或者order by子句,应该建立复合索引;

对于like语句,以%或者‘-’开头的不会使用索引,以%结尾会使用索引;

尽量不要在列上进行运算(函数操作和表达式操作);

尽量不要使用not in和<>操作;

多表连接时,尽量小表驱动大表,即小表 join 大表;

在千万级分页时使用limit;

对于经常使用的查询,可以开启缓存;

大部分情况连接效率远大于子查询.

mysql中提供了多种索引

  1. 主键索引:primary key
  2. 唯一索引:unique key
  3. 全文索引:full
  4. 普通索引:index

并发控制

LOCK TABLES
	tbl_name [[AS] alias] lock_type
	[, tbl_name [[AS] alias] lock_type] ...
lock_type: READ , WRITE
UNLOCK TABLES  #解锁
FLUSH TABLES tb_name[,...] [WITH READ LOCK]
#关闭正在打开的表(清除查询缓存),通常在备份前加全局读锁
MariaDB [hellodb]> SELECT clause [FOR UPDATE | LOCK IN SHARE MODE]
#查询时加写或读锁
  • 锁:

    • 读锁:共享锁,只读不可写,多个读互不阻塞,

      MariaDB [hellodb]> LOCK TABLES students READ;

    • 写锁:独占锁,排它锁,一个写锁会阻塞其它读和它锁

      MariaDB [hellodb]> LOCK TABLES students WRITE;

  • 实现

    • 存储引擎:自行实现其锁策略和锁粒度
    • 服务器级:实现了锁,表级锁;用户可显式请求
  • 分类:

    • 隐式锁:由存储引擎自动施加锁
    • 显式锁:用户手动请求
  • 锁机制: innodb默认是行锁,但是如果在事务操做过程中,没有使用到索引,系统会自动进行全表检索苏剧,自动升级为表所,后续事务会讲到。

    • 行锁:只有当前行被锁住,别的用户不能操作当前行
    • 表所:整张表被锁住,别的用户都不能操作
    • 页锁:显示的整页被锁住

read 锁

自己可以读,但是不可以写;其他人可以读,不可以写。

lock tables students read;
update students set classid=3 where stuid=25;
ERROR 1099 (HY000): Table 'students' was locked with a READ lock and can't be updated

write锁

自己可以读,但是不可以写;其他人不可以读

flush tables students; 		#清除缓存
lock tables students write;
select * from students where stuid=12;
上一篇
DNS服务