转载自 https://blog.csdn.net/lemon89/article/details/50193891php
相关文章: html
深刻理解Mysql——高性能索引与高性能SQL主轴让磁盘盘片转动,而后传动手臂可伸展让读取头在盘片上进行读写操做。每一个盘片有两面,均可记录信息,因此一张盘片对应着两个磁头。mysql
磁盘物理结构以下图:算法
扇区:盘片被分为许多扇形的区域,每一个区域叫一个扇区,硬盘中每一个扇区的大小固定为512字节
磁道:盘片表面上以盘片中心为圆心,不一样半径的同心圆环称为磁道。sql
一个I/O请求所花费的时间=寻道时间+旋转延迟+数据传输时间(约10ms)数据库
当须要从磁盘读取数据时,系统会将数据逻辑地址传给磁盘,磁盘的控制电路按照寻址逻辑将逻辑地址翻译成物理地址,即肯定要读的数据在哪一个磁道,哪一个扇区。为了读取这个扇区的数据,须要将磁头放到这个扇区上方,为了实现这一点,磁头须要移动对准相应磁道,这个过程叫作寻道,所耗费时间叫作寻道时间,而后磁盘旋转将目标扇区旋转到磁头下,这个过程耗费的时间叫作旋转时间。segmentfault
将读写磁头移动至正确的磁道上所须要的时间。寻道时间越短,I/O操做越快,目前磁盘的平均寻道时间通常在3-15ms。缓存
指盘片旋转将请求数据所在的扇区移动到读写磁盘下方所须要的时间。旋转延迟取决于磁盘转速,一般用磁盘旋转一周所需时间的1/2表示。好比:7200rpm的磁盘平均旋转延迟大约为60*1000/7200/2 = 4.17ms,而转速为15000rpm的磁盘其平均旋转延迟为2ms。服务器
是指完成传输所请求的数据所须要的时间,它取决于数据传输率,其值等于数据大小除以数据传输率。数据传输时间一般远小于前两部分消耗时间。简单计算时可忽略。数据结构
当一次IO时,不光把当前磁盘地址的数据,而是把相邻的数据也都读取到内存缓冲区内,由于局部预读性原理告诉咱们,当计算机访问一个地址的数据的时候,与其相邻的数据也会很快被访问到,因此每次读取页的整数倍(一般一个节点就是一页)。每一次IO读取的数据咱们称之为一页(page)。
预读的长度通常为页(page)的整倍数。页是计算机管理存储器的逻辑块,硬件及操做系统每每将主存和磁盘存储区分割为连续的大小相等的块,每一个存储块称为一页(在许多操做系统中,页得大小一般为4k),主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时,会触发一个缺页异常,此时系统会向磁盘发出读盘信号,磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中,而后异常返回,程序继续运行。
连续读写性能很好,但随机读写性能不好
机械硬盘的连续读写性能很好,但随机读写性能不好,这主要是由于磁头移动到正确的磁道上须要时间,随机读写时,磁头须要不停的移动,时间都浪费在了磁头寻址上,因此性能不高。
IOPS
IOPS(Input/Output Per Second)即指每秒内系统能处理的I/O请求数量。
随机读写频繁的应用,如小文件存储等,关注随机读写性能,IOPS是关键衡量指标。
能够推算出磁盘的IOPS = 1000ms / (Tseek + Trotation + Transfer)
常见磁盘的随机读写最大IOPS为:
磁盘吞吐量
指单位时间内能够成功传输的数据数量。
磁盘阵列与服务器之间的数据通道对吞吐量影响很大。
顺序读写频繁的应用,如视频点播,关注连续读写性能、数据吞吐量是关键衡量指标。
索引是什么?
MySQL官方对索引的定义为:索引是帮助MySQL高效获取数据的数据结构。简而言之,索引是数据结构。
B+Tree,简单来讲就是一种为磁盘或者其余存储设备而设计的一种平衡二叉树,在B+tree中全部记录都按照key的大小存放在叶子结点上,各叶子结点直接用指针链接。
由二叉树,平衡二叉树,BTree演化而来。
二叉树 要保证父节点大于左子结点,小于右子节点。
平衡二叉树 在二叉树的基础上,还要保证任一结点的两个儿子字树高度差不大于1。
BTree 是一种自平衡二叉树,继承了上述平衡二叉树的特性,另外并保证了每一个叶子结点到根节点的距离相同。
BTree vs B+Tree:
B+树与BTree主要不一样就是data的存放位置,以及叶子结点的指针构成链表。
二叉树
平衡二叉树
BTree
B+Tree
能够在这个网站上查看动画进行操做Algorithms。
汇集索引:
InnfoBD引擎是索引组织表,全部数据都存放在汇集索引中。
准确来讲汇集索引并非某种单独的索引类型,而是一种数据存储方式。就是指在同一个结构中保存了B+tree索引以及数据行。InnoDB中一般主键就是一个汇集索引。
innoDB中,用户若是没有设置主键索引,会随机选择一个惟一的非空索引替代,
若是没有这样的索引,会隐式的定义一个主键做为隐式的汇集索引。
一般将主键设置为一个与业务无关的自增数字,这样能保证按照主键顺序插入数据,避免页分裂以及碎片问题。
主键索引的非叶子结点存放的是<.key,address.>,address就是指向下一层的指针。
主键索引的叶子结点保存了全部列的信息,所以经过主键索引能够快速获取数据。
辅助索引
(或称为非汇集索引、二级索引)
辅助索引的叶子结点并无存放数据,而是存放了主键值。
由于二级索引叶子页中存放了主键索引的值信息,若是主键索引很大的话,会致使全部索引都比较大。所以主键索引尽量要小
也就是说使用辅助索引查询,会经过叶子结点找到对应的主键,在主键索引中找到最终的数据。
1.使用索引能够大大减小服务器须要扫描的数据量。
2.使用索引能够帮助服务器避免排序或者临时表
3.索引是随机I\O变为 顺序I\O.
索引并非适用于任何状况。对于中型、大型表适用。对于小型表全表扫描更高效。而对于特大型表,考虑”分区”技术。
如下讲解使用以下表做为示例:
mysql> show create table people \G
*************************** 1. row ***************************
Table: people
Create Table: CREATE TABLE `people` (
`last_name` varchar(50) NOT NULL,
`first_name` varchar(50) NOT NULL,
`dob` date NOT NULL,
`gender` enum('m','f') NOT NULL,
KEY `last_name` (`last_name`,`first_name`,`dob`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
对于KEY last_name
(last_name
,first_name
,dob
),where 后的谓词必须包含last_name(组合索引的最左列),不然没法使用这个索引.
示例: select ... from people where .... last_name="..."....
这个语句将使用 last_name
(last_name
,first_name
,dob
)索引。
示例:
SELECT ... FROM people WHERE last_name="..." AND dob="..."
这个语句只使用了last_name
(last_name
,first_name
,dob
)的last_name列,由于缺乏first_name,因此后续dob列也没法从索引中搜索。
示例:
SELECT ... FROM people WHERE last_name > "..." AND first_name= "..." AND dob= "..."
这个语句只使用了last_name
(last_name
,first_name
,dob
)的last_name列,由于last_name 使用了范围查询,因此后续索引的两个列没法使用。
什么事范围查询:
使用了范围查询的语句。范围查询指使用 “>” 、”<”、“between” “like”的查询。注意“in”不算范围查询,属于多值查询条件。
列做为函数参数或表达式的一部分没法正常使用索引。
示例1: SELECT ... FROM people WHERE last_name+1 = "1001"
示例2: SELECT ... FROM people FORCE INDEX(last_name) WHERE LEFT(last_name,3) = "..."
以上示例均没法使用last_name
(last_name
,first_name
,dob
)索引。explain 展现位全表扫描。
什么是索引的选择性:
索引的基数(Cardinality) / 表的总记录数(#T) select count(Distinct columnName)/count(*) from Table
范围从 1#T ~ 1 ,值越高查询效率越高。惟一索引的选择性是:1.
注:索引的基数(Cardinality)不重复的索引值。此处计算的基数(Cardinality),与SHOW INDEX 语句中的Cardinality并不一致!SHOW INDEX 语句中的只是预估值。
通常状况,将选择性高的列放在左边,选择性高表明这个列的过滤性较好,尽量的尽快过滤掉无用的数据。
前缀索引
对于 较大的Varchar类型、Text类型、Blob类型,须要创建索引时必须使用前缀索引,由于mysql不容许索引完整大小,并且索引字段越大效率越差。
能够索引开始的部分字符串(取代所有),大大节约索引空间,提升索引效率。但这样会下降索引的选择性。
因此,对比较长的 (Varchar、Text、BLOB等等数据类型)列查询,要保证索引的选择性,又要不能太长以节省空间。因此“前缀”须要选的恰到好处:
“前缀索引”的基数应该接近完整的列索引的基数。
示例:前7个字符的前缀索引
mysql> select count(Distinct last_name)/count(*) from people ; +------------------------------------+ | count(Distinct last_name)/count(*) |
+------------------------------------+
| 0.7059 |
+------------------------------------+
1 row in set (0.07 sec)
------------------------------------------------------------
mysql> select count(Distinct left(last_name,5))/count(*), count(Distinct left(la st_name,6))/count(*) ,count(Distinct left(last_name,7))/count(*) from people \G *************************** 1. row *************************** count(Distinct left(last_name,5))/count(*): 0.6471
count(Distinct left(last_name,6))/count(*): 0.7059 count(Distinct left(last_name,7))/count(*): 0.7059
1 row in set (0.00 sec)
mysql> alter table people add key (last_name(6))
因此使用前6个字符便可达到完整字段的过滤性。
注意:
前缀索引是可以使索引更小,更快的方法,可是没法使用前缀索引作 Group By\Order By,也不能用前缀索引作覆盖查询(Using Index)。
除了使用前缀索引的方式处理这类大字段索引的状况,还有以下方式:
伪哈希索引
-- step1建表语句
CREATE TABLE `people` ( `id` BIGINT(20) NOT NULL AUTO_INCREMENT, `last_name` VARCHAR(50) COLLATE utf8_bin NOT NULL, `first_name` VARCHAR(50) COLLATE utf8_bin NOT NULL, `dob` DATE NOT NULL, `gender` ENUM('m','f') COLLATE utf8_bin NOT NULL, `blog_url` VARCHAR(128) COLLATE utf8_bin DEFAULT NULL, `crc32_url` BIGINT(20) DEFAULT NULL, PRIMARY KEY (`id`), KEY `last_name` (`last_name`,`first_name`,`dob`), KEY `crc32_url` (`crc32_url`) ) ENGINE=INNODB
由于blog_url是一个较长的字符串,因此直接将blog_url做为索引列会影响索引的总体效率。如今,尝试用一个伪hash值作一个伪hash索引。
-- step2 创建触发器,用于维护hash值
DELIMITER //
CREATE TRIGGER pseudohash_crc32_ins BEFORE INSERT ON people FOR EACH ROW BEGIN SET New.crc32_url = CRC32(New.blog_url);
END // CREATE TRIGGER pseudohash_crc32_upd BEFORE UPDATE ON people FOR EACH ROW BEGIN SET New.crc32_url = CRC32(New.blog_url);
END // DELIMITER;
效果以下,很是明显:
SELECT COUNT(1) FROM people ;
/** 636480 */
SELECT blog_url FROM people WHERE blog_url="http://www.-588141732.com" AND crc32_url=1790086969 /** sql执行时间: 执行耗时 : 0.004 sec 传送时间 : 0 sec 总耗时 : 0.004 sec explain: "id" "select_type" "table" "partitions" "type" "possible_keys" "key" "key_len" "ref" "rows" "filtered" "Extra" "1" "SIMPLE" "people" \N "ref" "crc32_url" "crc32_url" "9" "const" "1" "10.00" "Using where" */ EXPLAIN SELECT blog_url FROM people WHERE blog_url="http://www.-588141732.com" /** sql执行时间: 执行耗时 : 0.413 sec 传送时间 : 0 sec 总耗时 : 0.413 sec explain: "id" "select_type" "table" "partitions" "type" "possible_keys" "key" "key_len" "ref" "rows" "filtered" "Extra" "1" "SIMPLE" "people" \N "ALL" \N \N \N \N "630928" "10.00" "Using where" */
crc函数当数据量达到93000时,会产生1%的冲突。
若是要避免hash冲突的几率可使用MD5()截取的方式取代crc()。
如:
SELECT CONV(RIGHT(MD5("http://www.-aqq3feaeaff41732fff.com"),16),16,10) AS hashCode;
三星索引(关系型数据库索引设计及优化)
1.一星 使用where后的谓词列,按照选择性构造索引。
2.二星 若是语句中有排序操做,使用索引自带的顺序的排序(消除fileSort)。
3.三星 若是能够的话,将select后的还不在索引中的列名放到索引后边,能够覆盖索引(using index),而不须要读取表数据。
以下这个sql就是一个三星索引。SELECT last_name,first_name,dob FROM people WHERE last_name=”101899” AND first_name=”10189900” AND dob=”2017-08-03” ORDER BY dob
在实际应用中,没法保证三个星每一个星都知足。须要权衡取舍。
未使用的索引 也是累赘。建议删除。
关于explain的详细解释,请参考:
explain 详解
或者查看官网文档
explain-output
除了explain,还能够查看sql耗时分布状况。
Show Profiles \Show profile queryId
SHOW PROFILES;
/**
"Query_ID" "Duration" "Query"
...
"19" "0.00007350" "select ...."
"20" "0.00026150" "select state, round(sum(duration),5) ....."
...
*/
SHOW PROFILE FOR QUERY 24
/**
"Status" "Duration"
"Creating sort index" "0.748448"
"freeing items" "0.001355"
"starting" "0.000029"
"cleaning up" "0.000019"
"init" "0.000015"
"statistics" "0.000012"
"end" "0.000008"
"preparing" "0.000007"
"Opening tables" "0.000007"
"closing tables" "0.000006"
"Sending data" "0.000005"
"query end" "0.000005"
"optimizing" "0.000004"
"System lock" "0.000004"
"Sorting result" "0.000003"
"checking permissions" "0.000001"
"checking permissions" "0.000001"
"executing" "0.000001"
*/
Step1:客户端向Mysql服务器发送SQL语句。
使用”半双工”通讯方式,客户端或服务端在一个链接上同一时刻只容许一方进行数据传输,而且直到数据传输完成,另外一方才能执行传输。
当语句太长,超过 max_allowed_packet ,服务端会拒绝接收。
一般建议加上limit,能够减小没必要要的数据从服务端发送到客户端。
Step2:服务器收到后先查询”查询缓存“,若是命中,从缓存中直接返回sql执行的结果集。不然,进入Step3。
这个缓存经过一个对大小写敏感的hash算法实现,及时只有一个字节不匹配,那也没法命中。
Step3:服务器解析、预处理、优化sql执行计划,而后将处理好的sql放入查询的执行计划中。
在这个阶段,sql会被转换为一个执行计划,使用这个执行计划于具体的存储引擎进行交换。这个阶段包括,解析、预处理、优化sql执行计划这三个子任务。
Step4:执行引擎经过调用”存储引擎”(如,innodb、myisam等)提供的API去执行这个计划。
Step5:服务器返回结果给客户端
Step1:explain查看 (show profile能够查看耗时分布)
Step2:确认优化目标\方向,对于复杂sql须要理清执行步骤
目标1. type是否可以按照 const>eq_reg>ref>range>index>ALL的顺序优化,最差也要达到range级别。
目标2. 避免filesort的出现、避免rows数据量太大等负面字段、索引选择性是否足够、对于关联查询尽可能保证关联字段在第二张表上有可用索引(缘由:NestLoop)。
Step3:遵守SQL索引原则增长或调整SQL,常见以下(可参考上文去理解)
除了full Join,其余全部类型的查询SQL,都以相似的方式执行。
NestLoop (内嵌套循环)算法,简单来讲就是逐行查询处理,或者内嵌逐行查询。对于高版本的使用join buffer对上层表数据缓存,无需屡次遍历上层表,下层表直接使用(Block NestLoop)。
如下以两个示例详细说明执行计划,其余join以及单表查询原理也是相似的!
Join执行顺序伪代码演示
示例1:内关联inner join
SELECT people.id,user.id FROM user INNER JOIN people ON user.name = people.name WHERE user.enumType = 'orange' AND people.enumType = 'orange';
示例1对应伪代码:
//先扫描先执行的表,优化器一般选择关联的较小的表,explain第一行。
people_iterator=people_table.iterator();
//逐行遍历,而且丢弃where筛选不经过的行
while(people_iterator.hashNext()){
people_item=people_iterator.next();
if(people_item.enumType=='orange'){
//筛选经过后,在进入第二个嵌套
user_iterator=user_table.iterator()
//逐行遍历第二个表
while(user_iterator.hashNext()){
user_item=user_iterator.next();
//过滤:on 的条件匹配以及当前表的where条件
if(user_item.name==people_item.name&&user_item.enumType=='orange' ){
output(people.id,user.id);
}
}
}
}
示例2:非内关联
SELECT people.id,user.id FROM user LEFT JOIN people ON user.name = people.name WHERE user.enumType = 'orange' AND people.enumType = 'orange';
示例2伪代码
//先扫描先执行的表,优化器一般选择关联的较小的表,explain第一行。
people_iterator=people_table.iterator();
//逐行遍历,而且丢弃where筛选不经过的行
while(people_iterator.hashNext()){
people_item=people_iterator.next();
if(people_item.enumType=='orange'){
//筛选经过后,在进入第二个嵌套
user_iterator=user_table.iterator()
//逐行遍历第二个表
while(user_iterator.hashNext()){
user_item=user_iterator.next();
//过滤:on 的条件匹配以及当前表的where条件
if(user_item.name==people_item.name&&user_item.enumType=='orange' ){
output(people.id,user.id);
}
//与innerjoin不一样的,leftJoin须要即便on条件不成立,也要保留左边数据
else if(!is_innerJoin){
output(null,user.id);//保留左边数据
}
}
}
}
注意:尽可能保证关联字段在第二张表上有可用索引。
(由于第一张表示全表扫描,而后会对第二张表用关联字段查询,详情请看NestLoop理解关联过程)
1.一般状况下,使用一个性能好的sql去作更多的事情,而不是使用多个sql。
除非这个sql过长效率低下或者对于delete这种语句,过长的delete会致使太多的数据被锁定,耗尽资源,阻塞其余sql。
2.分解关联查询。
将关联(** join……)放在应用中处理,执行小而简单的sql,好处是:
关于Count()
count()函数有两种含义:统计行数、统计列数。
好比:count(*)表明统计的行数;count(talbe.cloumn)表明统计的是这个列不为null的数量。
关于Limit
在使用Limit 1000,20这种操做的时候,mysql会扫描偏移量(1000条无效查询)数据,而只取后20条,尽可能避免这种写法,想办法规避。
关于Union
须要将where、order by、limit 这些限制放入到每一个子查询,才能重分提高效率。另外如非必须,尽可能使用Union all,由于union会给每一个子查询的临时表加入distinct,对每一个临时表作惟一性检查,效率较差。
/*1.查看索引 (1)单位是GB*/
SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024*1024), 6), ' GB') AS 'Total Index Size' FROM information_schema.TABLES WHERE table_schema LIKE 'database';
/* +------------------+ | Total Index Size | +------------------+ | 1.70 GB | +------------------+ */
/* (2)单位是MB */
SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 6), ' MB') AS 'Total Index Size' FROM information_schema.TABLES WHERE table_schema LIKE 'database';
/* 其中“database”为你所要查看的数据库 */
/* 2.查看表空间 */
SELECT CONCAT(ROUND(SUM(data_length)/(1024*1024*1024), 6), ' GB') AS 'Total Data Size' FROM information_schema.TABLES WHERE table_schema LIKE 'database';
/* +-----------------+ | Total Data Size | +-----------------+ | 3.01 GB | +-----------------+ */
/* 3.查看数据库中全部表的信息 */
SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name', table_rows AS 'Number of Rows', CONCAT(ROUND(data_length/(1024*1024*1024),6),' G') AS 'Data Size', CONCAT(ROUND(index_length/(1024*1024*1024),6),' G') AS 'Index Size' , CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),6),' G') AS'Total' FROM information_schema.TABLES WHERE table_schema LIKE 'database';
关于SQL执行顺序可参考:
https://www.2cto.com/database/201512/453280.html
https://huoding.com/2013/06/04/261其他推荐文章:
http://blog.codinglabs.org/articles/index-condition-pushdown.html
http://www.noobyard.com/article/p-rkedevie-bd.html
https://tech.meituan.com/mysql-index.html
https://tech.meituan.com/about-desk-io.html
http://www.orczhou.com/index.php/2013/04/how-mysql-choose-index-in-a-join/推荐书籍:
Mysql内核——innoDB存储引擎;
高性能mysql(https://dev.mysql.com/doc/index-other.html);