【Mysql学习】索引操作

一:创建索引

第一种创建索引方式:

CREATE TABLE 表名( 属性名 数据类型[完整性约束条件],
属性名 数据类型[完整性约束条件],
......
属性名 数据类型
[ UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY
[ 别名] ( 属性名1 [(长度)] [ ASC | DESC] )
);

UNIQUE是可选参数,表示索引为唯一性索引;

FULLTEXT是可选参数,表示索引为全文索引;

SPATIAL也是可选参数,表示索引为空间索引;

INDEXKEY参数用来指定字段为索引的,两者选择其中之一就可以了,作用是一样的;

"别名"是可选参数,用来给创建的索引取的新名称;

"属性1"参数指定索引对应的字段的名称,该字段必须为前面定义好的字段;

"长度"是可选参数,其指索引的长度,必须是字符串类型才可以使用;

"ASC"和"DESC"都是可选参数,"ASC"参数表示升序排列,"DESC"参数表示降序排列。

1. 创建普通索引的SQL代码如下:

CREATE TABLE index1(id INT,
                    name VARCHAR(20),
                    sex BOOLEAN,
                    INDEX(id)
);
2. 创建一个唯一性索引的SQL代码如下:

CREATE TABLE index2(id INT UNIQUE,
                    name VARCHAR(20),
                    UNIQUE INDEX index2_id(id ASC)
);
3.创建全文索引的SQL代码如下:

CREATE TABLE index3(id INT,
                    info VARCHAR(20),
                    FULLTEXT INDEX index3_info(info)
)ENGINE=MyISAM;
4. 创建单列索引的SQL代码如下:

CREATE TABLE index4(id INT,
                    subject VARCHAR(30),
                    INDEX index4_st(subject(10))
);
5. 创建多列索引的SQL代码如下:

CREATE TABLE index5(id INT,
                    name VARCHAR(20),
                    sex CHAR(4),
                    INDEX index5_ns(name,sex)
);
6.建空间索引的SQL代码如下:

CREATE TABLE index6(id INT,
                    space GEOMETRY NOT NULL,
                    SPATIAL INDEX index6_sp(space)
)ENGINE=MyISAM;
View Code

第二种创建索引方式:

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING index_type] ON table_name (index_col_name,...)

其中对应的语法变量信息如下:

[UNIQUE|FULLTEXT|SPATIAL]:中括号中的三个关键字表示创建的索引类型,他们分别表示唯一索引、全文索引、空间索引三种不同的索引类型。如果我们不指定任何关键字,则默认为普通索引。

index_name:表示索引的名字,由用户自行定义,以便以后对该索引进行修改管理操作。

index_type:表示索引的具体实现方式,在MySql中,有两种不同形式的索引----BTREE索引和HASH索引。在存储引擎为MYISAM和INNODB的表中只能使用BTREE,其默认值就是BTREE;在存储引擎为MEMORY或者HEAP的表中可以使用HASH和BTREE两种类型的索引,其默认值是HASH。

存储引擎

允许的索引类型

MyISAM

BTREE

InnoDB

BTREE

MEMORY/HEAP

HASH, BTREE

index_col_name:表示需要创建索引的字段名称,我们还可以针对多个字段创建复合索引,只需要在多个字段名称之间以英文逗号隔开即可。

此外,对于char或varchar类型的字段,我们还可以只使用字段内容前面的一部分来创建索引,只需要在对应的字段后面加上形如(length)的指令即可,表示只需要使用字段内容前面的length个字符来创建索引。

CREATE INDEX idx_user_username ON user (username(6));

由于多数字段的前6个字符通常不同,所以此索引不会比使用字段的全部内容创建的索引速度慢很多。另外,使用字段的一部分创建索引可以使索引文件大大减小,从而节省了大量的磁盘空间,有可能提高INSERT操作的速度。

必须注意的是:在MySql中,对已TEXT和BLOG这种大数据类型的字段,必须给出前缀长度(length)才能成功创建索引。

 第三种创建索引方式:

ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name (index_col_name,...) [USING  index_type]

在MySql中,只有当表的存储引擎为MYISAM,INNODB和BDB类型时,你才能向具有NULL值的列或者数据类型为TEXT或BLOB的列添加索引。

二:删除索引

在MySql中删除索引的方法非常简单,其完整语法如下:

ALTER TABLE table_name DROP INDEX index_name;

drop index index_name on table_name ;

 

alter table table_name drop primary key ;

其中,前两条语句是等价的,删除掉table_name中的索引index_name。

第3条语句只在删除PRIMARY KEY索引时使用,因为一个表只可能有一个PRIMARY KEY索引,因此不需要指定索引名。如果没有创建PRIMARY KEY索引,但表具有一个或多个UNIQUE索引,则MySQL将删除第一个UNIQUE索引。

如果从表中删除了某列,则索引会受到影响。对于多列组合的索引,如果删除其中的某列,则该列也会从索引中删除。如果删除组成索引的所有列,则整个索引将被删除。

三:修改索引

在MySql中并没有提供修改索引的直接指令,一般情况下,我们先删除原索引,再根据需要创建一个同名的索引,从而变相地实现修改索引的操作。

//先删除索引

ALTER TABLE table_name DROP INDEX index_name;

//创建同名的索引

CREATE INDEX index_name ON table_name (index_col_name,...);

四:查看索引

在MySql中,要查看某个数据库表中的索引页非常简单,只需要使用以下两个命令中的任意一种即可:

SHOW INDEX FROM table_name [FROM db_name];

SHOW INDEX FROM [db_name.]table_name;

show keys from tablename;

查询的结果显示信息为

含义
Table 表名
Non_unique 如果索引不能包括重复词,则为0。如果可以,则为1
Key_name 索引的名称
Seq_in_index 索引中的列序列号,从1开始
Column_name 列名称
Collation 列以什么方式存储在索引中。在MySQL中,有值‘A’(升序)或NULL(无分类)
Cardinality 索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数,所以即使对于小型表,该值也没有必要是精确的。基数越大,当进行联合时,MySQL使用该索引的机会就越大
Sub_part 如果列只是被部分地编入索引,则为被编入索引的字符的数目。如果整列被编入索引,则为NULL
Packed 指示关键字如何被压缩。如果没有被压缩,则为NULL
Null 如果列含有NULL,则含有YES。如果没有,则该列含有NO
Index_type 用过的索引方法(BTREE, FULLTEXT, HASH, RTREE)
Comment 解释
MySQL索引详解(优缺点,何时需要/不需要创建索引,索引及sql语句的优化)
 一、什么是索引?
  索引是对数据库表中的一列或多列值进行排序的一种结构,使用索引可以快速访问数据库表中的特定信息。

二、索引的作用?
  索引相当于图书上的目录,可以根据目录上的页码快速找到所需的内容,提高性能(查询速度)

三、优点:
通过创建唯一性索引,可以保证数据库表中的每一行数据的唯一性。
可以加快数据的检索速度
可以加速表与表之间的连接
在使用分组和排序进行检索的时候,可以减少查询中分组和排序的时间

四、缺点
创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。
索引需要占用物理空间,数据量越大,占用空间越大
会降低表的增删改的效率,因为每次增删改索引,都需要进行动态维护
 
五、什么时候需要创建索引
 主键自动建立唯一索引
 频繁作为查询条件的字段应该创建索引
 查询中排序的字段创建索引将大大提高排序的速度(索引就是排序加快速查找
 查询中统计或者分组的字段;
 
 六、什么时候不需要创建索引
频繁更新的字段不适合创建索引,因为每次更新不单单是更新记录,还会更新索引,保存索引文件
where条件里用不到的字段,不创建索引;
表记录太少,不需要创建索引;
经常增删改的表;
数据重复且分布平均的字段,因此为经常查询的和经常排序的字段建立索引。注意某些数据包含大量重复数据,因此他建立索引就没有太大的效果,例如性别字段,只有男女,不适合建立索引。
 
七、索引的分类:
普通索引:最基本的索引,它没有任何限制
唯一索引:索引列的值必须唯一,且不能为空,如果是组合索引,则列值的组合必须唯一。
主键索引:特殊的索引,唯一的标识一条记录,不能为空,一般用primary key来约束。
联合索引:在多个字段上建立索引,能够加速查询到速度

 八、索引和sql语句的优化
1、前导模糊查询不能使用索引,
  如name like ‘%静’
2、Union、in、or可以命中索引,建议使用in
3、负条件查询不能使用索引,可以优化为in查询,
  其中负条件有!=、<>、not innot exists、not like等
4、联合索引最左前缀原则,又叫最左侧查询,
  如果在(a,b,c)三个字段上建立联合索引,那么它能够加快a|(a,b)|(a,b,c)三组的查询速度。
5、建立联合查询时,区分度最高的字段在最左边
6、如果建立了(a,b)联合索引,就不必再单独建立a索引。
  同理,如果建立了(a,b,c)索引就不必再建立a,(a,b)索引
7、存在非等号和等号混合判断条件时,在建索引时,要把等号条件的列前置
8、范围列可以用到索引,但是范围列后面的列无法用到索引。
  索引最多用于一个范围列,如果查询条件中有两个范围列则无法全用到索引。范围条件有:<、<=、>、>=、between等。
9、把计算放到业务层而不是数据库层。
  在字段上计算不能命中索引,
10、强制类型转换会全表扫描,
  如果phone字段是varcher类型,则下面的SQL不能命中索引。Select * fromuser where phone=13800001234
11、更新十分频繁、数据区分度不高的字段上不宜建立索引。
  更新会变更B+树,更新频繁的字段建立索引会大大降低数据库性能。
  “性别”这种区分度不太大的属性,建立索引是没有什么意义的,不能有效过滤数据,性能与全表扫描类似。
  一般区分度在80%以上就可以建立索引。区分度可以使用count(distinct(列名))/count(*)来计算。
12、利用覆盖索引来进行查询操作,避免回表。
  被查询的列,数据能从索引中取得,而不是通过定位符row-locator再到row上获取,即“被查询列要被所建的索引覆盖”,这能够加速度查询。
13、建立索引的列不能为null,使用not null约束及默认值
14、利用延迟关联或者子查询优化超多分页场景,
  MySQL并不是跳过offset行,而是取offset+N行,然后放弃前offset行,返回N行,那当offset特别大的时候,效率非常低下,要么控制返回的总数,要么对超过特定阈值的页进行SQL改写。
15、业务上唯一特性的字段,即使是多个字段的组合,也必须建成唯一索引。
16、超过三个表最好不要用join,
  需要join的字段,数据类型必须一致,多表关联查询时,保证被关联的字段需要有索引。
17、如果明确知道查询结果只要一条,limit 1能够提高效率,比如验证登录的时候。
18、Select语句务必指明字段名称
19、如果排序字段没有用到索引,就尽量少排序
20、尽量用union all 代替 union。
  Union需要将集合合并后在进行唯一性过滤操作,这会涉及到排序,大量的cpu运算,加大资源消耗及延迟,当然,使用union all的前提条件是两个结果集没有重复数据。

21、使用合理的分页提高效率。
  select id,name from product limit 866613, 20
使用上述SQL语句做分页的时候,可能有人会发现,随着表数据量的增加,直接使用limit分页查询会越来越慢。
优化的方法如下:
可以取前一页的最大行数的id,然后根据这个最大的id来限制下一页的起点。
比如此列中,上一页最大的id是866612。
SQL可以采用如下的写法:select id,name from product where id> 866612 limit 20。
View Code
mysql Hash索引和BTree索引区别
Hash仅支持=、>、>=、<、<=、between。BTree可以支持like模糊查询

索引是帮助mysql获取数据的数据结构。最常见的索引是Btree索引和Hash索引。

不同的引擎对于索引有不同的支持:Innodb和MyISAM默认的索引是Btree索引;而Mermory默认的索引是Hash索引。

我们在mysql中常用两种索引算法BTree和Hash,两种算法检索方式不一样,对查询的作用也不一样。
一、BTree
BTree索引是最常用的mysql数据库索引算法,因为它不仅可以被用在=,>,>=,<,<=和between这些比较操作符上,而且还可以用于like操作符,只要它的查询条件是一个不以通配符开头的常量,例如:
select * from user where name like ‘jack%’;
select * from user where name like ‘jac%k%’;
如果一通配符开头,或者没有使用常量,则不会使用索引,例如:
select * from user where name like ‘%jack’;
select * from user where name like simply_name;
二、Hash
Hash索引只能用于对等比较,例如=,<=>(相当于=)操作符。由于是一次定位数据,不像BTree索引需要从根节点到枝节点,最后才能访问到页节点这样多次IO访问,所以检索效率远高于BTree索引。
但为什么我们使用BTree比使用Hash多呢?主要Hash本身由于其特殊性,也带来了很多限制和弊端:

Hash索引仅仅能满足“=”,“IN”,“<=>”查询,不能使用范围查询。
联合索引中,Hash索引不能利用部分索引键查询。
对于联合索引中的多个列,Hash是要么全部使用,要么全部不使用,并不支持BTree支持的联合索引的最优前缀,也就是联合索引的前面一个或几个索引键进行查询时,Hash索引无法被利用。
Hash索引无法避免数据的排序操作
由于Hash索引中存放的是经过Hash计算之后的Hash值,而且Hash值的大小关系并不一定和Hash运算前的键值完全一样,所以数据库无法利用索引的数据来避免任何排序运算。
Hash索引任何时候都不能避免表扫描
Hash索引是将索引键通过Hash运算之后,将Hash运算结果的Hash值和所对应的行指针信息存放于一个Hash表中,由于不同索引键存在相同Hash值,所以即使满足某个Hash键值的数据的记录条数,也无法从Hash索引中直接完成查询,还是要通过访问表中的实际数据进行比较,并得到相应的结果。
Hash索引遇到大量Hash值相等的情况后性能并不一定会比BTree高
对于选择性比较低的索引键,如果创建Hash索引,那么将会存在大量记录指针信息存于同一个Hash值相关联。这样要定位某一条记录时就会非常麻烦,会浪费多次表数据访问,而造成整体性能底下。
1. hash索引查找数据基本上能一次定位数据,当然有大量碰撞的话性能也会下降。而btree索引就得在节点上挨着查找了,很明显在数据精确查找方面hash索引的效率是要高于btree的;
2. 那么不精确查找呢,也很明显,因为hash算法是基于等值计算的,所以对于“like”等范围查找hash索引无效,不支持;
3. 对于btree支持的联合索引的最优前缀,hash也是无法支持的,联合索引中的字段要么全用要么全不用。提起最优前缀居然都泛起迷糊了,看来有时候放空得太厉害;
4. hash不支持索引排序,索引值和计算出来的hash值大小并不一定一致。
View Code
一、MySQL的目录结构
   1、bin目录
        用于放置一些可执行文件,如mysql.exe、mysqld.exe、mysqlshow.exe等。
   2、data目录
        用于放置一些日志文件以及数据库。
   3、include目录
        用于放置一些头文件,如:mysql.h、mysql_ername.h等。
   4、lib目录
        用于放置一系列库文件。
   5、share目录
        用于存放字符集、语言等信息。
   6、my.ini
        是MySQL数据库中使用的配置文件。
   7、my-huge.ini
        适合超大型数据库的配置文件。
   8、my-large.ini
        适合大型数据库的配置文件。
   9、my-medium.ini
        适合中型数据库的配置文件。
   10、my-small.ini
        适合小型数据库的配置文件。
   11、my-template.ini
        是配置文件的模板,MySQL配置向导将该配置文件中选择项写入到my.ini文件。
   12、my-innodb-heavy-4G.ini
        表示该配置文件只对于InnoDB存储引擎有效,而且服务器的内存不能小于4GB。

二、配置文件参数详解

[client]
port = 3306
socket = /tmp/mysql.sock

[mysqld]
port = 3306
socket = /tmp/mysql.sock

basedir = /usr/local/mysql
datadir = /data/mysql
pid-file = /data/mysql/mysql.pid
user = mysql
bind-address = 0.0.0.0
server-id = 1 #表示是本机的序号为1,一般来讲就是master的意思

skip-name-resolve
# 禁止MySQL对外部连接进行DNS解析,使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意,如果开启该选项,
# 则所有远程主机连接授权都要使用IP地址方式,否则MySQL将无法正常处理连接请求

#skip-networking

back_log = 600
# MySQL能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求,这就起作用,
# 然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。
# 如果期望在一个短时间内有很多连接,你需要增加它。也就是说,如果MySQL的连接数据达到max_connections时,新来的请求将会被存在堆栈中,
# 以等待某一连接释放资源,该堆栈的数量即back_log,如果等待连接的数量超过back_log,将不被授予连接资源。
# 另外,这值(back_log)限于您的操作系统对到来的TCP/IP连接的侦听队列的大小。
# 你的操作系统在这个队列大小上有它自己的限制(可以检查你的OS文档找出这个变量的最大值),试图设定back_log高于你的操作系统的限制将是无效的。

max_connections = 1000
# MySQL的最大连接数,如果服务器的并发连接请求量比较大,建议调高此值,以增加并行连接数量,当然这建立在机器能支撑的情况下,因为如果连接数越多,介于MySQL会为每个连接提供连接缓冲区,就会开销越多的内存,所以要适当调整该值,不能盲目提高设值。可以过'conn%'通配符查看当前状态的连接数量,以定夺该值的大小。

max_connect_errors = 6000
# 对于同一主机,如果有超出该参数值个数的中断错误连接,则该主机将被禁止连接。如需对该主机进行解禁,执行:FLUSH HOST。

open_files_limit = 65535
# MySQL打开的文件描述符限制,默认最小1024;当open_files_limit没有被配置的时候,比较max_connections*5和ulimit -n的值,哪个大用哪个,
# 当open_file_limit被配置的时候,比较open_files_limit和max_connections*5的值,哪个大用哪个。

table_open_cache = 128
# MySQL每打开一个表,都会读入一些数据到table_open_cache缓存中,当MySQL在这个缓存中找不到相应信息时,才会去磁盘上读取。默认值64
# 假定系统有200个并发连接,则需将此参数设置为200*N(N为每个连接所需的文件描述符数目);
# 当把table_open_cache设置为很大时,如果系统处理不了那么多文件描述符,那么就会出现客户端失效,连接不上

max_allowed_packet = 4M
# 接受的数据包大小;增加该变量的值十分安全,这是因为仅当需要时才会分配额外内存。例如,仅当你发出长查询或MySQLd必须返回大的结果行时MySQLd才会分配更多内存。
# 该变量之所以取较小默认值是一种预防措施,以捕获客户端和服务器之间的错误信息包,并确保不会因偶然使用大的信息包而导致内存溢出。

binlog_cache_size = 1M
# 一个事务,在没有提交的时候,产生的日志,记录到Cache中;等到事务提交需要提交的时候,则把日志持久化到磁盘。默认binlog_cache_size大小32K

max_heap_table_size = 8M
# 定义了用户可以创建的内存表(memory table)的大小。这个值用来计算内存表的最大行数值。这个变量支持动态改变

tmp_table_size = 16M
# MySQL的heap(堆积)表缓冲大小。所有联合在一个DML指令内完成,并且大多数联合甚至可以不用临时表即可以完成。
# 大多数临时表是基于内存的(HEAP)表。具有大的记录长度的临时表 (所有列的长度的和)或包含BLOB列的表存储在硬盘上。
# 如果某个内部heap(堆积)表大小超过tmp_table_size,MySQL可以根据需要自动将内存中的heap表改为基于硬盘的MyISAM表。还可以通过设置tmp_table_size选项来增加临时表的大小。也就是说,如果调高该值,MySQL同时将增加heap表的大小,可达到提高联接查询速度的效果

read_buffer_size = 2M
# MySQL读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区,MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。
# 如果对表的顺序扫描请求非常频繁,并且你认为频繁扫描进行得太慢,可以通过增加该变量值以及内存缓冲区大小提高其性能

read_rnd_buffer_size = 8M
# MySQL的随机读缓冲区大小。当按任意顺序读取行时(例如,按照排序顺序),将分配一个随机读缓存区。进行排序查询时,
# MySQL会首先扫描一遍该缓冲,以避免磁盘搜索,提高查询速度,如果需要排序大量数据,可适当调高该值。但MySQL会为每个客户连接发放该缓冲空间,所以应尽量适当设置该值,以避免内存开销过大

sort_buffer_size = 8M
# MySQL执行排序使用的缓冲大小。如果想要增加ORDER BY的速度,首先看是否可以让MySQL使用索引而不是额外的排序阶段。
# 如果不能,可以尝试增加sort_buffer_size变量的大小

join_buffer_size = 8M
# 联合查询操作所能使用的缓冲区大小,和sort_buffer_size一样,该参数对应的分配内存也是每连接独享

thread_cache_size = 8
# 这个值(默认8)表示可以重新利用保存在缓存中线程的数量,当断开连接时如果缓存中还有空间,那么客户端的线程将被放到缓存中,
# 如果线程重新被请求,那么请求将从缓存中读取,如果缓存中是空的或者是新的请求,那么这个线程将被重新创建,如果有很多新的线程,
# 增加这个值可以改善系统性能.通过比较Connections和Threads_created状态的变量,可以看到这个变量的作用。(–>表示要调整的值)
# 根据物理内存设置规则如下:
# 1G  —> 8
# 2G  —> 16
# 3G  —> 32
# 大于3G  —> 64

query_cache_size = 8M
#MySQL的查询缓冲大小(从4.0.1开始,MySQL提供了查询缓冲机制)使用查询缓冲,MySQL将SELECT语句和查询结果存放在缓冲区中,
# 今后对于同样的SELECT语句(区分大小写),将直接从缓冲区中读取结果。根据MySQL用户手册,使用查询缓冲最多可以达到238%的效率。
# 通过检查状态值'Qcache_%',可以知道query_cache_size设置是否合理:如果Qcache_lowmem_prunes的值非常大,则表明经常出现缓冲不够的情况,
# 如果Qcache_hits的值也非常大,则表明查询缓冲使用非常频繁,此时需要增加缓冲大小;如果Qcache_hits的值不大,则表明你的查询重复率很低,
# 这种情况下使用查询缓冲反而会影响效率,那么可以考虑不用查询缓冲。此外,在SELECT语句中加入SQL_NO_CACHE可以明确表示不使用查询缓冲

query_cache_limit = 2M
#指定单个查询能够使用的缓冲区大小,默认1M

key_buffer_size = 4M
#指定用于索引的缓冲区大小,增加它可得到更好处理的索引(对所有读和多重写),到你能负担得起那样多。如果你使它太大,
# 系统将开始换页并且真的变慢了。对于内存在4GB左右的服务器该参数可设置为384M或512M。通过检查状态值Key_read_requests和Key_reads,
# 可以知道key_buffer_size设置是否合理。比例key_reads/key_read_requests应该尽可能的低,
# 至少是1:100,1:1000更好(上述状态值可以使用SHOW STATUS LIKE 'key_read%'获得)。注意:该参数值设置的过大反而会是服务器整体效率降低

ft_min_word_len = 4
# 分词词汇最小长度,默认4

transaction_isolation = REPEATABLE-READ
# MySQL支持4种事务隔离级别,他们分别是:
# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE.
# 如没有指定,MySQL默认采用的是REPEATABLE-READ,ORACLE默认的是READ-COMMITTED

log_bin = mysql-bin
binlog_format = mixed
expire_logs_days = 30 #超过30天的binlog删除

log_error = /data/mysql/mysql-error.log #错误日志路径
slow_query_log = 1
long_query_time = 1 #慢查询时间 超过1秒则为慢查询
slow_query_log_file = /data/mysql/mysql-slow.log

performance_schema = 0
explicit_defaults_for_timestamp

#lower_case_table_names = 1 #不区分大小写

skip-external-locking #MySQL选项以避免外部锁定。该选项默认开启

default-storage-engine = InnoDB #默认存储引擎

innodb_file_per_table = 1
# InnoDB为独立表空间模式,每个数据库的每个表都会生成一个数据空间
# 独立表空间优点:
# 1.每个表都有自已独立的表空间。
# 2.每个表的数据和索引都会存在自已的表空间中。
# 3.可以实现单表在不同的数据库中移动。
# 4.空间可以回收(除drop table操作处,表空不能自已回收)
# 缺点:
# 单表增加过大,如超过100G
# 结论:
# 共享表空间在Insert操作上少有优势。其它都没独立表空间表现好。当启用独立表空间时,请合理调整:innodb_open_files

innodb_open_files = 500
# 限制Innodb能打开的表的数据,如果库里的表特别多的情况,请增加这个。这个值默认是300

innodb_buffer_pool_size = 64M
# InnoDB使用一个缓冲池来保存索引和原始数据, 不像MyISAM.
# 这里你设置越大,你在存取表里面数据时所需要的磁盘I/O越少.
# 在一个独立使用的数据库服务器上,你可以设置这个变量到服务器物理内存大小的80%
# 不要设置过大,否则,由于物理内存的竞争可能导致操作系统的换页颠簸.
# 注意在32位系统上你每个进程可能被限制在 2-3.5G 用户层面内存限制,
# 所以不要设置的太高.

innodb_write_io_threads = 4
innodb_read_io_threads = 4
# innodb使用后台线程处理数据页上的读写 I/O(输入输出)请求,根据你的 CPU 核数来更改,默认是4
# 注:这两个参数不支持动态改变,需要把该参数加入到my.cnf里,修改完后重启MySQL服务,允许值的范围从 1-64

innodb_thread_concurrency = 0
# 默认设置为 0,表示不限制并发数,这里推荐设置为0,更好去发挥CPU多核处理能力,提高并发量

innodb_purge_threads = 1
# InnoDB中的清除操作是一类定期回收无用数据的操作。在之前的几个版本中,清除操作是主线程的一部分,这意味着运行时它可能会堵塞其它的数据库操作。
# 从MySQL5.5.X版本开始,该操作运行于独立的线程中,并支持更多的并发数。用户可通过设置innodb_purge_threads配置参数来选择清除操作是否使用单
# 独线程,默认情况下参数设置为0(不使用单独线程),设置为 1 时表示使用单独的清除线程。建议为1

innodb_flush_log_at_trx_commit = 2
# 0:如果innodb_flush_log_at_trx_commit的值为0,log buffer每秒就会被刷写日志文件到磁盘,提交事务的时候不做任何操作(执行是由mysql的master thread线程来执行的。
# 主线程中每秒会将重做日志缓冲写入磁盘的重做日志文件(REDO LOG)中。不论事务是否已经提交)默认的日志文件是ib_logfile0,ib_logfile1
# 1:当设为默认值1的时候,每次提交事务的时候,都会将log buffer刷写到日志。
# 2:如果设为2,每次提交事务都会写日志,但并不会执行刷的操作。每秒定时会刷到日志文件。要注意的是,并不能保证100%每秒一定都会刷到磁盘,这要取决于进程的调度。
# 每次事务提交的时候将数据写入事务日志,而这里的写入仅是调用了文件系统的写入操作,而文件系统是有 缓存的,所以这个写入并不能保证数据已经写入到物理磁盘
# 默认值1是为了保证完整的ACID。当然,你可以将这个配置项设为1以外的值来换取更高的性能,但是在系统崩溃的时候,你将会丢失1秒的数据。
# 设为0的话,mysqld进程崩溃的时候,就会丢失最后1秒的事务。设为2,只有在操作系统崩溃或者断电的时候才会丢失最后1秒的数据。InnoDB在做恢复的时候会忽略这个值。
# 总结
# 设为1当然是最安全的,但性能页是最差的(相对其他两个参数而言,但不是不能接受)。如果对数据一致性和完整性要求不高,完全可以设为2,如果只最求性能,例如高并发写的日志服务器,设为0来获得更高性能

innodb_log_buffer_size = 2M
# 此参数确定些日志文件所用的内存大小,以M为单位。缓冲区更大能提高性能,但意外的故障将会丢失数据。MySQL开发人员建议设置为1-8M之间

innodb_log_file_size = 32M
# 此参数确定数据日志文件的大小,更大的设置可以提高性能,但也会增加恢复故障数据库所需的时间

innodb_log_files_in_group = 3
# 为提高性能,MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3

innodb_max_dirty_pages_pct = 90
# innodb主线程刷新缓存池中的数据,使脏数据比例小于90%

innodb_lock_wait_timeout = 120 
# InnoDB事务在被回滚之前可以等待一个锁定的超时秒数。InnoDB在它自己的锁定表中自动检测事务死锁并且回滚事务。InnoDB用LOCK TABLES语句注意到锁定设置。默认值是50秒

bulk_insert_buffer_size = 8M
# 批量插入缓存大小, 这个参数是针对MyISAM存储引擎来说的。适用于在一次性插入100-1000+条记录时, 提高效率。默认值是8M。可以针对数据量的大小,翻倍增加。

myisam_sort_buffer_size = 8M
# MyISAM设置恢复表之时使用的缓冲区的尺寸,当在REPAIR TABLE或用CREATE INDEX创建索引或ALTER TABLE过程中排序 MyISAM索引分配的缓冲区

myisam_max_sort_file_size = 10G
# 如果临时文件会变得超过索引,不要使用快速排序索引方法来创建一个索引。注释:这个参数以字节的形式给出

myisam_repair_threads = 1
# 如果该值大于1,在Repair by sorting过程中并行创建MyISAM表索引(每个索引在自己的线程内) 

interactive_timeout = 28800
# 服务器关闭交互式连接前等待活动的秒数。交互式客户端定义为在mysql_real_connect()中使用CLIENT_INTERACTIVE选项的客户端。默认值:28800秒(8小时)

wait_timeout = 28800
# 服务器关闭非交互连接之前等待活动的秒数。在线程启动时,根据全局wait_timeout值或全局interactive_timeout值初始化会话wait_timeout值,
# 取决于客户端类型(由mysql_real_connect()的连接选项CLIENT_INTERACTIVE定义)。参数默认值:28800秒(8小时)
# MySQL服务器所支持的最大连接数是有上限的,因为每个连接的建立都会消耗内存,因此我们希望客户端在连接到MySQL Server处理完相应的操作后,
# 应该断开连接并释放占用的内存。如果你的MySQL Server有大量的闲置连接,他们不仅会白白消耗内存,而且如果连接一直在累加而不断开,
# 最终肯定会达到MySQL Server的连接上限数,这会报'too many connections'的错误。对于wait_timeout的值设定,应该根据系统的运行情况来判断。
# 在系统运行一段时间后,可以通过show processlist命令查看当前系统的连接状态,如果发现有大量的sleep状态的连接进程,则说明该参数设置的过大,
# 可以进行适当的调整小些。要同时设置interactive_timeout和wait_timeout才会生效。

[mysqldump]
quick
max_allowed_packet = 16M #服务器发送和接受的最大包长度

[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M

上述配置环境都可以通过环境变量查看:show variables like '%sql_mode%';
View Code



posted @ 2020-06-18 10:09  gtea  阅读(204)  评论(0编辑  收藏  举报