Skip to main content
Rails Performance Techniques:

Improving Query Performance with Database Indexes

Episode 119 · May 17, 2016

Learn how to improve the speed of your database queries by adding database indexes to your tables

Performance


Transcripts

Earn a free month

تحسين أداء الاستعلامات باستخدام Database Indexes

غَردت البارحة على تويتر منبهًا على ضرورة استخدام Database Indexes. حيث أني قمت بتجربة على أحد قواعد البيانات، حجمها حوالي 500 ميغا، فإن استعلامًا لعمودين استغرق قرابة الثلاث ثواني، وهذا وقت طويل بلا شك. ولكن وبعد إضافة خاصيّة الجدولة indexes انخفض الرقم إلى 2.3 إلى 3.0 ميلي ثانية، وهذا تحسّن بنسبة ألف مرّة. وهذا بالفعل تحسّن ملفت للنظر ولا يمكن تجاهله. والجدير بالذكر أن أداء قاعدة البيانات لا يحصل على القدر الكافي من الاهتمام في معظم دروس "ريلز" المنتشرة وذلك على افتراض هذا شأن دروس قوعد البيانات وليس من اختصاص "ريلز" بالتحديد. ويجب الانتباه إلى أن مشاكل الأداء هذه لا تظهر مع قواعد البيانات الصغيرة، ولكن مع قواعد البيانات الكبيرة الحجم فالمشكلة موجودة ولها من أثر كبير على أداء التطبيقات.

إذا في هذه الحلقة سنعمل على مشروع Ruby on Rails صغير وفيه ملف الـ schema.rb يحتوي على جدول المستخدمين users ذو الحقلين email و sign_in_count فقط لا غير:

db/schema.rb

ActiveRecord::Schema.define(version: 20160517152044) do
  create_table “users”, force: cascade do |t”
    t.string “email”
    t.integer “sign_in_count”
    t.datatime “created_at”,  null: false
    t.datatime “updated_at”,  null: false
  end
end

كما قمت باستخدام مكتبة faker بغرض إنشاء مليونين من سجلات المستخدمين والتي وصلت بحجم تجاوز 200m من قواعد البيانات sqlite3 وذلك فقط بغرض التجربة والتي تحتوي بالضبط على 2231077 سجل.

لنتحدث بالأرقام. إن الاستعلام عن عدد السجلات في قاعدة البيانات يستغرق تمامًا 97 جزء من الثانية. مع العلم أنه عادة ما يستغرق نفس الاستعلام 0.1 جزء من الثانية، وذلك في المراحل الأولى للمشروع حيث عدد السجلات قليل للغاية.

الجدير بالذكر سرعة جلب أول سجل من قاعدة البيانات وهو حوالي 0.2 جزء من الثانية. ولكن عند إجراء استعلام على قاعدة البيانات عندها سيكون الأمر مختلف:

#rails console
User.first
#User Load (0.2ms)…

User.where(email: “[email protected]”)
#User Load (344ms)…

وكلما زاد الاستعلام تعقيدًا كلما أصبحت الاستعلامات أبطئ. وبطبيعة الحال ستحتاج أي صفحة ويب ديناميكية إلى إجراء استعلام واحدًا على الأقل الأمر الذي سينعكس على أداء التطبيق وسرعة الاستجابة.

قبل الشروع في الجدولة أريد فقط التنبيه على نقطة هامة وهي وجوب مراقبة ومتابعة الاستعلامات في البيئة الحقيقية (production) وذلك عن طريق سجلات "ريلز" (Rails logs) بين الحين والآخر. ولكن إن كنت تستخدم خدمات مثل Heroku أو Skylight أو New Relic فعليك بالاستفادة مما تقدّمه من تسهيلات في هذا الصدد، فهي قادرة على عرض أبطئ استعلام في بضغطة زر واحدة عندها يمكن التركيز على هذه الاستعلامات البطيئة ومعالجتها واحدة تلو الأخرى.

يقدّم لنا إطار العمل Ruby on Rails طريقة (method) جاهزة للاستخدام وهي add_index والتي يمكنك استدعائها مع ترحيل قواعد البيانات (database migrations)، ولربما كنت قد شاهدتها من قبل، ربما في ترحيلات (migrations) مكتبة device، ولكنك قد لا تشاهدها مع مولّد التطبيقات الخاص بريلز (scaffold) فهي لا تُضاف أوتوماتيكيا، بل تُضاف عند الحاجة الفعلية وذلك مع ما يتوافق مع التطبيق واحتياجاته، فالإفراط في استخدام الجدولة (indexes) سيؤدي إلى إضافة عبئ إضافي على قاعدة البيانات واستهلاك المزيد من المساحة التخزينية على القرص الصلب، وكما سيؤدي إلى بطء في إدراج وتحديث السجلات في قاعدة البيانات. بمعنى آخر إن خاصيّة الجدولة تقدّم أداء أفضل من ناحية القراءة إلا أنها قد تُبطئ من عملية الكتابة. وبما أنه عادة ما يكون الضغط على القراءة أكثر منه مع الكتابة فمن الجيّد استخدام خاصيّة الجدولة مع عدم الإفراط في استخدامها.

كل ما تحتاجه الطريقة (method) أمرين: اسم الجدول واسم العمود المُراد إضافة جدولة (index) إليه. مع الانتباه إلى أن العمود id هو أوتوماتيكيا مجدّول ولا داعي لجدولته يدويًا، ولكن إن كان هناك استعلامات متكرّرة مثلًا على عمود البريد الإلكتروني (email) –لربما لأغراض تسجيل الدخول–فربما من الضروري إضافة جدولة (index) على هذا العمود وذلك كما في: add_index :users, :email. أو إنّ كان تسجيل الدخول يتطلّب اسم المُستخدم عندها add_index :users, :name. أما لو كان تسجيل الدخول عن طريق اسم المستخدم أو البريد الإلكتروني فعندما من المستحسن إضافة جدولة لكلٍ منهما على حدة.

إن كنت تستعلم عن عمودين في نفس الوقت، كمثال عندما يكون الاستعلام عن عمود البريد الإلكتروني وعمود حالة النشاط (active) عندما من المستحسن إنشاء جدولة تتضمّن كلا العمودين وذلك من خلال تمرير اسمي العمودين كمصفوفة (array) وبذلك تحصل على أفضل أداء من خلال إنشاء جدولة لكلا العمودين معًا. ففي مثالنا لو كنا نستعلم بشكل متكرر عن الـ email و sign_in_count فمن الجيّد إنشاء جدولة تتضمن هذين العمودين عندها سيكون الاستعلام سريعًا للغاية.

add_index(:accounts, [:branch_id, :party_id], unique: true)

الطريقة add_index تسمح بتمرير خيار unique: true والغرض منه التأكّد من أن المُدخل في قاعدة البيانات هو مدخل فريد. بمعنى عند المحاولة مثلًا في التسجيل في الموقع ببريد إلكتروني مُستخدم بالفعل فإن محرك قاعدة البيانات لن يسمح بذلك. وبالإضافة إلى ما سبق يمكن أيضًا الاستعانة بالـ validation على مستوى التطبيق نفسه وذلك كما في validates_uniqueness_of.

لنعد الآن إلى مثالنا وتنفيذ الأمر التالي:

#rails console
rails generate migration AddIndexToUsers

وسنقوم بكتابة الترحيل يدويًا كما في التالي:

class AddIndexToUsers < ActiveRecord::Migration[5.0]
  def change
    add_index(:users, [:email, :sign_in_count])
  end
end

من المهم جدًا الانتباه إلى أن الإعداد السابق يضرب عصفرين بحجر واحد حيث أنه سيتمّ تحسين الاستعلام الخاصّة بعمود البريد الإلكتروني فقط والاستعلامات التي تحتوي على البريد الإلكتروني وعدد مرات الدخول معًا، ولكنها لن تحسّن من الاستعلامات القائمة على عمود عدد مرات الدخول (sign_in_count) فقط، ولتحقيق ذلك يجب إضافة جدولة مستقلة:

add_index(:users, :sign_in_count)

سنقوم الآن بعملية الترحيل:

rake db:migrate

الجدير بالملاحظة أن حجم قاعدة البيانات قد أصبح 279 ميغا، بمعنى أن عملية الجدولة هذه فقط أخذت حوالي 79 ميغا لتخزين معلومات الترحيل، وهذا ليس بالحجم الصغير.

سنستعرض الآن سرعة استجابة قاعدة البيانات بعد التعديل ونلاحظ الفرق:

#rails condosle
User.where(email: “[email protected]”)
#User Load (0.3ms)….

كما هو ملاحظ الوقت المستغرق لاستكمال الاستعلام لا يتجاوز 0.3 جزء من الثانية، وبالطبع الفرق شاسع من قبل إضافة الجدولة (index) مقارنة 344 جزء من الثانية، وهذا تحسن كبير. والآن لنستعرض كامل الاستعلام: البريد الإلكتروني مع عدد مرات تسجيل الدخول:

#rails condosle
User.where(email: “[email protected]”, sign_in_count: 47)
User Load (0.4ms)…

أيضًا التحسن واضح وضوح الشمس ولا مجال للمقارنة والاستعلام لم يستغرق سوى 0.4 جزء من الثانية.

ما أريد التشديد عليه هو حساب سرعة تنفيذ الاستعلام قبل وبعد إضافة الجدولة في قاعدة البيانات وذلك بهدف التحقق والقيام بالإجراء اللازم والصحيح لتحسين سرعة استجابة الاستعلام، وعدم إضافة الجدولة بتهوّر وعدم معرفة أثر إضافة الجدولة على قاعدة البيانات من عدمه. كما أنه من الجيّد جدًا استخدام أدوات مثل New Relic أو Skylight أو أيًا كانت الأداة وذلك بهدف معرفة أداء الاستعلامات في البيئة الحقيقية للتطبيق والتركيز على الاستعلامات ذات الضغط العالي والأداء المنخفض.

What's up guys? I apologize if I'm off a little bit this episode, I'm recovering from a cold, but yesterday I tweeted out a tweet that said basically: Hey, pay attention and make sure that you're using database indexes or indeces on your database for the queries that you do, because on a somewhat small database I had, it's about 500 megabytes, a query on that that just said: Give me the records in this table, and match these two columns. That would take three seconds in our database, which is really slow, and if you add database indexes to it, it went down to an average something like 2.3 to 3 ms instead, so it was basically 1000 times improvement, which is really impressive. This is one of those features that was not covered enough in most of the tutorials and things, so it's kind of assumed that if you're going to get deeper into SQL stuff, that's not where rails are going to talk about this stuff. You can go learn about SQL on your own, but I want to talk about that, because it's important to actually making fast rails applications. This isn't something that you're going to have to care about for a long time because most of your applications aren't going to hit these performance problems, it requires a fairly good chunk of data in your system, but it can really really improve your query speed. In this episode, we're going to open up a really simple rails application that I've built. There's really nothing to it, let's open up the schema file, and we'll see what we've got here. I have a user's table with an email and a sign-in account and that's it. There's nothing more to it, and I've gone and used the faker gem to create somewhere over two million number of user records. If we open this database file up, this is just our SQLite database, that's about 200MB of user data, so the two million records kind of transfer into a 201MB file in sqlite. You can see that we're already starting to push of pretty large amount of data at two million records, and if we open up this in our console, we can see that the user account is 2,231,077 records, so that's quite a fiew records, and I have an email address that I generated here, that we can go look up the first user, and we'll look up this other user to see what our performance is like for our queries. You can see here that when I did a count, a simple count on the user table, it took 97 ms, and normally if you're doing this in your application, you're probably doing this in development with a small database and you'll notice that that's like 0.1 ms, something super fast, but even grabbing the first record here is actually pretty fast, it's 0.2 ms, so that's really really quick, but if we were to go and do queries on this database, then it becomes a little bit tougher to do performance-wise for the database, let me grab this and paste that in, and so if we try to find the user where the email is morgan, we're going to see some pretty large query times. My example yesterday was that mine were about 10 times slower than this, and I had about a three times larger database. This even gets impacted a little bit more when you also query for multiple things and say: Well, I only want where the sign-in count is 47 or whatever. You will have different tables, you're querying against different columns you're querying against, but the more of these you kind of add in there, the more complicated your queries become, the slower your database ends up being, and indexes are really designed here to solve this problem. It shouldn't take you a third of a second in order to grab the user's that match this email address. That's pretty slow, and it's going to make for a very slow web application, because you're probably going to be doing a handful of queries, and if they're all taking a third of a second, your website is going to take at least a second to generate before it even gets sent back and forth to the user's browser, and that's unbelievably slow. Before we go implement database indexes in our example, let me mention real quick that you're going to want to track your production SQL queries and monitor their performance when this becomes a problem. Number one you can probably just look in your rails logs, if you're tracking the SQL queries in your logs, you can just look at those and check those once in a while and just see how slow they're going, but if you're using something like Heroku, skylight, new relic, any of those tools that actually monitor your database query performance, you can just use those and they often give you tools or easy ways to say: Give me the slowest database queries, and then you can go there and start focusing your time and knocking those of the list. Our example is really simple, we need to index the user table here, and we're probably going to normally be doing queries where the email address needs to be matched, but maybe we're also doing multiple column queries, so we're saying: Where the email is this and the sign-in account is whatever. This is just an example, but if you're searching for different things, you're probably doing blog posts that were published at anytime after this time, and then they're also not in draft status or something like that. You're probably in most cases searching on multiple columns, so we're going to talk about how to add an index for multiple columns and how that benefits you in your database queries.

Rails provides an add_index method that you can call in your database migrations. You probably have seen this in there in a couple places like your devise migrations, but for normal scaffolds and stuff that you do, you probably haven't automatically added those. You don't really need to add indexes, until you know exactly how your application is going to function so you can add the proper indexes in. If you add too many indexes, every time you have an index, it has to keep updating the main table, it has to update the index, anytime you insert a record it has to do it multiple times or update a record as well, so you actually have to keep an eye on how many indexes you're addin because number one, you're eating up more storage space, and number two you're slowind down your inserts and your updates in your database. While this improves query performance reads, it actually slows downs writes a little bit. You're getting the tradeoff there, and in most cases, you're doing heavier on the reads, so it's ok to add some indexes in there, just don't go too overboard with it unless you know exactly what you need. The add_index method really just requires two things. Number one is the table name and number two is the columns you want to index. If you're querying on a single field all the time like an id in the database, that's automatically going to be index. So the primary keys are automatically indexed for your records, but if you query on something like an email address because your users log in with their emai, then you want to add an index for say the user's table and the email column. If you're using user names to log in, you would want to index the username column, or if you allow both, you might want to add separate indexes for each one so you could query those as well. If there's cases where you actually query for two columns at the same time and you say: where the email address is this and active is true, then you can actually create an index that includes both of those, and in the second parameter you would pass in an array of the multiple column names. This would create an index on two columns, and it would allow you to make those queries faster. In our example here, if we did the query on the email and sign in count all the time, then we could acually say: Let's index both of these together and then that will make that query much much faster. This example here also has the unique option turned on, and that basically is going to say: If you applied this to your user model or table, and you had the email column is unique, then it would allow you to never be able to insert the same email into the database multiple times. If you ever tried to sign up twice with the same email address that was already in the database, it's going to give you an error on the database level, and then you can also do the rails validation for uniqueness on top of that, so you can enforce your data on validations inside the database. They're just a little bit more simple in your database than they would be in rails, but it's always good to be able to do that when you know for sure that you never ever want duplicate for example emails in your users table.

Transcript written by Miguel

Loading...

Subscribe to the newsletter

Join 31,353+ developers who get early access to new screencasts, articles, guides, updates, and more.

    By clicking this button, you agree to the GoRails Terms of Service and Privacy Policy.

    More of a social being? We're also on Twitter and YouTube.