mysql查看被锁住的表

 

 

mysql查看被锁住的表

 

 

mysql查看被锁住的表
 
查询是否锁表
show OPEN TABLES where In_use > 0;
查看所有进程
MySQL:
 
show processlist;
mariabd:
 
show full processlist;
查询到相对应的进程===然后 kill id
 
杀掉指定mysql连接的进程号
kill $pid
查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; 
查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; 
查看innodb引擎的运行时信息
show engine innodb status\G;
查看造成死锁的sql语句，分析索引情况，然后优化sql语句；
 
查看服务器状态
show status like '%lock%';
查看超时时间：
show variables like '%timeout%';

 

 

 

sql优化方式

1.查询数据库的数据尽量使用索引来查询，避免全表扫描。尽量只查询索引条件的字段，
　　例如，一张名为user用户数据表的id字段为索引，name字段为用户，为非索引字段，
　　　　　　当查询某条数据时，只查询id比查询name效率会高很多。
　　因为叶子节点存储索引值(id)的值，这样不会回表查询，实现了索引覆盖。

2.从数据库中查询数据，sql语句尽量避免使用or来连接条件查询。
　　因为使用or会导致执行sql的时候进行范围的索引扫描或则全表查询，效率降低。
　　例如，select id from user wehere name='a' or name='b',
　　　　可以改为以下的形式来提高查询的效率：
　　　　select id from user wehere name='a'
　　　　union
　　　　select id from user wehere name='b'

3.在连续数组的查询中，能使用between的情况下，尽量使用between，而不使用in。in和not in可能会导致全表查询

4.sql语句的where查询条件，对字段进行表达式操作，会导致mysql引擎放弃使用索引而进行全表扫描。
　　例如，select * from user where age/2=10(age字段有索引，在where中尽量不要进行表达式操作)
5.sql语句的where查询条件，对字段进行函数操作，也会导致mysql引擎放弃使用索引而进行全表扫描查询。
　　例如，select name from user where substr(name,2,3)='tom'

6.多张数据表查询数据，使用inner join，left/right join来替代子查询。
　　因为子查询需要在内存中创建临时表，来完成这个逻辑上的需要两个步骤的查询工作。(创建临时表也有可能会降低效率)

7.　　in()适合B表比A表数据小的情况，
　exists()适合B表比A表数据大的情况，
　　例如，select name from user a where exists(select name from user b where a.id=19) 和　　
　　　　  select name from user a where id in(select name from user b where b.id=19)

8.在使用like进行数据表的查询时，能用单%的情况下，不建议使用双%，
　　双%查询会导致mysql引擎放弃使用索引而进行全表扫描查询，查询时尽量把%放后面，或者不使用%。

9.在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左开始匹配。
　　左边匹配不到，整个sql不走索引。
　　例如，select name from user where u_one='1' and u_two='2'
　　(如果第一个没找到，不走索引，那么后面也不会走索引，即最左优先)

10.从数据库中查询数据时候，使用精确的类型匹配。
　　比如，　　  select id from user wehere id='3'　如果id建立的varchar类型的走索引，
　　如果写成　　select id from user wehere id=3　　 不走索引

11.选取最适应的字段属性，一般说来，数据库中的表越小，执行的查询也就会越快。
　　因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小
　　另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为not null，
　　这样在将来执行查询的时候，数据库不用去比较null的值。

12.数据量比较大时，合理使用分区表。使用partition by 子句定义每个分区存放的数据。
　　在执行查询的时候，优化器会根据分区定义过滤哪些没有我们需要数据的分区，
　　这样查询就无需扫描所有分区，只需要查询包含需要数据的分区就可以了。

13.合理选择存储引擎。
　　lnnodb: 适合数据完整性，并发性处理，擅长更新，删除。
　　myisam: 适合高速查询及插入。擅长插入和查询。

14.慢查询日志的使用，在调试的时候开启慢查询，定位的慢查询语句，再做优化策略。
　　关闭/开启语句 slow_query_log=0|1，
　　超出该时间临界点，就为慢查询 long_query_time=N
　　
　

 

 

mysql 为什么采用B+树作为索引的数据结构

  索引本身数据量也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。
  这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，
  所以索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数，提升索引效率。

常用的索引类型：

顺序查找:  最基本的查询算法-复杂度O（n），大数据量此算法效率糟糕。

二叉树查找（binary tree search）:  O(log2n)，对于某些情况，二叉查找树会退化成一个有n个节点的线性链，和顺序查找差不多。显然这个二叉树的查询效率就很低。

hash索引:  哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效，无法满足范围查找。

红黑树： 一种平衡二叉树，一个节点只能有左子树和右子树，导致树高度非常高,逻辑上很近的节点（父子）物理上可能很远，
　　   无法利用局部性，IO次数多查找慢,效率低。逻辑上相邻节点没法直接通过顺序指针关联，可能需要迭代回到上层节点重复向下遍历找到对应节点，效率低

B Tree： B-TREE 每个节点都是一个二元数组: [key, data]，所有节点都可以存储数据。key为索引,data为数据。
　　检索原理：首先从根节点进行二分查找，如果找到则返回对应节点的data，否则对相应区间的指针指向的节点递归进行查找，直到找到节点或未找到节点返回null指针。
　　缺点： 1.插入删除新的数据记录会破坏B-Tree的性质，因此在插入删除时，需要对树进行一个分裂、合并、转移等操作以保持B-Tree性质。造成IO操作频繁。
　　　　   2.区间查找可能需要返回上层节点重复遍历，IO操作繁琐。

B+Tree:  B Tree的变种
　　　　 与B Tree相比，B+Tree有以下不同点：非叶子节点不存储data，只存储索引key；只有叶子节点才存储data
　　　　Mysql中B+Tree：在经典B+Tree的基础上进行了优化，增加了顺序访问指针。
　　　　在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree。这样就提高了区间访问性能

2、B+树索引的性能优势：
1）、结合操作系统存储结构优化处理： mysql巧妙运用操作系统存储结构(一个节点分配到一个存储页中->尽量减少IO次数) & 磁盘预读(缓存预读->加速预读马上要用到的数据)。
2）、B+树单个节点能放多个子节点，相同IO次数，检索出更多信息。
3）、B+树只在叶子节点存储数据 & 所有叶子结点包含一个链指针 & 其他内层非叶子节点只存储索引数据。只利用索引快速定位数据索引范围，先定位索引再通过索引高效快速定位数据。


B+树的优势:
因为B+树没有与内部节点相关联的数据，所以在内存页面上可以容纳更多的键。因此，访问叶子节点上的数据需要更少的缓存丢失。
B+树的叶节点是链接的，因此对树中的所有对象进行全面扫描只需要一个线性遍历所有叶节点。另一方面，B树需要遍历树中的每一层。这种全树遍历可能比B+叶子的线性遍历涉及更多的缓存丢失。


B树的优势:
因为B树包含每个键的数据，所以经常访问的节点可以更靠近根，因此可以更快地访问。

 

mysql 数据库的索引类型有哪些

使用索引可以大大提高MySQL的检索速度，但是也会降低更新表的速度，索引虽好可不要贪多哦。

有哪些索引可供选择呢？

1、普通索引　　

最基本的索引，它没有任何限制，用于加速查询。

创建方法：

ａ.　建表的时候一起创建

CREATE TABLE mytable ( name VARCHAR(32) , INDEX index_mytable_name (name) );

b. 建表后，直接创建索引

CREATE INDEX index_mytable_name ON mytable(name);

c. 修改表结构

ALTER TABLE mytable ADD INDEX index_mytable_name (name);

注：如果是字符串字段，还可以指定索引的长度，在列命令后面加上索引长度就可以了（例如:name(11)）

2、唯一索引

索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

创建方法：

ａ.　建表的时候一起创建

CREATE TABLE mytable ( `name` VARCHAR(32) , UNIQUE index_unique_mytable_name (`name`) );

b. 建表后，直接创建索引

CREATE UNIQUE INDEX index_mytable_name ON mytable(name);

c. 修改表结构

ALTER TABLE mytable ADD UNIQUE INDEX index_mytable_name (name);

注：如果是字符串字段，还可以指定索引的长度，在列命令后面加上索引长度就可以了（例如:name(11)）

3、主键索引

是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。

创建方法：

ａ.　建表的时候一起创建

CREATE TABLE mytable ( `id` int(11) NOT NULL AUTO_INCREMENT , `name` VARCHAR(32) , PRIMARY KEY (`id`) );

b. 修改表结构

ALTER TABLE test.t1 ADD CONSTRAINT t1_pk PRIMARY KEY (id);

注：如果是字符串字段，还可以指定索引的长度，在列命令后面加上索引长度就可以了（例如:name(11)）

 

4、组合索引

指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。

创建方法：

ａ.　建表的时候一起创建

CREATE TABLE mytable ( `id` int(11) , `name` VARCHAR(32) , INDEX index_mytable_id_name (`id`,`name`) );

b. 建表后，直接创建索引

CREATE INDEX index_mytable_id_name ON mytable(id,name);

c. 修改表结构

ALTER TABLE mytable ADD INDEX index_mytable_id_name　(id,name);

5、全文索引

主要用来查找文本中的关键字，而不是直接与索引中的值相比较。

fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。

fulltext索引配合match against操作使用，而不是一般的where语句加like。

实际使用较少。


【拓展】、哪些情况需要创建索引

1、主键自动建立唯一索引

2、频繁作为查询条件的字段应该创建索引(where 后面的语句)

3、查询中与其它表关联的字段，外键关系建立索引

4、查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

5、查询中统计或者分组字段

（6）、哪些情况不要创建索引

1、表记录太少

2、经常增删改的表：提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。 因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件

3、Where条件里用不到的字段不创建索引

4、数据重复且分布平均的表字段，某个数据列包含许多重复的内容，建立索引没有太大实际效果。

 

mysql 中 innoDB 和myIsam 的区别

1、innodb和myisam是什么？
    innodb和myisam是mysql最常用的两种存储引擎。
     MySQL 有多种存储引擎，每种存储引擎有各自的优缺点（第3节说）。可以使用命令“show engines”查看引擎（下图附命令以及显示内容）。

2、innodb和myisam区别？
　　1)、事务和外键
　　　　InnoDB具有事务，支持4个事务隔离级别（读未提交（Read Uncommitted），
　　　　　　读已提交（Read Committed），可重复读（Repeated Read），串行化（Serializable）），
　　　　　　回滚，崩溃修复能力和多版本并发的事务安全。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。
　　　　MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。

　　2)、锁
　　　　mysql支持三种锁定级别，行级、页级、表级;
　　　　MyISAM支持表级锁定，提供与 Oracle 类型一致的不加锁读取(non-locking read in SELECTs)
　　　　InnoDB支持行级锁，InnoDB表的行锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，注意间隙锁的影响。
　　　　例如update table set num=1 where name like “%aaa%”

　　3)、并发
　　　　MyISAM读写互相阻塞：不仅会在写入的时候阻塞读取，MyISAM还会在读取的时候阻塞写入，但读本身并不会阻塞另外的读。并发性能较差。
　　　　InnoDB 读写阻塞与事务隔离级别相关。

　　4)、索引
　　　　InnoDB（索引组织表）使用的聚簇索引、索引就是数据，顺序存储，因此能缓存索引，也能缓存数据。
　　　　MyISAM（堆组织表）使用的是非聚簇索引、索引和文件分开，随机存储，只能缓存索引。

　　5)、存储
　　　　MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型， .frm文件存储表定义，数据文件的扩展名为.MYD，  索引文件的扩展名是.MYI。
　　　　InnoDB，基于磁盘的资源是InnoDB表空间数据文件和它的日志文件，InnoDB 表的大小只受限于操作系统文件的大小
　　　　注意：MyISAM表是保存成文件的形式，在跨平台的数据转移中使用MyISAM存储会省去不少的麻烦 



　　2)、全文索引（基本没人用，不需要关注，最好使用  ElasticSearch，Solr）

　　Innodb不支持全文索引，如果一定要用的话，最好使用sphinx等搜索引擎。myisam对中文支持的不是很好。

　　在 MySQL 5.7.6 之前，全文索引只支持英文全文索引，不支持中文全文索引，需要利用分词器把中文段落预处理拆分成单词，然后存入数据库。
　　从 MySQL 5.7.6 开始，MySQL内置了ngram全文解析器，用来支持中文、日文、韩文分词。

3、如何使用innodb和myisam？ 
 mysql 5.5版本以及以后默认使用innoDB,如果创建表时候不指定存储引擎默认使用 innoDB,如果不想使用默认的存储引擎可以使用如下方法改变：


　　a、表创建默认存储引擎：

    create table table_name(   
       id int primary key,   
       name varchar(50)   
    ) type=MyISAM;

　　b、表级修改存储引擎：

       alter  table `Table_name`  engine= MyISAM;

　　c、设置默认存储引擎，配置文件my.ini ：

      my.ini，在[mysqld]最后添加为上default-storage-engine=myisam，重启服务

 

如何避免sql注入漏洞

1、简单又有效的方法使用PreparedStatement
　　采用预编译语句集，它内置了处理SQL注入的能力，只要使用它的setXXX(如：setString)方法传值即可。

　　使用好处：
　　　　(1).代码的可读性和可维护性变好
　　　　(2).PreparedStatement尽最大可能提高性能.
　　　　(3).最重要的一点是极大地提高了安全性.

原理：

sql注入只对sql语句的编译过程有破坏作用，
而PreparedStatement sql语句编译阶段已经准备好了,执行阶段只是把输入串作为数据处理,
而不再对sql语句进行解析,准备,因此也就避免了sql注入问题。


我们的一些热门ORM框架在处理sql时候也都使用了PreparedStatement
比如MyBatis，我们在使用MyBatis要注意：在注入参数值得时候使用#{xxxx}
#{xxxx} 已经启用了预编译功能，在SQL执行前，会先将上面的SQL发送给数据库进行编译；
执行时，直接使用编译好的SQL，替换占位符“?”就可以了。因为SQL注入只能对编译过程起作用，所以这样的方式就很好地避免了SQL注入的问题。
“${xxx}”这样格式的参数会直接参与SQL编译，从而不能避免注入攻击。有时有些操作要使用这种方式，比如传入表，模糊匹配等。
可以使用bind + #{} 模糊查询 防止SQL注入 (#{}进行预编译，传递的参数不进行编译，只作为参数，相当于PreparedStatement）
bind 元素可以从OGNL 表达式中创建一个变量并将其绑定到上下文。比如：

<select id="selectBlog" resultType="Blog">

<bind name="pattern" value="'%' + _parameter.getTitle() + '%'" />

SELECT * FROM BLOG

WHERE title LIKE #{pattern}

</select>

2.使用过滤器

如果我们做不到所有的sql语句都使用PreparedStatement，我们可以使用过滤器，进行全局的拦截这些字符串。在过滤器中，使用正则表达式过滤传入的参数 。
正则表达式，判断是否匹配：

String begin="您的请求参数信息 ";
String pattern="|and|exec|execute|insert|select|delete|update|count|drop|*|%|chr|mid|master|truncate|char|declare|sitename|net user|xp_cmdshell|;|or|-|+|,|like";
//可以通过配置文件，去配置这些特殊字符，一边随时添加一些关键字

Pattern r = Pattern.compile(pattern);
Matcher isMatch = r.matcher( begin);

if(isMatch.find()){
    //危险请求参数
}

参考：https://www.toutiao.com/i6792175892463354380/