利用索引优化MySQL查询性能

MySQL的系统架构如图所示:


image.png

在解分析查询缓存的性能优化后,我们还要考虑数据在磁盘上的组织和使用恰当的index和SQL语句来提高性能的问题。MySQL的索引数据结构采用的是B+TREE树:


image.png

只有sql语句的写法和index序列的相匹配才能充分发挥index的作用。

查看MySQL中index情况的工具

  1. explain
MariaDB [hellodb]> explain select * from students where age=20;
+------+-------------+----------+------+---------------+---------+---------+-------+------+-------+
| id   | select_type | table    | type | possible_keys | key     | key_len | ref   | rows | Extra |
+------+-------------+----------+------+---------------+---------+---------+-------+------+-------+
|    1 | SIMPLE      | students | ref  | idx_age       | idx_age | 1       | const |    2 |       |
+------+-------------+----------+------+---------------+---------+---------+-------+------+-------+
  1. 变量userstate
SET GLOBAL userstat=1;    # or add it to my.cnf
MariaDB [(none)]> SHOW INDEX_STATISTICS;
+--------------+------------+------------+-----------+
| Table_schema | Table_name | Index_name | Rows_read |
+--------------+------------+------------+-----------+
| hellodb      | students   | PRIMARY    |         2 |
+--------------+------------+------------+-----------+

索引设计原则

  1. 独立的使用列:应避免使用index参与运算,表达式和函数的操作,在where语句中将索引列单独放在比较符号一侧
# 这样的语句是无法使用索引的
MariaDB [hellodb]> explain select * from students where stuid + 10 > 20;
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | students | ALL  | NULL          | NULL | NULL    | NULL |   25 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
  1. 左前缀索引:构建指定index字段左侧的字符数,字符数可以通过不重复的索引值和数据表的记录总数的比值,大概在90%即可
create index idx_name on students(name(10));
MariaDB [hellodb]> explain select * from students where name like 's%';
+------+-------------+----------+-------+---------------+----------+---------+------+------+-------------+
| id   | select_type | table    | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+------+-------------+----------+-------+---------------+----------+---------+------+------+-------------+
|    1 | SIMPLE      | students | range | idx_name      | idx_name | 32      | NULL |    4 | Using where |
+------+-------------+----------+-------+---------------+----------+---------+------+------+-------------+
  1. 多列索引:AND操作时更适合使用多列索引,而不是为每个列创建单独索引
create index idx_name_age on students(name,age);
MariaDB [hellodb]> explain select * from students where name like 's%' and age=20;
+------+-------------+----------+-------+---------------+--------------+---------+------+------+-----------------------+
| id   | select_type | table    | type  | possible_keys | key          | key_len | ref  | rows | Extra                 |
+------+-------------+----------+-------+---------------+--------------+---------+------+------+-----------------------+
|    1 | SIMPLE      | students | range | idx_name_age  | idx_name_age | 153     | NULL |    4 | Using index condition |
+------+-------------+----------+-------+---------------+--------------+---------+------+------+-----------------------+
  1. 选择合适的index顺序:无排序和分组时,将选择性最高的放在左侧
  2. 列中有NULL值,不要设置index
  3. 尽量使用短索引
  4. 对于经常在where子句使用的列,最好设置索引
  5. 对于多个列where或order by子句,应该建立复合索引
  6. 对于like子句,以%或_开头的不会使用index
MariaDB [hellodb]> explain select * from students where name like '%s%';
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | students | ALL  | NULL          | NULL | NULL    | NULL |   25 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
MariaDB [hellodb]> explain select * from students where name like '%s';
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | students | ALL  | NULL          | NULL | NULL    | NULL |   25 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
  1. 不要使用not in 和<>操作

SQL语句建议

  1. 尽量不要使用*,要写全字段名
  2. join效率远大于子查询
  3. 多表连接时,小表join大表
  4. 有大量记录的表分页时使用limit
  5. 对于经常使用的查询,可以开启缓存
  6. 使用explain和profile分析查询语句
  7. 查看慢查询日志,找出执行时间长的sql语句进行优化

分析查询语句性能

慢查询日式

- slow_query_log=ON|OFF #开启或关闭慢查询
- long_query_time=N #慢查询的阀值,单位秒, 
- slow_query_log_file=HOSTNAME-slow.log #慢查询日志文件
- log_slow_filter = admin,filesort,filesort_on_disk,full_join,full_scan,
query_cache,query_cache_miss,tmp_table,tmp_table_on_disk # 慢查询日志记录的类别
- log_queries_not_using_indexes=ON #记录未使用index的查询,默认OFF,即不记录
- log_slow_rate_limit = 1 #多少次查询才记录,mariadb特有
- log_slow_verbosity= Query_plan,explain #记录内容格式

利用profile进行分析

MariaDB [hellodb]> show variables like "%profiling";
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| have_profiling | YES   |
| profiling      | OFF   |
+----------------+-------+
2 rows in set (0.00 sec)

MariaDB [hellodb]> set profiling=ON;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> select sleep(1) from teachers;
+----------+
| sleep(1) |
+----------+
|        0 |
|        0 |
|        0 |
|        0 |
+----------+
4 rows in set (4.01 sec)

MariaDB [hellodb]> show profiles;
+----------+------------+-------------------------------+
| Query_ID | Duration   | Query                         |
+----------+------------+-------------------------------+
|        1 | 4.00351854 | select sleep(1) from teachers |
+----------+------------+-------------------------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000086 |
| checking permissions | 0.000009 |
| Opening tables       | 0.000020 |
| After opening tables | 0.000005 |
| System lock          | 0.000004 |
| Table lock           | 0.000002 |
| After table lock     | 0.000005 |
| init                 | 0.000013 |
| optimizing           | 0.000008 |
| statistics           | 0.000029 |
| preparing            | 0.000009 |
| executing            | 0.000003 |
| Sending data         | 0.000030 |
| User sleep           | 1.000813 |
| User sleep           | 1.000540 |
| User sleep           | 1.000857 |
| User sleep           | 1.001032 |
| end                  | 0.000018 |
| query end            | 0.000010 |
| closing tables       | 0.000006 |
| freeing items        | 0.000004 |
| updating status      | 0.000015 |
| cleaning up          | 0.000002 |
+----------------------+----------+
最后编辑于 :
©著作权归作者所有,转载或内容合作请联系作者
【社区内容提示】社区部分内容疑似由AI辅助生成,浏览时请结合常识与多方信息审慎甄别。
平台声明:文章内容(如有图片或视频亦包括在内)由作者上传并发布,文章内容仅代表作者本人观点,简书系信息发布平台,仅提供信息存储服务。

友情链接更多精彩内容