Mysql基础（二）

一、权限类别

库级别、表级别、字段级别、管理类、程序类
1.1 管理类：

CREATE TEMPORARY TABLES
CREATE USER
FILE
SUPER
SHOW DATABASES
RELOAD
SHUTDOWN
REPLICATION SLAVE
REPLICATION CLIENT
LOCK TABLES
PROCESS

1.2 程序类：

FUNCTION
PROCEDURE
TRIGGER
CREATE,ALTER,DROP,EXCUTE

1.3 库和表级别：TABLE or DATABASE

ALTER
CREATE
CREATE VIEW
DROP
INDEX
SHOW VIEW
GRANT OPTION: 能够把自己获得的权限赠给其他用户一个副本；

1.4 数据操作：

SELECT
INSERT
DELTE
UPDATE

1.5 字段级别：

SELECT(col1,col2,...)
UPDATE(col1,col2,...)
INSERT(col1,col2,...)

1.6 所有：ALL PRIVILEGES,ALL
1.7 元数据数据库：mysql
授权表：

db, host, user
columns_privs, tables_priv, procs_priv, proxies_priv

二、用户账号：

'USERNAME'@'HOST':
	@'HOST':
	主机名：
	IP地址 或Network
	通配符：%，_： 如 172.16.%.%

创建用户：CREATE USER

CREATE USER 'USERNAME'@'HOST' [IDENTIFIED BY 'PASSWORD'];

查看用户获得的权限：SHOW GRANTS FOR

SHOW GRANTS FOR 'USERNAME'@'HOST'

用户重命名： RENAME USER

RENAME USER old_user_name TO new_user_name;

删除用户：

DROP USER 'USERNAME'@'HOST'

修改密码：

（1）SET PASSWORD FOR
（2）UPDATE mysql.user SET password=PASSWORD('your_password') WHERE clause;
（3）mysqladmin password
mysqladmin [OPTIONS] command command... ...
-u , -h, -p

忘记管理员密码的解决办法：

• 启动mysqld进程时，为其使用， --skip-grant-tables --skip-networking
• vim /etc/rc.d/init.d/mysqld
• 使用UPDATE命令修改管理员密码
• 关闭mysqld进程，移除上述2个选项，重启mysqld；

========================================================
1．修改MySQL的登录设置：

# vim /etc/my.cnf 
在[mysqld]的段中加上一句：skip-grant-tables 
例如： 
[mysqld] 
datadir=/var/lib/mysql 
socket=/var/lib/mysql/mysql.sock 
skip-grant-tables 
保存并且退出vi。

2．重新启动mysqld

# service mysqld restart 
Stopping MySQL: [ OK ] 
Starting MySQL: [ OK ]

3．登录并修改MySQL的root密码

# mysql 
Welcome to the MySQL monitor. Commands end with ; or \g. 
Your MySQL connection id is 3 to server version: 3.23.56 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer. 
mysql> USE mysql ; 
Database changed 
mysql> UPDATE user SET Password = password ( 'new-password' ) WHERE User = 'root' ; 
Query OK, 0 rows affected (0.00 sec) 
Rows matched: 2 Changed: 0 Warnings: 0 
mysql> flush privileges ; 
Query OK, 0 rows affected (0.01 sec) 
mysql> quit

4．将MySQL的登录设置修改回来

# vim /etc/my.cnf 
将刚才在[mysqld]的段中加上的skip-grant-tables删除 
保存并且退出vim

5．重新启动mysqld

# service mysqld restart 
Stopping MySQL: [ OK ] 
Starting MySQL: [ OK ]

=============================================

三、授权：GRANT

GRANT priv_type[,...] ON [{table|function|procedure}] db.{table| routine} TO 'USERNAME'@'HOST' [IDENTIFIED BY 'PASSWORD'] [REQUIRE SSL] [WITH with_option]
with_option:
GRANT OPTION
| MAX_QUERIES_PRE_HOUR count
| MAX_UPDATES_PRE_HOUR count
| MAX_CONNECTIONS_PRE_HOUR count
| MAX_USER_CONNECTIONS count

取消授权：REVOKE

REVOKE priv_type [(column_list)]  [, priv_type [(column_list)]] ... ON [object_type] priv_level FROM user [, user] ...

四、查询缓存

4.1 如何判断是否命中：
通过查询语句的哈希值判断：哈希值考虑的因素包括查询本身、要查询的数据库、客户端使用协议版本, … …
查询语句任何字符上的不同，都会导致缓存不能命中
4.2 哪些查询可能不会被缓存？
查询中包含UDF、存储函数、用户自定义变量、临时表、mysql库中系统表、或者包含列级权限的表、有着不确定值的函数（Now()）;
4.3 查询缓存相关的服务器变量：
query_cache_min_res_unit:查询缓存中内存块的最小分配单位；
较小值会减少浪费，但会导致更频繁的内存分配操作；
较大值会带来浪费，会导致碎片过多；
query_cache_limit:能够缓存的最大查询结果；
对于有着较大结果的查询语句：建议在SELECT中使用SQL_NO_CACHE
query_cache_size:查询缓存总共可用的内存空间，单位是字节，必须是1024的整数倍；
query_cache_type:ON, OFF, DEMAND
query_cache_wlock_invalidate:如果某表被其他的连接锁定，是否仍然可以从查询缓存中返回结果；默认值为OFF，表示可以在表被其它连接场景中继续从缓存返回数据，ON表示不允许；
4.4 查询相关的状态变量

mysql> SHOW GLOBAL STATUS LIKE 'Qcache%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 1031832 |
| Qcache_hits             | 0       |
| Qcache_inserts          | 0       |
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 29      |
| Qcache_queries_in_cache | 0       |
| Qcache_total_blocks     | 1       |
+-------------------------+---------+

缓存命中率的评估：Qcache_hits/(Qcache_hits+Com_select)

五、Mysql中的索引

**5.1 基本法则：**索引应该构建在被用作查询条件的字段上；
5.2 索引类型：
（1）B+ Tree索引
顺序存储，每一个叶子节点到根结点的距离是相同的；左前缀索引，适合查询范围类的数据；
可以使用B+Tree索引的查询类型：全键值、键值范围或键前缀查找：
全值匹配：精确某个值
匹配最左前缀：只明确匹配起头部分
匹配范围值
精确匹配某一列兵范围匹配另一列
只访问索引的查询

不适合使用B+Tree索引的场景：
如果不从最左列开始，索引无效
不能跳过索引中的列
如果查询中某个列是为范围查询，那么其右侧的列都无法再使用索引优化查询
（2）Hash索引
基于哈希表实现，特别使用于精确匹配索引中的所有列；
注意：只有Memory存储引擎支持显式hash索引
使用场景：
只支持等值比较查询：包括-, IN(), <->;
不适合使用hash索引的场景：
存储的非为值得顺序，因此，不适用于顺序查询；
不支持模糊匹配，
（3）空间索引（R-Tree）
MyISAM支持空间索引；
（4）全文索引（FULLTEXT）
在文本中查找关键词

5.3 索引优点：
索引可以降低服务需要扫描的数据量，减少了10次数；
索引可以帮助服务器避免排序和使用临时表；
索引可以帮助将随机I/O转为顺序I/O;
5.4 高性能索引策略：
独立使用列，尽量避免其参与运算；
左前缀索引，索引构建于字段的左侧的多少个字符，要通过索引选择性来评估
(索引选择性：不重复的索引值和数据表的记录总数的比值；)
多列索引：AND操作时更适合使用多列索引；
选择合适的索引列次序：将选择性最高放左侧；
5.5 冗余和重复索引
不好的索引使用策略
5.6 通过EXPLAIN来分析索引的有效性：

EXPLAIN SELECT clause

获取查询执行计划信息，用来查看查询优化器如何执行查询；
输出：
（1）id：当前查询语句中，每个SELECT语句的编号；
复杂类型的查询有三种：

• 简单子查询；
• 用于FROM中的子查询；
• 联合查询：UNION; （注意UNION查询的分析结果会出现一额外匿名临时表）

（2）select_type:
简单查询为SIMPLE;
复杂查询：

• SUBQUERY: 简单子查询；
• DERIVED:用于FROM中的子查询；
• UNION: UNION语句的第一个之后的SELECT语句；
• UNION RESTFUL:匿名临时表

（3）table：SELECT语句关联到的表；
（4）type：关联类型，或访问类型，即MySQL决定的如何去查询表中的行的方式；
ALL:全表扫描；
index：根据索引的次序进行全表扫描；如果在Extra列出现“Using index”表示了使用覆盖索引，而非全表扫描；
range：有范围限制的根据索引实现范围扫描；扫描位置始于索引中的某一点，结束于另一点；
ref：根据索引返回表中匹配某单个值的所有行；
eq_ref: 仅返回一个行，但需要额外与某个参考值做比较；
const，system：直接返回单个行；
（5）possible_keys:查询可能会用到的索引；
（6）key：查询中使用了的索引；
（7）key_len: 在索引使用的字节数；
（8）ref：在利用key字段所表示的索引完成查询时所有的列或某常量值；
（9）rows： MySQL估计为找所有的目标行而需要读取的行的行数；
（10）Extra: 额外信息

Using index: MYSQL讲会使用覆盖索引，以避免访问表；
Using where：MySQL服务器将在存储引擎检索后，再进行一次过滤；
Using temporary: MySQL对结果排序时会使用临时表；
Using filesort: 对结果使用一个外部索引排序；

六、MySQL存储引擎

**6.1 表类型：**CREATE TABLE … ENGINE=
6.2 InnoDB:
处理大量的短期事务；
数据存储于“表空间（table space）”中：
（1）所有InnoDB表的数据和索引放置于同一个表空间中：
表空间文件：datadir定义的目录下
数据文件：ibddata1, ibddata2, …
（2）每个表单独使用一个表空间存储表的数据和索引：

innodb_file_per_table=ON

数据文件（存储数据和索引）：tb1_name.ibd
基于MVCC来支持高并发，支持所有的四个隔离级别，默认级别为REPEATABLE READ;间隙锁防止幻读；
使用聚集索引
支持“自适应hash索引”
锁粒度：行级锁
6.3 MariaDB（XtraDB（percona））
数据存储：表空间
并发：MVCC、间隙锁
索引：聚集索引、辅助索引
性能：预计操作、自适应hash、插入缓存区
备份：支持热备(xtrabacup)
6.4 MyISAM
支持全文索引（FULLTEXT index）、压缩、空间函数（GIS）；但不支持事务，且为表级锁；
崩溃后无法安全恢复；
适用场景：只读（或者写较少）、表较小（可以接受长时间进行修复操作）
Aria：crash-safe
文件：

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

特性：

加锁和并发：表级锁
修复：手工或自动修复，但可能丢失数据

索引：非聚集索引
延迟更新索引键：
压缩表

行格式：dynamic，fixed，compressed, compact, redundent

6.5 其他存储引擎

• CSV: 将普通的CSV（字段通过逗号分隔）作为MySQL表使用； MRG_MYISAM：将多个MyISAM表合并成为一个虚拟表；
• BLACKHOME：类似于/dev/null，不真正存储任何数据 MEMORY：所有数据都保存于内存中…内存表；
• ARCHIVE：只支持SELECT和INSERT操作；支持行级锁和专用缓存
• FEDERATED：用于访问其他远程MySQL服务器一个代理，它通过创建一个到远程MySQL服务器的客户端连接，并将查询到远程服务器执行，而后完成数据存取；在MariaDB上实现的是FederatedX

6.6 MariaDB支持的其他存储引擎：

OQGraph, SphinxSE, TOKUdb, Cassandra, CONNECT, SQUENCE

七、并发控制

锁：读锁：共享锁；写锁：独占锁
锁粒度：表级锁、行级锁
锁策略：在锁粒度及数据安全性寻求的平衡机制；
每种存储引擎都可以自行实现其锁策略和锁粒度；
MySQL在服务器及也实现了锁，表级锁；用户可显示请求
（1）LOCK TABLES

    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...

lock_type: {
    READ [LOCAL]
  | [LOW_PRIORITY] WRITE
}

UNLOCK TABLES

（2）FLUSH TABLES tb_name[,...] [WITH READ LOCK]
（3）SELECT clase [FOR UPDATE] [WITH READ LOCK]

mysql> SHOW GLOBAL  VARIABLES LIKE '%ssl%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| have_openssl  | YES             |
| have_ssl      | YES             |
| ssl_ca        | ca.pem          |
| ssl_capath    |                 |
| ssl_cert      | server-cert.pem |
| ssl_cipher    |                 |
| ssl_crl       |                 |
| ssl_crlpath   |                 |
| ssl_key       | server-key.pem  |
+---------------+-----------------+

分类：
隐式锁：由存储引擎自动施加锁；
显式锁：

八、MySQL事务

8.1 事务：一组原子性的SQL查询，或者说一个独立工作单元。
ACID测试：

• A：atomicity,原子性：是个事务中的所有操作要么全部成功执行，要么全部失败后回滚；
• C: consistency, 一致性：数据库总是从一个一致性状态转换为另一个一致性状态；
• I：Isolation,隔离性：一个事务所作出的操作在提交之前，是不能为其它所见；
• D：durability，持久性，一旦事务提交，其所做的修改会永久保存于数据库中；

启动事务： START TRANSACTION
结束事务：
（1）COMMIT: 提交
（2）ROLLBACK：回滚
注意：只有事务型存储引擎方能支持此类操作；
建议：显式请求和提交事务，而不要使用“自动提交”功能；autocommit=[0|1]

mysql> show variables like '%auto%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| auto_increment_increment    | 1     |
| auto_increment_offset       | 1     |
| autocommit                  | ON    |
| automatic_sp_privileges     | ON    |
| innodb_autoextend_increment | 64    |
| innodb_autoinc_lock_mode    | 1     |
| innodb_stats_auto_recalc    | ON    |
| sql_auto_is_null            | OFF   |

8.2 事务支持savepoint：

mysql> help savepoint;
SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier
RELEASE SAVEPOINT identifier

8.3 事务隔离级别：

• READ UNCOMMITED（读未提交）
• READ COMMITED（读提交）
• REPEATABLE READ (可重读)
• SERIALIZABILE（可串行化）

====================================================================

==========================================================================================
隔离级别                  脏读（Dirty Read） 不可重复读（NonRepeatable Read）  幻读（Phantom Read） 

===========================================================================================
未提交读（Read uncommitted）    可能              可能                       可能
已提交读（Read committed）      不可能            可能                       可能
可重复读（Repeatable read）     不可能            不可能                     可能
可串行化（Serializable ）       不可能            不可能                     不可能
===========================================================================================

• ·未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据
• ·提交读(Read Committed)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)
• ·可重复读(Repeated Read)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读
• ·串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞

可能存在问题：

1. 脏读：指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。
2. 不可重复读：是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。不可重复读：是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。
3. 幻读：第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。幻读：第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。
   当隔离级别是可重复读，且禁用innodb_locks_unsafe_for_binlog的情况下，在搜索和扫描index的时候使用的next-key
   locks可以避免幻读。
4. 加锁读：

tx_isolation:服务器变量，默认为REPEATABLE-READ,可在SESSION级进行修改；

mysql> SHOW GLOBAL VARIABLES LIKE '%isola%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
| tx_isolation          | REPEATABLE-READ |
+-----------------------+-----------------+

MVCC：多版本并发控制
8.4、死锁
两个或多个事务在同一资源相互占用，并请求锁定对方占用的资源的状态。
8.5 事务日志：
事务日志的写入类型为“通知”，因此其操作为“顺序IO”；此日志通常也被称为“预写式日志(write ahead logging)”

innodb_log_file_size
innodb_log_files_in_group
innodb_log_group_home_dir