未加星标

高性能MySQL mysql mysql数据库 mysql函数 高性能MySQL

字体大小 | |
[数据库(mysql) 所属分类 数据库(mysql) | 发布者 店小二03 | 时间 2017 | 作者 红领巾 ] 0人收藏点击收藏

高性能 mysql

一、关于数据类型

选择的原则是:在满足使用的前提,要小而简单。

(1)整形比字符操作代价更低。

(2)保存日期格式时,要选用MySQL内置的日志格式(TimeStamp,DateTime),而不使用字符串。PS:TimeStamp使用4个字节进行存储(存储的时间从1970到2034),而DateTime使用8个字节存储(时间从1001到9999),上述两种时间格式推荐使用TimeStamp. 上述的两种日期格式的精度都只是到秒,如果要存储到毫秒或者更高的精度,可以考虑用BIGINT来保存。

(3)小数点的保存如何有涉及到运算,用float和double可能会精度不准,可以考虑将小数乘于相应的倍数,然后用BIGINT来存储。

(4)整数的几种类型

TINYINT、SMALLINT、MEDIUMINT、BIGINT分别使用8,16,32,64位。保存范围为-2^N到2^N-1(这里N=位数-1),如果加入UNSIGNED则保存的范围数(在正数范围内)提升一倍。

注意:对于INT(1)和INT(20)来说存储的空间和计算是一样的,因此指定位数是没有意义的。

(5)关于存储字符串的几种类型的选择

主要有CHAR, VARCHAR, BLOB, TEXT

关于CHAR和VARCHAR

其中CHAR(10)是定长为10(只需要10个字节)的,而VARCHAR(10)是变长的(不仅需要10个字节,还需要记录长度,如果长度小于255则额外需要1个字节,如果大于255则需要两个字节)。

在使用的时候,如果是定长字符推荐CHAR(N),比如定长的MD5加密的字符串。

CHAR的优势是经常更新不会产生太多的碎片。

对于VARCHAR中的变长个数如果定义的太长也会浪费存储空间的。比如VARCHAR(5)和VARCHAR(100)存储“hello”存储的空间都是一样的,但是更长的varchar的列会消耗更多的内存。

关于BLOB和TEXT

两个都是存储长的字符串,而BLOB存储的格式是二进制,TEXT是字符串。

上述的两种类型都有TINYBLOB,SMALLBLOB,BOLOB,MEDIUMBLOB,LONGBLOB,BLOB

以及TINYTEXT,SMALLTEXT,TEXT,MEDIUMTEXT,LONGTEXT

(6)关于保存ip地址

对于通常的ipv4,比如:192.168.1.123,我们会用15位char来存储这种是不推荐的,实际上ipv4是32位的无符号整数,推荐将ipv4转成整数进行保存。

在MySQL中提供了INET_ATON()将ipv4转成整形以及INET_NTOA()将整形转成ipv4

一个示例的用法如下:

create table tbl_ip(ipint unsigned, name char(1))

insert intotbl_ip(inet_aton(‘192.168.1.122’),’d’);

select * from tbl_ipwhere ip=inet_aton(‘192.168.1.122’);

select inet_ntoa(ip)from tbl_ip;

(7)默认值的缺省值的设置

尽量不用NULL,可以设置为“”或者0。

(8)在正式环境如何更改表的结构

方法一:创建影子表,当完成后,通过一个原子的重命名操作切换影子表和原表

MySQL语句的例子:

DROP IF EXISTStbl_channel_new, tbl_channel_old;

CREATE TABLEtbl_channel_new LIKE tbl_channel;(注意:这里只是复制了表的结果,其数据集仍然是空的)

…(执行修改操作)

RENAME TABLE tbl_channelTO tbl_channel_old, tbl_channel_new TO tbl_channel;

最后新表就是我们修改的表,并保存了一份之前的表,如果新表有问题可以快速回滚。

方法二:在更改表的时候可能会遇到表很大,而内存又不足,以及索引又很多,此时如果执行ALTER TABLE操作可能需要花费数个小时甚至数天的时间才能完成。

通过.frm文件的替换完成,但是实际实验中不成功。

方法三:通常表的修改应该在备机中执行ALTER并在完成后把它切换成主库。

(9)MySQL中的计数器

使用的用法:

UPDATE tbl_stat SET cnt=cnt+1;

上面的操作在计数的时候可以不用取得当前的值,而直接通过指定的列和要增加的值来计算统计结果。

在数据统计中的问题,对于某一行的计数由于会有事务,因此都会有一个全局的互斥锁,使得并发性能不高。

改进方法:

将计算器保存在多行中,每次随机选择一行进行更新,示例代码如下:

CREATE TABLEhit_counter(

slot tinyint unsigned not null primary key,

cnt int unsigned not null

)ENGINE=InnoDB;

然后预先在这张表上增加100行数据。随机选择一个槽(slot)进行更新:

UPDATE hit_counter SETcnt=cnt+1 WHERE slot=RAND()*100;

进一步改进,如果需要每隔一段时间开始一个新的计数器(比如:每天一个),则修改表为:

CREATE TABLEdaily_hit_counter(

day date not null,

slot tinyint unsigned not null,

cnt int unsigned not null,

primary key(date, slot)

)ENGINE=InnoDB;

在这个场景中,可以不用预先增加行,而用ON DUPLICATEKEY UPDATE代替(即如果不存在指定的主键KEY则插入,存在则执行更新操作),语句如下:

INSERT INTOdaily_hit_counter(day, slot, cnt) VALUES(CURRENT_DAY, RAND()*100, 1) ONDUPLICATE KEY UPDATE cnt=cnt+1;

二、如何使用索引

1、什么是索引,什么时候需要使用索引?

索引是通过额外的空间来保存你需要快速查询的一些字段,将这些字段按照一定的顺序存储并与原始数据进行映射,这样如果你要查询这些字段就无需去查询原表(可以肯定的是索引表的数据量肯定比原表小)

索引是提高查询性能的最佳途径。

但是索引也有它适用的范围,通常对于可以预计是小表的话,我们不会去见索引会浪费空间;对于中表或者大表,通过建立合适的索引能够极大地提高查询速度;当然对于超大表,不建议建立索引,会浪费太多的存储空间,会有其他第三方支持,补充一点:对于超大表的话可以考虑用分区表。

2、B-Tree索引的查询类型?

MySQL数据库中最常用的引擎是InnoDB(当然还有MyISAM这种引擎不怎么用),一般来说索引的类型指明都是用B-Tree索引,该索引的特点有如下:

(1)匹配最左前缀:即最左边的列是要先匹配,才能匹配剩余的列。

(2)全值匹配

(3)范围匹配:当用了范围匹配,在之后的列将不再用索引了

(4)精确匹配某一列,并范围匹配另外一列:即将2、3结合

(5)只访问索引查询:这个特性也就是我们说的覆盖索引

3、InnoDB中无哈希索引,如何利用哈希索引?

在InnoDB中是不存在哈希索引,那么哈希索引又如何用,其实并不能叫做哈希索引,只是用哈希特性,本质上还是利用B-Tree索引。

通常的用法是:针对某个较长的字符串,比如存储了URL,值为:http://www.baidu.com,那么我们要对URL进行建立索引,如果直接建立的话,索引占用的空间太大,可以通过哈希函数(比如:crc32)将其值映射到一个固定长度的数值,将该列的值添加表中,并为该列创建索引。这里面会有一个问题,那就是可能会存在重复的哈希值。解决的办法:

(1)如果能够容忍重复的情况下,在查询where条件中,除了附加索引的列的值,还要添加url的值,当重复的时候,就会用url的值来去重。

(2)另外一种方法,是通过64位的哈希函数来减少重复的概率。

补充一点:针对长文本建立索引的时候,我们也可以考虑取长文本的前缀建立索引,参考第5个问题。

4、那些情况下创建的索引列不起作用?

索引列不起作用的情况:

(1)将所有列使用函数等条件,比如:

SELECTactor_id FROM sakila.actor WHERE actor.id+ 1 = 5; //使用了加号

SELEC… WHERE TO_DAYS(CURRENT_DATE)-TO_DAYS(date_col)<=10;

(2)范围查询之后的索引列不起作用

范围查询主要是指< 、>,以及like,注意:对于in 会转化多个等式,因此仍然可以。

5、前缀索引如何创建及其特性,以及如何实现后缀索引?

创建前缀索引:

ALTER TABLEsakila.city_demo ADD KEY (city(7))

根据某个前缀利用group统计出个数:

SELECT COUNT(*) AS cnt,LEFT(city, 3) AS pref FROM sakila.city_demo GROUP BY pref ORDER BY cnt DESCLIMIT 10;

在一个查询中计算不同长度的前缀的选择性:

SELECT COUNT(DISTINCTLEFT(city, 3))/COUNT(*) AS sel3,COUNT(DISTINCT LEFT(city, 4))/COUNT(*) AS sel4FROM sakila.city_demo;

6、多列索引中列的排序如何选择?

对于B-Tree的多列索引,采用的是最左列排序,即在设计多列索引的情况,应该将选择性最高的放到索引的最前列(好处是能够快速定位到具体的行,不然会存在很多重复的行)

一般选择的法则:经验法则,示例如下:

比如针对两个列:staff_id和cunstom_id列的选择,通过以下方法:

SELECT COUNT(DISTINCTstaff_id)/COUNT(*) AS staff_id_selectivity, COUNT(DISTINCT custom_id)/COUNT(*)AS customer_id_selectivity,COUNT(*) FROM payment \G;

如果确认custom_id比staff_id的选择性更高,则添加如下多列索引:

ALTER TABLE payment ADDKEY(customer_id, staff_id);

7、什么是覆盖索引?如何使用覆盖索引来实现延迟关联呢?

覆盖索引:如果在某次查询中的字段覆盖了索引中的字段(即:一个索引包含或者说覆盖所有要查询的字段)

之所以要强调覆盖索引,是因为覆盖索引的条目数目通常远小于数据行大小,且索引是按列值顺序存储的(适合group以及相关范围查询)

一个使用延迟关联中使用了覆盖索引的例子:

比如:SELECT * FROM products WHEREactor=”SEAN CARRY” AND title like ‘%APOLO%’\G;

通过Explain计划看到,Extra:Using where说明没有使用到索引。

解决方法:添加多列索引(artist, title,pro_id)

SELECT *

FROM products

JOIN(

SELECT pro_id

FROM products

WHERE actor=’SEAN CARRY’AND title LIKE‘%APOLLO%’

)AS t1 on(t1.prod_id=products.prod_id)

分析上面的语句:

可以看到在JOIN中的列全部是我们建立的多列索引的列,因此可以利用覆盖索引,当我们覆盖索引查找到需要的pro_id后,再关联表查询便可减少数据量。

8、MySQL中有序结果集生成的方式有哪些?索引在其中起什么作用?

两种方式:(1)通过排序操作;(2)按索引排序(如果Explain中的type:index说明是按索引排序)

9、什么是冗余索引?

比如创建了一个多列索引(A,B),再创建一个单列索引(A),此时单列索引就是冗余索引,因为这只是第一个索引的前缀索引;如果创建一个单列索引(B),则不是。

在创建索引中对于相同的列,我们可能会按照不同的顺序来创建索引来满足不同的业务需求。

10、范围条件IN和>、<在多列索引中使用的区别?

IN在索引中会转化为多个等式,而<、>不可以。

11、索引导致的性能缺点有哪些?

索引的问题:

(1)占用硬盘,mysql需要的时候会调入内存,但是如果太大仍然会有问题。

(2)导致插入、更新等操作变慢

12、选择索引和编写利用索引的查询,需要记住的三个原则:

(1)单行访问是很慢的

(2)按顺序访问范围数据是很快的

(3)索引覆盖查询是很快的

本文数据库(mysql)相关术语:navicat for mysql mysql workbench mysql数据库 mysql 存储过程 mysql安装图解 mysql教程 mysql 管理工具

主题: MySQLSQLInnoDB数据硬盘TIDU数据库CUEDI
分页:12
转载请注明
本文标题:高性能MySQL mysql mysql数据库 mysql函数 高性能MySQL
本站链接:http://www.codesec.net/view/523171.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mysql) | 评论(0) | 阅读(107)