mysql闲谈

mysql中可能产生死锁例如：下面两个事务同时处理StockPrice表
事务一：

START TRANSCATION
updata StockPrice set close = 45.50 where stock_id = 4 and data = '2010-05-01';
updata StockPrice set close = 19.80 where stock_id = 3 and data = ''2010-05-02';
COMMIT

事务二：

START TRANSCATION
updata StockPrice set close = 20.12 where stock_id = 3 and data = '2010-05-02';
updata StockPrice set close = 47.20 where stock_id = 4 and data = ''2010-05-01';
COMMIT   

如果两个事务同时执行了第一行数据，则，同时加锁了该行的数据，接着每个事物都尝试更新第二条数据，却发现即将要更新的数据已经被对方锁定，则陷入死循环，除非有外部的因素介入打破这种死循环，才能解除死锁
自动提交事务的方法：

mysql >show variables like+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.01 sec)
mysql > set autocommit = 1;

当autocommit =0 时候，所以的查询都是在一个事务中，是一个自动提交模式
3.显示表的相关信息

show table status like 'user' \G;
           Name: user
         Engine: InnoDB
        Version: 8
     Row_format: Compact
           Rows: 112
 Avg_row_length: 2
    Data_length: 348
Max_data_length: 0
   Index_length: 160
      Data_free: 944
 Auto_increment: NULL
    Create_time: 2019-06-26 16:45:45
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.01 sec)

4.修改 表的引擎
将mytable的引擎修改为innodb

mysql > ALTER TABLE mytable ENGINE = InnoDB

上述的语法适用任何存储引擎，不过需要的时间长，这是因为mysql会按行将数据从原表复制到一张新表中，在复制期间可能会很消耗所有的IO能力。同时原有在这里插入代码片的表上会加上读锁。
5.创建于查询

mysql >create table innodb_table like myisam_table;
mysql >alter table innodb_table engin = innodb;
mysql >insert into innodb_table select * from myisam_table;

//如果数据量很大的时候可以分批处理

mysql >iSTART TRANSACTION
mysql >insert into innodb_table select * from myisam_table where id between x and y;
mysql >commit        

mysql的show status 命令返回了一些计数器
6. 反范式的优缺点
一个网站，允许用户发送消息，并且一些用户是付费用户的，现在是想看付费用户最近的10条信息，如果是范式化的结构 并且索引发送日期字段published,这个查询看起来如下：

mysql>select message_text,user_name from message inner join user on message.user_id = user.id where user.accout_type = 'premiuvm' order by message.published desc limit 10; 

说明：mysql需要扫描message表的published字段的索引，对于每一行的找到的数据，将需要到user表里检查这个用户是不是付费用户，如果只有一小部分是用户是付费的，这个效率是非常低的。
如果使用反范式组织数据，将两张 表的字段合并一下，并且增加一个索引(account_type,published),就可以不通过这个关联写出这个查询了

mysql> select message_text ,user_name from user_messages where account_type = 'premiuvm' order by published desc limit 10;

9.计算机表
该表主要用来记录点赞数，假设有一个计数器表，只有一行数据，记录网站的点击次数

mysql> create table hit_counter (
		cnt int unsigned not null) engine  = innodb;

网站每次点击的时候都会导致对计数器进行更新

mysql > update hit_counter ( slot tinyint unsigned not null primary key, cnt int unsigned not null) engine  = innodb;

然后选择其中的一行进行更新

mysql > update hit_counter set cnt = cnt + 1 where slot = rand()*100; 

10.手动维护一个哈希值，也可以使用一个触发器实现
创建表如下：

create table pseeduhash
(
	id int unsigned not null auto_increment,
	url varchar(255)not null,
	url_crc int unsigned not null default 0,
	primary key (id)
);

//创建触发器。先临时修改一个语句分隔符，然后这样就可以在触发器定义中使用分号

create trigger pseeduhash_crc_upd before update on pseeduhash for each row begin set new.url_crc = crc32(new.url);

验证一下触发器如何维护哈希索引；

insert into pseeduhash (url) values ('http://www.mysql.com');
select * from pseeduhash ;

11.需要找到最常见的城市列表

select count(*) as cnt,city from sakila.city_demo group by city order by cnt desc limit 10;
//按照city来分组，按照cnt来排序，以降序的方式
现在查找到最频繁出现的城市前缀，先从3个前缀字母开始
select count(*) as cnt,left(city,3) as pref from sakia.city_demo group by pref order by cnt desc limit 10;
//每个前缀都比原来的城市出现的次数更多，因此唯一的前缀比唯一的城市要少的多，然后我们增加前缀长度，
//直到这个前缀的选择性接近完整列的选择性，经过试验后发现前缀长度为7的比较适合
select count(*) as cnt ,left(city,7) as pref from sakia.city_demo group by pref order by cnt desc limit 10;
//创建一个前缀索引
alter table sakia.city_demo and key (city(7));