MySQL知识积累

一、MySQL中myisam与innodb的区别

MyISAM和InnoDB都是MySQL的存储引擎，服务器通过API和底层存储引擎进行通信，存储引擎API封装了很多的底层函数，使得不对具体的存储引擎相互区分，目前用的最多的就是MyISAM和InnoDB。但两者也有很多不同。

  1. MyISAM不支持事务，InnoDB支持事务。InnoDB是默认的事务性引擎；这也注定了对于写操作比较多，或者表比较大的情况，最好不用MyISAM。
  2. MyISAM支持表锁，InnoDB支持行锁。这也注定了在并发性能上，MyISAM的表锁的特性是最大的瓶颈；
  3. MyISAM不支持外键，InnoDB支持外键；
  4. MyISAM支持全文索引，InnoDB不支持全文索引；
  5. InnoDB支持MVCC，MyISAM不支持。

二、mysqldump

mysqldump用来备份数据库还是比较有用的，这里说一下看到的题：

1、要把insert的每一行都单行显示。

只需加上--skip-extened-insert，就可以实现上述功能： 

mysqldump -uroot -p app_dailyblog blog_category --skip-extended-insert >cate.sql

2、从mysqldump中恢复某一个库： 

mysql -uroot -p blog_category --one-database < blog.sql

三、锁

锁可分为行级锁和表锁，InnoDB都支持，那InnoDB是如何实现行级锁的呢？

锁这方面还是比较复杂的，有很多东西要看。我只知道 InnoDB是基于索引来完成行锁例 。

四、MySQL日志类别

错误日志、查询日志、二进制日志、慢查询日志。

  1. 错误日志；记录启动，停止mysqld出现的错误；
  2. 查询日志；记录建立的客户端连接以及所有语句，包括错误的；
  3. 二进制文件，记录所有更改数据的Sql语句，用于主从复制；
  4. 慢查询，记录所有查询时间超过 long_query_time的查询；

五、模糊匹配

  • 如果 REGEXP 模式与被测试值的任何地方匹配，模式就匹配 ( 这不同于 LIKE 模式匹配，只有与整个值匹配，模式才匹配 ) 。

1. 关键字 regexp等于rlike

http://www.sqlines.com/mysql/regexp_rlike MySQL - REGEXP, RLIKE - Guide, Examples and Alternatives

这两条相同：

select * from users where name regexp '傻'

select * from users where name rlike '傻'

2. like 和rlike区别

这两条返回不一样，like匹配整个字串，rlike是匹配部分。

select * from users where name rlike '傻' select * from users where name like '傻'

rlike的返回结果是这样

六、MySQL的drop，delete，truncate

drop>delete>truncate
程度从强到弱
1、drop  table tb
drop将表格直接删除;
2、truncate (table) tb
删除表中的所有数据，不能与where一起使用
3、delete from tb (where)
删除表中的数据(可制定某一行)
区别：truncate和delete的区别
1、事务：truncate是不可以rollback的，但是delete是可以rollback的；
原因：truncate删除整表数据(ddl语句,隐式提交)，delete是一行一行的删除，可以rollback
2、效果：truncate删除后将重新水平线和索引(id从零开始) ,delete不会删除索引
3、 truncate 不能触发任何Delete触发器。
4、delete 删除可以返回行数
七、MySQL的大小写
1、在Linux平台上，MySQL对数据库和表名的大小写是敏感的，而Windows是不敏感的。在Linux下，我们可以对low_er_case_table_names进行修改， 为0时（Linux默认），大小写敏感，创建和查询都是区分大小写；为1时，创建表以小写，查询表也是以小写；为2时，创建表区分大小写，查询表以小写 。
2、列名与列的别名在所有系统中都不区分大小写；
注意MySQL的保留字，比如limit,desc等等，如果有这些，在查询的时候，字段必须添加反引号。
Mysql线程池
Mysql中也有线程池的概念，用来解决高并发的问题。但其和普通的线程池比如Java线程池不太一样，
Mysql线程池中有着线程组的概念。首先看下Mysql线程池的示意图（来源于网络）:
看到上面不难发现，这和普通的由多个线程组成的线程池不同的是，线程池里是由多个线程组 thread group组成。一个group里面包含两个队列还有worker以及listener thread.
两个队列分成高优先级和低优先级队列。一般事务语句都会放到高优先级里处理。
一个线程可以是worker也可以是listener。listener当发现队列中没有任务时，会将自己转换为worker线程，执行语句。如果队列中有任务，则将任务放到队列中，让其他worker线程执行。
这个和经典的Reactor多线程略有区别。那个是一组线程接收请求，一组线程处理请求，比如Netty。
Mysql是listener和worker角色是可以转换的。
上面也提到了优先级队列，通过优先级队列可以避免调度死锁的问题，即优先解决事务语句，以便尽早释放资源。
Mysql的线程配置可以通过如下命令查看一下： 
mysql> show variables like 'thread%';
+-------------------------------+-----------------+
| Variable_name                 | Value           |
+-------------------------------+-----------------+
| thread_cache_size             | 256             |
| thread_handling               | pool-of-threads |
| thread_pool_high_prio_mode    | transactions    |
| thread_pool_high_prio_tickets | 4294967295      |
| thread_pool_idle_timeout      | 60              |
| thread_pool_max_threads       | 100000          |
| thread_pool_oversubscribe     | 20              |
| thread_pool_size              | 40              |
| thread_pool_stall_limit       | 50              |
| thread_stack                  | 196608          |
| thread_statistics             | OFF             |
+-------------------------------+-----------------+

Mysql查询优化
1、尽量不使用嵌套select查询，而使用join.select子查询会导致外层的每一条记录都会进入一次子查询中执行一遍，如果外层表数据很大的话，那性能会很差 。
2、尽量不要查询所有的列，只查询想要的列，尤其是使用了覆盖索引的，这样可能就不会回表查。
3、对于limit 10000,1这种，如果有查询必要，也不要直接查询，可以先缓存Id，然后再查。或者知道边界的话，比如时间，可以用between and.
下面的会使索引失效：
4、like查询尽量不要以 % 开头。
5、查询时索引不要用表达式，否则会导致索引失效。
6、尽量将大查询分而治之变成小查询，通过缓存，提升性能。
7、不要用not in 查询。
8、不要用!=  <>等，
join查询过程：
当有索引的情况:
根据过滤条件得到一张驱动表，然后对于驱动表中的每一条，到t2中，如果是覆盖索引，直接就可以根据索引搜索到对应数据。如果不是覆盖索引，还有其他列，它的做法是先将join buffer读满，读取的是驱动表的数据，然后读取被驱动表的主键id，然后进行主键排序，再回表查询。
当没有索引关联时，先选择驱动表，然后填满join buffer，然后全表扫描被驱动表，这是一个非常耗时的过程。所以，在做关联时，关联列要选择索引关联。
数据表的拷贝：
insert into  select
Mysql自增不连续的原因
1、唯一键冲突；
2、事务回滚；
3、多次执行会成倍分配多个id;
--------EOF---------
微信分享/微信扫码阅读