Mysql

mysql索引类型

btree索引:mysql中普遍使用B+Tree索引,但在实现方式上又分为聚簇索引和非聚簇索引。
hash索引:

聚簇索引和非聚簇索引

聚簇索引是指主索引文件和数据文件为同一份文件,聚簇索引主要用在Innodb存储引擎中。在该索引实现方式中B+Tree的叶子节点上的data就是数据本身,key为主键。
非聚簇索引是指B+Tree的叶子节点上的data并不是数据本身,而是数据存放的地址。主索引和辅助索引没啥区别,只是主索引中的key一定得是唯一的。主要用在MyISAM存储引擎中。非聚簇索引比聚簇索引多了一次读取数据的IO操作,所以查找性能上会差。

mysql表类型

1.InnoDB:
InnoDB,支持外键、行锁、事务是他的最大特点。如果有大量的update和insert,建议使用InnoDB,特别是针对多个并发和QPS较高的情况。

支持事务,InnoDB的AUTOCOMMIT默认是打开的,即每条SQL语句会默认被封装成一个事务自动提交,这样会影响速度,所以最好是把多条SQL语句显示放在begin和commit之间,组成一个事务去提交。
不加锁读取
支持外键
支持行锁
不支持FULLTEXT类型的索引
DELETE 表时,是一行一行的删除
InnoDB 把数据和索引存放在表空间里面
跨平台可直接拷贝使用
InnoDB中必须包含AUTO_INCREMENT类型字段的索引
表格很难被压缩
InnoDB的主键范围更大,最大是MyISAM的2倍
innodb属于索引组织表,有两种存储方式,共享表空间存储和多表空间存储,两种存储方式的表结构和myisam一样,以表名开头,扩展名是.frm。如果使用共享表空间,那么所有表的数据文件和索引文件都保存在一个表空间里,一个表空间可以有多个文件,通过innodb_data_file_path和innodb_data_home_dir参数设置共享表空间的位置和名字,一般共享表空间的名字叫ibdata1-n。如果使用多表空间,那么每个表都有一个表空间文件用于存储每个表的数据和索引,文件名以表名开头,以.ibd为扩展名。

2.MyISAM:
MyISAM:是基于传统的ISAM类型,ISAM是Indexed Sequential Access Method (有索引的顺序访问方法) 的缩写,它是存储记录和文件的标准方法。不是事务安全的,不支持外键。如果执行大量的select,MyISAM比较适合。

不支持事务,回滚将造成不完全回滚,不具有原子性。
不支持外键
只支持表级锁,不支持行锁定,这样同一个表上的读锁和写锁是互斥的,MyISAM并发读写时如果等待队列中既有读请求又有写请求,默认写请求的优先级高,即使读请求先到,所以MyISAM不适合于有大量查询和修改并存的情况,那样查询进程会长时间阻塞。因为MyISAM是锁表,所以某项读操作比较耗时会使其他写进程饿死。在操作myisam表时,select,update,delete,insert语句都会给表自动加锁,如果加锁以后的表满足insert并发的情况下,可以在表的尾部插入新的数据。

支持全文搜索,全文索引是指对char、varchar和text中的每个词(停用词除外)建立倒排序索引。MyISAM的全文索引其实没啥用,因为它不支持中文分词,必须由使用者分词后加入空格再写到数据表里,而且少于4个汉字的词会和停用词一样被忽略掉。
DELETE 表时,先drop表,然后重建表
MyISAM 表被存放在三个文件:frm 文件存放表格定义,数据文件是MYD (MYData) ,索引文件是MYI (MYIndex)引伸。
跨平台很难直接拷贝
MyISAM中可以使AUTO_INCREMENT类型字段建立联合索引
表格可以被压缩。
MyISAM支持GIS数据,InnoDB不支持。GIS数据是指空间数据对象:Point,Line,Polygon,Surface等。
MyISAM保存表的具体行数,在查询不带where时,直接返回保存的行数。所以没有where的count(*)使用MyISAM要比InnoDB快得多。因为MyISAM内置了一个计数器,count(*)时它直接从计数器中读;而InnoDB必须扫描全表。所以在InnoDB上执行count(*)时一般要伴随where,且where中要包含主键以外的索引列。这里特别强调“主键以外”,是因为InnoDB中primary index是和raw data存放在一起的,而secondary index则是单独存放,然后有个指针指向primary key。所以只是count(*)的话使用secondary index扫描更快,而primary key则主要在扫描索引同时要返回raw data时的作用较大。

3.对比选择:
MyISAM相对简单,在效率上要优于InnoDB。从效率上来看,如果系统读多写少,对原子性要求低,那么MyISAM最好的选择,且MyISAM恢复速度快,可直接用备份覆盖恢复。如果系统读少写多,尤其是高并发写入,InnoDB就是首选了。

MyISAM与InnoDB两者之间区别与选择,详细总结,性能对比
https://www.cnblogs.com/y-rong/p/8110596.html

mysql中不要用blob等大字段

在开发规范中,我们一般都是要求字段不要设置成blob这种大字段。原因是查询的时候效率问题。另外一个就是在插入量大的时候,生成的binlog太多,容易导致一些问题。线上就遇到了,1k多的插入,binlog狂写,磁盘干满了。

utf8mb4

MySQL在5.5.3之后增加了utf8mb4的编码,mb4就是most bytes 4的意思,专门用来兼容四字节的unicode。
utf8mb4是utf8的超集,为了节省空间,一般情况下使用utf8就够了。

int(11)

int(n)中,n表示查询显示时的最小宽度,即最小的字符数。若不够最小长度,则前面补0。

发表评论