【MySQL】5. 性能调优
1. 分表:
1. 全值匹配 : 联合索引的情况下,使用了所有的索引值 查询。
2. 匹配左边:按顺序 索引, 缺少第一个 索引字段的情况下,索引将完全失效。
2. 列前缀匹配: 模糊查询时,使用前缀匹配,因为 只有前缀是 排序的。
3. 精准匹配 和 范围匹配: 并存
4. 索引用于排序: 避免 文件排序(读入内存之后排序)
SELECT * FROM person_info ORDER BY name, birthday, phone_number LIMIT 10;
5.使用联合索引进行排序注意事项:索引的顺序不能颠倒。(在 where 中有 索引优化器,所以可以颠倒,此处不支持)
SELECT * FROM person_info WHERE name = 'A' ORDER BY birthday, phone_number LIMIT 10;
6. WHERE子句中出现非排序使用到的索引列
SELECT * FROM person_info WHERE country = 'China' ORDER BY name LIMIT 10;
7.排序列包含非同一个索引的列
SELECT * FROM person_info ORDER BY name, country LIMIT 10;
8.排序列使用了复杂的表达式
SELECT * FROM person_info ORDER BY UPPER(name) LIMIT 10;
9.用于分组
SELECT name, birthday, phone_number, COUNT(*) FROM person_info GROUP BY name, birthday, phone_number
10. 查询 带非聚集索引 的表,需要做 回表操作:
在索引中查询时,查询到数据之后,会进行 顺序I/O处理, 在回表操作中,每个数据的 记录不在磁盘的连续位置,所以 要做 随机I/O。
如果 二级索引查询到的数据太多,需要到 聚集索引中做 随机I/O。效率很低,不如不用 二级 + 聚集索引。
而,MySQL设计中 存在一个 索引优化器,会对查询的工作量做统计计算操作,来判断 是否使用 二级 + 回表操作。
SELECT * FROM person_info ORDER BY name, birthday, phone_number;
select * 通常考虑 聚集索引。 加 limit *;限制,考虑 二级索引 + 回表操作。
11. 覆盖索引
SELECT name, birthday, phone_number FROM person_info WHERE name > 'Asa' AND name < 'Barlow'
为了彻底告别回表操作带来的性能损耗,我们建议:最好在查询列表里只包含索引列
12. 如何挑选索引:
1. 只为用于搜索、排序或分组的列创建索引 :出现在 where 、 group by 、 orader by 的列需要,出现在select 的不用。
2. 考虑列的基数: 可选的值,越多,越值得。
3. 索引列的类型尽量小 查询会快、占用空间 会少(主键的情况下,二级索引中也能节省空间)
4. 索引字符串值的前缀:
CREATE TABLE person_info(
name VARCHAR(100) NOT NULL,
birthday DATE NOT NULL,
phone_number CHAR(11) NOT NULL,
country varchar(100) NOT NULL,
KEY idx_name_birthday_phone_number (name(10), birthday, phone_number)
);
2. 参数配置:
key_buffer_size # 用于索引的缓冲区大小
query_cache_size # 查询缓存,不开启请设为0
tmp_table_size # 临时表缓存大小
innodb_buffer_pool_size # Innodb 缓冲区大小
innodb_log_buffer_size # Innodn 日志缓冲区大小
sort_buffer_size # 连接数,每个线程排序的缓冲大小
read_buffer_size # 连接数,读入缓冲区大小
read_rnd_buffer_size # 连接数,随机读取缓冲区大小
join_buffer_size # 连接数,关联表缓存大小
thread_stack # 连接数,每个线程的堆栈大小
binlog_cache_size # 连接数,(KB)二进制日志缓存大小(4096的倍数)
thread_cache_size # 线程池大小
table_open_cache # 表缓存(最大不要超过2048)
max_connections # 最大连接数
continue…
参考文章:
https://www.cnblogs.com/miketwais/articles/mysql_partition.html