家庭电影网站建设,百度小程序注册流程,网站后台管理 源码,深圳公司注册地址一、MYSQL数据库设计规范1、数据库命名规范a、采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线’_组成;b、命名简洁明确(长度不能超过30个字符);c、例如#xff1a;user, stat, log, 也可以wifi_user, wifi_stat, wifi_log给数据库加个前缀;d、除非是备份数…一、MYSQL数据库设计规范1、数据库命名规范a、采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线’_组成;b、命名简洁明确(长度不能超过30个字符);c、例如user, stat, log, 也可以wifi_user, wifi_stat, wifi_log给数据库加个前缀;d、除非是备份数据库可以加0-9的自然数user_db_20151210;2、数据库表名命名规范a、采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线’‘组成;b、命名简洁明确,多个单词用下划线’分隔;例如user_login, user_profile, user_detail, user_role, user_role_relation,user_role_right, user_role_right_relation注表前缀’user_可以有效的把相同关系的表显示在一起;3、数据库表字段名命名规范a、采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线’‘组成;b、命名简洁明确,多个单词用下划线’分隔;例如user_login表字段 user_id, user_name, pass_word, eamil, tickit, status, mobile, add_time;c、每个表中必须有自增主键,add_time(默认系统时间)d、表与表之间的相关联字段名称要求尽可能的相同;4、数据库表字段类型规范用尽量少的存储空间来存数一个字段的数据;例如能使用int就不要使用varchar、char,能用varchar(16)就不要使用varchar(256);IP地址最好使用int类型;固定长度的类型最好使用char,例如邮编;能使用tinyint就不要使用smallint,int;最好给每个字段一个默认值,最好不能为null;5、数据库表索引规范命名简洁明确,例如user_login表user_name字段的索引应为user_name_index唯一索引;为每个表创建一个主键索引;为每个表创建合理的索引;建立复合索引请慎重;6、简单熟悉数据库范式1、第一范式(1NF)字段值具有原子性,不能再分(所有关系型数据库系统都满足第一范式);例如姓名字段,其中姓和名是一个整体,如果区分姓和名那么必须设立两个独立字段;2、第二范式(2NF)一个表必须有主键,即每行数据都能被唯一的区分;备注必须先满足第一范式;3、第三范式(3NF)一个表中不能包涵其他相关表中非关键字段的信息,即数据表不能有沉余字段;备注必须先满足第二范式;备注往往我们在设计表中不能遵守第三范式,因为合理的沉余字段将会给我们减少join的查询;例如相册表中会添加图片的点击数字段,在相册图片表中也会添加图片的点击数字段;二、MYSQL数据库设计原则1、核心原则不在数据库做运算;cpu计算务必移至业务层;控制列数量(字段少而精,字段数建议在20以内);平衡范式与冗余(效率优先往往牺牲范式)拒绝3B(拒绝大sql语句big sql、拒绝大事务big transaction、拒绝大批量big batch);2、字段类原则用好数值类型(用合适的字段类型节约空间);字符转化为数字(能转化的最好转化,同样节约空间、提高查询性能);避免使用NULL字段(NULL字段很难查询优化、NULL字段的索引需要额外空间、NULL字段的复合索引无效);少用text类型(尽量使用varchar代替text字段);3、索引类原则合理使用索引(改善查询,减慢更新,索引一定不是越多越好);字符字段必须建前缀索引;不在索引做列运算;innodb主键推荐使用自增列(主键建立聚簇索引,主键不应该被修改,字符串不应该做主键)(理解Innodb的索引保存结构就知道了);不用外键(由程序保证约束);4、sql类原则sql语句尽可能简单(一条sql只能在一个cpu运算,大语句拆小语句,减少锁时间,一条大sql可以堵死整个库);简单的事务;避免使用trig/func(触发器、函数不用客户端程序取而代之);不用select (消耗cpu,io,内存,带宽,这种程序不具有扩展性);OR改写为IN(or的效率是n级别);OR改写为UNION(mysql的索引合并很弱智);select id from t where phone ’159′ or name ‘john’;select id from t where phone’159′unionselect id from t where name’jonh’避免负向%;慎用count();limit高效分页(limit越大效率越低);使用union all替代union(union有去重开销);少用连接join;使用group by;请使用同类型比较;打散批量更新;三、数据库结构的优化1、选择合适的数据类型1、数据类型选择数据类型的选择重点在于“合适”二字如何确定选择的数据类型是否合适了1、使用可以存下你的数据的最小的数据类型。(时间类型数据可以使用varchar类型可以使用int类型也可以使用时间戳类型)2、使用简单的数据类型int要比varchar类型在mysql处理上简单。(int类型存储时间是最好的选择)3、尽可能的使用not null定义字段。(innodb的特性所决定非not null的值需要额外的在字段存储同时也会增加IO和存储的开销)4、尽量少用text类型非用不可时最好考虑分表。2、案例案例一int类型存储时间-时间转换使用int来存储日期时间利用FROM_UNIXTIME(),UNIX_TIMESTAMP()两个函数来进行转换。创建表create table test(id int auto_increment not null,timestr int ,primary key(id));导入数据insert into test (timestr) values (unix_timestamp(2018-05-29 16:00:00));查询数据如下图所示时间进行转换select FROM_UNIXTIME(timestr) from test;结论1、unix_timestamp()函数是将日期格式的数据转换为int类型2、FROM_UNIXTIME(timestr)函数是将int类型转换为时间格式案例二ip地址的存储在我们的外部应用中都要记录ip地址大部分场合都是varchar(15)进行存储就需要15个字节进行存储但是bigint只需要8个字节进行存储当数据量很大的时候(千万级别的数据)相差7个字节但是不能小看这7个字节给大家算一下。一个字段就多这么多那如果我们这样的字段需要上万个字段了是需要很多的存储空间的。使用bigint(8)来存储ip地址利用INET_ATON(),INET_NTOA()两个函数来进行转换。创建表create table sessions(id int auto_increment not null,ipaddress bigint,primary key (id));导入数据insert into sessions (ipaddress)values (inet_aton(192.168.0.1));转换select inet_ntoa(ipaddress) from sessions; 检索:2、数据库表的范式化优化1、表范式化范式化是指数据库设计的规范目前说道范式化一般是指第三设计范式。也就是要求数据表中不存在非关键字段对任意候选关键字段的传递函数依赖则符合第三范式。存在以下传递函数依赖关系(商品名称)-(分类)-(分类描述)也就是说存在非关键字段 “分类描述”对关键字段“商品名称”的传递函数依赖。不符合第三范式要求的表存在以下问题1、数据冗余(分类分类描述)对于每一个商品都会进行记录。2、数据的插入异常3、数据的更新异常4、数据的删除异常(删除所有数据分类和分类描述都会删除没有所有的记录)如何转换成符合第三范式的表(拆分表)将原来的不符合第三范式的表拆分为3个表商品表、分类表、分类和商品的关系表2、反范式化反范式化是指为了查询效率的考虑把原本符合第三范式的表“适当”的增加冗余以达到优化查询效率的目的反范式化是一种以空间来换取时间的操作。如何查询订单信息select b.用户名b.电话b.地址a.订单IDsum(c.商品价格*c.商品数量)as 订单价格from 订单表 as ajoin 用户表 as b on a.用户IDb.订单IDjoin 订单商品表 as c on c.订单IDb.订单IDgroup by b.用户名b.电话b.地址a.订单ID对于这样的表结构对于sum()group by会产生临时表增加IO量。我们怎么优化都效率不高那我们怎么样才能让它效率高了就需要一些字段进行冗余。订单表中增加了冗余字段那SQL该怎么写了select a.用户名a.电话a.地址a.订单ID,a.订单价格 from 订单表 as a说明表结构的设计直接涉及到SQL的查询效率及优化。3、数据库表的垂直拆分1、垂直拆分定义所谓的垂直拆分就是把原来一个有很多列的表拆分成多个表这解决了表的宽度问题。2、垂直拆分原则通常垂直拆分可以按以下原则进行1、把不常用的字段表单独存放到一个表中。2、把大字段独立存放到一个表中。3、把经常一起使用的字段放到一起。例子以film表为例在该表中title和description这两个字段占空间比较大况且在使用频率也比较低因此可以将其提取出来将上面的一个达标垂直拆分为两个表(film和film_ext)如下所示1、2、4、数据库表的水平拆分1、为什么水平拆分表的水平拆分是为了解决单表数据量过大的问题水平拆分的表每一个表的结构都是完全一致的以下面的peyment表为例来说明desc payment;show create table payment;CREATE TABLE payment ( payment_id smallint(5) unsigned NOT NULL AUTO_INCREMENT, customer_id smallint(5) unsigned NOT NULL, staff_id tinyint(3) unsigned NOT NULL, rental_id int(11) DEFAULT NULL, amount decimal(5,2) NOT NULL, payment_date datetime NOT NULL, last_update timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (payment_id), KEY idx_fk_staff_id (staff_id), KEY idx_fk_customer_id (customer_id), KEY fk_payment_rental (rental_id), KEY inx_paydate (payment_date), CONSTRAINT fk_payment_customer FOREIGN KEY (customer_id) REFERENCES customer (customer_id) ON UPDATE CASCADE, CONSTRAINT fk_payment_rental FOREIGN KEY (rental_id) REFERENCES rental (rental_id) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT fk_payment_staff FOREIGN KEY (staff_id) REFERENCES staff (staff_id) ON UPDATE CASCADE) ENGINEInnoDB AUTO_INCREMENT16050 DEFAULT CHARSETutf82、水平不拆分原因如果单表的数据量达到上亿条那么这时候我们尽管加了完美的索引查询效率低写入的效率也相应的降低。3、如何将数据平均分为N份通常水平拆分的方法为1、对customer_id进行hash运算如果要拆分为5个表则使用mod(customer_id5)取出0-4个值。2、针对不动的hashid把数据存储到不同的表中。4、水平拆分面临的挑战1、夸分区表进行数据查询前端业务统计业务上给不同的用户返回不同的业务信息对分区表没有大的挑战。2、统计及后台报表操作但是对后台进行报表统计时数据量比较大后台统计时效性比较低后台就用汇总表将前后台的表拆分开。四、数据库系统配置优化1、定义数据库是基于操作系统的目前大多数MySQL都是安装在linux系统之上所以对于操作系统的一些参数配置也会影响到MySQL的性能下面就列出一些常用的系统配置。2、优化配置参数-操作系统优化包括操作系统的优化及MySQL的优化1、操作系统的优化网络方面的配置要修改/etc/sysctl.conf1、增加tcp支持的队列数net.ipv4.tcp_max_syn_backlog 65535//2、减少断开连接时资源回收(tcp有连接状态)net.ipv4.tcp_max_tw_buckets 8000 //net.ipv4.tcp_tw_reuse 1net.ipv4.tcp_tw_recycle 1net.ipv4.tcp_fin_timeout 10说明 TCP是有连接状态通过netstat查看连接状态经常会看到timeout状态或者timewait状态连接为了加快timewait状态的连接回收就需要调整上面的四个参数保持TCP连接数在一个适当的状态。2、打开文件数的限制打开文件数的限制可以使用ulimit –a查看目录的各个限制可以修改/etc/security/limits.conf文件增加以下内容以修改打开文件数量的限制(永久生效)*Soft nofile 65535*Hard nofile 65535如果一次有效就要使用ulimit –n 65535即可。(默认情况是1024)除此之外最好在MySQL服务器上关闭iptablesselinux等防火墙软件。3、优化配置参数- MySQL配置文件优化1、MySQL配置文件修改Mysql可以通过启动时指定参数和使用配置文件两种方法进行配置在大多数情况下配置文件位于/etc/my.cnf 或者是 /etc/mysql/my.cnf在Windows系统配置文件可以是位于C://windows//my.ini文件MySQL查找配置文件的顺序可以通过以下方法获得。/usr/sbin/mysqld --verbose --help | grep -A 1 default options执行后的结果如下图所示注意如果存在多个位置存在配置文件则后面的会覆盖前面的。2、MySQL配置文件-常用参数说明1、连接请求的变量1、max_connectionsMySQL的最大连接数增加该值增加mysqld 要求的文件描述符的数量。如果服务器的并发连接请求量比较大建议调高此值以增加并行连接数量当然这建立在机器能支撑的情况下因为如果连接数越多介于MySQL会为每个连接提供连接缓冲区就会开销越多的内存所以要适当调整该值不能盲目提高设值。数值过小会经常出现ERROR 1040: Too many connections错误可以过’conn%’通配符查看当前状态的连接数量以定夺该值的大小。show variables like ‘max_connections’ 最大连接数show status like ‘max_used_connections’响应的连接数如下show variables like max_connections;show variables like max_used_connections;说明理想值设置为多大才合适了max_used_connections / max_connections * 100% (理想值≈ 85%)如果max_used_connections跟max_connections相同 那么就是max_connections设置过低或者超过服务器负载上限了低于10%则设置过大。2、back_logMySQL能暂存的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求这就起作用。如果MySQL的连接数据达到max_connections时新来的请求将会被存在堆栈中以等待某一连接释放资源该堆栈的数量即back_log如果等待连接的数量超过back_log将不被授予连接资源。back_log值指出在MySQL暂时停止回答新请求之前的短时间内有多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接你需要增加它换句话说这值对到来的TCP/IP连接的侦听队列的大小。当观察你主机进程列表(mysql show full processlist)发现大量264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时就要加大back_log 的值了。默认数值是50可调优为128对于Linux系统设置范围为小于512的整数。mysql show full processlist;±—±-----±----------±-------±--------±-----±------±----------------------| Id | User | Host | db | Command | Time | State | Info |±—±-----±----------±-------±--------±-----±------±----------------------| 54 | root | localhost | sakila | Query | 0 | init | show full processlist |±—±-----±----------±-------±--------±-----±------±----------------------1 row in set (0.00 sec)3、interactive_timeout一个交互连接在被服务器在关闭前等待行动的秒数。一个交互的客户被定义为对mysql_real_connect()使用CLIENT_INTERACTIVE 选项的客户。默认数值是28800可调优为7200。2、缓冲区变量1、全局缓冲1、key_buffer_sizekey_buffer_size指定索引缓冲区的大小它决定索引处理的速度尤其是索引读的速度。通过检查状态值Key_read_requests和Key_reads可以知道key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低至少是1:1001:1000更好(上述状态值可以使用SHOW STATUS LIKE ‘key_read%’获得)。key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表但是内部的临时磁盘表是MyISAM表也要使用该值。可以使用检查状态值created_tmp_disk_tables得知详情。举例如下show variables like key_buffer_size;key_buffer_size为512MB我们再看一下key_buffer_size的使用情况show global status like key_read%;一共有27813678764个索引读取请求有6798830个请求在内存中没有找到直接从硬盘读取索引计算索引未命中缓存的概率key_cache_miss_rate Key_reads / Key_read_requests * 100%设置在1/1000左右较好默认配置数值是8388600(8M)主机有4GB内存可以调优值268435456(256MB)。2、query_cache_size使用查询缓冲MySQL将查询结果存放在缓冲区中今后对于同样的SELECT语句(区分大小写)将直接从缓冲区中读取结果。通过检查状态值Qcache_*可以知道query_cache_size设置是否合理(上述状态值可以使用SHOW STATUS LIKE ‘Qcache%’获得)。如果Qcache_lowmem_prunes的值非常大则表明经常出现缓冲不够的情况如果Qcache_hits的值也非常大则表明查询缓冲使用非常频繁此时需要增加缓冲大小如果Qcache_hits的值不大则表明你的查询重复率很低这种情况下使用查询缓冲反而会影响效率那么可以考虑不用查询缓冲。此外在SELECT语句中加入SQL_NO_CACHE可以明确表示不使用查询缓冲。与查询缓冲有关的参数还有query_cache_type、query_cache_limit、query_cache_min_res_unit。query_cache_type指定是否使用查询缓冲可以设置为0、1、2该变量是SESSION级的变量。query_cache_limit指定单个查询能够使用的缓冲区大小缺省为1M。query_cache_min_res_unit是在4.1版本以后引入的它指定分配缓冲区空间的最小单位缺省为4K。检查状态值Qcache_free_blocks如果该值非常大则表明缓冲区中碎片很多这就表明查询结果都比较小此时需要减小query_cache_min_res_unit。举例如下show global status like qcache%;查询缓存碎片率 Qcache_free_blocks / Qcache_total_blocks * 100%如果查询缓存碎片率超过20%可以用FLUSH QUERY CACHE整理缓存碎片或者试试减小query_cache_min_res_unit如果你的查询都是小数据量的话。查询缓存利用率 (query_cache_size – Qcache_free_memory) / query_cache_size * 100%查询缓存利用率在25%以下的话说明query_cache_size设置的过大可适当减小查询缓存利用率在80以上而且Qcache_lowmem_prunes 50的话说明query_cache_size可能有点小要不就是碎片太多。查询缓存命中率 (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%示例服务器查询缓存碎片率20.46查询缓存利用率62.26查询缓存命中率1.94命中率很差可能写操作比较频繁吧而且可能有些碎片。3、record_buffer_size每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描你可能想要增加该值。默认数值是131072(128K)可改为16773120 (16M)4、read_rnd_buffer_size随机读缓冲区大小。当按任意顺序读取行时(例如按照排序顺序)将分配一个随机读缓存区。进行排序查询时MySQL会首先扫描一遍该缓冲以避免磁盘搜索提高查询速度如果需要排序大量数据可适当调高该值。但MySQL会为每个客户连接发放该缓冲空间所以应尽量适当设置该值以避免内存开销过大。一般可设置为16M5、sort_buffer_size每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY或GROUP BY操作。默认数值是2097144(2M)可改为16777208 (16M)。6、join_buffer_size联合查询操作所能使用的缓冲区大小。record_buffer_sizeread_rnd_buffer_sizesort_buffer_sizejoin_buffer_size为每个线程独占也就是说如果有100个线程连接则占用为16M*1007、table_cache表高速缓存的大小。每当MySQL访问一个表时如果在表缓冲区中还有空间该表就被打开并放入其中这样可以更快地访问表内容。通过检查峰值时间的状态值Open_tables和Opened_tables可以决定是否需要增加table_cache的值。如果你发现open_tables等于table_cache并且opened_tables在不断增长那么你就需要增加table_cache的值了(上述状态值可以使用SHOW STATUS LIKE ‘Open%tables’获得)。注意不能盲目地把table_cache设置成很大的值。如果设置得太高可能会造成文件描述符不足从而造成性能不稳定或者连接失败。1G内存机器推荐值是128256。内存在4GB左右的服务器该参数可设置为256M或384M。8、max_heap_table_size用户可以创建的内存表(memory table)的大小。这个值用来计算内存表的最大行数值。这个变量支持动态改变即set max_heap_table_size#这个变量和tmp_table_size一起限制了内部内存表的大小。如果某个内部heap(堆积)表大小超过tmp_table_sizeMySQL可以根据需要自动将内存中的heap表改为基于硬盘的MyISAM表。9、tmp_table_size通过设置tmp_table_size选项来增加一张临时表的大小例如做高级GROUP BY操作生成的临时表。如果调高该值MySQL同时将增加heap表的大小可达到提高联接查询速度的效果建议尽量优化查询要确保查询过程中生成的临时表在内存中避免临时表过大导致生成基于硬盘的MyISAM表。show global status like created_tmp%;每次创建临时表Created_tmp_tables增加如果临时表大小超过tmp_table_size则是在磁盘上创建临时表Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服务创建的临时文件文件数比较理想的配置是Created_tmp_disk_tables / Created_tmp_tables * 100% 25%比如上面的服务器Created_tmp_disk_tables / Created_tmp_tables * 100% 1.20%应该相当好了默认为16M可调到64-256最佳线程独占太大可能内存不够I/O堵塞10、thread_cache_size可以复用的保存在中的线程的数量。如果有新的线程从缓存中取得当断开连接的时候如果有空间客户的线置在缓存中。如果有很多新的线程为了提高性能可以这个变量值。通过比较 Connections和Threads_created状态的变量可以看到这个变量的作用。默认值为110可调优为80。11、thread_concurrency推荐设置为服务器 CPU核数的2倍例如双核的CPU, 那么thread_concurrency的应该为42个双核的cpu, thread_concurrency的值应为8。默认为812、wait_timeout指定一个请求的最大连接时间对于4GB左右内存的服务器可以设置为5-10。3、配置InnoDB的几个变量1、innodb_buffer_pool_size对于InnoDB表来说innodb_buffer_pool_size的作用就相当于key_buffer_size对于MyISAM表的作用一样。InnoDB使用该参数指定大小的内存来缓冲数据和索引。对于单独的MySQL数据库服务器最大可以把该值设置成物理内存的80%。根据MySQL手册对于2G内存的机器推荐值是1G(50%)。show status like ‘innodb%’;2、innodb_flush_log_at_trx_commit主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点取值分别为0、1、2三个。0表示当事务提交时不做日志写入操作而是每秒钟将log buffer中的数据写入日志文件并flush磁盘一次1则在每秒钟或是每次事物的提交都会引起日志文件写入、flush磁盘的操作确保了事务的ACID设置为2每次事务提交引起写入日志文件的动作但每秒钟完成一次flush磁盘操作。实际测试发现该值对插入数据的速度影响非常大设置为2时插入10000条记录只需要2秒设置为0时只需要1秒而设置为1时则需要229秒。因此MySQL手册也建议尽量将插入操作合并成一个事务这样可以大幅提高速度。根据MySQL手册在允许丢失最近部分事务的危险的前提下可以把该值设为0或2。3、innodb_log_buffer_sizelog缓存大小一般为1-8M默认为1M对于较大的事务可以增大缓存大小。可设置为4M或8M。4、innodb_additional_mem_pool_size该参数指定InnoDB用来存储数据字典和其他内部数据结构的内存池大小。缺省值是1M。通常不用太大只要够用就行应该与表结构的复杂度有关系。如果不够用MySQL会在错误日志中写入一条警告信息。根据MySQL手册对于2G内存的机器推荐值是20M可适当增加。innodb_thread_concurrency8推荐设置为 2*(NumCPUsNumDisks)默认一般为8[client]port 3306socket /tmp/mysql.sock[mysqld]port 3306socket /tmp/mysql.sockbasedir /usr/local/mysqldatadir /data/mysqlpid-file /data/mysql/mysql.piduser mysqlbind-address 0.0.0.0server-id 1 #表示是本机的序号为1,一般来讲就是master的意思5、skip-name-resolve# 禁止MySQL对外部连接进行DNS解析使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意如果开启该选项# 则所有远程主机连接授权都要使用IP地址方式否则MySQL将无法正常处理连接请求#skip-networkingback_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大小32Kmax_heap_table_size 8M# 定义了用户可以创建的内存表(memory table)的大小。这个值用来计算内存表的最大行数值。这个变量支持动态改变tmp_table_size 16M# MySQL的heap(堆积)表缓冲大小。所有联合在一个DML指令内完成并且大多数联合甚至可以不用临时表即可以完成。# 大多数临时表是基于内存的(HEAP)表。具有大的记录长度的临时表 (所有列的长度的和)或包含BLOB列的表存储在硬盘上。# 如果某个内部heap(堆积)表大小超过tmp_table_sizeMySQL可以根据需要自动将内存中的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 — 64query_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#指定单个查询能够使用的缓冲区大小默认1Mkey_buffer_size 4M#指定用于索引的缓冲区大小增加它可得到更好处理的索引(对所有读和多重写)到你能负担得起那样多。如果你使它太大# 系统将开始换页并且真的变慢了。对于内存在4GB左右的服务器该参数可设置为384M或512M。通过检查状态值Key_read_requests和Key_reads# 可以知道key_buffer_size设置是否合理。比例key_reads/key_read_requests应该尽可能的低# 至少是1:1001:1000更好(上述状态值可以使用SHOW STATUS LIKE ‘key_read%’获得)。注意该参数值设置的过大反而会是服务器整体效率降低ft_min_word_len 4# 分词词汇最小长度默认4transaction_isolation REPEATABLE-READ# MySQL支持4种事务隔离级别他们分别是# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE.# 如没有指定MySQL默认采用的是REPEATABLE-READORACLE默认的是READ-COMMITTEDlog_bin mysql-binbinlog_format mixedexpire_logs_days 30 #超过30天的binlog删除log_error /data/mysql/mysql-error.log #错误日志路径slow_query_log 1long_query_time 1 #慢查询时间 超过1秒则为慢查询slow_query_log_file /data/mysql/mysql-slow.logperformance_schema 0explicit_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_filesinnodb_open_files 500# 限制Innodb能打开的表的数据如果库里的表特别多的情况请增加这个。这个值默认是300innodb_buffer_pool_size 64M# InnoDB使用一个缓冲池来保存索引和原始数据, 不像MyISAM.# 这里你设置越大,你在存取表里面数据时所需要的磁盘I/O越少.# 在一个独立使用的数据库服务器上,你可以设置这个变量到服务器物理内存大小的80%# 不要设置过大,否则,由于物理内存的竞争可能导致操作系统的换页颠簸.# 注意在32位系统上你每个进程可能被限制在 2-3.5G 用户层面内存限制,# 所以不要设置的太高.innodb_write_io_threads 4innodb_read_io_threads 4# innodb使用后台线程处理数据页上的读写 I/O(输入输出)请求,根据你的 CPU 核数来更改,默认是4# 注:这两个参数不支持动态改变,需要把该参数加入到my.cnf里修改完后重启MySQL服务,允许值的范围从 1-64innodb_thread_concurrency 0# 默认设置为 0,表示不限制并发数这里推荐设置为0更好去发挥CPU多核处理能力提高并发量innodb_purge_threads 1# InnoDB中的清除操作是一类定期回收无用数据的操作。在之前的几个版本中清除操作是主线程的一部分这意味着运行时它可能会堵塞其它的数据库操作。# 从MySQL5.5.X版本开始该操作运行于独立的线程中,并支持更多的并发数。用户可通过设置innodb_purge_threads配置参数来选择清除操作是否使用单# 独线程,默认情况下参数设置为0(不使用单独线程),设置为 1 时表示使用单独的清除线程。建议为1innodb_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开发人员建议设置为18M之间innodb_log_file_size 32M# 此参数确定数据日志文件的大小更大的设置可以提高性能但也会增加恢复故障数据库所需的时间innodb_log_files_in_group 3# 为提高性能MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3innodb_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]quickmax_allowed_packet 16M #服务器发送和接受的最大包长度[myisamchk]key_buffer_size 8Msort_buffer_size 8Mread_buffer 4Mwrite_buffer 4M附录1、查看innodb的相关参数信息show variables like innodb%;2、查看innodb的相关参数状态show status like innodb%;五、MySQL的执行顺序MySQL的语句一共分为11步如下图所标注的那样最先执行的总是FROM操作最后执行的是LIMIT操作。其中每一个操作都会产生一张虚拟的表这个虚拟的表作为一个处理的输入只是这些虚拟的表对用户来说是透明的但是只有最后一个虚拟的表才会被作为结果返回。如果没有在语句中指定某一个子句那么将会跳过相应的步骤。下面我们来具体分析一下查询处理的每一个阶段1.FORM: 对FROM的左边的表和右边的表计算笛卡尔积。产生虚表VT12.ON: 对虚表VT1进行ON筛选只有那些符合的行才会被记录在虚表VT2中。3.JOIN 如果指定了OUTER JOIN(比如left join、 right join)那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中产生虚拟表VT3, rug from子句中包含两个以上的表的话那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤一直到处理完所有的表为止。4.WHERE 对虚拟表VT3进行WHERE条件过滤。只有符合的记录才会被插入到虚拟表VT4中。5.GROUP BY: 根据group by子句中的列对VT4中的记录进行分组操作产生VT5.6.CUBE | ROLLUP: 对表VT5进行cube或者rollup操作产生表VT6.7.HAVING 对虚拟表VT6应用having过滤只有符合的记录才会被 插入到虚拟表VT7中。六、MySQL执行引擎介绍(了解)1、MyISAM存储引擎不支持事务、也不支持外键优势是访问速度快对事务完整性没有 要求或者以selectinsert为主的应用基本上可以用这个引擎来创建表支持3种不同的存储格式分别是静态表动态表压缩表静态表表中的字段都是非变长字段这样每个记录都是固定长度的优点存储非常迅速容易缓存出现故障容易恢复缺点是占用的空间通常比动态表多(因为存储时会按照列的宽度定义补足空格)ps在取数据的时候默认会把字段后面的空格去掉如果不注意会把数据本身带的空格也会忽略。动态表记录不是固定长度的这样存储的优点是占用的空间相对较少缺点频繁的更新、删除数据容易产生碎片需要定期执行OPTIMIZE TABLE或者myisamchk-r命令来改善性能压缩表因为每个记录是被单独压缩的所以只有非常小的访问开支2、InnoDB存储引擎该存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM引擎写的处理效率会差一些并且会占用更多的磁盘空间以保留数据和索引。InnoDB存储引擎的特点支持自动增长列支持外键约束3、MEMORY存储引擎Memory存储引擎使用存在于内存中的内容来创建表。每个memory表只实际对应一个磁盘文件格式是.frm。memory类型的表访问非常的快因为它的数据是放在内存中的并且默认使用HASH索引但是一旦服务关闭表中的数据就会丢失掉。MEMORY存储引擎的表可以选择使用BTREE索引或者HASH索引两种不同类型的索引有其不同的使用范围Hash索引优点Hash 索引结构的特殊性其检索效率非常高索引的检索可以一次定位不像B-Tree 索引需要从根节点到枝节点最后才能访问到页节点这样多次的IO访问所以 Hash 索引的查询效率要远高于 B-Tree 索引。Hash索引缺点那么不精确查找呢也很明显因为hash算法是基于等值计算的所以对于“like”等范围查找hash索引无效不支持Memory类型的存储引擎主要用于哪些内容变化不频繁的代码表或者作为统计操作的中间结果表便于高效地对中间结果进行分析并得到最终的统计结果。对存储引擎为memory的表进行更新操作要谨慎因为数据并没有实际写入到磁盘中所以一定要对下次重新启动服务后如何获得这些修改后的数据有所考虑。4、MERGE存储引擎Merge存储引擎是一组MyISAM表的组合这些MyISAM表必须结构完全相同merge表本身并没有数据对merge类型的表可以进行查询更新删除操作这些操作实际上是对内部的MyISAM表进行的。
