mysql数据库学习（三）

 

CREATE TABLE `test_innodb_lock` (
  `ID` int(11) DEFAULT NULL,
  `NAME` varchar(16) DEFAULT NULL,
  `SEX` varchar(1) DEFAULT NULL,
  KEY `IDX_TEST_INNODB_LOCK_ID` (`ID`),
  KEY `IDX_TEST_INNODB_LOCK_NAME` (`NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


INSERT INTO TEST_INNODB_LOCK VALUES(4,'400','0');
INSERT INTO TEST_INNODB_LOCK VALUES(5,'500','1');
INSERT INTO TEST_INNODB_LOCK VALUES(6,'600','0');
INSERT INTO TEST_INNODB_LOCK VALUES(7,'700','0');
INSERT INTO TEST_INNODB_LOCK VALUES(8,'800','1');
INSERT INTO TEST_INNODB_LOCK VALUES(9,'900','1');
INSERT INTO TEST_INNODB_LOCK VALUES(1,'200','0');
CREATE INDEX IDX_TEST_INNODB_LOCK_ID ON TEST_INNODB_LOCK(ID);
CREATE INDEX IDX_TEST_INNODB_LOCK_NAME ON TEST_INNODB_LOCK(NAME);

 

一、应用优化

　　1.1、使用连接池

　　1.2、减少对MySQL的访问

　　　　1.2.1、避免对数据（同一张表）进行重复检索

　　　　1.2.2、增加cache(使用Hibernate/Mybatis提供的一级缓存、二级缓存)，使用redis等

　　1.3、负载均衡

　　　　

二、MySQL查询缓存优化(完全相同的SQL语句)

　　2.1、操作流程

　　2.2、查询缓存配置

 　　　   >show   variables like 'have_query_cache';  　　#查看缓存配置

　　　　>show   variables like 'query_cache_type';  　　  #查看当前MySQL是否开启了缓存

　　　　>show   variables like 'query_cache_size';  　　  #查看缓存占用大小Byte

　　　　>show   status     like 'Qcache%';  　　  　　　　#查看查询缓存状态变量

 

　　2.3、开启查询缓存query_cache_type（在/usr/my.cnf配置中，增加参数配置query_cache_type=1）

 　　2.4、查询缓存SELECT选项

SELECT SQL_CACHE [table_cols...] FROM table;

SELECT SQL_NO_CACHE [table_cols...] FROM table;

  　　2.5、查询缓存失效

　　　　2.5.1、SQL语句不一致，要想命中缓存，SQL语句必须完全一样，包括大小写

　　　　2.5.2、当查询语句中有一些不确定的值时，

　　　　　　>now()/current_date()/curdate()/rand()/uuid()/user()/database()

　　　　2.5.3、不使用任何表查询语句（SELECT 'A';）

　　　　2.5.4、查询mysql\information_schema\performance_schema数据库中的表时，不走缓存

　　　　　　>select * from information_schema.engines;

　　　　2.5.5、在存储的函数，触发器或事件的主体内执行的查询

　　　　2.5.6、表更改，将删除缓存中关于该表的所有缓存，包括MERGE映射到已更改表的查询。

　　　　　　>INSERT/UPDATE/DELETE/TRUNCATE TABLE/ALTER ATBLE/DROP TABLE/DROP DATABASE

三、MySQL内存管理及优化

　　3.1、内存优化原则

 　　3.2、MyISAM内存优化

　　myisam存储引擎使用key_buffer缓存索引块，加速myisam索引的读写速度，对于表的数据块，完全依赖于操作系统的IO缓存

　　　　3.2.1、在my.cnf中添加key_buffer_size=50M

 　　　　3.2.2、read_buffer_size

　　　　3.2.3、read_rnd_buffer_size

 　　3.3、InnoDB内存优化（my.cnf）

　　　　　  InnoDB用一块内存区做IO缓存池，该缓存池不仅用来缓存innodb的索引块，而且也用来缓存数据块

　　　　3.3.1、innodb_buffer_pool_size=512M

　　　　3.3.2、innodb_log_buffer_size=10M

四、MySQL并发参数调整

　　4.1、max_connections

　　4.2、back_log

　　4.3、table_open_cache

 　　4.4、thread_cache_size

　　4,5、innodb_lock_wait_timeout

 

 五、MySQL锁问题

　　5.1、锁概述：锁是计算机协调多个进程或线程并发访问某一资源的机制，如何解决并发问题一致性、有效性？

　　5.2、锁分类

　　　　5.2.1、对数据操作的粒度分：表锁　　行锁

　　　　5.2.2、对数据操作的类型分：读锁　　写锁

　　5.3、MySQL锁

 

　　5.4、MyISAM锁

　　　　5.4.1、MyISAM锁的读锁，不会阻塞其他线程的读锁，但是会阻塞其他线程的写锁

　　　　5.4.2、MyISAM锁的写锁，会阻塞其他线程的读锁，也会阻塞其他线程的写锁

 

　　　　5.4.3、查看锁的争用情况

　　　　　　>show open tables;　　　　　　　　　　　　#锁定后，in_use会出现变化

　　　　　　>show status like 'table_lock%';　　   #查看　

　　5.5、InnoDB行锁

　　　　5.5.1、行锁介绍

　　　　　　特点：偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高

　　　　　　　　　与MyISAM最大的不同：一是支持事务，二是采用了行级锁

　　　　5.5.2、事务及其ACID属性（事务是由一组SQL语句组成的逻辑处理单元）

　　　　>Mysql隔离级别Repeatable read

　　　　>show variables like 'tx_isolation';

 　　　　5.5.3、InnoDB的行锁模式，默认就是行锁

　　　　　　　  可以通过下面语句显示的加锁

　　　　　　　　共享锁(S)>SELECT *  FROM table_name WHERE ......　　LOCK IN SHARE MODE;

　　　　　　　　排他锁(S)>SELECT *  FROM table_name WHERE ......　　FOR UPDATE ;

  

 //#########################################演示###################################################

 

 //#########################################演示###################################################

 　　　　5.5.4、无索引行锁升级为表锁

　　　　　　　  如果不通过索引查询数据，那么InnoDB将对表中的所有记录加锁，相当与表锁

　　　　　　　  >show index from table_name;

　　　　5.5.5、间隙锁危害

 

　　　　5.5.6、行锁争用情况

　　　　　　　  >show status like 'innodb_row_lock%';

　　　　5.5.7、总结以及优化建议

 

六、常用SQL技巧

　　6.1、SQL语句的编写顺序和执行顺序（图片来源https://blog.csdn.net/qq_44785877/article/details/107828184）

　　6.2、正则表达式的使用>SELECT * FROM TABLE_NAME WHERE NAME REGEXP 'S$';

 

 　　6.3、常用类型函数

数字函数	作用	数字函数	作用
ABS	求绝对值	ASIN	求反正弦值
AQRT	求二次方根	COS	求余正弦值
MOD	求余数	ACOS	求反余正弦值
CEIL和CEILING	两个函数功能相同，都是返回不小于参数的最小整数，即向上取整	TAN	求正切值
FLOOR	向下取整，返回值转化为一个BIGINT	ATAN	求反正切值
RAND	生成一个0~1之间的随机数，传入整数参数时，用来产生重复序列	COT	求余切值
ROUND	对所传参数进行四舍五入	SIN	求正弦值
SIGN	返回参数的符号
POW/POWER	两个函数的功能相同，都是（m,n）所传参数m的n次方的结果值

 

 

 

 

 

 

 

 

 

 

 

 

字符串函数	作用	字符串函数	作用
LENGTH	计算长度	TRIM	删除字符串左右两侧的空格
CONCAT	合并字符串函数，参数可以有多个	REPLACE	替换函数
INSERT	替换字符串函数	SUBSTRING	截取字符串
LOWER		REVERSE	字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串
UPPER
LEFT	从左侧截取字符串	RIGHT

 

 

 

 

 

 

 

 

日期函数	作用	日期函数	作用
CUPDATE和CURRENT_DATE	返回当前系统的日期值	TIME_TO_SEC	将时间参数转化为秒
CURTIME和CURRENT_TIME	返回当前系统的时间值	SEC_TO_TIME	将秒参数转化为时间，
NOW和SYSDATE	返回当前系统的日期和时间值	DATE_ADD和ADDDATE	向日期添加指定的时间间隔
MONTH	获取指定日期中的月份	DATE_SUB和SUBDATE	向日期减去指定的时间间隔
MONTHNAME	获取指定日期中的月份英文名称	ADDTIME	在原始时间上添加指定的时间
DAYNAME	获取指定日期对应的星期几的英文名称	SUBTIME	在原始时间上减去指定的时间
DAYOFWEEK	获取指定日期对应的一周的索引位置值	DATEDIFF	获取两个日期之间的间隔，返回参数1减去参数2的值
WEEK	获取指定日期是一年中的第几周，返回值的范围是否为0~52或1~53	DATE_FORMAT	格式化指定的日期，根据参数返回指定格式的值
DAYOFYEAR	获取指定日期是一年中的第几天，返回值范围是1~366	WEEKDAY	获取指定日期在一周内的对应的工作日索引
DAYOFMONTH	获取指定日期是一个月中的第几天，返回值范围是1~31
YEAR	获取年份，返回值是1970~2069

 

 

 

 

 

 

 

 

 

 

 

 

 

 

聚合函数	作用
MAX
MIN
COUNT
SUM
AVG