MYSQL点滴记录

最新推荐文章于 2024-10-16 15:26:08 发布

来过我生命的你

最新推荐文章于 2024-10-16 15:26:08 发布

阅读量222

点赞数

分类专栏： myql 文章标签： mysql

本文链接：https://blog.csdn.net/york_2016/article/details/80415594

版权

myql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

MYSQL索引相关

MySQL索引的类型

1. 普通索引
2. 唯一索引
3. 全文索引
4. 单列索引、组合索引

全文索引（FULLTEXT）

MySQL从3.23.23版开始支持全文索引和全文检索，FULLTEXT索引仅可用于 MyISAM 表；他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE 或CREATE INDEX被添加。对于较大的数据集，将你的资料输入一个没有FULLTEXT索引的表中，然后创建索引，其速度比把资料输入现有FULLTEXT索引的速度更为快。不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。

4. 单列索引、组合索引

多个单列索引与单个多列索引的查询效果不同，因为执行查询时，MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。

组合索引有最左前缀特性。
例如对article表中针对title和time建立一个组合索引：

ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10))。
建立这样的组合索引，其实是相当于分别建立了下面两组组合索引：

–-title,time

–-title

–使用到上面的索引
      SELECT * FROM article WHREE title='测试' AND time=1234567890;
      SELECT * FROM article WHREE utitle='测试';
–不使用上面的索引
      SELECT * FROM article WHREE time=1234567890;

创建索引的条件

1、较频繁的作为查询条件的字段应该创建索引
2、唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件，很多比较有经验的Query调优专家经常说，当一条Query返回的数据超过了全表的15％时，就不应该再使用索引扫描来完成这个Query了。对于“15％”这个数字我们并不能判定是否很准确，但是至少侧面证明了唯一性太差的字段并不适合创建索引。
3、更新非常频繁的字段不适合创建索引
4、不会出现在WHERE子句中的字段不该创建索引
5、在一般的应用场景中，只要不是其中某个过滤字段在大多数场景下能过滤90％以上的数据，而其他的过滤字段会频繁的更新，一般更倾向于创建组合索引，尤其是在并发量较高的场景下。因为当并发量较高的时候，即使只为每个Query节省了很少的IO消耗，但因为执行量非常大，所节省的资源总量仍然是非常可观的。

MYSQL优化查询注意事项

明确优化目标；优化更需要优化的Query；定位优化对象的性能瓶颈；

① 为查询缓存优化查询例如使用redis缓存
② EXPLAIN 我们的SELECT查询，多使用Profile；
③ 当只要一行数据时使用LIMIT 1
④ 为搜索字段建立索引，仅仅使用最有效的过滤条件，并尽可能在索引中完成排序；
⑤ 尽可能避免复杂的Join和子查询，在Join表的时候使用相当类型的列，并将其索引，永远用小结果集驱动大的结果集；
⑥ 千万不要 ORDER BY RAND ()
⑦ 避免SELECT *，只取自己需要的列；
⑧ 永远为每张表设置一个ID
⑨可以使用ENUM 而不要VARCHAR
⑩ 尽可能的使用NOT NULL
⑪ 固定长度的表会更快
⑫ 垂直分割
⑬ 拆分DELETE或INSERT语句
⑭ 越小的列会越快
⑮ 选择正确的存储引擎
⑯ 小心 "永久链接"，小心隐式转换
⑰ 字符集设置utf8_mb4，否则emoji乱码
⑱ 避免偏移量大的limit

索引合并问题：

1、(x AND y) OR z => (x OR z) AND (y OR z)
2、(x OR y) AND z => (x AND z) OR (y AND z)

1、嵌套循环算法 5.5之前JOIN采用 Nested Loop Join ，之后 Block Nested-Loop Join
2、利用索引进行排序操作，主要是利用了索引的有序性。在通过索引进行检索的过程中，就已经得到了有序的数据访问顺序，依次读取结果数据后就不须要进行排序操作，进而避免了此操作，提高了需要有序结果集的Query的性能。
3、在数据库中表的某个字段创建索引，所带来的最大益处就是将该字段作为检索条件时可以极大地提高检索效率，加快检索时间，降低检索过程中须要读取的数据量。索引还有一个非常重要的用途，那就是降低数据的排序成本。

在MySQL中，ORDER BY的实现有如下两种类型：
●　一种是通过有序索引直接取得有序的数据，这样不用进行任何排序操作即可得到满足客户端要求的有序数据并返回给客户端；
●　另外一种则须通过MySQL的排序算法将存储引擎中返回的数据进行排序后，再将排序后的数据返回给客户端。

（1）取出满足过滤条件作为排序条件的字段，以及可以直接定位到行数据的行指针信息，在Sort Buffer中进行实际的排序操作，然后利用排好序的数据根据行指针信息返回表中取得客户端请求的其他字段的数据，再返回给客户端；（2）根据过滤条件一次取出排序字段及客户端请求的所有其他字段的数据，并将不须要排序的字段存放在一块内存区域中，然后在Sort Buffer中将排序字段和行指针信息进行排序，最后再利用排序后的行指针与存放在内存区域中和其他字段一起的行指针信息进行匹配、合并结果集，再按照顺序返回给客户端。
（2）根据过滤条件一次取出排序字段及客户端请求的所有其他字段的数据，并将不须要排序的字段存放在一块内存区域中，然后在Sort Buffer中将排序字段和行指针信息进行排序，最后再利用排序后的行指针与存放在内存区域中和其他字段一起的行指针信息进行匹配、合并结果集，再按照顺序返回给客户端。

1.聚类索引只需要找到要检索的所有数据中的开头和结尾数据即可；而不像非聚集索引，必须先查到目录中查到每一项数据对应的页码，然后再根据页码查到具体内容。
2. 索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
3. 使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
4. 索引列排序
MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
5. like语句操作
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
6. 不要在列上进行运算
例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate<’2007-01-01′。关于这一点可以围观：一个单引号引发的MYSQL性能损失。
最后总结一下，MySQL只对一下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形)。而理论上每张表里面最多可创建16个索引，不过除非是数据量真的很多，否则过多的使用索引也不是那么好玩的，比如我刚才针对text类型的字段创建索引的时候，系统差点就卡死了。

MYSQL数据库执行顺序

FORM: 对FROM的左边的表和右边的表计算笛卡尔积。产生虚表VT1
ON: 对虚表VT1进行ON筛选，只有那些符合<join-condition>的行才会被记录在虚表VT2中。
JOIN：如果指定了OUTER JOIN（比如left join、 right join），那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中，产生虚拟表VT3, rug from子句中包含两个以上的表的话，那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤，一直到处理完所有的表为止。
WHERE：对虚拟表VT3进行WHERE条件过滤。只有符合<where-condition>的记录才会被插入到虚拟表VT4中。
GROUP BY: 根据group by子句中的列，对VT4中的记录进行分组操作，产生VT5.
CUBE | ROLLUP: 对表VT5进行cube或者rollup操作，产生表VT6.
HAVING：对虚拟表VT6应用having过滤，只有符合<having-condition>的记录才会被插入到虚拟表VT7中。
SELECT：执行select操作，选择指定的列，插入到虚拟表VT8中。
DISTINCT：对VT8中的记录进行去重。产生虚拟表VT9.
ORDER BY: 将虚拟表VT9中的记录按照<order_by_list>进行排序操作，产生虚拟表VT10.
LIMIT：取出指定行的记录，产生虚拟表VT11, 并将结果返回。

数据库隔离级别

REPEATABLE READ 可重复读

MySQL数据库默认的隔离级别。该级别解决了READ UNCOMMITTED隔离级别导致的问题。它保证同一事务的多个实例在并发读取事务时，会“看到同样的”数据行。不过，这会导致另外一个棘手问题“幻读”。InnoDB和Falcon存储引擎通过多版本并发控制机制解决了幻读问题。

READ COMMITTED 读取提交内容

大多数数据库系统的默认隔离级别（但是不是MySQL的默认隔离级别），满足了隔离的早先简单定义：一个事务开始时，只能“看见”已经提交事务所做的改变，一个事务从开始到提交前，所做的任何数据改变都是不可见的，除非已经提交。这种隔离级别也支持所谓的“不可重复读”。这意味着用户运行同一个语句两次，看到的结果是不同的。

READ UNCOMMITTED 读取未提交内容

在这个隔离级别，所有事务都可以“看到”未提交事务的执行结果。在这种级别上，可能会产生很多问题，除非用户真的知道自己在做什么，并有很好的理由选择这样做。本隔离级别很少用于实际应用，因为它的性能也不必其他性能好多少，而别的级别还有其他更多的优点。读取未提交数据，也被称为“脏读”

SERIALIZABLE 可串行化

该级别是最高级别的隔离级。它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简而言之，SERIALIZABLE是在每个读的数据行上加锁。在这个级别，可能导致大量的超时Timeout和锁竞争Lock Contention现象，实际应用中很少使用到这个级别，但如果用户的应用为了数据的稳定性，需要强制减少并发的话，也可以选择这种隔离级别

设置隔离级别
transaction-isolation = REPEATABLE-READ

查看隔离级别

查询当前会话隔离级别

select @@tx_isolation;

设置当前会话隔离级别

set session transaction isolation level read uncommitted;

set session transaction isolation level read committed;

set session transaction isolation level repeatable read;

set session transaction isolation level serializable;

MYSQL锁

MYSQL乐观锁：

版本号机制：一般是在数据表中加上一个数据版本号version字段，表示数据被修改的次数，当数据被修改时，version值会加一。当线程A要更新数据值时，在读取数据的同时也会读取version值，在提交更新时，若刚才读取到的version值为当前数据库中的version值相等时才更新，否则重试更新操作，直到更新成功。

核心代码：

update table set x=x+1, version=version+1 where id=? and version=?;

MYSQL悲观锁：

必须关闭mysql数据库的自动提交属性

set autocommit=0;
start transaction;
select * from table where id = ? for update;
.......
commit;

一些有用的SQL：


1.查询进程  

     SHOW PROCESSLIST; KILL ID ;

2：查看当前的事务

     SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

3：查看当前锁定的事务

     SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

4：查看当前等锁的事务

     SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

5：explain执行计划 
    
     explain + SQL

6： 查询最大连接数，缓存
     
     show variables like 'max_connections'; 
     show variables like 'query_cache'; 
     show status like "Qcache%"

7：MySQL表碎片整理
 
     OPTIMIZE TABLE table.name; 

8：强制使用或忽略索引(尽量不要使用)

     SELECT * FROM TABLE1 FORCE INDEX (FIELD1) WHERE ...;
     SELECT * FROM TABLE1 IGNORE INDEX (FIELD1, FIELD2) WHERE ...;