Práticas de otimização de banco de dados MySQL em ambiente single-node

Database optimization has many things to talk about, and can be divided into two stages according to the supported data volume: single machine database and sharding and table splitting, the former can generally support500W or10G within the data, if the value exceeds this, it is necessary to consider sharding and table splitting. In addition, in the interviews of general large enterprises, they often start from a single machine database and gradually ask about sharding and table splitting, interspersed with many database optimization issues. This article attempts to describe some practical experiences in single machine database optimization, the database is based on mysql, and welcome corrections if there are any unreasonable places.

1、Table Structure Optimization

When starting an application, the design of the database table structure often affects the performance of the application later, especially the performance after the user volume increases. Therefore, table structure optimization is an important step.

11、Character Set

Generally speaking, try to choose UTF-8Although GBK is better than UTF in storing data in the middle-8uses less storage space, but UTF-8Compatible with various languages, in fact, we do not need to sacrifice scalability for this storage space. In fact, if you need to convert from GBK to UTF later-8The cost to be paid is very high, and data migration is required, while the storage space can be completely solved by spending money to expand the hard disk.

12、Primary Key

When using MySQL's innodb, the underlying storage model of innodb is B+The tree, which uses the primary key as a clustered index, uses the inserted data as leaf nodes, and can quickly find leaf nodes through the primary key, thus quickly obtaining records. Therefore, when designing the table, it is necessary to add a primary key, and it is better to be auto-incrementing. Because auto-incrementing primary keys can let the inserted data be inserted into the underlying B in order of the primary key.+In the leaf nodes of the tree, since they are ordered, this insertion almost does not require moving existing other data, so the insertion efficiency is very high. If the primary key is not auto-incrementing, then each primary key value is approximately random, and it may be necessary to move a large amount of data to ensure B+The characteristics of the tree, which increase unnecessary costs.

13、字段

131、已建立索引的字段必须加上not null约束，并设置默认值

132、不建议使用float、double来存小数，防止精度损失，建议使用decimal

133、不建议使用Text/blob para armazenar dados em grande quantidade，porque a leitura e escrita de grandes textos causará uma grande I/O overhead，ao mesmo tempo que consome o cache do mysql，sob alta concorrência, reduz significativamente a capacidade de saída do banco de dados，portanto, é recomendável armazenar dados de texto grandes em sistemas de armazenamento de arquivos especializados，no mysql, armazene apenas o endereço de acesso ao arquivo，por exemplo, artigos de blog podem ser armazenados em arquivos，no mysql, armazene apenas o endereço relativo do arquivo。

134、varchar类型长度建议不要超过8K。

135、建议使用Datetime而不是timestamp，尽管Datetime ocupa8bytes，enquanto o timestamp ocupa4bytes，mas o último deve garantir que não esteja vazio e que seja sensível ao fuso horário.

136、建议表中增加gmt_create和gmt_modified两个字段，用来记录数据创建的修改时间。这两个字段建立的原因是方便查问题。

14、索引创建

141、在这个阶段由于对业务并不了解，所以尽量不要盲目加索引，只为一些一定会用到索引的字段加普通索引。

142、创建innodb单列索引的长度不要超过767bytes，se exceder, usará o anterior255bytes como prefixo do índice

143、创建innodb组合索引的各列索引长度不要超过767bytes，一共加起来不要超过3072bytes

2、SQL优化

一般来说sql就那么几种：基本的增删改查，分页查询，范围查询，模糊搜索，多表连接

21、基本查询

一般查询需要走索引，如果没有索引建议修改查询，把有索引的那个字段加上，如果由于业务场景没法使用这个字段，那么需要看这个查询调用量大不大，如果大，比如每天调用10W+，这就需要新增索引，如果不大，比如每天调用100+，则可以考虑保持原样。另外，select * Tente usar o mínimo possível，coloque o que precisar no comando sql，e ignore os campos desnecessários，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser que você precise deles，a não ser chega a/O e espaço de memória.

22、高效分页

limit m,n其实质就是先执行limit m+n，然后从第m行取n行，这样当limit翻页越往后翻m越大，性能越低。比如

select * from A limit 100000,10，这种sql语句的性能是很差的，建议改成下面的版本:

selec id,name,age from A where id >=(select id from A limit 100000,1) limit 10

23、consulta de faixa

A consulta de faixa inclui between, maior que, menor que e in. A consulta in do MySQL tem uma restrição de quantidade, se a quantidade for pequena, pode seguir a consulta de índice, se a quantidade for grande, torna-se uma busca completa da tabela. E between, maior que, menor que, essas consultas não seguem o índice, então é melhor colocá-las após as condições de consulta que seguem o índice.

24、buscalike

O uso de like %name% dessa maneira não seguirá o índice, equivalente a uma busca completa da tabela, quando a quantidade de dados for pequena, não haverá muitos problemas, mas quando a quantidade de dados for grande, o desempenho cairá muito, é recomendável usar um mecanismo de busca para substituir essa busca abrangente quando a quantidade de dados for grande, se for impossível, também é necessário adicionar uma condição que possa seguir o índice antes da consulta de busca.

25、conexão de várias tabelas

A subconsulta e o join podem realizar a extração de dados entre várias tabelas, mas a subconsulta tem um desempenho ruim, é recomendável mudar a subconsulta para join. Para o join do MySQL, ele usa o algoritmo Nested Loop Join, que é fazer a consulta no conjunto de resultados da tabela anterior para a tabela seguinte, por exemplo, o conjunto de resultados da tabela anterior é100 dados, a próxima tabela tem10Os dados W, então, é necessário em100*10Os dados W são filtrados da coleção W para obter o conjunto de resultados final. Portanto, é melhor usar tabelas de pequeno conjunto de resultados para fazer join com grandes tabelas, ao mesmo tempo, estabelecer índices nos campos de join. Se não puder estabelecer índices, é necessário configurar um tamanho suficiente do buffer de join. Se essas técnicas não puderem resolver o problema de desempenho causado pelo join, então simplesmente não use join, quebre uma consulta join em duas consultas simples. Além disso, evite fazer join de mais de três tabelas, geralmente, quando há mais de três tabelas, o desempenho é muito ruim, é recomendável dividir o sql.

3、otimização do pool de conexões de banco de dados

A conexão de pool de banco de dados é essencialmente um cache, é um meio de resistência a alta concorrência. A otimização do pool de conexões de banco de dados é principalmente a otimização dos parâmetros, geralmente usamos o pool de conexões DBCP, seus parâmetros específicos são os seguintes:

31　 initialSize

O número inicial de conexões, aqui o inicial se refere à primeira chamada de getConnection, não ao momento do início do aplicativo. O valor inicial pode ser configurado como a média histórica da quantidade de concorrência.

32、minIdle

O número mínimo de conexões livres mantidas. O DBCP abrirá uma thread no plano de fundo para recolher conexões livres, quando essa thread realizar o recolhimento de conexões livres, ela manterá o número de conexões minIdle. Geralmente, é configurado para5，a quantidade de concorrência realmente pequena pode ser configurada para1

33、maxIdle

O número máximo de conexões livres mantidas, conforme configurado pelo pico de concorrência da negócios. Por exemplo, se o pico de concorrência for20, então, quando o pico passar, essas conexões não serão recolhidas imediatamente. Se, após um curto período de tempo, outro pico vier, o pool de conexões pode reutilizar essas conexões livres sem a necessidade de criar e fechar conexões com frequência.

34、maxActive

Número máximo de conexões ativas, configurado com base no valor de concorrência aceitável. Por exemplo, o valor máximo de concorrência aceitável para a concorrência em um único servidor pode ser100, então este maxActive deve ser100, então só pode ser servido ao mesmo tempo por100 solicitações de serviço, as solicitações extras serão descartadas após o tempo máximo de espera. Este valor deve ser configurado, para evitar ataques de concorrência maliciosos e proteger o banco de dados.

35、maxWait

O tempo máximo de espera para obter uma conexão, é recomendável configurá-lo de forma mais curta, por exemplo3s, assim permitindo que as solicitações falhem rapidamente, porque enquanto uma solicitação está esperando obter uma conexão, a thread não pode ser liberada, e o número de concorrência de threads em um único servidor é limitado, se esse tempo for configurado muito alto, por exemplo, como sugerido na internet60s, então essa thread neste60s é impossível liberar, sempre que esse tipo de solicitação for grande, o número de threads disponíveis para a aplicação diminui, e o serviço se torna inacessível.

36、minEvictableIdleTimeMillis

O tempo que a conexão permanece ociosa antes de ser recolhida, o padrão30 minutos.

37、validationQuery

Usado para verificar se a conexão é válida, geralmente é uma consulta simples, é recomendável configurar

38、testOnBorrow

Quando solicitar a conexão, verificar a conexão, não é recomendável ativá-lo, isso pode afetar significativamente o desempenho

39、testOnReturn

Ao devolver a conexão, verificar a conexão, não é recomendável ativá-lo, isso pode afetar significativamente o desempenho

310、testWhileIdle

Depois que for ativado, a thread de limpeza de conexões no plano de fundo verificará periodicamente as conexões ociosas para validateObject, se a conexão falhar, ela será removida, sem afetar o desempenho, é recomendável ativá-lo.

311、numTestsPerEvictionRun

Representa a quantidade de verificações feitas a cada vez, é recomendável configurar o mesmo valor que 'maxActive', para que cada vez seja possível verificar todos os links.

312、Aquecimento do pool de conexões

Para o pool de conexões, é recomendável aquecê-lo ao iniciar a aplicação, realizando consultas SQL simples antes de fornecer acesso ao público, para que o pool de conexões atinja o número necessário de conexões.

4、Otimização de índice

Quando a quantidade de dados aumenta até certo ponto, a otimização de SQL já não pode melhorar o desempenho. Neste momento, é necessário usar a grande técnica: índice. Existem três níveis de índice, geralmente é suficiente dominar esses três níveis, e, além disso, para os campos onde os índices são criados, é necessário considerar sua seleção.

41、Índice primário

Na condição após 'where', pode-se criar índices. Uma coluna pode ser criada como índice simples, enquanto múltiplas colunas devem ser criadas como índice composto. O índice composto deve seguir o princípio do prefixo mais à esquerda.

42、Índice secundário

Se há campos usados em 'order by' ou 'group by', pode-se considerar a criação de índices nesses campos. Dessa forma, devido à ordem natural dos índices, pode-se evitar a ordenação trazida por 'order by' e 'group by', melhorando assim o desempenho.

43, índice de três níveis

Se as duas estratégias acima não funcionarem, adicione também o índice para o campo consultado, formando assim o que se chama de cobertura de índice, o que pode reduzir uma vez a operação I/A operação O, porque o mysql consulta dados primeiro pelo índice primário, então pelo índice comum com base no índice primário, e então pelo registro correspondente com base no índice comum. Se todos os registros necessários estiverem no índice comum, não precisamos do terceiro passo. Claro, este método de construção de índices é extremo e não é adequado para cenários gerais.

44

, a escolha de índice

5, arquivamento de dados históricos

Quando a quantidade de dados aumentou anualmente5Quando atingimos X00W, os índices também são ineficazes. Neste caso, a abordagem geral é considerar o particionamento de banco de dados e de tabelas. Se o crescimento do negócio não for explosivo, mas os dados estão aumentando lentamente, podemos não considerar a técnica complexa de particionamento de banco de dados e de tabelas, mas fazer o arquivamento de dados históricos. Nós arquivamos dados históricos que já completaram o ciclo de vida, como6Os dados de antes de X meses são arquivados. Podemos usar a tarefa de agendamento quartz para agendar automaticamente a arquivagem na madrugada.6Os dados de antes de X meses são pesquisados e armazenados no servidor hbase remoto. Claro, também precisamos fornecer uma interface de consulta de dados históricos para o caso de emergência.

Aqui está a compilação de materiais de otimização do banco de dados mysql standalone, continuaremos a complementar informações relacionadas, obrigado pelo suporte dos visitantes deste site!

Declaração: O conteúdo deste artigo é proveniente da Internet, pertence ao autor original, é contribuído e carregado voluntariamente pelos usuários da Internet, o site não possui direitos de propriedade, não foi editado manualmente e não assume responsabilidade por questões legais relevantes. Se você encontrar conteúdo suspeito de direitos autorais, bem-vindo a enviar e-mail para: notice#oldtoolbag.com (ao enviar e-mail, substitua # por @ para denunciar. Forneça provas relevantes, e, uma vez confirmada, o site deletará imediatamente o conteúdo suspeito de infringir direitos autorais.)