MYSQL操作

最新推荐文章于 2024-11-07 15:10:30 发布

weixin_34341117

最新推荐文章于 2024-11-07 15:10:30 发布

阅读量65

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/YDDMAX/p/6217829.html

版权

本文探讨了MySQL中影响性能的关键因素，包括唯一性约束处理、大事务处理、游标使用、truncate与delete的区别、ALTER TABLE语句用法及查询优化等方面，并通过具体案例展示了更新和加锁机制的工作原理。

摘要由CSDN通过智能技术生成

３、表的列包含unique属性

　　mysql会对unique列建索引，而且unique列不能太大，建表的时候就有要求。

　　在对数据更新操作（insert、update）时，执行消耗主要包含两方面：一方面会在unique索引上查找本次更新的数据是否unique；另一方面多个请求同时更新，为了保证unique，可能会存在加锁。

　　详见《MySql的unique实现原理简析》

４、大事务

　　　因为一次事务里可能会包含加锁操作，所以如果一个事务过大，那么事务所拥有锁的时间就很长，那么就会造成别的事务等待锁超时，此时会严重降低数据库的吞吐率。比如上面的３里面的unique属性，如果一次更新太多数据，而这些更新又需要采取一定的

　　　同步保证unique，那么如果不同的事务存在更新后的数据不是unique的，那么有的事务就会等待锁（等待冲突的那个事务提交），如果此时事务很大，就会引起锁超时。

５、MYSQL的游标采取临时表的形式，在open cs的时候会读取所有的游标选取的数据到临时表里面。一般临时表为内存临时表，如果存在下面两种情况则不得不使用磁盘临时表：

　　１、如果表的大小超过tmp_table_size的时候会在磁盘上创建临时表。

２、游标的内存临时表不支持text或者blob数据的存储，如果游标选取的列含有text或者blob的时候，mysql会在磁盘上存储这些数据。

６、

create table encrypt_mapping(
id int auto_increment primary key,
original_value varchar(200),
encr_value varchar(200),
type int,
unique key or_type_uniIndex (original_value,type)

);

事务１：insert ignore into encrypt_mapping select XXX from XXX;
事务２：insert ignore into encrypt_mapping select XXX from XXX;

此时因为encrypt_mapping里面的unique key,如果事务１先执行，事务２再执行因为两个大事务之间存在unique冲突的情况会出现锁超时的现象。

7、truncate　table 比delete　快很多，因为delete需要根据where条件查找到数据再删除，delete的执行过程需要走索引和加锁等操作。而truncate只是删除整个表，所以会快很多。

8、ALTER TABLE 的语法

    删除列：ALTER TABLE XXX DROP COLUMN   XXX;

    增加列：ALTER TABLE ADD COLUMN  XXX;

　  修改列：ALTER TABLE MODIFY COLUMN XXX;

９、一个查询分析

　　test表结构

mysql> show create table test;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                           |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `inde` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1

test表数据

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  7 | a    |
|  8 | ab   |
|  9 | abc  |
| 10 | abcd |
+----+------+
4 rows in set (0.00 sec)

　　dim表结构

mysql> show create table dim;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| dim   | CREATE TABLE `dim` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `n` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `n_index` (`n`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 |

　　dim表数据

mysql> select * from dim;
+----+------+
| id | n    |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
|  5 |    5 |
+----+------+
5 rows in set (0.00 sec)

　　expalin

mysql> explain select * from test inner join dim on dim.n=LENGTH(name);
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | test  | NULL       | index | NULL          | inde    | 103     | NULL |    4 |   100.00 | Using index              |
|  1 | SIMPLE      | dim   | NULL       | ref   | n_index       | n_index | 5       | func |    1 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+

　　　分析：虽然dim.n=LENGTH(name)，该条件后面跟的是个表达式，但是　　是走索引的。

10、身份证的脱敏在建索引和未建索引时，性能是3倍的差别，手机的脱敏也很大。虽然是3倍，但是如果时间基数很大，这个差值讲会很大，所以不要忽视性能调优。

11、更新加锁分析

stu表：

 CREATE TABLE `stu` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `address` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=latin1

数据：

select * from stu;
+----+-------+----------+
| id | name  | address  |
+----+-------+----------+
|  1 | name1 | address1 |
|  2 | name2 | address2 |
+----+-------+----------+
2 rows in set (0.00 sec)

dim表：

CREATE TABLE `dim` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1

数据：

select * from dim;
+----+--------+
| id | value  |
+----+--------+
|  1 | value1 |
|  2 | value2 |
|  3 | value3 |
+----+--------+
3 rows in set (0.00 sec)

TestCase1：

事务1：　　　　　　　　　　　　　　　　事务2：

start transaction; start transaction;

update name='nameUpdate'

where id in (select id from dim);

更新两行

select id from dim;

查询出1,2,3

　　　　　　　　　　　　　　　　　　　　　　update dim set value='mm' where id=2;

　　　　　　　　　　　　　　　　　　　　　　ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

　　　　　　　　　　　　　　　　　　　　　　update dim set value='mm' where id=3;　

本行没有锁住

insert into dim(value) values('vv');

　　　　　　　　　现在的数据为：1,2,3,4

事务1没有加间隙锁

insert into stu(id,name,address)

values(4,'name','address');

update name='mn'

where id in (select id from dim);

更新3行（说明读出的数据是当前读）

commit; 　　　　　　　　　　　commit;

说明：1、更新语句里面的where包含select时执行的是当前读（虽然没有显示的加锁），也就是更新语句执行的是当前读更新。

2、update和delete时会锁住要更新的行，也会锁住where条件里的非更新表里面的行（只会锁住相对应的行，不是全部的符合条件的行），经过测试验证加的锁是读锁。但是where条件里的非更新表里的行不会加间隙锁。

TestCase2:

事务1：　　　　　　　　　　　　　　　　事务2：

start transaction; start transaction;

insert into stu(name,address)

select value,value from dim;

　　　　　　　　　　　　　　　　　　　　　　　update dim set id=101 where id=4;
　　　　　　　　　　　　　　　　　　　　　　　ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

　　　　　　　　　　　　　　　　　　事务1加锁

　　　　　　　　　　　　　　　　　　　　　　　insert into dim(value) values('hh');

　　　　　　　　　　　　　　　　　　　　　　 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

　　　　　　　　　　　　　　　　　　　　　　　事务1加了间隙锁

　　　　　　　　　　　　　　　　　　　　　　　select * from dim lock in share mode;

事务1加的只是读锁　

commit; 　　　　　　　　　　 commit;

说明：insert时会锁住所有需要读取的行，加的是读锁，而且会加间隙锁

TestCase3:

事务1：　　　　　　　　　　　　　　　　事务2：

start transaction; start transaction;

select * from stu;

查出id为1,2数据

　　　　　　　　　　　　　　　　　　　　　　　insert into stu(name,address) values('name1','address1');

select * from stu lock in share mode;

查出数据为1,2,3的数据

select * from stu;

查出数据为1,2的数据

commit; 　　　　　　　　　　 commit;

说明：加锁读为显示读，不加锁的普通读永远返回的是快照的结果

12、（接11，继续更新加锁分析）下面的测试案例表明mysql的更新操作(update和delete会加间隙锁)

　　所以当数据库的更新操作很频繁时，间隙所很影响性能，如果业务允许的话，建议调整数据库的事物级别，或者关掉间隙锁。

1 start transaction;                            start transaction;
2 
3 delete from  test;                                   
4 
5                                                insert into test values(2'namw2');（锁超时，因为左边的delete加了间隙所）
6 commit;                                        commit;

　　分析：数据库的读分为当前读和快照读，数据库的更新操作（delete、update、insert）都是当前读，在执行过程中都会加锁。

转载于:https://www.cnblogs.com/YDDMAX/p/6217829.html