mysql数据库优化？mysql数据库可视化软件

很多朋友对于mysql数据库优化和mysql数据库可视化软件不太懂，今天就由小编来为大家分享，希望可以帮助到大家，下面一起来看看吧！

优化MYSQL数据库的方法

在开始演示之前，我们先介绍下两个概念。

概念一，数据的可选择性基数，也就是常说的cardinality值。

查询优化器在生成各种执行计划之前，得先从统计信息中取得相关数据，这样才能估算每步操作所涉及到的记录数，而这个相关数据就是cardinality。简单来说，就是每个值在每个字段中的唯一值分布状态。

比如表t1有100行记录，其中一列为f1。f1中唯一值的个数可以是100个，也可以是1个，当然也可以是1到100之间的任何一个数字。这里唯一值越的多少，就是这个列的可选择基数。

那看到这里我们就明白了，为什么要在基数高的字段上建立索引，而基数低的的字段建立索引反而没有全表扫描来的快。当然这个只是一方面，至于更深入的探讨就不在我这篇探讨的范围了。

概念二，关于HINT的使用。

这里我来说下HINT是什么，在什么时候用。

HINT简单来说就是在某些特定的场景下人工协助MySQL优化器的工作，使她生成最优的执行计划。一般来说，优化器的执行计划都是最优化的，不过在某些特定场景下，执行计划可能不是最优化。

比如：表t1经过大量的频繁更新操作，（UPDATE,DELETE,INSERT），cardinality已经很不准确了，这时候刚好执行了一条SQL，那么有可能这条SQL的执行计划就不是最优的。为什么说有可能呢？

来看下具体演示

譬如，以下两条SQL，

A：

select* from t1 where f1= 20;

B：

select* from t1 where f1= 30;

如果f1的值刚好频繁更新的值为30，并且没有达到MySQL自动更新cardinality值的临界值或者说用户设置了手动更新又或者用户减少了sample page等等，那么对这两条语句来说，可能不准确的就是B了。

这里顺带说下，MySQL提供了自动更新和手动更新表cardinality值的方法，因篇幅有限，需要的可以查阅手册。

那回到正题上，MySQL 8.0带来了几个HINT，我今天就举个index_merge的例子。

示例表结构：

mysql> desc t1;+------------+--------------+------+-----+---------+----------------+| Field| Type| Null| Key| Default| Extra|+------------+--------------+------+-----+---------+----------------+| id| int(11)| NO| PRI| NULL| auto_increment|| rank1| int(11)| YES| MUL| NULL||| rank2| int(11)| YES| MUL| NULL||| log_time| datetime| YES| MUL| NULL||| prefix_uid| varchar(100)| YES|| NULL||| desc1| text| YES|| NULL||| rank3| int(11)| YES| MUL| NULL||+------------+--------------+------+-----+---------+----------------+7 rows in set(0.00 sec)

表记录数：

mysql> select count(*) from t1;+----------+| count(*)|+----------+| 32768|+----------+1 row in set(0.01 sec)

这里我们两条经典的SQL：

SQL C：

select* from t1 where rank1= 1 or rank2= 2 or rank3= 2;

SQL D：

select* from t1 where rank1=100 and rank2=100 and rank3=100;

表t1实际上在rank1,rank2,rank3三列上分别有一个二级索引。

那我们来看SQL C的查询计划。

显然，没有用到任何索引，扫描的行数为32034，cost为3243.65。

mysql> explain format=json select* from t1 where rank1=1 or rank2= 2 or rank3= 2\G*************************** 1. row***************************EXPLAIN:{"query_block":{"select_id": 1,"cost_info":{"query_cost":"3243.65"},"table":{"table_name":"t1","access_type":"ALL","possible_keys": ["idx_rank1","idx_rank2","idx_rank3" ],"rows_examined_per_scan": 32034,"rows_produced_per_join": 115,"filtered":"0.36","cost_info":{"read_cost":"3232.07","eval_cost":"11.58","prefix_cost":"3243.65","data_read_per_join":"49K"},"used_columns": ["id","rank1","rank2","log_time","prefix_uid","desc1","rank3" ],"attached_condition":"((`ytt`.`t1`.`rank1`= 1) or(`ytt`.`t1`.`rank2`= 2) or(`ytt`.`t1`.`rank3`= 2))"}}}1 row in set, 1 warning(0.00 sec)

我们加上hint给相同的查询，再次看看查询计划。

这个时候用到了index_merge,union了三个列。扫描的行数为1103，cost为441.09，明显比之前的快了好几倍。

mysql> explain format=json select/*+ index_merge(t1)*/* from t1 where rank1=1 or rank2= 2 or rank3= 2\G*************************** 1. row***************************EXPLAIN:{"query_block":{"select_id": 1,"cost_info":{"query_cost":"441.09"},"table":{"table_name":"t1","access_type":"index_merge","possible_keys": ["idx_rank1","idx_rank2","idx_rank3" ],"key":"union(idx_rank1,idx_rank2,idx_rank3)","key_length":"5,5,5","rows_examined_per_scan": 1103,"rows_produced_per_join": 1103,"filtered":"100.00","cost_info":{"read_cost":"330.79","eval_cost":"110.30","prefix_cost":"441.09","data_read_per_join":"473K"},"used_columns": ["id","rank1","rank2","log_time","prefix_uid","desc1","rank3" ],"attached_condition":"((`ytt`.`t1`.`rank1`= 1) or(`ytt`.`t1`.`rank2`= 2) or(`ytt`.`t1`.`rank3`= 2))"}}}1 row in set, 1 warning(0.00 sec)

我们再看下SQL D的计划：

不加HINT，

mysql> explain format=json select* from t1 where rank1=100 and rank2=100 and rank3=100\G*************************** 1. row***************************EXPLAIN:{"query_block":{"select_id": 1,"cost_info":{"query_cost":"534.34"},"table":{"table_name":"t1","access_type":"ref","possible_keys": ["idx_rank1","idx_rank2","idx_rank3" ],"key":"idx_rank1","used_key_parts": ["rank1" ],"key_length":"5","ref": ["const" ],"rows_examined_per_scan": 555,"rows_produced_per_join": 0,"filtered":"0.07","cost_info":{"read_cost":"478.84","eval_cost":"0.04","prefix_cost":"534.34","data_read_per_join":"176"},"used_columns": ["id","rank1","rank2","log_time","prefix_uid","desc1","rank3" ],"attached_condition":"((`ytt`.`t1`.`rank3`= 100) and(`ytt`.`t1`.`rank2`= 100))"}}}1 row in set, 1 warning(0.00 sec)

加了HINT，

mysql> explain format=json select/*+ index_merge(t1)*/* from t1 where rank1=100 and rank2=100 and rank3=100\G*************************** 1. row***************************EXPLAIN:{"query_block":{"select_id": 1,"cost_info":{"query_cost":"5.23"},"table":{"table_name":"t1","access_type":"index_merge","possible_keys": ["idx_rank1","idx_rank2","idx_rank3" ],"key":"intersect(idx_rank1,idx_rank2,idx_rank3)","key_length":"5,5,5","rows_examined_per_scan": 1,"rows_produced_per_join": 1,"filtered":"100.00","cost_info":{"read_cost":"5.13","eval_cost":"0.10","prefix_cost":"5.23","data_read_per_join":"440"},"used_columns": ["id","rank1","rank2","log_time","prefix_uid","desc1","rank3" ],"attached_condition":"((`ytt`.`t1`.`rank3`= 100) and(`ytt`.`t1`.`rank2`= 100) and(`ytt`.`t1`.`rank1`= 100))"}}}1 row in set, 1 warning(0.00 sec)

对比下以上两个，加了HINT的比不加HINT的cost小了100倍。

总结下，就是说表的cardinality值影响这张的查询计划，如果这个值没有正常更新的话，就需要手工加HINT了。相信MySQL未来的版本会带来更多的HINT。

Mysql数据库优化可以从哪几个方面优化

1、选取最适用的字段属性

MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。

2、使用连接（JOIN）来代替子查询(Sub-Queries)

MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。例如，我们要将客户基本信息表中没有任何订单的客户删除掉，就可以利用子查询先从销售信息表中将所有发出订单的客户ID取出来，然后将结果传递给主查询。

3、使用联合(UNION)来代替手动创建的临时表

MySQL从4.0的版本开始支持union查询，它可以把需要使用临时表的两条或更多的select查询合并的一个查询中。在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。使用union来创建查询的时候，我们只需要用UNION作为关键字把多个select语句连接起来就可以了，要注意的是所有select语句中的字段数目要想同。下面的例子就演示了一个使用UNION的查询。

4、事务

尽管我们可以使用子查询（Sub-Queries）、连接（JOIN）和联合（UNION）来创建各种各样的查询，但不是所有的数据库操作都可以只用一条或少数几条SQL语句就可以完成的。更多的时候是需要用到一系列的语句来完成某种工作。但是在这种情况下，当这个语句块中的某一条语句运行出错的时候，整个语句块的操作就会变得不确定起来。设想一下，要把某个数据同时插入两个相关联的表中，可能会出现这样的情况：第一个表中成功更新后，数据库突然出现意外状况，造成第二个表中的操作没有完成，这样，就会造成数据的不完整，甚至会破坏数据库中的数据。要避免这种情况，就应该使用事务，它的作用是：要么语句块中每条语句都操作成功，要么都失败。换句话说，就是可以保持数据库中数据的一致性和完整性。事物以BEGIN关键字开始，COMMIT关键字结束。在这之间的一条SQL操作失败，那么，ROLLBACK命令就可以把数据库恢复到BEGIN开始之前的状态。

5、锁定表

尽管事务是维护数据库完整性的一个非常好的方法，但却因为它的独占性，有时会影响数据库的性能，尤其是在很大的应用系统中。由于在事务执行的过程中，数据库将会被锁定，因此其它的用户请求只能暂时等待直到该事务结束。如果一个数据库系统只有少数几个用户来使用，事务造成的影响不会成为一个太大的问题；但假设有成千上万的用户同时访问一个数据库系统，例如访问一个电子商务网站，就会产生比较严重的响应延迟。其实，有些情况下我们可以通过锁定表的方法来获得更好的性能。

6、使用外键

锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。这个时候我们就可以使用外键。

7、使用索引

索引是提高数据库性能的常用方法，它可以令数据库服务器以比没有索引快得多的速度检索特定的行，尤其是在查询语句当中包含有MAX(),MIN()和ORDERBY这些命令的时候，性能提高更为明显。

希望可以帮到你，谢谢！

mysql数据库的优化方法

我们都知道，服务器数据库的开发一般都是通过java或者是PHP语言来编程实现的，而为了提高我们数据库的运行速度和效率，数据库优化也成为了我们每日的工作重点，今天，回龙观IT培训就一起来了解一下mysql服务器数据库的优化方法。

为什么要了解索引

真实案例

案例一：大学有段时间学习爬虫，爬取了知乎300w用户答题数据，存储到mysql数据中。那时不了解索引，一条简单的“根据用户名搜索全部回答的sql“需要执行半分钟左右，完全满足不了正常的使用。

案例二：近线上应用的数据库频频出现多条慢sql风险提示，而工作以来，对数据库优化方面所知甚少。例如一个用户数据页面需要执行很多次数据库查询，性能很慢，通过增加超时时间勉强可以访问，但是性能上需要优化。

索引的优点

合适的索引，可以大大减小mysql服务器扫描的数据量，避免内存排序和临时表，提高应用程序的查询性能。

索引的类型

mysql数据中有多种索引类型，primarykey，unique，normal，但底层存储的数据结构都是BTREE;有些存储引擎还提供hash索引，全文索引。

BTREE是常见的优化要面对的索引结构，都是基于BTREE的讨论。

B-TREE

查询数据简单暴力的方式是遍历所有记录;如果数据不重复，就可以通过组织成一颗排序二叉树，通过二分查找算法来查询，大大提高查询性能。而BTREE是一种更强大的排序树，支持多个分支，高度更低，数据的插入、删除、更新更快。

现代数据库的索引文件和文件系统的文件块都被组织成BTREE。

btree的每个节点都包含有key，data和只想子节点指针。

btree有度的概念d>=1。假设btree的度为d，则每个内部节点可以有n=[d+1，2d+1)个key，n+1个子节点指针。树的大高度为h=Logb[(N+1)/2]。

索引和文件系统中，B-TREE的节点常设计成接近一个内存页大小(也是磁盘扇区大小)，且树的度非常大。这样磁盘I/O的次数，就等于树的高度h。假设b=100，一百万个节点的树，h将只有3层。即，只有3次磁盘I/O就可以查找完毕，性能非常高。

索引查询

建立索引后，合适的查询语句才能大发挥索引的优势。

另外，由于查询优化器可以解析客户端的sql语句，会调整sql的查询语句的条件顺序去匹配合适的索引。

好了，本文到此结束，如果可以帮助到大家，还望关注本站哦！