<MySQL8 Cookbook > - 数据库优化
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了<MySQL8 Cookbook > - 数据库优化,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含4431字,纯文字阅读大概需要7分钟。
内容图文
![<MySQL8 Cookbook > - 数据库优化](/upload/InfoBanner/zyjiaocheng/504/9f9b70807f9740d99e8b84d764cb30b0.jpg)
EXPLAIN
select dept_name from dept_emp join employees on dept_emp.emp_no=employees.emp_no join departments on departments.dept_no=dept_emp.dept_no where employees.first_name=‘Aamer‘;
EXPLAIN format=JSON select dept_name from dept_emp join employees on dept_emp.emp_no=employees.emp_no join departments on departments.dept_no=dept_emp.dept_no where employees.first_name=‘Aamer‘;
mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
| 30 |
+-----------------+
1 row in set (0.01 sec)
mysql> explain format=json for connection 30\G
ERROR 3012 (HY000): EXPLAIN FOR CONNECTION command is supported only for SELECT/UPDATE/INSERT/DELETE/REPLACE
mysql> pager grep rows
mysql> SELECT e.emp_no,salary from salaries s join employees e on s.emp_no=e.emp_no where first_name=‘Adam‘;
2384 rows in set (0.24 sec)
mysql> pager
Default pager wasn‘t set, using stdout.
mysqlslap
mysqlslap 对于模拟多个用户同时对 MySQL 发起“进攻”提供了方便。同时详细的提供了“高负荷*** MySQL”的详细数据报告。
#mysqlslap -uroot -p --create-schema=employees --query="SELECT e.emp_no,salary from salaries s join employees e on s.emp_no=e.emp_no where first_name=‘Adam‘;" -c 100 -i 100
Enter password:
Benchmark
Average number of seconds to run all queries: 0.995 seconds
Minimum number of seconds to run all queries: 0.876 seconds
Maximum number of seconds to run all queries: 1.155 seconds
Number of clients running queries: 100
Average number of queries per client: 1
#mysqlslap -uroot -p --auto-generate-sql --concurrency=50,100 --number-of-queries=1000 -i 5 --debug-info
Enter password:
Benchmark
Average number of seconds to run all queries: 0.148 seconds
Minimum number of seconds to run all queries: 0.148 seconds
Maximum number of seconds to run all queries: 0.148 seconds
Number of clients running queries: 50
Average number of queries per client: 20
Benchmark
Average number of seconds to run all queries: 0.143 seconds
Minimum number of seconds to run all queries: 0.143 seconds
Maximum number of seconds to run all queries: 0.143 seconds
Number of clients running queries: 100
Average number of queries per client: 10
mysqlslap -uroot -p --auto-generate-sql --only-print
#mysqlslap -uroot -p --concurrency=50,100,200 --iterations=1 --number-int-cols=4 --number-char-cols=35 \
--auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=innodb --number-of-queries=200
Enter password:
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.042 seconds
Minimum number of seconds to run all queries: 0.042 seconds
Maximum number of seconds to run all queries: 0.042 seconds
Number of clients running queries: 50
Average number of queries per client: 4
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.022 seconds
Minimum number of seconds to run all queries: 0.022 seconds
Maximum number of seconds to run all queries: 0.022 seconds
Number of clients running queries: 100
Average number of queries per client: 2
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.515 seconds
Minimum number of seconds to run all queries: 0.515 seconds
Maximum number of seconds to run all queries: 0.515 seconds
Number of clients running queries: 200
Average number of queries per client: 1
#mysqlslap -uroot -p --delimiter=";" --create="CREATE TABLE a (b int);INSERT INTO a VALUES (23)" --query="SELECT * FROM a" --concurrency=50 --iterations=200
Enter password:
Benchmark
Average number of seconds to run all queries: 0.002 seconds
Minimum number of seconds to run all queries: 0.002 seconds
Maximum number of seconds to run all queries: 0.007 seconds
Number of clients running queries: 50
Average number of queries per client: 1
mysqlslap --concurrency=5 --iterations=5 --query=query.sql --create=create.sql --delimiter=";"
参考:https://my.oschina.net/moooofly/blog/152547
where、order by、group by、表连接都会使用到索引
主键的选择:
1、唯一非空
2、尽量的小,因为所有的二级索引都会存储主键
3、选择一个单调自增的键,物理行是按照主键排序的
alter table employees add [unqiue] index idx_name(first_name(10));
alter table employees drop index idx_name;
<MySQL8 Cookbook > - 数据库优化
标签:方便 数据 imu iter mysqlsla comm client net def
本文系统来源:http://blog.51cto.com/395469372/2333230
内容总结
以上是互联网集市为您收集整理的<MySQL8 Cookbook > - 数据库优化全部内容,希望文章能够帮你解决<MySQL8 Cookbook > - 数据库优化所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。