澳门新萄京官方网站-www.8455.com-澳门新萄京赌场网址

澳门新萄京官方网站:目录优化剖析,浅谈MySQ

2019-09-16 作者:www.8455.com   |   浏览(180)

  mysql的数据库的数据库,即存储mysql数据库的底层目录,是在/var/lib/mysql目录下(Linux,win在目录下的data中)。

mysql开发必知必会,mysql开发必知

  mysql的数据库的数据库,即存储mysql数据库的底层目录,是在/var/lib/mysql目录下(Linux,win在目录下的data中)。

澳门新萄京官方网站 1

  我们新创建的数据库db1就是在/var/lib/mysql目录下创建了一个文件夹db1。

  mysql相关的目录

澳门新萄京官方网站 2

  我们使用/usr/bin/mysqladmin创建过root用户的密码等操作。

  mysql默认的配置文件读取顺序为:

[[email protected] bin]# which mysqld
/usr/sbin/mysqld
[[email protected] bin]# ^C
[[email protected] bin]# /usr/sbin/mysqld --verbose --help |grep -A 1 'Default options'
2018-03-07 17:30:00 0 [Note] /usr/sbin/mysqld (mysqld 5.6.39) starting as process 14259 ...
2018-03-07 17:30:00 14259 [Note] Plugin 'FEDERATED' is disabled.
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 
#mysql的配置信息的读取目录顺序
2018-03-07 17:30:00 14259 [Note] Binlog end
2018-03-07 17:30:00 14259 [Note] Shutting down plugin 'CSV'
2018-03-07 17:30:00 14259 [Note] Shutting down plugin 'MyISAM'

  查看mysql的字符集

mysql> show variables like 'character%';
 -------------------------- ---------------------------- 
| Variable_name            | Value                      |
 -------------------------- ---------------------------- 
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
 -------------------------- ---------------------------- 
8 rows in set (0.00 sec)

mysql> show variables like '%char%';
 -------------------------- ---------------------------- 
| Variable_name            | Value                      |
 -------------------------- ---------------------------- 
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
 -------------------------- ---------------------------- 
8 rows in set (0.00 sec)

  此时数据库的字符集为拉丁,我们需要改为utf8。

  修改mysql的默认配置文件/etc/my.cnf。

[mysql]  
# 设置mysql客户端默认字符集  
default-character-set=utf8   
socket=/var/lib/mysql/mysql.sock  

[mysqld]  
skip-name-resolve  
#设置3306端口  
port = 3306   
socket=/var/lib/mysql/mysql.sock  
# 设置mysql的安装目录  
basedir=/usr/local/mysql  
# 设置mysql数据库的数据的存放目录  
datadir=/usr/local/mysql/data  
# 允许最大连接数  
max_connections=200  
# 服务端使用的字符集默认为8比特编码的latin1字符集  
character-set-server=utf8  
# 创建新表时将使用的默认存储引擎  
default-storage-engine=INNODB  
lower_case_table_name=1  
max_allowed_packet=16M  

  关闭mysql并重新开启:

[[email protected] mysql-community-server-5.6.39]# service mysqld stop
Stopping mysqld (via systemctl):                           [  确定  ]
[[email protected] mysql-community-server-5.6.39]# service mysqld start
Starting mysqld (via systemctl):                           [  确定  ]
[[email protected] mysql-community-server-5.6.39]# mysql -uroot -p
Enter password: 

  新建数据库db2显示db1与db2的数据库的字符编码:

mysql> create database db2;
Query OK, 1 row affected (0.04 sec)

mysql> use db2;
Database changed
mysql> show variables like '%char%';
 -------------------------- ---------------------------- 
| Variable_name            | Value                      |
 -------------------------- ---------------------------- 
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
 -------------------------- ---------------------------- 
8 rows in set (0.00 sec)

mysql> use db1;
Database changed
mysql> show variables like '%char%';
 -------------------------- ---------------------------- 
| Variable_name            | Value                      |
 -------------------------- ---------------------------- 
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
 -------------------------- ---------------------------- 
8 rows in set (0.00 sec)

  之前创建的数据库的字符编码仍为Latin,而修改过后的新建数据库已经使用utf8了,说明我们的配置生效了。

  查看mysql的安装目录

[[email protected] mysql-community-server-5.6.39]# vim /etc/my.cnf
[[email protected] mysql-community-server-5.6.39]# ps -ef|grep mysql
root      14599      1  0 18:44 ?        00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql
mysql     14941  14599  0 18:44 ?        00:00:02 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock --port=3306
root      15008  11441  0 18:54 pts/0    00:00:00 grep --color=auto mysql

  mysql主要的配置文件

  二进制日志log-bin  用于主从复制;

  错误日志log-error 默认关闭,记录严重警告和错误信息。

  查询日志log 默认关闭,用来查询sql语句,开启会降低mysql整体性能

  数据文件:前面提到的数据库的数据库文件,frm文件用于存放表结构,myd文件存放表数据,myi文件用于存放表索引。

  配置文件:Linux为/etc/my.cnf,win为my.ini。

  mysql架构介绍

澳门新萄京官方网站 3

  第一层为连接层,connectors。

  接下来就进入mysql层:

  第二层为一部分是与外层的连接池(并发部分),备份安全集群等部份,然后到对输入sql语句进行解析的部分(sqlinterface接口对你要进行是curd的某项操作进行分开解析),解析到optimizer部分为mysql自己的逻辑操作部分(人进行数据查询首先关注select部分,但是机器关心的是从from哪张表开始,这个部分是mysql的优化器),最后一部分是缓存与缓冲,解决数据库存取时的io操作问题,所以这一层主要是业务逻辑处理层。

  第三层为可拔插的存储引擎部分。有很多种,我们经常使用到的是innodb有时也用myisam。

  第四层就是底层的文件存储层,底层的文件系统等。

  与其他数据库相比,mysql的架构可以在多种不同场景应用并发挥良好作用主要体现在存储引擎的架构上,可插拔的存储引擎架构将查询处理和其他的系统任务以及数据的存储提取相分离,我们可以根据业务的实际需求选择合适的存储引擎。

澳门新萄京官方网站 4

  查看存储引擎:

mysql> show engines;
 -------------------- --------- ---------------------------------------------------------------- -------------- ------ ------------ 
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
 -------------------- --------- ---------------------------------------------------------------- -------------- ------ ------------ 
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
 -------------------- --------- ---------------------------------------------------------------- -------------- ------ ------------ 
9 rows in set (0.00 sec)

  主流的两种存储引擎的对比:

澳门新萄京官方网站 5

  关于mysql性能下降的分析

  如果mysql的性能下降,sql慢,执行时间长,等待时间长,那么问题可能出现在:

  1.查询语句写得太烂了

  2.索引失效了

  3.关联查询的join写的太多了(可能是表的设计的问题)

  4.服务器调优及各个参数的设置问题(缓冲及线程数等)

  你写的sql:

澳门新萄京官方网站 6

  mysql都出来的查询语句

澳门新萄京官方网站 7

  机读的顺序

澳门新萄京官方网站 8

   mysql索引

  定义:索引是帮助mysql高效获取数据的数据结构。我们可以根据索引的特点理解为,索引就是排好序的快速查找的数据结构。

  索引影响的部分是where后面的查找与orderby后面的排序。

  除了数据,数据库系统还维护了一个满足特定查找算法的数据结构,这些数据结构以某种方式指向数据,这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。

澳门新萄京官方网站 9

  为了加快col2的查找,可以维护一个二叉树,每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针,这样就可以运用二叉查找在一定复杂度内获取到相应数据,从而快速的检索出符合条件的记录。

  一般来说,索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储在磁盘上。

  我们一般说的索引都是b树结构组织索引,其中聚集索引,次要索引,覆盖索引,复合索引,前缀索引,唯一索引都是使用b树索引。除了b树索引意外还有hash索引。

  索引的优势:

  查找时提高数据检索效率,降低数据库io成本。排序时,索引对数据进行排序,降低数据排序成本,降低了CPU的消耗。

  索引的劣势:

  索引本身是一张表,保存了主键与索引字段,并指向实体表的记录,所以索引本身也占空间。

  索引虽然大大加快了查询速度,却降低了更新速度,在更新表时,不仅要存更新的数据,还要存索引文件每次更新添加的索引列字段,都会调整因为更=新带来的简直变化后的索引信息。

  索引只是提高效率的一个因素,如果你的mysql有大量数据的表,那么就要花时间研究建立最优秀的索引或优化查询语句。

  mysql索引分类

  单值索引:一个索引只包含单个列,一个表可以有多个单列索引。

  唯一索引:索引列的值必须唯一,但允许空值。

  复合索引:一个索引包含多个列。

  b树查找

澳门新萄京官方网站 10

  这里磁盘块Ⅰ存储了指引搜索方向的数据项,17与35不一定存在于数据表中,只是一个趋势,叶子节点存的数据必须是真实存在的数据。根节点的数据表示,查找的数据范围是小于17还是位于17和35之间还是大于35的数据。然后从根出发一直找到索引的数据,只需要三步即可。

  那些情况需要创建索引

  1.主键自动建立唯一索引

  2.频繁作为查找条件的字段应该创建索引

  3.查询中与其他表关联的字段,外键关系建立索引

  4.频繁更新的字段不适合创建索引,不只是更新记录还会更新索引,加重io负担

  5.where条件里用不到的字段不创建索引

  6.单键/组合索引的选择问题,在高并发下倾向创建组合索引

  7.查询中排序的字段,排序字段通过索引访问将大大提高排序速度

  8.查询中统计或者分组字段

  哪些情况不要创建索引

  1.表记录太少

  2.经常增删改的表

  3.数据重复且分布平均的表的字段。比如性别列。

  Explain

  除了mysql的optimizer自动的优化导致的sql性能下降和cache与buffer的硬件部分性能瓶颈,我们分析一条sql语句是否高效就必须用到Explain进行查询。

  explain关键字可以模拟优化sql查询语句,从而知道mysql是如何处理你的sql语句的,可以用来分析你的查询语句或是表结构的性能瓶颈。

  能查看什么:

  表的读取顺序,数据读取操作的操作类型,那些索引可以使用,那些索引被实际使用,表之间的引用,每张表有多少行被优化器查询。

  执行方法

explain sql

   explain中的字段

  id字段

  此字段用来表示mysql查询时执行select子句或操作表的顺序,即表的读取顺序。

  查询结果可能有三种情况

  1.id相同时。

澳门新萄京官方网站 11

  即此时mysql加载的顺序为t1>t3>t2。

  2.id不同

澳门新萄京官方网站 12

  即此时mysql加载的顺序为t3>t1>t2。

  3.混合存在

澳门新萄京官方网站 13

  即此时mysql加载的顺序为t3><derived2>>t2。

  select_type字段

  即数据读取操作的操作类型

  1.simple

  简单的select查询,查询不包括子查询与union

  2.primary

  如果查询包含了任何复杂的字部分,最外层查询被标记为primary

  3.subquery

  在select或者where列表中包含了子查询

  4.derived

  在from列表包含的子查询被标记为derived衍生,mysql会递归执行这些子查询,把结果放到临时表中

  5.union

  若第二个select出现在union后则被标记为union,若union包含在from子句的子查询中,外层select将被标记为derived。

  6.union result

  从union表获取结果的select

  table字段,就是表名字段,表时这一行数据是关于那张表的。

  type字段

  直接的显示了表是否被优化是否最佳状态的直接体现。

  一般常见的类型为system>const>eq_ref>ref>range>index>all

  当你的数据达到百万级并且sql语句执行为all,那么你的查询语句或者说表需要进行优化了。当你的sql执行为ref或者range就已经很优了。

  type的不同状态:

  1.system

  表只有一行记录(等于系统表),是const类型的特例,平时基本遇不到可以忽略不计

  2.const常量

  表通过索引一次就找到了,const用于比较primary key或者unique索引,因为只匹配一行数据,所以很快

澳门新萄京官方网站 14

  这里的id为主键索引,在t1表唯一只匹配一行数据,查找相当于查询一个常量。

  一张表只有一行数据,相当于京东就你一个用户,快是快,但是实际基本不会用到。

  3.eq_ref

  唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配,常见于主键或唯一索引扫描

澳门新萄京官方网站 15

  类比员工表与部门表中,部门表id最为外键,员工表的外键关联到部门表id,将部门表id建成索引,ceo作为公司员工,在部门表索引时只能查到一个唯一的数据与之匹配,这种情况就是eq_ref,反之其他的部门拥有多名员工,查询就是ref。

  4.ref字段

  非唯一索引扫描,匹配某个单独值的所有行,可能找到符合条件的多个值,所以它是查找和扫描的混合体。

  类比上述的表查找所有的程序员就是ref类型。

澳门新萄京官方网站 16

  5.range

  只检索给定范围行,使用一个索引来选择行,key列显示你使用了哪个索引,一般是where语句出现between,<,>,in等的查询,这种范围扫描优于全标搜索,因为他有起始位置。

澳门新萄京官方网站 17

  6.index

  全索引扫描,优于all因为只扫描了索引,索引往往小于数据的大小。

澳门新萄京官方网站 18

  7.all

  全表查询,匹配到所有行

澳门新萄京官方网站 19

  possible_keys与key字段

  possible_key是smysql判断你可能使用的索引,显示可能应用在这张表的索引,一个或多个,查询涉及到的字段上若存在索引,该索引将被列出,但不一定被查询实际使用

  key字段

  实际中使用的索引,如果为null则没有使用索引

澳门新萄京官方网站 20

  这种情况为查询中使用了覆盖索引,该索引与查询的select重叠。

  key_length字段

澳门新萄京官方网站:目录优化剖析,浅谈MySQL索引优化深入分析。  条件越精确,key_length越长,此字段为索引字段最大可能长度,并非实际使用长度,根据定义计算得出,不是通过表内检索得来的值。表示索引中使用的字节数,可通过该列计算查询中使用的索引长度。澳门新萄京官方网站 21

  ref字段

  显示索引的那一列被使用了,如果可能的话是一个常数,那些列或常量被用于查找索引列上的值。

澳门新萄京官方网站 22

  即ref体现了实际被索引的列。

  rows字段

  根据表中信息,估算找到所需记录大致需要读取的行数。

澳门新萄京官方网站 23

  extra

  包含的额外信息,也是重要字段

  1.using filesort

  说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。

澳门新萄京官方网站 24

  这里我们对表中的三列进行了联合索引,前者在orderby排序时使用了外部排序,这大大降低了表的排序性能,因为在使用索引排序时最好是按照它的索引顺序进行排序。

  2.using temporary

  使用了临时表保存中间结果,mysql在对查询结果排序时使用了临时表,常见于排序orderby和分组查询groupby。

澳门新萄京官方网站 25

  在这张表中,创建col1与col2联合索引,groupby跳过col1直接groupby col2,导致断层,需要创建临时的表,mysql对临时表进行一次自己的排序。

  3.using index

澳门新萄京官方网站 26

  所以这是个好字段,使用覆盖索引。

澳门新萄京官方网站 27

  还有一些其他字段不很重要的字段,就不一一解释了,有兴趣自行了解。

澳门新萄京官方网站 28

澳门新萄京官方网站 29

   范围后的索引会失效

澳门新萄京官方网站 30

  左右连接查表的时候给另一边加索引。

  多表连接查询时的优化:

  1.减少join查询的循环总次数,即永远以小结果集驱动大结果集。

  2.优先优化nestedloop的内层循环。

  3.保证join语句中被驱动表join字段已经被索引

  4.无法保证被驱动表的join条件字段被索引且内存资源充足的前提下,不要吝惜joinbuffer的设置。

mysql的数据库的数据库,即存储mysql数据库的底层目录,是在/var/lib/mysql目录下(Linux,win在目录下的data中...

浅谈MySQL索引优化分析,浅谈mysql索引

为什么你写的sql查询慢?为什么你建的索引常失效?通过本章内容,你将学会MySQL性能下降的原因,索引的简介,索引创建的原则,explain命令的使用,以及explain输出字段的意义。助你了解索引,分析索引,使用索引,从而写出更高性能的sql语句。还在等啥子?撸起袖子就是干!

案例分析

我们先简单了解一下非关系型数据库和关系型数据库的区别。

MongoDB是NoSQL中的一种。NoSQL的全称是Not only SQL,非关系型数据库。它的特点是性能高,扩张性强,模式灵活,在高并发场景表现得尤为突出。但目前它还只是关系型数据库的补充,它在数据的一致性,数据的安全性,查询的复杂性问题上和关系型数据库还存在一定差距。

MySQL是关系性数据库中的一种,查询功能强,数据一致性高,数据安全性高,支持二级索引。但性能方面稍逊与MongoDB,特别是百万级别以上的数据,很容易出现查询慢的现象。这时候需要分析查询慢的原因,一般情况下是程序员sql写的烂,或者是没有键索引,或者是索引失效等原因导致的。

 公司ERP系统数据库主要是MongoDB(最接近关系型数据的NoSQL),其次是Redis,MySQL只占很少的部分。现在又重新使用MySQL,归功于阿里巴巴的奇门系统和聚石塔系统。考虑到订单数量已经是百万级以上,对MySQL的性能分析也就显得格外重要。

我们先通过两个简单的例子来入门。后面会详细介绍各个参数的作用和意义。

说明:需要用到的sql已经放在了github上了,喜欢的同学可以点一下star,哈哈。

场景一:订单导入,通过交易号避免重复导单

业务逻辑:订单导入时,为了避免重复导单,一般会通过交易号去数据库中查询,判断该订单是否已经存在。

最基础的sql语句

mysql> select * from itdragon_order_list where transaction_id = "81X97310V32236260E";
 ------- -------------------- ------- ------ ---------- -------------- ---------- ------------------ ------------- ------------- ------------ --------------------- 
| id  | transaction_id   | gross | net | stock_id | order_status | descript | finance_descript | create_type | order_level | input_user | input_date     |
 ------- -------------------- ------- ------ ---------- -------------- ---------- ------------------ ------------- ------------- ------------ --------------------- 
| 10000 | 81X97310V32236260E |  6.6 | 6.13 |    1 |      10 | ok    | ok        | auto    |      1 | itdragon  | 2017-08-18 17:01:49 |
 ------- -------------------- ------- ------ ---------- -------------- ---------- ------------------ ------------- ------------- ------------ --------------------- 

mysql> explain select * from itdragon_order_list where transaction_id = "81X97310V32236260E";
 ---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- 
| id | select_type | table        | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra    |
 ---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- 
| 1 | SIMPLE   | itdragon_order_list | NULL    | ALL | NULL     | NULL | NULL  | NULL |  3 |  33.33 | Using where |
 ---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- 

查询的本身没有任何问题,在线下的测试环境也没有任何问题。可是,功能一旦上线,查询慢的问题就迎面而来。几百上千万的订单,用全表扫描?啊?哼!

怎么知道该sql是全表扫描呢?通过explain命令可以清楚MySQL是如何处理sql语句的。打印的内容分别表示:

  1. id : 查询序列号为1。
  2. select_type : 查询类型是简单查询,简单的select语句没有union和子查询。
  3. table : 表是 itdragon_order_list。
  4. partitions : 没有分区。
  5. type : 连接类型,all表示采用全表扫描的方式。
  6. possible_keys : 可能用到索引为null。
  7. key : 实际用到索引是null。
  8. key_len : 索引长度当然也是null。
  9. ref : 没有哪个列或者参数和key一起被使用。
  10. Extra : 使用了where查询。

 因为数据库中只有三条数据,所以rows和filtered的信息作用不大。这里需要重点了解的是type为ALL,全表扫描的性能是最差的,假设数据库中有几百万条数据,在没有索引的帮助下会异常卡顿。

初步优化:为transaction_id创建索引

mysql> create unique index idx_order_transaID on itdragon_order_list (transaction_id);
mysql> explain select * from itdragon_order_list where transaction_id = "81X97310V32236260E";
 ---- ------------- --------------------- ------------ ------- -------------------- -------------------- --------- ------- ------ ---------- ------- 
| id | select_type | table        | partitions | type | possible_keys   | key        | key_len | ref  | rows | filtered | Extra |
 ---- ------------- --------------------- ------------ ------- -------------------- -------------------- --------- ------- ------ ---------- ------- 
| 1 | SIMPLE   | itdragon_order_list | NULL    | const | idx_order_transaID | idx_order_transaID | 453   | const |  1 |   100 | NULL |
 ---- ------------- --------------------- ------------ ------- -------------------- -------------------- --------- ------- ------ ---------- ------- 

这里创建的索引是唯一索引,而非普通索引。

唯一索引打印的type值是const。表示通过索引一次就可以找到。即找到值就结束扫描返回查询结果。

普通索引打印的type值是ref。表示非唯一性索引扫描。找到值还要继续扫描,直到将索引文件扫描完为止。(这里没有贴出代码)
显而易见,const的性能要远高于ref。并且根据业务逻辑来判断,创建唯一索引是合情合理的。

再次优化:覆盖索引

mysql> explain select transaction_id from itdragon_order_list where transaction_id = "81X97310V32236260E";
 ---- ------------- --------------------- ------------ ------- -------------------- -------------------- --------- ------- ------ ---------- ------------- 
| id | select_type | table        | partitions | type | possible_keys   | key        | key_len | ref  | rows | filtered | Extra    |
 ---- ------------- --------------------- ------------ ------- -------------------- -------------------- --------- ------- ------ ---------- ------------- 
| 1 | SIMPLE   | itdragon_order_list | NULL    | const | idx_order_transaID | idx_order_transaID | 453   | const |  1 |   100 | Using index |
 ---- ------------- --------------------- ------------ ------- -------------------- -------------------- --------- ------- ------ ---------- ------------- 

这里将select * from 改为了 select transaction_id from 后 Extra 显示 Using index,表示该查询使用了覆盖索引,这是一个非常好的消息,说明该sql语句的性能很好。若提示的是Using filesort(使用内部排序)和Using temporary(使用临时表)则表明该sql需要立即优化了。

根据业务逻辑来的,查询结构返回transaction_id 是可以满足业务逻辑要求的。

场景二,订单管理页面,通过订单级别和订单录入时间排序

业务逻辑:优先处理订单级别高,录入时间长的订单。
 既然是排序,首先想到的应该是order by, 还有一个可怕的 Using filesort 等着你。

最基础的sql语句

mysql> explain select * from itdragon_order_list order by order_level,input_date;
 ---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ---------------- 
| id | select_type | table        | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra     |
 ---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ---------------- 
| 1 | SIMPLE   | itdragon_order_list | NULL    | ALL | NULL     | NULL | NULL  | NULL |  3 |   100 | Using filesort |
 ---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ---------------- 

首先,采用全表扫描就不合理,还使用了文件排序Using filesort,更加拖慢了性能。

MySQL在4.1版本之前文件排序是采用双路排序的算法,由于两次扫描磁盘,I/O耗时太长。后优化成单路排序算法。其本质就是用空间换时间,但如果数据量太大,buffer的空间不足,会导致多次I/O的情况。其效果反而更差。与其找运维同事修改MySQL配置,还不如自己乖乖地建索引。

澳门新萄京官方网站:目录优化剖析,浅谈MySQL索引优化深入分析。初步优化:为order_level,input_date 创建复合索引

mysql> create index idx_order_levelDate on itdragon_order_list (order_level,input_date);
mysql> explain select * from itdragon_order_list order by order_level,input_date;
 ---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ---------------- 
| id | select_type | table        | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra     |
 ---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ---------------- 
| 1 | SIMPLE   | itdragon_order_list | NULL    | ALL | NULL     | NULL | NULL  | NULL |  3 |   100 | Using filesort |
 ---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ---------------- 

创建复合索引后你会惊奇的发现,和没创建索引一样???都是全表扫描,都用到了文件排序。是索引失效?还是索引创建失败?我们试着看看下面打印情况

mysql> explain select order_level,input_date from itdragon_order_list order by order_level,input_date;
 ---- ------------- --------------------- ------------ ------- --------------- --------------------- --------- ------ ------ ---------- ------------- 
| id | select_type | table        | partitions | type | possible_keys | key         | key_len | ref | rows | filtered | Extra    |
 ---- ------------- --------------------- ------------ ------- --------------- --------------------- --------- ------ ------ ---------- ------------- 
| 1 | SIMPLE   | itdragon_order_list | NULL    | index | NULL     | idx_order_levelDate | 68   | NULL |  3 |   100 | Using index |
 ---- ------------- --------------------- ------------ ------- --------------- --------------------- --------- ------ ------ ---------- ------------- 

将select * from 换成了 select order_level,input_date from 后。type从all升级为index,表示(full index scan)全索引文件扫描,Extra也显示使用了覆盖索引。可是不对啊!!!!检索虽然快了,但返回的内容只有order_level和input_date 两个字段,让业务同事怎么用?难道把每个字段都建一个复合索引?

MySQL没有这么笨,可以使用force index 强制指定索引。在原来的sql语句上修改 force index(idx_order_levelDate) 即可。

mysql> explain select * from itdragon_order_list force index(idx_order_levelDate) order by order_level,input_date;
 ---- ------------- --------------------- ------------ ------- --------------- --------------------- --------- ------ ------ ---------- ------- 
| id | select_type | table        | partitions | type | possible_keys | key         | key_len | ref | rows | filtered | Extra |
 ---- ------------- --------------------- ------------ ------- --------------- --------------------- --------- ------ ------ ---------- ------- 
| 1 | SIMPLE   | itdragon_order_list | NULL    | index | NULL     | idx_order_levelDate | 68   | NULL |  3 |   100 | NULL |
 ---- ------------- --------------------- ------------ ------- --------------- --------------------- --------- ------ ------ ---------- ------- 

再次优化:订单级别真的要排序么?

其实给订单级别排序意义并不大,给订单级别添加索引意义也不大。因为order_level的值可能只有,低,中,高,加急,这四种。对于这种重复且分布平均的字段,排序和加索引的作用不大。

我们能否先固定 order_level 的值,然后再给 input_date 排序?如果查询效果明显,是可以推荐业务同事使用该查询方式。

mysql> explain select * from itdragon_order_list where order_level=3 order by input_date;
 ---- ------------- --------------------- ------------ ------ --------------------- --------------------- --------- ------- ------ ---------- ----------------------- 
| id | select_type | table        | partitions | type | possible_keys    | key         | key_len | ref  | rows | filtered | Extra         |
 ---- ------------- --------------------- ------------ ------ --------------------- --------------------- --------- ------- ------ ---------- ----------------------- 
| 1 | SIMPLE   | itdragon_order_list | NULL    | ref | idx_order_levelDate | idx_order_levelDate | 5    | const |  1 |   100 | Using index condition |
 ---- ------------- --------------------- ------------ ------ --------------------- --------------------- --------- ------- ------ ---------- ----------------------- 

和之前的sql比起来,type从index 升级为 ref(非唯一性索引扫描)。索引的长度从68变成了5,说明只用了一个索引。ref也是一个常量。Extra 为Using index condition 表示自动根据临界值,选择索引扫描还是全表扫描。总的来说性能远胜于之前的sql。

上面两个案例只是快速入门,我们需严记一点:优化是基于业务逻辑来的。绝对不能为了优化而擅自修改业务逻辑。如果能修改当然是最好的。

索引简介

官方定义:索引(Index) 是帮助MySQL高效获取数据的数据结构。

大家一定很好奇,索引为什么是一种数据结构,它又是怎么提高查询的速度?我们拿最常用的二叉树来分析索引的工作原理。

看下面的图片:

澳门新萄京官方网站 31

创建索引的优势

1 提高数据的检索速度,降低数据库IO成本:使用索引的意义就是通过缩小表中需要查询的记录的数目从而加快搜索的速度。

2 降低数据排序的成本,降低CPU消耗:索引之所以查的快,是因为先将数据排好序,若该字段正好需要排序,则真好降低了排序的成本。

创建索引的劣势

1 占用存储空间:索引实际上也是一张表,记录了主键与索引字段,一般以索引文件的形式存储在磁盘上。

2 降低更新表的速度:表的数据发生了变化,对应的索引也需要一起变更,从而减低的更新速度。否则索引指向的物理数据可能不对,这也是索引失效的原因之一。

3 优质索引创建难:索引的创建并非一日之功,也并非一直不变。需要频繁根据用户的行为和具体的业务逻辑去创建最佳的索引。

索引分类

我们常说的索引一般指的是BTree(多路搜索树)结构组织的索引。其中还有聚合索引,次要索引,复合索引,前缀索引,唯一索引,统称索引,当然除了B 树外,还有哈希索引(hash index)等。

  1. 单值索引:一个索引只包含单个列,一个表可以有多个单列索引
  2. 唯一索引:索引列的值必须唯一,但允许有空值
  3. 复合索引:一个索引包含多个列,实际开发中推荐使用

实际开发中推荐使用复合索引,并且单表创建的索引个数建议不要超过五个

基本语法:

创建:

create [unique] index indexName on tableName (columnName...)
alter tableName add [unique] index [indexName] on (columnName...)

删除:

drop index [indexName] on tableName

查看:

show index from tableName

哪些情况需要建索引:

1 主键,唯一索引
2 经常用作查询条件的字段需要创建索引
3 经常需要排序、分组和统计的字段需要建立索引
4 查询中与其他表关联的字段,外键关系建立索引

哪些情况不要建索引:

1 表的记录太少,百万级以下的数据不需要创建索引
2 经常增删改的表不需要创建索引
3 数据重复且分布平均的字段不需要创建索引,如 true,false 之类。
4 频发更新的字段不适合创建索引
5 where条件里用不到的字段不需要创建索引

性能分析

MySQL 自身瓶颈

MySQL自身参见的性能问题有磁盘空间不足,磁盘I/O太大,服务器硬件性能低。
1 CPU:CPU 在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
2 IO:磁盘I/O 瓶颈发生在装入数据远大于内存容量的时候
3 服务器硬件的性能瓶颈:top,free,iostat 和 vmstat来查看系统的性能状态

explain 分析sql语句

使用explain关键字可以模拟优化器执行sql查询语句,从而得知MySQL 是如何处理sql语句。

 ---- ------------- ------- ------------ ------ --------------- ----- --------- ------ ------ ---------- ------- 
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
 ---- ------------- ------- ------------ ------ --------------- ----- --------- ------ ------ ---------- ------- 

id

select 查询的序列号,包含一组可以重复的数字,表示查询中执行sql语句的顺序。一般有三种情况:
 第一种:id全部相同,sql的执行顺序是由上至下;
 第二种:id全部不同,sql的执行顺序是根据id大的优先执行;
 第三种:id既存在相同,又存在不同的。先根据id大的优先执行,再根据相同id从上至下的执行。

select_type

select 查询的类型,主要是用于区别普通查询,联合查询,嵌套的复杂查询
simple:简单的select 查询,查询中不包含子查询或者union
primary:查询中若包含任何复杂的子查询,最外层查询则被标记为primary
subquery:在select或where 列表中包含了子查询
derived:在from列表中包含的子查询被标记为derived(衍生)MySQL会递归执行这些子查询,把结果放在临时表里。
union:若第二个select出现在union之后,则被标记为union,若union包含在from子句的子查询中,外层select将被标记为:derived
union result:从union表获取结果的select

partitions

表所使用的分区,如果要统计十年公司订单的金额,可以把数据分为十个区,每一年代表一个区。这样可以大大的提高查询效率。

type

这是一个非常重要的参数,连接类型,常见的有:all , index , range , ref , eq_ref , const , system , null 八个级别。
 性能从最优到最差的排序:system > const > eq_ref > ref > range > index > all
对java程序员来说,若保证查询至少达到range级别或者最好能达到ref则算是一个优秀而又负责的程序员。
all:(full table scan)全表扫描无疑是最差,若是百万千万级数据量,全表扫描会非常慢。
index:(full index scan)全索引文件扫描比all好很多,毕竟从索引树中找数据,比从全表中找数据要快。
range:只检索给定范围的行,使用索引来匹配行。范围缩小了,当然比全表扫描和全索引文件扫描要快。sql语句中一般会有between,in,>,< 等查询。
ref:非唯一性索引扫描,本质上也是一种索引访问,返回所有匹配某个单独值的行。比如查询公司所有属于研发团队的同事,匹配的结果是多个并非唯一值。
eq_ref:唯一性索引扫描,对于每个索引键,表中有一条记录与之匹配。比如查询公司的CEO,匹配的结果只可能是一条记录,
const:表示通过索引一次就可以找到,const用于比较primary key 或者unique索引。因为只匹配一行数据,所以很快,若将主键至于where列表中,MySQL就能将该查询转换为一个常量。
system:表只有一条记录(等于系统表),这是const类型的特列,平时不会出现,了解即可

possible_keys

显示查询语句可能用到的索引(一个或多个或为null),不一定被查询实际使用。仅供参考使用。

key

显示查询语句实际使用的索引。若为null,则表示没有使用索引。

key_len

显示索引中使用的字节数,可通过key_len计算查询中使用的索引长度。在不损失精确性的情况下索引长度越短越好。key_len 显示的值为索引字段的最可能长度,并非实际使用长度,即key_len是根据表定义计算而得,并不是通过表内检索出的。

ref

显示索引的哪一列或常量被用于查找索引列上的值。

rows

根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数,值越大越不好。

extra

Using filesort: 说明MySQL会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序” 。出现这个就要立刻优化sql。
Using temporary: 使用了临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和 分组查询 group by。 出现这个更要立刻优化sql。
Using index: 表示相应的select 操作中使用了覆盖索引(Covering index),避免访问了表的数据行,效果不错!如果同时出现Using where,表明索引被用来执行索引键值的查找。如果没有同时出现Using where,表示索引用来读取数据而非执行查找动作。
 覆盖索引(Covering Index) :也叫索引覆盖,就是select 的数据列只用从索引中就能够取得,不必读取数据行,MySQL可以利用索引返回select 列表中的字段,而不必根据索引再次读取数据文件。
Using index condition: 在5.6版本后加入的新特性,优化器会在索引存在的情况下,通过符合RANGE范围的条数 和 总数的比例来选择是使用索引还是进行全表遍历。
Using where: 表明使用了where 过滤
Using join buffer: 表明使用了连接缓存
impossible where: where 语句的值总是false,不可用,不能用来获取任何元素
distinct: 优化distinct操作,在找到第一匹配的元组后即停止找同样值的动作。

filtered

一个百分比的值,和rows 列的值一起使用,可以估计出查询执行计划(QEP)中的前一个表的结果集,从而确定join操作的循环次数。小表驱动大表,减轻连接的次数。

通过explain的参数介绍,我们可以得知:
1 表的读取顺序(id)
 2 数据读取操作的操作类型(type)
 3 哪些索引被实际使用(key)
 4 表之间的引用(ref)
 5 每张表有多少行被优化器查询(rows)

性能下降的原因

从程序员的角度
1 查询语句写的不好
2 没建索引,索引建的不合理或索引失效
3 关联查询有太多的join

从服务器的角度
1 服务器磁盘空间不足
2 服务器调优配置参数设置不合理

总结

1 索引是排好序且快速查找的数据结构。其目的是为了提高查询的效率。
2 创建索引后,查询数据变快,但更新数据变慢。
3 性能下降的原因很可能是索引失效导致。
4 索引创建的原则,经常查询的字段适合创建索引,频繁需要更新的数据不适合创建索引。
5 索引字段频繁更新,或者表数据物理删除容易造成索引失效。
6 擅用 explain 分析sql语句
7 除了优化sql语句外,还可以优化表的设计。如尽量做成单表查询,减少表之间的关联。设计归档表等。

到这里,MySQL的索引优化分析就结束了,有什么不对的地方,大家可以提出来。如果觉得不错可以点一下推荐。

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持帮客之家。

为什么你写的sql查询慢?为什么你建的索引常失效?通过本章内容,你将学会MySQL性能下降的原因,索...

为什么你写的sql查询慢?为什么你建的索引常失效?通过本章内容,你将学会MySQL性能下降的原因,索引的简介,索引创建的原则,explain命令的使用,以及explain输出字段的意义。助你了解索引,分析索引,使用索引,从而写出更高性能的sql语句。还在等啥子?撸起袖子就是干!

MySQL索引优化分析

为什么你写的sql查询慢?为什么你建的索引常失效?通过本章内容,你将学会MySQL性能下降的原因,索引的简介,索引创建的原则,explain命令的使用,以及explain输出字段的意义。助你了解索引,分析索引,使用索引,从而写出更高性能的sql语句。还在等啥子?撸起袖子就是干!

澳门新萄京官方网站 32

案例分析

案例分析

我们先简单了解一下非关系型数据库关系型数据库的区别。
MongoDB是NoSQL中的一种。NoSQL的全称是Not only SQL,非关系型数据库。它的特点是性能高扩张性强模式灵活,在高并发场景表现得尤为突出。但目前它还只是关系型数据库的补充,它在数据的一致性,数据的安全性,查询的复杂性问题上和关系型数据库还存在一定差距。
MySQL是关系性数据库中的一种,查询功能强数据一致性高数据安全性高支持二级索引。但性能方面稍逊与MongoDB,特别是百万级别以上的数据,很容易出现查询慢的现象。这时候需要分析查询慢的原因,一般情况下是程序员sql写的烂,或者是没有键索引,或者是索引失效等原因导致的。
公司ERP系统数据库主要是MongoDB(最接近关系型数据的NoSQL),其次是Redis,MySQL只占很少的部分。现在又重新使用MySQL,归功于阿里巴巴的奇门系统和聚石塔系统。考虑到订单数量已经是百万级以上,对MySQL的性能分析也就显得格外重要。

我们先通过两个简单的例子来入门。后面会详细介绍各个参数的作用和意义。
说明:需要用到的sql已经放在了github上了,喜欢的同学可以点一下star,哈哈。

  我们新创建的数据库db1就是在/var/lib/mysql目录下创建了一个文件夹db1。

我们先简单了解一下非关系型数据库和关系型数据库的区别。

场景一:订单导入,通过交易号避免重复导单

业务逻辑:订单导入时,为了避免重复导单,一般会通过交易号去数据库中查询,判断该订单是否已经存在。

  mysql相关的目录

MongoDB是NoSQL中的一种。NoSQL的全称是Not only SQL,非关系型数据库。它的特点是性能高,扩张性强,模式灵活,在高并发场景表现得尤为突出。但目前它还只是关系型数据库的补充,它在数据的一致性,数据的安全性,查询的复杂性问题上和关系型数据库还存在一定差距。

最基础的sql语句

mysql> select * from itdragon_order_list where transaction_id = "81X97310V32236260E";
 ------- -------------------- ------- ------ ---------- -------------- ---------- ------------------ ------------- ------------- ------------ --------------------- 
| id    | transaction_id     | gross | net  | stock_id | order_status | descript | finance_descript | create_type | order_level | input_user | input_date          |
 ------- -------------------- ------- ------ ---------- -------------- ---------- ------------------ ------------- ------------- ------------ --------------------- 
| 10000 | 81X97310V32236260E |   6.6 | 6.13 |        1 |           10 | ok       | ok               | auto        |           1 | itdragon   | 2017-08-18 17:01:49 |
 ------- -------------------- ------- ------ ---------- -------------- ---------- ------------------ ------------- ------------- ------------ --------------------- 

mysql> explain select * from itdragon_order_list where transaction_id = "81X97310V32236260E";
 ---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- 
| id | select_type | table               | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
 ---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- 
|  1 | SIMPLE      | itdragon_order_list | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
 ---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- 

查询的本身没有任何问题,在线下的测试环境也没有任何问题。可是,功能一旦上线,查询慢的问题就迎面而来。几百上千万的订单,用全表扫描?啊?哼!
怎么知道该sql是全表扫描呢?通过explain命令可以清楚MySQL是如何处理sql语句的。打印的内容分别表示:
id : 查询序列号为1。
select_type : 查询类型是简单查询,简单的select语句没有union和子查询。
table : 表是 itdragon_order_list。
partitions : 没有分区。
type : 连接类型,all表示采用全表扫描的方式。
possible_keys : 可能用到索引为null。
key : 实际用到索引是null。
key_len : 索引长度当然也是null。
ref : 没有哪个列或者参数和key一起被使用。
Extra : 使用了where查询。
因为数据库中只有三条数据,所以rows和filtered的信息作用不大。这里需要重点了解的是type为ALL,全表扫描的性能是最差的,假设数据库中有几百万条数据,在没有索引的帮助下会异常卡顿。

澳门新萄京官方网站 33

MySQL是关系性数据库中的一种,查询功能强,数据一致性高,数据安全性高,支持二级索引。但性能方面稍逊与MongoDB,特别是百万级别以上的数据,很容易出现查询慢的现象。这时候需要分析查询慢的原因,一般情况下是程序员sql写的烂,或者是没有键索引,或者是索引失效等原因导致的。

初步优化:为transaction_id创建索引

mysql> create unique index idx_order_transaID on itdragon_order_list (transaction_id);
mysql> explain select * from itdragon_order_list where transaction_id = "81X97310V32236260E";
 ---- ------------- --------------------- ------------ ------- -------------------- -------------------- --------- ------- ------ ---------- ------- 
| id | select_type | table               | partitions | type  | possible_keys      | key                | key_len | ref   | rows | filtered | Extra |
 ---- ------------- --------------------- ------------ ------- -------------------- -------------------- --------- ------- ------ ---------- ------- 
|  1 | SIMPLE      | itdragon_order_list | NULL       | const | idx_order_transaID | idx_order_transaID | 453     | const |    1 |      100 | NULL  |
 ---- ------------- --------------------- ------------ ------- -------------------- -------------------- --------- ------- ------ ---------- ------- 

这里创建的索引是唯一索引,而非普通索引。
唯一索引打印的type值是const。表示通过索引一次就可以找到。即找到值就结束扫描返回查询结果。
普通索引打印的type值是ref。表示非唯一性索引扫描。找到值还要继续扫描,直到将索引文件扫描完为止。(这里没有贴出代码)
显而易见,const的性能要远高于ref。并且根据业务逻辑来判断,创建唯一索引是合情合理的。

  我们使用/usr/bin/mysqladmin创建过root用户的密码等操作。

 公司ERP系统数据库主要是MongoDB(最接近关系型数据的NoSQL),其次是Redis,MySQL只占很少的部分。现在又重新使用MySQL,归功于阿里巴巴的奇门系统和聚石塔系统。考虑到订单数量已经是百万级以上,对MySQL的性能分析也就显得格外重要。

再次优化:覆盖索引

mysql> explain select transaction_id from itdragon_order_list where transaction_id = "81X97310V32236260E";
 ---- ------------- --------------------- ------------ ------- -------------------- -------------------- --------- ------- ------ ---------- ------------- 
| id | select_type | table               | partitions | type  | possible_keys      | key                | key_len | ref   | rows | filtered | Extra       |
 ---- ------------- --------------------- ------------ ------- -------------------- -------------------- --------- ------- ------ ---------- ------------- 
|  1 | SIMPLE      | itdragon_order_list | NULL       | const | idx_order_transaID | idx_order_transaID | 453     | const |    1 |      100 | Using index |
 ---- ------------- --------------------- ------------ ------- -------------------- -------------------- --------- ------- ------ ---------- ------------- 

这里将select * from 改为了 select transaction_id from
Extra 显示 Using index,表示该查询使用了覆盖索引,这是一个非常好的消息,说明该sql语句的性能很好。若提示的是Using filesort(使用内部排序)和Using temporary(使用临时表)则表明该sql需要立即优化了。
根据业务逻辑来的,查询结构返回transaction_id 是可以满足业务逻辑要求的。

  mysql默认的配置文件读取顺序为:

我们先通过两个简单的例子来入门。后面会详细介绍各个参数的作用和意义。

场景二,订单管理页面,通过订单级别和订单录入时间排序

业务逻辑:优先处理订单级别高,录入时间长的订单。
既然是排序,首先想到的应该是order by, 还有一个可怕的 Using filesort 等着你。

[root@localhost bin]# which mysqld
/usr/sbin/mysqld
[root@localhost bin]# ^C
[root@localhost bin]# /usr/sbin/mysqld --verbose --help |grep -A 1 'Default options'
2018-03-07 17:30:00 0 [Note] /usr/sbin/mysqld (mysqld 5.6.39) starting as process 14259 ...
2018-03-07 17:30:00 14259 [Note] Plugin 'FEDERATED' is disabled.
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 
#mysql的配置信息的读取目录顺序
2018-03-07 17:30:00 14259 [Note] Binlog end
2018-03-07 17:30:00 14259 [Note] Shutting down plugin 'CSV'
2018-03-07 17:30:00 14259 [Note] Shutting down plugin 'MyISAM'

说明:需要用到的sql已经放在了github上了,喜欢的同学可以点一下star,哈哈。

最基础的sql语句

mysql> explain select * from itdragon_order_list order by order_level,input_date;
 ---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ---------------- 
| id | select_type | table               | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
 ---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ---------------- 
|  1 | SIMPLE      | itdragon_order_list | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |      100 | Using filesort |
 ---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ---------------- 

首先,采用全表扫描就不合理,还使用了文件排序Using filesort,更加拖慢了性能。
MySQL在4.1版本之前文件排序是采用双路排序的算法,由于两次扫描磁盘,I/O耗时太长。后优化成单路排序算法。其本质就是用空间换时间,但如果数据量太大,buffer的空间不足,会导致多次I/O的情况。其效果反而更差。与其找运维同事修改MySQL配置,还不如自己乖乖地建索引。

  查看mysql的字符集

初步优化:为order_level,input_date 创建复合索引

mysql> create index idx_order_levelDate on itdragon_order_list (order_level,input_date);
mysql> explain select * from itdragon_order_list order by order_level,input_date;
 ---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ---------------- 
| id | select_type | table               | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
 ---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ---------------- 
|  1 | SIMPLE      | itdragon_order_list | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |      100 | Using filesort |
 ---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ---------------- 

创建复合索引后你会惊奇的发现,和没创建索引一样???都是全表扫描,都用到了文件排序。是索引失效?还是索引创建失败?我们试着看看下面打印情况

mysql> explain select order_level,input_date from itdragon_order_list order by order_level,input_date;
 ---- ------------- --------------------- ------------ ------- --------------- --------------------- --------- ------ ------ ---------- ------------- 
| id | select_type | table               | partitions | type  | possible_keys | key                 | key_len | ref  | rows | filtered | Extra       |
 ---- ------------- --------------------- ------------ ------- --------------- --------------------- --------- ------ ------ ---------- ------------- 
|  1 | SIMPLE      | itdragon_order_list | NULL       | index | NULL          | idx_order_levelDate | 68      | NULL |    3 |      100 | Using index |
 ---- ------------- --------------------- ------------ ------- --------------- --------------------- --------- ------ ------ ---------- ------------- 

select * from 换成了 select order_level,input_date from 后。type从all升级为index,表示(full index scan)全索引文件扫描,Extra也显示使用了覆盖索引。可是不对啊!!!!检索虽然快了,但返回的内容只有order_level和input_date 两个字段,让业务同事怎么用?难道把每个字段都建一个复合索引?
MySQL没有这么笨,可以使用force index 强制指定索引。在原来的sql语句上修改 force index(idx_order_levelDate) 即可。

mysql> explain select * from itdragon_order_list force index(idx_order_levelDate) order by order_level,input_date;
 ---- ------------- --------------------- ------------ ------- --------------- --------------------- --------- ------ ------ ---------- ------- 
| id | select_type | table               | partitions | type  | possible_keys | key                 | key_len | ref  | rows | filtered | Extra |
 ---- ------------- --------------------- ------------ ------- --------------- --------------------- --------- ------ ------ ---------- ------- 
|  1 | SIMPLE      | itdragon_order_list | NULL       | index | NULL          | idx_order_levelDate | 68      | NULL |    3 |      100 | NULL  |
 ---- ------------- --------------------- ------------ ------- --------------- --------------------- --------- ------ ------ ---------- ------- 
mysql> show variables like 'character%';
 -------------------------- ---------------------------- 
| Variable_name            | Value                      |
 -------------------------- ---------------------------- 
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
 -------------------------- ---------------------------- 
8 rows in set (0.00 sec)

mysql> show variables like '%char%';
 -------------------------- ---------------------------- 
| Variable_name            | Value                      |
 -------------------------- ---------------------------- 
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
 -------------------------- ---------------------------- 
8 rows in set (0.00 sec)

场景一:订单导入,通过交易号避免重复导单

再次优化:订单级别真的要排序么?

其实给订单级别排序意义并不大,给订单级别添加索引意义也不大。因为order_level的值可能只有,低,中,高,加急,这四种。对于这种重复且分布平均的字段,排序和加索引的作用不大。
我们能否先固定 order_level 的值,然后再给 input_date 排序?如果查询效果明显,是可以推荐业务同事使用该查询方式。

mysql> explain select * from itdragon_order_list where order_level=3 order by input_date;
 ---- ------------- --------------------- ------------ ------ --------------------- --------------------- --------- ------- ------ ---------- ----------------------- 
| id | select_type | table               | partitions | type | possible_keys       | key                 | key_len | ref   | rows | filtered | Extra                 |
 ---- ------------- --------------------- ------------ ------ --------------------- --------------------- --------- ------- ------ ---------- ----------------------- 
|  1 | SIMPLE      | itdragon_order_list | NULL       | ref  | idx_order_levelDate | idx_order_levelDate | 5       | const |    1 |      100 | Using index condition |
 ---- ------------- --------------------- ------------ ------ --------------------- --------------------- --------- ------- ------ ---------- ----------------------- 

和之前的sql比起来,type从index 升级为 ref(非唯一性索引扫描)。索引的长度从68变成了5,说明只用了一个索引。ref也是一个常量。Extra 为Using index condition 表示自动根据临界值,选择索引扫描还是全表扫描。总的来说性能远胜于之前的sql。

上面两个案例只是快速入门,我们需严记一点:优化是基于业务逻辑来的。绝对不能为了优化而擅自修改业务逻辑。如果能修改当然是最好的。

  此时数据库的字符集为拉丁,我们需要改为utf8。

业务逻辑:订单导入时,为了避免重复导单,一般会通过交易号去数据库中查询,判断该订单是否已经存在。

索引简介

官方定义:索引(Index) 是帮助MySQL高效获取数据的数据结构。
大家一定很好奇,索引为什么是一种数据结构,它又是怎么提高查询的速度?我们拿最常用的二叉树来分析索引的工作原理。看下面的图片:
澳门新萄京官方网站 34

创建索引的优势
1 提高数据的检索速度,降低数据库IO成本:使用索引的意义就是通过缩小表中需要查询的记录的数目从而加快搜索的速度。
2 降低数据排序的成本,降低CPU消耗:索引之所以查的快,是因为先将数据排好序,若该字段正好需要排序,则真好降低了排序的成本。

创建索引的劣势
1 占用存储空间:索引实际上也是一张表,记录了主键与索引字段,一般以索引文件的形式存储在磁盘上。
2 降低更新表的速度:表的数据发生了变化,对应的索引也需要一起变更,从而减低的更新速度。否则索引指向的物理数据可能不对,这也是索引失效的原因之一。
3 优质索引创建难:索引的创建并非一日之功,也并非一直不变。需要频繁根据用户的行为和具体的业务逻辑去创建最佳的索引。

  修改mysql的默认配置文件/etc/my.cnf。

最基础的sql语句

索引分类

我们常说的索引一般指的是BTree(多路搜索树)结构组织的索引。其中还有聚合索引,次要索引,复合索引,前缀索引,唯一索引,统称索引,当然除了B 树外,还有哈希索引(hash index)等。

单值索引:一个索引只包含单个列,一个表可以有多个单列索引
唯一索引:索引列的值必须唯一,但允许有空值
复合索引:一个索引包含多个列,实际开发中推荐使用
实际开发中推荐使用复合索引,并且单表创建的索引个数建议不要超过五个

基本语法:
创建:

create [unique] index indexName on tableName (columnName...)
alter tableName add [unique] index [indexName] on (columnName...)

删除:

drop index [indexName] on tableName

查看:

show index from tableName

哪些情况需要建索引:
1 主键,唯一索引
2 经常用作查询条件的字段需要创建索引
3 经常需要排序、分组和统计的字段需要建立索引
4 查询中与其他表关联的字段,外键关系建立索引

哪些情况不要建索引:
1 表的记录太少,百万级以下的数据不需要创建索引
2 经常增删改的表不需要创建索引
3 数据重复且分布平均的字段不需要创建索引,如 true,false 之类。
4 频发更新的字段不适合创建索引
5 where条件里用不到的字段不需要创建索引

[mysql]  
# 设置mysql客户端默认字符集  
default-character-set=utf8   
socket=/var/lib/mysql/mysql.sock  

[mysqld]  
skip-name-resolve  
#设置3306端口  
port = 3306   
socket=/var/lib/mysql/mysql.sock  
# 设置mysql的安装目录  
basedir=/usr/local/mysql  
# 设置mysql数据库的数据的存放目录  
datadir=/usr/local/mysql/data  
# 允许最大连接数  
max_connections=200  
# 服务端使用的字符集默认为8比特编码的latin1字符集  
character-set-server=utf8  
# 创建新表时将使用的默认存储引擎  
default-storage-engine=INNODB  
lower_case_table_name=1  
max_allowed_packet=16M  
mysql> select * from itdragon_order_list where transaction_id = "81X97310V32236260E";
 ------- -------------------- ------- ------ ---------- -------------- ---------- ------------------ ------------- ------------- ------------ --------------------- 
| id  | transaction_id   | gross | net | stock_id | order_status | descript | finance_descript | create_type | order_level | input_user | input_date     |
 ------- -------------------- ------- ------ ---------- -------------- ---------- ------------------ ------------- ------------- ------------ --------------------- 
| 10000 | 81X97310V32236260E |  6.6 | 6.13 |    1 |      10 | ok    | ok        | auto    |      1 | itdragon  | 2017-08-18 17:01:49 |
 ------- -------------------- ------- ------ ---------- -------------- ---------- ------------------ ------------- ------------- ------------ --------------------- 

mysql> explain select * from itdragon_order_list where transaction_id = "81X97310V32236260E";
 ---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- 
| id | select_type | table        | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra    |
 ---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- 
| 1 | SIMPLE   | itdragon_order_list | NULL    | ALL | NULL     | NULL | NULL  | NULL |  3 |  33.33 | Using where |
 ---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- 

性能分析

  关闭mysql并重新开启:

查询的本身没有任何问题,在线下的测试环境也没有任何问题。可是,功能一旦上线,查询慢的问题就迎面而来。几百上千万的订单,用全表扫描?啊?哼!

MySQL 自身瓶颈

MySQL自身参见的性能问题有磁盘空间不足,磁盘I/O太大,服务器硬件性能低。
1 CPU:CPU 在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
2 IO:磁盘I/O 瓶颈发生在装入数据远大于内存容量的时候
3 服务器硬件的性能瓶颈:top,free,iostat 和 vmstat来查看系统的性能状态

[root@localhost mysql-community-server-5.6.39]# service mysqld stop
Stopping mysqld (via systemctl):                           [  确定  ]
[root@localhost mysql-community-server-5.6.39]# service mysqld start
Starting mysqld (via systemctl):                           [  确定  ]
[root@localhost mysql-community-server-5.6.39]# mysql -uroot -p
Enter password: 

怎么知道该sql是全表扫描呢?通过explain命令可以清楚MySQL是如何处理sql语句的。打印的内容分别表示:

explain 分析sql语句

使用explain关键字可以模拟优化器执行sql查询语句,从而得知MySQL 是如何处理sql语句。

 ---- ------------- ------- ------------ ------ --------------- ----- --------- ------ ------ ---------- ------- 
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref  | rows | filtered | Extra |
 ---- ------------- ------- ------------ ------ --------------- ----- --------- ------ ------ ---------- ------- 

  新建数据库db2显示db1与db2的数据库的字符编码:

  1. id : 查询序列号为1。
  2. select_type : 查询类型是简单查询,简单的select语句没有union和子查询。
  3. table : 表是 itdragon_order_list。
  4. partitions : 没有分区。
  5. type : 连接类型,all表示采用全表扫描的方式。
  6. possible_keys : 可能用到索引为null。
  7. key : 实际用到索引是null。
  8. key_len : 索引长度当然也是null。
  9. ref : 没有哪个列或者参数和key一起被使用。
  10. Extra : 使用了where查询。

id

select 查询的序列号,包含一组可以重复的数字,表示查询中执行sql语句的顺序。一般有三种情况:
第一种:id全部相同,sql的执行顺序是由上至下;
第二种:id全部不同,sql的执行顺序是根据id大的优先执行;
第三种:id既存在相同,又存在不同的。先根据id大的优先执行,再根据相同id从上至下的执行。

mysql> create database db2;
Query OK, 1 row affected (0.04 sec)

mysql> use db2;
Database changed
mysql> show variables like '%char%';
 -------------------------- ---------------------------- 
| Variable_name            | Value                      |
 -------------------------- ---------------------------- 
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
 -------------------------- ---------------------------- 
8 rows in set (0.00 sec)

mysql> use db1;
Database changed
mysql> show variables like '%char%';
 -------------------------- ---------------------------- 
| Variable_name            | Value                      |
 -------------------------- ---------------------------- 
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
 -------------------------- ---------------------------- 
8 rows in set (0.00 sec)

 因为数据库中只有三条数据,所以rows和filtered的信息作用不大。这里需要重点了解的是type为ALL,全表扫描的性能是最差的,假设数据库中有几百万条数据,在没有索引的帮助下会异常卡顿。

select_type

select 查询的类型,主要是用于区别普通查询,联合查询,嵌套的复杂查询
simple:简单的select 查询,查询中不包含子查询或者union
primary:查询中若包含任何复杂的子查询,最外层查询则被标记为primary
subquery:在select或where 列表中包含了子查询
derived:在from列表中包含的子查询被标记为derived(衍生)MySQL会递归执行这些子查询,把结果放在临时表里。
union:若第二个select出现在union之后,则被标记为union,若union包含在from子句的子查询中,外层select将被标记为:derived
union result:从union表获取结果的select

  之前创建的数据库的字符编码仍为Latin,而修改过后的新建数据库已经使用utf8了,说明我们的配置生效了。

初步优化:为transaction_id创建索引

partitions

表所使用的分区,如果要统计十年公司订单的金额,可以把数据分为十个区,每一年代表一个区。这样可以大大的提高查询效率。

  查看mysql的安装目录

mysql> create unique index idx_order_transaID on itdragon_order_list (transaction_id);
mysql> explain select * from itdragon_order_list where transaction_id = "81X97310V32236260E";
 ---- ------------- --------------------- ------------ ------- -------------------- -------------------- --------- ------- ------ ---------- ------- 
| id | select_type | table        | partitions | type | possible_keys   | key        | key_len | ref  | rows | filtered | Extra |
 ---- ------------- --------------------- ------------ ------- -------------------- -------------------- --------- ------- ------ ---------- ------- 
| 1 | SIMPLE   | itdragon_order_list | NULL    | const | idx_order_transaID | idx_order_transaID | 453   | const |  1 |   100 | NULL |
 ---- ------------- --------------------- ------------ ------- -------------------- -------------------- --------- ------- ------ ---------- ------- 

type

这是一个非常重要的参数,连接类型,常见的有:all , index , range , ref , eq_ref , const , system , null 八个级别。
性能从最优到最差的排序:system > const > eq_ref > ref > range > index > all
对java程序员来说,若保证查询至少达到range级别或者最好能达到ref则算是一个优秀而又负责的程序员。
all:(full table scan)全表扫描无疑是最差,若是百万千万级数据量,全表扫描会非常慢。
index:(full index scan)全索引文件扫描比all好很多,毕竟从索引树中找数据,比从全表中找数据要快。
range:只检索给定范围的行,使用索引来匹配行。范围缩小了,当然比全表扫描和全索引文件扫描要快。sql语句中一般会有between,in,>,< 等查询。
ref:非唯一性索引扫描,本质上也是一种索引访问,返回所有匹配某个单独值的行。比如查询公司所有属于研发团队的同事,匹配的结果是多个并非唯一值。
eq_ref:唯一性索引扫描,对于每个索引键,表中有一条记录与之匹配。比如查询公司的CEO,匹配的结果只可能是一条记录,
const:表示通过索引一次就可以找到,const用于比较primary key 或者unique索引。因为只匹配一行数据,所以很快,若将主键至于where列表中,MySQL就能将该查询转换为一个常量。
system:表只有一条记录(等于系统表),这是const类型的特列,平时不会出现,了解即可

[root@localhost mysql-community-server-5.6.39]# vim /etc/my.cnf
[root@localhost mysql-community-server-5.6.39]# ps -ef|grep mysql
root      14599      1  0 18:44 ?        00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql
mysql     14941  14599  0 18:44 ?        00:00:02 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock --port=3306
root      15008  11441  0 18:54 pts/0    00:00:00 grep --color=auto mysql

这里创建的索引是唯一索引,而非普通索引。

possible_keys

显示查询语句可能用到的索引(一个或多个或为null),不一定被查询实际使用。仅供参考使用。

  mysql主要的配置文件

唯一索引打印的type值是const。表示通过索引一次就可以找到。即找到值就结束扫描返回查询结果。

key

显示查询语句实际使用的索引。若为null,则表示没有使用索引。

  二进制日志log-bin  用于主从复制;

普通索引打印的type值是ref。表示非唯一性索引扫描。找到值还要继续扫描,直到将索引文件扫描完为止。(这里没有贴出代码)
显而易见,const的性能要远高于ref。并且根据业务逻辑来判断,创建唯一索引是合情合理的。

key_len

显示索引中使用的字节数,可通过key_len计算查询中使用的索引长度。在不损失精确性的情况下索引长度越短越好。key_len 显示的值为索引字段的最可能长度,并非实际使用长度,即key_len是根据表定义计算而得,并不是通过表内检索出的。

  错误日志log-error 默认关闭,记录严重警告和错误信息。

再次优化:覆盖索引

ref

显示索引的哪一列或常量被用于查找索引列上的值。

  查询日志log 默认关闭,用来查询sql语句,开启会降低mysql整体性能

mysql> explain select transaction_id from itdragon_order_list where transaction_id = "81X97310V32236260E";
 ---- ------------- --------------------- ------------ ------- -------------------- -------------------- --------- ------- ------ ---------- ------------- 
| id | select_type | table        | partitions | type | possible_keys   | key        | key_len | ref  | rows | filtered | Extra    |
 ---- ------------- --------------------- ------------ ------- -------------------- -------------------- --------- ------- ------ ---------- ------------- 
| 1 | SIMPLE   | itdragon_order_list | NULL    | const | idx_order_transaID | idx_order_transaID | 453   | const |  1 |   100 | Using index |
 ---- ------------- --------------------- ------------ ------- -------------------- -------------------- --------- ------- ------ ---------- ------------- 

rows

根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数,值越大越不好。

  数据文件:前面提到的数据库的数据库文件,frm文件用于存放表结构,myd文件存放表数据,myi文件用于存放表索引。

这里将select * from 改为了 select transaction_id from 后 Extra 显示 Using index,表示该查询使用了覆盖索引,这是一个非常好的消息,说明该sql语句的性能很好。若提示的是Using filesort(使用内部排序)和Using temporary(使用临时表)则表明该sql需要立即优化了。

extra

Using filesort: 说明MySQL会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序” 。出现这个就要立刻优化sql。
Using temporary: 使用了临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和 分组查询 group by。 出现这个更要立刻优化sql。
Using index: 表示相应的select 操作中使用了覆盖索引(Covering index),避免访问了表的数据行,效果不错!如果同时出现Using where,表明索引被用来执行索引键值的查找。如果没有同时出现Using where,表示索引用来读取数据而非执行查找动作。
覆盖索引(Covering Index) :也叫索引覆盖,就是select 的数据列只用从索引中就能够取得,不必读取数据行,MySQL可以利用索引返回select 列表中的字段,而不必根据索引再次读取数据文件。
Using index condition: 在5.6版本后加入的新特性,优化器会在索引存在的情况下,通过符合RANGE范围的条数 和 总数的比例来选择是使用索引还是进行全表遍历。
Using where: 表明使用了where 过滤
Using join buffer: 表明使用了连接缓存
impossible where: where 语句的值总是false,不可用,不能用来获取任何元素
distinct: 优化distinct操作,在找到第一匹配的元组后即停止找同样值的动作。

  配置文件:Linux为/etc/my.cnf,win为my.ini。

根据业务逻辑来的,查询结构返回transaction_id 是可以满足业务逻辑要求的。

filtered

一个百分比的值,和rows 列的值一起使用,可以估计出查询执行计划(QEP)中的前一个表的结果集,从而确定join操作的循环次数。小表驱动大表,减轻连接的次数。

通过explain的参数介绍,我们可以得知:
1 表的读取顺序(id)
2 数据读取操作的操作类型(type)
3 哪些索引被实际使用(key)
4 表之间的引用(ref)
5 每张表有多少行被优化器查询(rows)

  mysql架构介绍

场景二,订单管理页面,通过订单级别和订单录入时间排序

性能下降的原因

从程序员的角度
1 查询语句写的不好
2 没建索引,索引建的不合理或索引失效
3 关联查询有太多的join
从服务器的角度
1 服务器磁盘空间不足
2 服务器调优配置参数设置不合理

澳门新萄京官方网站 35

业务逻辑:优先处理订单级别高,录入时间长的订单。
 既然是排序,首先想到的应该是order by, 还有一个可怕的 Using filesort 等着你。

总结

1 索引是排好序且快速查找的数据结构。其目的是为了提高查询的效率。
2 创建索引后,查询数据变快,但更新数据变慢。
3 性能下降的原因很可能是索引失效导致。
4 索引创建的原则,经常查询的字段适合创建索引,频繁需要更新的数据不适合创建索引。
5 索引字段频繁更新,或者表数据物理删除容易造成索引失效。
6 擅用 explain 分析sql语句
7 除了优化sql语句外,还可以优化表的设计。如尽量做成单表查询,减少表之间的关联。设计归档表等。

到这里,MySQL的索引优化分析就结束了,有什么不对的地方,大家可以提出来。如果觉得不错可以点一下推荐。

  第一层为连接层,connectors。

最基础的sql语句

参考文献

MySQL order by排序优化:

  接下来就进入mysql层:

mysql> explain select * from itdragon_order_list order by order_level,input_date;
 ---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ---------------- 
| id | select_type | table        | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra     |
 ---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ---------------- 
| 1 | SIMPLE   | itdragon_order_list | NULL    | ALL | NULL     | NULL | NULL  | NULL |  3 |   100 | Using filesort |
 ---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ---------------- 

  第二层为一部分是与外层的连接池(并发部分),备份安全集群等部份,然后到对输入sql语句进行解析的部分(sqlinterface接口对你要进行是curd的某项操作进行分开解析),解析到optimizer部分为mysql自己的逻辑操作部分(人进行数据查询首先关注select部分,但是机器关心的是从from哪张表开始,这个部分是mysql的优化器),最后一部分是缓存与缓冲,解决数据库存取时的io操作问题,所以这一层主要是业务逻辑处理层。

首先,采用全表扫描就不合理,还使用了文件排序Using filesort,更加拖慢了性能。

  第三层为可拔插的存储引擎部分。有很多种,我们经常使用到的是innodb有时也用myisam。

MySQL在4.1版本之前文件排序是采用双路排序的算法,由于两次扫描磁盘,I/O耗时太长。后优化成单路排序算法。其本质就是用空间换时间,但如果数据量太大,buffer的空间不足,会导致多次I/O的情况。其效果反而更差。与其找运维同事修改MySQL配置,还不如自己乖乖地建索引。

  第四层就是底层的文件存储层,底层的文件系统等。

初步优化:为order_level,input_date 创建复合索引

  与其他数据库相比,mysql的架构可以在多种不同场景应用并发挥良好作用主要体现在存储引擎的架构上,可插拔的存储引擎架构将查询处理和其他的系统任务以及数据的存储提取相分离,我们可以根据业务的实际需求选择合适的存储引擎。

mysql> create index idx_order_levelDate on itdragon_order_list (order_level,input_date);
mysql> explain select * from itdragon_order_list order by order_level,input_date;
 ---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ---------------- 
| id | select_type | table        | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra     |
 ---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ---------------- 
| 1 | SIMPLE   | itdragon_order_list | NULL    | ALL | NULL     | NULL | NULL  | NULL |  3 |   100 | Using filesort |
 ---- ------------- --------------------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ---------------- 

澳门新萄京官方网站 36

创建复合索引后你会惊奇的发现,和没创建索引一样???都是全表扫描,都用到了文件排序。是索引失效?还是索引创建失败?我们试着看看下面打印情况

  查看存储引擎:

mysql> explain select order_level,input_date from itdragon_order_list order by order_level,input_date;
 ---- ------------- --------------------- ------------ ------- --------------- --------------------- --------- ------ ------ ---------- ------------- 
| id | select_type | table        | partitions | type | possible_keys | key         | key_len | ref | rows | filtered | Extra    |
 ---- ------------- --------------------- ------------ ------- --------------- --------------------- --------- ------ ------ ---------- ------------- 
| 1 | SIMPLE   | itdragon_order_list | NULL    | index | NULL     | idx_order_levelDate | 68   | NULL |  3 |   100 | Using index |
 ---- ------------- --------------------- ------------ ------- --------------- --------------------- --------- ------ ------ ---------- ------------- 
mysql> show engines;
 -------------------- --------- ---------------------------------------------------------------- -------------- ------ ------------ 
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
 -------------------- --------- ---------------------------------------------------------------- -------------- ------ ------------ 
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
 -------------------- --------- ---------------------------------------------------------------- -------------- ------ ------------ 
9 rows in set (0.00 sec)

将select * from 换成了 select order_level,input_date from 后。type从all升级为index,表示(full index scan)全索引文件扫描,Extra也显示使用了覆盖索引。可是不对啊!!!!检索虽然快了,但返回的内容只有order_level和input_date 两个字段,让业务同事怎么用?难道把每个字段都建一个复合索引?

  主流的两种存储引擎的对比:

MySQL没有这么笨,可以使用force index 强制指定索引。在原来的sql语句上修改 force index(idx_order_levelDate) 即可。

澳门新萄京官方网站 37

mysql> explain select * from itdragon_order_list force index(idx_order_levelDate) order by order_level,input_date;
 ---- ------------- --------------------- ------------ ------- --------------- --------------------- --------- ------ ------ ---------- ------- 
| id | select_type | table        | partitions | type | possible_keys | key         | key_len | ref | rows | filtered | Extra |
 ---- ------------- --------------------- ------------ ------- --------------- --------------------- --------- ------ ------ ---------- ------- 
| 1 | SIMPLE   | itdragon_order_list | NULL    | index | NULL     | idx_order_levelDate | 68   | NULL |  3 |   100 | NULL |
 ---- ------------- --------------------- ------------ ------- --------------- --------------------- --------- ------ ------ ---------- ------- 

  关于mysql性能下降的分析

再次优化:订单级别真的要排序么?

  如果mysql的性能下降,sql慢,执行时间长,等待时间长,那么问题可能出现在:

其实给订单级别排序意义并不大,给订单级别添加索引意义也不大。因为order_level的值可能只有,低,中,高,加急,这四种。对于这种重复且分布平均的字段,排序和加索引的作用不大。

  1.查询语句写得太烂了

我们能否先固定 order_level 的值,然后再给 input_date 排序?如果查询效果明显,是可以推荐业务同事使用该查询方式。

  2.索引失效了

mysql> explain select * from itdragon_order_list where order_level=3 order by input_date;
 ---- ------------- --------------------- ------------ ------ --------------------- --------------------- --------- ------- ------ ---------- ----------------------- 
| id | select_type | table        | partitions | type | possible_keys    | key         | key_len | ref  | rows | filtered | Extra         |
 ---- ------------- --------------------- ------------ ------ --------------------- --------------------- --------- ------- ------ ---------- ----------------------- 
| 1 | SIMPLE   | itdragon_order_list | NULL    | ref | idx_order_levelDate | idx_order_levelDate | 5    | const |  1 |   100 | Using index condition |
 ---- ------------- --------------------- ------------ ------ --------------------- --------------------- --------- ------- ------ ---------- ----------------------- 

  3.关联查询的join写的太多了(可能是表的设计的问题)

和之前的sql比起来,type从index 升级为 ref(非唯一性索引扫描)。索引的长度从68变成了5,说明只用了一个索引。ref也是一个常量。Extra 为Using index condition 表示自动根据临界值,选择索引扫描还是全表扫描。总的来说性能远胜于之前的sql。

  4.服务器调优及各个参数的设置问题(缓冲及线程数等)

上面两个案例只是快速入门,我们需严记一点:优化是基于业务逻辑来的。绝对不能为了优化而擅自修改业务逻辑。如果能修改当然是最好的。

  你写的sql:

索引简介

澳门新萄京官方网站 38

官方定义:索引(Index) 是帮助MySQL高效获取数据的数据结构。

  mysql都出来的查询语句

大家一定很好奇,索引为什么是一种数据结构,它又是怎么提高查询的速度?我们拿最常用的二叉树来分析索引的工作原理。

澳门新萄京官方网站 39

看下面的图片:

  机读的顺序

澳门新萄京官方网站 40

澳门新萄京官方网站 41

创建索引的优势

   mysql索引

1 提高数据的检索速度,降低数据库IO成本:使用索引的意义就是通过缩小表中需要查询的记录的数目从而加快搜索的速度。

  定义:索引是帮助mysql高效获取数据的数据结构。我们可以根据索引的特点理解为,索引就是排好序的快速查找的数据结构。

2 降低数据排序的成本,降低CPU消耗:索引之所以查的快,是因为先将数据排好序,若该字段正好需要排序,则真好降低了排序的成本。

  以后别人问你mysql索引是什么的时候,别再说什么字典什么查的快什么的了,你得专业的告诉他,索引就是用B 树实现的快速查找的一种数据类型。

创建索引的劣势

  索引影响的部分是where后面的查找与orderby后面的排序。

1 占用存储空间:索引实际上也是一张表,记录了主键与索引字段,一般以索引文件的形式存储在磁盘上。

  除了数据,数据库系统还维护了一个满足特定查找算法的数据结构,这些数据结构以某种方式指向数据,这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。

2 降低更新表的速度:表的数据发生了变化,对应的索引也需要一起变更,从而减低的更新速度。否则索引指向的物理数据可能不对,这也是索引失效的原因之一。

澳门新萄京官方网站 42

3 优质索引创建难:索引的创建并非一日之功,也并非一直不变。需要频繁根据用户的行为和具体的业务逻辑去创建最佳的索引。

  为了加快col2的查找,可以维护一个二叉树,每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针,这样就可以运用二叉查找在一定复杂度内获取到相应数据,从而快速的检索出符合条件的记录。

索引分类

  一般来说,索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储在磁盘上。

我们常说的索引一般指的是BTree(多路搜索树)结构组织的索引。其中还有聚合索引,次要索引,复合索引,前缀索引,唯一索引,统称索引,当然除了B 树外,还有哈希索引(hash index)等。

  我们一般说的索引都是b树结构组织索引,其中聚集索引,次要索引,覆盖索引,复合索引,前缀索引,唯一索引都是使用b树索引。除了b树索引意外还有hash索引。

  1. 单值索引:一个索引只包含单个列,一个表可以有多个单列索引
  2. 唯一索引:索引列的值必须唯一,但允许有空值
  3. 复合索引:一个索引包含多个列,实际开发中推荐使用

  索引的优势:

实际开发中推荐使用复合索引,并且单表创建的索引个数建议不要超过五个

  查找时提高数据检索效率,降低数据库io成本。排序时,索引对数据进行排序,降低数据排序成本,降低了CPU的消耗。

基本语法:

  索引的劣势:

创建:

  索引本身是一张表,保存了主键与索引字段,并指向实体表的记录,所以索引本身也占空间。

create [unique] index indexName on tableName (columnName...)
alter tableName add [unique] index [indexName] on (columnName...)

  索引虽然大大加快了查询速度,却降低了更新速度,在更新表时,不仅要存更新的数据,还要存索引文件每次更新添加的索引列字段,都会调整因为更=新带来的简直变化后的索引信息。

删除:

  索引只是提高效率的一个因素,如果你的mysql有大量数据的表,那么就要花时间研究建立最优秀的索引或优化查询语句。

drop index [indexName] on tableName

  mysql索引分类

查看:

  单值索引:一个索引只包含单个列,一个表可以有多个单列索引。

show index from tableName

  唯一索引:索引列的值必须唯一,但允许空值。

哪些情况需要建索引:

  复合索引:一个索引包含多个列。

1 主键,唯一索引
2 经常用作查询条件的字段需要创建索引
3 经常需要排序、分组和统计的字段需要建立索引
4 查询中与其他表关联的字段,外键关系建立索引

  b树查找

哪些情况不要建索引:

澳门新萄京官方网站 43

1 表的记录太少,百万级以下的数据不需要创建索引
2 经常增删改的表不需要创建索引
3 数据重复且分布平均的字段不需要创建索引,如 true,false 之类。
4 频发更新的字段不适合创建索引
5 where条件里用不到的字段不需要创建索引

  这里磁盘块Ⅰ存储了指引搜索方向的数据项,17与35不一定存在于数据表中,只是一个趋势,叶子节点存的数据必须是真实存在的数据。根节点的数据表示,查找的数据范围是小于17还是位于17和35之间还是大于35的数据。然后从根出发一直找到索引的数据,只需要三步即可。

性能分析

  那些情况需要创建索引

MySQL 自身瓶颈

  1.主键自动建立唯一索引

MySQL自身参见的性能问题有磁盘空间不足,磁盘I/O太大,服务器硬件性能低。
1 CPU:CPU 在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
2 IO:磁盘I/O 瓶颈发生在装入数据远大于内存容量的时候
3 服务器硬件的性能瓶颈:top,free,iostat 和 vmstat来查看系统的性能状态

  2.频繁作为查找条件的字段应该创建索引

explain 分析sql语句

  3.查询中与其他表关联的字段,外键关系建立索引

使用explain关键字可以模拟优化器执行sql查询语句,从而得知MySQL 是如何处理sql语句。

  4.频繁更新的字段不适合创建索引,不只是更新记录还会更新索引,加重io负担

 ---- ------------- ------- ------------ ------ --------------- ----- --------- ------ ------ ---------- ------- 
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
 ---- ------------- ------- ------------ ------ --------------- ----- --------- ------ ------ ---------- ------- 

  5.where条件里用不到的字段不创建索引

id

  6.单键/组合索引的选择问题,在高并发下倾向创建组合索引

select 查询的序列号,包含一组可以重复的数字,表示查询中执行sql语句的顺序。一般有三种情况:
 第一种:id全部相同,sql的执行顺序是由上至下;
 第二种:id全部不同,sql的执行顺序是根据id大的优先执行;
 第三种:id既存在相同,又存在不同的。先根据id大的优先执行,再根据相同id从上至下的执行。

  7.查询中排序的字段,排序字段通过索引访问将大大提高排序速度

select_type

  8.查询中统计或者分组字段

select 查询的类型,主要是用于区别普通查询,联合查询,嵌套的复杂查询
simple:简单的select 查询,查询中不包含子查询或者union
primary:查询中若包含任何复杂的子查询,最外层查询则被标记为primary
subquery:在select或where 列表中包含了子查询
derived:在from列表中包含的子查询被标记为derived(衍生)MySQL会递归执行这些子查询,把结果放在临时表里。
union:若第二个select出现在union之后,则被标记为union,若union包含在from子句的子查询中,外层select将被标记为:derived
union result:从union表获取结果的select

  哪些情况不要创建索引

partitions

  1.表记录太少

表所使用的分区,如果要统计十年公司订单的金额,可以把数据分为十个区,每一年代表一个区。这样可以大大的提高查询效率。

  2.经常增删改的表

type

  3.数据重复且分布平均的表的字段。比如性别列。

这是一个非常重要的参数,连接类型,常见的有:all , index , range , ref , eq_ref , const , system , null 八个级别。
 性能从最优到最差的排序:system > const > eq_ref > ref > range > index > all
对java程序员来说,若保证查询至少达到range级别或者最好能达到ref则算是一个优秀而又负责的程序员。
all:(full table scan)全表扫描无疑是最差,若是百万千万级数据量,全表扫描会非常慢。
index:(full index scan)全索引文件扫描比all好很多,毕竟从索引树中找数据,比从全表中找数据要快。
range:只检索给定范围的行,使用索引来匹配行。范围缩小了,当然比全表扫描和全索引文件扫描要快。sql语句中一般会有between,in,>,< 等查询。
ref:非唯一性索引扫描,本质上也是一种索引访问,返回所有匹配某个单独值的行。比如查询公司所有属于研发团队的同事,匹配的结果是多个并非唯一值。
eq_ref:唯一性索引扫描,对于每个索引键,表中有一条记录与之匹配。比如查询公司的CEO,匹配的结果只可能是一条记录,
const:表示通过索引一次就可以找到,const用于比较primary key 或者unique索引。因为只匹配一行数据,所以很快,若将主键至于where列表中,MySQL就能将该查询转换为一个常量。
system:表只有一条记录(等于系统表),这是const类型的特列,平时不会出现,了解即可

  Explain

possible_keys

  除了mysql的optimizer自动的优化导致的sql性能下降和cache与buffer的硬件部分性能瓶颈,我们分析一条sql语句是否高效就必须用到Explain进行查询。

显示查询语句可能用到的索引(一个或多个或为null),不一定被查询实际使用。仅供参考使用。

  explain关键字可以模拟优化sql查询语句,从而知道mysql是如何处理你的sql语句的,可以用来分析你的查询语句或是表结构的性能瓶颈。

key

  能查看什么:

显示查询语句实际使用的索引。若为null,则表示没有使用索引。

  表的读取顺序,数据读取操作的操作类型,那些索引可以使用,那些索引被实际使用,表之间的引用,每张表有多少行被优化器查询。

key_len

  执行方法

显示索引中使用的字节数,可通过key_len计算查询中使用的索引长度。在不损失精确性的情况下索引长度越短越好。key_len 显示的值为索引字段的最可能长度,并非实际使用长度,即key_len是根据表定义计算而得,并不是通过表内检索出的。

explain sql

ref

   explain中的字段

显示索引的哪一列或常量被用于查找索引列上的值。

  id字段

rows

  此字段用来表示mysql查询时执行select子句或操作表的顺序,即表的读取顺序。

根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数,值越大越不好。

  查询结果可能有三种情况

extra

  1.id相同时。

Using filesort: 说明MySQL会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序” 。出现这个就要立刻优化sql。
Using temporary: 使用了临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和 分组查询 group by。 出现这个更要立刻优化sql。
Using index: 表示相应的select 操作中使用了覆盖索引(Covering index),避免访问了表的数据行,效果不错!如果同时出现Using where,表明索引被用来执行索引键值的查找。如果没有同时出现Using where,表示索引用来读取数据而非执行查找动作。
 覆盖索引(Covering Index) :也叫索引覆盖,就是select 的数据列只用从索引中就能够取得,不必读取数据行,MySQL可以利用索引返回select 列表中的字段,而不必根据索引再次读取数据文件。
Using index condition: 在5.6版本后加入的新特性,优化器会在索引存在的情况下,通过符合RANGE范围的条数 和 总数的比例来选择是使用索引还是进行全表遍历。
Using where: 表明使用了where 过滤
Using join buffer: 表明使用了连接缓存
impossible where: where 语句的值总是false,不可用,不能用来获取任何元素
distinct: 优化distinct操作,在找到第一匹配的元组后即停止找同样值的动作。

澳门新萄京官方网站 44

filtered

  即此时mysql加载的顺序为t1>t3>t2。

一个百分比的值,和rows 列的值一起使用,可以估计出查询执行计划(QEP)中的前一个表的结果集,从而确定join操作的循环次数。小表驱动大表,减轻连接的次数。

  2.id不同

通过explain的参数介绍,我们可以得知:
1 表的读取顺序(id)
 2 数据读取操作的操作类型(type)
 3 哪些索引被实际使用(key)
 4 表之间的引用(ref)
 5 每张表有多少行被优化器查询(rows)

澳门新萄京官方网站 45

性能下降的原因

  即此时mysql加载的顺序为t3>t1>t2。

从程序员的角度
1 查询语句写的不好
2 没建索引,索引建的不合理或索引失效
3 关联查询有太多的join

  3.混合存在

从服务器的角度
1 服务器磁盘空间不足
2 服务器调优配置参数设置不合理

澳门新萄京官方网站 46

总结

  即此时mysql加载的顺序为t3><derived2>>t2。

1 索引是排好序且快速查找的数据结构。其目的是为了提高查询的效率。
2 创建索引后,查询数据变快,但更新数据变慢。
3 性能下降的原因很可能是索引失效导致。
4 索引创建的原则,经常查询的字段适合创建索引,频繁需要更新的数据不适合创建索引。
5 索引字段频繁更新,或者表数据物理删除容易造成索引失效。
6 擅用 explain 分析sql语句
7 除了优化sql语句外,还可以优化表的设计。如尽量做成单表查询,减少表之间的关联。设计归档表等。

  select_type字段

到这里,MySQL的索引优化分析就结束了,有什么不对的地方,大家可以提出来。如果觉得不错可以点一下推荐。

  即数据读取操作的操作类型

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持脚本之家。

  1.simple

您可能感兴趣的文章:

  • MySQL 索引分析和优化
  • MySQL索引背后的之使用策略及优化(高性能索引策略)
  • Mysql使用索引实现查询优化
  • MySQL中索引优化distinct语句及distinct的多字段操作
  • Mysql性能优化案例 - 覆盖索引分享

  简单的select查询,查询不包括子查询与union

  2.primary

  如果查询包含了任何复杂的字部分,最外层查询被标记为primary

  3.subquery

  在select或者where列表中包含了子查询

  4.derived

  在from列表包含的子查询被标记为derived衍生,mysql会递归执行这些子查询,把结果放到临时表中

  5.union

  若第二个select出现在union后则被标记为union,若union包含在from子句的子查询中,外层select将被标记为derived。

  6.union result

  从union表获取结果的select

  table字段,就是表名字段,表时这一行数据是关于那张表的。

  type字段

  直接的显示了表是否被优化是否最佳状态的直接体现。

  一般常见的类型为system>const>eq_ref>ref>range>index>all

  当你的数据达到百万级并且sql语句执行为all,那么你的查询语句或者说表需要进行优化了。当你的sql执行为ref或者range就已经很优了。

  type的不同状态:

  1.system

  表只有一行记录(等于系统表),是const类型的特例,平时基本遇不到可以忽略不计

  2.const常量

  表通过索引一次就找到了,const用于比较primary key或者unique索引,因为只匹配一行数据,所以很快

澳门新萄京官方网站 47

  这里的id为主键索引,在t1表唯一只匹配一行数据,查找相当于查询一个常量。

  一张表只有一行数据,相当于京东就你一个用户,快是快,但是实际基本不会用到。

  3.eq_ref

  唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配,常见于主键或唯一索引扫描

澳门新萄京官方网站 48

  类比员工表与部门表中,部门表id最为外键,员工表的外键关联到部门表id,将部门表id建成索引,ceo作为公司员工,在部门表索引时只能查到一个唯一的数据与之匹配,这种情况就是eq_ref,反之其他的部门拥有多名员工,查询就是ref。

  4.ref字段

  非唯一索引扫描,匹配某个单独值的所有行,可能找到符合条件的多个值,所以它是查找和扫描的混合体。

  类比上述的表查找所有的程序员就是ref类型。

澳门新萄京官方网站 49

  5.range

  只检索给定范围行,使用一个索引来选择行,key列显示你使用了哪个索引,一般是where语句出现between,<,>,in等的查询,这种范围扫描优于全标搜索,因为他有起始位置。

澳门新萄京官方网站 50

  6.index

  全索引扫描,优于all因为只扫描了索引,索引往往小于数据的大小。

澳门新萄京官方网站 51

  7.all

  全表查询,匹配到所有行

澳门新萄京官方网站 52

  possible_keys与key字段

  possible_key是smysql判断你可能使用的索引,显示可能应用在这张表的索引,一个或多个,查询涉及到的字段上若存在索引,该索引将被列出,但不一定被查询实际使用

  key字段

  实际中使用的索引,如果为null则没有使用索引

澳门新萄京官方网站 53

  这种情况为查询中使用了覆盖索引,该索引与查询的select重叠。

  key_length字段

  条件越精确,key_length越长,此字段为索引字段最大可能长度,并非实际使用长度,根据定义计算得出,不是通过表内检索得来的值。表示索引中使用的字节数,可通过该列计算查询中使用的索引长度。澳门新萄京官方网站 54

  ref字段

  显示索引的那一列被使用了,如果可能的话是一个常数,那些列或常量被用于查找索引列上的值。

澳门新萄京官方网站 55

  即ref体现了实际被索引的列。

  rows字段

  根据表中信息,估算找到所需记录大致需要读取的行数。

澳门新萄京官方网站 56

  extra

  包含的额外信息,也是重要字段

  1.using filesort

  说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。

澳门新萄京官方网站 57

  这里我们对表中的三列进行了联合索引,前者在orderby排序时使用了外部排序,这大大降低了表的排序性能,因为在使用索引排序时最好是按照它的索引顺序进行排序。

  2.using temporary

  使用了临时表保存中间结果,mysql在对查询结果排序时使用了临时表,常见于排序orderby和分组查询groupby。

澳门新萄京官方网站 58

  在这张表中,创建col1与col2联合索引,groupby跳过col1直接groupby col2,导致断层,需要创建临时的表,mysql对临时表进行一次自己的排序。

  3.using index

澳门新萄京官方网站 59

  所以这是个好字段,使用覆盖索引。

澳门新萄京官方网站 60

  还有一些其他字段不很重要的字段,就不一一解释了,有兴趣自行了解。

澳门新萄京官方网站 61

澳门新萄京官方网站 62

   范围后的索引会失效

澳门新萄京官方网站 63

  左右连接查表的时候给另一边加索引。

  多表连接查询时的优化:

  1.减少join查询的循环总次数,即永远以小结果集驱动大结果集。

  2.优先优化nestedloop的内层循环。

  3.保证join语句中被驱动表join字段已经被索引

  4.无法保证被驱动表的join条件字段被索引且内存资源充足的前提下,不要吝惜joinbuffer的设置。

  如何避免索引失效

  1.全值匹配我最爱

澳门新萄京官方网站 64

  此为完全覆盖和部分覆盖,并且满足左前缀法则,索引正确使用。

  2.最佳左前缀法则

澳门新萄京官方网站 65

  带头大哥不能死,中间兄弟不能断。

  3.不在索引列上做任何操作(计算函数类型转换)、会导致索引失效而转向全表扫描。

澳门新萄京官方网站 66

  索引列上少计算。

  4.存储引擎不能使用索引范围条件右边的列

澳门新萄京官方网站 67

  后一条的key_length长度比之前完全覆盖短,age后索引其实已经失效了,即范围之后全失效。

  5.尽量使用覆盖索引

  即覆盖列与索引列一致,减少select *的使用

澳门新萄京官方网站 68

  6.使用不等于时无法使用索引会导致全表扫描

  7.isnull与isnotnull也无法使用索引

  8.like以通配符开头,mysql索引失效变成全表扫描。

澳门新萄京官方网站 69

  如果需求一定要两边都是百分号查找,那么需用使用覆盖索引来解决索引失效问题。

  9.字符串不加单引号索引失效

澳门新萄京官方网站 70

  10.少用or,用它来连接时会索引失效

  这十条mysql索引优化的口诀是:

  带头大哥不能死,中间兄弟不能断。

  索引列上无计算,like百分加右边。

  字符串里有引号。

澳门新萄京官方网站 71

   mysql的自动调优,因为上述的都是常量级别,所以会被调成覆盖索引类似。

澳门新萄京官方网站 72

  此表1,2是覆盖索引,3是范围索引,所以用到三个索引,因为3type降为range等级,范围之后全失效,所以4未索引。

澳门新萄京官方网站 73

  同理,此处使用了四个索引。

澳门新萄京官方网站 74

  此处,1,2为查找索引,3用于排序索引,所以,严格地认为此处使用了三个索引。

澳门新萄京官方网站 75

  同理,与上述一致,完全一样。

澳门新萄京官方网站 76

  c4空中阁楼,中间兄弟断了,mysql自己优化排序了,糟糕。

澳门新萄京官方网站 77

  1查找索引,23为排序索引。

澳门新萄京官方网站 78

  与上述不同,此处的2,3没有按照索引顺序排列,违背左前缀优先的理念,故出现filesort。

澳门新萄京官方网站 79

  使用两个查找索引和两个排序索引。

澳门新萄京官方网站 80

  上面c2被查询了,所以排序时相当于一个常量,所以并为filesort。

澳门新萄京官方网站 81

  分组之前必排序,所以他与orderby基本一致。

   熟记口诀:

澳门新萄京官方网站 82

   数据库分析

  1.先观察一下sql生产时的慢sql情况

  2.开启慢日志查询,设置阙值,查询过慢的sql挑出来

  3.explain sql分析

  4.show profile

  5.对sql数据库服务器进行参数调优

  总结就是:开启捕获慢查询语句,explain分析定位,show profile查询sql在MySQL服务器里执行细节与生命周期,sql数据库服务器调优。

澳门新萄京官方网站 83

澳门新萄京官方网站 84

  in与exist的转换。

澳门新萄京官方网站 85

   show profile

  查看是否开启了,默认是关闭的

mysql> show variables like 'profiling';
 --------------- ------- 
| Variable_name | Value |
 --------------- ------- 
| profiling     | OFF   |
 --------------- ------- 
1 row in set (0.01 sec)

  所以我们需要打开

mysql> set profiling=on;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show variables like 'profiling';
 --------------- ------- 
| Variable_name | Value |
 --------------- ------- 
| profiling     | ON    |
 --------------- ------- 
1 row in set (0.00 sec)

澳门新萄京官方网站 86

show profile cpu,block io for query (id);

  看出一条sql的完整生命周期。

  比较严重的问题:

  converting head to myisam 查询结果太大,内存不够用存到磁盘上了。

  creating tmp table 创建临时表(拷贝数据到临时表,用完删除)

  copying to tmp table on disk 把内存中临时表复制到磁盘,

  locked 锁住了

本文由澳门新萄京官方网站发布于www.8455.com,转载请注明出处:澳门新萄京官方网站:目录优化剖析,浅谈MySQ

关键词: