mysql 学习记录

二、基本命令

2.1设置数据库区分大小写

• 数据库大小写设置参数
  lower_case_table_names： 此参数不可以动态修改，必须重启数据库
  lower_case_table_names = 1 表名存储在磁盘是小写的，但是比较的时候是不区分大小写
  lower_case_table_names=0 表名存储为给定的大小和比较是区分大小写的
  lower_case_table_names=2, 表名存储为给定的大小写但是比较的时候是小写的
  show variables like '%lower'

2.4 字符集设定

• 启动服务器时使用
  --character-set-server和--collation-server选项
• 创建数据库或者数据表时设置
  create database db_name character set charset collate collation

2.5 数据库的选定

• 数据库的选定

  use db_name 
  select * from samdb.president
  

• 数据库创建
  create database db_name character set utf8 collate utf8_icelandic_ci
• 查看数据库定义
  show create database mydb
• 数据库的删除
  drop database db_name

2.6 存储引擎

• 多种存储引擎

  存储引擎	说明
  archive	用于数据存档的引擎(数据插入后就不能再修改了)
  blackhole	这种存储引擎的写操作是删除数据，读操作是返回空白记录
  csv	这种操作引擎在存储数据时以逗号作为数据项之间的分隔符
  example	示例（存根）存储引擎
  falcon	用来进行事务处理的存储引擎
  federated	用来访问远程数据表的存储引擎
  InnoDb	具备外键支持功能的事务处理引擎
  memory	内存里的数据库
  merge	用来管理由多个MyIsAM数据表构成的数据表集合
  MyISAM	默认的存储引擎
  NDB	MySQL Cluster专用存储引擎

• 数据库移植
  mysqldump命令移植
  二进制移植，即将硬盘文件复制到另一机器，不是所有存储引擎都支持（MyISAM与InnoDb支持），确认两个机器浮点数格式一致
• 创建数据库
  create table mytab (...) engine = innodb
• 创建临时表
  create temporary table tb_name
  创建一个临时表，在与服务器连接断开时会自动消失
• 创建数据表副本
  create table new_tb_name like tb_name 完整保存数据结构
  create table new_tb_name select * from tb_name 不保存数据结构
• 数据库分区

  create table log_partition
  {
      dt datetime not null,
      info varchar(100) not null ,
      index(dt)
  }
  partition by rang(YEAR(dt))
  {
      partition p0 values less than (2005),
      partition p1 values less than (2006),
      partition p2 values less than (2007),
      partition p3 values less than (2008),
      partition p4 values less than MAXVALUE,
  }
  

  alter table log_partition reorganize partition p4
  into (
      partition p4 values less than (2009),
      partition p5 values less than MAXVALUE
  )
  

• 删除数据库
  drop table tb_name
• 增加索引
  alter table tb_name add index index_name (index_columns)
• 删除索引
  drop index index_name on tb_name
• 修改数据表
  alter table tb_name modify column_name char(20)
alter table tb_name change column_name new_column_name char(20)
• 修改数据引擎
  alter table tb_name engine = engine_name
• 修改数据表名称
  alter table tb_name rename to new_tb_name

2.8 数据库联查

• inner join cross join join 三者是等价的
  会将两个表连接起来，产生笛卡尔积，可以使用where或者on来限制生成的连接表
  select t1.*,t2.* from t1 innor join t2 where t1.i1 = t2.i2
select t1.*,t2.* from t1 innor join t2 on t1.i1 = t2.i2
  on 是限制临时表的大小
  where 是在生成临时表后过滤（貌似用on性能会好点？）
• left join 与 right join
  左联查会筛选出左表数据，不管右表数据是否存在
  右联查与左联查相反

2.9 子查询

• exist in

2.13 事务

• 事务的提交和回滚机制确保尚未全部完成的操作。
• 事务 mysql在执行每一条sql语句时都会自动地对该语句所涉及的资源进行锁定以避免个语句之间相互干扰

3 数据类型

3.2 数值类型

• 数值类型
  精确值类型：包含整数类型及decimal。decimal类型保存的精确值可以有一个小数部分，这种数据类型非常适合用来保存财务金额数据。不存在四舍五入
  浮点数类型：分为单精度（float）及双精度（double）。可以带小数部分，但容纳的是可能发生四舍五入的近似值。适合对精度不高的数据。如粮食平均亩产，空间距离，失业率
  bit类型：保存位字段值
• 数据类型
  TINYINT 非常小的整数
  SMALLINT 小整数
  MEDIUMINT 中等大小的整数
  INT 标准的整数
  BIGINT 大整数
  DECIMAL 定点数
  FLOAT 单精度浮点数
  DOUBLE 双精度浮点数

3.4 AUTO_INCREMENT 属性

• 每个数据表只能有一个AUTO_INCREMENT属性，且为整形，浮点型很少有
• 该列必须添加索引
• 必须非空 not null
• 最近生成的序号值可以通过LAST_INSERT_ID()函数获得
• 插入一个序号值大于当前计数器的数值会让计数器跳过一个区间
• InnoDb引擎5.0.3之后创建表时可以设置AUTO_INCREMENT的初始值
• 不支持非正数，所以可以将AUTO_INCREMENT数据列定义为UNSIGNED类型，好处是有两倍序列编号可用
• TRUNCATE TABLE可将计数序列重新从1开始计数
• 如果想重新编排序列编号，先删除该数据列，再添加上，数据库会自动添加编号

3.6 数据类型的选用

• 确定数据类型时多问一句：会不会有例外？可以避免后期修改数据库字段属性。
• 以数值类型为例，取值范围越大，需要的存储空间就越大。

5 查询优化

5.1 使用索引

• 索引的用途
  一是在查询操作中把与where子句中所给出的条件相匹配的数据行尽快找出来，二在关联操作中可以快速找出其他表中的匹配数据
  对于使用MIN()或MAX()函数的查询，带索引的字段，可以迅速找到不用逐行扫描。
  mysql经常使用索引完成ORDER By或GROUP By分类及分组
• 索引的缺点
  索引会降低数据的插入速度，因为在插入数据时会要求所有索引都要做出改变。
  索引会占据磁盘空间，多个索引会占用更大的空间
  InnoDB数据表的共享表空间不受操作系统文件尺寸的限制
  如果不需要某个特定索引来加快查询速度，就不要创建它
• 调训索引
  尽量为用来搜索，分类或分组的数据列编制索引。
  数据列的维度越高越适合创建索引。当查询优化器程序确定出某一个数值在数据表中出现频率超过30%，会跳过索引，进行全表扫描，如今更为复杂。
  尽量选择短小的值进行索引
  为字符串的前缀编写索引
  充分利用最坐边地区前缀（复合索引）
  散列索引：会有一个散列函数依次处理每一个数据列值。使用 = 或者 <=> 速度很快。但范围比较操作表现不佳
  BTREE索引：比较通用，还可用与LIKE匹配
• force index、use index、ignore index
• 索引列如果进行了算术运算，将不会使用索引
• 不要在Like语句开始使用匹配符,不会使用索引。可以这样使用 like 'str%'，会使用索引
• 优化器对联查的优化效果比子查询的优化效果会好一些
• 避免过多使用mysql的自动类型转换功能，会阻止索引的使用

5.2 explain的使用

• explain sql语句
• analyse tableName
• show index from tableName
• show status where Variable_name = 'Connections';
• show processlist

5.3 提高效率

• 尽量使用数值操作，少使用字符串操作
• 如果小类型够用，就不要选用大类型
• InnoDB使用VARCHAR MyISAM使用CHAR
• 考虑使用ENUM类型
• select hospital_id from hospital_storage_size procedure analyse() mysql可以给出建议
• optimize table语句可以清理MyISAM数据表碎片，各种存储引擎使用的碎片整理办法是：先转储数据表，再利用转储文件删除并重建一个（但不建议这样做，风险太大）
• 人造索引，根据数据表中的其他数据列计算出一个散列值并保存在一个数据里中。（散列值在<或>=等操作进行范围搜索中毫无用处）
• mysqldump --extended-insert 生成含有多个数据行的insert语句 --complete-insert 产生多个单行insert语句。单行多数据insert比多行insert效率高

5.6 调度和锁定问题

• 写入比读取有更高的优先权
• 对数据库的写操作必须按照“写”请求先来后到的顺序一个接一个地进行
• 对同一个数据表进行的读操作可以同时进行

5.7 系统管理员所完成的优化

• 在内存中访问数据比从磁盘上访问数据快
• 在内存中尽可能长时间保存数据可以减少磁盘活动量
• 保留索引的信息要比保留数据行的内容更重要
• opened-tables 变量控制数据表缓存中打开文件的信息 show status like 'Opened_tables'
• InnoDB存储引擎有自己用于缓冲数据和索引值的缓存，大小由innodb_buffer_pool_size控制。日志缓冲大小由innodb_log_buffer_size控制

5.8 查询缓存

• show variables like 'have_query_cache' 查看是否支持缓存
• query_cache_type 查询缓存的模式 0：不缓存，不检索 1：缓存查询，不包括以select sql_no_cache开头查询 2：只缓存以select sql_cache开头的查询
• query_cache_size 决定了查询缓存分配的大小，以字节为单位
• query_cache_size:是查询缓存数据 innodb_buffer_pool_size存原始数据和索引。

---