В Mysql: 21 хорошая привычка для написания SQL, вы это заслужили

предисловие

Каждая хорошая привычка - это сокровище. Эта статья разделена на три направления: SQL-лекарство от сожалений, оптимизация производительности SQL и элегантность спецификации SQL. Я делюсь 21 хорошей привычкой написания SQL. Спасибо за чтение, давай~

адрес github, спасибо за каждую звезду

GitHub.com/Я бы хотел 123/Java…

публика:маленький мальчик собирает улиток

1. После написания SQL сначала объясните и просмотрите план выполнения (оптимизация производительности SQL)

При разработке и записи SQL каждый день пытаются развить эту хорошую привычку: после написания SQL используйте объяснение, чтобы проанализировать его и уделять особое внимание, следует ли использовать индекс.

explain select * from user where userid =10086 or age =18;

2. Управляйте оператором удаления или обновления и добавляйте ограничение (лекарство от сожалений SQL)

При выполнении оператора удаления или обновления постарайтесь максимально увеличить ограничение.В качестве примера возьмем следующий SQL:

delete from euser where age > 30 limit 200;

Потому что добавление лимита в основном имеет следующие преимущества:

Уменьшить стоимость написания неправильного SQL, Когда вы выполняете этот SQL в командной строке, если ограничение не добавлено,Случайное рукопожатие, данные могут быть полностью удалены, еслиудалить неправильнокакие? Добавьте ограничение в 200, и оно будет другим. Если вы удалите неправильные данные, вы потеряете только 200 фрагментов данных, которые можно быстро восстановить через журнал binlog.
SQL, вероятно, будет более эффективным, вы добавляете ограничение 1 в строке SQL, если первая строка достигает целевого возврата, если нет ограничения, она продолжит сканирование таблицы.
Избегайте длинных транзакций, Когда удаление выполняется, если возраст индексирован, MySQL добавит блокировки записи и блокировки промежутка ко всем соответствующим строкам, и все строки, связанные с выполнением, будут заблокированы.Если количество удалений велико, это напрямую повлияет на связанные службы что нельзя использовать.
Если объем данных велик, легко заполнить ЦП, Если вы удаляете большой объем данных, не добавляйте ограничение для ограничения количества записей, легко заполнить процессор, что приводит к более медленному и медленному удалению.

3. При разработке таблицы добавьте соответствующие комментарии ко всем таблицам и полям (спецификация SQL элегантна)

Эту полезную привычку необходимо развивать: при проектировании таблиц базы данных все таблицы и поля добавляются с соответствующими комментариями, что впоследствии легче поддерживать.

Положительный пример:

CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键Id',
  `name` varchar(255) DEFAULT NULL COMMENT '账户名',
  `balance` int(11) DEFAULT NULL COMMENT '余额',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='账户表';

Пример счетчика:

CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `balance` int(11) DEFAULT NULL,
  `create_time` datetime NOT NULL ,
  `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8;

4. Формат записи SQL, сохраняйте тот же размер ключевого слова и используйте отступ. (Спецификация SQL элегантна)

Положительный пример:

SELECT stu.name, sum(stu.score)
FROM Student stu
WHERE stu.classNo = '1班'
GROUP BY stu.name

Пример счетчика:

SELECT stu.name, sum(stu.score) from Student stu WHERE stu.classNo = '1班' group by stu.name.

Очевидно, что унификация написания ключевых слов с заглавными буквами и использование выравнивания отступов сделают ваш SQL более элегантным~

5. Оператор INSERT указывает соответствующее имя поля (элегантная спецификация SQL).

Пример счетчика:

insert into Student values ('666','捡田螺的小男孩','100');

Положительный пример:

insert into Student(student_id,name,score) values ('666','捡田螺的小男孩','100');

6. Сначала измените операцию SQL в тестовой среде, запишите подробные шаги операции и план отката, а затем просмотрите их перед производством. (SQL лекарство от сожалений)

Сначала измените операцию SQL в тестовой среде, чтобы избежать синтаксических ошибок, и запустите ее в производство.
Чтобы изменить операцию Sql, вам нужно указать подробные шаги операции, особенно при наличии зависимостей, таких как: сначала изменить структуру таблицы, а затем дополнить соответствующие данные.
Существует план отката для изменения операций SQL, и просмотрите соответствующие изменения SQL перед переходом к рабочей среде.

7. При разработке таблицы базы данных добавьте три поля: первичный ключ, время_создания, время_обновления. (Спецификация SQL элегантна)

Пример счетчика:

CREATE TABLE `account` (
  `name` varchar(255) DEFAULT NULL COMMENT '账户名',
  `balance` int(11) DEFAULT NULL COMMENT '余额',
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='账户表';

Положительный пример:

CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键Id',
  `name` varchar(255) DEFAULT NULL COMMENT '账户名',
  `balance` int(11) DEFAULT NULL COMMENT '余额',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='账户表';

причина:

Необходимо добавить первичный ключ, таблица без первичного ключа не имеет души
Рекомендуется добавить время создания и время обновления.Полезны подробные записи аудита и отслеживания.

В руководстве по разработке на Али тоже упоминается этот момент, как показано на рисунке

8. После написания оператора SQL проверьте столбцы после where, упорядочить и сгруппировать, были ли проиндексированы столбцы, связанные с несколькими таблицами, и отдать приоритет объединенному индексу. (оптимизация производительности SQL)

Пример счетчика:

select * from user where address ='深圳' order by age ;

Положительный пример:

添加索引
alter table user add index idx_address_age (address,age)

9. Перед изменением или удалением важных данных необходимо сначала сделать резервную копию, сначала сделать резервную копию, сначала сделать резервную копию (лекарство от сожалений SQL)

Если вы хотите изменить или удалить данные, вы должны сделать резервную копию данных, которые будут изменены, прежде чем выполнять SQL.В случае неправильной работы вы все равно можете съесть.лекарство сожаления~

10. Для полей, стоящих за where, обратите внимание на неявное преобразование их типов данных (оптимизация производительности SQL)

Пример счетчика:

//userid 是varchar字符串类型
select * from user where userid =123;

Положительный пример:

select * from user where userid ='123';

причина:

Поскольку, когда одинарные кавычки не добавляются, это сравнение между строками и числами, и их типы не совпадают, MySQL сделает неявное преобразование типов, преобразует их в числа с плавающей запятой, а затем сравнит их, что в конечном итоге приведет к признание индекса недействительным.

11. Попробуйте определить все столбцы как NOT NULL (спецификация SQL элегантна)

Не нулевые колонны - это больше пространства, столбцу NULL требуется дополнительный байт в качестве флага, чтобы определить, является ли он NULL.
Столбцы NULL должны обратить внимание на проблему нулевого указателя, При вычислении и сравнении столбцов NULL необходимо обратить внимание на проблему с нулевым указателем.

12. Чтобы изменить или удалить SQL, сначала напишите ГДЕ для проверки, а затем добавьте удаление или обновление после подтверждения (лекарство от сожаления SQL)

Особенно при работе с полученными данными, если вы столкнулись с измененным или удаленным SQL, сначала добавьте запрос where, подтвердите OK, а затем выполните операцию обновления или удаления.

13. Уменьшите ненужные возвраты полей, например, используйте select вместо select * (оптимизация производительности SQL)

Пример счетчика:

select * from employee;

Положительный пример:

select id，name from employee;

причина:

Экономьте ресурсы и снижайте нагрузку на сеть.
Покрывающие индексы могут использоваться для уменьшения возвращаемых таблиц и повышения эффективности запросов.

14. Все таблицы должны использовать механизм хранения Innodb (элегантная спецификация SQL).

Innodb поддерживает транзакции, блокировки на уровне строк, улучшенную восстанавливаемость и лучшую производительность при высокой степени параллелизма.Поэтому, если нет особых требований (то есть функций, которым Innodb не может соответствовать, таких как хранение столбцов, данных о пространстве хранения и т. д.), все таблицы должны использовать механизм хранения Innodb

15. Набор символов базы данных и таблица унифицирован с использованием UTF8 (спецификация SQL элегантна)

Унифицированное использование кодировки UTF8

Может избежать искаженных проблем
Можно избежать проблемы сбоя индекса, вызванной сравнением и преобразованием различных наборов символов.

Если вы храните выражения, вы можете рассмотреть utf8mb4

16. Попробуйте использовать varchar вместо char. (оптимизация производительности SQL)

Пример счетчика:

  `deptName` char(100) DEFAULT NULL COMMENT '部门名称'

Положительный пример:

`deptName` varchar(100) DEFAULT NULL COMMENT '部门名称'

причина:

Поскольку пространство для хранения поля переменной длины поначалу невелико, место для хранения можно сэкономить.

17. Если значение поля изменяется или добавляется состояние, указанное полем, комментарий к полю необходимо своевременно обновлять. (Спецификация SQL элегантна)

Этот момент является оговоркой Mysql в руководстве по разработке на Али. Если значение ваших полей, особенно состояние перечисления, изменено или состояние добавлено, для лучшего обслуживания в дальнейшем вам необходимо немедленно обновить аннотации полей.

18. SQL модифицирует данные, выработайте привычку начинать + фиксировать транзакцию (SQL лекарство от сожалений)

Положительный пример:

begin;
update account set balance =1000000
where name ='捡田螺的小男孩';
commit;

Пример счетчика:

update account set balance =1000000
where name ='捡田螺的小男孩';

19. Именование индекса должно быть стандартизировано, имя индекса первичного ключа — pk_ имя поля, уникальное имя индекса — uk _ имя поля, общее имя индекса — idx _ имя поля. (Спецификация SQL элегантна)

Описание: pk_ — первичный ключ, uk _ — уникальный ключ, idx _ — сокращение от index.

20. Нет преобразования функций и вычисления выражений для столбцов в предложении WHERE.

Предположим, что loginTime индексируется

Пример счетчика:

select userId,loginTime from loginuser where Date_ADD(loginTime,Interval 7 DAY) >=now();

Положительный пример:

explain  select userId,loginTime from loginuser where  loginTime >= Date_ADD(NOW(),INTERVAL - 7 DAY);

причина:

В столбце индекса используется встроенная функция mysql, а индекс недействителен.

21. Если нужно изменить/обновить слишком много данных, подумайте о том, чтобы делать это в пакетном режиме.

Пример счетчика:

delete from account  limit 100000;

Положительный пример:

for each(200次)
{
 delete from account  limit 500;
}

причина:

Большие пакеты операций могут вызвать задержки ведущий-ведомый.
Большие пакетные операции будут генерировать большие транзакции, блокирующие.
Большие пакетные операции, объем данных слишком велик, ЦП будет заполнен.

Ссылка и спасибо

Это хорошая привычка добавлять лимит после удаления?
«Руководство по развитию Али»

публика

Публичный аккаунт стека серверных технологий: маленький мальчик собирает улиток