Skip to content

Instantly share code, notes, and snippets.

@safecat

safecat/mysql-learn.md

Last active April 14, 2017 04:02
Show Gist options
  • Save safecat/6c3be71363ae2eb6c934 to your computer and use it in GitHub Desktop.
Save safecat/6c3be71363ae2eb6c934 to your computer and use it in GitHub Desktop.
Download ZIP
深入浅出MySQL学习笔记
Raw
mysql-learn.md
  • 安装
    • rpm -ivh mysql-server.rpm
    • rpm -ivh mysql-client.rpm
  • 查看
    • netstat -nlp
    • ./mysqld_safe &
  • ANSI/ISO SQL 是公认的关系数据库标准，PostgreSQL是对其实现最好的开源数据库，但因为其开发目的为教学数据库，故更加追求完美实现标准，性能是短板。

##数据类型

  • 小数
    • 浮点数：float 单精度；double 双精度
    • 定点数：decimal
    • 注意
      • 浮点数后面定义(M,D)是非标准用法，尽量不要使用。
      • 例：float(7,4) 可以显示 -999.9999，超过范围的值会四舍五入
      • 浮点数不指定精度时，会按照系统和硬件的精度。
      • 定点数不指定精度时，会按照(10,0)
      • 定点数在系统内部以字符串方式存放，可以用于货币。
  • CHAR 和 VARCHAR
    • MyISAM中，使用定长列更快。
    • 在InnoDB中，内部行存储格式不区分定长变长，而VARCHAR的字节占用可能更少，所以建议使用VARCHAR。
  • TEXT 和 BLOB
    • 拉出来凑定长表或避免SELECT *的网络传输消耗。

##DML

  • 操作符
    • <=> NULL安全等于
  • HAVING & WHERE 的区别：筛选前后
  • UNION & UNION ALL 的区别：是否排重

##InnoDB

  • 唯一的行锁引擎。
  • 存储方式
    • 共享表空间存储。结构在.frm中。数据在 innodb_data_home_dir 索引在 innodb_data_file_path 中。
    • 多表空间存储。结构在.frm中。每个表的数据和索引单独存在.ibd中。如果是分区表，分区分别对应单独的.ibd，文件名是表名+分区名，在创建分区时指定分区的数据文件位置。需要设置参数 innodb_file_per_table。
  • 支持集群索引、查询缓存。
  • 批量插入速度慢。

MyISAM

  • 不支持外键、事务，访问速度快
  • MYD和MYI可以分放在不同的目录，平均分布IO，获得更快的速度
  • 静态(定长)表(默认)、动态表、压缩表
  • 变长字段频繁改动会有碎片，可用OPTIMIZE来优化。
  • 读取和插入为主，很少的更新和删除，适用。
  • 支持全文索引。

其它引擎

###MEMORY

  • 默认使用HASH索引，可以指定B树索引
  • 表大小受max_heap_table_size影响，默认16M。
  • 唯一支持HASH索引的引擎

###MERGE

  • .frm存储表定义，.MRG存储组合表信息。.MRG可以直接修改，但需要FLUSH TABLES。

##索引

###查询原则

  • 前缀索引：为TEXT或类似类型的字段的前n个字符创建索引
  • 字节和字符数区别。（latin1是单字节字符集，GBK是双字节字符集，UTF-8是1-4字节字符集）。前缀索引字节数不宜超过500字节（引擎限制），注意换算成字符。
  • 如果创建组合索引index(f1, f2)，则where f1='x'可以用到索引，f2='x'不行。
  • MySQL执行OR查询时，实际是对OR的每个字段都分别查询，然后UNION。如果其中有一个字段不使用索引，则两个条件都不使用。
  • 索引查询是类型敏感的，如果查询条件的数据类型不同，则不用扫表转换。

###优化建议

  • 尽量使用前缀索引，查询更快，IO更少，内存中可以容纳更多键值。
  • 导入大批量数据时，可以使用 关闭MyIsam表非唯一索引的更新、按InnoDB表逐渐顺序导入、关闭非唯一校验、关闭自动提交 四种方式来提升速度。
  • 从同一客户端INSERT，可以一次插入多值降低连接消耗；非同一客户端INSERT，可以使用INSERT DELAYED提高速度。
  • LOAD DATA INFILE 比 INSERT 快二十多倍。
  • 在 GROUP BY 语句中，使用 ORDER BY NULL 来禁止排序可以避免排序消耗。
  • 子查询使用索引，但其母查询不使用（即使所有该建的索引都建立了），MySQL会为此创建临时表，所以应当尽量避免此类情况。
  • 拆表、逆范式、中间表。

###索引问题排查

  • handler_read_rnd_next 表示在数据文件中读下一行的请求数，如果过高，说明经常扫表；handler_read_key 表示根据索引读数据的请求数，如果过低，说明索引建立的不好；handler_read_rnd_key 表示根据固定位置读取行的请求数，如果过高，说明你执行很多需要排序的查询，该值会很高。你可能有很多需要完整表扫描的查询，或者你使用了不正确的索引用来多表查询。
  • ORDER BY 使用索引的方式较复杂，建议采用EXPLAIN来确保查询。
    • 如果 ORDER BY 后有多个字段，字段的排序顺序不同，则不使用索引。
    • WHERE 字段和 ORDER BY 字段不同时，不使用索引。

##事务 ###ACID

  • Atomicity，原子性。事务是原子操作单元，其操作要么都执行，要么都不执行。
  • Consistent，一致性。事务的开始和结束时，数据都必须保持一致状态。
  • Isolation，隔离性。事务不受外部并发操作独立运行，中间状态对外部不可见。
  • Durable，持久性。事务对数据的修改是永久性的，即使系统故障也能保持。

##锁机制 ###工作原理

  • 针对表级锁，如果当前是读锁定，则别的请求还可以读或者设定读锁定，但如果当前是写锁定，其它请求就只有等待。
  • 加锁是自动的（SELECT时加读锁，其它加写锁）。
  • 如果查询中使用表别名，则需要对别名也加锁。
  • MyISAM引擎允许并发，受concurrent_insert系统变量制约。
  • MySQL的锁队列中，写锁比读锁的优先级更高，所以在MyISAM有大量写操作时，读操作可能长期等待。
    • 指定启动参数 low-priority-updates 使MyISAM引擎的读请求优先
    • 执行命令 SET LOW_PRIORITY_UPDATES = 1，使该连接的更新请求优先级降低。
    • 指定增、删、改语句的LOW_PRIORITY属性降低优先级。
    • 设定系统参数 max_write_lock_count，设定写锁数量阀值，使读锁有机会获取。
  • 读锁时间过长也会使写锁“饿死”，所以尽量减少此类操作。
  • InnoDB中有共享锁和排它锁，其用途基本类似与MyISAM中的读锁和写锁。
  • InnoDB默认不会给SELECT操作加锁（共享锁也不加），但可以通过在语句尾部加 LOCK IN SHARE MODE 以添加共享锁，加 FOR UPDATE 以添加排它锁。
  • InnoDB的行锁加在索引中的索引项上，所以只有通过索引条件检索数据才会加行锁，否则会加表锁。即使获取的行是不同的，如果索引项一样，也会锁冲突。
  • 如果在where条件中有范围筛选，则这个范围内不存在的记录会被InnoDB加上间隙锁。如果使用相等条件但记录不存在，也会加间隙锁。
  • 在执行CTAS（CREATE TABLE AS SELECT）操作时，InnoDB会给源表加共享锁。此时其它进程可以读取数据，但不能写入数据（无法获取排它锁）。假设CTAS操作时不做锁表操作，虽然实际行为是CTAS操作拷贝了写入前的数据，但BINLOG记录的顺序是先写入再拷贝，这会导致主从数据不一致。如果CTAS操作选取的是源表的范围，则会加间隙锁。设置innodb_locks_unsafe_for_binlog为ON可以确保并发，但可能导致主从不一致。

###死锁和隔离级别

  • 死锁的原因：事务中的锁是逐步获取的，可能出现两个session互相等待锁。
  • InnoDB可以自动探测大部分死锁，并自动解决。但无法探测涉及表锁、外部锁的死锁。
  • 解决死锁的一些办法
    • 并发的两个程序采用相同的顺序访问数据表。
    • 并发的两个程序采用相同的顺序访问数据行。
    • 事务中如果有写入操作，则应该直接申请足够级别的锁，不应等到操作时再申请。
    • 两个进程对同一条不存在的记录加FOR UPDATE锁，同时更新，后一个会死锁退出。
  • 可以通过 table_locks_immediate 和 table_locks_waited 两个变量来查看锁争用状态。
  • InnoDB行锁争用的情况，可以通过 innodb_row_lock% 系列的数据库状态来查看。
  • InnoDB中表锁的使用场景
    • 事务需要更新大部分数据，使用行锁效率低下。
    • 事务涉及多个表，容易造成死锁，干脆锁掉所有涉及表。
  • InnoDB中两个事务并发写入时，每个事务都不知道其它事务的存在，故而可能同时修改同一行数据；如果此时又有读取事务并发，则读取事务读取的可能是脏数据（因为那两个并发写尚未执行完成）；一个写事务如果在另一个写事务完成之后重复读取了开始读过的一行数据，两次取到数据可能不同，甚至可能改行被删除。这些问题被称为 Lost Update, Dirty Reads, Non-repeatable Reads, Phantom Reads。在读写事务前加锁可以避免此类问题，但加锁和并发本身是矛盾的，于是有不同的事务隔离级别来适配各种矛盾情况。
  • 隔离级别表
  • 使用SHOW ENGINE INNODB STATUS查看最后一个死锁发生的原因。

##MySQL配置

  • key_buffer_size MyISAM索引块缓存大小，5.1之后可以指定多个。使用 CACHE INDEX INDEX_NAME,INDEX_NAME2 IN BUFFER_NAME来指定索引存放的索引缓存。预装一个表的全部索引LOAD INDEX INTO CACHE TABLENAME。可以在 mysqld_init.sql中设定MySQL启动时建立表和缓存池的关系。
  • table_cache是用户打开表的缓存数量，每个连接都至少会打开一个。因此，它应该设定为 max_connections * N(每个连接使用表的数量) + M(一些额外的冗余)。可以通过检查 open_tables 和 opened_tables 来校对这个参数。
  • innodb_buffer_pool_size 缓存InnoDB的数据块和索引块，在DB服务器上，可以设为物理内存的80%，设置越大，硬盘IO越少。
  • innodb_flush_log_at_trx_commit 日志和数据写入时机。0每秒一次写入日志和数据；1事务提交时写入日志和数据；2事务提交时写入日志，1秒写一次数据。一条数据的写入方向是：SQL语句->记录日志->从日志将数据写入(flush)磁盘。默认1.
  • innodb_additional_mem_poo_size 数据结构存储池，默认1MB，如果用完，InnoDB会从系统分配内存，并写入Warning。维持默认即可。
  • innodb_lock_wait_timeout 锁最长等待时间。
  • innodb_support_xa 分布式事务，默认1，设为0可提高性能。
  • innodb_log_buffer_size 日志缓存的大小，默认1M，一般8-16M就够了，只需满足1秒的缓存即可。
  • innodb_log_file_size 设定每个日志文件的大小，默认5M，越大性能越快，但灾难恢复时间加大。

##磁盘IO

  • 使用软连接分布IO。
  • 修改fstab禁止系统更新文件的atime属性
    修改fstab：LABEL=/home /home ext3 noatime 1 2
    执行命令：#mount -oremount /home
  • 在裸设备（Raw Device）上存放共享表空间。避免系统缓存对InnoDB的副作用。
  • 应用优化
  • 使用连接池，应用建立一个连接池，需要时取用，避免重复建立连接的消耗。
  • 通过 %query_cache% 相关属性查看和开启查询缓存，通过 %qcache% 相关状态查看缓存状态。
  • 建立主从复制时，如果只需要复制部分表，可以建立虚拟主库，使用BLACKHOLE引擎用于过滤BINLOG，降低传输。
  • 对于没有删除操作的MyISAM表，可以设为插入和查询并行执行。
  • 如果列有默认值，可以在SQL语句中不写，减少MySQL语法分析消耗。
  • 自增字段在高并发下对性能有影响，应尽量避免。
  • BINLOG默认不开启。
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment