Mysql高级-存储引擎、驱动、索引、优化

发表于

MySQL5.1版本前默认存储引擎是myisam
MySQL5.5版本开始默认存储引擎是innodb

字段类型

锁

  • myisam 和 memory 存储引擎采用的是表级锁
  • BDB存储引擎采用的是页面锁，但也支持表级锁

  • innodb 存储引擎既支持行锁也支持表锁，默认采用行锁

  • 表级锁：开销小，加锁快，不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低

  • 行级锁：开销大，加锁慢，会出现死锁，锁定粒度小，发生锁冲突的概率最低，并发度也最高
  • 页面锁：介于上述二者之间
1
2
3
4
5
6
myisam 表锁争用情况
> show status like 'table_lock%';
table_lock_waited  值高有着叫严重的表锁争用情况
table_lock_immediate

table_lock_immediate/table_lock_waited > 5000 争用情况正常

myisam 锁机制

表共享读锁（不会阻塞其它进程对同一表的读请求，但会阻塞同一表的写请求）
表独占写锁（会阻塞其它进程对同一表的读写操作）

并发插入
concurrent_instert
0:不允许并发插入
1:有空洞不允许并发插入，无空洞允许
2:都允许

innodb

1
2
3
4
5
6
> show status like 'innodb_row_lock%';
innodb_row_lock_current_waits:
innodb_row_lock_time
innodb_row_lock_time_avg
innodb_row_lock_time_max
innodb_row_lock_waits
  • 行锁不影响读操作，只影响写操作。同时如果更新语句条件没有加索引，会锁定整个表，但还是不影响读操作

事务

innodb 事务隔离级别

1
2
3
4
5
6
7
8
9
10
11
12
* read uncommitted 	非提交读（会带来涨读）
* read committed 	提交读 
* repeatable read 	可重复读 （会出现幻读）
* serializable 		序列化 （会加锁，让数据不可变）

查看隔离级别
select @@tx_isolation			查看当前会话隔离级别
select @@global.tx_isolation 	查看系统隔离级别

设置隔离级别
set session transaction isolation level xxx 设置档期会话隔离级别
set global transaction isolation level xxx  设置系统全局隔离级别

驱动

php5.3之前定的渠道是libmysql.dll或so 之后是mysqlnd

驱动API

  • msyql （mysql_real_escape_string($name)）转移特深字符，用于msyql_queue() 安全查询
  • mysqli 新增：面向对象接口、绑定语句支持（prepare、bind_param）、多语句支持、事务支持等
  • pdo_mysql 特点：多数据库统一支持

索引和全文检索技术

  1. 普通索引

    如果没有唯一性要求，可以选择普通索引

  2. 唯一索引

    如果列上有唯一性要求，可以选择唯一索引

  3. 全文索引

    如果需要模糊搜索，可以选择全文索引

  4. 组合索引

    如果有多个条件一起查询，可以选择组合索引
    注意最左原则

  • 索引的实现方式

    B+ 树 、聚簇索引 、 非聚簇索引 对中文支持不友好

sql优化

执行顺序

1
2
3
4
5
6
7
8
9
10
11
1. from 子句对其后面的多个表进行笛卡尔积，产生的虚拟表VT1
2. on 对VT1 数据过滤 得到 VT2
3. 将未符合调价的保留表中数据添加到 VT2 中 得到 VT3
4. where 子句对 VT3 过滤 得到 VT4
5. group 对 VT4 得到 VT5
6. cube|roolup 子句进行操作得到 VT6
7. having 对VT6数据进行Having 得到VT7
8. select 从 VT7 中选择要获取的字段 得到 VT8
9. distinct 去重 得到 VT9
10. order by 对VT9 结果进行排序后，形成 VT10
11. limit 从 VT10 中取出指定的数据，形成 VT11 返回给用户

explain

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
EXPLAIN 输出格式
EXPLAIN 命令的输出内容大致如下:

mysql> explain select * from user_info where id = 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_info
   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: 额外的信息

分表

读写分离