Необходимые вещи для back-end программиста: 30 советов по написанию высококачественного SQL

MySQL

предисловие

В этой статье будут объединены демонстрационные примеры, чтобы описать 30 предложений по оптимизации SQL, большинство из которых суммированы в ходе фактической разработки и надеемся быть полезными для всех.

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

1. Старайтесь не использовать select * для запроса SQL, а выбирать определенные поля.

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

select * from employee;

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

select id，name from employee;

причина:

  • Заполняйте только обязательные поля, чтобы сэкономить ресурсы и снизить нагрузку на сеть.
  • Когда select * выполняет запрос, вполне вероятно, что покрывающий индекс не будет использоваться, что приведет к запросу возвращаемой таблицы.

2. Если вы знаете, что есть только один результат запроса или только максимальная/минимальная запись, рекомендуется использовать ограничение 1

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

CREATE TABLE `employee` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `sex` int(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

select id，name from employee where name='jay'

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

select id，name from employee where name='jay' limit 1;

причина:

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

3. Старайтесь не использовать или в предложении where для соединения условий

Создайте новую пользовательскую таблицу, она имеет общий индекс userId, структура таблицы следующая:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) NOT NULL,
  `age` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_userId` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Предположим теперь, что вам нужно запросить пользователей, чей идентификатор пользователя равен 1 или чей возраст равен 18, легко получить следующий sql

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

select * from user where userid=1 or age =18

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

//使用union all 
select * from user where userid=1 
union all 
select * from user where age = 18

//或者分开两条sql写：
select * from user where userid=1
select * from user where age = 18

причина:

  • Использование или может сделать индекс недействительным, что приведет к полному сканированию таблицы.

В случае or+age без индекса предположим, что он использует индекс userId, но когда он достигает условия запроса возраста, он должен выполнить полное сканирование таблицы, которое требует трехэтапного процесса: полное сканирование таблицы + сканирование индекса. + объединить Если он выполняет полное сканирование таблицы в начале, оно будет выполнено за одно сканирование. В MySQL есть оптимизатор, который считается с точки зрения эффективности и стоимости, в случае или условия индекс может дать сбой, что кажется разумным.

4. Оптимизация лимита пейджинга

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

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

select id，name，age from employee limit 10000，10

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

//方案一 ：返回上次查询的最大记录(偏移量)
select id，name from employee where id>10000 limit 10.

//方案二：order by + 索引
select id，name from employee order by id  limit 10000，10

//方案三：在业务允许的情况下限制页数：

причина:

  • Когда смещение наибольшее, эффективность запроса будет ниже, потому что Mysql не пропускает смещение и напрямую извлекает следующие данные, а сначала добавляет смещение + количество элементов для выборки, а затем извлекает переднее смещение Данные на этом участке смены отбрасываются и возвращаются.
  • Если используется схема оптимизации 1, возвращается последняя самая большая запись запроса (смещение), так что смещение можно пропустить, и эффективность значительно повышается.
  • Схема 2 использует порядок по индексу +, что также может повысить эффективность запросов.
  • В случае третьего плана рекомендуется обсудить с бизнесом, нужно ли после этого проверять пейджинг. Потому что подавляющее большинство пользователей не прокручивают слишком много страниц назад.

5. Оптимизируйте лайки

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

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

select userId，name from user where userId like '%123';

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

select userId，name from user where userId like '123%';

причина:

  • Поставьте % впереди и не переходите к индексу, как показано ниже:
  • Поставьте % после ключевого слова, и индекс все равно будет взят. следующее:

6. Используйте условия where, чтобы ограничить запрашиваемые данные, чтобы избежать возврата избыточных строк.

Предположим, бизнес-сценарий таков: запросите, является ли пользователь участником. Видели старый код реализации, как это. . .

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

List<Long> userIds = sqlMap.queryList("select userId from user where isVip=1");
boolean isVip = userIds.contains(userId);

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

Long userId = sqlMap.queryObject("select userId from user where userId='userId' and isVip='1' ")
boolean isVip = userId！=null;

причина:

  • Проверьте, какие данные необходимы, чтобы избежать возврата ненужных данных и сократить накладные расходы.

7. Старайтесь избегать использования встроенных функций MySQL для индексных столбцов.

Бизнес-требования: запрашивать пользователей, которые вошли в систему за последние семь дней (при условии, что 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, а индекс недействителен.

  • Если в столбец индекса не добавляются встроенные функции, индекс все равно пойдет.

8. Старайтесь избегать операций с выражениями над полями в предложении where, что заставит систему отказаться от использования индексов и выполнить полное сканирование таблицы.

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

select * from user where age-1 =10；

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

select * from user where age =11；

причина:

  • Хотя возраст имеет индекс, но из-за операции над ним индекс прямо теряется. . .

9. Внутреннее соединение, левое соединение, правое соединение, сначала используйте внутреннее соединение, если это левое соединение, результат левой таблицы должен быть как можно меньше.

  • Внутреннее соединение внутреннее соединение, когда две таблицы объединяются и запрашиваются, сохраняются только те наборы результатов, которые точно совпадают в двух таблицах.
  • Левое соединение Когда два таблица присоединяются к запросу, вернут все строки из левой таблицы, даже если в правой таблице нет соответствующей записи.
  • Когда правое соединение объединяет две таблицы, возвращаются все строки в правой таблице, даже если в левой таблице нет совпадающих записей.

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

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

select * from tab1 t1 left join tab2 t2  on t1.size = t2.size where t1.id>2;

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

select * from (select * from tab1 where id >2) t1 left join tab2 t2 on t1.size = t2.size;

причина:

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

10. Старайтесь избегать использования оператора != или в предложении where, иначе движок откажется от использования индекса и выполнит полное сканирование таблицы.

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

select age,name  from user where age <>18;

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

//可以考虑分开两条sql写
select age,name  from user where age <18;
select age,name  from user where age >18;

причина:

  • Использование != и , скорее всего, сделает индекс недействительным.

11. При использовании объединенного индекса обратите внимание на порядок столбцов индекса и, как правило, придерживайтесь принципа сопоставления самого левого.

Структура таблицы: (есть сводный индекс idx_userid_age, впереди userId, сзади age)

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_userid_age` (`userId`,`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

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

select * from user where age = 10;

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

//符合最左匹配原则
select * from user where userid=10 and age =10；
//符合最左匹配原则
select * from user where userid =10;

причина:

  • Когда мы создаем объединенный индекс, такой как (k1,k2,k3), это эквивалентно созданию трех индексов (k1), (k1,k2) и (k1,k2,k3), что является самым левым принципом сопоставления.
  • Совместный индекс не соответствует крайнему левому принципу, и индекс вообще не будет работать, но это также связано с оптимизатором Mysql.

12. Чтобы оптимизировать запрос, вам следует подумать о создании индексов для столбцов, связанных с параметрами «где» и «упорядочить по», и попытаться избежать полного сканирования таблицы.

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

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

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

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

13. Если вставляется слишком много данных, рассмотрите возможность пакетной вставки.

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

for(User u :list){
 INSERT into user(name,age) values(#name#,#age#)   
}

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

//一次500批量插入，分批进行
insert into user(name,age) values
<foreach collection="list" item="item" index="index" separator=",">
    (#{item.name},#{item.age})
</foreach>

причина:

  • Производительность массовой вставки хорошая, что экономит время

Аналогия: если вам нужно переместить 10 000 кирпичей на крышу, у вас есть лифт, лифт может вместить соответствующее количество кирпичей за раз (до 500), вы можете транспортировать по одному кирпичу или 500. за раз, как вы думаете, что занимает больше всего времени?

14. При необходимости используйте покрывающие индексы.

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

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

// like模糊查询，不走索引了
select * from user where userid like '%123%'

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

//id为主键，那么为普通索引，即覆盖索引登场了。
select id,name from user where userid like '%123%';

15. Осторожно используйте ключевое слово different

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

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

SELECT DISTINCT * from  user;

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

select DISTINCT name from user;

причина:

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

16. Удалите избыточные и повторяющиеся индексы

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

  KEY `idx_userId` (`userId`)  
  KEY `idx_userId_age` (`userId`,`age`)

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

  //删除userId索引，因为组合索引（A，B）相当于创建了（A）和（A，B）索引
  KEY `idx_userId_age` (`userId`,`age`)

причина:

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

17. Если объем данных велик, оптимизируйте операторы изменения/удаления.

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

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

//一次删除10万或者100万+？
delete from user where id <100000;
//或者采用单一循环操作，效率低，时间漫长
for（User user：list）{
   delete from user； 
}

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

//分批进行删除,如每次500
delete user where id<500
delete product where id>=500 and id<1000；

причина:

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

18. Рассмотрите возможность использования значения по умолчанию вместо null в предложении where.

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

select * from user where age is not null;

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

//设置0为默认值
select * from user where age>0;

причина:

  • Это не значит, что если вы используете null или не null, индекс не будет взят, это связано с версией MySQL и стоимостью запроса.

Если оптимизатор mysql обнаружит, что стоимость использования индекса выше, чем стоимость неиспользования индекса, он обязательно откажется от индекса.！=，>is null，is not nullЧасто считается, что индекс становится недействительным, но на самом деле это связано с тем, что в целом стоимость запроса высока, и оптимизатор автоматически сдается.

  • Если вы замените нулевое значение на значение по умолчанию, часто можно переместить индекс, и в то же время смысл будет относительно ясен.

19. Не используйте более 5 подключений к таблицам

  • Чем больше связанных таблиц, тем больше время компиляции и накладные расходы.
  • Разбиение таблицы соединений на более мелкие исполнения более удобочитаемо.
  • Если вам нужно объединить много таблиц, чтобы получить данные, это означает плохой дизайн.

20. Разумное использование существующих и в

Предполагая, что таблица A представляет таблицу сотрудников предприятия, а таблица B представляет таблицу отделов, для запроса всех сотрудников всех отделов легко получить следующий SQL:

select * from A where deptId in (select deptId from B);

Написание этого эквивалентно:

Таблица отдела первого запроса B

select deptId from B

Затем используйте deptId отдела, чтобы запросить сотрудников A

select * from A where A.deptId = B.deptId

Его можно абстрагировать в такой цикл:

   List<> resultSet ;
    for(int i=0;i<B.length;i++) {
          for(int j=0;j<A.length;j++) {
          if(A[i].id==B[j].id) {
             resultSet.add(A[i]);
             break;
          }
       }
    }

Очевидно, что в дополнение к in мы также можем использовать exists для получения той же функции запроса, как показано ниже:

select * from A where exists (select 1 from B where A.deptId = B.deptId);

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

Тогда запись этого эквивалентна:

выберите * из A, первый цикл из таблицы A

выберите * из B, где A.deptId = B.deptId, а затем выполните цикл из таблицы B.

Точно так же его можно абстрагировать в такой цикл:

   List<> resultSet ;
    for(int i=0;i<A.length;i++) {
          for(int j=0;j<B.length;j++) {
          if(A[i].deptId==B[j].deptId) {
             resultSet.add(A[i]);
             break;
          }
       }
    }

Самая напряженная релиз базы данных связана с программой. Если ссылка дважды, каждый раз готовит миллионы запроса набора данных, заканчивая вверх и слева, поэтому только дважды; наоборот, установленные миллионах ссылок, ссылки для выпуска повторяются снова и снова, так что система по не наступила. Что принцип оптимизации MySQL - это небольшой большой стол, управляемый столом, небольшие данные, установленные для привода больших наборов данных, позволяя лучше выполнять.

Поэтому мы хотим выбрать самый внешний цикл маленьким, то есть, еслиОбъем данных B меньше, чем у A, поэтому его можно использовать. Если объем данных B больше, чем у A, целесообразно выбрать существующий..

21. Попробуйте заменить union на union all

Если в результатах поиска нет повторяющихся записей, рекомендуется заменить union на union all.

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

select * from user where userid=1 
union  
select * from user where age = 10

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

select * from user where userid=1 
union all  
select * from user where age = 10

причина:

  • Если используется объединение, независимо от того, дублируются ли результаты поиска, они будут пытаться объединиться, а затем отсортироваться перед выводом окончательного результата. Если известно, что в результатах поиска нет повторяющихся записей, используйте union all вместо union, что повысит эффективность.

22. Индекса не должно быть слишком много, как правило, меньше 5.

  • Чем больше индексов, тем лучше, хотя индекс повышает эффективность запроса, он также снижает эффективность вставки и обновления.
  • Индексы могут быть перестроены во время вставки или обновления, поэтому необходимо тщательно продумать создание индекса в зависимости от конкретной ситуации.
  • Количество индексов в таблице не должно превышать 5. Если индексов слишком много, необходимо рассмотреть, не существуют ли некоторые индексы.

23. Старайтесь использовать числовые поля Если поля содержат только числовую информацию, постарайтесь не оформлять их как символы.

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

king_id` varchar（20） NOT NULL COMMENT '守护者Id'

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

`king_id` int(11) NOT NULL COMMENT '守护者Id'`

причина:

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

24. Индексы не подходят для полей с большим количеством повторяющихся данных, таких как поля гендерной базы данных.

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

25. Старайтесь не возвращать слишком много данных клиенту.

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

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

//一次性查询所有数据回来
select * from LivingInfo where watchId =useId and watchTime >= Date_sub(now(),Interval 1 Y)

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

//分页查询
select * from LivingInfo where watchId =useId and watchTime>= Date_sub(now(),Interval 1 Y) limit offset，pageSize

//如果是前端分页，可以先查询前两百条记录，因为一般用户应该也不会往下翻太多页，
select * from LivingInfo where watchId =useId and watchTime>= Date_sub(now(),Interval 1 Y) limit 200 ;

26. При соединении нескольких таблиц в операторе SQL используйте псевдоним таблицы и добавляйте префикс псевдонима к каждому столбцу, чтобы семантика была более ясной.

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

select  * from A inner
join B on A.deptId = B.deptId;

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

select  memeber.name,deptment.deptName from A member inner
join B deptment on member.deptId = deptment.deptId;

27. Максимально используйте varchar/nvarchar вместо char/nchar.

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

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

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

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

причина:

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

28. Чтобы повысить эффективность группировки по оператору, вы можете отфильтровать ненужные записи перед выполнением оператора.

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

select job，avg（salary） from employee  group by job having job ='president' 
or job = 'managent'

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

select job，avg（salary） from employee where job ='president' 
or job = 'managent' group by job；

29. Если тип поля — строка, то where необходимо заключить в кавычки, иначе индекс будет неверным.

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

select * from user where userid =123;

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

select * from user where userid ='123';

причина:

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

30. Используйте объяснение для анализа вашего плана SQL

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

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

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

Личный публичный аккаунт

  • Если вы считаете, что это хорошо написано, пожалуйста, поставьте лайк + подпишитесь, спасибо~
  • Если что-то не так, укажите на это, большое спасибо.
  • В то же время, я очень жду, что мои друзья обратят внимание на мой официальный аккаунт, и позже я постепенно представлю более качественные галантерейные товары~ хи хи
  • адрес гитхаба:GitHub.com/Я бы хотел 123/Java…
Категории