MySQL 随笔记录

最新推荐文章于 2025-08-22 11:10:38 发布

转载最新推荐文章于 2025-08-22 11:10:38 发布 · 96 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://blog.51cto.com/kangjunfei/2177507

文章标签：

#数据库

engine=innodb和engine=myisam的区别

myisam是默认类型它是存储记录和文件的标准方法与其他存储引擎比较,MyISAM具有检查和修复表格的大多数工具. MyISAM表格可以被压缩,而且它们支持全文搜索.它们不是事务安全的,而且也不支持外键。如果事物回滚将造成不完全回滚，不具有原子性。如果执行大量的SELECT，MyISAM是更好的选择。

innodb这种类型是事务安全的.它与BDB类型具有相同的特性,它们还支持外键.InnoDB表格速度很快.具有比BDB还丰富的特性,因此如果需要一个事务安全的存储引擎,建议使用它.如果你的数据执行大量的INSERT或UPDATE,出于性能方面的考虑，应该使用InnoDB表,
对于支持事物的InnoDB类型的标，影响速度的主要原因是AUTOCOMMIT默认设置是打开的，而且程序没有显式调用BEGIN 开始事务，导致每插入一条都自动Commit，严重影响了速度。可以在执行sql前调用begin，多条sql形成一个事物（即使autocommit打开也可以），将大大提高性能。

** 常用的mysql索引方法有 B-Tree 和Hash索引

**  ***key distribution可以认为是表的“统计信息**

它决定表做join的时候的join顺序，以及在某个查询中是否会用到某个索引
要查看key distribution，可以使用 SHOW INDEX 或者查询 INFORMATION_SCHEMA.STATISTICS 表，两者是一样的
show index from table;
这个命令有助于诊断性能低下的查询，尤其是查询是否使用了可用的索引
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
1.Table
表的名称。
2.Non_unique
如果索引不能包括重复词，则为0。如果可以，则为1。
3.Key_name
索引的名称。
4.Seq_in_index
索引中的列序列号，从1开始。
5.Column_name
列名称。
6.Collation
列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）。
7.Cardinality
索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。
8.Sub_part
如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。
9.Packed
指示关键字如何被压缩。如果没有被压缩，则为NULL。
10.Null
如果列含有NULL，则含有YES。如果没有，则该列含有NO。
11.Index_type
用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。
12.Comment
多种评注。

ANALYZE命令主要对表做 key distribution 分析 //线上不要用别锁表
ANALYZE TABLE 作用的对象是 InnoDB, NDB, 和 MyISAM 表，对视图不起作用。在分析期间，会对表加上read lock(InnoDB, MyISAM) , 如果自从上次分析之后，表没有发生变化，则不会被再次分析。

SHOW OPEN TABLES [FROM db_name] [LIKE 'pattern']

　　SHOW OPEN TABLES列举在表缓存中当前被打开的非TEMPORARY表。

　　SHOW OPEN TABLES会返回以下字段：

　　·Database

　　含有该表的数据库。

　　·Table

　　表名称。

　　·In_use

　　表当前被查询使用的次数。如果该数为零，则表是打开的，但是当前没有被使用。

　　·Name_locked

　　表名称是否被锁定。名称锁定用于取消表或对表进行重命名等操作。

主键索引 PRIMARY 唯一的不能为空一个表只能有一个主键索引通常在建表的时候就指定 id
建表时指定

CREATE 表名(ID INT NOT NULL,USERNAME VARCHAR(16) NOT NULL,PRIMARY KEY(ID))
PRIMARY KEY(ID)

普通索引 NORMAL 不受限制
建表时指定
CREATE 表名(ID INT NOT NULL,USERNAME VARCHAR(16) NOT NULL,INDEX USERNAME_INDEX(USERNAME(16))) //给列USERNAME建普通索引USERNAME_INDEX

ALTER语句指定

ALTER 表名 ADD INDEX U_INDEX (USERNAME) //给列USERNAME建普通索引 U_INDEX

删除索引

DROP INDEX U_INDEX ON 表名 //删除表t_user中的索引U_INDEX

唯一索引 UNIQUE 唯一可允许为空
建表时指定

CREATE 表名r(ID INT NOT NULL,USERNAME VARCHAR(16) NOT NULL,UNIQUE U_INDEX(USERNAME)) //给列USERNAME添加唯一索引T_USER

ALTER语句指定

ALTER 表名 ADD UNIQUE u_index(USERNAME) //给列T_USER添加唯一索引u_index

删除索引

DROP INDEX U_INDEX ON 表名

全文索引 FULLTEXT 长篇文章可用如果短篇文章可用INDEX

char和varchar再数据库设计的时候要明白：
char 是固定长度0-255 如果存数据的时候有空格则会把空格存入数据表中，取数据的时候空格丢弃

varchar 是可变长度 0-65535 如果存数据的时候有空格则会不会把空格存入数据表中，取数据的时候如果数据有空格则会保留空格

索引不足之处：
索引提高了查询的速度，但是降低了INSERT、UPDATE、DELETE的速度，因为在插入、修改、删除数据时，还要同时操作一下索引文件；
占用一定的磁盘空间
一般情况下不是鼓励使用like,如果非使用，那么需要注意 like"%aaa%"不会使用索引；但like“aaa%”会使用索引。

转载于:https://blog.51cto.com/kangjunfei/2177507