速记李博Alex
MySQL数据库优化是多⽅⾯的，原则是减少系统的瓶颈，减少资源的占⽤，增加系统的反应速度。 在MySQL中，可以通过SHOW STATUS 语句查询⼀些MySQL的性能参数。如查询连接次数，可以执⾏如下语句：
SHOW STATUS LIKE 'Connections';
如果要查询慢查询次数，可以执⾏如下语句：
SHOW STATUS LIKE 'Slow_queries';
⼀些常⽤的性能参数如下：
- Connections：连接MySQL服务器的次数；
- Uptime：MySQL服务器的上线时间；
- Slow_queries：慢查询的次数；
- Com_select：查询操作的次数(select可以换insert，update，delete，分别查询插⼊、更新、删除的次数)。
MySQL数据库优化可以从三⽅⾯考虑，分别是优化查询、优化数据库结构、优化MySQL数据库。
优化查询
分析查询语句。
利⽤索引来加快查询速度。
优化⼦查询。
分析查询语句
通过对查询语句的分析，可以了解查询语句的执⾏情况。MySQL中提供了EXPLAIN 和DESCRIBE 来分析查询语句。
⽰例如下：
EXPLAIN SELECT * FROM `user` WHERE username = 'admin';
结果如图⽰：
⼏个参数说明⼀下：
- id：SELECT识别 符。这是SELECT的查询序列号。
- select_type：表⽰SELECT语句的类型。
- table：表⽰查询的表。
- type：表⽰表的连接类型。ALL表⽰ 进⾏了全表扫描。这是最坏的结果。
- possible_keys：指出MySQL能使⽤哪个索引在该表中到⾏。如果为NULL，表⽰没有相关索引。
- key：表⽰查询实际使⽤到的索引。
- key_len：表⽰MySQL选择的索引字段按字节计算的长度。
- ref：表⽰使⽤哪个列或常数与索引⼀起来查询记录。
- rows：显⽰MySQL在表中进⾏查询时必须检查的⾏数。
-
Extra：表⽰MySQL在处理查询时的详细信息。
利⽤索引来加快查询速度
MySQL中提⾼性能的⼀个最有效的⽅式就是对数据表设计合理的索引。索引提供了⾼效的查询数据的⽅法，并且加快了查询速度。如分析查询语句中，显⽰的type为ALL 就是表⽰进⾏了全表扫描，没有使⽤索引的情况。对username 添加⼀个名为username_index 普通索引，再来分析查询语句，结果如图⽰：
type类型变为ref，这表⽰从表中读取所有匹配的⾏，⽤于索引既不是UNIQUE也不是PRIMARY KEY 的情况，或者查询中使⽤了索引列左⼦集，即索引中左边的部分列组合。
使⽤索引有⼏种特殊情况，在这些情况下，有可能使⽤带有索引的字段查询时，索引并没有起效，下⾯介绍⼀下这⼏种特殊的情况。
使⽤LIKE关键字的查询语句。
在使⽤LIKE关键字进⾏查询的语句中，如果匹配字符串的第⼀个字符为“%”，索引不会起作⽤。只有“%”不在第⼀个位置，索引才会起作⽤。
使⽤多列索引的查询语句。
MySQL可以为多个字段创建索引。⼀个索引可以包括16个字段。对于多列索引，只有查询条件中使⽤了这些字段中的第⼀个字段时，索引才会起作⽤。
使⽤OR关键字的查询语句。
查询语句的查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，查询中才使⽤索引。否则，查询将不使⽤索引。
优化⼦查询
⼦查询可以⼀次性完成很多逻辑上需要多个步骤才能完成的SQL操作。⼦查询虽然可以使查询语句很灵活，但是执⾏效率不⾼。执⾏⼦查询时，MySQL需要为内层查询语句的查询结果建⽴⼀个临时表。然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。因此，⼦查询的速度会受到⼀定的影响。
在MySQL中，可以使⽤连接(JOIN)查询来替代⼦查询。连接查询不需要建⽴临时表，其速度⽐⼦查询要快。
优化数据库结构
高速查询合理的数据库结构不仅可以使数据库占⽤更⼩的磁盘空间，⽽且能够使查询速度更快。数据库结构的设计，需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多⽅⾯的内容。
将字段很多的表分解成多个表
对于字段很多的表，如果有些字段的使⽤频率很低，可以将这些字段分离出来形成新表。因为当⼀个表的数据量很⼤时，会由于使⽤频率低的字段存在⽽变慢。
增加中间表
对于需要经常联合查询的表，可以建⽴中间表以提⾼查询效率。通过建⽴中间表，把需要经常联合查询的数据插⼊到中间表中，然后将原来的联合查询改为对中间表的查询，以此来提⾼查询效率。
增加冗余字段
设计数据库表时应尽量遵循范式理论的规约，尽可能减少冗余字段，让数据库设计看起来精致、优雅。但是，合理地加⼊冗余字段可以提⾼查询速度。
优化插⼊记录的速度
插⼊记录时，影响插⼊速度的主要是索引、唯⼀性校验、⼀次插⼊记录条数等。可以分别对此进⾏针对性优化。
分析表、检查表和优化表
MySQL提供了分析表、检查表和优化表的语句。分析表主要是分析关键字的分布；检查表主要是检查表是否存在错误；优化表主要是消除删除或者更新造成的空间浪费。
分析表：ANALYZE TABLE 表名；
检查表：CHECK TABLE 表名；
优化表：OPTIMIZE TABLE 表名；(只能优化表中的VARCHAR、BLOB或TEXT类型的字段)
上述的这三个⽅法，数据库系统都会⾃动对表加⼀个只读锁。在分析表期间，只能读取表中的记录，不能更新和插⼊记录。在多数场景下，不需要使⽤优化表，即使对可变长度的⾏进⾏了⼤量的更新，也不需要经常运⾏，每周⼀次或每⽉⼀次即可，并且只需要对特定的表运⾏。
优化MySQL服务器
优化服务器硬件
服务器的硬件性能直接决定着MySQL数据库的性能。硬件的性能瓶颈，也决定了数据库的运⾏速度和效率。⼀般常⽤的优化服务器硬件的⽅法如下：
1. 配置较⼤的内存。
2. 配置⾼速磁盘系统，以减少读盘的等待时间，提⾼响应速度。
3. 合理分布磁盘I/O，把磁盘I/O分散在多个设备上，以减少资源竞争，提⾼并⾏操作能⼒。
4. 配置多处理器，MySQL是多线程的数据库，多处理器可同时执⾏多个线程。
优化MySQL的参数
通过优化MySQL的参数可以提⾼资源利⽤率，从⽽达到提⾼MySQL服务器性能的⽬的。
MySQL服务的配置参数都在myf或者my.ini⽂件的[MySQLd]组中。其中：
key_buffer_size：表⽰索引缓冲区的⼤⼩。索引缓冲区所有的线程共享。合理的缓冲区⼤⼩可以更好处理索引。
table_cache：表⽰同时打开表的个数。
query_cache_size：表⽰查询缓冲区的⼤⼩。
innodb_buffer_pool_size：表⽰InnoDB类型的表和索引的最⼤缓存。值越⼤，查询的速度就会越快。
max_connections：表⽰最⼤连接数，该值不是越⼤越好，过多的连接数可能会导致MySQL服务器僵死。
……其它略。
补充
1.索引是不是越多越好？
合理的索引可以提⾼查询速度，但是索引也不是越多越好。在执⾏插⼊语句前，MySQL要为新插⼊的记录建⽴索引，所以过多的索引会导致插⼊操作变慢。
2. 如何使⽤查询缓冲区？
查询缓冲区可以提⾼查询速度，但是只适合查询语句⽐较多、更新语句⽐较少的情况，具体⽅式参照优化MySQL参数。
发布评论